@artman328 2021-12-29T00:51:36.000000Z 字数 11241 阅读 1034

SQL 练习（基于 MySQL）

SQL 数据库

SQL 练习（基于 MySQL）

一、连接到数据库服务器

语法：

mysql -h 服务器域名或IP地址 -P 数据库服务器端口号 -u 用户名 -p

-h 忽略默认为本机地址 127.0.0.1
-P 忽略默认为 3306

例如：

mysql -h db.gotoweb.top -u user -p

回车后会提示输入密码。输入密码后，回车，看到：

mysql>

这个提示符后，就说明已经成功登录了数据库服务器。

二、查看服务器中有哪些数据库

语法：
show databases;

show: 显示，展示

例子：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| company            |
| information_schema |
+--------------------+
2 rows in set (0.19 sec)

可见当前服务器中当前用户可用的有两个数据库。其中 information_schema 是系统数据库，可暂时不理。

三、进入（使用）数据库

语法：
use 数据库名；

use: 使用

例如：

mysql> use company;
Database changed (提示：数据库已经切换，说明进入成功)
mysql>

四、查看数据库中有哪些表

语法：
show tables;
例如：

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| emps              |
+-------------------+
1 row in set (0.18 sec)

可见数据库 company 中目前只有一张表：emps

五、查看表中数据

1、查看全部数据

语法：

SELECT 字段名1，字段名2, ... 字段名n  FROM 表名;

select: 挑选
from: 从

或者（用 * 号表示全部字段）：

SELECT * FROM 表名;

例如：

mysql> select * from emps;
+----+--------+--------+--------+------------+----------+------------+------------+----------+
| id | emp_no | name   | gender | birth_date | hometown | department | emp_date   | salary   |
+----+--------+--------+--------+------------+----------+------------+------------+----------+
|  1 | E00001 | 李桂香 | 女     | 1988-07-03 | 广东     | 销售部     | 2018-09-29 | 10390.00 |
|  2 | E00002 | 潘梅   | 女     | 1998-01-31 | 云南     | 设计部     | 2021-05-10 |  7590.00 |
|  3 | E00003 | 王秀英 | 女     | 1986-12-30 | 云南     | 销售部     | 2017-07-09 | 10320.00 |
|  4 | E00004 | 马利   | 男     | 1970-08-10 | 云南     | 销售部     | 2020-02-23 |  8860.00 |
|  5 | E00005 | 张丽华 | 女     | 1971-05-31 | 北京     | 设计部     | 2012-04-01 | 12890.00 |
|  6 | E00006 | 赵倩   | 女     | 1967-03-21 | 北京     | 人事部     | 2018-11-23 |  5160.00 |
|  7 | E00007 | 郑英   | 女     | 1996-08-22 | 云南     | 销售部     | 2020-06-17 |  6720.00 |
|  8 | E00008 | 谭欢   | 男     | 1969-04-14 | 云南     | 销售部     | 2011-09-12 |  6060.00 |
|  9 | E00009 | 金兰英 | 女     | 1970-07-29 | 北京     | 售后部     | 2017-02-27 | 12470.00 |
......

2、查看部分数据

语法：

SELECT 字段名1，字段名2, ... 字段名n  FROM 表名 LIMIT [跳过的行数,] 所要的行数;

limit: 限制

或者（用 * 号表示全部字段）：

SELECT * FROM 表名 LIMIT [跳过的行数,] 行数;

[]括号里的是可以省略的内容。

例如：
查看从头开始的5条数据

mysql> select id, name, gender, birth_date 
-> from emps 
-> limit 5;
+----+-----------+--------+------------+
| id | name      | gender | birth_date |
+----+-----------+--------+------------+
|  1 | 李桂香    | 女     | 1988-07-03 |
|  2 | 潘梅      | 女     | 1998-01-31 |
|  3 | 王秀英    | 女     | 1986-12-30 |
|  4 | 马利      | 男     | 1970-08-10 |
|  5 | 张丽华    | 女     | 1971-05-31 |
+----+-----------+--------+------------+
5 rows in set (0.332 sec)

查看略过开头开始的200条数据后的5条数据

