【韩老师零基础30天学会Java 26】合并查询，左右连接，约束，外键，自增长，索引，存储引擎，视图，权限管理，作业联系事务，

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all
1.union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

2.union
该操作赋与union all相似，但是会自动去掉结果集中重复行

-- 合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union  就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

连接

外连接

●提出一个问题
1.前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛
卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示

2.比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些
没有员工的部门。

●外连接

1. 左外连接(如果左则的表完全显示我们就说是左外连接)

2. 右外连接(如果右侧的表完全显示我们就说是右外连接)

引出问题

• 普通的from多表，筛选笛卡尔积的 是内连接。
• 和 inner join 一样。

-- 外连接

-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。

-- 使用我们学习过的多表查询的SQL， 看看效果如何? 没有员工的部门，不会显示

SELECT dname, ename, job 
	FROM emp, dept
	WHERE emp.deptno = dept.deptno
	ORDER BY dname

创建表

-- 创建 stu
/*
id  name   
1   Jack
2   Tom
3   Kity
4   nono

*/
CREATE TABLE stu (
	id INT,
	`name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu;
-- 创建 exam
/*
id   grade
1    56
2    76
11   8

*/
CREATE TABLE exam(
	id INT,
	grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;

左右连接

-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）
-- 普通连接，没成绩的人，不现实
SELECT `name`, stu.id, grade
	FROM stu, exam
	WHERE stu.id = exam.id;

-- 改成左外连接
-- select .. from 表1 left join 表2 on 条件。表1：就是左表。表2:就是右表]

SELECT `name`, stu.id, grade
	FROM stu LEFT JOIN exam
	ON stu.id = exam.id;

-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id;
	
	
-- 案例2	

-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, job
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno

-- 使用右外连接实现
SELECT dname, ename, job
	FROM emp RIGHT JOIN dept
	ON dept.deptno = emp.deptno

mysql约束

基本介绍
约束用于确保数据库数据满足特定的商业规则。在mysql中，约束包括:

• not null、

• unique,

• primary key,

• foreign key,

• 和check五种.

主键

字段名字段类型 primary key

用于唯一的标示表行的数据,当定义主键约束后，该列不能重复，不为null

Duplicate entry '1' for key 'PRIMARY'

//java中如此捕获
catch (DuplicateKeyException e) {
}

duplicate
英
/ˈdjuːplɪkeɪt/
美
/ˈduːplɪkeɪt/
v.
复制，复印；（无必要地）重复（某事）；使成倍增加
adj.
完全一样的，复制的；二重的
n.
完全一样的东西，复制品

-- 主键使用

-- -------------------------不能为空--------------------------
-- id	name 	email
CREATE TABLE t17
	(id INT PRIMARY KEY, -- 表示id列是主键 
	`name` VARCHAR(32),
	email VARCHAR(32));
	
-- 主键列的值是不可以重复
-- 主键使用的细节讨论
-- primary key不能重复而且不能为 null。
INSERT INTO t17
	VALUES(NULL, 'hsp', 'hsp@sohu.com');
-- -------------------------不能为空--------------------------


-- -------------------------复合主键--------------------------
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18
	(id INT PRIMARY KEY, -- 表示id列是主键 
	`name` VARCHAR(32), PRIMARY KEY -- 错误的
	email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
	(id INT , 
	`name` VARCHAR(32), 
	email VARCHAR(32),
	PRIMARY KEY (id, `name`) -- 这里就是复合主键
	);

INSERT INTO t18
	VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t18
	VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- -------------------------复合主键--------------------------


-- -------------------------主键的两种方式--------------------------
-- 主键的指定方式 有两种 
-- 1. 直接在字段名后指定：字段名  primakry key
-- 2. 在表定义最后写 primary key(列名); 
CREATE TABLE t19
	(id INT , 
	`name` VARCHAR(32) PRIMARY KEY, 
	email VARCHAR(32)
	);

CREATE TABLE t20
	(id INT , 
	`name` VARCHAR(32) , 
	email VARCHAR(32),
	PRIMARY KEY(`name`) -- 在表定义最后写 primary key(列名)
	);
-- 使用desc 表名，可以看到primary key的情况

DESC t20 -- 查看 t20表的结果，显示约束的情况
DESC t18

非空 唯一

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。

字段名 字段类型 not null
字段名 字段类型 unique

unique(唯一)
当定义了唯一约束后，该列值是不能重复的.。

1. 如果没有指定not null，则unique字段可以有多个null

• 空值是 没有值，多个空，不违反 唯一约束。

2. —张表可以有多个unique字段

CREATE TABLE t21
	(id INT UNIQUE ,  -- 表示 id 列是不可以重复的.
	`name` VARCHAR(32) , 
	email VARCHAR(32)
	);
	
INSERT INTO t21
	VALUES(1, 'jack', 'jack@sohu.com');
-- 插入相同的id 失败。
INSERT INTO t21
	VALUES(1, 'tom', 'tom@sohu.com');
	
-- unqiue使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
	VALUES(NULL, 'tom', 'tom@sohu.com'); -- null 可以重复插入。
SELECT * FROM t21;


-- 2. 一张表可以有多个unique字段
CREATE TABLE t22
	(id INT UNIQUE ,  -- 表示 id 列是不可以重复的.
	`name` VARCHAR(32) UNIQUE , -- 表示name不可以重复 
	email VARCHAR(32)
	);
DESC t22

外键

foreign key(外键)
用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级图示)

FOREIGN KEY(本表字段名) REFERENCES 主表名(主键名或unique字段名)
-- 比如：学生表示 外键所在表，即：本表字段名
-- 班级表为 主表。先建主表。

学生有所属 班级ID 字段。

• 班级表中有 100,200 ID。
• 如果学生表 插入的ID是 300，那无法插入。

如果我们要求,每个学生所在的班级号class _id是存在的班级编号就可以把 class_id做成外键约束

• 如果 有一个学生 的班级ID是200，此时 删除班级表的 ID200的数据，会失败。
• 除非先 删除 学生，在删除 班级。

错误代码:1452
[23000][1452] Cannot add or update a child row: a foreign key constraint fails ('hsp_de02 . my_stu , CONISTATNT'my_stu_ibfk, 1' FOREIGN KEY (class_id ` ) REFERENCES my_class(id) )

-- 外键演示

-- 创建 主表 my_class
CREATE TABLE my_class (
	id INT PRIMARY KEY , -- 班级编号
	`name` VARCHAR(32) NOT NULL DEFAULT '');

