MySQL数据库的学习笔记

基础篇MySQL

需要数据库数据文件的可以找我要。

基础语句

连接数据库：

`mysql -uroot -pdong    ``# u 后面跟用户名 p 后面是密码`

查看数据库：

`show databases;`

使用数据库：

`use databases;`

查看数据库中所有表：

`show tables;　`

创建数据库：(create database + 数据库名)

`create database test_db_name`

删除数据库：(drop database + 数据库名)

`drop database dbname;`

查看表的结构 ：DESC 表名

数据类型

数值型

# 如何设置有符号和无符号
CREATE TABLE tab_int (
	t1 INT, # 默认有符号
	t2 INT UNSIGNED
);

数值型特点

1.如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

2.如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值

3.如果不设置长度，会有默认的长度

4.长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofil1使用！

小数型

分类：
1.浮点型
float（M，D）doule（M，D）
2，定点型
dec（M，D）
decimal（M，D）

特点：
1、M：整数部位+小数部位 D：小数部位
如果超过范围，则插入临界值

2、M和D都可以省略

如果是decimal，则默认为10，D默认为0

如果是float和double，则会根据插入的数值的精度来决定精度

3、定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

字符型

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

日期型

	字节	范围	时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受

查询语句

1.查询单个字段

select 字段名 from 表名

2.查询多个字段

select 字段名，字段名。。。 from 表名

3.查询所有字段

select * from 表名

4.查询常量值&表达式

select 100;

select 98%100;

5.起别名

select last_name AS 姓 from 表名

select last_name 姓 from 表名

6.去重

select distinct last_name from 表名

7.concat拼接

select concat(last_name, first_name) from 表名

8.条件查询where

select 查询列表 from 表名 where 筛选条件

​ 简单的条件运算符：> < = != <>(安全等于) >= <=

​ 逻辑运算符：&&(and) ||(or) !(not)

​ 模糊查询：like between …and in is null

​ 通配符：%任意多个字符，包含0 _ 任意单个字符

9.排序查询

select 查询列表 from 表名 [where 筛选条件] order by 排序字段 [asc(升序) | desc(降序)]

10.分组查询

​ 语法：

​ select 分组函数，列

​ from 表

​ [where 筛选条件]

​ group by 分组的列表

​ [having 分组后的条件]

​ [order by 子句]

​ 注意：

​ 查询列表必须特殊，要求是分组函数和group by后出现的字段

#查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

#查询哪个部门的员工个数大于2
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;
HAVING COUNT(*) > 2;

#查询领导编号>102的每个领导手下的最低工资>5000的领导标号和最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

#查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示 
SELECT AVG(salary), department_id, job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id, department_id
ORDER BY AVG(salary) DESC;

	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

​ 总结：

​ 1.分组函数做条件肯定是放在having子句中

​ 2.可以支持多个字段分组，也可以支持多个字段

​ 3.排序要在分组字段之后

11.having和where

having是在分组之后的筛选条件，where是没分组之前元数据进行筛选。使用时要注意是在分组后还是分组前进行筛选，分组前使用where，分组后使用having。可是使用分组前的尽量使用分组前，效率更高。

连接查询

​ 笛卡尔乘积：表1 有m行，表2 有n行 结果=m*n行

​ 分类：

​ 内连接：等值连接、非等值连接、自连接

​ 外连接：左外连接、右外连接、全外连接

​ 交叉连接

​ sql92标准：仅支持内连接

​ sql99标准：支持内连接+外连接(左外和右外)+交叉连接

等值连接

#查询员工名对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;

#查询城市名种第二个字符为o的部门名和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

#查询每个城市的部门个数
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

# 查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`;

非等值连接

#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

自连接

#查询员工名和上级的名称
SELECT e.`last_name` 员工名, m.`last_name` 领导名
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

Sql99语法

语法：

​ select 查询列表

​ from 表1 别名 [连接类型]

​ inner[可省略] join 表2 别名

​ on 连接条件

​ [where 筛选条件]

​ [group by 分组]

​ [having 筛选条件]

​ [order by 排序列表]

分类：

​ 内连接 ： inner join

​ 外连接 ：

​ 左外 ：left [outer]

​ 右外 ：right [outer]

​ 全外 ：full [outer]

	交叉连接 ： cross

Sql99等值连接

# 查询员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`;

# 查询名字中包含e的员工名和工种名（添加筛选）
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE '%e%';

# 查询部门个数>3的城市名和部门个数(添加分组+筛选）
SELECT `city`,COUNT(*) 部门个数
FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id` = d.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;

# 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT `department_name`,COUNT(*) 员工个数
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY 员工个数 DESC;

# 查询员工名、部门名、工种名，并按部门名降序
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d ON e.`department_id` = d.`department_id`
INNER JOIN `jobs` j ON j.`job_id` = e.`job_id`
ORDER BY `department_name` DESC;

Sql99非等值连接

#查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e
JOIN job_grades j
ON salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#查询员工工资级别>20的个数，按工资级别降序排列
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades j
ON e.salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY COUNT(*) DESC;

Sql99自连接

#查询员工名和上级的名称
SELECT e.`last_name` 员工名, m.`last_name` 领导名
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`;

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录

