net start mysql //启动mysql服务
net stop mysql //停止mysql服务
//本机默认端口3306登入
mysql -u root -p //进入mysql管理终端 -u 用户名 -p 密码(此处可不写，回车后再填也可以) 
//连接Mysql数据库指令
mysql -h 主机ip -P 端口 -u 用户名 -p密码
//-p密码 不需要待空格
//-p密码，密码可以省略回车后再输入
//-h 主机IP ，默认本机
//-P 端口， 默认3306
flush privilesges //刷新权限

CREATE DATABASE db01;//创建db01数据库
CCREATE DATABASE `name`;//使用反引号规避关键字

SHOW DATABASES;//显示数据库
SHOW CREATE DATABASE db01;//显示创建数据库语句
DROP DATABASE db01;//删除数据库

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
//案例
mysqldump -u root -p -B db01 > name.sql;

Source 文件名.sql
//案例
Source name.sql

//备份数据库的表
mysqldump -u 用户名 -p 密码 数据库 表1 表2 表n > d:\\文件名.sql

//修改表名
Rename table 表名 to 新表名;
//修改表字符集
alter table 表名 character set 字符集;

// 修改job列，使其长度为60
ALTER TABLE emp 
	MODIFY job VARCHAR(60) NOT NULL DEFAULT '';

-- 表复制(蠕虫复制)
CREATE TABLE my_tab01
	(id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);

DESC my_tab01;
SELECT * FROM my_tab01;

-- 自我复制
-- 1. 把emp表复制到my_tab01表
INSERT INTO my_tab01
	(id,`name`,sal,job,deptno)
	SELECT empno, ename, sal, job, deptno
		FROM emp;
SELECT * FROM emp;
-- 自我复制  每执行一次翻倍
INSERT INTO my_tab01
	SELECT * FROM my_tab01;

-- 删除一张重复的表
-- 1. 先创建一张my_tab02
-- 2. 让my_ tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp;-- 把emp表的结构(列) 复制到my_tab02

DESC my_tab02;

INSERT INTO my_tab02
	SELECT * FROM emp;
SELECT * FROM my_tab02;

-- 3. 考虑去重
-- (1) 创建一个新的临时空表my_tem,该表结构与my_tab02一致
CREATE TABLE my_tem LIKE my_tab02;
-- (2) 将my_tem 通过distinct 关键字处理后把记录复制到my_tem表
INSERT INTO my_tem
	SELECT DISTINCT * FROM my_tab02;
-- (3) 清除my_tab02的所有记录
DELETE FROM my_tab02;
-- (4) 把my_tem表的记录复制到my_tab02
INSERT INTO my_tab02
	SELECT * FROM my_tem;
-- (5) drop 删除掉my_tem表
DROP TABLE my_tem;
-- 
SELECT * FROM my_tem

//显示表结构 可以查看表所有的列
DESC 表名 
//案例
DESC emp;

//定义无符号的整数
create table t1 (id tinyint);//默认是有符号的
create table t1 (id tinyint unsigned);//无符号定义

FLOAT/DOUBLE[UNSIGNED]//float 单精度，double 双精度
/*
可以支持更加精确的小数位，M是小数位数(精度)的总数,D是小数点(标度)后面的位数
如果D是0，则没有小数点 
M最大65，D最大是30；如果D被省略，默认是0，如果M被省略，默认是10
*/
DECIMAL[M,D][UNSIGNED]

CHAR(size)//固定长度字符串，最大255字符 
VARCHAR(size)//可变长度字符 最大65532字节 [utf8编码最大21844字符 其中1-3字节用于记录大小]

//案例
insert into goods values(1,200);
//细节
//(1) 插入的数据应与字段的数据类型相同。比如把'abc'添加到int类型会错误
//(2) 数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
//(3) 在values中列出的数据位置必须与被加入的列的排列位置相对应。
//(4) 字符和日期型数据应包含在单引号中。
//(5) 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
//(6) insert into tab name(列名..) values (),(),() 形式添加多条记录
//(7) 如果是给表中的所有字段添加数据，可以不写前面的字段名称
//(8) 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