-- 创建 从表 my_stu
CREATE TABLE my_stu (
	id INT PRIMARY KEY , -- 学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT , -- 学生所在班级的编号
	-- 下面指定外键关系
	FOREIGN KEY (class_id) REFERENCES my_class(id))

-- 测试数据
INSERT INTO my_class 
	VALUES(100, 'java'), (200, 'web');
	
SELECT * FROM my_class;
INSERT INTO my_stu 
	VALUES(1, 'tom', 100);

INSERT INTO my_stu 
	VALUES(4, 'mary', 400); -- 这里会失败...因为400班级不存在

INSERT INTO my_stu 
	VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_class;

-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
	WHERE id = 100; 
	-- Cannot delete or update a parent row: a foreign kdey constraint fails

1.外键指向的表的字段，要求是primary key或者是
unique
2表的类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致(长度可以不同)
4.外键字段的值，必须在主键字段中出现过，或者为null[前
提是外键字段允许为null]
5.一旦建立主外键的关系，数据不能随意删除了.

check

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000_{2000之间如果不再1000}2000之间就会提示出错。
oracle和 sql server均支持check ，但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。

在mysql中实现check的功能,一般是在程序中控制，或者通过触发器完成。

-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解 
-- 学习 oracle, sql server, 这两个数据库是真的生效.

-- 测试
CREATE TABLE t23 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32) ,
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
	);
	
-- 添加数据
INSERT INTO t23 
	VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;

MySQL 8.0.16是第一个支持 CHECK 约束的版本. 如果您使用 MySQL 8.0.15 或更早版本,MySQL参考手册说: CHECK子句被解析但被所有存储引擎忽略

约束练习题

-- 使用约束的课堂练习

CREATE DATABASE shop_db;

-- 现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：
-- 商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，
-- 供应商provider);
-- 客户customer（客户号customer_id,姓名name,住址address,电邮email性别sex,身份证card_Id);
-- 购买purchase（购买订单号order_id，客户号customer_id,商品号goods_id,购买数量nums);
-- 1 建表，在定义中要求声明 [进行合理设计]：
-- (1)每个表的主外键；
-- (2)客户的姓名不能为空值；
-- (3)电邮不能够重复;
-- (4)客户的性别[男|女] check 枚举..
-- (5)单价unitprice 在 1.0 - 9999.99 之间 check

-- 商品goods
CREATE TABLE goods (
	goods_id INT PRIMARY KEY COMMENT '商品ID',
	goods_name VARCHAR(64) NOT NULL DEFAULT '',
	unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 
		CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT '');
	
-- 客户customer（客户号customer_id,姓名name,住址address,电邮email性别sex,
-- 身份证card_Id);
CREATE TABLE customer(
	customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定
	`name` VARCHAR(64) NOT NULL DEFAULT '',
	address VARCHAR(64) NOT NULL DEFAULT '',
	email VARCHAR(64) UNIQUE NOT NULL,
	sex ENUM('男','女') NOT NULL ,  -- 这里老师使用的枚举类型, 是生效
	card_Id CHAR(18)); 
	
-- 购买purchase（购买订单号order_id，客户号customer_id,商品号goods_id,
-- 购买数量nums);
CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id CHAR(8) NOT NULL DEFAULT '', -- 外键约束在后
	goods_id INT NOT NULL DEFAULT 0 , -- 外键约束在后
	nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
DESC goods;
DESC customer;
DESC purchase;

commen
英
/ˈkɒment
n.
评论，意见；批评，指责；说明，写照；<旧>解说，注释；（计算机）注解
v.
评论，发表意见；（计算机）注解，把（部分程序）转成注解

自增长

●自增长基本介绍一个问题
在某张表中，存在一个id列(整数),我们希望在添加记录的时候,该列从1开始，自动的增长，怎么处理?

字段名 整型 primary key auto_increment

increment
英
/ˈɪŋkrəmənt/
n.
<正式>（尤指连续、定量的）增长；<正式>工资定量增长，定量加薪；（变量、函数的）正（或负）增量
v.
（计算机）使（数字值）各自增加

添加自增长的字段方扰
-- 字段1 添加为null
insert into xxx(字段1.字段2...) vaiues(null，值..…);
-- 字段1，添加时忽略
insert into xxx(字段2.….) values('值1'，'值2'..…);
-- 添加所有值，字段1 添加为 null
insert into xxx values(null, '值1'...)

自增长使用细节
1.一般来说自增长是和primary key配合使用的

2.自增长也可以单独使用[但是需要配合一个unique]
3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常
少这样使用)
4.自增长默认从1开始,你也可以通过如下命令修改alter
table表名auto increment = XXX;

案例

-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
	(id INT PRIMARY KEY AUTO_INCREMENT,
	 email VARCHAR(32)NOT NULL DEFAULT '',
	 `name` VARCHAR(32)NOT NULL DEFAULT ''); 
DESC t24
-- 测试自增长的使用
INSERT INTO t24
	VALUES(NULL, 'tom@qq.com', 'tom');

INSERT INTO t24
	(email, `name`) VALUES('hsp@sohu.com', 'hsp');

SELECT * FROM t24;

-- 修改默认的自增长开始值
CREATE TABLE t25
	(id INT PRIMARY KEY AUTO_INCREMENT,
	 email VARCHAR(32)NOT NULL DEFAULT '',
	 `name` VARCHAR(32)NOT NULL DEFAULT '');

ALTER TABLE t25 AUTO_INCREMENT = 100
INSERT INTO t25 -- 开始为 100
	VALUES(NULL, 'mary@qq.com', 'mary');

-- 如果你添加数据时,给自增长字段(列)指定的有值,则以指定的值为准。下次从667开始。
-- 如果指定了自增长，—般来说，就按照自增长的规则来添加数据.
INSERT INTO t25
	VALUES(666, 'hsp@qq.com', 'hsp');
SELECT * FROM t25;

alter
英
/ˈɔːltə(r)/
v.
改变，改动；（使）变化；改（服装）；改建（某建筑）；<美>阉割

索引

说起提高数据库性能,索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍干倍。

创建海量表

-- 创建测试数据库 tmp
CREATE DATABASE tmp;

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 DECLARE chars_str VARCHAR(100) DEFAULT
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
    # concat 函数 : 连接函数mysql函数
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
  RETURN return_str;
  END $$


 #这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$

 #创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 SET autocommit = 0; #默认不提交sql语句
 REPEAT
 SET i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 END REPEAT;
 #commit整体提交所有sql语句，提高效率
   COMMIT;
 END $$

 #添加8000000数据
