Mysql笔记

windows安装记录Windows中Mysql安装-CSDN博客

用到的库

通过网盘分享的文件：atguigudb.sql
链接: https://pan.baidu.com/s/1YfC20c2vK9odn-XRJJwUJw 提取码: utk7 
--来自百度网盘超级会员v5的分享

Mysql4中表关联关系

1.1对1,比较少用,因为完全可以一张表,当有些数据是常用的,二有些数据是不常用的情况下,可以使用1对1关系,例如员工姓名,年龄,身份证号可能是常用的,但是员工的背景,教育等人员档案是常用的,可以考虑用1对1

2.1对多.例如顾客可能有多个订单

3.多对多,必须要有中间表,例如用户和角色,中间表记录用户和角色的主键

4.自我引用(自关联),例如部门表

cmd中

可以通过

net stop mysql80或者net start mysql80

来关闭或启动mysql,这里mysql80是安装时候指定的服务名称

net stop mysql80 && net start mysql80   就是先关闭在启动

另外也可以用

sc stop mysql80和sc start mysql80 作用差不多   sc方式看到的参数多些

进入mysql

mysql -h localhost -u root -p123456  -P 3306

如果是本机可以

mysql -uroot -p123456

这里注意一点,  -p后面不能有空格,mysql会误认为空格也是密码,其他都可以有空格,另外不建议密码直接写上去,如果密码不直接写上去,例如端口是13306的话

mysql -uroot -P 13306 -p   考虑为什么把-p写最后面  原因密码写出来总不好吧 那么只能放在最后

退出mysql    使用 exit或者quit

cmd中可以通过命令

mysql --version;查看mysql数据库版本

当进入mysql之后可以通过命令

select version();来查看数据库版本

show databases;查看所有数据库

create database testdb1;创建数据库,没有指定字符集,默认用的是mysql默认字符集,可以在my.ini中进行修改,mysql8不需要  但是mysql5.7或者以下  这样创建数据库没有自定字符集的话,默认就是mysql默认字符集

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

在[mysql]   和  [mysqld]下  分别如图设置   mysql8不需要

注意,这样要重启MYSQL服务才可以生效

还需要注意一点,即使你改了,但是原先创建的数据库和表的字符集还是没有改变

ALTER DATABASE dbtest1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SHOW CREATE DATABASE dbtest1; 查看是否已经修改

ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 

如果是修改特定字段

ALTER TABLE employees MODIFY name VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 

 show create table employees; 查看

use dbtest1;使用dbtest1数据库

show tables;查看dbtest1数据库中所有表

create table employees(id int,name varchar(15));创建表,没有指定字符集,默认按use的数据库的字符集

insert into employees values(1001,'Tom');

insert into employess values(1002,'Han'),(1003,'Jack');

select * from employees;查看表中所有数据

insert into employess values(1004,'中国');如果不是mysql8,5.7或者以下会报错

show variable like 'datadir';可以查看DB真实数据的保存目录

show variable like 'character_%';可以查看当前mysql系统使用的字符集

show variable like 'collation_%';查看当前mysql数据库的排序规则

DROP DATABASE db1;删除库

DROP TABLE tb1;删除表及结构

DELETE TABLE tb1;逐行删除表中数据

TRUNCATE TABLE tb2;;删除表中数据,大数据删除,执行速度快,因不记录日志

#基本的SELECT语言

#SQL的分类
/*
DDL:数据定义语言:CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE 

DML:数据操作语言:INSERT \ DELETE \ UPDATE

DQL:数据查询语言:严格来说SELECT属于DQL,但有时也被归纳为DML

DCL:数据控制语言:GRANT(授予权限)  REVOKE(撤销权限)

TCL:事务控制语言:COMMIT \ ROLLBACK \ SAVEPOINT \ SET TRANSACTION
很多时候有些人把TCL归纳为DCL,都可以
*/

#以下主要是DQL 即SELECT 数据查询

USE dbtest1;

CREATE TABLE emp(id int,name varchar(15));

INSERT INTO emp
VALUES(1002,'Tom');

INSERT INTO emp
VALUES(1003,'Jerry');

SELECT * FROM emp;

DROP DATABASE dbtest1;

#字符串,日期时间类型的变量需要使用一对''表示 
#在特殊情况下,别名用双引号表示("")

/*
数据导入2种方式
1:使用source 文件的全路径名(在命令行导入,工具中不行)
2:工具导入
*/

SOURCE C:/Users/user/Desktop/asd.sql;

SHOW DATABASES;

USE atguigudb;

SHOW TABLES;

SELECT * FROM employees;

#基本SELECT语句
SELECT 1;
SELECT 9/2;
SELECT 9/2,3*3;
SELECT 9/2 a,3*3 b;
SELECT 10/3;
SELECT 9.0/2;
SELECT 1+1.3*2;
SELECT 2+2+3*2+1.1 FROM DUAL;#DUAL是伪表
#在MYSQL中可以SELECT 1;  但是在Oracle中DUAL必须写  就是说必须SELECT 1 FROM DUAL;DUAL是Oracle 中的伪表,它是一个特殊的表,默认只有一行一列。
SELECT sysdate(); #2024-12-10 06:28:31
SELECT sysdate() "select";

/*
SQL的书写顺序 SELECT...FROM...JOIN...ON...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT
SQL的执行顺序 FROM...JOIN...ON...WHERE...GROUP BY...HAVING...SELECT...DISTINCT...ORDER BY...LIMIT
*/

SELECT * FROM employees;

SELECT employee_id,last_name,salary FROM employees;

#列的别名,去重,NULL,排序DESC,默认ASC,注意:列别名不能是纯数字 例如1,2,3  可以是"1","2","3"

#列的别名AS,全称ALIAS,可以省略,特殊时候使用双引号"",不要使用'',原因是MYSQL中不规范,在其他数据库就不能用单引号  规范
SELECT employee_id emp_id,last_name AS lname,department_id "部门ID",salary*12 "annual sal" FROM employees;

#去除重复行 DISTINCT 如果后面是多个字段,去重条件会将后面几个字段做一个整体去去重
SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT department_id,salary FROM employees;#将department_id,salary做为整体去重

#空值一旦参与运算 结果一定也为NULL 空值:NULL
SELECT employee_id,salary "月工资",salary*(1+commission_pct)*12 "年工资",commission_pct "绩效" FROM employees

