-- 事务管理:只能作用在DML语句上,如果在一个事务中使用delete删除所有记录,可以找回.

-- 使用delete删除后可以用COMMIT和ROLLBACK找回数据,使用truncate后就找不回来了.

-- delete、truncate、drop的区别 √

delete、truncate、只是删除表的记录,而drop会直接删除表.

• DQL3-排序查询
• 对数据按一定规则进行排序操作，使用order by 子句
• Select * form 表【where 子句】 order by 字段[asc|desc]
• Order  by 必须出现在select 中最后一个子句
• ASC用来指定升序降序（），desc用来指定降序升序
• 多个列排序：首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推
• 多列排序时，不管正序还是倒序，每个列需要单独设置排序方式
• Select * from 表[where 子句] order by 字段 [asc|desc],字段【asc|desc】……

主键约束： primary key （默认就是唯一非空的）

外键约束：用于在两个表之间建立关系，需要指定引用主表的那一列

• 如果表A的主键是表B中的字段，则该字段称为表B的外键，表A（主表），表B（从表).
• 外键是用来实现参照完整性的，主表更新时从表也更新，主表删除时，如果从表有匹配的项，删除失败

唯一约束：unique

非空约束： not null

表与表之间的关系

数据库都是关系型的数据库，存的是实体之间的关系

实体的关系总结起来就有三种关系：

一对多：

客户和订单：一个客户可以生产多个订单，一个订单只能属于是某一个客户。

部门和员工：一个部门下可以有多个员工，一个员工只能属于某一个部门。

多对多：

学生和课程：一个学生可以选择多门课程，一门课程可以被多个学生选择。

订单和商品：一个订单中包含多个商品，一个商品也可以出现多个订单中。

一对一：

公司与地址，一个公司只能有一个注册地址，一个地址也只能被一个公司注册。

外键关联的话，如果主表中的数据，从表用了的，就删除不了、

代码大杂烩，按照DDL DML TCL DQL DCL顺序进行

