MySQL数据库基础(三)数据的导入导出及表记录管理 mysql运维linux

一、数据导入导出 1.1 搜索路径查看修改
查看搜索路径

mysql> show variables like "secure_file_priv"; +------------------+-----------------------+ | Variable_name| Value| +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec)[root@mysql ~]# ls -ld /var/lib/mysql-files/ drwxr-x--- 2 mysql mysql 6 11月 24 14:07 /var/lib/mysql-files/

修改搜索路径

[root@mysql ~]# mkdir /myload [root@mysql ~]# chown mysql /myload/[root@mysql ~]# vim /etc/my.cnf [mysqld] secure_file_priv="/myload"//在[mysqld]下面追加配置 [root@mysql ~]# systemctl restart mysqldmysql> show variables like "secure_file_priv"; +------------------+----------+ | Variable_name| Value| +------------------+----------+ | secure_file_priv | /myload/ | +------------------+----------+

1.2 数据导入导出
命令格式
数据导入步骤
-默认只有root用户有数据导入权限
-建表
-导入数据
命令格式
mysql> load data infile “目录名/文件名"
into table 库名.表名
fields terminated by “分隔符”
lines terminated by "\n";
注意事项
具体如下︰
-字段分隔符要与文件一致
-表字段类型和字段个数要与文件匹配
-导入数据时指定文件的绝对路径
数据的导出
命令格式
·格式1
mysql> select 命令 into outfile “目录名/文件名”
·格式2
mysql> select 命令 into outfile “目录名/文件名” fields terminated by “分隔符”;
·格式3
mysql> select命令into outfile “目录名/文件名”
fields terminated by “分隔符”
lines terminated by “\n”;
注意事项
-导出数据行数由SQL查询决定
-导出的是表记录，不包括字段名
-自动创建存储数据的文件
-存储数据文件，具有唯一性
1.3 案例1∶数据导入导出
具体要求如下:
1 )修改检索目录为/myload 。
2 )将/etc/passwd文件导入db3库的user表里，并添加行号字段。
3）将db3库user表所有记录导出,存到/myload/user.txt 文件里。

