MySQL基础篇

零、基本格式

#sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....


#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

#两种语法中，除了group by和limit之外，其他位置都可以声明子查询

一、基本规则

• SQL语句可以单行或多行书写，以分号结尾。
• SOL语句可以使用空格/缩进来增强语句的可读性。
• MysQL数据库的SQL语句不区分大小写，关键字建议使用大写。
• 注释写法

        单行注释：-- 注释内容（--空格）或＃注释内容(MySQL特有）

        多行注释：/*注释内容*/

• 关键字不能被缩写也不能分行
• 关于标点符号：

        必须保证所有的()、单引号、双引号是成对结束的

        必须使用英文状态下的半角输入方式

        字符串型和日期时间类型的数据可以使用单引号（' '）表示

        列的别名，尽量使用双引号（" "），而且不建议省略as

注意：

• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来，若创建数据库名中间有空格也需要引起来。

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增、删、改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

二、条件运算符

#1. 算术运算符： +  -  *  /  div  % mod
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 * 30, 100 + 35.5, 100 - 35.5 
FROM DUAL;

# 在SQL中，+没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）
SELECT 100 + '1'  
FROM DUAL;
# 在Java语言中，结果是：1001。 
# 在SQL语言中，结果是：101。
 
SELECT 100 + 'a' 
FROM DUAL;
#此时将'a'看做0处理,结果是：100

SELECT 100 + NULL  
FROM DUAL;
# null值参与运算，结果为null

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2,100 / 3, 100 DIV 0  # 分母如果为0，则结果为null
FROM DUAL;

# 取模运算： % mod
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL;

#练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;

#2. 比较运算符
#2.1 =  <=>  <> !=  <  <=  >  >= 

# = 的使用
SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' #字符串存在隐式转换。如果转换数值不成功，则看做0
FROM DUAL;

SELECT 'a' = 'a','ab' = 'ab','a' = 'b' #两边都是字符串的话，则按照ANSI的比较规则进行比较。
FROM DUAL;

SELECT 1 = NULL,NULL = NULL # 只要有null参与判断，结果就为null
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct = NULL;  #此时执行，不会有任何的结果

# <=> ：安全等于。 记忆技巧：为NULL而生。

SELECT 1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a'
FROM DUAL;

SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;

#练习：查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

SELECT 3 <> 2,'4' <> NULL, '' != NULL,NULL != NULL
FROM DUAL;

#2.2 
#① IS NULL \ IS NOT NULL \ ISNULL
#练习：查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

#练习：查询表中commission_pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

#② LEAST() \ GREATEST 

SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;

SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#③ BETWEEN 条件下界1 AND 条件上界2  （查询条件1和条件2范围内的数据，包含边界）
#查询工资在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
#where salary between 6000 and 8000;
WHERE salary >= 6000 && salary <= 8000;

#交换6000 和 8000之后，查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;

#查询工资不在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary < 6000 or salary > 8000;

#④ in (set)\ not in (set)

#练习：查询部门为10,20,30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);

#练习：查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);

#⑤ LIKE :模糊查询
# % : 代表不确定个数的字符 （0个，1个，或多个）

#练习：查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#练习：查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# _ ：代表一个不确定的字符

#练习：查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

#⑥ REGEXP \ RLIKE :正则表达式

SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;

#3. 逻辑运算符： OR ||  AND && NOT ! XOR

# or  and 
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20;
#where department_id = 10 and department_id = 20;
WHERE department_id = 50 AND salary > 6000;

# not 
SELECT last_name,salary,department_id
FROM employees
#where salary not between 6000 and 8000;
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;

# XOR :追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;

#注意：AND(&&)的优先级高于OR(||)

#4. 位运算符： & |  ^  ~  >>   <<

SELECT 12 & 5, 12 | 5,12 ^ 5 
FROM DUAL;

SELECT 10 & ~1 FROM DUAL;

#在一定范围内满足：每向左移动1位，相当于乘以2；每向右移动一位，相当于除以2。
SELECT 4 << 1 , 8 >> 1
FROM DUAL;

三、去重、排序和分页

--去重    关键字：distinct
select distinct 字段名 from 表格名;
select distinct * from 表格名;

--排序    关键字：order by
select 字段 from 表格名 order by 字段 排序方式;
-- 多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
select 字段 from 表格名 order by 字段1 排序方式,字段2 排序方式;

/*如果从低到高，从小到大，则后面添加asc     因为默认使用，所以可以不添加
  如果从高到低，从大到小，则后面添加desc
  注意：asc和desc要添加在列名到后面 
列的别名只能在order by中使用 */

--分页查询    关键字：LIMIT
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询识录数；

/*起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10 */

四、单行函数

4.1基本函数

函数	用法
ABS(x)	返回 x 的绝对值
SIGN(X)	返回 X 的符号。正数返回 1 ，负数返回 -1 ， 0 返回 0
PI()	返回圆周率的值
CEIL(x) ， CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回 X 除以 Y 后的余数
RAND()	返回 0~1 的随机值
RAND(x)	返回 0~1 的随机值，其中 x 的值用作种子值，相同的 X 值会产生相同的随机数
ROUND(x)	返回一个对 x 的值进行四舍五入后，最接近于 X 的整数
ROUND(x,y)	返回一个对 x 的值进行四舍五入后最接近 X 的值，并保留到小数点后面 Y 位
TRUNCATE(x,y)	返回数字 x 截断为 y 位小数的结果
SQRT(x)	返回 x 的平方根。当 X 的值为负数时，返回 NULL
RADIANS(x)	将角度转化为弧度，其中，参数 x 为角度值
DEGREES(x)	将弧度转化为角度，其中，参数 x 为弧度值

4.2三角函数

函数	用法
SIN(x)	返回 x 的正弦值，其中，参数 x 为弧度值
ASIN(x)	返回 x 的反正弦值，即获取正弦为 x 的值。如果 x 的值不在 -1 到 1 之间，则返回 NULL
COS(x)	返回 x 的余弦值，其中，参数 x 为弧度值
ACOS(x)	返回 x 的反余弦值，即获取余弦为 x 的值。如果 x 的值不在 -1 到 1 之间，则返回 NULL
TAN(x)	返回 x 的正切值，其中，参数 x 为弧度值
ATAN(x)	返回 x 的反正切值，即返回正切值为 x 的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回 x 的余切值，其中， X 为弧度值

4.3对数函数 

函数	用法
POW(x,y) ， POWER(X,Y)	返回 x 的 y 次方
EXP(X)	返回 e 的 X 次方，其中 e 是一个常数， 2.718281828459045
LN(X) ， LOG(X)	返回以 e 为底的 X 的对数，当 X <= 0 时，返回的结果为 NULL
LOG10(X)	返回以 10 为底的 X 的对数，当 X <= 0 时，返回的结果为NULL
LOG2(X)	返回以 2 为底的 X 的对数，当 X <= 0 时，返回 NULL

4.4进制间的转换 

函数	用法
BIN(x)	返回 x 的二进制编码
HEX(x)	返回 x 的十六进制编码
OCT(x)	返回 x 的八进制编码
CONV(x,f1,f2)	返回 f1 进制数变成 f2 进制数

4.5字符串函数

函数	用法
ASCII(S)	返回字符串 S 中的第一个字符的 ASCII 码值
CHAR_LENGTH(s)	返回字符串 s 的字符数。作用与 CHARACTER_LENGTH(s) 相同
LENGTH(s)	返回字符串 s 的字节数，和字符集有关
CONCAT(s1,s2,......,sn)	连接 s1,s2,......,sn 为一个字符串
CONCAT_WS(x, s1,s2,......,sn)	同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x
INSERT(str, idx, len, replacestr)	将字符串 str 从第 idx 位置开始， len 个字符长的子串替换为字符串 replacestr
REPLACE(str, a, b)	用字符串 b 替换字符串 str 中所有出现的字符串 a
UPPER(s) 或 UCASE(s)	将字符串 s 的所有字母转成大写字母
LOWER(s) 或 LCASE(s)	将字符串 s 的所有字母转成小写字母
LEFT(str,n)	返回字符串 str 最左边的 n 个字符
RIGHT(str,n)	返回字符串 str 最右边的 n 个字符
LPAD(str, len, pad)	用字符串 pad 对 str 最左边进行填充，直到 str 的长度为 len 个字符
RPAD(str ,len, pad)	用字符串 pad 对 str 最右边进行填充，直到 str 的长度为 len 个字符
LTRIM(s)	去掉字符串 s 左侧的空格
RTRIM(s)	去掉字符串 s 右侧的空格
TRIM(s)	去掉字符串 s 开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串 s 开始与结尾的 s1
TRIM(LEADING s1 FROM s)	去掉字符串 s 开始处的 s1
TRIM(TRAILING s1 FROM s)	去掉字符串 s 结尾处的 s1
REPEAT(str, n)	返回 str 重复 n 次的结果
SPACE(n)	返回 n 个空格
STRCMP(s1,s2)	比较字符串 s1,s2 的 ASCII 码值的大小
SUBSTR(s,index,len)	返回从字符串 s 的 index 位置其 len 个字符，作用与 SUBSTRING(s,n,len) 、 MID(s,n,len) 相同
LOCATE(substr,str)	返回字符串 substr 在字符串 str 中首次出现的位置，作用于 POSITION(substr IN str) 、 INSTR(str,substr) 相同。未找到，返回 0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果 m=1 ，则返回 s1 ，如果 m=2 ，则返回 s2 ，如 果 m=n ，则返回 sn
FIELD(s,s1,s2,…,sn)	返回字符串 s 在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串 s1 在字符串 s2 中出现的位置。其中，字符串 s2 是一个以逗号分 隔的字符串
REVERSE(s)	返回 s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果 value1 与 value2 相等，则返回 NULL ，否则返回 value1

4.6日期和时间函数

4.6.1获取日期、时间

函数	用法
CURDATE() ， CURRENT_DATE()	返回当前日期，只包含年、 月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、 分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回 UTC （世界标准时间） 日期
UTC_TIME()	返回 UTC （世界标准时间） 时间

4.6.2日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以 UNIX 时间戳的形式返回当前时间。 SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)	将时间 date 以 UNIX 时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将 UNIX 时间戳的时间转换为普通格式的时间

