数据库（MySQL）练习
- 一、练习
-
- 1.15练习
- 1.16练习
-
- 1.显示所有职工基本信息
- 2.查询所有职工所属部门的部门号，不显示重复的部门号
- 3.求出所有职工的人数
- 4.列出最高工和最低工资
- 5.列出职工的平均工资和总工资
- 6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表
- 7. 显示所有党员的年龄
- 8.列出工资在4000-8000之间的所有职工姓名
- 9.列出所有孙姓和李姓的职工姓名
- 10.列出所有部门号为102和103日不是党员的职工号、姓名
- 11.将职工表t_worker中的职工按出生的先后顺序排序
- 12.显示工资最高的前3名职工的职工号和姓名
- 13.求出各部门党员的人数
- 14.统计各部门的工资和平均工资并保留2位小数
- 15.列出总人数大于等于3的部门号和总人数
- 1.18练习
-
- 1.分别查询student表和score表的所有记录
- 2.查询 student表 的第2条到5条记录
- 3.从student表中查询计算机系和英语系的学生的信息
- 4.从student表中查询年龄小于22岁的学生信息
- 5.从student表中查询每个院系有多少人
- 6.从score表中查询每个科目的最高分
- 7.查询李广昌的考试科目(cname)和考试成绩(grade)
- 8.用连接的方式查询所有学生的信息和考试信息
- 9.计算每个学生的总成绩
- 10.计算每个考试科目的平均成绩
- 11.查询计算机成绩低于95的学生信息
- 12.将计算机考试成绩按从高到低进行排序
- 13.从student表和score表中 查询出学生的学号，然后合并查询结果
- 14.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
- 15.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
- 2.7 练习
- 2.8 练习
- 二、注意事项
一、练习
1.15练习
win11安装配置MySQL超详细教程: https://baijiahao.baidu.com/s?id=1786910666566008458&wfr=spider&for=pc
准备工作：
mysql -uroot -p #以管理员身份登录
mysql> select user(); #查看当前登录账户，登录方式
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show databases; #查看当下账户的所有库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb_temp1 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
11 rows in set (0.00 sec)
mysql> create database mydb6_product; #创建新库 mydb6_product
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb_temp1 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
12 rows in set (0.00 sec)
mysql> use mydb6_product; #转换当前登录库
Database changed
mysql> select database();
+---------------+
| database() |
+---------------+
| mydb6_product |
+---------------+
1 row in set (0.00 sec)
employees表：
主键：primary key
不能为空：not null
设置默认值：default’***'
mysql> create table employees(id int primary key , name varchar(50) not null ,age int ,gender varchar(10) not null default'unknown' ,salary float);
Query OK, 0 rows affected (0.04 sec)
mysql> desc employees;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(10) | NO | | unknown | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
orders表
mysql> create table orders(id int primary key , name varchar(100) not null ,price float,
quantity int ,category varchar(50));
Query OK, 0 rows affected (0.03 sec)
mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(100) | NO | | NULL | |
| price | float | YES | | NULL | |
| quantity | int | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
invoices表：
主键自增长：primary key auto_increment
外键关联到orders表的id列：,foreign key(order_id) references orders(id) 注意用逗号分隔后再开始写外键关联
日期型：date
要求数据大于0：check(数据>0)
mysql> create table invoices(number int primary key auto_increment, order_id int ,foreign key(order_id) references orders(id) ,in_date date,total_amount float check(total_amount>0));
Query OK, 0 rows affected (0.03 sec)
mysql> desc invoices;
+--------------+-------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------+------+-----+---------+----------------+
| number | int | NO | PRI | NULL | auto_increment |
| order_id | int | YES | MUL | NULL | |
| in_date | date | YES | | NULL | |
| total_amount | float | YES | | NULL | |
+--------------+-------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
3张表如下:
mysql> show tables;
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees |
| invoices |
| orders |
+-------------------------+
3 rows in set (0.01 sec)
1.16练习
准备工作：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb7_openlab |
| mydb_temp1 |
| mydbx_temp3 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
14 rows in set (0.00 sec)
mysql> create database mydb8_work;
Query OK, 1 row affected (0.01 sec)
mysql> use mydb8_work;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb8_work |
+------------+
1 row in set (0.00 sec)
插入数据：
mysql> insert into t_worker values(101,1001,'2015-5-4',7500.00,'群众','张春燕','1990-7
-1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_worker values(101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2-8');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_worker values(102,1003,'2008-1-4',10500.00,'党员','王博涵','1983-
6-8');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_worker values(102,1004,'2016-10-10',5500.00,'群众','赵小军','1994-9-5');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_worker values(102,1005,'2014-4-1',8800.00,'党员','钱有财','1992-12-30');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_worker values(103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9
-2');
Query OK, 1 row affected (0.02 sec)
完成查询：
1.显示所有职工基本信息
select * from t_worker
(1)、显示 所有 职工 的基本信息。
mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages | politics | name | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
| 101 | 1001 | 2015-05-04 | 7500.00 | 群众 | 张春燕 | 1990-07-01 |
| 101 | 1002 | 2019-02-06 | 5200.00 | 团员 | 李名博 | 1997-02-08 |
| 102 | 1003 | 2008-01-04 | 10500.00 | 党员 | 王博涵 | 1983-06-08 |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵小军 | 1994-09-05 |
| 102 | 1005 | 2014-04-01 | 8800.00 | 党员 | 钱有财 | 1992-12-30 |
| 103 | 1006 | 2019-05-05 | 5500.00 | 党员 | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
2.查询所有职工所属部门的部门号，不显示重复的部门号
select distinct department_id from t_worker
(2)、查询所有职工所属部门的部门号，不显示重复的部门号。
去重：distinct
mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages | politics | name | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
| 101 | 1001 | 2015-05-04 | 7500.00 | 群众 | 张春燕 | 1990-07-01 |
| 101 | 1002 | 2019-02-06 | 5200.00 | 团员 | 李名博 | 1997-02-08 |
| 102 | 1003 | 2008-01-04 | 10500.00 | 党员 | 王博涵 | 1983-06-08 |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵小军 | 1994-09-05 |
| 102 | 1005 | 2014-04-01 | 8800.00 | 党员 | 钱有财 | 1992-12-30 |
| 103 | 1006 | 2019-05-05 | 5500.00 | 党员 | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
mysql> select distinct department_id from t_worker;
+---------------+
| department_id |
+---------------+
| 101 |
| 102 |
| 103 |
+---------------+
3 rows in set (0.00 sec)
3.求出所有职工的人数
select count(worker_id) from t_worker
(3)、求出所有职工的人数。
聚合函数 - count（统计数量）
select 聚合函数(字段列表) from 表名
有主键count(worker_id)，不然就用count(1)
mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages | politics | name | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
| 101 | 1001 | 2015-05-04 | 7500.00 | 群众 | 张春燕 | 1990-07-01 |
| 101 | 1002 | 2019-02-06 | 5200.00 | 团员 | 李名博 | 1997-02-08 |
| 102 | 1003 | 2008-01-04 | 10500.00 | 党员 | 王博涵 | 1983-06-08 |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵小军 | 1994-09-05 |
| 102 | 1005 | 2014-04-01 | 8800.00 | 党员 | 钱有财 | 1992-12-30 |
| 103 | 1006 | 2019-05-05 | 5500.00 | 党员 | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
mysql> select count(worker_id) from t_worker;
+------------------+
| count(worker_id) |
+------------------+
| 6 |
+------------------+
1 row in set (0.03 sec)
4.列出最高工和最低工资
select max(wages) '最高工资' , min(wages)'最低工资' from t_worker
(4)、列出最高工和最低工资。
聚合函数：
mysql> select max(wages) '最高工资' , min(wages)'最低工资' from t_worker;
+----------+----------+
| 最高工资 | 最低工资 |
+----------+----------+
| 10500.00 | 5200.00 |
+----------+----------+
1 row in set (0.00 sec)
5.列出职工的平均工资和总工资
select round(avg(wages),2) '平均工资',sum(wages)'总工资' from t_worker
(5)、列出职工的平均工资和总工资。
mysql> select round(avg(wages),2) '平均工资',sum(wages)'总工资' from t_worker;
+----------+----------+
| 平均工资 | 总工资 |
+----------+----------+
| 7166.67 | 43000.00 |
+----------+----------+
1 row in set (0.01 sec)
6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表
create table t_workdate select worker_id,name,worker_date from t_worker
(6)、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
mysql> create table t_workdate select worker_id,name,worker_date from t_worker;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t_workdate;
+-----------+--------+-------------+
| worker_id | name | worker_date |
+-----------+--------+-------------+
| 1001 | 张春燕 | 2015-05-04 |
| 1002 | 李名博 | 2019-02-06 |
| 1003 | 王博涵 | 2008-01-04 |
| 1004 | 赵小军 | 2016-10-10 |
| 1005 | 钱有财 | 2014-04-01 |
| 1006 | 孙菲菲 | 2019-05-05 |
+-----------+--------+-------------+
6 rows in set (0.00 sec)
7. 显示所有党员的年龄
select year(now())-year(borth_date) '年龄' from t_worker where politics='党员'
(7)、显示所有党员的年龄。
mysql> select year(now())-year(borth_date) '年龄' from t_worker where politics='党员';
+------+
| 年龄 |
+------+
| 42 |
| 33 |
| 29 |
+------+
3 rows in set (0.01 sec)
使表格更加完整：
mysql> select name'姓名',politics'政治面貌',year(now())-year(borth_date) '年龄' from t_worker where politics='党员';
+--------+----------+------+
| 姓名 | 政治面貌 | 年龄 |
+--------+----------+------+
| 王博涵 | 党员 | 42 |
| 钱有财 | 党员 | 33 |
| 孙菲菲 | 党员 | 29 |
+--------+----------+------+
3 rows in set (0.00 sec)
8.列出工资在4000-8000之间的所有职工姓名
select name from t_worker where wages>=4000 and wages<=8000
(8)、列出工资在4000-8000之间的所有职工姓名
mysql> select name from t_worker where wages>=4000 and wages<=8000;
+--------+
| name |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+
4 rows in set (0.00 sec)
9.列出所有孙姓和李姓的职工姓名
select name '姓名' from t_worker where name like '孙%' or name like '李%'
(9)、列出所有孙姓和李姓的职工姓名。
mysql> select name '姓名' from t_worker where name like '孙%' or name like '李%' ;
+--------+
| 姓名 |
+--------+
| 李名博 |
| 孙菲菲 |
+--------+
2 rows in set (0.00 sec)
10.列出所有部门号为102和103日不是党员的职工号、姓名
select worker_id,name from t_worker where (department_id=102 or department_id=103) and politics != '党员'
(10)、列出所有部门号为102和103日不是党员的职工号、姓名。
mysql> select worker_id,name from t_worker where (department_id=102 or department_id=103) and politics != '党员';
+-----------+--------+
| worker_id | name |
+-----------+--------+
| 1004 | 赵小军 |
+-----------+--------+
1 row in set (0.00 sec)
11.将职工表t_worker中的职工按出生的先后顺序排序
select * from t_worker order by borth_date
(11)、将职工表t_worker中的职工按出生的先后顺序排序。
升序：asc(ascend)，默认 降序：desc (descend)
mysql> select * from t_worker order by borth_date;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages | politics | name | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
| 102 | 1003 | 2008-01-04 | 10500.00 | 党员 | 王博涵 | 1983-06-08 |
| 101 | 1001 | 2015-05-04 | 7500.00 | 群众 | 张春燕 | 1990-07-01 |
| 102 | 1005 | 2014-04-01 | 8800.00 | 党员 | 钱有财 | 1992-12-30 |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵小军 | 1994-09-05 |
| 103 | 1006 | 2019-05-05 | 5500.00 | 党员 | 孙菲菲 | 1996-09-02 |
| 101 | 1002 | 2019-02-06 | 5200.00 | 团员 | 李名博 | 1997-02-08 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)
12.显示工资最高的前3名职工的职工号和姓名
select worker_id,name from t_worker order by wages desc limit 3
(12)、显示工资最高的前3名职工的职工号和姓名。
分页查询 - limit 记录数 # 从第一条记录开始显示几条记录
mysql> select worker_id,name from t_worker order by wages desc limit 3;
+-----------+--------+
| worker_id | name |
+-----------+--------+
| 1003 | 王博涵 |
| 1005 | 钱有财 |
| 1001 | 张春燕 |
+-----------+--------+
3 rows in set (0.00 sec)
13.求出各部门党员的人数
select department_id '部门号',count(worker_id) '党员人数' from t_worker where politics = '党员' group by department_id
(13)、求出各部门党员的人数。
group by ：分组
mysql> select department_id '部门号',count(worker_id) '党员人数' from t_worker where politics = '党员' group by department_id;
+--------+----------+
| 部门号 | 党员人数 |
+--------+----------+
| 102 | 2 |
| 103 | 1 |
+--------+----------+
2 rows in set (0.00 sec)
14.统计各部门的工资和平均工资并保留2位小数
select department_id '部门号',sum(wages)'部门工资总和'from t_worker group by department_id
(14)、统计各部门的工资和平均工资并保留2位小数
mysql> select department_id '部门号',sum(wages)'部门工资总和'from t_worker group by department_id;
+--------+--------------+
| 部门号 | 部门工资总和 |
+--------+--------------+
| 101 | 12700.00 |
| 102 | 24800.00 |
| 103 | 5500.00 |
+--------+--------------+
3 rows in set (0.00 sec)
15.列出总人数大于等于3的部门号和总人数
select department_id '部门号',count(worker_id)'人数'from t_worker group by department_id having count(worker_id)>=3
(15)、列出总人数大于等于3的部门号和总人数。
mysql> select department_id '部门号',count(worker_id)'人数'from t_worker group by department_id having count(worker_id)>=3;
+--------+------+
| 部门号 | 人数 |
+--------+------+
| 102 | 3 |
+--------+------+
1 row in set (0.00 sec)
1.18练习
创建数据库mydb11_stu并使用数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb10_city |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb7_openlab |
| mydb8_work |
| mydb9_stusys |
| mydb_temp1 |
| mydbx_temp3 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
17 rows in set (0.00 sec)
mysql> create database mydb11_stu;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb10_city |
| mydb11_stu |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb7_openlab |
| mydb8_work |
| mydb9_stusys |
| mydb_temp1 |
| mydbx_temp3 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
18 rows in set (0.00 sec)
mysql> use mydb11_stu;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb11_stu |
+------------+
1 row in set (0.00 sec)
创建student表
mysql> create table student(id int(10) not null unique primary key, name varchar(20) not null , sex varchar(4) , birth year , department varchar(20) , address varchar(50));
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| birth | year | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
创建score表
mysql> create table score(id int(10) not null unique primary key auto_increment, stu_id int(10) not null, c_name varchar(20),grade int(10));
Query OK, 0 rows affected, 3 warnings (0.03 sec)
mysql> desc score;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| stu_id | int | NO | | NULL | |
| c_name | varchar(20) | YES | | NULL | |
| grade | int | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
插入数据
(1) 向student表插入记录如下:
mysql> insert student values(901,'张三丰','男',2002,'计算机系','北京市海淀区');
Query OK, 1 row affected (0.01 sec)
mysql> insert student values(902,'周全有','男',2000,'中文系','北京市昌平区');
Query OK, 1 row affected (0.02 sec)
mysql> insert student values(903,'张思维','女',2003,'中文系','湖南省永州区');
Query OK, 1 row affected (0.01 sec)
mysql> insert student values(904,'李广昌','男',1999,'英语系','辽宁省皋新市');
Query OK, 1 row affected (0.02 sec)
mysql> insert student values(905,'王翰','男',2004,'英语系','福建省厦门市');
Query OK, 1 row affected (0.01 sec)
mysql> insert student values(906,'王心凌','女',1998,'计算机系','湖南省衡阳市');
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
(2)向score表插入记录如下:
mysql> insert into score values(null,901,'计算机',98);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score values(null,901,'英语',80);
Query OK, 1 row affected (0.02 sec)
mysql> insert into score values(null,902,'计算机',65);
Query OK, 1 row affected (0.02 sec)
mysql> insert into score values(null,902,'中文',88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values(null,903,'中文',95);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values(null,904,'计算机',70);
Query OK, 1 row affected (0.02 sec)
mysql> insert into score values(null,904,'英语',92);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values(null,905,'英语',94);
Query OK, 1 row affected (0.02 sec)
mysql> insert into score values(null,906,'计算机',49);
Query OK, 1 row affected (0.02 sec)
mysql> insert into score values(null,906,'英语',83);
Query OK, 1 row affected (0.02 sec)
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
查询
1.分别查询student表和score表的所有记录
select * from student || select * from score
(1).分别查询student表和score表的所有记录
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
2.查询 student表 的第2条到5条记录
select * from student limit 1,4
(2).查询 student表 的第2条到5条记录
考察内容：分页
limit 初始位置，记录数 # 从第几条记录开始显示，显示几条，注意：第一条记录的位置是0
limit 记录数 # 从第一条记录开始显示几条记录
limit 记录数 offset 初始位置 # 从第几条记录开始显示之后的几条记录 （偏移量方式）
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from student limit 1,4;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
+-----+--------+------+-------+------------+--------------+
4 rows in set (0.00 sec)
limit 记录数 offset 初始位置 # 从第几条记录开始显示之后的几条记录 （偏移量方式）
#方法二
mysql> select * from student limit 4 offset 1;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
+-----+--------+------+-------+------------+--------------+
4 rows in set (0.00 sec)
3.从student表中查询计算机系和英语系的学生的信息
select * from student limit 1,4
(3).从student表中查询计算机系和英语系的学生的信息
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from student where department='计算机系'or department='英语系';
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from student where department in('计算机系','英语系');
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
4 rows in set (0.00 sec)
4.从student表中查询年龄小于22岁的学生信息
select * from student where year(now())-birth<22
(4).从student表中查询年龄小于22岁的学生信息
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from student where year(now())-birth<22;
+-----+------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+------+------+-------+------------+--------------+
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
+-----+------+------+-------+------------+--------------+
1 row in set (0.00 sec)
5.从student表中查询每个院系有多少人
select department as '院系', count(id) as '人数' from student where id group by department
(5).从student表中查询每个院系有多少人
归类院系
分组
group by department统计人数
聚合函数 - count（统计数量）
select 聚合函数(字段列表) from 表名
表格呈现：
院系、人数
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| birth | year | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select department as '院系', count(id) as '人数' from student where id group by department;
+----------+------+
| 院系 | 人数 |
+----------+------+
| 计算机系 | 2 |
| 中文系 | 2 |
| 英语系 | 2 |
+----------+------+
3 rows in set (0.00 sec)
- 统计函数count（主键）
- 分组查询（各院系）
6.从score表中查询每个科目的最高分
select c_name as '科目', max(grade) as '最高分' from score where grade group by c_name
(6).从score表中查询每个科目的最高分
group by：分组
分组 (每个科目) group by c_name
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
mysql> select c_name as '科目', max(grade) as '最高分' from score where grade group by c_name;
+--------+--------+
| 科目 | 最高分 |
+--------+--------+
| 计算机 | 98 |
| 英语 | 94 |
| 中文 | 95 |
+--------+--------+
3 rows in set (0.00 sec)
7.查询李广昌的考试科目(cname)和考试成绩(grade)
select distinct score.c_name '考试科目',score.grade '考试成绩' from student,score where score.stu_id=(select id from student where name='李广昌' )
select c_name,grade from student join score on student.id=score.stu_id where student.name='李广昌'
select name,c_name,grade from student join score on student.id=score.stu_id where student.name='李广昌'
(7).查询李广昌的考试科目(cname)和考试成绩(grade)
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
李广昌 - student 表
c_name 、grade - score 表
- 通过’李广昌‘这个名字去对应上 id
mysql> select name,id from student where name='李广昌' ;
+--------+-----+
| name | id |
+--------+-----+
| 李广昌 | 904 |
+--------+-----+
1 row in set (0.00 sec)
mysql> select id from student where name='李广昌' ;
+-----+
| id |
+-----+
| 904 |
+-----+
1 row in set (0.00 sec)
- 通过 id （student）即 stu_id（score）去查询 c_name 、grade
mysql> select distinct score.c_name '考试科目',score.grade '考试成绩' from student,score where score.stu_id=(select id from student where name='李广昌' );
+----------+----------+
| 考试科目 | 考试成绩 |
+----------+----------+
| 英语 | 92 |
| 计算机 | 70 |
+----------+----------+
2 rows in set (0.00 sec)
或者：
mysql> select c_name,grade from student join score on student.id=score.stu_id where student.name='李广昌';
+--------+-------+
| c_name | grade |
+--------+-------+
| 计算机 | 70 |
| 英语 | 92 |
+--------+-------+
2 rows in set (0.00 sec)
或者：
- 多表关联
select name,c_name,grade from student join score on student.id=score.stu_id;e
- 查询条件
where student.name=‘李广昌’;
mysql> select name,c_name,grade from student join score on student.id=score.stu_id where student.name='李广昌';
+--------+--------+-------+
| name | c_name | grade |
+--------+--------+-------+
| 李广昌 | 计算机 | 70 |
| 李广昌 | 英语 | 92 |
+--------+--------+-------+
2 rows in set (0.00 sec)
8.用连接的方式查询所有学生的信息和考试信息
select * from student left outer join score on student.id = score.stu_id union select * from student right outer join score on student.id = score.stu_id
select * from student,score where student.id=score.stu_id
select student.*,c_name,grade from student join score on student.id=score.stu_id
(8).用连接的方式查询所有学生的信息和考试信息
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
union： 会自动压缩多个结果集合中的重复结果，对两个结果集进行并集操作，不包括重复行
mysql> select * from student left outer join score on student.id = score.stu_id union select * from student right outer join score on student.id = score.stu_id;
+------+--------+------+-------+------------+--------------+------+--------+--------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+------+--------+------+-------+------------+--------------+------+--------+--------+-------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 2 | 901 | 英语 | 80 |
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 1 | 901 | 计算机 | 98 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 4 | 902 | 中文 | 88 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 | 5 | 903 | 中文 | 95 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 7 | 904 | 英语 | 92 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 6 | 904 | 计算机 | 70 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 | 8 | 905 | 英语 | 94 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 10 | 906 | 英语 | 83 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 49 |
+------+--------+------+-------+------------+--------------+------+--------+--------+-------+
10 rows in set (0.00 sec)
mysql> select * from student,score where student.id=score.stu_id;
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 1 | 901 | 计算机 | 98 |
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 2 | 901 | 英语 | 80 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 4 | 902 | 中文 | 88 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 | 5 | 903 | 中文 | 95 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 6 | 904 | 计算机 | 70 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 7 | 904 | 英语 | 92 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 | 8 | 905 | 英语 | 94 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 49 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 10 | 906 | 英语 | 83 |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
10 rows in set (0.00 sec)
mysql> select student.*,c_name,grade from student join score on student.id=score.stu_id;
+-----+--------+------+-------+------------+--------------+--------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+--------+------+-------+------------+--------------+--------+-------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 中文 | 88 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 | 中文 | 95 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 计算机 | 70 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 英语 | 92 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 | 英语 | 94 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 计算机 | 49 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 英语 | 83 |
+-----+--------+------+-------+------------+--------------+--------+-------+
10 rows in set (0.00 sec)
9.计算每个学生的总成绩
select name '姓名',sum(grade) '总成绩' from student,score where student.id=score.stu_id group by name
(9).计算每个学生的总成绩
- 两表需要连接起来 select * from student,score where student.id=score.stu_id;
- 计算每个人的总成绩 sum(grade)
- 呈现表格：学生、总成绩（分组group by name）
mysql> select * from student;
+-----+--------+------+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+------+-------+------------+--------------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 49 |
| 10 | 906 | 英语 | 83 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)
mysql> select name '姓名',sum(grade) '总成绩' from student,score where student.id=score.stu_id group by name;
+--------+--------+
| 姓名 | 总成绩 |
+--------+--------+
| 张三丰 | 178 |
| 周全有 | 153 |
| 张思维 | 95 |
| 李广昌 | 162 |
| 王翰 | 94 |
| 王心凌 | 132 |
+--------+--------+
6 rows in set (0.00 sec)
mysql> select student.id'编号',name'姓名',sum(grade)'总成绩' from student join score on student.id
=score.stu_id group by student.id;
+------+--------+--------+
| 编号 | 姓名 | 总成绩 |
+------+--------+--------+
| 901 | 张三丰 | 178 |
| 902 | 周全有 | 153 |
| 903 | 张思维 | 95 |
| 904 | 李广昌 | 162 |
| 905 | 王翰 | 94 |
| 906 | 王心凌 | 132 |
+------+--------+--------+
6 rows in set (0.00 sec)
10.计算每个考试科目的平均成绩
select c_name '科目',avg(grade) '科目平均成绩' from score where grade group by c_name
select c_name,round(avg(grade),2) from student left join score on student.id=score.stu_id group by c_name
select c_name'考试科目', round(avg(grade),2)'平均成绩' from score group by c_name
(10).计算每个考试科目的平均成绩
mysql> select c_name '科目',avg(grade) '科目平均成绩' from score where grade group by c_name;
+--------+--------------+
| 科目 | 科目平均成绩 |
+--------+--------------+
| 计算机 | 70.5000 |
| 英语 | 87.2500 |
| 中文 | 91.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
mysql> select c_name,round(avg(grade),2) from student left join score on student.id=score.stu_id group by c_name;
+--------+---------------------+
| c_name | round(avg(grade),2) |
+--------+---------------------+
| 英语 | 87.25 |
| 计算机 | 70.50 |
| 中文 | 91.50 |
+--------+---------------------+
3 rows in set (0.00 sec)
mysql> select c_name'考试科目', round(avg(grade),2)'平均成绩' from score group by c_name;
+----------+----------+
| 考试科目 | 平均成绩 |
+----------+----------+
| 计算机 | 70.50 |
| 英语 | 87.25 |
| 中文 | 91.50 |
+----------+----------+
3 rows in set (0.00 sec)
11.查询计算机成绩低于95的学生信息
(11).查询计算机成绩低于95的学生信息
mysql> select * from student as st join score as sc on st.id=sc.stu_id where sc.c_name = '计算机' and sc.grade < 95; +-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 6 | 904 | 计算机 | 70 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 49 |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
3 rows in set (0.00 sec)
mysql> select st.id,st.name,st.birth,st.department,st.address from student as st join score as sc on st.id=sc.stu_id where sc.c_name = '计算机' and sc.grade < 95;
+-----+--------+-------+------------+--------------+
| id | name | birth | department | address |
+-----+--------+-------+------------+--------------+
| 902 | 周全有 | 2000 | 中文系 | 北京市昌平区 |
| 904 | 李广昌 | 1999 | 英语系 | 辽宁省皋新市 |
| 906 | 王心凌 | 1998 | 计算机系 | 湖南省衡阳市 |
+-----+--------+-------+------------+--------------+
3 rows in set (0.00 sec)
12.将计算机考试成绩按从高到低进行排序
select student.*,c_name,grade from student join score on student.id=score.stu_id where c_name='计算机' order by grade desc
(12).将计算机考试成绩按从高到低进行排序
mysql> select * from score where c_name='计算机' order by grade desc;
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 6 | 904 | 计算机 | 70 |
| 3 | 902 | 计算机 | 65 |
| 9 | 906 | 计算机 | 49 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)
mysql> select student.*,c_name,grade from student join score on student.id=score.stu_id where c_name='计算机' order by grade desc;
+-----+--------+------+-------+------------+--------------+--------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+--------+------+-------+------------+--------------+--------+-------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 计算机 | 70 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 计算机 | 49 |
+-----+--------+------+-------+------------+--------------+--------+-------+
4 rows in set (0.00 sec)
13.从student表和score表中 查询出学生的学号，然后合并查询结果
select id from student union select stu_id from score
(13).从student表和score表中 查询出学生的学号，然后合并查询结果
mysql> select score.stu_id,student.name,student.sex,student.birth,student.department,student.address,score.id,score.c_name,score.grade from stu
dent,score where student.id=score.stu_id;
+--------+--------+------+-------+------------+--------------+----+--------+-------+
| stu_id | name | sex | birth | department | address | id | c_name | grade |
+--------+--------+------+-------+------------+--------------+----+--------+-------+
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 1 | 计算机 | 98 |
| 901 | 张三丰 | 男 | 2002 | 计算机系 | 北京市海淀区 | 2 | 英语 | 80 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 3 | 计算机 | 65 |
| 902 | 周全有 | 男 | 2000 | 中文系 | 北京市昌平区 | 4 | 中文 | 88 |
| 903 | 张思维 | 女 | 2003 | 中文系 | 湖南省永州区 | 5 | 中文 | 95 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 6 | 计算机 | 70 |
| 904 | 李广昌 | 男 | 1999 | 英语系 | 辽宁省皋新市 | 7 | 英语 | 92 |
| 905 | 王翰 | 男 | 2004 | 英语系 | 福建省厦门市 | 8 | 英语 | 94 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 9 | 计算机 | 49 |
| 906 | 王心凌 | 女 | 1998 | 计算机系 | 湖南省衡阳市 | 10 | 英语 | 83 |
+--------+--------+------+-------+------------+--------------+----+--------+-------+
10 rows in set (0.00 sec)
mysql> select id from student union select stu_id from score;
+-----+
| id |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
+-----+
6 rows in set (0.00 sec)
14.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select name,department,c_name,grade from student join score on student.id=score.stu_id where name like '张%' or name like '王%'
(14).查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> select name,department,c_name,grade from student join score on student.id=score.stu_id where name like '张%' or name like '王%';
+--------+------------+--------+-------+
| name | department | c_name | grade |
+--------+------------+--------+-------+
| 张三丰 | 计算机系 | 计算机 | 98 |
| 张三丰 | 计算机系 | 英语 | 80 |
| 张思维 | 中文系 | 中文 | 95 |
| 王翰 | 英语系 | 英语 | 94 |
| 王心凌 | 计算机系 | 计算机 | 49 |
| 王心凌 | 计算机系 | 英语 | 83 |
+--------+------------+--------+-------+
6 rows in set (0.00 sec)
15.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
select st.name,year(now()-birth) as age,st.department,sc.c_name,sc.grade from student st join score sc on st.id = sc.stu_id where st.address like '湖南%'
(15).查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql> select st.name,year(now()-birth) as age,st.department,sc.c_name,sc.grade from student st join score sc on st.id = sc.stu_id where st.address like '湖南%';
+--------+------+------------+--------+-------+
| name | age | department | c_name | grade |
+--------+------+------------+--------+-------+
| 张思维 | 2025 | 中文系 | 中文 | 95 |
| 王心凌 | 2025 | 计算机系 | 计算机 | 49 |
| 王心凌 | 2025 | 计算机系 | 英语 | 83 |
+--------+------+------------+--------+-------+
3 rows in set (0.00 sec)
2.7 练习
mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.02 sec)
mysql> use mydb15_indexstu;
Database changed
建表：
mysql> create table student(sno int primary key auto_increment , sname varchar(30) not null unique , ssex varchar(2) check (ssex='男' or ssex = '女') not null , sage int not null , sdept varchar(10) default '计算机' not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(30) | NO | UNI | NULL | |
| ssex | varchar(2) | NO | | NULL | |
| sage | int | NO | | NULL | |
| sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> create table course(cno int primary key not null , cname varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table sc(sno int not null , cno varchar(10) primary key not null , score int not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | int | NO | | NULL | |
| cno | varchar(10) | NO | PRI | NULL | |
| score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
alter table student modify sage smallint;
- 修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
mysql> #修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
mysql> alter table student modify sage smallint;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(30) | NO | UNI | NULL | |
| ssex | varchar(2) | NO | | NULL | |
| sage | smallint | YES | | NULL | |
| sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
2.为Course表中Cno 课程号字段设置索引,并查看索引
设置索引：
1.使用create语句在已经存在的表上创建索引
create index index1 on course(cno)
创建的索引名称 对哪个字段进行索引创建（即指定字段名）
create index indexname on tablename (columnName(length) [asc|desc]);
asc | desc：指定索引的排序方式，asc是升序，desc是降序，默认asc。
2.使用alter table语句来添加索引
alter table course add index index1(course(cno))
alter table tableName add index indexname(columnname(length) [asc|desc]);
查看索引：
show create table course\G
show index from course;
或者
show index from course\G
- 为Course表中Cno 课程号字段设置索引,并查看索引
mysql> #为Course表中Cno 课程号字段设置索引,并查看索引
mysql> create index index1 on course(cno);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table course\G
*************************** 1. row ***************************
Table: course
Create Table: CREATE TABLE `course` (
`cno` int NOT NULL,
`cname` varchar(20) NOT NULL,
PRIMARY KEY (`cno`),
KEY `index1` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> show index from course;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| course | 1 | index1 | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)
mysql> show index from course\G
*************************** 1. row ***************************
Table: course
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: course
Non_unique: 1
Key_name: index1
Seq_in_index: 1
Column_name: cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
alter table sc add index sc_index(sno,cno asc)
- 为SC表建立按学号(sno)和课程号(cno) 组合的 升序 的 主键索引，索引名为SC_INDEX
mysql> #为SC表建立按学号(sno)和课程号(cno) 组合的 升序 的 主键索引，索引名为SC_INDEX
mysql> alter table sc add index sc_index(sno,cno asc);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from sc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sc | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sc | 1 | sc_index | 1 | sno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sc | 1 | sc_index | 2 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.03 sec)
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | int | NO | | NULL | |
| cno | varchar(10) | NO | PRI | NULL | |
| score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> create unique index SC_INDEX on sc(sno asc,cno asc);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from sc\G
*************************** 1. row ***************************
Table: sc
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: sc
Non_unique: 0
Key_name: SC_INDEX
Seq_in_index: 1
Column_name: sno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: sc
Non_unique: 0
Key_name: SC_INDEX
Seq_in_index: 2
Column_name: cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.02 sec)
4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩
create or replace view stu_info as select sname,ssex,cname,score from student join sc on student.sno=sc.sno join course on sc.cno=course.cno;
- 创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩
mysql> #创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | int | NO | MUL | NULL | |
| cno | varchar(10) | NO | PRI | NULL | |
| score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(30) | NO | UNI | NULL | |
| ssex | varchar(2) | NO | | NULL | |
| sage | smallint | YES | | NULL | |
| sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> create or replace view stu_info as select sname,ssex,cname,score from student join sc on student.sno=sc.sno join course on sc.cno=course.cno;
Query OK, 0 rows affected (0.02 sec)
mysql> show create view stu_info;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| stu_info | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_info` AS select `student`.`sname` AS `sname`,`student`.`ssex` AS `ssex`,`course`.`cname` AS `cname`,`sc`.`score` AS `score` from ((`student` join `sc` on((`student`.`sno` = `sc`.`sno`))) join `course` on((`sc`.`cno` = `course`.`cno`))) | gbk | gbk_chinese_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> create view stu_info1 as select student.sname as '姓名',student.ssex as '性别',course.cn
ame as '课程名',sc.score as '成绩' from student , course,sc;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from stu_info1;
Empty set (0.00 sec)
5.删除所有索引
drop index 索引名 on 表名
- 删除所有索引
mysql> show index from sc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sc | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sc | 1 | sc_index | 1 | sno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sc | 1 | sc_index | 2 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
mysql> drop index sc_index on sc;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from sc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sc | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql> show index from course;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| course | 1 | index1 | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
mysql> drop index index1 on course;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from course;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course | 0 | PRIMARY | 1 | cno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | sno | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| student | 0 | sname | 1 | sname | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)
2.8 练习
mysql> create table goods(gid char(8) primary key, name varchar(10), price decimal(8,2), num int);
Query OK, 0 rows affected (0.02 sec)
mysql> desc goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid | char(8) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| num | int | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> create table orders(oid int primary key auto_increment, gid char(10) not null, name varchar(10), price decimal(8,2), onum int, otime date);
Query OK, 0 rows affected (0.03 sec)
mysql> desc orders;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| oid | int | NO | PRI | NULL | auto_increment |
| gid | char(10) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| onum | int | YES | | NULL | |
| otime | date | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> insert into goods values('A0001','橡皮',2.5,100),('B0001','小楷本',2.8,210),('C0001','铅笔',1.2,120),('D0001','计算器',28,20);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 100 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
- 触发器：
- 建立触发器（只有一个执行语句）：create trigger 触发器名称 before/after 触发事件 on 表名 for each row 执行语句;
- 多个执行语句：
create trigger 触发器名称 before/after 触发事件 on 表名 for each row 执行语句
begin
Tab执行语句列表
end;
触发器时机：before / after
on <表名称> ：在哪张表上建立触发器
触发事件：insert / update / delete
1.建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试
create trigger 1_after_insert_trigger after insert on orders for each row update goods set num=num-new.onum where gid = new.gid;
建立触发器，订单表中增加订单数量 后，商品表 商品数量 同步减少对应的商品订单出数量,并测试
mysql> create trigger 1_after_insert_trigger after insert on orders for each row update goods set num=num-new.onum where gid = new.gid;
Query OK, 0 rows affected (0.02 sec)
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: 1_after_insert_trigger
Event: INSERT
Table: orders
Statement: update goods set num=num-new.onum where gid = new.gid
Timing: AFTER
Created: 2025-02-08 21:03:37.98
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8mb4_0900_ai_ci
mysql> select * from orders;
Empty set (0.02 sec)
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 100 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
mysql> insert into orders values(1,'A0001','橡皮','2.5','10',now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from orders;
+-----+-------+------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+------+-------+------+------------+
| 1 | A0001 | 橡皮 | 2.50 | 10 | 2025-02-08 |
+-----+-------+------+-------+------+------------+
1 row in set (0.00 sec)
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 90 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
2.建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量
create trigger 2_after_update_trigger after update on orders for each row update goods set num=num+old.onum where gid = new.gid;
建立触发器，实现功能:客户取消订单(orders)，恢复商品表(goods)对应商品的数量
mysql> create trigger 2_after_update_trigger after update on orders for each row update goods set num=num+old.onum where gid = new.gid;
Query OK, 0 rows affected (0.02 sec)
mysql> delete from orders where gid='A0001';
Query OK, 1 row affected (0.02 sec)
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 90 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
mysql> select * from orders;
Empty set (0.00 sec)
3.建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新
create trigger 3_after_update_trigger after update on orders for each row update goods set num=num-new.onum+old.onum where gid = new.gid;
建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新
mysql> create trigger 3_after_update_trigger after update on orders for each row update goods set num=num-new.onum+old.onum where gid = new.gid;
Query OK, 0 rows affected (0.12 sec)
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 90 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.01 sec)
mysql> select * from orders;
Empty set (0.00 sec)
mysql> insert into orders values(1,'A0001','橡皮',2.5,32,now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from goods;
+-------+--------+-------+------+
| gid | name | price | num |
+-------+--------+-------+------+
| A0001 | 橡皮 | 2.50 | 58 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
mysql> select * from orders;
+-----+-------+------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+------+-------+------+------------+
| 1 | A0001 | 橡皮 | 2.50 | 32 | 2025-02-09 |
+-----+-------+------+-------+------+------------+
1 row in set (0.00 sec)
- 存储过程：
建立存储过程：- delimiter 自定义结束符号eg.//
- create procedure 存储过程名称([IN | OUT | INOUT]参数名 类型…)
begin
TabSQL语句
end 自定义结束符号// - delimiter ;
IN ：输入参数
OUT ：输出参数
INOUT ：输入输出参数
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb10_city |
| mydb11_stu |
| mydb12_journal |
| mydb13_indexdb |
| mydb14_job |
| mydb15_indexstu |
| mydb16_trigger |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb7_openlab |
| mydb8_work |
| mydb9_stusys |
| mydb_temp1 |
| mydbx_temp3 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
23 rows in set (0.01 sec)
mysql> use mydb7_openlab
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_mydb7_openlab |
+-------------------------+
| dept |
| emp |
| emp_new |
| user |
+-------------------------+
4 rows in set (0.01 sec)
4.创建提取emp_new表所有员工姓名和工资的存储过程s1
mysql> delimiter //
mysql> create procedure s1()
-> begin
-> select name,incoming from emp_new;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s1();
创建提取emp_new表 所有员工姓名和工资 的存储过程s1
mysql> select * from emp_new;
+------+------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+------+------+----------------+----------+-------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.02 sec)
mysql> delimiter //
mysql> create procedure s1()
-> begin
-> select name,incoming from emp_new;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s1();
+------+----------+
| name | incoming |
+------+----------+
| 张三 | 4000 |
| 李四 | 3500 |
| 王五 | 2000 |
| 赵六 | 7500 |
| 荣七 | 8500 |
| 牛八 | 7300 |
+------+----------+
6 rows in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
5.创建存储过程s2，实现输入员工姓名后返回员工的年龄
mysql> delimiter //
mysql> create procedure s2(in s_name varchar(11) , out s_age int)
-> begin
-> select age into s_age from emp_new where name=s_name;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s2('赵六',@age);
Query OK, 1 row affected (0.01 sec)
mysql> select @age;
创建存储过程s2，实现输入员工姓名后返回员工的年龄（ in out ）
mysql> select * from emp_new;
+------+------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+------+------+----------------+----------+-------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.00 sec)
mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid | int | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int | YES | | NULL | |
| worktime_start | date | YES | | NULL | |
| incoming | int | YES | | NULL | |
| dept2 | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> delimiter //
mysql> create procedure s2(in s_name varchar(11) , out s_age int)
-> begin
-> select age into s_age from emp_new where name=s_name;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s2('赵六',@age);
Query OK, 1 row affected (0.01 sec)
mysql> select @age;
+------+
| @age |
+------+
| 57 |
+------+
1 row in set (0.00 sec)
6.创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资
mysql> delimiter //
mysql> create procedure s3(in s3_dept2 int , out avg_incoming int)
-> begin
-> select avg(incoming) into avg_incoming from emp_new where dept2=s3_dept2 group by dept2;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s3(102,@avg_incoming);
Query OK, 1 row affected (0.00 sec)
mysql> select @avg_incoming;
创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资
mysql> select * from emp_new;
+------+------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+------+------+----------------+----------+-------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.00 sec)
mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid | int | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int | YES | | NULL | |
| worktime_start | date | YES | | NULL | |
| incoming | int | YES | | NULL | |
| dept2 | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure s3(in s3_dept2 int , out avg_incoming int)
-> begin
-> select avg(incoming) into avg_incoming from emp_new where dept2=s3_dept2;
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call s3(102,@avg_incoming);
Query OK, 1 row affected (0.00 sec)
mysql> select @avg_incoming;
+---------------+
| @avg_incoming |
+---------------+
| 8000 |
+---------------+
1 row in set (0.00 sec)
二、注意事项
2.1
日期需要以字符串形式添加 eg.‘2000-10-1’
mysql> insert into student2 value(1001 , '李成峰','男','2000-10-1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
+------+--------+------+------------+
1 row in set (0.00 sec)
delete一定要带where，
mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孙德胜 | 男 | 1998-12-31 |
| 1004 | 凤飞飞 | 女 | 2009-03-15 |
| 1005 | 尹志平 | 男 | 1235-09-01 |
+------+--------+------+------------+
5 rows in set (0.00 sec)
mysql> delete from student2 where id=1005; #单条删除
Query OK, 1 row affected (0.02 sec)
mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孙德胜 | 男 | 1998-12-31 |
| 1004 | 凤飞飞 | 女 | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)
delete from student2不加where，删除全表内容，表结构还存在
drop 删除包括表结构
删除之前可开启一个事务，方便回溯
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
开启一个事务，方便回溯
mysql> delete from student2 ;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student2;
Empty set (0.00 sec)
mysql> desc student2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| sex | char(2) | YES | | 女 | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孙德胜 | 男 | 1998-12-31 |
| 1004 | 凤飞飞 | 女 | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)