CALL insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
DELIMITER ;

索引实战入门 CREATE INDEX ON

SELECT COUNT(*) FROM emp;

-- 在没有创建索引时，我们的查询一条记录
SELECT * 
	FROM emp 
	WHERE empno = 1234567 
-- 使用索引来优化一下， 体验索引的牛

-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建ename列索引,emp.ibd 文件大小 是 827m

-- empno_index 索引名称 
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
CREATE INDEX empno_index ON emp (empno)

-- 创建索引后， 查询的速度如何

SELECT * 
	FROM emp 
	WHERE empno = 1234578 -- 0.003s 原来是4.5s


-- 创建索引后，只对创建了索引的列有效 
SELECT * 
	FROM emp 
	WHERE ename = 'PjDlwy' -- 没有在ename创建索引时，时间4.7s

CREATE INDEX ename_index ON emp (ename) -- 在ename上创建索引

原理

没有索引为什么会慢? 会全表扫描

使用索引为什么会快?

• 形成一个索引的数据结构,比如二叉树。B树，B+树

索引的代价
磁盘占用 。二叉树占空间。
对dml(update delete insert)语句的效率影响

• 比如删除了 7，会导致二叉树数据结构的 改变。

• 如果对表进行dml(修改,删除，添加)会对索引进行维护，对速度有影响

• 项目中，select 最多，占90%。

• 博主有10W个粉丝，发了10篇文章。每个粉丝看一次。

当我们没有索引.
select * from emp where id = 1
进行全表扫描，查询速度慢
-- 假如id不是主键。 虽然 id=1 第一个查出来了。
-- 但是 不确定下面还有没有，所以会全表扫描

• 建立所以后，形成 二叉树

  5

2 7

1 3 6 8

​ 4 9

• 如果查询 1，只需要查 3次。 5 2 1

思考:如果我们比较了30次覆盖的表的范围2^30

索引类型

1. 主键索引，主键自动的为主索引(类型Primary key)

2. 唯一索引 (UNIQUE)

3. 普通索引(INDEX)

4. 全文索引 (FULLTEXT)[适用于MyISAM]
   开发中考虑使用:全文搜索Solr和ElasticSearch (ES)

   1. 一篇文章 里面有 多个电影字，建立索引。

create table t1 (
	id int primary key,-- 主键,同时也是索引，称为主键索引
    name varchar(32)
);

create table t2(
	id int unique, -- id是唯一的，同时也是索引，称为unique索引
    name varchar(32));
)

添删除查询

1．添加索引(建小表测试id , name ) index_use.sql
create [UNIQUE] index index_name on tbl_name (col_name[(length)[ASC | DESC] , ..…);

alter table table_name ADD INDEX [index name] (index_ col name,.)

2.添加主键(索引)ALTER TABLE表名ADD PRIMARY KEY(列名.…);
3.删除索引
DROP INDEX index name ON tbl_ name

alter table table_name drop index index name;

4.删除主键索引比较特别: alter table t_b drop primary key;

5.查询索引的三种方式
show index(es) from table_name;
show keys from table_name;
desc table_Name;

CREATE INDEX id_index ON t25 (id);
ALTER TABLE t25 ADD INDEX id_index (id)

-- 演示mysql的索引的使用
-- 创建索引
CREATE TABLE t25 (
	id INT ,
	`name` VARCHAR(32));
	
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
-- create [UNIQUE] index index_name on tbl_name (col_name);
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 展示索引：Non_unique 非唯一索引吗？如果是1，代表是非唯一索引（普通索引）。0代表 不是非唯一索引，就是 是。

-- 添加普通索引方式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(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)

SHOW INDEX FROM t25


-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY

-- 修改索引 ， 先删除，在添加新的索引

-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25

索引的使用规则

小结:哪些列上适合使用索引
1．较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
-- 经常通过部门 查询 雇员。

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询
条件
select * from emp where sex =‘男‘
-- 一半是男，一半是女，不适合。

3．更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
-- 统计登录测试。经常更新，不适合。

4. 不会出现在WHERE子句中字段不该创建索引

事务

什么是事务
事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

●事务和锁
当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的

• 数据操作语言（Data Manipulation Language）

manipulation
美
/məˌnɪpjuˈleɪʃn/
全球发音
简明 例句 百科
n.
操纵；推拿；（熟练的）控制，使用；（对账目等的）伪造，篡改；（对储存在计算机上的信息的）操作，处理

mysql数据库控制台事务的几个重要操作

1.start transaction --开始一个事务
2.savepoint 保存点名--设置保存点
3.rollback to保存点名--回退事务
4.rollback --回退全部事务
5.commit--提交事务,所有的操作生效,不能回退

细节:
1.没有设置保存点
2.多个保存点
3.存储引擎
4.开始事务方式

事务管理
savepoint
rollback
commit
隔离级别
ACID

update balance set money=money-100 where id = 100
update balance set money=money+100 where id = 200

这里我们就引出一个需求，将多个dml (update,insert ,delete）

当做一个整体，要么全部成功，要么全部失败—>就使用事务来解决

回退事务
在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。
当执行回退事务时，通过指定保存点可以回退到指定的点,这里我们作图说明

提交事务
使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据

实战 开启设置保存点

-- 事务的一个重要的概念和具体操作

-- 1. 创建一张测试表
CREATE TABLE t27
	( id INT,
	  `name` VARCHAR(32));

-- 2. 开始事务
START TRANSACTION 

-- 3. 设置保存点
SAVEPOINT a
-- 执行dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;

SAVEPOINT b
-- 执行dml操作
INSERT INTO t27 VALUES(200, 'jack');

-- 回退到 b
ROLLBACK TO b 
-- 如果此时回到a点。则 无法回到 b点了。
-- 因为 a点 是最前面第一个点。回退到a点，b点会删除。

-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK
COMMIT

细节问题

1. 如果不开始事务,默认情况下，dml操作是自动提交的，不能回滚

2. 如果开始一个事务，你没有创建保存点.你可以执行rollback，默认就是回退到你事务开始的状态.

3. 你也可以在这个事务中(还没有提交时)，创建多个保存点.比如: savepointaaa;执行dml , savepoint bbb;

4. 你可以在事务没有提交前，选择回退到哪个保存点.

5. mysql的事务机制需要innodb的存储引擎还可以使用,myisam不好使.

6. 开始一个事务start transaction, set autocommit=off;

-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION 
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;

事务的隔离级别

isolation
英
/ˌaɪsəˈleɪʃn/
n.
隔离，孤立；孤独；绝缘；（尤指对混合物或微生物的）离析

在不同的事务中，根据隔离的强度不同， 每个事务看到 同一个表的数据 不同。

• c1 在操作一个表的时候，
• c2 看这张表时，看到的是什么样的数据。根据c2的隔离级别。

● 事务隔离级别介绍
1.多个连接开启各自事务操作数据库中数据时，数据库系统要负
责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)

4级别3问题

2．如果不考虑隔离性，可能会引发如下问题:
脏读
不可重复读
幻读

脏读(dirty read):当一个事务读取另一个事务尚未提交的改变时，产生脏读

不可重复读(nonrepeatable read):同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。

• C2中看到了 C1的修改，C2就发生了 不可重复读。
• C2在统计10:00之前的数据，却读到了 C2在10点以后 提交的数据。
• 主要是：修改和删除。

幻读(phantom read):同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作,每次返回不同的结果集，此时发生幻读。

• 主要是：插入操作。

事务隔离级别
概念:Mysql隔离级别定义了事务与事务之间的隔离程度。

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted）	√	√	√	不加锁
读已提交(Read committed)	X	√	√	不加锁
可重复读(Repeatable read)	X	X	X	不加锁
可串行化(Serializable )	X	X	X	加锁

• 加锁，一个表 被一个事务操作，就不会在 操作这个表。

实战

-- 演示mysql的事务隔离级别

-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
SELECT @@tx_isolation;

-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+

-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
-- set session transaction isolation levle read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 两边都启动事务
start transaction;

-- 4. 创建表
CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT);