//案例
update goods set sal = 2 where id = 1;
//细节
//(1) UPDATE语法可以用新值更新原有表行中的各列。
//(2) SET子句指示要修改哪些列和要给予哪些值。
//(3) WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。
//(4)如果需要修改多个字段,可以通过set字段1=值1,字段2=值2.….

//案例
delete from goods where id = 1;
//细节
//(1) 如果不使用where子句，将删除表中所有数据。
//(2) Delete语句不能删除某一列的值(可使用update 设为null或者"")使用delete语句仅删除记录，不删除表本身。
//(3) 如要删除表，使用drop语句。drop table表名;

//案例
select * from goods;
//细节
//(1) Select 指定查询哪些列的数据。
//(2) column指定列名。
//(3) *号代表查询所有列。
//(4) From指定查询哪张表。
//(5) DISTINCT可选,指显示结果时,是否去掉重复数据

select name,(math + chinese + english) from students;

select name as '名字' from students;

//案例
SELECT * FROM emp 
	ORDER BY sal;
//细节
//(1) Order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
//(2) Asc升序[默认]、Desc降序
//(3) ORDER BY子句应位于SELECT语句的结尾。

//案例
SELECT COUNT(*) FROM student;
//细节
//(1) 如果该值是null就不会统计
//(2) count返回行的总数
// count(*) 和 count(列) 的区别
// count(*)  返回满足条件的记录的行数
// count(列)：统计满足条件的某列有多少 但是会排除 为null的情况

// 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS 数学总分, SUM(english) AS 英语总分, SUM(chinese) AS 语文总分 FROM student;

// 求一个班级总分平均分
SELECT AVG(chinese + english + math) FROM student;

// Max Min 函数的使用
// 求班级最高分 和最低分
SELECT MAX(chinese + english + math), MIN(chinese + english + math) FROM student;

// 显示平均工资低于2000的部门号和它的平均工资//别名 
// 1. 显示均工资低于2000的部门号
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;

// 2. 显示平均工资低于2000的部门号和它的平均工资
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000;

-- 字符串相关的函数的使用 
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,....]) 连接子串，将多个列拼接成一列
SELECT CONCAT (ename, ' 工作是 ', job) FROM emp;

-- INSTR(string, substring)  返回substring 在 string 中出现的位置, 没有返回0
-- dual 亚元表 系统表 可以作为测试表使用
SELECT INSTR ('zhibi', 'bi') FROM DUAL;

-- UCASE(string2)  转换成大写
SELECT UCASE(ename) FROM emp;

-- LCASE(string2) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT(string2, length)  从string2中的左边起取length个字符
-- RIGHT(string2, length)  从string2中的右边起取length个字符
SELECT LEFT(ename, 2) FROM emp;

-- LENGTH (string) string 长度[按照字节]
SELECT LENGTH (ename) FROM emp;
-- REPLACE(str, search str, replacr str) 在str中 用 replace str替换 search str
SELECT ename, REPLACE(job, 'MANAGER','经理') FROM emp;

-- STRCMP(string1, string2) 逐字符比较量字串大小
SELECT STRCMP('ZB','ZB') FROM DUAL;

-- SUBSTRING(str, position [, length]) 从str的position开始[从1开始计算]
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM(string2) RTRIM(string2) trim 去除前端空格或后端空格
SELECT LTRIM('     执笔') FROM DUAL;
SELECT LTRIM('执笔     ') FROM DUAL;
SELECT TRIM('     执笔     ') FROM DUAL;

-- 以首字母小写的方式显示所有emp员工的姓名
-- 1. 首字母小写
-- 2. 获取除首字母的所有字母
-- 3. 拼接 
SELECT CONCAT (LCASE(LEFT(ename,1)), SUBSTRING(ename, 2)) FROM emp;

-- 第二种方式
-- 1. 获取首字母小写
SELECT LCASE(LEFT(ename,1)) FROM emp;
-- 2. 替换第一个字母
SELECT REPLACE(ename,LEFT(ename,1),LCASE(LEFT(ename,1))) FROM emp;