​ 如果从表中有和它匹配的，则显示匹配的值

​ 如果从表中没有和它匹配的，则显示null

​ 外连接查询结果=内连接结果+主表中有而从表没有的记录

2、左外连接，left join左边的是主表

​ 右外连接，right join右边的是主表

3、左外和右外交换两个表的顺序，可以实现同样的效果

Sql99左外连接

#查询男朋友不在男生表的女生名
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

# 查询哪个部门没有员工
SELECT d.*, e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

Sql99右外连接

#查询男朋友不在男生表的女生名
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

# 查询哪个部门没有员工
SELECT d.*, e.`employee_id`
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

子查询

概念：出现在其他语句内部的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为外查询或主查询

分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
*where或having后面：
标量子查询(单行)
列子查询(多行)
行子查询
exists后面（相关子查询）: 表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

where或having后面

一、where或having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
列子查询，一般搭配着多行操作符使用in，any/some，all

标量子查询（单行子查询）

#查询比Abel工资高的员工
SELECT salary 
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

# 返回job-id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE `employee_id` = 141
)
AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

#返回公司工资最少的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

列子查询（多行子查询）

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY｜SOME	和子查询返回的某一个值比较
ＡＬＬ	和子查询返回的所有值比较

# 查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
	SELECT `boyfriend_id`
	FROM `beauty`
);


# 返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name,department_id
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
);

# 返回其他工种中比job_id为'IT_PROG'部门"任意"工资低的员工的员工名、姓名、job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY (
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