-- --------------终端2 读未提交级别，产生 脏 不可重复 幻读-----------
-- 终端1执行：添加1个数据，还未提交。
-- insert into account values (100,'tom’, 1000);
-- 终端2可查询到，产生脏读。

-- 终端1：在修改这条数据。在插入一条数据。 提交事务。
-- update account set money = 800 where id=100;
-- insert into account values(200,'jack’, 2000); 幻读，主要是对于插入数据。
commit; -- 提交事务后。

-- 终端2查询：可查到 这两个结果。（既能看到 修改的效果，也能看到添加的）。
-- 叫做：不可重复度和幻读。终端2事务还没提交呢？已经被其他的事务影响了（终端1的提交，影响了我）。
-- 终端2潜台词：能看到的数据，就是我连接到 mysql（开启事务） 这个的时间点。
-- 只要我的事务还没提交，我读到的就应该是：我开启事务的 数据，而不能受到别的影响。这才叫正常。
-- 如果不这样：可能有 终端1,3,4,5 都操作过了。终端2 读取的数据 不知道是谁的了。
-- --------------终端2 读未提交级别，产生 脏 不可重复 幻读-----------


-- --------------终端2 读已提交级别，产生 不可重复 幻读-----------
-- 提交事务后，终端1开始事务。
-- 终端2，提交事务 改为 读已提交。在开始事务。
SET SESSION TRANSACTION ISOLATION LEVEL Read committed;
-- 终端1 添加数据。
-- insert into account values (300, 'scott’, 8000) ;
-- 终端2 查询（因为是 读已提交），看不到这个 终端1，插入的数据。

-- 终端1修改数据
update account set money = 1800 where id=200;
-- 终端1提交数据
-- 终端2查询，读取到数据（增加了 终端插入的这条）。
-- 终端2查询，两次结果查询的不一样（当前是开着事务的）。产生：不可重复 和 幻读。
-- --------------终端2 读已提交级别，产生 不可重复 幻读-----------


-- --------------终端2 可重复度级别，不产生错误-----------
-- 重新开关终端2，改为 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable read
-- 两个终端 都提交 事务。再次开始事务。
-- 终端1插入数据，在修改数据。
insert into account values(400, 'milan', 6000) ;
update account set money = 100 where id=300;
-- 终端2查询数据：出现 终端1 最新插入的 和 最新修改的 都没有生效。
-- 终端1 提交事务
-- 终端2 还没有提交事务，终端2 再次查询，查到的数据依然是 没有终端1 插入和修改的。
-- 即：我开事务的那一刻，就决定了 当前数据库在这个点了。随便查，都是这一刻的数据（不会被他人影响）
-- --------------终端2 可重复度级别，不产生错误-----------

-- -----------------------可串行话锁表----------------
-- 重新连 终端2。级别改为可串行话。
SET SESSION TRANSACTION ISOLATION LEVEL Serializable
-- 终端1 开启事务。终端2 开启事务，终端1：插入数据。在修改数据。
insert into account values (500,'terry' , 80000) ;
update account set money = 900 where id=300;
-- 终端2 查询：会卡在这里。因为发现 有一个事务还没提交，就不能查询。
-- 终端1执行提交。终端2，立马就有结果，或者 锁超时。
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- -----------------------可串行话锁表----------------

-- 查看当前会话隔离级别 
SELECT @@tx_isolation
-- 查看系统当前隔离级别。所有用户登录后，默认的隔离级别。
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
set session transaction isolation level repeatable read;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
set global
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

• mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊
  要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

• 终端1开了线程，插入 主键id为1的数据，成功。

• 终端2开了线程，插入 主键id为1的数据，会陷入等待。直到终端1 提交或回退。

  • 如果 终端2，插入的数据 不是冲突的，则可以插入成功。
  • 如果同时修改一条数据，也会 陷入等待。 或者 终端1 插入了 id5，还没提交。终端2 修改这个记录。
  • 如果：修改的是 UPDATE t25 SET name=‘2’ WHERE name=‘222’; ，这种是 全局扫描表，修改的。肯定锁表。
    • 另一个终端，对此表操作，都会进入等待。等待 这个更新提交。

全局修改，修改mysql.ini配置文件，在最后加上
#可选参数有:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

• 重启mysql服务，生效。
• net stop mysql

事务的特性

事务的acid特性

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

2.一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态

• 事务提交后 整体的改变。

3.隔离性(lsolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4.持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

mysql表类型和存储引擎

●基本介绍

1. MySQL的表类型由存储引擎(Storage Engines）决定，主要包括MylSAM.
   innoDB、Memory等。

2. MySQL 数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE.
   MRG MYISAM、MYISAM、InnoBDB。这里没有BLACKHOLE 和 最后一个。

看引擎的选项：共有8种：

1. ARCHIVE

   1. 存档类型的存储引擎

2. BLACKHOLE

   1. /dev/null存储引擎(你写入的任何东西都会消失)
   2. 黑洞

3. csv

4. lnnoDB

   1. 支持事务行级锁定和外键

5. MEMORY

   1. 基于哈希，存储在内存中，对临时表很有用

6. MRG_MYISAM：SQLyog没有这个

   1. 收集相同的MylSAM表

7. MylSAM

   1. 不支持事务，批量添加非常快。

8. PERFORMANCE_SCHEMA：SQLyog没有这个

9. FEDERATED federated mysql不支持。

10. 这六种又分为两类，一类是”事务安全型”(transaction-safe)，比如:
    InnoDB;

    其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory]。

show engines;

特点

特点	Myisam	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起
   MylSAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
2. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应
   一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉,表的结构还在。

Innodb的三种锁

Innodb 有全局锁、表级锁、行级锁三种

1. 对于表级锁而言，当执行 DDL 语句去修改表结构时，会使用表级锁。
2. 对于行级锁而言，一般情况下都会默认使用行级锁，

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

innodb 和 myisam

1、innodb支持事务，而myisam不支持事务。
2、innodb支持外键，而myisam不支持外键。
3、innodb默认表锁，使用索引检索条件时是行锁，而myisam是表锁（每次更新增加删除都会锁住表）。
4、innodb和myisam的索引都是基于b+树，但他们具体实现不一样，innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的。
5、innodb是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次，myisam是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引和辅助索引是分开的。
6、innodb不存储表的行数，所以select count( * )的时候会全表查询，而myisam会存放表的行数，select count(*）的时候会查的很快。

总结:mysql默认使用innodb，如果要用事务和外键就使用innodb，如果这张表只用来查询，可以用myisam。如果更新删除增加频繁就使用innodb。

原文链接：https://blog.csdn.net/weixin_41832850/article/details/106063426

• MySQL 5.1之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB

MyISAM:默认表类型，它是基于传统的ISAM类型，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键。

InnoDB:支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。

MyISAM :
myisam属于堆表
myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。

InnoDB :
innodb属于索引组织表
innodb有两种存储方式，共享表空间存储和多表空间存储

如何选择

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM是不二选择,速度快

2. 如果需要支持事务，选择lnnoDB。

3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，
   速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态().)

4. id 和 state 在线 离线

5. 获取自己 或 好友的状态 都查询这个表。 用户每次上线，插入这个表。

6. 状态丢失了 也没事。

实战

ALTER TABLE 表名 ENGINE =储存引擎;

-- 表类型和存储引擎

-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁

-- myisam 存储引擎
CREATE TABLE t28 (
	id INT,
	`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁

START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1 -- 回退了没用，数据还是在的

-- memory 存储引擎。比如用在：用户的状态，在线，离线。
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在] 
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)

CREATE TABLE t29 (
	id INT,
	`name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
	VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29 -- 重启mysql 服务，数据丢失。

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

视图

emp表的列信息很多，有些信息是个人重要信息(比如sal, comm,mgr, hiredate)，如果我们希望某个用户只能查询emp表的(empno.ename, job和deptno )信息，有什么办法?

1.视图是一个虚拟表，其内容由查询定义。同真实的表
一样，视图包含列,其数据来自对应的真实表(基表)2。视图和基表关系的示意图

对视图的总结
1.视图是根据基表(可以是多个基表)来创建的
2.视图也有列，数据来自基表
3.通过视图可以修改基表的数据
4.基本的改变，也会影响到视图的数据

视图的使用

视图的基本使用
1. create view视图名as select语句
2. alter view视图名as select语句，更新
3. SHoW CREATE VIEW 视图名
4. drop view视图名1,视图名2

1．创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:视图名.frm)
2．视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

实战1 增删 改数据

-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息

-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno, ename, job, deptno FROM emp; 

-- 查看视图
DESC emp_view01

SELECT * FROM emp_view01;
SELECT empno, job  FROM emp_view01;

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;


-- 视图的细节

-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

-- 修改视图 会影响到基表

UPDATE emp_view01 
	SET job = 'MANAGER' 
	WHERE empno = 7369

-- 修改基本表， 会影响到视图

-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01

CREATE VIEW emp_view02
	AS
	SELECT empno, ename FROM emp_view01
	
SELECT * FROM emp_view02

-- 视图的课堂练习
-- 针对 emp ，dept , 和   salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]

/*
	分析: 使用三表联合查询，得到结果
	将得到的结果，构建成视图
	  
*/
CREATE VIEW emp_view03
	AS
	SELECT empno, ename, dname, grade
	FROM emp, dept, salgrade
	WHERE emp.deptno = dept.deptno AND 
	(sal BETWEEN losal AND hisal) 

DESC emp_view03
SELECT * FROM emp_view03

特点

1．安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这
时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,
数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3．灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很
多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

• 原来的项目用到了 A B 表。不用 A B 表了，使用 A B 表中的数据 建立视图。
  • 项目用 这个视图即可。

mysql管理

mysqi中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明:

1. host:允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
   2.user:用户名;
2. authentication string:密码，是通过mysql的password()函数加密之后
   的密码。

创建用户
create user ‘用户名’ @ ’允许登录位置’ identified by ‘密码’ 
说明:创建用户，同时指定密码

删除用户
drop user ‘用户名’ @ ’允许登录位置’;

代码 创建 修改密码

-- Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用

-- 1. 创建新的用户
-- 解读 (1) 'hsp_edu'@'localhost' 表示用户的完整信息 'hsp_edu' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
--     *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'

SELECT `host`, `user`, authentication_string  
	FROM mysql.user

-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'

-- 3. 登录

-- root 用户修改 hsp_edu@localhost 密码, 是可以成功.
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456')


修改自己的密码:
set password = password('密码');
-- 修改他人的密码(需要有修改用户密码权)
set password for '用户名'@"登录位置’ = password('密码');


--  修改自己的密码, 没问题
SET PASSWORD = PASSWORD('abcdef')
-- 修改其他人的密码， 需要权限

identified
adj.
被识别的；经鉴定的；被认同的
v.
鉴定；辨认（identify 的过去式和过去分词）

权限管理

衩限
意义

ALL [PRIVILEGES]
设置除GRANT OPTION之外的所有简单权限

ALTER
允许使用ALTER TABLE

ALTER ROUTINE
更改或取消已存储的子程序

CREATE
允许使用CREATE TABLE

CREATE ROUTINE
创建已存储的子程序

CREATE TEMPORARY TABLES
允许使用CREATE TEMPORARY TABLE

CREATE USER
允许使用CREATE USER,DROP USER，RENAME USER和REVOKE ALL PRIVILEGES

CREATE VIEW
允许使用CREATE VIEw

DELETE
允许使用DELETE

DROP
允许使用DROP TABLE

EXECUTE
允许用户运行已存储的子程序

FILE
允许使用SELECT…INT0 OUTFILE和LOAD DATA INFILE

INDEX
允许使用CREATE INDEX和DROP INDEX

INSERT
允许使用INSERT

LoCK TABLES
允许对您拥有SELECT权限的表使用LOCK TABLES

PROCEss
允许使用SHOw FULL PROCESSLIST

REFERENCES
未被实施

RELOAD
允许使用FLUSH

REPLICATION CLIENT
允许用户询问从属服务器或主服务器的地址

REPLICATION SLAVE
用于复制型从属服务器（从主服务器中读取二进制日志事件〉

SELECT
允许使用SELECT

SHOw DATABASES
IsHOw DATABASES显示所有数据库

SHow VIEW
允许使用SHOwCREATE VIEw

SHUTDOwN
允许使用mysqladmin shutdown
允许使用CHANGE MASTER，KILL，PURGE MASTER LOGS和SET GLOBAL语

SUPER
句，mysqladmin debug命令﹔允许您连接〈一次）﹐即使已达到max_connections o

UPDATE
允许使用UPDATE

USAGE
“无权限”的同义词

GRANT OPTION
允许授予权限

给用户授权

基本语法:
grant 权限列表 on 库.对象名 to ‘用户名’@’登录位置’【identified by ‘密码’】

说明:
1，权限列表，多个权限用逗号分开
grant select on .......
grant select,delete,create on ......
grant all 【privileges】on ...... //表示赋予该用户在该对象上的所有权限

2. 特别说明
   *.*︰代表本系统中的所有数据库的所有对象(表，视图，存储过程)

   库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)

3. identified by可以省略，也可以写出.

(1)如果用户存在，就是修改该用户的密码。(2)如果该用户不存在，就是创建该用户!

grant all on *.* to ‘用户名' -- 就是和root用户一样了

回收用户授根基本语法:

revoke 权限列表 on 库.对象名 from '用户名"@"登录位置';

权限生效指令
如果权限没有生效，可以执行下面命令.基本语法:
FLUSH PRIVILEGES;

• 5.7 是 付了权限，就有效了。

revoke
英
/rɪˈvəʊk/
v.
撤回，撤销；（桥牌、惠斯特等牌戏中）有牌不跟
n.
有牌不跟

grant
v.
授予，给予；承认
n.
补助金，拨款；（政府给予的）土地；<正式>给予，授予；合法转让

实战

-- 演示 用户权限的管理

-- 创建用户 shunping  密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'

-- 使用root 用户创建 testdb  ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
	id INT ,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;

-- 给 shunping 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT 
	ON testdb.news
	TO 'shunping'@'localhost'
	
-- 可以增加update权限
GRANT UPDATE  
	ON testdb.news
	TO 'shunping'@'localhost'
	
	
-- 修改 shunping的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');

-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'

-- 删除 shunping
DROP USER 'shunping'@'localhost'

细节之处

-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 
-- create user  xxx;
-- create user 'shunping'@'localhost' identified by '123'
CREATE USER jack identified by '123'

SELECT `host`, `user` FROM mysql.user

-- 你也可以这样指定 
-- create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql

CREATE USER 'smith'@'192.168.1.%'

-- 在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'

DROP USER jack -- 默认就是 DROP USER 'jack'@'%' 。如果不是任一IP的不能删除。

DROP USER 'smith'@'192.168.1.%'

练习

• null 用 is判断

SELECT ename,sal,comm FROM emp WHERE comm IS NOT null;

作业2 简单查询

-- 2. 写出 查看DEPT表和EMP表的结构 的sql语句  homework02.sql   10min 自己先练习
-- 
	DESC dept
	DESC emp
-- 3. 使用简单查询语句完成:
-- (1) 显示所有部门名称。
SELECT dname 
	FROM dept;
-- (2) 显示所有雇员名及其全年收入 13月(工资+补助),并指定列别名"年收入"
SELECT ename, (sal + IFNULL(comm,0)) * 13 AS "年收入"
	FROM emp 
SELECT * FROM emp;


-- 
-- 4.限制查询数据。
-- (1) 显示工资超过2850的雇员姓名和工资。
SELECT ename, sal
	FROM emp 
	WHERE sal > 2850
-- (2) 显示工资不在1500到2850之间的所有雇员名及工资。
SELECT ename, sal
	FROM emp 
	WHERE sal < 1500 OR sal > 2850
	
SELECT ename, sal
	FROM emp 
	WHERE NOT (sal >= 1500 AND sal <= 2850)
-- (3) 显示编号为7566的雇员姓名及所在部门编号。
SELECT ename, deptno
	FROM emp 
	WHERE empno = 7566
-- (4) 显示部门10和30中工资超过1500的雇员名及工资。
SELECT ename, job
	FROM emp 
	WHERE (deptno = 10 OR deptno = 30) AND sal > 1500
-- (5) 显示无管理者的雇员名及岗位。
SELECT ename, job 
	FROM emp
	WHERE mgr IS NULL;
	
	
-- 
-- 5.排序数据。
-- (1) 显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期, 
-- 并以雇佣日期进行排序[默认]。
-- 思路 1. 先查询到对应结果 2. 考虑排序
SELECT ename, job, hiredate
	FROM emp
    -- WHERE hiredate between '1991-02-01' AND '1991-05-01'
	WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01'
	ORDER BY hiredate

-- (2) 显示获得补助的所有雇员名,工资及补助,并以工资降序排序
SELECT ename, sal, comm
	FROM emp
	ORDER BY sal DESC

时间字符串函数练习

-- homework03

-- ------1.选择部门30中的所有员工.
SELECT * FROM 
	emp
	WHERE deptno = 30
-- ------2.列出所有办事员(CLERK)的姓名，编号和部门编号. 
SELECT ename, empno, deptno, job FROM 
	emp
	WHERE job = 'CLERK'
-- ------3.找出佣金高于薪金的员工.
SELECT * FROM 
	emp
	WHERE IFNULL(comm, 0) > sal
-- ------4.找出佣金高于薪金60%的员工.
SELECT * FROM 
	emp
	WHERE IFNULL(comm, 0) > sal * 0.6
-- ------5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
-- 
SELECT * FROM
	emp
	WHERE (deptno = 10 AND job = 'MANAGER') 
	OR (deptno = 20 AND job = 'CLERK')
-- ------6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK), 
 -- 还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
SELECT * FROM
	emp
	WHERE (deptno = 10 AND job = 'MANAGER') 
	OR (deptno = 20 AND job = 'CLERK')
	OR (job != 'MANAGER' AND job != 'CLERK' AND sal >= 2000 )
-- ------7.找出收取佣金的员工的不同工作.
SELECT DISTINCT job
	FROM emp
	WHERE comm IS NOT NULL

-- ------8.找出不收取佣金或收取的佣金低于100的员工.
SELECT *
	FROM emp
	WHERE comm IS  NULL OR IFNULL(comm, 0) < 100
-- ------9.找出各月倒数第3天受雇的所有员工.
-- 老韩提示: last_day(日期)， 可以返回该日期所在月份的最后一天
-- last_day(日期) - 2 得到日期所有月份的倒数第3天
SELECT * 
	FROM emp
	WHERE LAST_DAY(hiredate) - 2  =  hiredate

-- ------10.找出早于12年前受雇的员工.（即： 入职时间超过12年）
SELECT * 
	FROM emp
	WHERE DATE_ADD(hiredate, INTERVAL 12 YEAR) < NOW() 

-- 
-- ------11.以首字母小写的方式显示所有员工的姓名.
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2))
	FROM emp;