mysql> create database db3; //创建数据库 Query OK, 1 row affected (0.01 sec)mysql> create table db3.user(//创建表单 -> name char(50), -> password char(1), -> uid int, -> gid int, -> comment char(150), -> homedir char(50), -> shell char(50)); Query OK, 0 rows affected (0.09 sec)mysql> load data infile "/myload/passwd" -> into table db3.user fields terminated by ":"//导入数据其中以 : 为分隔符 -> lines terminatedd by "\n"; Query OK, 21 rows affected (0.02 sec) Records: 21Deleted: 0Skipped: 0Warnings: 0mysql> select * from db3.user; +-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ | name| password | uid| gid| comment| homedir| shell| +-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ | root| x|0 |0 | root| /root| /bin/bash| | bin| x|1 |1 | bin| /bin| /sbin/nologin| | daemon| x|2 |2 | daemon| /sbin| /sbin/nologin| | adm| x|3 |4 | adm| /var/adm| /sbin/nologin| | lp| x|4 |7 | lp| /var/spool/lpd| /sbin/nologin| | sync| x|5 |0 | sync| /sbin| /bin/sync| | shutdown| x|6 |0 | shutdown| /sbin| /sbin/shutdown | | halt| x|7 |0 | halt| /sbin| /sbin/halt| | mail| x|8 |12 | mail| /var/spool/mail | /sbin/nologin| | operator| x|11 |0 | operator| /root| /sbin/nologin| | games| x|12 |100 | games| /usr/games| /sbin/nologin| | ftp| x|14 |50 | FTP User| /var/ftp| /sbin/nologin| | nobody| x|99 |99 | Nobody| /| /sbin/nologin| | systemd-network | x|192 |192 | systemd Network Management| /| /sbin/nologin| | dbus| x|81 |81 | System message bus| /| /sbin/nologin| | polkitd| x|999 |998 | User for polkitd| /| /sbin/nologin| | tss| x|59 |59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null| /sbin/nologin| | sshd| x|74 |74 | Privilege-separated SSH| /var/empty/sshd | /sbin/nologin| | chrony| x|998 |996 || /var/lib/chrony | /sbin/nologin| | tcpdump| x|72 |72 || /| /sbin/nologin| | mysql| x|27 |27 | MySQL Server| /var/lib/mysql| /bin/false| +-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ 21 rows in set (0.00 sec)mysql> alter table db3.user -> add id int primary key auto_increment first; Query OK, 0 rows affected (0.12 sec) Records: 0Duplicates: 0Warnings: 0mysql> select * from db3.user; +----+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ | id | name| password | uid| gid| comment| homedir| shell| +----+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ |1 | root| x|0 |0 | root| /root| /bin/bash| |2 | bin| x|1 |1 | bin| /bin| /sbin/nologin| |3 | daemon| x|2 |2 | daemon| /sbin| /sbin/nologin| |4 | adm| x|3 |4 | adm| /var/adm| /sbin/nologin| |5 | lp| x|4 |7 | lp| /var/spool/lpd| /sbin/nologin| |6 | sync| x|5 |0 | sync| /sbin| /bin/sync| |7 | shutdown| x|6 |0 | shutdown| /sbin| /sbin/shutdown | |8 | halt| x|7 |0 | halt| /sbin| /sbin/halt| |9 | mail| x|8 |12 | mail| /var/spool/mail | /sbin/nologin| | 10 | operator| x|11 |0 | operator| /root| /sbin/nologin| | 11 | games| x|12 |100 | games| /usr/games| /sbin/nologin| | 12 | ftp| x|14 |50 | FTP User| /var/ftp| /sbin/nologin| | 13 | nobody| x|99 |99 | Nobody| /| /sbin/nologin| | 14 | systemd-network | x|192 |192 | systemd Network Management| /| /sbin/nologin| | 15 | dbus| x|81 |81 | System message bus| /| /sbin/nologin| | 16 | polkitd| x|999 |998 | User for polkitd| /| /sbin/nologin| | 17 | tss| x|59 |59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null| /sbin/nologin| | 18 | sshd| x|74 |74 | Privilege-separated SSH| /var/empty/sshd | /sbin/nologin| | 19 | chrony| x|998 |996 || /var/lib/chrony | /sbin/nologin| | 20 | tcpdump| x|72 |72 || /| /sbin/nologin| | 21 | mysql| x|27 |27 | MySQL Server| /var/lib/mysql| /bin/false| +----+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+ 21 rows in set (0.00 sec)mysql> select * from db3.user into outfile "/myload/user1.txt"; Query OK, 21 rows affected (0.00 sec)mysql> exit Bye [root@mysql ~]# cat /myload/user1.txt 1rootx00root/root/bin/bash 2binx11bin/bin/sbin/nologin 3daemonx22daemon/sbin/sbin/nologin 4admx34adm/var/adm/sbin/nologin 5lpx47lp/var/spool/lpd/sbin/nologin 6syncx50sync/sbin/bin/sync 7shutdownx60shutdown/sbin/sbin/shutdown 8haltx70halt/sbin/sbin/halt 9mailx812mail/var/spool/mail/sbin/nologin 10operatorx110operator/root/sbin/nologin 11gamesx12100games/usr/games/sbin/nologin 12ftpx1450FTP User/var/ftp/sbin/nologin 13nobodyx9999Nobody//sbin/nologin 14systemd-networkx192192systemd Network Management//sbin/nologin 15dbusx8181System message bus//sbin/nologin 16polkitdx999998User for polkitd//sbin/nologin 17tssx5959Account used by the trousers package to sandbox the tcsd daemon/dev/null/sbin/nologin 18sshdx7474Privilege-separated SSH/var/empty/sshd/sbin/nologin 19chronyx998996/var/lib/chrony/sbin/nologin 20tcpdumpx7272//sbin/nologin 21mysqlx2727MySQL Server/var/lib/mysql/bin/false

