MYSQL（表数据操作）

概览

MySQL数据操作： DML

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据。

插入数据insert

1. 插入完整数据（顺序插入）
    语法一：
    #INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    #INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    #INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    #INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    #INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

更新数据update

语法：
    #UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE CONDITION;

示例：
    #UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;

删除数据delete

语法：
    #DELETE FROM 表名 WHERE CONITION;

示例：
    #DELETE FROM mysql.user WHERE password=’’;

查询数据 select

单表查询

单表查询的语法及关键字执行的优先级

语法

SELECT DISTINCT 字段1 as 别名1,字段2 as 别名2... FROM 表名 as 表别名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

优先级

from
where
group by
select
distinct   #去重
having
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.执行distinct（去重）

5.将分组的结果进行having过滤

6.将结果按条件排序：order by

7.限制结果的显示条数

简单查询

#查询时新增列
#select name,age,11 from tb12;       #表中没有11这个列，select查的时候，新增11这个列，然后这个列中所有行的数据都是11

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int

#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

建表和数据准备

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

简单查询

where约束

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是80或90或100
4. like 'e%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

#1:单条件查询
    SELECT emp_name FROM employee WHERE post='sale';
        
#2:多条件查询
    SELECT emp_name,salary FROM employee WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT emp_name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;

    SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL;

    SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL;
        
    SELECT emp_name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT emp_name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT emp_name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;

    SELECT emp_name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    #通配符’%’
    SELECT * FROM employee WHERE emp_name LIKE 'eg%';

    #通配符’_’
    SELECT * FROM employee WHERE emp_name LIKE 'al__';

where约束

SQL语句中where 1=1的意义
1、动态SQL拼接
适合多条件搜索，当要构造动态sql语句时为了防止sql语句结构不当,所以加上where 1=1 ，这样SQL语句不会报错
2、查询表结构
优点：数据库开销小
where 1=1是sql语句条件逻辑判断表达式，由于1=1成立，恒为真，该表达式1=1将始终返回"真"。这种写法实际目的是为了获取逻辑值"True",其实诸如2=2, 1+2=3，'中'='中'等之类的写法都可以返回逻辑值"True"，只不过1=1的运算开销更小，故被应用的最普遍

group by 分组

#单独使用GROUP BY关键字分组
    #SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

#GROUP BY关键字和GROUP_CONCAT()函数一起使用
    #SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    #SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

#GROUP BY与聚合函数一起使用
    #select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

分组

强调：

分组类似于去重，distinct也可以去重，但效率不高，不推荐

select num from 表 group by num having max(id) > 10

特别的：group by 必须在where之后，order by之前

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
#count()的时候最好写count(1)或count(主键)，这样查询效率高
示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

having过滤

HAVING与WHERE不一样的地方!!!!!!

#！！！执行优先级从高到低：where > group by > having 

#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
    如果对于聚合函数结果进行二次筛选时，必须使用having

mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)

mysql> select * from emp where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | emp_name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
row in set (0.00 sec)

mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+
rows in set (0.00 sec)

验证

order by查询排序

#按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

#按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee ORDER BY age,salary DESC;

select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

limit限制查询的记录数

示例：
    SELECT * FROM employee ORDER BY salary DESC LIMIT 3;               #默认初始位置为0 
    
    SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
　　
　　select * from 表 limit 5 offset 4    - 从第4行开始的5行

使用正则表达式查询

SELECT * FROM employee WHERE emp_name REGEXP '^ale';

SELECT * FROM employee WHERE emp_name REGEXP 'on$';

SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

小结：对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

多表查询

准备

建表与数据准备

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

表department与employee

多表连接查询

#重点：外链接语法
#SELECT 字段列表 FROM 表1 inner|left|right join 表2 ON 表1.字段 = 表2.字段;

1 交叉连接：不使用任何匹配条件。生成笛卡尔积（了解）

mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  1 | egon       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | egon       | male   |   18 |    200 |  202 | 销售         |
|  1 | egon       | male   |   18 |    200 |  203 | 运营         |
|  2 | alex       | female |   48 |    201 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  2 | alex       | female |   48 |    201 |  202 | 销售         |
|  2 | alex       | female |   48 |    201 |  203 | 运营         |
|  3 | wupeiqi    | male   |   38 |    201 |  200 | 技术         |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  202 | 销售         |
|  3 | wupeiqi    | male   |   38 |    201 |  203 | 运营         |
|  4 | yuanhao    | female |   28 |    202 |  200 | 技术         |
|  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  4 | yuanhao    | female |   28 |    202 |  203 | 运营         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+

2 内连接：只连接匹配的行

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 
+----+-----------+------+--------+--------------+
| id | name      | age  | sex    | name         |
+----+-----------+------+--------+--------------+
|  1 | egon      |   18 | male   | 技术         |
|  2 | alex      |   48 | female | 人力资源     |
|  3 | wupeiqi   |   38 | male   | 人力资源     |
|  4 | yuanhao   |   28 | female | 销售         |
|  5 | liwenzhou |   18 | male   | 技术         |
+----+-----------+------+--------+--------------+

#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

3 外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技术         |
|  5 | liwenzhou  | 技术         |
|  2 | alex       | 人力资源     |
|  3 | wupeiqi    | 人力资源     |
|  4 | yuanhao    | 销售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

4 外链接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技术         |
|    2 | alex      | 人力资源     |
|    3 | wupeiqi   | 人力资源     |
|    4 | yuanhao   | 销售         |
|    5 | liwenzhou | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

5 全外连接：显示左右两个表全部记录

#全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL        |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

#注意 union与union all的区别：union会去掉相同的纪录

组合，上下连表，在一张表中展示
    #1、组合，自动去重
    select nickname from A
    union
    select name from B;
 
    #2、组合，不处理重合
    select nickname from A
    union all
    select name from B;

符合条件连接查询

#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id
and age > 25 order by age asc;

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