-- ------12.显示正好为5个字符的员工的姓名.

SELECT * 
	FROM emp
	WHERE LENGTH(ename) = 5

-- ------13.显示不带有"R"的员工的姓名.
SELECT * 
	FROM emp
	WHERE ename NOT LIKE '%R%'
-- ------14.显示所有员工姓名的前三个字符.
SELECT LEFT(ename,3)
	FROM emp
-- ------15.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ename, 'A', 'a') 
	FROM emp
-- ------16.显示满10年服务年限的员工的姓名和受雇日期.
SELECT ename, hiredate
	FROM emp
	WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) <= NOW()
-- ------17.显示员工的详细资料,按姓名排序.

-- ------18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
-- 
SELECT ename, hiredate
	FROM emp
	ORDER BY hiredate 
-- ------19.显示所有员工的姓名、工作和薪金,按工作降序排序,若工作相同则按薪金排序.
SELECT ename, job, sal
	FROM emp
	ORDER BY job DESC, sal 
-- ------20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,
-- 若月份相同则将最早年份的员工排在最前面.
SELECT ename, CONCAT(YEAR(hiredate),'-', MONTH(hiredate))
	FROM emp
	ORDER BY MONTH(hiredate), YEAR(hiredate)

-- ------21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT ename, FLOOR(sal / 30), sal / 30 
	FROM emp; 