二、管理表记录 2.1 增加表记录
语法格式
·格式1∶ 添加1条记录，给所有字段赋值
-insert into 表名 values (字段值列表);
·格式2∶ 添加多条记录，给所有字段赋值
-insert into 表名
values (字段值列表),(字段值列表),(字段值列表);
语法格式(续1)
·格式3︰添加1条记录，给指定字段赋值
-insert into 表名(字段名列表) values(字段值列表);
·格式4∶添加多条记录，给指定字段赋值
-insert into 表名(字段名列表)
values (字段值列表)， //第1条记录
(字段值列表)， //第2条记录
(字段值列表); //第3条记录
2.2 查询表记录
语法格式
格式1
-select字段1，......字段N from 库名.表名;
格式2条件查询
-select字段1,....字段N from 库名.表名 where 条件表达式;
注意事项
要求如下
-* 表示所有字段
-查看当前库表记录时库名可以省略
-字段列表决定显示列个数
-条件决定显示行的个数
2.3 更新表记录
语法格式
·格式1，批量更新
-update 库名.表名
set 字段名=值，字段名=值，字段名=值......;
·格式2，条件匹配更新
-update 库名.表名
set 字段名=值字段名=值字段名=值,.....
where 条件表达式;
注意事项
·具体如下:
-字段值要与字段类型相匹配
-对于字符类型的字段，值要用双引号括起来
-若不使用where限定条件，会更新所有记录字段值-限定条件时，只更新匹配条件的记录的字段值
2.4 删除表记录
语法格式
·格式1，条件匹配删除
-delete from 库名.表名 where条件表达式;
·格式2，删除所有记录
-delete from 库名.表名;
注意事项
具体如下:
-不加条件删除表中的所有行
2.5 案例2∶管理表记录
-具体要求如下:
-练习插入表记录
-练习更新表记录
-练习查询表记录
-练习删除表记录

mysql> create table stu_info( -> name char(20), -> gender char(10) default "boy", -> age int(10)); Query OK, 0 rows affected, 1 warning (0.28 sec)mysql> desc stu_info; +--------+----------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | name| char(20) | YES|| NULL|| | gender | char(10) | YES|| boy|| | age| int| YES|| NULL|| +--------+----------+------+-----+---------+-------+mysql> insert stu_info values -> ("Jim","girl",24), -> ("Tom","boy",21), -> ("Lily","girl",20); mysql> select * from stu_info; +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily | girl|20 | +------+--------+------+ 3 rows in set (0.00 sec)mysql> insert into stu_info(name,age) values("Jerry",27); Query OK, 1 row affected (0.01 sec)mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | | Jerry | boy|27 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> update stu_info set age=20; Query OK, 4 rows affected (0.01 sec) Rows matched: 4Changed: 4Warnings: 0mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|20 | | Tom| boy|20 | | Lily| girl|20 | | Jerry | boy|20 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> update stu_info set age=25 where name="Lily"; Query OK, 1 row affected (0.01 sec) Rows matched: 1Changed: 1Warnings: 0mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|20 | | Tom| boy|20 | | Lily| girl|25 | | Jerry | boy|20 | +-------+--------+------+ 4 rows in set (0.01 sec)mysql> delete from stu_info where age >20; Query OK, 1 row affected (0.01 sec)mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|20 | | Tom| boy|20 | | Jerry | boy|20 | +-------+--------+------+ 3 rows in set (0.00 sec)mysql> delete from stu_info; Query OK, 3 rows affected (0.01 sec)mysql> select * from stu_info; Empty set (0.00 sec)

三、基本匹配条件 3.1 数值比较字符比较逻辑匹配范围匹配
数值比较
-字段必须是数值类型

文章图片

字符比较/匹配空非空
-字段必须是字符类型

文章图片

逻辑匹配
-多个判断条件时使用

文章图片

范围匹配/去重显示
·匹配范围内的任意一个值即可

文章图片

3.2 高级匹配条件模糊查询正则表达式四则运算
模糊查询·用法
where 字段名 like "通配符'
_ 表示1个字符
% 表示0~n个字符
-示例
-列出name值 “4个字符” 的记录