mysql> select id, name, gender, birth_date from emps limit 200,5;
+-----+-----------+--------+------------+
| id  | name      | gender | birth_date |
+-----+-----------+--------+------------+
| 201 | 邹凤兰    | 女     | 1976-09-05 |
| 202 | 朱明      | 男     | 1973-01-28 |
| 203 | 李宁      | 男     | 1979-09-02 |
| 204 | 胡明      | 男     | 1992-08-07 |
| 205 | 邱金凤    | 女     | 1967-03-28 |
+-----+-----------+--------+------------+
5 rows in set (0.177 sec)

3、查看不重复数据

语法：

SELECT DISTINCT 字段列表 FROM 表名;

distinct: 不同的

选择字段列表组合不重复的记录。
例如：
查看员工表中员工所属的部门有哪些？

mysql> select distinct department from emps;
+------------+
| department |
+------------+
| 销售部     |
| 设计部     |
| 人事部     |
| 售后部     |
| 公关部     |
+------------+
5 rows in set (0.183 sec)

4、按特定顺序查看数据

语法：

SELECT 字段列表/* FROM 表名 ORDER BY 字段1 [顺序][，字段2 [顺序，]...]

order by ... : 依据 ... 排序
asc: ascending, 上升的
desc: descending, 下降的

顺序：升序为 ASC, 降序为 DESC，不写默认为升序。
中文排序需要转码：convert(字段 using gbk)
例如：
以下按部门顺序（部门相同按性别顺序，性别相同按月薪顺序降序）查看员工数据。

mysql> select id, name, gender, department,salary 
-> from emps 
-> order by convert(department using gbk), convert(gender using gbk), salary;
| 477 | 朱静      | 女     | 售后部     |  7310.00 |
| 295 | 何欣      | 女     | 售后部     |  6760.00 |
| 720 | 金雪      | 女     | 售后部     |  5380.00 |
| 832 | 汪凯      | 男     | 销售部     | 13000.00 |
| 324 | 石欢      | 男     | 销售部     | 12960.00 |
...
| 587 | 唐鑫      | 男     | 销售部     |  5070.00 |
| 761 | 唐林      | 男     | 销售部     |  5060.00 |
| 673 | 潘佳      | 男     | 销售部     |  5040.00 |
| 127 | 尹建      | 男     | 销售部     |  5030.00 |
| 202 | 朱明      | 男     | 销售部     |  5010.00 |
| 735 | 陈坤      | 男     | 销售部     |  5010.00 |
| 582 | 陈琴      | 女     | 销售部     | 12990.00 |
| 592 | 叶玉      | 女     | 销售部     | 12990.00 |
| 621 | 宋秀荣    | 女     | 销售部     | 12880.00 |
| 322 | 阎晶      | 女     | 销售部     | 12880.00 |
| 376 | 曾畅      | 女     | 销售部     | 12850.00 |
| 755 | 鞠桂珍    | 女     | 销售部     | 12840.00 |
...

查看跳过前100条记录后的5条数据

mysql> select id, name, gender, birth_date 
-> from emps 
-> limit 100,5;
+-----+-----------+--------+------------+
| id  | name      | gender | birth_date |
+-----+-----------+--------+------------+
| 101 | 魏志强    | 男     | 1991-04-20 |
| 102 | 蒋淑珍    | 女     | 1966-10-24 |
| 103 | 仝龙      | 男     | 1992-07-12 |
| 104 | 刘丽丽    | 女     | 1996-12-11 |
| 105 | 罗畅      | 女     | 1969-12-02 |
+-----+-----------+--------+------------+
5 rows in set (0.184 sec)

5、查看时给字段改名

语法：

SELECT 字段名1 AS 新字段名1, 字段名2 AS 新字段名2, ... FROM 表名 ...

as: 作为

例如：

mysql> SELECT id AS 编号, name AS 姓名, birth_date AS 出生日期 
-> FROM emps 
-> limit 5;
+--------+-----------+--------------+
| 编号   | 姓名      | 出生日期     |
+--------+-----------+--------------+
|      1 | 李桂香    | 1988-07-03   |
|      2 | 潘梅      | 1998-01-31   |
|      3 | 王秀英    | 1986-12-30   |
|      4 | 马利      | 1970-08-10   |
|      5 | 张丽华    | 1971-05-31   |
+--------+-----------+--------------+
5 rows in set (0.187 sec)

六、按条件查看数据

语法：

SELECT 字段列表 或 *  FROM 表名 WHERE 条件;

where: 在那里

条件由比较运算和逻辑运算构成。

比较运算

序号	运算符	含义	举例
1	=	等于	name='王晓晓'
2	!=	不等于	price!=800
3	>	大于	count>1500
4	>=	大于或等于	dateOfBirth>='1990-12-31'
5	<	小于	age<18
6	<=	小于等于	age<=18
7	between x and y	介于 x 和 y 之间（含 x 和 y）	age between 18 and 60
8	in (a,b,...)	值等于 a,b,...中的任意一个	loaction in ('昆明','上海','北京')
9	like 'xyz'	像（匹配），% 代表零到任意多个任意字符的组合	name like '李%' name like '%明' name like '%小%'

逻辑运算

序号	运算符	含义	举例
1	and	并且	a>10 and b>30（两条都要满足）
2	or	或者	a>10 or b>30（只要一条满足即可）
3	not	不（否定）	name not like '%so%' id not in (1,10,12)
4	xor	异或（相异为真）	age>18 xor title=''

举例

1、查在某个工资范围内的员工

查看员工工资在8000到9000之间（含8000和9000）的信息。

mysql> SELECT id, name, salary 
-> FROM emps 
-> WHERE salary>=8000 and salary<=9000;
+-----+-----------+---------+
| id  | name      | salary  |
+-----+-----------+---------+
|   4 | 马利      | 8860.00 |
|  11 | 王桂芝    | 8370.00 |
|  17 | 杨宇      | 8630.00 |
......
| 844 | 江杨      | 8190.00 |
+-----+-----------+---------+
116 rows in set (0.198 sec)

或者：

mysql> SELECT id, name, salary 
-> FROM emps 
-> WHERE salary between 8000 and 9000;
+-----+-----------+---------+
| id  | name      | salary  |
+-----+-----------+---------+
|   4 | 马利      | 8860.00 |
|  11 | 王桂芝    | 8370.00 |
|  17 | 杨宇      | 8630.00 |
......
| 844 | 江杨      | 8190.00 |
+-----+-----------+---------+
116 rows in set (0.190 sec)

2、查找出生于某个日期后的员工

以下查找出生于1995年12月31日以后的员工，并按生日升序排序。

mysql> select id,name,gender,birth_date 
-> from emps 
-> where birth_date>'1995-12-31' 
-> order by birth_date asc;
+-----+-----------+--------+------------+
| id  | name      | gender | birth_date |
+-----+-----------+--------+------------+
| 496 | 李莹      | 女     | 1996-01-18 |
| 119 | 邵艳      | 女     | 1996-01-24 |
| 753 | 李宁      | 男     | 1996-01-24 |
|  70 | 李兰英    | 女     | 1996-01-25 |
......
| 520 | 刘桂香    | 女     | 1998-11-16 |
| 334 | 胡丽娟    | 女     | 1998-11-18 |
| 802 | 叶春梅    | 女     | 1998-11-20 |
+-----+-----------+--------+------------+
71 rows in set (0.191 sec)

3、查找某个姓氏的员工

以下查找姓“许”的员工。

mysql> select id, name, gender 
-> from emps 
-> where name like '许%';
+-----+-----------+--------+
| id  | name      | gender |
+-----+-----------+--------+
| 145 | 许莉      | 女     |
| 316 | 许伟      | 男     |
| 479 | 许玉华    | 女     |
| 584 | 许宁      | 男     |
| 773 | 许燕      | 女     |
| 784 | 许建华    | 男     |
| 799 | 许小红    | 女     |
+-----+-----------+--------+
7 rows in set (0.187 sec)

4、查找姓名包含某些关键字的员工

以下查找姓名中包含“金”字的员工。

mysql> select id, name, gender 
-> from emps 
-> where name like '%金%';
+-----+-----------+--------+
| id  | name      | gender |
+-----+-----------+--------+
|   9 | 金兰英    | 女     |
|  56 | 金冬梅    | 女     |
| 122 | 董金凤    | 女     |
| 205 | 邱金凤    | 女     |
| 395 | 金刚      | 男     |
| 602 | 郑金凤    | 女     |
| 618 | 王金凤    | 女     |
| 674 | 陈金凤    | 女     |
| 720 | 金雪      | 女     |
| 795 | 邱金凤    | 女     |
| 803 | 金桂花    | 女     |
+-----+-----------+--------+
11 rows in set (0.200 sec)

5、查找id为某些值的员工

以下例列出id为11，23，155，278的员工。

mysql> select id, name, gender 
-> from emps 
-> where id in (11,23,155,278);
+-----+-----------+--------+
| id  | name      | gender |
+-----+-----------+--------+
|  11 | 王桂芝    | 女     |
|  23 | 李淑英    | 女     |
| 155 | 刘琴      | 女     |
| 278 | 杨斌      | 男     |
+-----+-----------+--------+
4 rows in set (0.184 sec)

复杂条件查找

以下查找“月薪小于5000并且性别为女性；或者月薪大于8000，并且性别为男性或者出生于1995年以后”的员工。

mysql> select id,name,gender,birth_date,salary 
from emps 
where (salary<8000 and gender='女') 
or (salary>10000 and (gender='男' or birth_date>'1995-12-31'))
order by gender, salary desc, birth_date;
+-----+-----------+--------+------------+----------+
| id  | name      | gender | birth_date | salary   |
+-----+-----------+--------+------------+----------+
| 169 | 陈海燕    | 女     | 1997-11-14 | 12710.00 |
| 486 | 周秀梅    | 女     | 1997-01-08 | 12450.00 |
| 802 | 叶春梅    | 女     | 1998-11-20 | 12430.00 |
|  51 | 李洁      | 女     | 1998-08-07 | 12190.00 |
|  19 | 蔡柳      | 女     | 1997-10-31 | 11930.00 |
| 696 | 袁丹      | 女     | 1998-06-25 | 11890.00 |
...
| 133 | 杨欢      | 男     | 1981-03-19 | 10060.00 |
| 770 | 邱杨      | 男     | 1983-03-24 | 10060.00 |
| 340 | 何浩      | 男     | 1991-11-17 | 10050.00 |
| 106 | 单平      | 男     | 1993-05-30 | 10030.00 |
| 813 | 杨楠      | 男     | 1998-03-20 | 10030.00 |
| 504 | 王军      | 男     | 1973-12-31 | 10020.00 |
+-----+-----------+--------+------------+----------+
322 rows in set (0.209 sec)

七、统计与计算

1、统计表中的记录条数

语法：

SELECT COUNT(*) FROM 表名;

例如：

（1）统计表中的所有记录数

mysql> SELECT COUNT(*) AS 记录数 FROM emps;
+-----------+
| 记录数    |
+-----------+
|       852 |
+-----------+
1 row in set (0.197 sec)

（2）统计表中月薪在8000元及以上的员工数

mysql> SELECT COUNT(*) AS 8000元及以上员工人数 
-> FROM emps 
-> WHERE salary>=8000;
+------------------------------+
| 8000元及以上员工人数         |
+------------------------------+
|                          545 |
+------------------------------+
1 row in set (0.313 sec)

2、获取表中某数字列的总和、最大值、最小值和平均值

语法：

SELECT SUM(列名), MAX(列名), MIN(列名), AVG(列名) FROM 表名 ...

sum: 总和；max: maximum, 最大值; min: minimum, 最小值; avg: average, 平均值

例如：
统计和计算emps表中月薪一列 (salary) 的总和、最大值、最小值及平均值。

mysql> SELECT SUM(salary) AS 月薪总额, MAX(salary) AS 最高月薪, 
-> MIN(salary) AS 最低月薪, AVG(salary) AS 平均月薪 
-> FROM emps;
+--------------+--------------+--------------+--------------+
| 月薪总额     | 最高月薪     | 最低月薪     | 平均月薪     |
+--------------+--------------+--------------+--------------+
|   7788690.00 |     13000.00 |      5010.00 |  9141.654930 |
+--------------+--------------+--------------+--------------+
1 row in set (0.186 sec)

3、分组统计

语法：

SELECT 分组列名1，分组列名2, ..., 统计函数... FROM emps ... GROUP BY 分组列名1, 分组列名2，...;

group by ... : 依据 ... 分组

举例：

（1）统计 emps 表中各部门人数

mysql> SELECT department AS 部门, count(*) AS 人数 
-> FROM emps 
-> GROUP BY department; 
+-----------+--------+
| 部门      | 人数   |
+-----------+--------+
| 人事部    |     66 |
| 公关部    |     56 |
| 售后部    |     74 |
| 设计部    |     66 |
| 销售部    |    590 |
+-----------+--------+
5 rows in set (0.752 sec)

（2）统计 emps 表中各部门男女员工人数

 SELECT department AS 部门, gender AS 性别, count(*) AS 人数 
 -> FROM emps 
 -> GROUP BY department, gender;
+-----------+--------+--------+
| 部门      | 性别   | 人数   |
+-----------+--------+--------+
| 人事部    | 女     |     36 |
| 人事部    | 男     |     30 |
| 公关部    | 女     |     30 |
| 公关部    | 男     |     26 |
| 售后部    | 女     |     41 |
| 售后部    | 男     |     33 |
| 设计部    | 女     |     31 |
| 设计部    | 男     |     35 |
| 销售部    | 女     |    309 |
| 销售部    | 男     |    281 |
+-----------+--------+--------+
10 rows in set (0.187 sec)

（3）统计 emps 表中名年份出生的员工人数

SELECT YEAR(birth_date) AS 出生年份, COUNT(*) AS 员工人数 
-> FROM emps 
-> GROUP BY 出生年份;
+--------------+--------------+
| 出生年份     | 员工人数     |
+--------------+--------------+
|         1963 |            3 |
|         1964 |           23 |
|         1965 |           24 |
|         1966 |           23 |
|         1967 |           30 |
|         1968 |           31 |
...
|         1994 |           24 |
|         1995 |           21 |
|         1996 |           21 |
|         1997 |           19 |
|         1998 |           31 |
+--------------+--------------+
36 rows in set (0.187 sec)

YEAR(日期)：取出日期的年份信息
GROUP BY 中的列名可用别名替代

（4）按1000元为一个等级起点，统计各等级月薪的员工为数

即：收入0～999元几人，收入1000元到1999元几人，2000元到2999元几人...

SELECT FLOOR(salary/1000)*1000 AS 月薪等级, COUNT(*) AS 员工人数
-> FROM emps 
-> GROUP BY 月薪等级;
+--------------+--------------+
| 月薪等级     | 员工人数     |
+--------------+--------------+
|         5000 |           86 |
|         6000 |          103 |
|         7000 |          118 |
|         8000 |          114 |
|         9000 |           93 |
|        10000 |           96 |
|        11000 |          124 |
|        12000 |          116 |
|        13000 |            2 |
+--------------+--------------+
9 rows in set (0.187 sec)

FLOOR(小数)：取小数的整数部分，不四舍五入

或者：

mysql> SELECT CONCAT(FLOOR(salary/1000)*1000,'~',FLOOR(salary/1000)*1000+999) AS 月薪等级,
-> COUNT(*) 员工人数 
-> FROM emps 
-> GROUP BY 月薪等级 
-> ORDER BY FLOOR(salary/1000)*1000;
+--------------+--------------+
| 月薪等级     | 员工人数     |
+--------------+--------------+
| 5000~5999    |           86 |
| 6000~6999    |          103 |
| 7000~7999    |          118 |
| 8000~8999    |          114 |
| 9000~9999    |           93 |
| 10000~10999  |           96 |
| 11000~11999  |          124 |
| 12000~12999  |          116 |
| 13000~13999  |            2 |
+--------------+--------------+
9 rows in set (0.205 sec)

CONCAT(值1，值2，... 值n)：将所有列出来的值变成字符串并拼接进来。

期末随堂考思考题

1、请从 emps 表中选择出“月薪（salary）最高的5个员工，月薪相同的入职日期（emp_date）越早的排在越靠前的位置”。

2、请按此字段顺序显示50名员工信息：序号（id）,姓名（name），性别（gender）,出生日期（birth_date），年龄（从出生日期计算）。年龄越大越靠前。

两个日期时间之间的差值计算：

timestatmpdiff(单位,日期1，日期2)

单位：

表示	含义	举例
YEAR	年	'1999-10-10'出生的人当前年龄？ timestampdiff(YEAR, '1999-10-10',now())
MONTH	月
QUARTER	季度
DAY	天	现在距2035年还有几天？ timestampdiff(DAY,now(),'2035-01-01')
WEEK	星期
HOUR	小时
MINUTE	分
SECOND	秒

3、统计在公司工作超过5年的员工有几个？
4、列出在公司工作8年及以上，但月薪低于6000元的员工（列出的信息包含工龄）。
5、按部门列出最高工资、最低工资、平均工资和工资总额。
6、统计公司员工中男女员工各多少？
7、列出员工的籍贯（hometown）都有哪些（不重复）。
8、找出姓名包含“兰”字的员工。
9、找出所有90后员工，并按出生日期升序排序。
10、列出忽略前100条记录后的10条记录，按雇用日期（emp_date）升序排序。