# 返回其他工种中比job_id为'IT_PROG'部门"所有"工资低的员工的员工名、姓名、job_id以及salary
SELECT `last_name`,`first_name`,`job_id`,`salary`
FROM `employees`
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM `employees`
	WHERE `job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG';

练习题目

###########################################################
# 1.查询和zlotkey相同部门的员工姓名和工资
SELECT `last_name`,`salary`
FROM `employees`
WHERE `department_id` = (
	SELECT department_id
	FROM `employees`
	WHERE `last_name` = 'zlotkey'
);

# 2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT `employee_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary` > (
	SELECT AVG(`salary`)
	FROM `employees`
);

# 3，查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
SELECT `employee_id`,`last_name`,`salary`
FROM `employees` e
INNER JOIN (
	SELECT AVG(salary) ag,`department_id`
	FROM `employees`
	WHERE department_id IS NOT NULL
	GROUP BY department_id
) salary_id
ON e.`department_id` = salary_id.department_id
WHERE e.`salary` > salary_id.ag;

# 4.查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT `employee_id`,`last_name`
FROM `employees`
WHERE department_id IN (
	SELECT DISTINCT `department_id`
	FROM `employees`
	WHERE `last_name` LIKE '%u%'
);

# 5，查询在部门的location-id为1700的部门工作的员工的员工号
SELECT `employee_id`
FROM `employees`
WHERE `department_id` IN (
	SELECT `department_id`
	FROM `departments`
	WHERE `location_id` = 1700
);

# 6·查询管理者是K_ing的员工姓名和工资
SELECT `last_name`,`salary`
FROM `employees`
WHERE `manager_id` IN (
	SELECT `employee_id`
	FROM `employees`
	WHERE `last_name` = 'K_ing'
);

# 7，查询工资最高的员工的姓名，要求first-name和last_name显示为一列，列名为姓.名
SELECT MAX(salary), CONCAT(`first_name`, `last_name`) 姓名
FROM `employees`;

其余子查询

# select后面的查询
# 查询每个部门的员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE d.`department_id` = e.department_id
) 个数
FROM departments d;

# 查询每个分类的博客数量，按倒叙排列
SELECT *, (
	SELECT COUNT(*)
	FROM t_blog
	WHERE t_blog.`type_id` = t_type.`id`
) 个数
FROM t_type
ORDER BY 个数 DESC;

# exists后面（相关子查询）
语法：exists(完整的查询语句)
结果：1或0
# 查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT *
	FROM `employees` e
	WHERE e.`department_id` = d.`department_id`
);

分页查询

语法：

​ select 查询列表

​ from 表

​ [on连接条件

​ where 筛选条件

​ group by 分组字段

​ having 分组后的筛选

​ order by 排序的字段]

​ limit 起始索引，显示长度；

​ offset要显示条目的起始索引（起始索引从0开始）

​ size要显示的条目个数

#查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
或 SELECT * FROM employees LIMIT 5;

# 查询第11条一第25条
SELECT * 
FROM employees
LIMIT 10, 15;

# 有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE `commission_pct` IS NOT NULL
ORDER BY `salary` DESC
LIMIT 10;

联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果。

#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION [All 默认会自动去重，加上All则不会去重] 
SELECT * FROM employees WHERE department_id > 90;

常见函数

字符函数

length ： 计算字符串长度
    SELECT LENGTH('董浩同')

concat ： 拼接字符串 SELECT CONCAT('董', '浩同') 姓名;

upper、lower ： 将字符串变大写、小写

substr、substring ： 截取字符串
    截取从指定索引后面所有字符
    SELECT SUBSTR('董浩同', 2)
    截取指定索引处指定字符长度的字符
    SELECT SUBSTR('董浩同', 2, 3)

instr : 返回子串第一次出现的索引，找不到返回0
    SELECT INSTR('东好痛', '好'); // 2

trim ： 去掉前后空格,也可以指定去除的字符
	SELECT TRIM('           东好 痛   '); // 东好 痛
	SELECT TRIM('a' FROM 'aaaaaaaaa东好痛aaa');// 东好痛

lpad ：用指定的字符实现左填充指定长度
rpad ：用指定的字符实现右填充指定长度
	SELECT LPAD('董浩同', 4, '*'); // *董浩同

replace ：替换
	SELECT REPLACE('董浩同', '同', '样');// 董浩样

数学函数

round ：四舍五入
	SELECT ROUND(1.23); // 1
	SELECT ROUND(-1.23); // -1

ceil ：向上取整
	SELECT CEIL(1.18); // 2

floor ：向下取整
	SELECT FLOOR(1.18); // 1
	
truncate ：截断
	SELECT TRUNCATE(1.8888, 1); // 1.8
	SELECT TRUNCATE(1.8888, 2); // 1.88
	
mod ：取余
	SELECT MOD(10, 3); // 1
	SELECT 10 % 3; // 1

日期函数

now ：返回当前系统日期+时间
	SELECT NOW(); // 2020-04-27 11:17:26
	
year：返回年
month：返回月
day：返回日

	
curdate ：返回当前系统日期，不包含时间
	SELECT CURDATE(); // 2020-04-27
	
curtime ：返回当前系统时间，不包含日期
	SELECT CURTIME(); // 11:24:35
	
str_to_date ：将字符串通过指定格式转换成日期
	SELECT STR_TO_DATE('1999-9-24', '%Y-%c-%d'); // 1999-09-24
	
date_format ：将日期转换成字符

DATEDIFF（参数1，参数2）：获取两个参数之间的天数差	
	SELECT DATEDIFF (MAX(`hiredate`), MIN(`hiredate`)) FROM `employees`;

其他函数

查看版本号
SELECT VERSION();

查看当前用户
SELECT USER();

流程控制函数

if函数
	SELECT IF(10>5, '大', '小');
	
case函数
	case 要判断的字段或表达式
	when 常量1 then 要显示的值1或语句1；
	when 常量2 then 要显示的值2或语句2；
	。。。
	else 要显示的值n或语句n；
	end；

	SELECT salary,
    CASE
    WHEN salary>2000 THEN 'A'
    WHEN salary>1500 THEN 'B'
    WHEN salary>1000 THEN 'C'
    ELSE 'D'
    END AS 工资级别
    FROM employees;

分组函数

分组函数：用作统计使用，又称为聚合函数或统计函数或组函数。

sum ：求和
	SELECT SUM(salary) FROM employees;

avg ：平均值
min ：最小值
max ：最大值
count ：个数
	count(*) : 查询总个数
注意：
	1.sum、avg一般处理数值型
	2.max、min、count可以处理任何类型
	3.分组函数都忽略null值
	4.可以和distinct搭配去重 （SELECT SUM(DISTINCT salary) FROM `employees`;）
	5.和分组函数一同查询的字段要求是group by后的字段

数据操作

插入数据

方式一

insert into 表名(列名，… …) values(值1，… …)；

方式二

insert into 表名

set 列名= 值，列名=值，… …；

# 不可以为null的列必须插入值，可以为null的列如何插入值？
# 方式一
INSERT INTO beauty(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺昕','女','1990-4-23','189888888',NULL,2);
#方式二
INSERT INTO beauty(`id`,`name`,sex,`phone`)
VALUES(15,'景甜','女','189888888');

# 列的顺序是否可以调换 ? 可以
INSERT INTO beauty(`name`,`id`,`phone`,sex)
VALUES('白莲花',16,'189888888','女');

# 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(17,'张飞','男','1990-4-23','189888888',NULL,NULL);

两种方式的比较：

​ 1、方式一支持插入多行，方式二不支持

​ 2、方式一支持子查询，方式二不支持

修改语句

修改单表

语法：

update 表名

set 列名=新值，列名=新值，。。。

where 筛选条件;

# 修改beauty表中姓唐的女神的电话为13899888899
UPDATE `beauty` SET `phone` = '13899888899'
WHERE NAME LIKE '唐%';

# 修改boys表中id号为2的名称为张飞，魅力值10
UPDATE `boys` SET `boyName` = '张飞', `userCP` = 10
WHERE id = 2;

修改多表

语法：

sql92语法：

update 表1 别名，表2 别名

set 列=值。。。

where 连接条件

and 筛选条件

sql99语法：

update 表1 别名

inner | left | right join 表2 别名

on 连接条件

set 列=值。。。

where 筛选条件

# 修改1号男生的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b 
ON bo.`id` = b.`boyfriend_id`
SET b.`phone` = 114
WHERE bo.`id` = 1;

# 修改没有男朋友的女神的男朋友编号都为2号
UPDATE `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
SET b.`boyfriend_id` = 2
WHERE bo.`id` IS NULL;