-- ------22.找出在(任何年份的)2月受聘的所有员工。
SELECT * 
	FROM emp
	WHERE MONTH(hiredate) = 2
-- ------23.对于每个员工,显示其加入公司的天数.
-- 
SELECT ename, DATEDIFF(NOW(), hiredate) 
	FROM emp
-- ------24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT *
	FROM emp
	WHERE ename LIKE '%A%'
-- ------25.以年月日的方式显示所有员工的服务年限.   (大概)

-- 思路 1. 先求出 工作了多少天 
SELECT ename, FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS " 工作年 ", 
	FLOOR((DATEDIFF(NOW(), hiredate) % 365) / 31) AS " 工作月 ",
	DATEDIFF(NOW(), hiredate) % 31 AS " 工作天"
	FROM emp;

多表查询

-- 
-- (1)．列出至少有一个员工的所有部门

/*
	先查出各个部门有多少人
	使用 having 子句过滤
*/
SELECT COUNT(*) AS c, deptno
	FROM emp 
	GROUP BY deptno 
	HAVING c > 1
-- (2)．列出薪金比“SMITH”多的所有员工。
/*
	先查出 smith 的 sal => 作为子查询
	然后其他员工 sal 大于 smith 即可
*/
SELECT * 
	FROM emp
	WHERE sal > (
		SELECT sal 
			FROM emp 
			WHERE ename = 'SMITH'
	)