#使用COALESCE(arg1,arg2,....argn)函数,从左到右依次检查,返回第一个非NULL表达式
SELECT 
    employee_id,
    salary AS "月工资",
    salary * (1 + COALESCE(commission_pct, 0)) * 12 AS "年工资",
    commission_pct AS "绩效"
FROM 
    employees;
        
SELECT COALESCE(1,2);#结果是1

#使用IFNULL(ARG1,ARG2)如果ARG1不是NULL,使用ARG1;如果ARG1是NULL,使用ARG2
SELECT 
    employee_id,
    salary AS "月工资",
    salary * (1 + IFNULL(commission_pct, 0)) * 12 AS "年工资",
    commission_pct "绩效"
FROM 
    employees;
    
#着重号 `` (1左边的这个键) 有张表,表明叫order,属于关键字
SELECT * FROM order;#报错
SELECT * FROM `order`;#可以

#查询常数
SELECT "XX有限公司",employee_id,last_name,salary FROM employees;

SELECT "XX有限公司" as "company",employee_id,last_name,salary FROM employees;

#显示表结构DESC 全称DESCRIBE(描述)
DESCRIBE employees;
DESC employees;#和SHOW COLUMNS FROM employees;作用一样

#过滤数据 WHERE
SELECT * FROM employees WHERE department_id=90;
SELECT * FROM employees WHERE last_name='King';

#查询员工12个月的工资总和,并起别名为annual salary
SELECT employee_id,last_name,salary*(1+IFNULL(commission_pct,0))*12 "annual salary" FROM employees;

#算数运算符的使用 加减乘除取模(即取余)  + - * /(也可以用div表示) %(也可以用mod表示)(取余)
SELECT 100,100+0,100-0,100+50,100/3,100%3,100 DIV 3,100 MOD 3 FROM DUAL;#注意100 DIV 3要有空格

SELECT 100 整数,100+0 加法,100-0 减法,100+50 加法,100/3 除法,100%3 取余,100 DIV 3 除法,100 MOD 3 取余 FROM DUAL;

可以看出 /  和div还是有区别的

#查询员工ID为偶数的员工信息
SELECT * FROM employees WHERE (employee_id%2)=0;#别用==哈

=      等于
<=>    安全等于
<>(!=) 不等于
<      小于    
<=     小于等于 
>      大于
>=     大于等于

IS NULL       为空运算符
IS NOT NULL   不为空运算符
LEAST         最小值运算符
GREATEST      最大值运算符
BETWEEN AND   两者之间运算符
ISNULL        为空运算符(特定数据库)
IN            属于运算符
NOT IN        不属于运算符
LIKE          模糊匹配运算符
REGEXP        正则表达式运算符
RLIKE         正则表达式运算符

#查询时候好比for循环 条件成立:1是true  不成立:0是false  NULL是个什么鬼
#既然有NULL参与比较结果一定是NULL
SELECT * FROM employees WHERE commission_pct=NULL;#一条数据也没有

SELECT * FROM employees WHERE commission_pct IS NULL;#查出commission_pct是NULL的

#安全等于和普通等于的区别  =  <=>  唯一区别在NULL上面  NULL参与比较都是NULL  但是在安全等于的时候就会得出结果

#安全等于<=>

SELECT 1<=>2,1 != 2,1<=>'1',1<=>'A',0<=>'A','A'<=>'B','A'<=>'A',1<=>NULL,'A'<=>NULL,NULL<=>NULL FROM DUAL;

SELECT * FROM employees WHERE commission_pct<=>NULL;
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE ISNULL(commission_pct);#这三句效果是一样的

#LEAST(arg1,arg2,...argn)最小   GREATEST(arg1,arg2,...argn)最大  LENGTH(String)针对于字符串长度

#查询工资在6000-8000的员工信息 BETWENN...AND...(包含边界)    针对的范围
SELECT * FROM employees WHERE salary>=6000 AND salary<=8000;
SELECT * FROM employees WHERE salary>=6000 && salary<=8000;
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 8000;#注意6000 和 8000顺序不能换  第一个是下限   第二个是上线

#查询不在6000到8000之内的 NOT BETWEEN AND 不包含边界
SELECT * FROM employees WHERE salary<6000 OR salary>8000;
SELECT * FROM employees WHERE salary<6000 || salary>8000;
SELECT * FROM employees WHERE salary NOT BETWEEN 6000 AND 8000;

#IN和NOT IN  针对的具体的值
#查询部门为10,20,30部门的员工信息
SELECT * FROM employees WHERE department_id=10 OR department_id=20 OR department_id=30;
SELECT * FROM employees WHERE department_id IN(10,20,30);

#查询员工工资不是6000,7000,8000的员工信息
SELECT * FROM employees WHERE salary!=6000 AND salary!=7000 AND salary!=8000;
SELECT * FROM employees WHERE salary NOT IN(6000,7000,8000);

#LIKE模糊查询
#查询last_name中包含字符'a'的员工信息   %:0个或多个字符     _:1个字符
SELECT * FROM employees WHERE last_name like '%a%';

#查询包含a和e的
SELECT * FROM employees WHERE last_name like '%a%e%' OR last_name like '%e%a%';

#查询第二个字符是a的
SELECT * FROM employees WHERE last_name like '_a%';

#查询第二个字符就是_第三个字符是a 或者说查询第一个字符就是_第二个字符是a,此时需要用转义字符:\
SELECT * FROM employees WHERE last_name like '_\_a%';#第二个字符开始是_a
SELECT * FROM employees WHERE last_name like '\_a%';#以_开头

#除了用转义字符\ 还可以用其他的 用ESCAPE说明它是一个转义字符 最好用些特殊符号
SELECT * FROM employees WHERE last_name like '_A_a%' ESCAPE 'A';

#正则表达式  REGEXP 或者 RLIKE

#查部门是50的但是工资少于10000的或者工资大于10000但是部门不是50的 XOR:追求的不同
SELECT last_name,salary,department_id FROM employees WHERE department_id=50 XOR salary>=10000

#注意 OR可以和AND一起使用,AND的优先级高于OR  先AND再OR

#查工资不在5000到12000之间的员工的姓名和工资
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

#查询在20或者50号部门工作的员工姓名和部门号
SELECT last_name,department_id FROM employees WHERE department_id IN(20,50);