数据定义语言DDL CREATE,ALTER,DROP
-- 一、数据库相关的DDL
-- 1.创建数据库
CREATE DATABASE mybase;
-- 2创建数据库并指定字符集
CREATE DATABASE mybase1 CHARACTER SET UTF8;
-- 3、查看所有数据库
SHOW DATABASES;
-- 4查看当前使用的数据库
SELECT  DATABASE();
-- 5、修改数据库
ALTER DATABASE mybase CHARACTER SET UTF8;
-- 6、删除数据库
DROP DATABASE mybase1;
-- 切换数据库
USE mybase;
-- 二表相关DDL
-- 创建表
CREATE TABLE exam(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20), 
English INT,
Chinese INT,
Math int
);
-- 查看数据库中的所有表
SHOW TABLES;
-- 查看表的结构
DESC exam;
-- 表的删除
DROP TABLE exam;
-- 5、表的修改（基于exam表）
-- 5.1 添加列
ALTER TABLE exam ADD History INT NOT NULL;
DESCmybase exam;
-- 5.2 修改列的类型长度约束
ALTER TABLE exam MODIFY History DOUBLE(7,2);
-- 5.3 修改表的列名
ALTER TABLE exam CHANGE History Physics INT NOT NULL;
-- 5.4 修改表名
RENAME TABLE exam TO score;
DESC score;
-- 5.5 修改表的字符集
ALTER TABLE score CHARACTER SET GBK;
-- 5,6 删除列
ALTER TABLE score DROP Physics;
-- 三、练习：创建emp
CREATE TABLE emp(
yuangong INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal DOUBLE(7,2),
COMMIT DOUBLE(5,2),
DEPTNO INT NOT NULL 
);
-- 数据操作语言DML （update， insert ,delete）
-- 1、插入部分列
INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,'ranxiao',90,90,90);
INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(2,'xucheng',100,100,100);
-- 2、插入所有列
INSERT INTO score VALUES(3,'guiguizi',89,57,97);
-- 3、修改记录
UPDATE score SET Chinese = 99; -- 全表修改
UPDATE score SET Math = 100 WHERE id = '1' ;  -- where是限制条件  
-- 4、删除记录
DELETE FROM score WHERE id = '2';
DELETE FROM  score;
/*
DELETE 与 TRUNCATE的区别
TRUNCATE TABLE 删除表的记录；是将整个表删除掉，重新创建一个新的表，属于DDL
DELETE FROM 删除表的记录；一条一条进行删除，delete 。
*/
INSERT INTO score VALUES(3,'shu',80,88,90);
DELETE FROM score;
INSERT INTO score VALUES(NULL,'sushi',100,100,100); -- 不会清空 auto_increment 的值
TRUNCATE TABLE score;
INSERT INTO score VALUES(NULL,'dufu',234,463,575); -- 会清空 auto_increment的值
事务管理：只能作用在DML语句上，如果在一个事务中使用delete删除所有记录，可以找回
使用delete删除后可以用commit 和rollback 找回数据库，使用turncate后就找不回来了
COMMIT
ROLLBACK  
--delete、truncate、drop的区别
DELETE 、truncate、只是删除表的记录，而drop会直接删除表
-- 数据控制语言DCl (GRANT,REVOKE)
-- 主要为用户授予和撤销权限
-- 1 创建用户：create user 用户名@IP identified by 密码
CREATE USER apple@localhost IDENTIFIED BY '123456';
-- 2、给用户授权：grank 权限1、权限2、。。。。权限n on 数据库.* to 用户名@ip;
GRANT SELECT ,DROP ON mysql.`*` TO apple@localhost;
-- 3.撤销权限，revoke 权限1、权限2、.....权限n on 数据库.* from 用户名@iP; 
REVOKE SELECT  ON mysql.`*` FROM apple@localhost;
-- 4、查看用户的权限：show grants for 用户名@ip 
SHOW GRANTS FOR apple@localhost;
--  5、删除用户：drop user 用户名@IP;
DROP USER apple@localhost;
数据查询语言 DQL SELECT (DATABASE QUERY langurg)
-- 数据查询语言DQl（select）
CREATE TABLE exam(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
English INT,
Chinese INT,
Math int
);
插入列
INSERT INTO exam VALUES(null,'xucheng',234,576,68758);
INSERT INTO exam VALUES(NULL,'gupei',325,456,57);
INSERT INTO exam VALUES(NULL,'大哥',12,43,45);
INSERT INTO exam VALUES(NULL,'小花',88,78,90);
INSERT INTO exam VALUES(NULL,'小光',78,78,67);
INSERT INTO exam VALUES(NULL,'小蓝',98,89,89);
-- 1.全表查询: SELECT * FROM 表;
SELECT * FROM  exam;
-- 2.查询部分字段: SELECT 字段,字段,字段... FROM 表;
SELECT NAME,English,Math FROM exam;
-- 3.过滤重复字段行: SELECT [DISTINCT] *|列名 FROM 表;
SELECT DISTINCT Math FROM exam;
SELECT DISTINCT NAME,Math FROM exam;
-- 4.查询字段起别名: SELECT 字段 AS 新字段名,字段 新字段名 FROM 表;
SELECT NAME ,English AS English_sorce FROM exam;
SELECT NAME ,Chinese AS 语文成绩 FROM exam;
-- 5.查询指定字段
SELECT NAME,English, Chinese FROM exam WHERE NAME='大哥';
SELECT NAME, English FROM exam WHERE NAME = 'gupei';
-- 6.使用表达式+、-、*、/
SELECT id,NAME,English-20 AS _English FROM exam;
SELECT NAME ,English + Math + Chinese FROM exam;
-- 7.模糊查询
SELECT * FROM exam WHERE NAME LIKE '小_';
SELECT * FROM exam WHERE NAME LIKE '大_';
SELECT * FROM exam WHERE NAME LIKE '%%';
-- 8.使用and,or
SELECT * FROM exam WHERE English > 90 AND Chinese>90;
SELECT * from exam WHERE English > 90 OR Math<89;
-- 9.使用in,not in
SELECT * FROM exam WHERE id=2 OR id=3 or id =4;
SELECT * FROM exam WHERE id IN(2,3,4);
SELECT * FROM exam WHERE id  NOT IN(2,3,4);
-- 10.使用between...and []
SELECT * FROM exam WHERE English BETWEEN 90 AND 100;
-- 11. is null,is not null
INSERT INTO exam(id,NAME) VALUES(NULL,NULL);
SELECT * FROM exam WHERE NAME IS NULL;
SELECT * FROM exam WHERE NAME IS NOT NULL;
-- 11.排序查询
SELECT * FROM exam ORDER BY Chinese ASC; -- 升序 先小后大
SELECT * FROM exam ORDER BY Chinese DESC; -- 降序 先大后小
SELECT * FROM exam ORDER BY English DESC,Chinese DESC; -- 如果英语成绩相同，按照汉语成绩降序排列
SELECT * FROM exam WHERE NAME LIKE '小%' ORDER BY English ASC;
-- 12.聚合函数
SELECT SUM(English+ Chinese + Math) FROM exam ;
SELECT COUNT(id) FROM exam WHERE NAME IS NOT NULL; 
SELECT MAX(English) FROM exam;
SELECT MIN(English) FROM exam;
SELECT AVG(English) FROM exam;
-- 分组查询和分页查询 GROUP BY , LIMIT 
-- 分组查询
CREATE TABLE empp(
empno INT(11) PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT(11),
hiredate DATE,
sal DOUBLE(7,2),
COMMIT DOUBLE(7,2),
deptno INT NOT NULL
)
INSERT INTO test  VALUES
(1002,'白展堂','clerk',1001,'1983-05-09',7000.00,200.00,10),
(1003,'李大嘴','clerk',1002,'1980-07-08',8000.00,100.00,10),
(1004,'吕秀才','clerk',1002,'1985-11-12',4000.00,null,10),
(1005,'郭芙蓉','clerk',1002,'1985-03-04',4000.00,null,10),
(2001,'胡一菲','leader',null,'1994-03-04',15000.00,NULL,20),
(2002,'陈美嘉','manger',2001,'1993-05-24',10000.00,300.00,20),
(2003,'吕子乔','clerk',2002,'1995-05-19',7300.00,100.00,20),
(2004,'张伟','clerk',2002,'1994-10-12',8000.00,500.00,20),
(2005,'曾小贤','clerk',2002,'1993-05-10',9000.00,700.00,20),
(3001,'刘梅','leader',null,'1968-08-08',13000.00,NULL,30),
(3002,'夏冬梅','manger',3001,'1968-09-21',10000.00,600.00,30),
(3003,'夏雪','clerk',3002,'1989-09-21',8000.00,300.00,30),
(3004,'张一山','clerk',3002,'1991-06-16',88000.00,200.00,30);
查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM empp GROUP BY deptno;
SELECT * FROM empp GROUP BY deptno;
SELECT deptno,AVG(sal) FROM empp GROUP BY deptno;
查询每个职位的最高工资和最低工资
SELECT job,MAX(sal),MIN(sal) FROM empp GROUP BY job;
查询每个部门每种职位的最高工资
SELECT deptno,job,MAX(sal) FROM empp GROUP BY deptno,job;
查询每个部门的最高薪水，最有最高薪水大于15000的记录才被输出显示
SELECT deptno,job,MAX(sal) AS max_sal FROM empp  GROUP BY deptno HAVING MAX(sal)>=15000;
查询每个部门的平均工资大于9000的
SELECT deptno,AVG(sal) AS pingjun FROM empp GROUP BY deptno HAVING AVG(sal) >=8000;
-- 6.Havaing子句与where子句的区别
(1)where是用来过滤记录的,HAVING是用来过滤分组的
(2)过滤的时机不相同,先过滤Where后过滤Having.  
(3)WHERE是在查询表时逐行过滤以选取满足条件的记录
(4)having是在数据查询后并且分完组后对分组进行过滤的
(5)HAVING必须跟在group BY 后
(6)查询语句执行顺序:5select 1from 2where 3group by 4having 6order by
二 、 分页查询
从第几页开始多少页（下标从0开始）
SELECT * FROM empp LIMIT 0,3;
每页几条第几页==需要查看第几页-1 * 第二个参数
SELECT * FROM empp;
SELECT * FROM empp LIMIT 5 ,5; -- (3-1) 5条
每页两条第二页
SELECT * FROM empp LIMIT  2,3;
查看工资最高的前十个职员的信息
SELECT * FROM empp ORDER BY sal DESC LIMIT 0,10;
CREATE TABLE test(
empno INT(11) PRIMARY KEY AUTO_INCREMENT, -- 主键约束
ename VARCHAR(20),
job VARCHAR(20),
mgr INT(11),
hiredate DATE,
sal DOUBLE(7,2),
COMMIT DOUBLE(7,2),
deptno INT NOT NULL -- 非空约束
);
CREATE TABLE exam(
id INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR(20),
Chinese INT(11),
Math INT,
English INT 
);
TRUNCATE TABLE exam;
-- 添加 唯一约束 和非空约束
ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE  NOT NULL ;
-- 创建主表
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);
INSERT INTO dept VALUES
(10,'餐饮部','上海'),
(20,'销售部','浙江'),
(30,'财务部','北京'),
(40,'财务部','深圳');
为 test 加外键
ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno);
主表有限制，从表随意删。
-- 多表查询
多张数据表或视图的查询叫做连接查询
-- 1.笛卡尔积:
SELECT * FROM test, dept;
-- 2.等值链接(SELECT * FROM A,B WHERE A.主键=B.外键;)
SELECT * FROM test, dept WHERE  dept.deptno = test.deptno;
-- 3.内连接(SELECT * FROM A INNER JOIN B ON A.主键=B.外键;)
SELECT * FROM emp INNER JOIN dept ON dept.deptno = emp.deptno;
SELECT * FROM test INNER JOIN dept ON dept.deptno = test.deptno;
-- 4.外连接:
-- 	4.1左外连接:(SELECT * FROM A LEFT OUTER JOIN B ON 条件;)
SELECT * FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno;
-- 	4.2右外连接:(SELECT * FROM A right OUTER JOIN B ON 条件;)
SELECT * FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;
-- 5.子查询:
-- 	5.1单行单列,工作地点在上海的员工
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc ='上海');
-- 	5.2多行单列,工作地点不在上海的员工
SELECT * FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>'上海'); <>或！=都是不等于的意思
-- 6.自连接 
SELECT e1.*,e2.* FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno WHERE e1.ename ='吕子乔'
多表查询练习↓
-- 1.查看每个员工的名字以及其所在部门的名字
SELECT emp.ename,dept.dname,dept.loc 
FROM emp,dept 
WHERE emp.deptno = dept.deptno;
-- 2.查看工作地点在北京的员工有哪些
SELECT *
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE dept.loc ='北京';
-- 3.查看每个城市员工的平均工资
SELECT dept.loc,ifnull(avg(sal),0)
FROM emp right join dept
on emp.deptno =dept.deptno
GROUP BY dept.loc;
-- 4.查看工作地点在上海的员工有哪些
SELECT emp.*,dept.dname,dept.loc 
FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
WHERE dept.loc='上海';
-- 5.查找和曾小贤同职位的员工
SELECT *
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='曾小贤');
-- 6.查找薪水比整个机构平均水平高的员工
SELECT * 
FROM emp 
WHERE sal>(SELECT AVG(sal) FROM emp);
-- 7.查询出部门中有clerk但职位不是clerk的员工的信息
SELECT * 
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno FROM emp WHERE job='clerk') AND job!='clerk';
-- 8.查看每个城市员工的平均工资'
SELECT dept.loc,AVG(sal)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.loc;
-- 9.查询出最低薪水高于部门20的最低薪水的部门信息
SELECT deptno,MIN(sal) AS MIN_sal
FROM emp 
GROUP BY deptno 
HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20);
-- 10.列出所有员工的姓名及其直接上级的姓名
SELECT e1.ename,e2.ename
FROM emp e1 
LEFT JOIN emp e2 ON e1.mgr = e2.empno;
多表查询
多张数据表或视图的查询叫做连接查询
-- 笛卡尔积
SELECT * FROM test ,dept;
-- 2等值连接（select * from A,B where A.主键=B.外键）
SELECT * FROM test,dept WHERE dept.deptno = test.deptno;
-- 3 内连接（select * from A inner join B on A.主键 = B.外键）
SELECT * FROM test INNER JOIN  dept ON dept.deptno = test.deptno;
-- 4.外连接
-- 4.1 左外连接：（select * FROM A LEFT OUTER JOIN B ON 条件）
SELECT * FROM test LEFT OUTER  JOIN dept ON dept.deptno = test.deptno;
-- 4.2 右外连接 ： （select * from A rigjt outer JOIn B on 条件）
SELECT * FROM test RIGHT OUTER JOIN dept ON  dept.deptno = test.deptno;
-- 子查询
-- 5.1 单行单列，工作地点在上海的员工
SELECT * FROM test WHERE deptno=(SELECT deptno FROM dept WHERE loc = '上海');
-- 5.2 多行单列，工作地点不在上海的员工
SELECT * FROM test WHERE deptno IN (SELECT deptno FROM dept WHERE loc != '上海'); 
-- 6,自连接
SELECT e1.*,e2.* FROM test e1 INNER JOIN test e2 ON e1.mgr = e2.empno WHERE e1.ename = '吕子乔';
多表查询练习
1查看每个员工的名字以及其所在部门的名字,部门的位置
SELECT test.ename ,dept.dname,dept.loc FROM test , dept WHERE test.deptno = dept.deptno;
2查看工作地点在北京的员工有哪些
SELECT * fROM test, dept WHERE test.deptno = dept.deptno && dept.loc='北京'; --完全自己想象
SELECT * FROM test INNER JOIN dept ON test.deptno = dept.deptno WHERE dept.loc ='北京';
3 查看每个城市员工的平均工资
SELECT dept.loc,IFNULL(AVG(sal),0)FROM emp RIGHT JOIN d
多张数据表或视图的查询叫做连接查询
-- 1、笛卡尔积
SELECT * FROM test,dept;
-- 2,等值连接（select * from A,B where A.主键= B.外键）
SELECT * FROM test,dept WHERE dept.deptno = test.deptno;
3 内连接（select * FROM A INNER JOIN B ON A.主键= B.外键） 
-- 从表在前面，主表在后面, on后面的条件也是一样
SELECT * FROM  test INNER JOIN dept ON test.deptno = dept.deptno;
4外连接
右外连接（select * FROM A RIGHT OUTER JOIN B ON 条件）
SELECT * FROM test RIGHT OUTER JOIN dept ON test.deptno = dept.deptno;
左外连接(SELECT * FROM A LEFT OUTER JOIN B ON 条件)
SELECT * FROM test LEFT OUTER JOIN dept ON test.deptno = dept.deptno;
5 子查询：
5.1 单行单列。工作地点在上海的员工
SELECT * FROM test WHERE deptno = (SELECT deptno FROM dept WHERE loc ='上海');
5.2 多行单列 ,工作地点不在上海的员工
SELECT *FROM test WHERE deptno IN (SELECT deptno FROM dept WHERE loc != '上海');
6自连接
SELECT e1.*,e2.* FROM test e1 INNER JOIN test e2 ON e1.mgr = e2.empno WHERE e1.ename='李大嘴';  
多表查询练习
-- 1，查看每个员工的名字以及其所在部门的名字
SELECT test.ename ,dept.dname ,dept.loc FROM test ,dept WHERE test.deptno = dept.deptno;
-- 2，查看工作地点在北京的员工有哪些
SELECT test.ename FROM test INNER JOIN dept ON test.deptno = dept.deptno WHERE dept.loc='北京'; 
SELECT * FROM test ,dept WHERE test.deptno=dept.deptno;
-- 3查看每个城市员工的平均工资
SELECT dept.loc,IFNULL(AVG(sal),0) FROM test right OUTER join dept ON test.deptno = dept.deptno GROUP BY dept.loc ;
4 查看工作地点在上海的员工有哪些
SELECT test.ename FROM test INNER JOIN dept ON test.deptno = dept.deptno WHERE dept.loc = '上海';
5 查找与曾小贤同职位的员工
SELECT test.ename FROM test INNER JOIN dept ON test.deptno = dept.deptno WHERE job = (SELECT test.job FROM test WHERE ename='曾小贤');
6 查找薪水比整个机构平均水平高的员工
SELECT * FROM test WHERE sal > (SELECT AVG(sal) FROM test);
7 查询出部门中有clerk 但职位不是clerk的员工的信息
SELECT * FROM test WHERE deptno IN(SELECT DISTINCT deptno FROM test WHERE  job='clerk') AND job!='clerk'; 
8 查看每个城市员工的平均工资
SELECT dept.loc, IFNULL(AVG(sal),0) FROM  test INNER JOIN dept ON test.deptno = dept.deptno GROUP by dept.loc;
9 查询出最低薪水高于部门20的最低薪水的部门信息
SELECT deptno,MIN(sal) AS MIN_sal FROM test GROUP BY deptno HAVING MIN_sal > (SELECT MIN(sal) FROM test WHERE deptno = 20);
10 列出所有员工的姓名及其上级的姓名
SELECT e1.ename,e2.ename FROM test e1 INNER JOIN  test e2 ON e1.mgr=e2.empno;