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 开始

相关资讯

热文排行

最新新闻

推荐新闻

热搜词