4.6.3获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份： January ， ...
DAYNAME(date)	返回星期几： MONDAY ， TUESDAY.....SUNDAY
WEEKDAY(date)	返回周几，注意，周 1 是 0 ，周 2 是 1 ，。。。周日是 6
QUARTER(date)	返回日期对应的季度，范围为 1 ～ 4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是 1 ，周一是 2 ，。。。周六是 7
EXTRACT(type FROM date)	返回指定日期中特定的部分， type 指定返回的值

EXTRACT(type FROM date) 函数中 type 的取值与含义：

4.6.4时间和秒钟转换的函数

函数	用法
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。转化的公式为： 小时 *3600+ 分钟 *60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间

4.6.5计算日期和时间的函数 

函数	用法
DATE_ADD(datetime, INTERVAL expr type) ， ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差 INTERVAL 时 间段的日期时间
DATE_SUB(date,INTERVAL expr type) ， SUBDATE(date,INTERVAL expr type)	返回与 date 相差 INTERVAL 时间间隔的 日期

上述函数中type的取值 

常见的数值函数

常见的流程函数

聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

SELECT 聚合函数(字段列表) FROM 表名；
--注意：null值不参与所有聚合函数运算 

分组查询

1. 语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2.where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

-- 插入数据
insert into user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');
insert into user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into user(name,age,status,gender) values (null,19,'1','男');
insert into user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into user(name,age,gender) values ('Tom5',120,'男');

 外键约束

        外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