mysql> select * from mysql.user where userlike "_ _ _ _"\G;

正则表达式.
用法
where 字段名 regexp '正则表达式’
正则元字符 ^ $ . [] * |
示例
列出name值“以m开头或n结尾”的记录

mysql> select name from db3.user where name regexp "^m|n$"; +----------+ | name| +----------+ | bin| | daemon| | shutdown | | mail| | mysql| +----------+ 5 rows in set (0.00 sec)

四则运算
运算操作
-字段必须是数值类型

文章图片

3.3 操作查询结果聚集函数查询结果排序分组过滤等
【MySQL数据库基础(三)数据的导入导出及表记录管理】聚集函数
·MySQL内置数据统计函数

avg(字段名) //统计字段平均值
sum(字段名) //统计字段之和
min(字段名) //统计字段最小值
max(字段名) //统计字段最大值
count(字段名) //统计字段值个数

mysql> select count(name) from db3.user; mysql> select count(name) from db3.user -> where shell !="/bin/bash";

查询结果排序
·用法

SQL查询 order by 字段名 [ asc | desc ];
asc 升序排序
desc 降序排序 //通常是数值类型的字段

通常是数值类型字段

mysql> select id,name from db3.user where name regexp "^m|n$" order by id asc; +----+----------+ | id | name| +----+----------+ |2 | bin| |3 | daemon| |7 | shutdown | |9 | mail| | 21 | mysql| +----+----------+ 5 rows in set (0.00 sec)

查询结果分组
·用法

SQL查询 group by 字段名;

通常是字符类型字段

mysql> select shellfrom db3.user group by shell; mysql> select shellfrom db3.user where uid <=1000 group by shell;

查询结果过滤
.having用法

SQL查询 having 条件表达式;

mysql> select shellfrom db3.user where uid<=1000 group by shell having shell="/bin/bash"; +-----------+ | shell| +-----------+ | /bin/bash | +-----------+ 1 row in set (0.00 sec)

在这里，如果用WHERE代替HAVING就会出错。

mysql> select shellfrom db3.user where uid<=1000 group by shell where shell="/bin/bash"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where shell="/bin/bash"' at line 1mysql> select name,shellfrom db3.user where uid<=1000 having name="adm"; +------+---------------+ | name | shell| +------+---------------+ | adm| /sbin/nologin | +------+---------------+ 1 row in set (0.00 sec)

having语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
having语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
限制查询结果显示行数
注意事项：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
·用法

SQL查询limit 数字; 1 //显示查询结果前多少条记录
SQL查询limit 数字1,数字2; //显示指定范围内的查询记录其中:数字1 代表起始行（О表示第1行) 数字2代数总的显示行数

mysql> select name,shellfrom db3.user limit 3; //显示前面3行 +--------+---------------+ | name| shell| +--------+---------------+ | root| /bin/bash| | bin| /sbin/nologin | | daemon | /sbin/nologin | +--------+---------------+ 3 rows in set (0.00 sec)mysql> select name,shellfrom db3.user limit 3,4; //显示从第3行开始后面的4行 +----------+----------------+ | name| shell| +----------+----------------+ | adm| /sbin/nologin| | lp| /sbin/nologin| | sync| /bin/sync| | shutdown | /sbin/shutdown | +----------+----------------+ 4 rows in set (0.00 sec)

3.4 案例3:匹配条件
·具体要求如下︰
-练习数值比较的使用
-练习字符比较的使用
-练习逻辑比较的使用
-练习模糊匹配的使用
-练习正则匹配的使用
-练习查询结果分组、排序、过滤、限制显示记录行数
-练习聚集函数的使用
-练习四则运算的使用