删除语句

方式一

单表语法：

delete from 表名 where 筛选条件

# 删除手机号以9结尾的女神信息
DELETE FROM `beauty` WHERE `phone` LIKE '%9';

多表删除语法：

sql92语法：

delete 表1的别名，表2的别名

from 表1 别名， 表2 别名

where 连接条件

and 筛选条件

sql99语法：

delete 表1的别名， 表2的别名

from 表1 别名

inner | left | right join 表2 别名

set 列=值。。。

where 筛选条件

# 删除1号男生的女朋友
DELETE b
FROM beauty b
INNER JOIN boys bo 
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` = 1;

# 删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '黄晓明';

方式二

truncate table 表名 （直接删除表的所有内容）

truncate 对比delete：

1.delete可以加where条件，truncate 不能加

2.truncate删除，效率高一点

3.假如要删除的表中有自增长列，如果用delete删除后，在插入数据，自增长序列的值从断点开始，

而truncate删除后，再插入数据，自增长序列从1开始。

4.truncate删除没有返回值，delete删除有返回值

5.truncate删除不能回滚，delete删除可以回滚

数据定义语言

库的管理

创建库

create database [if not exists] 库名；

更改库的字符集

alter database 库名 character set 字符集；

库的删除

drop database [if exists] 库名；

# 创建数据库
CREATE DATABASE IF NOT EXISTS donghaotong;
# 修改字符集
ALTER DATABASE donghaotong CHARACTER SET gbk;
# 库的删除
DROP DATABASE IF EXISTS donghaotong;

表的管理

表的创建

create table [if not exists] 表名 (

​ 列名 列的类型[（长度）约束],

​ 。。。。。。

)；

表的修改

# 修改列名
ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 类型;
实例
ALTER TABLE USER CHANGE COLUMN test_name username VARCHAR(20);

#修改列的类型和约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 [新约束];
实例
ALTER TABLE USER MODIFY COLUMN birthday TIMESTAMP;
实例
ALTER TABLE USER MODIFY COLUMN birthday TIMESTAMP NOT NULL;
实例
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

#添加新列
ALTER TABLE 表名 ADD COLUMN 新列名 类型;
实例
ALTER TABLE USER ADD COLUMN phone INT;

#删除列
ALTER TABLE 表名 DROP COLUMN 列名;

#修改表名
ALTER TABLE 原表名 RENAME TO 新表名;
实例
ALTER TABLE USER RENAME TO me_user;

#表的删除
DROP TABLE 表名；

复制表

#仅复制表的结构
CREATE TABLE 新表名 LIKE 原表名;

#复制表的结构+数据(部分或全部均可，自行修改后面的查询语句)
CREATE TABLE 新表名
SELECT * FROM 原表名; (想复制这么可以使用select语句扩展)

常见约束

约束：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。

六大约束

​ NOT NULL ： 非空约束

​ DEFAULT ：默认值

​ PRIMARY KEY ：主键，保证字段的唯一性，和非空性

​ UNIQUE ：唯一，保证唯一性，可以为空

​ FOREIGN KEY ：外键约束，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联值

​ CHECK ： 检查约束（mysql不支持）

#添加列级约束
#只支持：默认，非空，主键，唯一
CREATE TABLE test(
	id INT PRIMARY KEY,  主键约束
	NAME VARCHAR(20) NOT NULL, 非空约束
	gender CHAR(1) CHECK(gender='男' OR gender='女'), 检查约束
	seat INT UNIQUE  唯一约束
	age INT DEFAULT 18  默认约束
);

#添加表级约束
#[CONSTRAINT 约束名] 约束类型(字段名) 
#除了非空、默认，其他都支持
CREATE TABLE test(
	id INT , 
	stuname varchar(20),
    gender char(1)，
    seat int,
    age int,
    majorid int,
    
    # CONSTRAINT pk 可以省略
	CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uq PRIMARY KEY(seat),
    CONSTRAINT ck CHECK(gender='男' OR gender='女')(seat),
	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id) 外键约束
);

# 通用写法，一般这样写即可
CREATE TABLE test(
	id INT PRIMARY KEY, 
	NAME VARCHAR(20) NOT NULL,
	gender VARCHAR(3) CHECK(gender='男' OR gender='女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorid INT,
	
	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id) 外键约束
);

外键：
	1.要求在从表设置外键关系
	2.从表的外键列的类型和主表的关联列的类型要求一致或兼容
	3.主表的关联列必须是一个key（一般主键或唯一）
	4.插入数据时，先插入主表，在插入从表
	5.删除数据时，先删除从表，在删除主表

主键和唯一对比

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	保证	不允许	至少有一个	允许，但不推荐
唯一	保证	允许	可以有多个	允许，但不推荐

外键特点

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

3、主表的关联列必须是一个key（一般是主键或唯一）

4.插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表

标识列

标识列：又称为自增长列 （AUTO_INCREMENT）

含义：可以不用手动的插入值，系统提供默认的序列值。

CREATE TABLE test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL
);

# 修改表的设置标识列
alter table test modify column id int PRIMARY KEY AUTO_INCREMENT;

# 修改表的删除标识列
alter table test modify column id int PRIMARY KEY;

特点：

​ 1.标识列搭配使用时，必须要求是一个key。

​ 2.一个表可以有至多一个标识列。

​ 3.标识列的类型只能是数值型

​ 4.标识列可以通过set auto_increment = 3设置步长

​ 5.也可以手动设置起始值，来设置起始位置。

存储引擎

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pj1sn53q-1655013832577)(image\屏幕截图 2021-08-25 202935.png)]

事务

介绍

事务：一个或一组sql语言组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的属性（ACID）：

​ 1.原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

​ 2.一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

​ 3.隔离性：指一个事务的执行不能被其他事务干扰，即一个事物内部的操作及使用的数据对并发的其他事务是 隔离的，并发执行的各个事务之间不能互相干扰。

​ 4.持久性：指一个事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障 不应该对其有任何影响。

事务的创建

​ 隐式事务：事务没有明显的开启和结束的标记。

​ 比如：insert、update、delete语句

​ 显示事务：事务具有明显的开启和结束的标记

​ 前提：必须先设置自动提交功能为禁止

步骤一：开启事务
set autocommit = 0；
start transaction；（可写可不写）

步骤二：编写事务中的sql语句
语句一；
语句二；
。。。。。。

步骤三：结束事务
commit；提交事务 | rollback；回滚事务

实例：
    # 演示事务
    SET autocommit = 0;
    START TRANSACTION;
    UPDATE account SET `balance` = 1000 WHERE username = '董浩同';
    UPDATE account SET `balance` = 500 WHERE username = '庞立';
    commit;

并发问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

​ 脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。

​ 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。

​ 幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入一些新的行之后，如果T1再次读取同一个表，就会多出几行。

事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会互相影响，避免各种并发问题。

隔离级别：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ocyZqH4E-1655013832578)(image\批注 2020-04-28 200331.jpg)]

MySQL默认的事务隔离级别：REPEATABLE READ

	脏读	不可重复读	幻读
read uncommitted	存在	存在	存在
read committed	不存在	存在	存在
repeatable read	不存在	不存在	存在
serializable	不存在	不存在	不存在

备注:

mysql中默认 第三个隔离级别"repeatable read"

oracle中默认第二个隔离级别read committed

查看隔离级别 : select @@ tx_isolation；

设置隔离级别 : set [session | global] transaction isolation level 隔离级别；

保存点

SAVEPOINT 保存点名称；

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET `balance` = 900 WHERE username = '董浩同';
SAVEPOINT a; # 设置保存点
UPDATE account SET `balance` = 600 WHERE username = '庞立';
ROLLBACK TO a; # 回滚到保存点a

视图

视图：一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保 存了sql逻辑，不保存查询结果。

创建视图

创建视图

语法：
CREATE VIEW 视图名
AS 
查询语句。。。

实例：
CREATE VIEW v1
AS 
SELECT * FROM me_user;


使用视图
SELECT * FROM v1;
SELECT * FROM v1 WHERE username LIKE '%王%';

修改视图

方式一：

create or replace view 视图名 （create or replace：存在即修改，不存在即添加）

as

查询语句；

方式二：

alter view 视图名

as

查询语句；

ALTER VIEW v1
AS 
SELECT * FROM v1 WHERE username LIKE '%王%';

删除视图

语法：drop view 视图名，视图名，。。。；

查看视图

DESC 视图名；

视图和表

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，只是一般不能增删改
表	create table	保存了数据	增删改查

视图练习

#1，查询姓名中包含a字符的员工名、部门名和工种信息
CREATE VIEW test01
AS
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
JOIN `departments` d ON e.`department_id` = d.`department_id`
JOIN `jobs` j ON e.`job_id` = j.`job_id`;

SELECT * FROM test01 WHERE last_name LIKE '%a%';

#2，修改视图test01查询100号员工 
CREATE OR REPLACE VIEW test01
AS
SELECT * FROM employees WHERE `employee_id` = 100;

SELECT * FROM test01;

#3，删除视图
DROP VIEW test01;

变量

系统变量

系统变量：变量由系统提供，不是用户定义，属于服务器层面

系统变量分为 ：全局变量、会话变量

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启

语法：

1.查看所有的系统变量
SHOW GLOBAL | [SESSION] VARIABLES;
2.查看满足条件的系统变量
SHOW GLOBAL | [SESSION] VARIABLES LIKE '%char%';
3.为某个系统变量赋值
SET GLOBAL | [SESSION] 系统变量名 = 值；
SET @@GLOBAL | [SESSION] .系统变量名 = 值；
4.查看指定的某个系统变量的值
select @@GLOBAL |[SESSION]  .系统变量名;

注意：
如果是全局级别，则需要加GLOBAL，如果是会话级别，则需要加SESSION，如果不写，则默认SESSION。

自定义变量

自定义变量：变量是用户自定义的，不是有系统的

用户变量

​ 作用域：针对于当前会话（连接）有效，同于会话变量的作用域。

​ 赋值的操作符：= 或 : =

​ 1.声明并初始化(或 赋值 或 更新值)

​ set @用户变量名 = 值；

​ set @用户变量名 := 值；

​ select @用户变量名 := 值；

​ 2.查看（使用）用户变量的值

​ select @用户变量名;

局部变量

​ 作用域： 仅仅在定义它的begin end中有效，应用在begin end的第一句话。

​ 1.声明

​ DECLARE 变量名 类型；

​ DECLARE 变量名 类型 DEFAULT 值；

​ 2.赋值

​ set @局部变量名 = 值；

​ set @局部变量名 := 值；

​ select @局部变量名 := 值；

​ 3.使用

​ select 局部变量名；

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin end中	只能在begin end中，且为第一句话	一般不用加@符号，需要限定类型

存储过程

存储过程：一组预先编译好的SQL语句的集合，理解成批处理语句。

语法：

​ CREATE PROCEDURE 存储过程名（参数列表）

​ BEGIN

​ 存储过程体（一组合法的SQL语句）

​ END

​ 注意：

​ 1.参数列表包含三部分：参数模式、参数名、参数类型

​ 参数模式：

​ IN：该参数可以作为输入，也就是该参数需要调用方传入值

​ OUT：该参数可以作为输处，也就是该参数可以作为返回值

​ INOUT：该参数可以作为输处也可以作为输出，也就是需要传入值，又可以返回值

​ 2.如果存储过程体仅仅只有一句话，BEGIN END可以忽略，存储过程体中的每条SQL语句的结尾要求必 须加分号。存储过程的结尾可以使用DELIMITER重新设置。

​ DELIMITER语法：

​ DELIMITER 结束标记

调用语法：CALL 存储过程名（实参列表）；

参数模式为IN的存储过程

#空参的存储过程
DELIMITER $
CREATE PROCEDURE myp2()
BEGIN
	INSERT INTO test(test_name)
	VALUES('董浩同1111');
END $
#调用存储过程
CALL myp2();


#有参的存储过程
#案例：创建存储过程实现，根据女神名，查询对应的男生信息
DELIMITER $
CREATE PROCEDURE myp1(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用存储过程
CALL myp1('柳岩');


#有参有返回值的存储过程
#案例：创建存储过程实现，用户是否登陆成功。
DELIMITER $
CREATE PROCEDURE myp1(IN username VARCHAR(10), IN PASSWORD VARCHAR(10))
BEGIN
	DECLARE result VARCHAR(20) DEFAULT ''; #声明并初始化
	
	SELECT COUNT(*) INTO result #赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0, '登陆成功', '登陆失败'); #使用
END $

CALL myp3('john', '8888'); # 返回：登陆成功
CALL myp3('john**', '8888'); # 返回：登陆失败

参数模式为IN、OUT的存储过程

#创建带out模式的存储过程
#案例：根据女生名，返回对应男生名
DELIMITER $
CREATE PROCEDURE myp1(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName #赋值给out修饰的boyName
	FROM boys bo
	INNER JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#创建一个用户变量
SET @boyName;
#传参并且调用存储过程
CALL myp1('柳岩', @boyName);
#查看结果
SELECT @boyName; #结果：张无忌

参数模式为INOUT的存储过程

#案例：传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp1(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $

#调用
SET @m = 10;
SET @n = 20;
CALL myp1(@m, @n);
#m=20, n=40
SELECT @m, @n;

存储过程的删除

语法：DROP PROCEDURE 存储过程名

DROP PROCEDURE myp1;

查看存储过程

SHOW CREATE PROCEDURE myp1;

函数

存储过程：可以有0个返回值，也可以多个返回，适合做批量插入、批量删除。

函数：有且仅有 1 个返回值，适合做处理数据后返回一个结果。

创建语法：

​ CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
​ BEGIN
​ 函数体
​ END

​ 注意：

​ 1.参数列表包含（参数名，参数类型）

​ 2.函数体肯定会有RETURN语句，如果没有会报错。如果RETURN语句没有放在函数体的最后也不会报错， 但不建议。

​ 3.函数体中仅有一句话，则可以省略begin end

​ 4.使用delimiter语句设置结束标记

调用语法：

​ SELECT 函数名（参数列表）

#无参数,有返回值
#返回男生表的所有数据
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; #定义变量
	SELECT COUNT(*) INTO c
	FROM boys;
	RETURN c;
END $

SELECT myf1()$

#根据员工名，返回他的工资
DELIMITER $
CREATE FUNCTION myf2(employName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @salary=0; #定义变量
	SELECT employees.salary INTO @salary
	FROM employees
	WHERE `last_name` = employName;
	
	RETURN @salary;
END $

SELECT myf2('Kochhar')$

# 根据部门门返回平均工资
DELIMITER $
CREATE FUNCTION myf3(deptname VARCHAR(20)) RETURNS DOUBLE
BEGIN 
	SET @sal=0;
	SELECT AVG(salary) INTO @sal
	FROM `employees` e
	JOIN `departments` d
	ON e.`department_id` = d. department_id
	WHERE d.`department_name` = deptname;
	
	RETURN @sal;
END $

SELECT myf3('IT')$

查看函数

SHOW CREATE FUNCTION myf2;

删除函数

DROP FUNCTION myf2;

流程控制结构

顺序结构：程序从上往下依次执行。

分支结构：程序从两条或多条路径中选择一条去执行。

循环结构：程序在满足一定条件的基础上，重复执行一段代码。

分支结构

1.if函数

功能：实现简单的双分支

语法：SELECT IF(表达式1， 表达式2， 表达式3)

​ 执行顺序：表达式1成立，执行表达式2，否则执行表达式3

2.case结构

功能1：类似于Java的switch语句，一般用于实现等值判断

语法：CASE 变量|表达式|字段

​ WHEN 要判断的值 THEN 返回的值1或语句；

​ WHEN 要判断的值 THEN 返回的值2或语句；

​ 。。。

​ ELSE 要返回的值n；

​ END CASE；

功能2：类似于Java的多重IF语句，一般用于实现区间判断。

语法：CASE

​ WHEN 要判断的值 THEN 返回的值1或语句；

​ WHEN 要判断的值 THEN 返回的值2或语句；

​ 。。。

​ ELSE 要返回的值n；

​ END CASE；

3.if结构

功能：实现多重分支

语法：

​ IF 条件1 THAN 语句1；

​ ELSEIF 条件2 THAN 语句2；

​ 。。。

​ ELSE 语句n；

循环结构

1.while

语法：while 循环条件 do

​ 循环体；

​ end while [标签]；

2.loop

语法：loop

​ 循环体；

​ end loop [标签];

3.repeat

语法：repeat

​ 循环体；

​ until 结束循环条件

​ end repeat [标签];

DELIMITER $
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= insertCount DO
		INSERT INTO users(userid, PASSWORD)
		VALUES('Rose' + i, '8888');
		SET i = i + 1;
	END WHILE;

END $

CALL pro_while(100);

编写一个程序用于计算 10 的阶乘
DROP FUNCTION IF EXISTS myf2;
DELIMITER $
CREATE FUNCTION () RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 1; #定义变量
	DECLARE i INT DEFAULT 1; #定义变量
	WHILE i <= 10 DO
		SET result = result * i;
		SET i = i + 1;
	END WHILE;
	RETURN result;
END $
SELECT myf2()$

题目练习

子查询练习题目

#1查询工资最低的员工信息：last_name，salary
SELECT `last_name`,`salary`
FROM `employees`
WHERE salary = (
	SELECT MIN(`salary`)
	FROM `employees`
);

#2查询平均工资最低的部门信息 
SELECT d.*
FROM `departments` d
WHERE d.department_id = (
	SELECT department_id
	FROM `employees`
	GROUP BY department_id
	HAVING AVG(salary) = (
		SELECT MIN(ag)
		FROM(
			SELECT AVG(`salary`) ag,department_id
			FROM `employees`
			WHERE department_id IS NOT NULL
			GROUP BY `department_id`
		) ag_dep
	)
);

#3查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,ag_id.ag
FROM `departments` d
JOIN (
	SELECT AVG(salary) ag,`department_id`
	FROM `employees`
	WHERE department_id IS NOT NULL
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
) ag_id
ON d.`department_id` = ag_id.department_id;

#4查询平均工资最高的job信息
SELECT j.*
FROM `jobs` j
INNER JOIN (
	SELECT AVG(salary), `job_id`
	FROM `employees`
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
) ag_job
ON j.`job_id` = ag_job.job_id;

#5查询平均工资高于公司平均工资的部门有哪些？
SELECT AVG(salary),`department_id`
FROM `employees`
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
	FROM `employees`
);

#6查询出公司中所有manager的详细信息.
SELECT e.*
FROM `employees` e
WHERE e.`employee_id` IN (
	SELECT DISTINCT `manager_id`
	FROM `employees`
);

#7各个部门中最高工资中最低的那个部门的最低工资是多少
SELECT  MIN(salary)
FROM `employees`
WHERE department_id = (
	SELECT `department_id`
	FROM `employees`
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

#8查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary
SELECT `last_name`,`department_id`,`email`,`salary`
FROM `employees`
WHERE `employee_id` = (
	SELECT `manager_id`
	FROM `departments`
	WHERE `department_id` = (
		SELECT `department_id`
		FROM `employees`
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	)
);

综合练习

#一、查询每个专业的学生人数
SELECT COUNT(*),`majorid`
FROM `student`
GROUP BY majorid;

#二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(`score`), MAX(score), `studentno`
FROM `result` 
GROUP BY `studentno`;

#三、查询姓张的每个学生的最低分大于60的学号、姓名
方式一
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentname` LIKE '张%'
AND studentno IN (
	SELECT `studentno`
	FROM `result`
	GROUP BY `studentno`
	HAVING MIN(`score`) > 60
);
方式二
SELECT s.studentno,s.`studentname`,MIN(score)
FROM `student` s
JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.studentno
HAVING MIN(score) > 60;