#查询公司中没有管理者的员工姓名和job_id
SELECT last_name,job_id FROM employees WHERE manager_id IS NULL;

#查询公司中有奖金的员工姓名,工资和奖金级别
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

#查询员工姓名第三个字母是a的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE '__a%';

#查询姓名中有a和k的员工姓名
SELECT last_name FROM employees WHERE last_name like '%a%k%' OR last_name like '%k%a%';
SELECT last_name FROM employees WHERE last_name like '%a%' AND last_name like '%k%';
#查询姓名以e结尾的员工信息
SELECT * FROM employees WHERE last_name LIKE '%e';

#查询部门编号在80-100之间的姓名,工种
SELECT last_name,job_id FROM employees WHERE department_id BETWEEN 80 AND 100;

#查询manager_id是100,101,110的员工姓名,工资,管理者id
SELECT last_name,salary,manager_id FROM employees WHERE manager_id=100 OR manager_id=101 OR manager_id=110;
SELECT last_name,salary,manager_id FROM employees WHERE manager_id IN(100,101,110);

#排序与分页  排序默认ASC 写不写都在
#如果没有使用排序操作,默认使用添加数据的先后顺序显示的
SELECT * FROM employees;

#按salary从高到低排序
SELECT employee_id,last_name,salary FROM employees ORDER BY salary DESC;

#列的别名只能在ORDER BY后面使用 原因在于SQL的执行顺序
#这样会报错,原因执行WHERE时候,还不知道别名annual_sal是什么
SELECT employee_id,salary,salary*12 annual_sal FROM employees WHERE annual_sal>50000;#报错
SELECT employee_id,salary,salary*12 annual_sal FROM employees ORDER BY annual_sal DESC;#没有问题

#二级排序  显示员工信息,按department_id降序排序,当department_id相同时候,按salary升序排序
SELECT employee_id,salary,department_id FROM employees ORDER BY department_id DESC,salary ASC;
SELECT employee_id,salary,department_id FROM employees ORDER BY department_id DESC,salary;#ASC可以省略不写

#分页 LIMIT 
SELECT employee_id,last_name FROM employees LIMIT 0,10;
SELECT employee_id,last_name FROM employees LIMIT 10;#0的话可以省略
SELECT employee_id,last_name FROM employees LIMIT 10,10;
SELECT employee_id,last_name FROM employees LIMIT 20,10;
SELECT employee_id,last_name FROM employees LIMIT 30,10;

#显示第pageNo页面,每页显示pageSize条记录.公式:LIMIT (pageNo-1)*pageSize,pageSize;

#查看第32,33条数据
SELECT * FROM employees LIMIT 31,2

#Mysql8.0新特性  LIMIT 3 OFFSET 4 效果和LIMIT 4,3效果相同
SELECT * FROM employees LIMIT 4,3;#意思是跳过前面4条,从第5条开始,显示3条
SELECT * FROM employees LIMIT 3 OFFSET 4;#和上面效果是一样的 颠倒换下位置就行了

#查询工资最高的员工信息
SELECT MAX(salary) AS max_salary FROM employees;#这样只是查最高工资,并没有查出员工信息
SELECT * FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);
SELECT * FROM employees ORDER BY salary DESC LIMIT 1;

#查询员工的姓名,部门,年薪,按年薪降序,按姓名升序显示
SELECT last_name,department_id,salary*12 annual_salary FROM employees ORDER BY annual_salary DESC,last_name;

#查询工资不在8000到17000之间的员工姓名和工资,按工资降序,显示第21到40位置的数据
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20;

#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id;#可见ODER BY 数值

#关联表(多表)查询 employees表关联departments表关联locations表

#查询员工'Abel'在哪个城市工作
SELECT A.last_name,C.city FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id LEFT JOIN locations C ON B.location_id=C.location_id WHERE A.last_name='Abel';

#分开查
#查'Abel'的信息
SELECT * FROM employees WHERE last_name='Abel';#得到部门department_id是80
SELECT * FROM departments WHERE department_id=80;#location_id是2500
SELECT * FROM locations WHERE location_id=2500;#Oxford

SELECT * FROM locations WHERE location_id=(SELECT location_id FROM departments WHERE department_id=(SELECT department_id FROM employees WHERE last_name='Abel'));

#为什么要分表??为了去除冗余

#笛卡尔积现象  错误原因 缺少连接条件
SELECT employee_id,department_name FROM employees,departments;
SELECT employee_id,department_name FROM employees,departments WHERE employees.department_id=departments.department_id;#用的内连接  就是两张表都有的数据

SELECT employee_id,department_name,city FROM employees,departments,locations WHERE employees.department_id=departments.department_id AND departments.location_id=locations.location_id;#用的内连接 员工表中有一个员工部门id是null

#如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表 从SQL优化角度,建议多表查询时,每个字段前都指明其所在的表
SELECT employees.employee_id,departments.department_name,locations.city FROM employees,departments,locations WHERE employees.department_id=departments.department_id AND departments.location_id=locations.location_id;

#起别名方式  一旦给表起了别名SELECT或WHERE中使用表名的话,则必须使用表的别名,和SQL的执行顺序有关
SELECT a.employee_id,b.department_name,c.city FROM employees a,departments b,locations c WHERE a.department_id=b.department_id AND b.location_id=c.location_id;

#多表查询的分类
#等值连接和非等值连接(根据连接条件)  自连接和非自连接(自我引用,自关联)  内连接和外连接

#1.等值连接和非等值连接
#等值连接上面已经有了  演示非等值连接
SELECT A.last_name,A.salary,B.grade_level FROM employees A,job_grades B WHERE A.salary BETWEEN B.lowest_sal AND B.highest_sal;

#自连接和非自连接
#自连接 查员id,姓名和上级id,姓名
SELECT * FROM employees;
SELECT A.employee_id,A.last_name,B.employee_id,B.last_name FROM employees A,employees B WHERE A.manager_id=B.employee_id;

#查询所有员工的last_name,department_name
#这样内连接只能查出106条数据,因为员工表中有一个员工没有部门id,而现在要查所有
SELECT
    E.employee_id,
    D.department_name 
FROM
    employees E,
    departments D 
WHERE
    E.department_id = D.department_id;

