MySQL数据库基础(三)数据的导入导出及表记录管理
来源：SegmentFault
时间：2023-02-24 16:08:50 461浏览 收藏
本篇文章向大家介绍《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 mysqld mysql> 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: 21 Deleted: 0 Skipped: 0 Warnings: 0
mysql> 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: 0 Duplicates: 0 Warnings: 0
mysql> 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
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二、管理表记录
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: 4 Changed: 4 Warnings: 0
mysql> 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: 1 Changed: 1 Warnings: 0
mysql> 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 user like "_ _ _ _"\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内置数据统计函数
- 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 shell from db3.user group by shell;
mysql> select shell from db3.user
where uid 查询结果过滤
.having用法
- SQL查询 having 条件表达式;
mysql> select shell from db3.user where uid
在这里，如果用WHERE代替HAVING就会出错。
mysql> select shell from db3.user where uid select name,shell from db3.user where uid
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,shell from 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,shell from 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: 5 Duplicates: 0 Warnings: 0
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 * 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 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 age from 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)好了，本文到此结束，带大家了解了《MySQL数据库基础(三)数据的导入导出及表记录管理》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
最新阅读
更多>
-
208 收藏
-
174 收藏
-
317 收藏
-
371 收藏
-
244 收藏
-
288 收藏
课程推荐
更多>
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
- 立即学习 484次学习