-- 数学相关函数
--ABS (num)绝对值
SELECT ABS (-10)FROM DUAL;

-- BIN(decimal number ) 十进制转二进制
SELECT BIN(10) FROM DUAL;

-- CEILING (number2 )向上取整，得到比num2大的最小整数
SELECT CEILING (-1.1) FROM DUAL;

-- CONV (number2 ,from_base , to_base)进制转换 conv 全称 convert转换
-- 8 是十进制数，转换为2进制输出
SELECT CONV (8 , 10, 2) FROM DUAL;

-- FLOOR (number2 )向下取整,得到比 num2小的最大整数
SELECT FLOOR(1.1) FROM DUAL;

-- FORMAT(number ,decimal places )保留小数位数(四舍五入)
SELECT FORMAT(12.12354668,2) FROM DUAL;

-- HEX(DecimalNumber )转十六进制

-- LEAST (number , number2[ ,..])求最小值
SELECT LEAST(1,2,-10,8) FROM DUAL;

-- MOD(numerator ,denominator )求余
SELECT MOD(10, 3) FROM DUAL;

-- RAND ( [seed]) RAND ( [seed])其范围为0 ≤ v ≤ 1.0 随机数
-- 会变的随机数 rand()
SELECT RAND() FROM DUAL;
-- 一旦执行就不变化的随机数 rand(seed) seed不变 随机数不变化
SELECT RAND(6) FROM DUAL;

-- 日期时间相关函数的使用
-- CURRENT_DATE () 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME () 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP () 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;

-- 创建一张存放信息的表
CREATE TABLE mes(
	id INT,
	content VARCHAR(30),
	send_time DATETIME);

INSERT INTO mes 
	VALUES(1, '新闻联播', CURRENT_TIMESTAMP());
INSERT INTO mes 
	VALUES(2, '浙江新闻', NOW());
INSERT INTO mes 
	VALUES(3, '广州新闻', NOW());
	
SELECT * FROM mes;
SELECT NOW() FROM DUAL;

-- 上应用实例
-- 显示所有新闻信息，发布日期只显示日期，不用显示时间.
SELECT id, content,DATE(send_time)
	FROM mes;
-- 请查询在10分钟内发布的帖子
SELECT * 
	FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * 
	FROM mes
	WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);	

-- 请在mysql的sql语句中求出 2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
-- 请用mysgl 的sql语句求出你活了多少天?[练习]
SELECT DATEDIFF(NOW(),'1998-06-05') FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习]
-- 先求出活80岁时的日期
SELECT DATE_ADD('1998-06-05', INTERVAL 80 YEAR) FROM DUAL;
-- 计算还能活多少天
SELECT DATEDIFF(DATE_ADD('1998-06-05', INTERVAL 80 YEAR),NOW()) FROM DUAL;


-- YEAR|MONTH|DAY| DATE(datetime)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2022-6-17') FROM DUAL;

-- unix_timestamp()  返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- FROM_UNIXTIME() 可以把unix_timestamp 秒数 转成指定格式的日期
-- %Y-%m-%d %H:%i:%s 固顶格式 表示年月日时分秒
-- 意义：在开发中，存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') FROM DUAL;

-- 加密和系统函数使用

-- USER() 查询用户
-- 可以查看登入到mysql的有哪些用户，以及登入的IP
SELECT USER() FROM DUAL;-- 用户@IP地址
-- DATABASE() 查询当前使用数据库的名称
SELECT DATABASE();

-- MD5(str)  为字符串算出一个MD5 32的字符串 常用(用户密码加密)
-- root 密码是 123456--> 加密md5 -> 在数据库中存放的加密后的密码
SELECT MD5('123456') FROM DUAL;
SELECT LENGTH(MD5('123456')) FROM DUAL;

-- 演示用户表 存放密码是md5
CREATE TABLE zb_user
	(id INT,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	psw CHAR(32) NOT NULL DEFAULT '');
INSERT INTO zb_user VALUES(1,'执笔', MD5('123456'));
	
SELECT * FROM zb_user;

SELECT * FROM zb_user
	WHERE `name` = '执笔' AND psw = MD5('123456');
	
