目录
8 聚合函数
8.1聚合函数讲解
#第八章 聚合函数
#1 常用的聚合函数
#1.1 avg /sum
SELECT AVG(salary) #6461.682243
FROM employees;
SELECT SUM(salary)
FROM employees;
SELECT SUM(salary) / COUNT(employee_id) #6461.682243
FROM employees;
#1.2 max / min
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name) #根据名字第一个字母
FROM employees;
#1.3 count
SELECT COUNT(salary),COUNT(employee_id),COUNT(commission_pct)
FROM employees;
#如果计算表中有多少条记录
#方式1；count(*)
#方式2；count(1)
#方式3；count(具体字段) 不一定对 不计算null
SELECT AVG(salary),SUM(salary) / COUNT(salary)
FROM employees;
#查询公司中平均奖金率
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;
#如果需要统计表中的统计数，哪个效率更高count(*) count(1) count(具体字段)
#如果使用的是myisam存储引擎，则三者效率相同，都是O（1）
#如果使用的是innodb存储引擎，则三者效率，count(*) = count(1) > count(字段)
#其他 方差 标准差 中位数
#2 GROUP BY 的使用
SELECT department_id,avg(salary)
FROM employees
GROUP BY department_id;
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
#查询各个department——id,jobid的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#或
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
SELECT department_id,job_id,AVG(salary) 'newSalary'
FROM employees
GROUP BY department_id WITH rollup
ORDER BY newSalary asc;
#3 HAVING 的使用 用来过滤数据的
#查询各个部门中最高工资比10000高的部门信息
#错误的
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000;
GROUP BY department_id;
#正确的
# 要求1： 如果过滤条件中使用了聚和函数，则必须使用having来替换where 否则报错
# 要求2：having必须声明在group by后面
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
having MAX(salary) > 10000;
#要求3：开发中，使用HAVING的前提是使用了GROUP BY
#查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门信息
#方式1 效率高于方式2
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
#或
#方式2
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10,20,30,40) and MAX(salary) > 10000
#结论：当过滤条件有聚合函数时，则此过滤函数条件必须声明在having中
# 当过滤条件没有聚合函数时，则此过滤函数条件必须声明在having中或声明在where中都可以，但是建议生命在where
/*
WHERE 与 HAVING 的对比
1. 从适用范围上来讲，having的适用范围更广
2. 如果过滤条件中没有聚合函数，这种情况下，where的执行效率要高于having
*/
#4 sql的执行原理
# 4.1 select 语句的完整结构
/*
方式1：（SQL92语法）
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
方式2 sql99：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页
*/
#4.2 sql语句的执行过程
#FROM ...,...->on -> (left/right join) -> where -> group by -> having -> SELECT ->
# distint -> order by -> limit
-- 比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：
-- SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
-- FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
-- WHERE height > 1.80 # 顺序 2
-- GROUP BY player.team_id # 顺序 3
-- HAVING num > 2 # 顺序 4
-- ORDER BY num DESC # 顺序 6
-- LIMIT 2 # 顺序 7
8.2聚合函数练习
#1.where子句可否使用组函数进行过滤?
-- no！
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;
#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT (MAX(salary) -MIN(salary)) 'difference'
FROM employees;
#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
-- SELECT e2.manager_id,e2.MIN(salary),e1.department_id
-- FROM employees e1 , employees e2
-- WHERE e1.department_id = e2.manager_id
-- GROUP BY manager_id;
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id is not null
GROUP BY manager_id
having min(salary) > 6000;
#答案
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name,l.location_id,COUNT(department_name),AVG(salary)
FROM departments d,locations l,employees e
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
order by AVG(salary)
SELECT department_name,location_id,COUNT(employee_id),AVG(salary) 'avgsalary'
FROM departments d LEFT JOIN employees e
on e.department_id = d.department_id
GROUP BY department_name,location_id
HAVING avgsalary is not null
ORDER BY avgsalary desc;
#答案
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT job_id,department_name,MIN(salary) 'minSalary'
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY job_id,department_name;
9子查询
9.1子查询讲解
#第九章 子查询 for 里面 套 for
#谁的工资比abel高
#方式一
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel';
SELECT
last_name,
salary
FROM
employees
WHERE
salary > 11000;#方式二 自连接
SELECT
e2.last_name,
e2.salary
FROM
employees e1,
employees e2
WHERE
e2.salary > e1.salary
AND e1.last_name = 'Abel';#方式三 子查询
SELECT
last_name,
salary
FROM
employees
WHERE
salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );#2. 称谓规范：外查询（主查询）、内查询（子查询）
/*
子查询（内查询）在主查询之前一次执行完成
子查询的结果被主查询使用
注意事项
子查询要包含在括号
将子查询放在比较条件的右侧
单行操作符对应单行子查询 多行操作符对应多行子查询
*/
SELECT
last_name,
salary
FROM
employees
WHERE
( SELECT salary FROM employees WHERE last_name = 'Abel' ) < salary; /*
3.子查询的分类
角度一 从内查询返回的结果的条目数
单行子查询vs多行子查询
角度二 内查询是否被执行多次
相关子查询 vs 不相关子查询
比如 :相关子查询的需求：查询工资大于本部门平均工资的员工信息
不相关子查询的需求：查询工资大于本公司平均工资的员工信息
*/ #4 单行子查询
#4.1 单行比较操作符 = > >= < <=
#需求1：查询工资大于149号员工工资的员工信息
SELECT last_name, salary, employee_id FROM employees WHERE salary > ( SELECT salary FROM employees WHERE employee_id = 149 );# 子查询的编写技巧 1 从里往外写 2 从外往里写
#需求2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT
job_id,
salary,
last_name
FROM
employees
WHERE
job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 )
AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 );#需求3：返回公司工资最少的员工的last_name,job_id和salary
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary = ( SELECT MIN( salary ) FROM employees );#需求4：查询与141号或174号员工的manager_id和department_id相同的其他员工的
# employee_id，manager_id，department_id
#方式一
SELECT
employee_id,
manager_id,
department_id
FROM
employees
WHERE
manager_id = ( SELECT manager_id
FROM employees
WHERE employee_id = 141
)
AND department_id = ( SELECT department_id
FROM employees
WHERE employee_id = 141
);
AND employee_id <> 141;
#方式二了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
SELECT manager_id,department_id
FROM employees
WHERE employee_id = 141
)
AND employee_id <> 141;
#需求5 查询最低工资大于110号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id is NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 110
);
# 显式员工的employee_id,last_name和location。其中，若员工
# department_id与location_id为1800的department_id相同，则location
# 为’Canada’，其余则为’USA’。
SELECT employee_id,last_name,case department_id
when (SELECT department_id
FROM departments
WHERE location_id = 1800) THEN 'Canda'
ELSE 'USA' end 'location'
FROM employees;
# 4.2 子查询中的空值问题
# 内查询空值不会报错，但没有结果
#4.3 非法使用子查询
#5多行子查询
#操作符 in any all some
SELECT employee_id,last_name
FROM employees
WHERE salary in
(SELECT MIN(salary)
FROM employees
GROUP BY department_id
);
#需求1：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT department_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
#需求2：返回其它job_id中比job_id为‘IT_PROG’部门全部工资低的员工的员工号、姓名、job_id 以及salary
SELECT department_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
# 需求3：查询平均工资最低的部门id
#方式一
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary)
FROM employees
GROUP BY department_id
);
#方式二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) 'avg_sal'
FROM employees
GROUP BY department_id
)
);
#5.3 空值问题
# 原因：内查询中存在NULL
# 6 相关子查询
#关联子查询： 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
#6.1
#需求1：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式一 使用相关子查询
SELECT last_name,salary,department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
#方式二 c从from中
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) tmp_avg_sal
WHERE e.salary > tmp_avg_sal.avg_sal
AND e.department_id = tmp_avg_sal.department_id;
#需求2：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
) asc;
#需求3：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
#输出这些相同id的员工的employee_id,last_name和其job_id
SELECT *
FROM job_history;
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
SELECT COUNT(*) #数目
FROM job_history j
WHERE j.employee_id = e.employee_id
);
#6.2 EXISTS 与 NOT EXISTS关键字
#需求：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式一
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
on emp.manager_id = mgr.employee_id;
#方式二
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id in (
SELECT DISTINCT manager_id
FROM employees
);
#方式三
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
SELECT *
FROM employees e2
WHERE e1.employee_id = e2.manager_id
);
#需求：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
10
11增删改查
11.1增删改查讲解
# 1. 添加数据
CREATE table if NOT EXISTS empl(
id int,
last_name VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
desc empl;
#fun 1 一条一条加数据
SELECT * FROM empl;
ALTER TABLE empl
MODIFY hire_date DATE;
# 1.1
INSERT INTO empl
VALUES (1,'tom','2000-08-26',12000); #按照声明字段的先后顺序添加
#1.2
INSERT INTO empl(id,last_name,salary)
VALUES (2,'marry',20000);
#1.3
INSERT INTO empl(id,last_name,salary)
VALUEs
(4,'俊杰',5000),
(5,'俊爱',6000);
#fun 2 将查询结果插入表中
SELECT * FROM empl;
INSERT into empl(id,last_name,salary,hire_date)
SELECT employee_id,last_name,salary,hire_date
FROM employees
WHERE department_id in (50,60);
#更新数据
#UPDATE table_reference SET col_name1=expr1 WHERE where_condition
UPDATE empl
SET hire_date = CURDATE()
WHERE id = 1;
SELECT * FROM empl;
UPDATE empl
SET hire_date = CURRENT_DATE(),salary = 999
WHERE id = 2;
#题目：将表中姓名中把包含字符a的提薪20%
UPDATE empl
SET salary = salary *1.2
WHERE last_name LIKE '%a%';
# 3删除数据
DELETE FROM empl
WHERE id = 1;
#mysql8的新特性
CREATE TABLE test1(
a int,
b int,
c int generated always as (a + b) virtual
);
SELECT * FROM test1;
INSERT INTO test1(a,b)
VALUEs(14,2);
UPDATE test1
SET a = 100
WHERE a = 1;
#5.综合案例
11.2增删改查练习
#1. 创建数据库dbtest11
create DATABASE dbtest11 CHARACTER SET 'utf8';
#2. 运行以下脚本创建表my_employees
USE dbtest11;
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);
show TABLES;
#3. 显示表my_employees的结构
desc my_employees;
#4. 向my_employees表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
INSERT INTO my_employees
VALUES
(1 ,'patel' ,'Ralph' ,'Rpatel', 895),
(2 ,'Dancs' ,'Betty' ,'Bdancs' ,860),
(3 ,'Biri' ,'Ben' ,'Bbiri' ,1100),
(4 ,'Newman' ,'Chad', 'Cnewman', 750),
(5 ,'Ropeburn', 'Audrey' ,'Aropebur', 1550);
SELECT * FROM my_employees;
#5. 向users表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
INSERT INTO users
VALUES
(1 ,'Rpatel', 10),
(2 ,'Bdancs', 10),
(3, 'Bbiri', 20),
(4 ,'Cnewman' ,30),
(5 ,'Aropebur' ,40);
SELECT * FROM users;
#6. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name='drelxer'
WHERE id = 3;
#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
set salary = 1000
WHERE salary < 900;
#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE u,m
FROM users u
JOIN my_employees m
on u.userid = m.userid
AND u.userid = 'Bbiri';
SELECT * FROM my_employees;
SELECT * FROM users;
#9. 删除my_employees、users表所有数据
DELETE
FROM users;
DELETE
FROM my_employees;
#10. 检查所作的修正
TRUNCATE TABLE my_employees;
#练习2
# 1. 使用现有数据库dbtest11
use dbtest11;
# 2. 创建表格pet
CREATE TABLE pet (
`name` VARCHAR(20),
`owner` VARCHAR(20),
species VARCHAR(20),
sex char(1),
birth YEAR,
death YEAR
);
# 3. 添加记录
INSERT INTO pet
VALUES
('bowser','diane','Dog','m','2003','2009'),
('Fluffy','harold','Cat','f','2013','2010');
INSERT INTO pet
(`name`,`owner`,species,sex,birth)
VALUES('Claws','gwen','Cat','m','2014');
INSERT INTO pet
(`name`,species,sex,birth)
VALUEs ('Chirpy','bird','f','2008'),
VALUEs ('Buffy','Dog','f','2009');
INSERT INTO pet
(`name`,`owner`,species,sex,birth)
VALUES('Fang','benny','Dog','m','2000');
SELECT * FROM pet;
# 4. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet
ADD owner_birth DATE;
# 5. 将名称为Claws的猫的主人改为kevin
#错误答案 my
-- ALTER TABLE pet
-- MODIFY `owner`= 'kevin'
-- WHERE `name` = 'Claws';
#答案
UPDATE pet
SET `owner`= 'kevin'
WHERE `name` = 'Claws';
# 6. 将没有死的狗的主人改为duck
UPDATE pet
SET `owner` = 'duck'
WHERE death is NULL
AND species = 'Dog';
SELECT * FROM pet;
# 7. 查询没有主人的宠物的名字；
SELECT `name`
FROM pet
WHERE `owner` is NULL;
# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT `name`,`owner`,death
FROM pet
WHERE death IS NOT NULL;
# 9. 删除已经死亡的狗
DELETE
FROM pet
WHERE death is not null;
# 10. 查询所有宠物信息
SELECT * FROM pet;
#练习三
# 1. 使用已有的数据库dbtest11
use dbtest11;
# 2. 创建表employee，并添加记录
CREATE table employee
(id int,
`name` VARCHAR(25),
sex CHAR(1),
tel VARCHAR(20),
addr VARCHAR(20),
salary DOUBLE(20,2)
);
INSERT INTO employee(id,`name`,sex,tel,addr,salary)VALUES
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);
# 3. 查询出薪资在1200~1300之间的员工信息。
SELECT *
FROM employee
WHERE salary BETWEEN 1200 and 1300;
# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
SELECT id,`name`,addr
FROM employee
WHERE `name` LIKE '刘%';
# 5. 将“李四”的家庭住址改为“广东韶关”
UPDATE employee
SET addr='广东韶关'
WHERE `name` = '李四';
# 6. 查询出名字中带“小”的员工
SELECT *
FROM employee
WHERE `name` like '%小%';12数据类型讲解
13约束
13.1约束讲解
#1.基础知识
#1.1 为什么需要约束 -- 为了保证数据的完整性
-- 角度一
-- 约束的字段的个数
-- 单列约束 vs 多列约束
#角度二 约束的作用范围
#列级约束：对应字段之后
#表级约束：所有字段之后
#角度三 约束的作用
#① not null（非空约束）
#② unique（唯一约束）
#③ primary key（主键约束）
#④ foreign key（外键约束）
#⑤ check （检查约束）
#⑥ default （默认值约束）
#1.4 如何添加约束
create table +
ALTER TABLE + -
# 2 如何查看表中的约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'employees';
# 3.not null（非空约束）
# 3.1 在create table添加约束
CREATE TABLE test2(
id int NOT NULL,
last_name VARCHAR(25) NOT null,
email VARCHAR(25));
DESC test2;
INSERT INTO test2
VALUES(1,'tom','dsadsad');
INSERT INTO test2
VALUES(1,NULL,'dsadsad');
#3.2 在alter table时添加约束
SELECT * FROM test2;
desc test2;
ALTER TABLE test2
MODIFY email VARCHAR(25) NOT null;
#3.3 alter table 时删除约束
alter TABLE test2
modify email VARCHAR(25) null;
#4.1 在create table添加约束
CREATE TABLE test4(
id int ,#unique, 列级约束
last_name VARCHAR(25) NOT NULL,
email VARCHAR(25),
#表级约束
#CONSTRAINT uk_test3_email UNIQUE(email)
UNIQUE(email)
);
DESC test4
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'test4';
#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
insert into test4
VALUES (1,'tom','2451477159.com'),
insert into test4
VALUES (1,'tom','2451477159.com');
#Duplicate entry '2451477159.com' for key 'test4.email'
#可以多次添加null值
#4.2 在alter table时添加约束
alter table test4
add unique(id);
alter table test4
modify last_name VARCHAR(25) unique;
desc test4;
#4.3 复合的唯一性约束
CREATE table users2(
id int,
`name` VARCHAR(25),
`password` VARCHAR(25),
#表级约束
UNIQUE(`name`,`password`)
);
desc users;
#4.4 alter table 时删除约束
-- 1.添加唯一性约束的列上也会自动创建唯一索引。
-- 2.删除唯一约束只能通过删除唯一索引的方式删除。
-- 3.删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
-- 4.如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；
-- 如果是组合列，那么默认和() 中排在第一个的列名相同。
-- 也可以是自定义唯一性约束名。
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'test4';
desc test4;
#删除唯一索引
alter table test4
drop index last_name; #或者命名的
#5 PRIMARY key
#主键约束特征 非空且唯一
#5.1 在create table时添加约束
create table test5(
id int PRIMARY key, #列级约束
last_name VARCHAR(25),
salary DOUBLE(10,2),
email VARCHAR(25)
);
create table test6(
id int,
last_name VARCHAR(25),
salary DOUBLE(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pry_test5_id PRIMARY key(id)
);
desc test5;
desc test6;
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test5';
#联合主键
create TABLE user1
(
id int,
NAME VARCHAR(25),
PASSWORD VARCHAR(25),
PRIMARY KEY(NAME,PASSWORD)
);
INSERT INTO user1
VALUES(1,'tom','abc');
INSERT INTO user1
VALUES(1,'tom1','abc');
select * from user1;
#5.2 在alter table时添加约束
create TABLE user2
(
id int,
NAME VARCHAR(25),
PASSWORD VARCHAR(25)
);
ALTER TABLE user2
ADD PRIMARY KEY(id);
#5.3 如何删除主键约束 实际开发一般不会做
ALTER TABLE user2
DROP PRIMARY key;
#6 自增长auto_increment
create TABLE user3
(
id int PRIMARY KEY auto_increment,
NAME VARCHAR(25),
PASSWORD VARCHAR(25)
);
#开发中 一旦主键作用的字段上声明有auto——increment 在T添加数据时，
#就不要给主键对应的字段去赋值了
INSERT into user3
VALUES (1,'tom','21321')
INSERT into user3(NAME,PASSWORD)
VALUES ('tom1','2132123');
INSERT into user3(id,NAME)
VALUES (0,'MARY');
INSERT into user3(id,NAME)
VALUES (8,'MARY');
INSERT into user3(id,NAME)
VALUES (10,'MARY');
SELECT * FROM user3;
# 6.2 alter中添加
create TABLE user5
(
id int PRIMARY KEY,
NAME VARCHAR(25),
PASSWORD VARCHAR(25)
);
ALTER table user5
MODIFY id int auto_increment;
#6.3 删除
ALTER TABLE user5
MODIFY id int;
#6.4 MySQL8.0新特性
# 从结果可以看出，自增变量已经持久化了。
# MySQL 8.0将自增主键的计数器持久化到 重做日志 中。
# 每次计数器发生改变，都会将其写入重做日志中。
# 如果数据库重启，InnoDB 会根据重做日志中的信息来初始化计数器的内存值。
# 7 foreing key
# 经典白听 不常用 阿里不让用
# 8 check约束
CREATE TABLE test7(
id INT,
last_name VARCHAR(25),
salary DOUBLE(10,2) CHECK(salary > 2000)
);
INSERT into test7
VALUES(1,'tom',20000);
#Check constraint 'test7_chk_1' is violated.
#添加失败
INSERT into test7
VALUES(2,'tom',1000);
# 9 default约束
#9.1 create 添加
-- CREATE TABLE test8(
-- id int PRIMARY KEY auto_increment,
-- `name` VARCHAR(25) NOT NULL,
-- salary DOUBLE(10,2) DEFAULT 2000,
-- );
CREATE TABLE test8(
id INT PRIMARY KEY auto_increment,
last_name VARCHAR(25) NOT NULL,
salary DOUBLE(10,2) DEFAULT 2000
);
# 9.2 alter 添加
ALTER TABLE test8
MODIFY salary DOUBLE(10,2) DEFAULT 2000;
#9.3 在alter table删除约束
alter TABLE test8
modify salary DOUBLE(10,2);
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name= 'test8';
#10 面试
#面试 1 、为什么建表时，加 not null default '' 或 default 0
# 答：不想让表中出现null 值。
#面试 2 、为什么不想要 null 的值
# 答: （1）不好比较。 null 是一种特殊值，
# 比较时只能用专门的is null 和is not null 来比较。碰到运算符，通常返回null 。
# （2）效率不高。影响提高索引效果。
# 因此，我们往往在建表时 not null default '' 或 default 0
#面试 3 、带 AUTO_INCREMENT 约束的字段值是从 1 开始的吗？
# 在MySQL 中，默认AUTO_INCREMENT的初始 值是1，每新增一条记录，字段值自动加1 。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第 一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一 条记录，同时指定id 值为 5 ，则以后插入的记录的 id 值就会从 6 开始往上增加。添加主键约束时，往往需要 设置字段自动增加属性。
#面试 4 、并不是每个表都可以任意选择存储引擎？ 外键约束（FOREIGN KEY）不能跨引擎使用。(主表和从表用的引擎要相同)
#MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：
#外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，
#那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
13.2约束练习
CREATE DATABASE test04_emp;
use test04_emp;
CREATE TABLE emp2(
id INT,
emp_name VARCHAR(15)
);
CREATE TABLE dept2(
id INT,
dept_name VARCHAR(15)
);
#1.向表emp2的id列中添加PRIMARY KEY约束
ALTER TABLE emp2
MODIFY id INT PRIMARY KEY;
desc emp2;
#2. 向表dept2的id列中添加PRIMARY KEY约束
ALTER TABLE dept2
MODIFY id INT PRIMARY KEY;
desc dept2;
#3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2
ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES
dept2(id);
#练习2
# 1、创建数据库test01_library
CREATE DATABASE test01_library;
# 2、创建表 books，表结构如下：
CREATE TABLE books(
id INT,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);
# 3、使用ALTER语句给books按如下要求增加相应的约束
ALTER TABLE books
MODIFY id INT(11) PRIMARY KEY auto_increment;
ALTER TABLE books
MODIFY id INT(11) NOT NULL;
ALTER TABLE books
MODIFY id INT(11) UNIQUE;
ALTER TABLE books
MODIFY `name` VARCHAR(100) NOT null;
ALTER TABLE books
MODIFY `authors` VARCHAR(100) NOT null;
ALTER TABLE books
MODIFY price FLOAT NOT null;
ALTER TABLE books
MODIFY pubdate YEAR NOT null;
ALTER TABLE books
MODIFY num INT(11) NOT null;
desc books;
#练习3
#1. 创建数据库test04_company
#2. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees
14视图
14.1视图讲解
#第十四章 视图view
#存储起来的select语句
/*
1．视图的理解/*
1.1视图，可以看作是一个虚拟表，本身是不存在存储数据的
视图的本质,就可以看作是存储起来的SELECT语句
1.2视图中SELECT语句涉及到的表，称为基表
1.3针对视图做DML（增删改）操作，会影响到对应基表中的数据。反之亦然。
1.4视图的应用场景:针对小型项目,不推荐使用视图。
针对大型项目,可以考虑使用视图。
1.5视图的优点:简化查询;控制数据的访问。
*/
CREATE DATABASE dbtest14;
USE dbtest14;
CREATE TABLE emps
AS
SELECT *
FROM atguigudb.employees;
CREATE TABLE depts
AS
SELECT *
FROM atguigudb.departments;
SELECT * FROM emps;
SELECT * FROM depts;
#2.1 针对单表
CREATE VIEW vu_empl
AS
SELECT employee_id,last_name,salary
FROM emps;
SELECT * FROM vu_empl;
#情况一 视图中的字段在基表中有对应的字段
#确定视图中字段名的方式一
CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id,last_name `name`,salary
FROM emps
WHERE salary > 8000;
SELECT * FROM vu_emp2;
#确定视图中的字段名的方式二
CREATE VIEW vu_emp3(emp_id,`name`,salary)
AS
SELECT employee_id,last_name,salary
FROM emps
WHERE salary > 8000;
SELECT * FROM vu_emp3;
#情况一 视图中的字段在基表中没有对应的字段
#比如AVG（salary）
#2.2 针对多表
CREATE VIEW vu_ed
AS
SELECT e.employee_id emp,e.last_name `name`,d.department_id dept,d.department_name dname
FROM atguigudb.employees e
JOIN atguigudb.departments d
ON e.department_id = d.department_id;
SELECT * FROM vu_ed;
CREATE VIEW vu_ed1
AS
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM atguigudb.employees e
JOIN atguigudb.departments d
ON e.department_id = d.department_id;
SELECT * FROM vu_ed1;
#2.3基于视图创建视图
CREATE VIEW vu_test
AS
SELECT *
FROM vu_ed1;
SELECT * FROM vu_test;
#3 查看视图
#3.1 查看数据库的表对象 视图对象
show tables;
#3.2 查看视图的结构
DESCRIBE vu_test;
#3.3 查看视图的属性信息
show table STATUS LIKE 'vu_test';
#3.4 查看视图的详细定义信息
show CREATE VIEW vu_test;
# 4 更新视图中的数据
# 4.1 一般情况下
#更新视图的数据 会导致基表的数据修改
#同理 更新基表中的数据，也会导致视图中的数据的修改
#删除视图中的数据 也会导致表中的数据的删除
# 4.2 不能更新视图中的数据
# 不存在的列名 avg(salary)
CREATE VIEW vu_emp_sal
AS
SELECT employee_id,last_name,salary,avg(salary) avg_sal
FROM atguigudb.employees
GROUP BY department_id;
DROP VIEW vu_emp_sal
UPDATE vu_emp_sal
SET avg_sal = 5000
WHERE department_id = 30;
#更新失败
#The target table vu_emp_sal of the UPDATE is not updatable
DELETE FROM vu_emp_sal
WHERE department_id = 30;
#The target table vu_emp_sal of the DELETE is not updatable
#5 修改视图
desc vu_emp1;
#方式一
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emps
WHERE salary > 8000;
ALTER VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,hire_date
FROM emps
WHERE salary > 8000;
# 6 删除视图
drop view vu_emp2;
drop view vu_ed1;
#因为a，b视图而存在的视图c，a，b视图删除时，c视图需要修改或者删除
# 7.视图优点
# 7.1. 操作简单
#将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。
# 7.2. 减少数据冗余
#视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
# 7.3. 数据安全
#MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表。
# 同时，MySQL 可以根据权限将用户对数据的访问限制在某些视图上， 用户不需要查询数据表，可以直接 通过视图获取数据表中的信息 。这在一定程度上保障了数据表中数据的安全性。
# 7.4. 适应灵活多变的需求
#当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较
#大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
# 7.5. 能够分解复杂的查询逻辑
#数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。
# 8 视图的不足(维护成本高 小项目不用 大项目看情况)
#如果我们在实际数据表的基础上创建了视图，那么， 如果实际数据表的结构变更了，我们就需要及时对 相关的视图进行相应的维护 。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。
#实际项目中，如果视图过多，会导致数据库维护成本的问题。
# 所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。
14.2视图练习
USE dbtest14
#1.使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID)
CREATE VIEW employee_vu
AS
SELECT last_name,employee_id,department_id
FROM atguigudb.employees;
#2. 显示视图的结构
desc employee_vu;
#3. 查询视图中的全部内容
SELECT *
FROM employee_vu;
#4. 将视图中的数据限定在部门号是80的范围内
ALTER VIEW employee_vu
AS
SELECT last_name,employee_id,department_id
FROM atguigudb.employees
WHERE department_id = 80;
15存储过程与存储函数
15.1存储过程与存储函数讲解
CREATE DATABASE dbtest15;
use dbtest15;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.departments;
SELECT * FROM employees;
SELECT * FROM departments;
#1 创建存储过程
#类型一 无参无返回值
#举例1 创建存储过程select_all_dta(),查看employees表的所有数据
delimiter $
CREATE PROCEDURE select_all_data()
BEGIN
select * FROM employees;
END $
delimiter ;
#2 存储过程的调用
CALL select_all_data();
#举例2：创建存储过程avg_employee_salary()，返回emps中所有员工的平均工资
delimiter $
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) FROM employees;
END $
delimiter;
CALL avg_employee_salary;
#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。
delimiter $
CREATE PROCEDURE show_max_salary()
BEGIN
SELECT max(salary) max_sal
FROM employees;
END $
delimiter ;
CALL show_max_salary();
#类型二 带out
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms” 输出:
desc employees;
delimiter $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE(10,2))
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END $
delimiter ;
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;
#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。
delimiter $
CREATE PROCEDURE show_someone_salary1(IN empname VARCHAR(20))
BEGIN
SELECT salary,last_name
FROM employees
WHERE last_name = empname;
END $
delimiter;
SELECT * FROM employees;
#调用方式一
CALL show_someone_salary1('King');
#调用方式二
SET @empname := 'King';
CALL show_someone_salary1(@empname);
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，
# 并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。
delimiter $
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE(10,2))
BEGIN
SELECT salary INTO empsalary
FROM employees
WHERE last_name = empname;
END $
delimiter;
SET @empname := 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，
# 并用INOUT参数“empname”输入员工姓名，输出领导的姓名。
delimiter $
CREATE PROCEDURE show_mgr_name3(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name
FROM employees
WHERE employee_id = (
SELECT manager_id
FROM employees
WHERE last_name = empname
);
END $
delimiter ;
set @empname := 'Abel';
CALL show_mgr_name3(@empname);
#2 存储函数
#举例1： 创建存储函数，名称为email_by_name()，参数定义为空，
# 该函数查询Abel的email，并返回，数据类型为字符串型。
DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END $
DELIMITER ;
SELECT email_by_name();
#举例2： 创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，
#并返回，数据类型为字符串型。
# 在创建函数前声明
#保证会成功
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN(SELECT email FROM employees WHERE employee_id = emp_id);
END $
delimiter ;
SELECT email_by_id(100);
#举例3： 创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，
#并返回，数据类型为整型。
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
RETURN(SELECT count(*) FROM employees WHERE department_id = dept_id);
END $
delimiter ;
SELECT count_by_id(100);
#3 存储过程 函数的查看
#3.1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE avg_employee_salary;
SHOW CREATE FUNCTION count_by_id;
#3.2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS LIKE 'avg_employee_salary';
SHOW FUNCTION STATUS LIKE 'count_by_id';
#3.3 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.ROUTINES
WHERE routine_name = 'avg_employee_salary';
SELECT * FROM information_schema.ROUTINES
WHERE routine_name = 'count_by_id' AND routine_type = 'FUNCTION';
#4 修改
ALTER FUNCTION count_by_id
SQL SECURITY INVOKER
COMMENT '部门人口数量';
#5 删除
DROP FUNCTION if EXISTS email_by_id;
#6 优缺点
#6.1 优点
-- 1、存储过程可以一次编译多次使用。
-- 2、可以减少开发工作量
-- 3、存储过程的安全性强。 我们在设定存储过程的时候可以 设置对用户的使用权限 ，
-- 4、可以减少网络传输量。
-- 5、良好的封装性。
#6.2 缺点
-- 1、可移植性差。
-- 2、调试困难。
-- 3、存储过程的版本管理很困难。
-- 4、它不适合高并发的场景。15.2存储过程与存储函数练习
CREATE DATABASE test15_pro_func;
USE test15_pro_func;
#存储过程练习
#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
CREATE TABLE admin(
id INT PRIMARY KEY auto_increment,
`name` VARCHAR(25) NOT NULL,
`password` VARCHAR(25) NOT null
);
delimiter $
CREATE PROCEDURE insert_user(IN username VARCHAR(25),IN userpassword VARCHAR(25))
BEGIN
INSERT INTO admin(`name`,`password`)
VALUE (username,userpassword);
END$
delimiter ;
CALL insert_user('tom','123qweasdzxc');
SELECT * FROM admin;
#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话
#准备工作
CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');
SELECT * FROM beauty;
delimiter $
CREATE PROCEDURE get_phone(IN bea_id INT,OUT bea_NAME VARCHAR(15),OUT bea_phone VARCHAR(15))
BEGIN
SELECT NAME,phone INTO bea_NAME,bea_phone
FROM beauty
WHERE id = bea_id;
END$
delimiter ;
CALL get_phone(2,@bea_NAME,@bea_phone);
SELECT @bea_NAME;
SELECT @bea_phone
#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小
delimiter $
CREATE PROCEDURE date_diff1(IN bea_birth_a DATETIME,IN bea_birth_b DATETIME,OUT inter INT)
BEGIN
SELECT DATEDIFF(bea_birth_a,bea_birth_b) INTO inter;
END$
delimiter ;
CALL date_diff1('1982-02-12','1991-11-12',@inter);
SELECT @inter;
#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
DELIMITER //
CREATE PROCEDURE format_date(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END //
DELIMITER ;
CALL format_date('1982-02-12',@strdate);
SELECT @strdate;
#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录
#创建带inout模式参数的存储过程
DELIMITER //
CREATE PROCEDURE beauty_limit(IN startIndex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startIndex,size;
END //
DELIMITER ;
CALL beauty_limit(1,3);
#6. 传入a和b两个值，最终a和b都翻倍并返回
delimiter $
CREATE PROCEDURE add_values(INOUT a INT,INOUT b INT)
BEGIN
SELECT a * 2 as `a`,b * 2 as `b`
FROM DUAL;
END $
delimiter ;
SET @a = 3,@b = 4;
CALL add_values(@a,@b);
SELECT @a,@b;
#7. 删除题目5的存储过程
DROP PROCEDURE beauty_limit;
#8. 查看题目6中存储过程的信息
SELECT * FROM information_schema.ROUTINES
WHERE routine_name = 'add_values';
SHOW PROCEDURE STATUS like 'add_values';
#存储函数练习
#0. 准备工作
USE test15_pro_func;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
return(SELECT count(*) FROM employees);
END $
delimiter ;
SELECT get_count();
#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $
CREATE FUNCTION ename_salary(emp_name VARCHAR(25))
RETURNS DOUBLE(10,2)
BEGIN
RETURN(SELECT salary
FROM employees
WHERE last_name = emp_name);
END $
delimiter ;
SELECT ename_salary('Abel');
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
delimiter $
CREATE FUNCTION dept_sal2(dept_name VARCHAR(25)) RETURNS DOUBLE(10,2)
BEGIN
RETURN(SELECT AVG(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = dept_name
);
END $
delimiter ;
SELECT dept_sal2('Marketing');
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
RETURN (SELECT num1 + num2 );
END //
DELIMITER ;
SET @num1 := 1.2,@num2 = 3.2;
SELECT add_float(@num1,@num2);
韩顺平索引
韩顺平索引讲解
#索引：
use atguigudb;
SELECT *
FROM employees
where last_name = 'Abel'; #0.031
#创建索引
#创建索引后 文件会变大 索引本身会占用空间
CREATE INDEX emp_index ON employees(employee_id);
CREATE INDEX empname_index ON employees(last_name);
SELECT *
FROM employees
where last_name = 'Abel'; # 0.032
#主键索引
#唯一索引
#普通索引
#全文索引(一般开发不使用mysql自带的全文索引，而是使用全文搜索solr和elasticsearch（es）)
#创建索引
CREATE TABLE t25(
id int,
`name` VARCHAR(25)
);
#查询表是否有索引
SHOW indexes FROM t25;
#添加索引
#添加唯一索引
CREATE UNIQUE INDEX id_index ON t25(id);
#添加普通索引方式1
CREATE INDEX id_index ON t25(id);
#如何选择
#1 如果某列的值 是不会重复的，则优先考虑使用unique索引 否则使用普通索引
#天添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index(id);
#添加主键索引
CREATE TABLE t26(
id INT,
`name` VARCHAR(25));
ALTER TABLE t26 ADD PRIMARY KEY(id);
SHOW INDEX FROM t25;
SHOW INDEX FROM t26;
#删除索引
DROP INDEX id_index on t25;
#删除主键索引
alter TABLE t26 DROP PRIMARY KEY
#修改索引 （先删除 在添加新的索引）
#查询索引
#方式一
SHOW INDEX FROM t25;
#方式二
SHOW indexes FROM t25;
#方式三
SHOW KEYS FROM t25;
#方式四
desc t25;
#那些列上适合使用索引
#1 较频繁的作为查询条件字段应该创建索引
#2 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
#3 更新非常频繁的字段不适合创建索引
#4 不会出现在where子句中字段不该创建索引韩顺平索引练习
#练习3
CREATE TABLE `orders`(
id INT,
goods VARCHAR(25),
orderPer varchar(25),
number INT
);
ALTER TABLE orders ADD PRIMARY KEY(id);
CREATE TABLE `orders2`(
id INT PRIMARY KEY,
goods VARCHAR(25),
orderPer varchar(25),
number INT
);
SHOW INDEX FROM orders
SHOW INDEX FROM orders2
#练习2
CREATE TABLE menu(
id INT,
menuName VARCHAR(25),
chief VARCHAR(25),
idcard INT(20),
price DOUBLE(10,2)
);
ALTER TABLE menu ADD PRIMARY KEY(id);
ALTER TABLE menu ADD UNIQUE(idcard);
SHOW index FROM menu;
CREATE TABLE menu1(
id INT PRIMARY KEY,
menuName VARCHAR(25),
chief VARCHAR(25),
idcard INT(20) UNIQUE,
price DOUBLE(10,2)
);
SHOW index FROM menu1;
#练习3
CREATE TABLE sportman(
id INT PRIMARY KEY,
`name` VARCHAR(25),
speciality VARCHAR(25)
);
CREATE INDEX index_name ON sportman(`name`);
ALTER TABLE sportman ADD INDEX speciality_index (speciality);
SHOW INDEX FROM sportman;
韩顺平存储引擎
#查看所有的存储引擎
show ENGINEs;
#1 innodb 一直使用的 支持事务 支持外键
#2 myisal
# 如果不需要事务 处理的只是基本的增删改查 就用这个myisam 速度快
CREATE TABLE t29(
id int,
`name` VARCHAR(25)) ENGINE myisam;
START transaction
SAVEPOINT a;
INSERT INTO t29 VALUES (1,'jack');
SELECT * from t29;
rollback to a;
#3 memory
# 3.1 数据存储在内存中
# 3.2 执行速度很快（没有io读写）
# 3.3 默认支持索引（hash表）
CREATE TABLE t30(
id int,
`name` VARCHAR(25)) ENGINE memory;
INSERT INTO t30 VALUES (1,'jack');
INSERT INTO t30 VALUES (2,'tom');
INSERT INTO t30 VALUES (3,'mary');
SELECT * from t30;
#memory 因为存储在内存中 所以mysql服务重启后 表内数据不存在 只保留列名
韩顺平隔离级别
#1 事务隔离级别介绍
# 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
#脏读：一个事务读取另外一个事务尚未提交的修改时
# 有可能回滚 放弃提交
#不可重复读：同一查询在同一事务中多次进行 由于其他提交事务所做的修改或删除
#每次返回不同的结果集 alter delete update
# delete alter c1还在事务还在进行，c2已提交的事务 让c1读走了
#幻读：同一查询在同一事务中多次进行 由于其他提交事务所做的插入操作 每次返回不同的结果集 insert
# 同上 不过是insert
CREATE TABLE account(
id INT,
`name` VARCHAR(25),
money DOUBLE(10,2)
);
#查看当前会话隔离级别
SELECT @@transaction_isolation;
#查看系统当前隔离级别
SELECT @@global.TRANSACTION_isolation;
#设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
#设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [级别]
#默认为可重复读 一般不去修改
#可以在my.ini里面设置默认的隔离级别，修改之后重新启动mysql服务
#2 事务acid特性
# 2.1 原子性
# 2.2 一致性
# 2.3 隔离性
# 2.4 持久性
start TRANSACTION
use atguigudb;
update dog set name = jack WHERE id = 1;韩顺平事务
#事务的一个重要的概念和具体操作
#1 创建一个表
CREATE TABLE t28
(
id INT,
`name` VARCHAR(25)
);
#2 开始事务
START TRANSACTION
#3 设置保存点
SAVEPOINT a
#执行dml操作
INSERT INTO t28
VALUES (100,'tom');
SAVEPOINT b
INSERT INTO t28
VALUES (101,'tom1');
#回退到b
ROLLBACK TO b;
SELECT * FROM t28;
ROLLBACK to a;
#如果这样
rollback #表示直接回到事务最开始的状态
COMMIT;
#commit结束事务点 删除之前所有的保存点 savepoint
#4 事务细节讨论
#4.1 如果不开始事务 默认情况下 dml操作是自动提交的 不能回滚
INSERT INTO t28
VALUES (103,'tom');
SELECT * FROM t28;
#4.2 如果开始一个事务 你没有创建保存点 你可以执行rollback
#默认就是回退到你事务开始的状态
START TRANSACTION;
INSERT INTO t28
VALUES (200,'tom');
INSERT INTO t28
VALUES (103,'tom');
ROLLBACK;
commit
#4.3 可以在事务没有提交时，创建多个保存点
#4.4 你可以在事务乜有提交前 选择回退到哪个保存点
#4.5 innodb存储引擎支持事务，myisam不支持
#4.6 开始一个事务 start transction / set autocommit=off
SET autocommit=off;
韩顺平用户管理讲解
韩顺平用户讲解
#因为项目开发中 可以根据不同的开发人员 给他不同的权限
#1. 创建新用户 'xxd_myself'@'localhost' 用户的完整信息 'xxd_myself 用户名 localhost 登录的ip
CREATE USER 'xxd_myself'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM mysql.user;
SELECT `host`,`user`,authentication_string
FROM mysql.user;
#删除用户
DROP USER 'xxd_myself'@'localhost'
# 3 登录用户
#不同的数据库用户 操作的库和表不相同
# 修改密码
SET PASSWORD = PASSWORD('abcdefg');
set PASSWORD FOR 'xxd_myself'@'localhost' = PASSWORD('abcdef');
#韩顺平用户管理练习
#用户管理练习题
#1 创建一个用户
CREATE user 'xxd'@'localhost' IDENTIFIED by '123';
#2 创建库和表 使用root创建
CREATE DATABASE testdb;
CREATE table news(
id int,
`name` VARCHAR(25));
INSERT INTO news VALUES(1,'beijign');
#3 给用户分配权限
GRANT SELECT,INSERT
ON testdb.news
to 'xxd'@'localhost';
因为版本不同 代码也不同韩顺平作业2,3
#2
DESC employees;
DESC departments;
#3.1
SELECT department_name
FROM departments;
#3.2
SELECT last_name,salary*(1 + IFNULL(commission_pct,0)) * 12 'year salary'
FROM employees;韩顺平作业4
#4.1
SELECT last_name,salary
FROM employees
WHERE salary > 2850;
#4.2
SELECT employee_id,salary
FROM employees
WHERE salary BETWEEN 1500 AND 2850;
#4.3
SELECT last_name,department_id
FROM employees
WHERE employee_id = 100;
#4.4
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN (10,30)
AND salary > 1500;
#4.5
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;韩顺平作业5
#5.1
SELECT last_name,job_id,hire_date
FROM employees
WHERE hire_date BETWEEN '1988-02-01' AND '2000-05-01'
ORDER BY hire_date;
#>= '1988-02-01' AND hire_date <= '2000-05-01'
#5.2
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary desc;韩顺平作业6
#6.1
SELECT *
FROM employees
WHERE department_id = 30;
#6.2
SELECT last_name,employee_id,department_id
FROM employees
WHERE job_id = 'IT_PROG';
#6.3
SELECT *
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
);
#6.4
SELECT *
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) * 1.6
FROM employees
);
#6.5
SELECT e1.department_id,e1.last_name,e1.employee_id,e2.manager_id
FROM employees e1 left JOIN employees e2
ON (e1.department_id = 10
AND e1.employee_id = e2.manager_id
)OR e1.department_id = 20;
#6.6
#6.7
SELECT DISTINCT job_id
FROM employees
WHERE commission_pct IS NOT NULL;
#6.8
SELECT last_name,employee_id,commission_pct
FROM employees
WHERE (salary * IFNULL(commission_pct,0)) < 1000;
#6.9 wei
#老韩提示
SELECT LAST_DAY(now()) #返回当前月份最后一天
SELECT *
FROM employees
WHERE LAST_DAY(hire_date) -2 = hire_date;
#6.10 wei 入职时间大于十二年
SELECT last_name,employee_id,hire_date
FROM employees
WHERE DATE_ADD(hire_date,INTERVAL 12 YEAR) < NOW();
#6.11 wei
SELECT CONCAT(LOWER(SUBSTRING(last_name,1,1)),substring(last_name,2))
FROM employees;
#6.12
SELECT last_name,employee_id
FROM employees
WHERE last_name LIKE '_____';
#6.13
SELECT last_name
FROM employees
WHERE last_name NOT LIKE '%r%';
#6.14
SELECT SUBSTRING(last_name,1,3)
FROM employees;
#6.15 wei
SELECT REPLACE(last_name,'A','a')
FROM employees;
#6.16
SELECT last_name,hire_date
FROM employees
WHERE date_add(hire_date,interval 10 year) < NOW();
#6.17
SELECT *
FROM employees
ORDER BY last_name;
#6.18
SELECT last_name,hire_date
FROM employees
ORDER BY hire_date;
#6.19
SELECT last_name,job_id,salary
FROM employees
ORDER BY job_id,salary desc;
#6.20 wei
SELECT last_name,concat(YEAR(hire_date),' ',MONTH(hire_date))
FROM employees
ORDER BY MONTH(hire_date),year(hire_date) ;
#6.21 wei
SELECT last_name,floor(salary / 30)
FROM employees;
#6.22
SELECT hire_date,last_name
FROM employees
WHERE MONTH(hire_date) = 2;
#6.23
SELECT last_name,DATEDIFF(now(),hire_date)'天数'
FROM employees;
#6.24
SELECT last_name
FROM employees
WHERE last_name LIKE '%A%';
#6.25
SELECT last_name,concat(round(DATEDIFF(NOW(),hire_date) / 365) ,'年',FLOOR((DATEDIFF(NOW(),hire_date) % 365) / 31) ,'月')
FROM employees;
韩顺平作业7
#7.1 wei
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 1;
#7.2
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#7.3
SELECT e1.last_name '员工',e1.hire_date '员工入职时间',e2.last_name '上级',e2.hire_date '上级入职时间'
FROM employees e1,employees e2
WHERE e1.hire_date > e2.hire_date
AND e1.manager_id = e2.employee_id
AND e1.last_name LIKE '%a%';
#7.4 列出部门名称和这些部门的员工信息 同时列出那些没有员工的部门
/*
显示所有部门 因此使用外连接（左外连接）
*/
SELECT department_name,e.*
FROM departments d
left JOIN employees e
ON d.department_id = e.employee_id;
#7.5 列出所有（特有信息）
#7.6
SELECT job_id
FROM employees
GROUP BY job_id
HAVING MIN(salary) < 3500;
#7.7
SELECT last_name
FROM employees
WHERE job_id = 'IT_PROG';
#7.8
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#7.9
SELECT last_name,job_id
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE last_name = 'Abel'
);
#7.10 列出薪水高于部门30工作的所有
SELECT salary,last_name
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE department_id = 30
);
#7.11 列出在每个部门工作的员工数量 平均工资 平均服务期限(wei)
SELECT COUNT(*) AS '员工数量',AVG(salary) AS '平均工资' ,
round(AVG((DATEDIFF(NOW(),hire_date)) /365 ),2) AS '平均年限(年)',department_id
FROM employees
GROUP BY department_id;
#7.12列出所有员工的姓名 部门名称和工资
SELECT e.last_name,d.department_name,e.salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
#7.13列出所有部门的详细信息和部门人数(wei)
#1. 先得出各部门的人数，把下面的结果看成临时表 和 departments 表联合查询
SELECT COUNT(*) as c,department_id
FROM employees
GROUP BY department_id
#2.
SELECT d.*,tmp.c
FROM departments d,(
SELECT COUNT(*) as c,department_id
FROM employees
GROUP BY department_id
) tmp
WHERE d.department_id = tmp.department_id;
#7.14列出各种工作的最低工资
SELECT MIN(salary),job_id
FROM employees
GROUP BY job_id;
#7.15列出管理者的最低薪水
SELECT MIN(leader.salary),leader.last_name
FROM employees leader,employees emp
WHERE leader.department_id = emp.manager_id;
#7.16列出所有员工的年工资 按年薪从低到高排序
SELECT (salary * (1 + IFNULL(commission_pct,0)) * 12) as year_sal
FROM employees
ORDER BY year_sal;韩顺平作业8
#8.1
#创建表 系
CREATE TABLE DEPARTMENT(
departmrntid VARCHAR(32) PRIMARY KEY,
deptname varchar(32) UNIQUE NOT NULL
);
#创建表 班 class
CREATE TABLE `class`(
classid INT PRIMARY KEY,
`subject` VARCHAR(32) NOT NULL DEFAULT '',
depename VARCHAR(32),
enrolltime INT NOT NULL DEFAULT 2000,
num INT NOT NULL DEFAULT 0,
FOREIGN KEY (depename) REFERENCES DEPARTMENT(deptname)
);
#创建表 学生
CREATE TABLE students(
studentid INT PRIMARY key,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
classid INT,
FOREIGN KEY (classid) REFERENCES `class`(classid)
);
#添加测试数据
INSERT INTO department VALUES ('001','数学');
INSERT INTO department VALUES ('002','计算机');
INSERT INTO department VALUES ('003','化学');
INSERT INTO department VALUES ('004','中文');
INSERT INTO department VALUES ('005','经济');
INSERT INTO class VALUES (101,'软件','计算机',1995,20);
INSERT INTO class VALUES (102,'微电子','计算机',1996,30);
INSERT INTO class VALUES (111,'无机化学','化学',1991,29);
INSERT INTO class VALUES (112,'高分子化学','化学',1992,24);
INSERT INTO class VALUES (121,'统计数学','数学',1990,21);
INSERT INTO class VALUES (131,'现代语言','中文',1995,27);
INSERT INTO class VALUES (141,'国际贸易','经济',1999,12);
INSERT INTO class VALUES (142,'国际贸易','经济',1993,20);
INSERT INTO students VALUES (8101,'张三',18,101);
INSERT INTO students VALUES (8102,'李四',19,121);
INSERT INTO students VALUES (8103,'王五',18,141);
INSERT INTO students VALUES (8104,'赵六',16,111);
INSERT INTO students VALUES (8105,'李可',17,102);
INSERT INTO students VALUES (8106,'赵龙',18,101);
SELECT * FROM department
SELECT * FROM class;
SELECT * FROM students;
#找出所有行李的学生
SELECT *
FROM students
WHERE `name` LIKE '李%';
#列出所有开设超过一个专业的系的名字
class depename
DEPARTMENT deptname
SELECT depename
FROM class
GROUP BY depename
HAVING count(*) <> 1;
#列出人数大于等于30的系的编号和名字
SELECT departmrntid,depename
FROM DEPARTMENT d,(
SELECT sum(num) as nums,depename
FROM class
GROUP BY depename
HAVING nums >= 30
) tmp
WHERE d.deptname = tmp.depename;
#给张三办理退学
start TRANSACTION;
#(应该先让班级减一，再去删除张三的信息，因为如果先删除张三信息，在对班级人数减一的时候就会找不到张三的班级，
#而无法对张三的班级人数减一)
#班级人数减一
UPDATE class set num = num - 1
WHERE classid = (
SELECT classid
FROM students
WHERE `name` = '张三'
);
#删除张三信息
DELETE
FROM students
WHERE `name` = '张三';
COMMIT;
SELECT * FROM students;
526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