--数据准备
create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

方法一：

--添加外键
create table 表名(
    字段名    数据类型,
    ...
    [CONSTRAINT][外键名称]FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
)

方法二： 

-- 添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

-- 删除外键
alter table 表名 drop foreign key 外键名称;
alter table emp drop foreign key fk_emp_dept_id;

 

-- 外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

多表查询

--笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
-- 多表查询 -- 笛卡尔积
select * from emp , dept where emp.dept_id = dept.id;

内连接:查询表A、表B交集部分数据

--隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件…;
--显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;

-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)  --- INNER JOIN ... ON ...
select e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;

外连接:左外、右外、满外

--左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
--右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
--满外连接（MySQL不支持）
SELECT 字段列表 FROM 表1 FULL [OUTER] JOIN 表2 ON 条件…;
返回所有符合条件的数据

-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
-- 1. 查询emp表的所有数据, 和对应的部门信息(左外连接)
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

-- 2. 查询dept表的所有数据, 和对应的员工信息(右外连接)
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 3. 查询所有满足条件的数据(满外连接)
select d.*, e.* from emp e full outer join dept d on e.dept_id = d.id;

自连接：当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B where 条件…;
自连接查询，可以是内连接查询，也可以是外连接查询。

-- 自连接
-- 1. 查询员工 及其 所属领导的名字
-- 表结构: emp