-- PASSWORD(str) 加密函数 mysql数据库的用户密码就是PASSWORD函数加密
SELECT PASSWORD('123456') FROM DUAL;
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user;

-- 流程控制语句

-- IF(expr1,expr2,expr3) 如果expr1为true 就返回expr2 否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;
-- IFNULL(expr1,expr2) 如果expr1不为空null，则返回expr1 否则返回expr2
SELECT IFNULL(NULL, '执笔') FROM DUAL;
-- #SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSB expr5 END;[类似多重分支]
-- 如果expr1为TRUE,则返回expr2,如果expr3为t，返回expr4，否则返回expr5
SELECT CASE 
	WHEN TRUE THEN 'jack' 
	WHEN FALSE THEN 'tom' 
	ELSE 'mary' END;
	
-- 1. 查询emp表 如果 comm是null 则显示0.0
SELECT ename, IFNULL(comm,0.0) FROM emp;
-- 判断是否为null 要使用 is null 半段不为空 使用 is not
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;

-- 2. 如果emp 表是job是CLERK 则显示职员 MANAGER显示经理 SALESMAN显示销售人员 其他正常
SELECT ename,(SELECT CASE 
		WHEN job = 'CLERK' THEN '职员' 
		WHEN job = 'MANAGER' THEN '经理' 
		WHEN job = 'SALESMAN' THEN '销售人员' 
		ELSE job END) AS 'job',job
	FROM emp;

-- 查询加强
-- 使用where子句
-- 如何查找1992.1.1后入职的员工
-- 在mysql中，日期类型可以直接比较
SELECT * FROM emp
	WHERE hiredate > '1992-01-01'
-- 如何使用like操作符
-- %表示0到多个字符  
-- _表示单个字符 

-- 如何显示首字符为s的员工姓名和工资
SELECT ename, sal FROM emp 
	WHERE ename LIKE 'S%';
-- 如何显示第三个字符为大写o的所有员工的姓名和工资
SELECT ename, sal FROM emp
	WHERE ename LIKE '__o%';
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;
-- 查询表结构
DESC emp;

-- 公式
SELECT * FROM emp
	ORDER BY empno
	LIMIT 每页显示记录数 * (第几页-1), 每页显示的记录数;

-- 按照雇员的empno号降序取出 每页显示5条记录 分别显示第3页 第5页对应的sql语句
-- 第三页
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 10,5;
-- 第五页
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20,5;

-- 请统计各个部门的平均工资，并且是大于1000的,并且按照平均工资从高到纸排序，
-- 取出前两行记录.
SELECT AVG(sal) AS avg_sal, deptno
	FROM emp
	GROUP BY deptno 
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2;

SELECT ename, sal, dname,emp.deptno 
	FROM emp,dept
	WHERE emp.deptno = dept.deptno;

--  多表查询  自连接
-- 显示公司员工 和他的上级的名字
-- 员工名字在emp 上级的名字在emp 
-- 员工和上级是通过 emp表 的mgr列关联
-- 自连接特点：
-- 		1. 把同一张表当做两张表使用
-- 		2. 需要给表取别名 格式： 表 别名

SELECT worker.ename AS '职员名', boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;

SELECT * FROM emp;

-- 子查询使用 嵌套查询
-- 如何显示与SMITH同一部门的所有员工
-- 单行子查询
/* 
	1. 先把查询到的SMITH的部门得到
	2. 把上面查询的select 语句当做一个子查询来使用
	
*/
SELECT deptno
	FROM emp
	WHERE ename = 'SMITH'
	
SELECT * 
	FROM emp
	WHERE deptno = (
		SELECT deptno
		FROM emp
		WHERE ename = 'SMITH'
	);

-- 多行子查询
-- 如何查询和部门10的工作相同的雇员
-- 名字、岗位、工资、部门号，但是不含10号部门自己的雇员
/*
	1. 先获取10号部门有哪些职位
	2. 名字、岗位、工资、部门号，但是不含10号部门自己的雇员
*/
SELECT * FROM emp;
SELECT DISTINCT job 
	FROM emp
	WHERE deptno = 20;
	