mysql> insert stu_info values -> ("Jim","girl",24), -> ("Tom","boy",21), -> ("Lily","girl",20), -> ("Betty","girl",25), -> ("Byrom","boy",25); Query OK, 5 rows affected (0.01 sec) Records: 5Duplicates: 0Warnings: 0mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select * from stu_info; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select count(*) from stu_info; +----------+ | count(*) | +----------+ |5 | +----------+ 1 row in set (0.00 sec)mysql> select avg(age),max(age),min(age) from stu_info; +----------+----------+----------+ | avg(age) | max(age) | min(age) | +----------+----------+----------+ |23.0000 |25 |20 | +----------+----------+----------+ 1 row in set (0.00 sec)mysql> select count(gender) from stu_info where gender="boy"; +---------------+ | count(gender) | +---------------+ |2 | +---------------+ 1 row in set (0.00 sec)mysql> select * from stu_info where age=25; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where age>=21; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> select * from stu_info where age between 20 and 25; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select * from stu_info where age between 21 and 24; +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | +------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where age>22 and gender='girl'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Betty | girl|25 | +-------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where age<25 or gender='girl'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | | Betty | girl|25 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> select * from stu_info where name in ("jim","lily","minnie"); +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | | Lily | girl|20 | +------+--------+------+ 2 rows in set (0.00 sec)mysql> select 123+3455; +----------+ | 123+3455 | +----------+ |3578 | +----------+ 1 row in set (0.00 sec)mysql> select 123*3455; +----------+ | 123*3455 | +----------+ |424965 | +----------+ 1 row in set (0.00 sec)mysql> select 12323/3455; +------------+ | 12323/3455 | +------------+ |3.5667 | +------------+ 1 row in set (0.00 sec)mysql> select name,age+15 from stu_info; +-------+--------+ | name| age+15 | +-------+--------+ | Jim|39 | | Tom|36 | | Lily|35 | | Betty |40 | | Byrom |40 | +-------+--------+ 5 rows in set (0.00 sec)mysql> select * from stu_info where name like 'j%'; +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | +------+--------+------+ 1 row in set (0.00 sec)mysql> select * from stu_info where name like 'b%'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where name like '___'; +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | +------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where name like '____'; +------+--------+------+ | name | gender | age| +------+--------+------+ | Lily | girl|20 | +------+--------+------+ 1 row in set (0.00 sec)mysql> select * from stu_info where name like 'b___'; Empty set (0.00 sec)mysql> select * from stu_info where name like 'b__'; Empty set (0.00 sec)mysql> select * from stu_info where name like 'b____'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 2 rows in set (0.00 sec)mysql> select * from stu_info where name regexp '^b|m$'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> select * from stu_info where name like 'b%' or name like '%m'; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 4 rows in set (0.00 sec)mysql> select* from stu_info order by age; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Lily| girl|20 | | Tom| boy|21 | | Jim| girl|24 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select* from stu_info order by age asc; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Lily| girl|20 | | Tom| boy|21 | | Jim| girl|24 | | Betty | girl|25 | | Byrom | boy|25 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select* from stu_info order by age desc; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Betty | girl|25 | | Byrom | boy|25 | | Jim| girl|24 | | Tom| boy|21 | | Lily| girl|20 | +-------+--------+------+ 5 rows in set (0.00 sec)mysql> select* from stu_info limit 3; +------+--------+------+ | name | gender | age| +------+--------+------+ | Jim| girl|24 | | Tom| boy|21 | | Lily | girl|20 | +------+--------+------+ 3 rows in set (0.00 sec)mysql> select agefrom stu_info group by age; +------+ | age| +------+ |24 | |21 | |20 | |25 | +------+ 4 rows in set (0.00 sec)mysql> select* from stu_info order by age desc limit 3; +-------+--------+------+ | name| gender | age| +-------+--------+------+ | Betty | girl|25 | | Byrom | boy|25 | | Jim| girl|24 | +-------+--------+------+ 3 rows in set (0.01 sec)mysql> select gender,count(gender) from stu_info group by gender; +--------+---------------+ | gender | count(gender) | +--------+---------------+ | girl|3 | | boy|2 | +--------+---------------+ 2 rows in set (0.00 sec)mysql> select gender as "性别",count(gender) as "人数" from stu_info group by gender; +--------+--------+ | 性别| 人数| +--------+--------+ | girl|3 | | boy|2 | +--------+--------+ 2 rows in set (0.00 sec)