select a.name , b.name from emp a , emp b where a.managerid = b.id;

-- 2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询-union, union all

• UNION：会执行去重操作
• UNION ALL:不会执行去重操作(效率高)

#9. 7种JOIN的实现：

# 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 方式2：左中图 UNION ALL 右上图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图  UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

#10. SQL99语法的新特性1:自然连接
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`;

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

#11. SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

#拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

SELECT 字段列表 FROM 表A … 
UNION [ALL] 
SELECT 字段列表 FROM 表B …;

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

-- union all , union
-- 1. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

select * from emp where salary < 5000
union all
select * from emp where age > 50;


select * from emp where salary < 5000
union
select * from emp where age > 50;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行)
• 表子查询(子查询结果为多行多列）

根据子查询位置，分为：WHERE之后、FROM之后、SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符：=  <>   >  >=   <   <=

-- 标量子查询
-- 1. 查询 "销售部" 的所有员工信息
-- a. 查询 "销售部" 部门ID
select id from dept where name = '销售部';

-- b. 根据销售部部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');



-- 2. 查询在 "方东白" 入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';

-- b. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN、 NOT IN、 ANY、 SOME、 ALL

-- 列子查询
-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息
-- a. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

-- b. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');


-- 2. 查询比 财务部 所有人工资都高的员工信息
-- a. 查询所有 财务部 人员工资
select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');

-- b. 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );


-- 3. 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');

-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：=、<>、IN、NOT IN

-- 行子查询
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
-- a. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

-- 表子查询
-- 1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
-- a. 查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

-- b. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );


-- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a. 入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';

-- b. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

多表查询案例

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);


-- 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;
-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
-- 3. 查询拥有员工的部门ID、部门名称
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;
-- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
-- 外连接
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;
-- 5. 查询所有员工的工资等级
-- 表: emp , salgrade
-- 连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;
-- 6. 查询 "研发部" 所有员工的信息及 工资等级
-- 表: emp , salgrade , dept
-- 连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id
-- 查询条件 : dept.name = '研发部'
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';
-- 7. 查询 "研发部" 员工的平均工资
-- 表: emp , dept
-- 连接条件 :  emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
-- 8. 查询工资比 "灭绝" 高的员工信息。
-- a. 查询 "灭绝" 的薪资
select salary from emp where name = '灭绝';
-- b. 查询比她工资高的员工数据
select * from emp where salary > ( select salary from emp where name = '灭绝' );
-- 9. 查询比平均薪资高的员工信息
-- a. 查询员工的平均薪资
select avg(salary) from emp;
-- b. 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );
-- 10. 查询低于本部门平均工资的员工信息
-- a. 查询指定部门平均薪资  1
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
-- b. 查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );
-- 11. 查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;

select count(*) from emp where dept_id = 1;

-- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
-- 表: student , course , student_course
-- 连接条件: student.id = student_course.studentid , course.id = student_course.courseid

select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;

四、常见DCL（控制语言）命令

1.查询用户

USE mysql;
SELECT * FROM user;

2. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 创建用户 itcast , 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户 heima , 可以在任意主机访问该数据库, 密码123456 ;
create user 'heima'@'%' identified by '123456';

3.修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 修改用户 heima 的访问密码为 1234 ;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

 4.删除用户

DROP USER '用户名'@'主机名';

-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

eg:show grants for 'heima'@'%';

 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

eg:grant all on itcast.* to 'heima'@'%';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

eg:REVOKE all on itcast.* to 'heima'@'%';

 二、数据库创建、修改与删除

--创建
Create database 数据库名;

--使用某个数据库
Use 数据库名;

--查看有哪些数据库
show databases;

--查看指定数据库下保存的数据表
show tables from 数据库名;

--查询当前使用的数据库 
SELECT DATABASE();

--查询当前数据库所有表
SHOW TABLES;

--查询表结构
DESC 表名;

--查询指定表的建表语句
SHOW CREATE TABLE 表名;

--更改数据库字符集
SHOW CREATE DATABASE 数据库名;
ALTER DATABASE 数据库名 CHARACTER SET 'utf8';

#1.4 删除数据库
#方式1：如果要删除的数据库存在，则删除成功。如果不存在，则报错
DROP DATABASE mytest1;

SHOW DATABASES;

#方式2：推荐。 如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。
DROP DATABASE IF EXISTS mytest1;

DROP DATABASE IF EXISTS mytest2;

#2. 如何创建数据表
USE atguigudb;

SHOW CREATE DATABASE atguigudb; #默认使用的是utf8

SHOW TABLES;

四、创建某个表

Create table 表名 (
	列名1 int auto_increment primary key，
	列名2 varchar（字符个数） not null，
	列名3 date null
);
--每个列名和每个列名用逗号隔开，最后一个则不需要

命令名	含义	命令名	含义	命令名	含义
Not null	不能为空	Null	可以为空	auto_increment	自动递增
primary key	设置为主键

五、插入数据 

1.给指定字段添加数据
INSERT INTO 数据库名.表名(列名1，列名2，列名3) VALUES (数值1，数值2，数值3);

2.给全部字段添加数据
INSERT INTO 数据库名.表名 VALUES (值1,值2,……);

3.批量添加数据
INSERT INTO 数据库名.表名(列名1，列名2) VALUES (数值1，数值2),(数值1，数值2),(数值1，数值2);
INSERT INTO 数据库名.表名 VALUES (值1,值2,……),(值1,值2,……),(值1,值2,……);

命令	含义
Default	帮助我们后面递增数字

六、改变表格

--增加一个列（添加字段）
Alter table 数据库名.表名 add 列名 数据类型 默许条件;

--修改数据类型
Alter Table 数据库名.表名 modify 字段名 新数据类型(长度);

--修改字段名和字段类型
ALTER TABLE 数据库名.表名 change 旧字段名 新字段名 类型（长度）；

--删除字段
ALTER TABLE 数据库名.表名 DROP 字段名;

--修改表名
ALTER TABLE 数据库名.表名 RENAME TO 新表名;

--修改数据
Update 数据库名.表格名 set 值 [where 条件];
--注意：若不跟where条件，则会更新整个表，使用需谨慎
UPDATE egg.eggs_record SET sold  = '2022-06-06' WHERE id = 3;

--删除数据库某个值
Delete from 数据库名.表格名 where 条件;

--删除数据库某个表格
Drop table 数据库名.表格名;

--删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

--删除数据库
Drop database 数据库名;

数值类型

字符串类型

日期类型

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

预备工作

-- 数据准备
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';

insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);


-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四';

方式一：修改提交方式为手动

1.查看/设置事务提交方式

SELECT @@autocommit;

SET @@autocommit =0;        --设置为手动提交

--1为自动提交，0为手动提交

2.转账操作流程

-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

注意：若出现 Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

原因：mysql的safe-updates模式中，如果where后跟的条件不是主键，就会出现这种错误

两种方式：

1. where后面加上主键的条件
2. 更改模式：SET SQL_SAFE_UPDATES = 0;

建议更改数据库模式

如果想要提高数据库安全等级，可以在恢复回原有的设置，执行命令：

SET SQL_SAFE_UPDATES = 1;

当任务完成后，则可以手动提交事务

COMMIT;

当任务报错，则可以回滚事务

ROLLBACK;

方式二（不用修改事物的提交方式）
1.开启事务

start transaction;

2.转账操作流程

-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

 当任务完成后，则可以手动提交事务

COMMIT;

当任务报错，则可以回滚事务

ROLLBACK;

事务的四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency)：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

事务隔离级别

        目的：解决并发带来的问题

        mysql默认为Repeatable Read，oracle默认为Read committed

        由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable 

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED I REPEATABLE READ I SERIALIZABLE }

eg1:set session transaction isolation level read uncommitted ;
eg2:set session transaction isolation level repeatable read ;

注意：事务隔离级别越高，数据越安全，但是性能越低。