#四、查询生日在"1988-1-1"后的学生姓名、专业名称
SELECT `studentname`,`majorname`
FROM `student` s
JOIN `major` m
ON s.`majorid` = m.`majorid`
WHERE DATEDIFF(s.`borndate`, '1988-1-1') > 0;

#五、查询每个专业的男生人数和女生人数分别是多少
方式一
SELECT COUNT(*), sex, `majorid`
FROM `student`
GROUP BY sex,majorid;
方式二
SELECT `majorid`,
(SELECT COUNT(*) FROM `student` WHERE sex = '男' AND majorid = s.`majorid`) 男,
(SELECT COUNT(*) FROM `student` WHERE sex = '女' AND majorid = s.`majorid`) 女
FROM `student` s
GROUP BY majorid;

#六、查询专业和张翠山一样的学生的最低分
SELECT MIN(`score`)
FROM `result`
WHERE `studentno` IN (
	SELECT s1.`studentno`
	FROM `student` s1
	WHERE s1.majorid = (
		SELECT s2.`majorid`
		FROM `student` s2
		WHERE s2.`studentname` = '张翠山'
	)
);

#七、查询大于60分的学生的姓名、密码、专业名
方式一
SELECT s.`studentname`, s.`loginpwd`, m.`majorname`
FROM `student` s, `result` r, `major` m
WHERE s.`studentno` = r.`studentno` 
AND s.`majorid` = m.`majorid`
AND r.`score` > 60;
方式二
SELECT studentname,loginpwd,majorname
FROM `student` s
JOIN `result` r ON s.`studentno` = r.`studentno`
JOIN `major` m ON s.`majorid` = m.`majorid`
WHERE r.`score` > 60;