SELECT ename, job,sal,deptno
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job 
		FROM emp
		WHERE deptno = 20
	) AND deptno != 20; -- 不等于也可以换成 <>

SELECT temp.ename, emp.empno 
	FROM (SELECT ename, empno FROM emp) temp, emp;

-- all 
-- 显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ALL(
			SELECT sal
				FROM emp
				WHERE deptno = 30
				);

-- 显示工资比部门30的其中一个高员工的工资高的员工姓名、工资和部门号
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ANY(
			SELECT sal
				FROM emp
				WHERE deptno = 30
				);

-- 多列子查询
-- 查询与smith 的部门和岗位完全相同的所有雇员(并且不含smith本人)
-- 1. 先获取smith本人你的部门和岗位信息
SELECT deptno, job
	FROM emp
	WHERE ename = 'SMITH';
	
-- 2. 查询与smith 的部门和岗位完全相同的所有雇员(并且不含smith本人)
SELECT *
	FROM EMP
	WHERE (deptno, job) = (
		SELECT deptno, job
		FROM emp
		WHERE ename = 'SMITH'
	) AND ename != 'SMITH';

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

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

-- 左外连接 
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;

CREATE TABLE t17
	(id INT PRIMARY KEY,-- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32)
	);

-- 主键使用细节
-- 1. primary key不能重复而且不能为null。
INSERT INTO t17
	VALUES(NULL,'zb','zb@sohu.com');
-- 2. 一张表最多只能有一个主键,但可以是复合主键
CREATE TABLE t18
	(id INT PRIMARY KEY,-- 表示id列是主键
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32)
	);-- 错误写法
	
-- 复合主键 (id和name做成复合主键 只有Id和 name都相同才不能添加进去) 
CREATE TABLE t19
	(id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`)
	);

-- 3. 主键的指定方式有两种
--    直接在字段名后指定:字段名primakry key   
CREATE TABLE t20
	(id INT PRIMARY KEY,
	`name` VARCHAR(32),
	email VARCHAR(32)
	);
 
--    在表定义最后写primary key(列名);
CREATE TABLE t21
	(id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`)
	);
-- 4. 使用desc表名，可以看到primary key的情况.
DESC t21;

create table student (
	id int not null);

CREATE TABLE t22
	(id INT UNIQUE, -- 表示id列是不可以重复的
	`name` VARCHAR(32),
	email VARCHAR(32));

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

-- foreign key 外键
# 这里是给订单表设置一个外键，外键是用户表的id字段
    CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
# 外键约束的格式
    CONSTRAINT `外键约束的名称` FOREIGN KEY (`从表字段`) REFERENCES `主表名` (`主表字段`) 
    约束规则...

-- 创建 主表 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(2,'jack',200);
INSERT INTO my_stu
	VALUES(3,'zb',300); -- 这里会失败 因为300号班级不存在
SELECT * FROM my_stu;

INSERT INTO my_stu
	VALUES(4,'zb',NULL);-- 可以加入

-- 一旦建立主外键关系就不能随意删除了
DELETE FROM my_class
	WHERE id = 100;

-- check 的使用
-- mysql 5.7 目前还不支持check 只做语法校验 但不会生效
-- oracle sql server 这两个数据库会生效

-- 测试
CREATE TABLE t24 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK (sal > 1000 AND sal < 2000)

//添加自增长方式
insert into xxx(字段1, 字段2....) values(null,'值'...);
insert into xxx(字段2..….) values(值1'，"值2'..…);
insert into xxx values(null,'值1'.....);

//细节
//(1)一般来说自增长是和primary key配合使用
//(2)自增长也可以单独使用[但是需要配合一个unique]
//(3)自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
//(4)自增长默认从1开爱:你新的升始直;table表名auto increment =新的开始值;
//(5)如果你添加数据时，给自增长字段（(列)指定的有值，则以指定的值为准,如果指定了自增长，一般来说，就按照自增长的规则来添加数据

-- 自增长
-- 创建表
CREATE TABLE t25(
		id INT PRIMARY KEY AUTO_INCREMENT, -- 自增长
		email VARCHAR(32) NOT NULL DEFAULT '',
		`name` VARCHAR(32) NOT NULL DEFAULT '');