#SQL92语法和SQL99语法
#SQL92语法的左外连接 MYSQL不支持SQL92的外连接,因此下面的会报错,(+)代表从表
SELECT
    E.employee_id,
    D.department_name 
FROM
    employees E,
    departments D 
WHERE
    E.department_id = D.department_id(+); 

#SQL99的左外连接 
SELECT
    E.employee_id,
    D.department_name 
FROM
    employees E
    LEFT JOIN departments D ON E.department_id = D.department_id;
    
#SQL99内连接 INNER JOIN 或者 JOIN效果相同
SELECT
    E.employee_id,
    D.department_name 
FROM
    employees E
    JOIN departments D ON E.department_id = D.department_id;

#MYSQL不支持满外连接

#UNION 要求,字段名称可以不同,以查询的第一张表的字段名称,查询的字段个数和字段类型必须相同 返回两个查询结果集的并集,去除重复记录

#UNION ALL 要求,字段名称可以不同,以查询的第一张表的字段名称,查询的字段个数和字段类型必须相同 返回两个查询的结果集的并集,不去重
SELECT employee_id,last_name FROM employees UNION ALL SELECT department_id,department_name FROM departments;

#SQL99新特性1 自然连接 NATURAL    JOIN 不用再写ON了 等值连接 生效的条件是字段名称相同 不太灵活
SELECT
    employee_id,
    last_name,
    department_name 
FROM
    employees e
    JOIN departments d ON e.`department_id` = d.`department_id` 
    AND e.`manager_id` = d.`manager_id`;#注意连接条件可以有多个

SELECT
    employee_id,
    last_name,
    department_name 
FROM
    employees e
NATURAL    JOIN departments d;

#SQL99新特性2 USING 生效条件 是基于两张表中 字段名称相同 的列
-- 使用 ON 进行连接
SELECT employee_id, last_name, department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

-- 使用 USING 进行连接
SELECT employee_id, last_name, department_name
FROM employees e
JOIN departments d
USING (department_id);

#显示所有员工的姓名,部门号和部门名称
SELECT A.last_name,A.department_id,B.department_name FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id;

#查询90号部门员工的job_id和90号部门的location_id
SELECT A.job_id,B.location_id FROM employees A JOIN departments B ON A.department_id=B.department_id WHERE A.department_id=90

#查询所有有奖金的员工的last_name,department_name,location_id,city  注意 LEFT JOIN的特性决定了以左表所有为主,即使在ON里添加条件例如ON A.department_id=B.department_id AND A.commission_pct IS NOT NULL这样也不行 注意注意注意 因此一定放在WHERE后面
SELECT A.last_name,B.department_name,B.location_id,C.city FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id LEFT JOIN locations C ON B.location_id=C.location_id WHERE A.commission_pct IS NOT NULL;

#查询city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT A.last_name,A.job_id,A.department_id,B.department_name,C.city FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id LEFT JOIN locations C ON B.location_id=C.location_id WHERE C.city='Toronto';

#查询所在部门名称为'Executive'的员工的部门名称,部门地址,姓名,工作,工资
SELECT B.department_name,C.city,A.last_name,D.job_title,A.salary FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id LEFT JOIN locations C ON B.location_id=C.location_id LEFT JOIN jobs D ON A.job_id=D.job_id WHERE B.department_name='Executive';

#查询哪些部门没有员工
SELECT B.department_id FROM employees A RIGHT JOIN departments B ON A.department_id=B.department_id WHERE A.department_id IS NULL;

#查询哪个城市没有部门
SELECT B.city FROM departments A RIGHT JOIN locations B ON A.location_id=B.location_id WHERE A.department_id IS NULL

#查询部门名为Sales或IT的员工信息
SELECT A.*,B.department_name FROM employees A LEFT JOIN departments B ON A.department_id=B.department_id WHERE B.department_name IN('Sales','IT');

/*
函数的分类:
从函数定义角度:分为内置函数和自定义函数
不同DBMS的内置函数差异还是很大的 就是说Mysql的内置函数在Oracle中不一定可以用 差异大

Mysql内置函数从实现功能角度分类
1.数值函数
2.字符串函数
3.日期和时间函数
4.流程控制函数
5.加密与解密函数
6.获取Mysql信息函数
-------以上都是单行函数------
7.聚合函数

将以上内置函数再次分类
1.单行函数(处理单条数据，输出单条结果)
2.聚合函数(分组函数,处理多条数据，返回单个结果)
*/
#1.数值函数
#挑几个觉得重要的
#四舍五入ROUND(ARG)一个参数表示小数点后面四舍五入,返回一个整数 ROUND(ARG1,ARG2)两个参数第二个参数表示保留几位小数,然后对后面的进行四舍五入
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,-1),ROUND(123.456,-2) FROM DUAL;

#截断
SELECT TRUNCATE(123.456,0),TRUNCATE(123.996,1),TRUNCATE(129.45,-1) FROM DUAL;

#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0) FROM DUAL;

#字符串函数
#LENGTH(ARG1) 返回字符串个数
SELECT LENGTH("A"),LENGTH("BB"),LENGTH("CC"),LENGTH("ASDF"),LENGTH("TREWQ")FROM DUAL;

#字符串连接函数CONCAT(ARG1,ARG2,...ARGN)将ARG1,ARG2...ARGN连接 注意有NULL参与就变成NULL
SELECT CONCAT("HELLO","WORLD"),CONCAT("1","WO","222"),CONCAT("H",NULL,"WORLD"),CONCAT("HELLO","WORLD","PPP")FROM DUAL;

#INSERT(str, pos, len, new_str)索引从1开始,将字符串中指定位置的内容替换为新字符串
SELECT INSERT("FENGHONGRUI",5,5,"NIHAO") FROM DUAL;

#REPLACE(str, from_str, to_str)将原字符串str,找到对应from_str,替换成to_str,如果from_str和str中的不匹配,则不做改动
SELECT REPLACE("HELLO","LL","WORLD"),REPLACE("HELLO","L","WORLD"),REPLACE("HELLO","LE","WORLD") FROM DUAL;

#UPPER(str)将字符串转换为大写    LOWER(str)将字符串转换为小写
SELECT UPPER("hhh"),LOWER("HHhH") FROM DUAL;

#LEFT(str,len)取字符串左边的len位   RIGHT(str,len)取字符串右边的len位
SELECT LEFT("QWERT",1),RIGHT("ASDDDD",3) FROM DUAL;