-- (3)．列出受雇日期晚于其直接上级的所有员工。
/*
	先把 emp 表 当做两张表 worker , leader
	条件 1. worker.hiredate > leader.hiredate
	     2. worker.mgr = leader.empno
*/

SELECT worker.ename AS '员工名', worker.hiredate AS '员工入职时间',
	leader.ename  AS '上级名', leader.hiredate AS '上级入职时间' 
	FROM emp worker , emp leader
	WHERE worker.hiredate > leader.hiredate 
	AND worker.mgr = leader.empno;

-- (4)．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

/*
	这里因为需要显示所有部门，因此考虑使用外连接，(左外连接)
	如果没有印象了，去看看老师讲的外连接.
*/
SELECT dname, emp.*
	FROM dept 
	LEFT JOIN emp ON dept.deptno = emp.deptno 

-- (5)．列出所有“CLERK”（办事员）的姓名及其部门名称。

SELECT ename, dname , job
	FROM emp, dept
	WHERE job = 'CLERK' AND emp.deptno = dept.deptno

-- (6)．列出最低薪金大于1500的各种工作。

/*
	查询各个部门的最低工资
	使用having 子句进行过滤
*/
SELECT MIN(sal) AS min_sal , job
	FROM emp
	GROUP BY job
	HAVING min_sal > 1500
-- (7)．列出在部门“SALES”（销售部）工作的员工的姓名。

SELECT ename, dname
	FROM emp , dept
	WHERE emp.deptno = dept.deptno AND dname = 'SALES'

-- (8)．列出薪金高于公司平均薪金的所有员工。

SELECT *
	FROM emp
	WHERE sal > (
		SELECT AVG(sal) 
			FROM emp
	)

-- (9)．列出与“SCOTT”从事相同工作的所有员工。

SELECT * 
	FROM emp
	WHERE job = (
		SELECT job 
			FROM emp
			WHERE ename = 'SCOTT'
	) AND ename != 'SCOTT'

-- (10)．列出薪金高于所在部门30的工作的所有员工的薪金的员工姓名和薪金。


