目录
一、数据导入导出
1.1 搜索路径 查看 修改
1.2数据导入导出
1.3 案例1∶数据导入导出
二、管理表记录
2.1 增加表记录
2.2 查询表记录
2.3 更新表记录
2.4 删除表记录
2.5 案例2∶管理表记录
三、基本匹配条件
3.1 数值比较 字符比较 逻辑匹配 范围匹配
3.2 高级匹配条件 模糊查询 正则表达式 四则运算
3.3 操作查询结果 聚集函数 查询结果排序 分组 过滤等
3.4 案例3:匹配条件
一、数据导入导出
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 <=1000 group by shell;
查询结果过滤
.having用法
- SQL查询 having 条件表达式;
mysql> select shell from 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 shell from 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 1
mysql> select name,shell from 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,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<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 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)