-- mysql的索引使用
-- 创建索引
CREATE TABLE t25(
	id INT,
	`name` VARCHAR(32)); 
	
-- 查询表是否有索引
SHOW INDEXES FROM t25; 
-- 添加索引
-- 添加唯一索引  
CREATE UNIQUE INDEX id_index ON t25(id);
-- 添加普通索引方式1
CREATE INDEX id_index ON t25(id);
-- 1. 如果某列的值，是不会重复的 则优先考虑使用unqiue索引 否则使用普通索引

-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)

-- 添加主键索引
-- 1. 在创建表的时候指定主键
-- 2. 建表后添加`hzy_db02`
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32));
	
ALTER TABLE t26 ADD PRIMARY KEY (id)

SHOW INDEXES FROM t26;


-- 删除索引
DROP INDEX id_index ON t25;

-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;

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

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

//mysql数据库控制台事务的几个重要操作
(1) start transaction //开始一个事务
(2) savepoint//保存点名--设置保存点
(3) rollback to//保存点名--回退事务
(4) rollback //回退全部事务
(5) commit//提交事务,所有的操作生效,不能回退

-- 事务操作

-- 创建表
CREATE TABLE t27(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	
-- 开启事务
START TRANSACTION;
-- 设置事务保存点
SAVEPOINT a;

-- 执行dml语句
INSERT INTO t27 VALUES(1,'tom');

SELECT * FROM t27;
-- 执行dml语句
SAVEPOINT b;
INSERT INTO t27 VALUES(2,'jack');

-- 回退事务
ROLLBACK TO a;

-- 回退全部事务
ROLLBACK;

-- 提交事务
COMMIT;

//细节
1．如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2．如果开始一个事务，你没有创建保存点.你可以执行rollback，默认就是回退到你事务开始的状态.
3.你也可以在这个事务中(还没有提交时).创建多个保存点.比如:savepoint aaa;执行dml , savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点.
5. mysql的事务机制需要innodb的存储引擎还可以使用,myisam不好使.
6．开始一个事务start transaction, set autocommit=off;

-- 事务细节

-- 1. 如果不开启事务，默认情况下 dml操作是自动提交的 不能回滚
INSERT INTO t27 VALUES(3,'mary');-- 自动提交 commit
SELECT * FROM t27;

-- 2. 如果开启一个事务，没有创建保存点 执行rollback 
-- 默认回退到事务开始的状态
START TRANSACTION;
INSERT INTO t27 VALUES(4,'smith');

INSERT INTO t27 VALUES(5,'kin');
ROLLBACK -- 直接回退到事务开始的状态
COMMIT;
-- 3. 在事务没提交前可以创建 多个保存点 savepoint aaa  savepoint bbb
-- 4. 事务没有提交前 可以选择回退到那个保存点
-- 5. Innodb 存储引擎支持事务  MyISAM不支持
-- 6. 开始一个事务 start transaction, set autocommit=off;
SET autocommit =off;

-- mysql事务  隔离级别
-- 1. 登入mysql
mysql -u root -p;

-- 2. 查询当前事务的隔离级别
SELECT @@tx_isolation;

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

-- 3. 把其中一个控制台的隔离级别设置为 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 4. 选择数据库 
USE 数据库名

-- 5. 创建表
CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT);
	
-- 查看当前会话隔离级别
SELECT @@tx_isolation;
-- 查看系统当前会话级别
SELECT @@global.tx_isolation;
-- 设置当前会话级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [设置的级别]

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

//显示当前数据库支持的 存储引擎
show engines;
//修改存储引擎
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;
	
-- 修改存储引擎
ALTER TABLE t29 ENGINE = INNODB;

//基本使用
1. create view视图名as select语句
2. alter view视图名as select语句
3. SHoW CREATE VIEW 视图名
4. drop view视图名1,视图名2
 
-- 视图的使用
-- 创建一个视图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;

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

-- 删除视图
DROP VIEW emp_view01;
    