-- 先查询出30部门的最高工资
SELECT ename, sal 
	FROM emp 
	WHERE sal > (
		SELECT MAX(sal) 
			FROM emp
			WHERE deptno = 30
	) 
-- (11)．列出在每个部门工作的员工数量、平均工资和平均服务期限(时间单位)。
-- 老师建议 ， 写sql 也是一步一步完成的
SELECT COUNT(*) AS "部门员工数量", deptno , AVG(sal) AS "部门平均工资" , 
	FORMAT(AVG(DATEDIFF(NOW(), hiredate) / 365 ),2) AS " 平均服务期限(年)"
	FROM emp 
	GROUP BY deptno

-- (12)．列出所有员工的姓名、部门名称和工资。

-- 就是 emp 和 dept 联合查询 ，连接条件就是 emp.deptno = dept.deptno

-- (13)．列出所有部门的详细信息和部门人数。

-- 1. 先得到各个部门人数 , 把下面的结果看成临时表 和 dept表联合查询
SELECT COUNT(*) AS c , deptno 
	FROM emp
	GROUP BY deptno

-- 2. 
SELECT dept.*, tmp.c AS "部门人数"
	FROM dept, (
		SELECT COUNT(*) AS c , deptno 
		FROM emp
		GROUP BY deptno
	) tmp 
	WHERE dept.deptno = tmp.deptno

-- (14)．列出各种工作的最低工资。

SELECT MIN(sal), job
	FROM emp
	GROUP BY job
-- (15)．列出MANAGER（经理）的最低薪金。

SELECT MIN(sal), job
	FROM emp
	WHERE job = 'MANAGER'
-- (16)．列出所有员工的年工资,按年薪从低到高排序。

-- 1. 先得到员工的年工资
SELECT ename, (sal + IFNULL(comm, 0)) * 12 year_sal
	FROM emp
	ORDER BY year_sal 
	
-- 技术就窗户纸 

综合题

-- 完成最后一个综合的练习

-- 8. 设学校环境如下:一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。
-- 现要建立关于系、学生、班级的数据库，关系模式为：
-- 班CLASS （班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num）
-- 学生STUDENT （学号studentid，姓名name，年龄age，班号classid）
-- 系 DEPARTMENT （系号departmentid，系名deptname）
-- 试用SQL语言完成以下功能：  homework05.sql 10min 
-- 
-- (1) 建表，在定义中要求声明：
--     （1）每个表的主外码。
--     （2）deptname是唯一约束。
--     （3）学生姓名不能为空。

-- 创建表 系 DEPARTMENT （系号departmentid，系名deptname）
CREATE TABLE DEPARTMENT (
	departmentid VARCHAR(32) PRIMARY KEY,
	deptname VARCHAR(32) UNIQUE NOT NULL);
	
-- 班CLASS （班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num）
CREATE TABLE `class` (
	classid INT PRIMARY KEY,
	`subject` VARCHAR(32) NOT NULL DEFAULT '',
	deptname VARCHAR(32) , -- 外键字段，在表定义后指定
	enrolltime INT NOT NULL DEFAULT 2000,
	num INT NOT NULL DEFAULT 0,
	FOREIGN KEY (deptname) REFERENCES  DEPARTMENT(deptname));
	
-- 学生STUDENT （学号studentid，姓名name，年龄age，班号classid）
CREATE TABLE hsp_student (
	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,'无机化学','化学',1995,29);
INSERT INTO class VALUES(112,'高分子化学','化学',1996,25);
INSERT INTO class VALUES(121,'统计数学','数学',1995,20);
INSERT INTO class VALUES(131,'现代语言','中文',1996,20);
INSERT INTO class VALUES(141,'国际贸易','经济',1997,30);
INSERT INTO class VALUES(142,'国际金融','经济',1996,14);

INSERT INTO hsp_student VALUES(8101,'张三',18,101);
INSERT INTO hsp_student VALUES(8102,'钱四',16,121);
INSERT INTO hsp_student VALUES(8103,'王玲',17,131);
INSERT INTO hsp_student VALUES(8105,'李飞',19,102);
INSERT INTO hsp_student VALUES(8109,'赵四',18,141);
INSERT INTO hsp_student VALUES(8110,'李可',20,142);
INSERT INTO hsp_student VALUES(8201,'张飞',18,111);
INSERT INTO hsp_student VALUES(8302,'周瑜',16,112);
INSERT INTO hsp_student VALUES(8203,'王亮',17,111);
INSERT INTO hsp_student VALUES(8305,'董庆',19,102);
INSERT INTO hsp_student VALUES(8409,'赵龙',18,101);

SELECT * FROM department
SELECT * FROM class
SELECT * FROM hsp_student

-- (3) 完成以下查询功能
--   3.1 找出所有姓李的学生。
-- 查表 hsp_student , like
SELECT * 
	FROM hsp_student
	WHERE `name` LIKE '李%' 
--   3.2 列出所有开设超过1个专业的系的名字。

-- 1. 先查询各个系有多少个专业
SELECT COUNT(*) AS nums, deptname 
	FROM class
	GROUP BY deptname HAVING nums > 1
--   3.3 列出人数大于等于30的系的编号和名字。
-- 1. 先查出各个系有多少人, 并得到 >= 30 的系

SELECT SUM(num) AS nums, deptname  
	FROM class 
	GROUP BY  deptname 
	HAVING nums >= 30
	
-- 2. 将上面的结果看成一个临时表 和 department 联合查询即可

SELECT  tmp.*, department.departmentid
	FROM department , (
		SELECT SUM(num) AS nums, deptname  
		FROM class 
		GROUP BY  deptname 
		HAVING nums >= 30
	) tmp 
	WHERE department.deptname = tmp.deptname;
	


-- (4) 学校又新增加了一个物理系，编号为006
-- 添加一条数据
INSERT INTO department VALUES('006','物理系');
-- (5) 学生张三退学，请更新相关的表

-- 分析：1. 张三所在班级的人数-1 2. 将张三从学生表删除  3. 需要使用事务控制

-- 开启事务
START TRANSACTION;
-- 张三所在班级的人数-1 
UPDATE class SET num = num - 1
	WHERE classid = (
		SELECT classid 
			FROM hsp_student 
			WHERE NAME = '张三'
	);

DELETE 
	FROM hsp_student
	WHERE NAME = '张三';
	
-- 提交事务
COMMIT;

SELECT * FROM hsp_student;
SELECT * FROM class