#字符串填充函数  LPAD(str, len, padstr)将str填充,len填充后的字符串总长度.如果len小于str的长度,则会截断字符串.填充从左开始.RPAD(str, len, padstr)填充从右边开始
SELECT LPAD(salary,3,"*"),LPAD(salary,10,"*"),RPAD(salary,10,"*") FROM employees

#TRIM(str)移除字符串两端空格,中间无法去除
SELECT TRIM('   Hello World   ') FROM DUAL;

#REPEAT(str,n)返回str重复n次的结果   SPACE(5)提供5个空格
SELECT REPEAT('H',5),SPACE(5),LENGTH(SPACE(5)) FROM DUAL;

#SUBSTR(STR,INDEX,LEN)将str从5开始 截取三个
SELECT SUBSTR("QWERTYU",5,3) FROM DUAL;

#REVERSE(STR)字符串反转
SELECT REVERSE("DCBA") FROM DUAL;

#NULLIF(STR1,STR2)比较两字符串 如果相等 返回NULL 否则返回STR1
SELECT NULLIF("ASD","ASD"),NULLIF("ASD","ADD") FROM DUAL;

#日期和时间函数
#CURDATE()返回当前年月日
SELECT CURDATE();
INSERT INTO TEST VALUES(NULL,CURDATE());

#CURTIME()返回当前时分秒
SELECT CURTIME();

#NOW()/SYSDATE()返回当前系统时间  年月日时分秒
SELECT NOW();
SELECT SYSDATE();

#UTC_DATE() 返回UTC(世界标准时间) 年月日
SELECT UTC_DATE();
#UTC_TIME() 返回UTC(世界标准时间) 时分秒
SELECT UTC_TIME();
#DAYNAME(CREATE_TIME)返回周几Wednesday
SELECT DAYNAME(CREATE_TIME) FROM TEST

#流程控制函数

#IF(VALUE,VALUE1,VALUE2)如果VALUE的值是TRUE,返回结果VALUE1,否则返回VALUE2
SELECT last_name,IF(salary>=8000,"高工资","低工资") FROM employees;
#CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2...ELSE 结果N END 别名  ELSE可以不写,那么没有匹配就是NULL

#加解密函数信息函数
#PASSWORD()在mysql8.0中启用
SELECT MD5("123456"),MD5('123456'),SHA('123456'),SHA(MD5('admin'));#SHA(MD5('admin'))Bladex的加密

#ENCODE(str, pass_str)对str进行加密pass_str为加密密钥  DECODE(crypt_str, pass_str)对加密后的crypt_str解密用相同的pass_str
#mysql8.0中已经不能用了
SELECT ENCODE("12345",1);
SELECT DECODE("12345",1);

#Mysql信息函数
#查看当前Mysql版本
SELECT VERSION();

#CONNECTION_ID()当前会话的唯一标识符
SELECT CONNECTION_ID();

#DATABASE() 或 SCHEMA()当前会话中使用的数据库名称。
SELECT DATABASE();
-- 或者
SELECT SCHEMA();
#USER() / CURRENT_USER() / SYSTEM_USER() / SESSION_USER()回当前连接 MySQL 的用户名，包含主机信息，格式为：username@hostname
SELECT USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER();

#CHARSET(value)返回指定字符串或变量的字符集名称
SELECT CHARSET('hello');

#COLLATION(value)返回指定字符串或变量的排序规则
SELECT COLLATION('hello');

#聚合函数  聚合删除会自动去掉null
#1.常见的集合聚合函数
#2.GROUP BY的使用
#3.HAVING的使用
#4.SQL底层执行原理

#常见的集合聚合函数
#AVG / SUM 针对于数字  可以是字符串的数字
SELECT AVG(salary),SUM(salary),AVG(salary)*12 FROM employees;
#无意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date) FROM employees;
#MAX / MIN                              
SELECT MIN(salary),MAX(salary) FROM employees;
SELECT MIN(last_name),MAX(last_name) FROM employees;
#COUNT 计算指定字段在查询结构中出现的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(commission_pct) FROM employees;#COUNT(commission_pct)不会计算NULL值
#如果计算表中有多少条数据
#方式1:COUNT(*):没问题
#方式2:COUNT(1):没问题
#方式3:COUNT(具体字段):不一定对(该字段可能值是NULL,聚合函数会自动排除NULL的字段)
SELECT COUNT(commission_pct) FROM employees GROUP BY commission_pct

SELECT AVG(salary),SUM(salary)/COUNT(salary),AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35 FROM employees;

#使用COUNT(*) COUNT(1) COUNT(具体非空字段,例如ID) 哪个效率高 如果使用的MyISAM引擎没有区别 如果使用InnoDB存储引擎 效率COUNT(*)=COUNT(1)>COUNT(具体非空字段,例如ID)

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

#在Mysql中SELECT @@SQL_MODE;STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  可以查看是否使用严格GROUP BY模式
#后来发现SELECT @@global.sql_mode;全局是生效的,但是会话SELECT @@SQL_MODE;没有
SELECT department_id,job_id,AVG(salary)FROM employees GROUP BY department_id;

SHOW VARIABLES LIKE 'DATADIR';#查看数据库目录
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';#全局设置
SELECT @@global.sql_mode;#查看全局设置
SELECT @@SQL_MODE;#查看会话级别设置
#注意:SELECT中出现的非聚合函数字段,必须声明在GROUP BY中.而GROUP BY中声明的字段可以不出现在SELECT中
SELECT department_id,job_id,AVG(salary)FROM employees GROUP BY department_id;#其实这样是错误的

#GROUP BY中使用WITH ROLLUP关键字 会增加一行记录AVG(salary)的平均工资 使用ROLLUP时不能同时使用ORDER BY是互斥的
SELECT department_id,AVG(salary)FROM employees WHERE department_id>80 GROUP BY department_id WITH ROLLUP;
SELECT department_id,AVG(salary) avg_sal FROM employees WHERE department_id>80 GROUP BY department_id ORDER BY avg_sal

#HAVING的使用 (作用:过滤数据)
#查询各个部门最高工资比10000高的部门信息
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000;

#如果过滤条件中使用了聚合函数,必须使用HAVING

#查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary) FROM employees WHERE department_id IN(10,20,30,40) GROUP BY department_id HAVING MAX(salary)>10000;

SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 AND department_id IN(10,20,30,40);
#上面两句SQL效果一样,但是department_id IN(10,20,30,40)放在WHERE后面效果好,原因是执行时机,WHERE在GROUP BY之前就已经把有些数据过滤了

#子查询(嵌套查询):子查询指一个查询语句嵌套在另一个查询语句内部的查询

#查询谁的工资比Abel高
    SELECT
        E2.last_name,
        E2.salary 
    FROM
        employees E1,
        employees E2 
    WHERE
        E2.salary > E1.salary #连接条件可以是等值连接和非等值连接
        AND E1.last_name = 'Abel';
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');

    SELECT
        E2.last_name,
        E2.salary 
    FROM
        employees E1
        JOIN employees E2 ON E2.salary > E1.salary #连接条件可以是等值连接和非等值连接
        
    AND E1.last_name = 'Abel';
    
#查询工资大于149号员工工资的员工信息
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE employee_id=149);

#查询job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,job_id,salary FROM employees WHERE job_id=(SELECT job_id FROM employees WHERE employee_id=141) AND salary>(SELECT salary FROM employees WHERE employee_id=143);

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

#查询与141号员工的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
    
#查询最低工资大于110号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=110)

#多行子查询 单行子查询返回一条数据 可以用等号连接   多行子查询返回多条数据  不能再用等号连接了
#多行子查询比较操作符 
/*
单行比较操作符
=
>
>=
<
<=
<>
多行比较操作符
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用,和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用,和子查询返回的所有值比较
SOME 实际上是ANY的别名,作用相同,一般常用ANY
*/
SELECT employee_id,last_name,salary,department_id FROM employees WHERE salary IN(SELECT MIN(salary) FROM employees GROUP BY department_id);

#ANY/ALL
#查询其他job_id中比job_id为IT_PROG任一工资低的员工工号,姓名,job_id以及salary
SELECT
    employee_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' );
    
#查询其他job_id中比job_id为IT_PROG所有工资低的员工工号,姓名,job_id以及salary
SELECT
    employee_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' );

#查询平均工资最低的部门id(注意MYSQL中聚合函数不能嵌套,Oracle中可以)
SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id;#MYSQL中不可以,Oracle中可以
SELECT department_id,AVG(salary) avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary;

SELECT
    MIN( avg_sal ) 
FROM
    (
    SELECT
        AVG( salary ) avg_sal 
    FROM
        employees 
    GROUP BY
    department_id) E#这个表必须有个别名

#感觉这样反而变得复杂
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 ) E) #这个表必须有个别名)
    
    
#EXISTS和NOT EXISTS 关键字
#查询公司管理者的信息
SELECT DISTINCT A.* FROM employees A,employees B WHERE A.employee_id=B.manager_id

#方式2子查询:
SELECT * FROM employees WHERE(employee_id IN(SELECT DISTINCT manager_id FROM employees));

#方式3EXISTS:
SELECT * FROM employees E1 
WHERE EXISTS(
    SELECT * FROM employees E2
    WHERE E1.employee_id=E2.manager_id
)

#DDL数据库创建,修改与删除
CREATE DATABASE IF NOT EXISTS TEST4 CHARACTER SET UTF8MB4;
#CHARACTER SET UTF8MB4;写不写都一样MYSQL8默认UTF8MB4 默认排序规则utf8mb4_0900_ai_ci
CREATE DATABASE IF NOT EXISTS TEST5;
SHOW DATABASES;
SHOW CREATE DATABASE TEST5;
SHOW VARIABLES LIKE 'character_%';
SHOW VARIABLES LIKE 'DATADIR';

USE TEST5;
#查看当前使用的数据库
SELECT DATABASE();

#修改数据库
ALTER DATABASE TEST5 CHARACTER SET GBK;

#删除
DROP DATABASE TEST5;
DROP DATABASE IF EXISTS TEST5;

#创建表
CREATE TABLE IF NOT EXISTS emp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
#查看表结构
DESC emp;
SHOW COLUMNS FROM emp;#作用一样
#查看创建表的语句结构
SHOW CREATE TABLE EMP;
#查看表数据

#基于现有的表
CREATE TABLE EMP2 AS SELECT * FROM EMP;#包含表里的数据
CREATE TABLE EMP3 AS SELECT ID FROM EMP;#包含表里的数据

#只复制表结构而不要数据
CREATE TABLE new_table AS
SELECT * FROM original_table#不用*可以选择要创建的字段 可以用关联查询
WHERE 1=0;

#创建表employees_copy,实现对employees表的复制,包括表数据
CREATE TABLE EMPLOYEES_COPY AS SELECT * FROM  ATGUIGUDB.employees;

#可以指定字段和字段名
CREATE TABLE new_table AS
SELECT column1 AS new_column1, column2 AS new_column2, ...
FROM source_database.source_table;

#创建表employees_copy,实现对employees表的复制,只复制结构不包括表数据
CREATE TABLE EMPLOYEES_COPY2 AS SELECT * FROM  ATGUIGUDB.employees WHERE 1=2;

#INSERT INTO ... SELECT
#往EMPLOYEES_COPY2插入数据
INSERT INTO EMPLOYEES_COPY2 SELECT * FROM EMPLOYEES_COPY
#列的名称不需要相同,只要字段类型相同
#INSERT INTO EMPLOYEES_COPY2(column2,column2) SELECT xxx,xxx FROM EMPLOYEES_COPY

#修改,重命名,删除,清空表
DESC emp;
#添加一个字段
ALTER TABLE emp ADD salary DOUBLE(10,2);#默认添加到表中最后一个字段
ALTER TABLE emp ADD phone varchar(15) FIRST;#添加到第一位
ALTER TABLE emp ADD email varchar(30) AFTER phone;#指定到某个字段后面
#修改一个字段 数据类型,长度,默认值等
ALTER TABLE emp MODIFY email VARCHAR(45);#修改字段类型长度
ALTER TABLE emp MODIFY email VARCHAR(45) default 'hrui@helljs.com';#默认值
#对字段重命名
ALTER TABLE emp CHANGE salary monthly_salary DOUBLE(10,2); #修改字段名,同时也可以修改字段类型
#删除一个字段
ALTER TABLE emp DROP COLUMN email;
#重命名表两种方式
RENAME TABLE EMP TO EMPEMP;
ALTER TABLE EMPEMP RENAME EMP;
#删除表(删除表结构和数据)
DROP table emp3;
DROP table IF EXISTS emp3;
#清空表(清空数据,保留结构)
TRUNCATE TABLE EMP;
DELETE FROM EMP;