#八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*),LENGTH(`email`)
FROM `student`
GROUP BY LENGTH(`email`);

#九、查询学生名、专业名、分数
SELECT `studentname`, `majorname`, `majorname`
FROM `student`,`result`,`major`
WHERE student.`studentno` = result.`studentno`
AND student.`majorid` = major.`majorid`;

#十、查询哪个专业没有学生，分别用左连接和右连接实现
SELECT `majorname`
FROM major m
LEFT JOIN `student` s
ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

SELECT `majorname`
FROM `student` s
RIGHT JOIN major m
ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

#十一、查询没有成绩的学生人数
SELECT COUNT(*)
FROM `student` s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL;

综合练习

 1、创建表Book表，字段如下：
	bid整型，要求主键
	bname李符，要求设置唯一键，并非空
	price浮点型，，要求有默认值10
	btypeld类型编号，要求引bookType表的
	id字段已知bookType表（不用创建），
	字段如下：id NAME 
2、开启事务，向表中插人1行数据，并结束
3、创建视图，实现查询价格大于100的书名和类型名
4、修改视图，实现查询价格在90-120之间的书名和价格
5、删除刚才建的视图

# 第一题
CREATE TABLE bookType(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
);
CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bname VARCHAR(20) UNIQUE NOT NULL,
	price DOUBLE DEFAULT 10,
	btypeld INT,
	CONSTRAINT fk FOREIGN KEY(btypeld) REFERENCES bookType(id)
);

# 第二题
SET autocommit = 0;
START TRANSACTION;
INSERT INTO bookType VALUES(1, '小说');
INSERT INTO Book VALUES(1, '平凡的世界', 110, 1);
COMMIT;

# 第三题
CREATE VIEW myv1
AS
SELECT `bname`,`name`
FROM `book` b, `booktype` bt
WHERE b.`btypeld` = bt.`id`
AND b.`price` > 100;

# 第四题
CREATE OR REPLACE VIEW myv1
AS
SELECT `bname`,`price`
FROM `book`
WHERE `price` BETWEEN 90 AND 120;

# 第五题
DROP VIEW myv1;