1 带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee where dep_id in (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

2 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name    | age  |
+---------+------+
| alex    | 48   |
| wupeiqi | 38   |
+---------+------+
2 rows in set (0.00 sec)

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

3 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

#查询更多的写法
1、
select 
    id,
    name,
    1,
    (select count(1) from tb)      #查询结果中可以写子查询结果
from tb2

2、
SELECT 
    student_id,
    (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
    (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
    (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
from score as s1;

3、
select 
    course_id,
    max(num),
    min(num),
    min(num)+1,
    case when min(num) <10 THEN 0 ELSE min(num) END as c  
from score GROUP BY course_id
#case when ... then ... else ...end表示条件语句，固定格式，可以写在查询结果中

4、按各科平均成绩从低到高和及格率的百分数从高到低顺序
#select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl
   #from score GROUP BY course_id order by AVG(num) asc,jgl desc;     #聚合函数与case when ... then ... else ...end条件表达式结合使用

5、课程平均分从高到低显示（显示任课老师）；
# select avg( if (isnull(score.num), 0, score.num)), teacher.tname from course left join score on
# course.cid = score.course_id left join teacher on course.teacher_id = teacher.tid group by score.course_id
#avg( if (isnull(score.num), 0, score.num))如果出现在数值中做判断，可以使用 if (isnull(score.num), 0, score.num)表示如果score.num字段为空就显示0，否则显示score.num

查询结果多种写法

mysql可视化工具：Navicat

对应转储SQL文件：数据库备份

Navicat中SQL注释：

单行注释：— —

多行注释：/*...*/

临时表：

select sid from (select * from class where cid=1) as A;括号中查询的部分数据作为一张虚拟表存在在内存中，就是临时表，然后再查临时表中的数据，临时表后面必须 as 表名

练习题：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

表结构和数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
思路：
获取所有有生物课程的人（学号，成绩） - 临时表
获取所有有物理课程的人（学号，成绩） - 临时表
根据【学号】连接两个临时表：
学号
物理成绩
生物成绩
然后再进行筛选

# select
# A.student_id, sw, ty
# from
# (select student_id, num as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A
# left join
# (select student_id, num  as ty from score left join course on score.course_id = course.cid where course.cname = '体育') as B
# on
# A.student_id = B.student_id
# where
# sw > if (isnull(ty), 0, ty);

3、查询平均成绩大于60分的同学的学号和平均成绩；
思路：
根据学生分组，使用avg获取平均值，通过having对avg进行筛选
#select student_id, avg(num) from score group by student_id having avg(num) > 60

4、查询所有同学的学号、姓名、选课数、总成绩；
# select
# score.student_id, sum(score.num), count(score.student_id), student.sname
# from score
# left join student on
# score.student_id = student.sid
# group by score.student_id

5、查询姓“李”的老师的个数；
#select count(tid) from teacher where tname like '李%'

#select count(1) from (select tid from teacher where tname like '李%') as B

6、查询没学过“叶平”老师课的同学的学号、姓名；
思路：
先查到“李平老师”老师教的所有课ID
获取选过课的所有学生ID
学生表中筛选
# select * from student where
# sid not in
# (select DISTINCT student_id from score where score.course_id in
# (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'))

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
思路：
先查到既选择001又选择002课程的所有同学
根据学生进行分组，如果学生数量等于2表示，两门均已选择

# select student_id, sname
# from
# (select student_id, course_id from score where course_id = 1 or course_id = 2) as B
# left join
# student
# on
# B.student_id = student.sid
# group by
# student_id
# HAVING
# count(student_id) > 1

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

#同上，只不过将001和002变成 in (叶平老师的所有课)

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
#同第1题

10、查询有课程成绩小于60分的同学的学号、姓名；
# select sid, sname from student where
# sid in (select distinct student_id from score where num < 60)

11、查询没有学全所有课的同学的学号、姓名；
思路：
在分数表中根据学生进行分组，获取每一个学生选课数量
如果数量 == 总课程数量，表示已经选择了所有课程

# select student_id, sname from score
# left join
# student
# on
# score.student_id = student.sid
# group by
# student_id
# HAVING
# count(course_id) = (select count(1) from course)

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
思路：
获取001同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选，获取所有学生信息
再与学生表连接，获取姓名
# select student_id, sname, count(course_id) from score
# left join
# student
# on
# score.student_id = student.sid
# where
# student_id != 1 and course_id in (select course_id from score where student_id = 1)
# group by student_id

13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
先找到和001的学过的所有人
然后个数 ＝ 001
所有学科＝＝》 其他人可能选择的更多
# select student_id, sname, count(course_id) from score
# left join
# student
# on
# score.student_id = student.sid
# where
# student_id != 1 and course_id in (select course_id from score where student_id = 1)
# group by student_id
# having
# count(course_id) ＝ (select count(course_id) from score where student_id = 1)

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
个数相同
002 学过的也学过
# select student_id, sname from score
# left join student on
# score.student_id = student.sid
# where
# student_id in (
#     select student_id from score
#     where student_id != 1 group by student_id HAVING count(course_id) =
#     (select count(1) from score where student_id = 1)) and course_id in (select course_id from score where student_id = 1)
# group by student_id
# HAVING
# count(course_id) = (select count(1) from score where student_id = 1)

15、删除学习“叶平”老师课的score表记录；
# delete from score where
# course_id in (
#     select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '叶平')

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
思路：
由于insert
支持
inset
into
tb1(xx, xx)
select
x1, x2
from tb2;
所有，获取所有没上过002课的所有人，获取002的平均成绩
# insert into score(student_id, course_id, num)
# select sid, 2, (select avg(num) from score where course_id = 2) from student where
# sid not in (
#     select student_id from score where course_id = 2)

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID, 语文, 数学, 英语, 有效课程数, 有效平均分；
# select
# sc.student_id,
# (select
#  num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
# (select
#  num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
# (select
#  num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
# count(sc.course_id),
# avg(sc.num)
# from score as sc
# group by student_id desc

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
#select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
思路：case、when..then
# select course_id, avg(num) as avgnum, sum(case when score.num > 60 then 1 else 0 END) / count(1) * 100 as percent
#from score group by course_id order by avgnum asc, percent desc;

20、课程平均分从高到低显示（现实任课老师）；
# select avg( if (isnull(score.num), 0, score.num)), teacher.tname from course
# left join score on
# course.cid = score.course_id
# left join teacher on
# course.teacher_id = teacher.tid
# group by score.course_id

21、查询各科成绩前三名的记录: (不考虑成绩并列情况)
# select score.sid, score.course_id, score.num, T.first_num, T.second_num from score
# left join
# (select sid,
#     (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,
#     (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3, 1) as second_num
# from score as s1) as T
# on
# score.sid = T.sid where score.num <= T.first_num and score.num >= T.second_num

22、查询每门课程被选修的学生数；
#select course_id, count(1) from score group by course_id;

23、查询出只选修了一门课程的全部学生的学号和姓名；
# select student.sid, student.sname, count(1) from score
# left join student on
# score.student_id = student.sid
# group by course_id
# having
# count(1) = 1

24、查询男生、女生的人数；
# select * from
#     (select count(1) as man from student where gender = '男') as A,
#     (select count(1) as feman from student where gender = '女') as B

25、查询姓“张”的学生名单；
#select sname from student where sname like '张%';

26、查询同名同姓学生名单，并统计同名人数；
#select sname, count(1) as count from student group by sname;

27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
# select course_id, avg( if (isnull(num), 0, num)) as avg from score
# group by course_id
# order by avg asc, course_id desc;

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
# select student_id, sname, avg( if (isnull(num), 0, num)) from score
# left join student on
# score.student_id = student.sid
# group by student_id;

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
# select student.sname, score.num from score
# left join course on
# score.course_id = course.cid
# left join student on
# score.student_id = student.sid
# where score.num < 60 and course.cname = '生物'

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
#select * from score where score.student_id = 3 and score.num > 80

31、求选了课程的学生人数
# select count(distinct student_id) from score
# select count(c) from (select count(student_id) as c from score group by student_id) as A

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
# select sname, num from score
# left join student on
# score.student_id = student.sid
# where
# score.course_id in
# (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师')
# order by num desc limit 1;

33、查询各个课程及相应的选修人数；
# select course.cname, count(1) from score
# left join course on
# score.course_id = course.cid
# group by course_id;

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
# select DISTINCT s1.course_id, s2.course_id, s1.num, s2.num from score as s1, score as s2
# where
# s1.num = s2.num and s1.course_id != s2.course_id;

35、查询每门课程成绩最好的前两名；
# select
# score.sid, score.course_id, score.num, T.first_num, T.second_num from score
# left join
# (select sid,
#     (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,
#     (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_num
# from score as s1) as T
# on
# score.sid = T.sid
# where
# score.num <= T.first_num and score.num >= T.second_num

36、检索至少选修两门课程的学生学号；
# select student_id from score
# group by
# student_id
# having
# count(student_id) > 1

37、查询全部学生都选修的课程的课程号和课程名；
# select course_id, count(1) from score
# group by
# course_id
# having
# count(1) = (select count(1) from student);

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
# select student_id, student.sname from score
# left join student on
# score.student_id = student.sid
# where
# score.course_id not in (
#     select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '张磊老师')
# group by student_id

39、查询两门以上不及格课程的同学的学号及其平均成绩；
# select student_id, count(1) from score where num < 60
# group by student_id having count(1) > 2

40、检索“004”课程分数小于60，按分数降序排列的同学学号；
# select student_id from score where num < 60 and course_id = 4
# order by num desc;

41、删除“002”同学的“001”课程的成绩；
#delete from score where course_id = 1 and student_id = 2

ＳＱＬ练习题

posted @ 2020-12-24 17:05 Blues-Li 阅读(39) 评论(0) 收藏举报

刷新页面返回顶部

Blues-Li

MYSQL（表数据操作）

概览

插入数据insert

更新数据update

删除数据delete

查询数据 select

单表查询

单表查询的语法及关键字执行的优先级

简单查询

where约束

group by 分组

聚合函数

having过滤

order by查询排序

limit限制查询的记录数

使用正则表达式查询

多表查询

准备

多表连接查询

子查询

mysql可视化工具：Navicat

公告