#DCL(TCL)中  COMMIT和ROLLBACK
#COMMIT:提交数据,一旦COMMIT,不能回滚

#关闭自动提交(会话级别)
SET AUTOCOMMIT=FALSE;

DELETE FROM employees_copy;

ROLLBACK;

SELECT * FROM employees_copy;

SELECT @@AUTOCOMMIT;
#开启自动提交
SET AUTOCOMMIT=TRUE;

#----------------------------------------------------------------------------
-- 开启事务
START TRANSACTION;

DELETE FROM employees_copy;
-- ROLLBACK或者COMMIT之后,事务自动结束
ROLLBACK;

-- 查询数据
SELECT * FROM employees_copy;
#----------------------------------------------------------------------------
-- 开启事务
BEGIN;

DELETE FROM employees_copy;
-- ROLLBACK或者COMMIT之后,事务自动结束
ROLLBACK;
-- 查询数据
SELECT * FROM employees_copy;

#DELETE和TRUNCATE区别是DELETE可以回滚  但是TRUNCATE无法回滚
#DDL操作不受事务影响

#DML INSERT DELETE UPDATE
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(id int,name varchar(15),hire_date date,salary double(10,2));

SELECT * FROM EMP1;
#没有指明添加的字段,要按申明的先后顺序全量添加
INSERT INTO EMP1 VALUES(1,'HRUI','2000-12-21',5555);
#前后字段位置一致
INSERT INTO EMP1(name,salary,id,hire_date)values('HANMEIMEI',5555,2,'2222-02-02');
#指定字段
INSERT INTO EMP1(name)values('lalala');
#多条插入
INSERT INTO EMP1(id,name,salary)VALUE(3,'zhang',6666),(4,'li',7777),(5,'wang',8888);
INSERT INTO EMP1 VALUES(6,'zh','2000-12-21',6666),(7,'liLI','2000-12-21',7777),(8,'wa','2000-12-21',8888);
#将查询结果插入到表中 从某张表里查出来的数据插入
#INSERT INTO EMP1.....SELECT * FROM XXX
CREATE TABLE EMP2(id int,name varchar(15),hire_date date,salary double(10,2));
INSERT INTO EMP2(id) SELECT id FROM EMP1;
CREATE TABLE EMP3(aid int,name varchar(15),hire_date date,salary double(10,2));
INSERT INTO EMP3(aid) SELECT id FROM EMP1;#只要类型相同  字段名称没关系
select * from emp3;
select * from emp2;
select * from emp1;

#UPDATE更新数据
#UPDATE TABLENAME SET COLUMN=XXX WHERE COLUMN=XXX
UPDATE EMP1 SET hire_date=CURDATE() WHERE id=5;
#同时修改一条数据的多个字段
UPDATE EMP1 SET hire_date=CURDATE(),salary=6000 WHERE id=5;
#批量修改
#将emp1表中,姓名中包含字符'a'的提薪20%
UPDATE emp1 set salary=salary*(1+0.2) where name like '%a%';

#修改数据时,可能存在不成功的情况(可能是由于约束的影响造成)

#删除操作DELETE FROM TABLENAME WHERE 条件
DELETE FROM EMP1 WHERE id=1;
#删除数据时,也有可能因为约束的影响导致删除失败

#MYSQL8新特性:计算列,某一列的值是通过其他列计算的来
USE atguigudb;

CREATE TABLE test1(
a int,
b int,
c int GENERATED ALWAYS AS(a+b) VIRTUAL);#C就是计算列

INSERT INTO test1(a,b)
VALUES(10,20);
select * from test1;
UPDATE test1 SET a=20;

#约束 对表中字段的限制
/*
从约束的字段个数说可以分为单列约束和多列约束
从约束的作用范围说可以分为列级约束和表级约束
在表中所有字段都声明完成,在所有字段的后面声明的约束-->表级约束
从约束作用(功能)上区分:
1.not null 非空约束
2.unique 唯一约束
3.primary key 主键约束
4.foreign key 外键约束
5.check 检查约束
6.default 默认值约束
如何添加约束?
1.CREATE TABLE时候添加约束
2.ALTER TABLE时去修改删除补充

约束 是一种规则，用来限制表中数据的合法性和一致性。
索引 是一种数据结构，用于提高查询性能。
有些约束会自动创建索引:例如主键约束会自动创建唯一索引  唯一约束也会自动创建唯一索引
*/
#如何查看表中的约束
SELECT * FROM information_schema.table_constraints WHERE table_name='employees';

CREATE TABLE my_table (
    id INT PRIMARY KEY,          -- id为主键
    name VARCHAR(255) UNIQUE     -- name字段设置唯一约束
);
INSERT INTO my_table value(1,null),(2,null);#唯一索引允许多个值为NULL

#非空约束 NOT NULL 默认所有的类型的值都可以是NULL 针对于列
CREATE DATABASE dbtest13;
USE dbtest13;

CREATE TABLE test1(
id int not null,
last_name varchar(15) not null,
email varchar(25) not null,
salary decimal(10,2) 
);

#用ALTER修改  注意:如果修改时候表里该字段已经有数据是NULL,会报错
ALTER TABLE test1 MODIFY salary DECIMAL(10,2) NOT NULL;

desc test1;
#非空约束 基本约束 NOT NULL 这里无法查到
SELECT * FROM information_schema.table_constraints WHERE table_name='test1';

#当插入NULL时报错
INSERT INTO test1 VALUE(1,'douyu',NULL,'50');#Column 'email' cannot be null

CREATE TABLE test2 (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL DEFAULT 'Unknown'#不能插入NULL,如果插入时候未指定列则默认给值
);

INSERT INTO test2(id)VALUES(1)#但是不能INSERT INTO test2 VALUES(1,NULL);不能显示给值

#唯一约束 UNIQUE 不能重复,但是可以有多个NULL 可以是某列也可以设置多列
CREATE TABLE test3(
id int unique,
last_name varchar(30),
email varchar(30) unique,
salary decimal(10,2)
)