//创建用户 同时指定密码
create user '用户名' @'允许登入的位置' identified by '密码';
    
//删除用户
drop user '用户名' @'允许登入的位置';

//修改用户密码(需要权限)
//修改自己的密码
set password = password('密码');
//修改他人密码(需要有修改权限)
set password for '用户名'@'登入位置' = password(密码);

grant 权限列表 on 库.对象名to'用户名'@'登录位置'[identified by'密码']

//1. 权限列表，多个权限用逗号分开
grant select on .......
grant select,delete,create on .....
grant all 【privileges】on .....//表示赋予该用户在该对象上的所有权限
//2. 特别说明
*.*：代表本系统中的所有数据库的所有对象(表，视图，存储过程)
库.*：表示某个数据库中的所有数据对象(表，视图，存储过程等)
//3. identified by可以省略，也可以写出.
//(1)如果用户存在，就是修改该用户的密码。
//(2)如果该用户不存在，就是创建该用户!

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

FLUSH PRIVILEGES;

-- 设学校环境如下:一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。
-- 现要建立关于系、学生、班级的数据库，关系模式为:
-- 班CLASS (班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)
CREATE TABLE class(
	classid INT PRIMARY KEY NOT NULL,
	`subject` VARCHAR(32) NOT NULL DEFAULT '',
	deptname VARCHAR(32) NOT NULL DEFAULT '',
	enroltime YEAR NOT NULL,
	num INT NOT NULL DEFAULT 0,
	FOREIGN KEY (deptname) REFERENCES department(deptname));
	
SELECT * FROM class;
DROP TABLE class;

-- 学生STUDENT (学号studentid，姓名name，年龄age，班号classid)
CREATE TABLE students (
	studentid INT PRIMARY KEY NOT NULL,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	age INT NOT NULL DEFAULT 0,
	classid INT NOT NULL,
	FOREIGN KEY (classid) REFERENCES class(classid));
SELECT * FROM students;
DROP TABLE students;

-- 系DEPARTMENT(系号departmentid，系名deptname)
CREATE TABLE department(
	departmentid VARCHAR(32) NOT NULL DEFAULT '',
	deptname VARCHAR(32) PRIMARY KEY NOT NULL DEFAULT '');
DESC department;
SELECT * FROM department;
DROP TABLE department;

-- 添加
INSERT INTO department VALUES('001','数学'),('002','计算机'),('003','化学'),('004','中文'),('005','经济');
DELETE FROM department;

INSERT INTO class 
	VALUES(101,'软件','计算机',1995,20),
		(102,'微电子','计算机',1996,30),
		(111,'无机化学','化学',1995,29),
		(112,'高分子化学','化学',1996,25),
		(121,'统计数学','数学',1995,20),
		(131,'现代语言','数学',1996,30),
		(141,'国际贸易','经济',1997,30),
		(142,'国际金融','经济',1996,14);
		
INSERT INTO students 
	VALUES('8101','张三',18,101),
		('8102','钱四',16,121),
		('8103','王玲',17,131),
		('8105','李飞',19,102),
		('8109','赵四',18,141),
		('8110','李可',20,142),
		('8201','张飞',18,111),
		('8302','周瑜',16,112),
		('8203','王亮',17,111),
		('8305','董庆',19,102),
		('8409','赵龙',18,101),
		('8510','李丽',20,142);
		
-- (3)完成以下查询功能
-- 3.1找出所有姓李的学生。
SELECT * FROM students
	WHERE `name` LIKE '李%';
-- 3.2 列出所有开设超过1个专业的系的名字。
SELECT * FROM department;
SELECT * FROM class;

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

-- 2. 看成零时表进行与department 组合查找
SELECT departmentid, department.deptname,nums
	FROM department,(
		SELECT SUM(num) AS nums,deptname
		FROM class
		GROUP BY deptname HAVING nums > 30
	) tem
	WHERE tem.deptname = department.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 students
			WHERE `name` = '张三'
	);

DELETE FROM students
	WHERE `name` = '张三';
	
-- 提交事务
COMMIT;
SELECT * FROM students;
SELECT * FROM class;