CREATE TABLE test3(
id int,
last_name varchar(30),
email varchar(30),
salary decimal(10,2),
CONSTRAINT uk_test2_email UNIQUE(id,email)#uk_test2_email是约束名称 删除约束时候要用到约束名,这样指的是id,email整体不能是唯一(复合唯一)
)
SELECT * FROM information_schema.table_constraints WHERE table_name='test3';

INSERT INTO test3 VALUES(1,'Tom','Tom@helljs.com',5000);
INSERT INTO test3 VALUES(NULL,'TomA',NULL,5000);
INSERT INTO test3 VALUES(NULL,'TomA',NULL,5000);
INSERT INTO test3 VALUES(2,'Tom','Tom@helljs.com',5000);
INSERT INTO test3 VALUES(3,'Tom','Tom@helljs.com',5000);
INSERT INTO test3 VALUES(3,'Tom','Tom@helljs.com',5000);

#删除约束
ALTER TABLE test3 DROP INDEX uk_test2_email;
#两个作用一样
DROP INDEX uk_test2_email ON test3
#添加
ALTER TABLE test3 ADD CONSTRAINT uk_test2_email UNIQUE (id, email);#id,email整体不能是唯一

ALTER TABLE test3 MODIFY email varchar(30) UNIQUE;
#查看test3表的索引
SHOW INDEX FROM test3;

#主键约束:用来唯一标识表中的一行记录  相当于唯一约束+非空约束 一个表中只能有一个主键约束 可以创建多列复合主键
#主键名总是PRIMARY 就算自己命名了主键约束名也没用 创建主键约束时候自动创建主键索引 如果删除主键约束,自动删除索引
CREATE TABLE test4(
id int primary key,
last_name varchar(20),
email varchar(20),
salary decimal(10,2)
)

CREATE TABLE test4(
id int,
last_name varchar(20),
email varchar(20),
salary decimal(10,2),
CONSTRAINT pk_test4_id PRIMARY KEY(id)#取名 但是不会用  还是会用primary,  CONSTRAINT pk_test4_id可以省略
)

CREATE TABLE test4(
id int,
last_name varchar(20),
email varchar(20),
salary decimal(10,2),
primary key(id,last_name)#复合主键 无论哪一列都不能为NULL
)
INSERT INTO test4 VALUES(NULL,'HRUI','HRUI@hellojs.com',50000);#复合主键 无论哪一列都不能为NULL

SELECT * FROM information_schema.table_constraints WHERE table_name='test4';

#ALTER TABLE时候添加主键
ALTER TABLE test4 ADD PRIMARY KEY(id);#一张表只能有一个主键
#删除主键
ALTER TABLE test4 DROP PRIMARY KEY;

#自增列 AUTO_INCREMENT 一个表只能有一个自增长列  一般与主键使用 可以和UNIQUE使用 AUTO_INCREMENT UNIQUE 也可以单独使用 自增列数据类型必须是整形
CREATE TABLE test5(
id int AUTO_INCREMENT PRIMARY KEY,#AUTO_INCREMENT PRIMARY KEY或者PRIMARY KEY AUTO_INCREMENT都可以
last_name varchar(25)
)

INSERT INTO test5 VALUES(0,'TOM');
INSERT INTO test5 VALUES(0,'TOM2');#字段上添加0或者NULL 时 自动开始从1累加

#ALTER TABLE时添加主键自增 或者添加自增
ALTER TABLE test5 MODIFY id int AUTO_INCREMENT;

#删除自增
ALTER TABLE test MODIFY id int;

#引用完整性
#外键约束 指定某个表的某个字段的引用完整性 自己觉得应该是被别人引用之后 例如部门表的主键(或唯一约束的列)被做为EMPLOYEES表的外键
#例如员工表中更新或者新增数据时候,部门id必须是部门表里存在的部门
#删除部门数据时,会检查要删除的部门ID是否被员工表引用,如果被引用则不能删除,要先删除员工表引用该部门的数据,再删除部门表的该部门数据
#一个表可以有多个外键约束,创建外键约束时,会默认创建一个普通索引
#主键约束和唯一约束会自动创建唯一索引 删除主键约束或者唯一约束会自动删除索引   外键约束会自动创建普通索引(索引名是列名) #删除外键约束后需要手动删除对应索引  但是删除表之后外键约束自动创建的普通索引也会被删除
#做为外键的列在主表中需要是主键或者唯一约束的列
#FOREIGN KEY(外键约束)  主表和从表:主表的主键被另一张表引用
#创建主表
USE atguigudb;
CREATE TABLE dept1(
dept_id int AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(15)
)
#创建从表
CREATE TABLE emp1(
emp_id int AUTO_INCREMENT PRIMARY KEY,
emp_name varchar(30),
department_id int,
#表级外键约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
)

DESC emp1;
SELECT * FROM information_schema.table_constraints WHERE table_name='emp1';

#外键约束等级
CREATE TABLE emp1(
emp_id int AUTO_INCREMENT PRIMARY KEY,
emp_name varchar(30),
department_id int,
#表级外键约束 ON UPDATE CASCADE ON DELETE SET NULL 意思是当主表更新主键的时候 从表的外键跟着更新 当主表删除主键时,从表外键设置为NULL
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id) ON UPDATE CASCADE ON DELETE SET NULL 
)

#删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
#查看表的索引名
SHOW INDEX FROM 表名;
#删除添加外键时自动创建的普通索引
ALTER TABLE 表名 DROP INDEX 索引名;

#CHECK 检查约束  检查某个字段是否符合要求,一般指的是值的范围  MYSQL5.7是不支持的 从 8.0.16 版本开始支持 CHECK 约束
CREATE TABLE emp2(
id int,
last_name varchar(15),
salary decimal(10,2) CHECK(salary>2000),
gender CHAR(1) CHECK (gender IN ('男', '女'))
)

#DEFAULT约束
CREATE TABLE emp3 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(20) DEFAULT '未分配' -- 默认部门
);

#修改添加
ALTER TABLE 表名 MODIFY 列名 数据类型 DEFAULT 默认值;
#删除
ALTER TABLE 表名 MODIFY 列名 类型;

CREATE TABLE emp4 (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认当前时间
);

CREATE TABLE emp5 (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) AUTO_INCREMENT=100; -- 设置自增从 100 开始