Mysql -- 基础

SQL

SQL通用语法：

SQL分类：

DDL:

数据库操作

查询：

SHOW DATABASES；

创建：

CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET字符集] [COLLATE 排序规则]；

删除：

DROP DATABASE [IF EXISTS] 数据库名；

使用：

USE 数据库名；

DDL - 表操作 - 查询：

查询当前数据库所有表

SHOW TABLES;

查询表结构：

DESC 表名

查询指定表的建表语句：

SHOW CREATE TABLE 表名；

DDL - 表操作 - 创建：

DDL - 表操作 - 数据类型

create table emp(-> id int comment '编号',-> workno varchar(10) comment '员工工号',-> name varchar(10) comment '姓名',-> gender char(1) comment '性别',-> age tinyint unsigned comment '年龄',-> idcard char(18) comment '身份证号',-> entrydate date comment '入职时间'-> ) comment '员工表';

DDL - 表操作 - 修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释][约束];

修改数据类型：

ALTER TABLE MODIFY 字段名 新数据类型（长度）

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释]

删除字段：

ALTER 表名 DROP 字段名;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

DDL - 表操作 - 删除

删除表

DROP TABLE [IF EXISTS] 表名

删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

相当于清空

DML:

DML - 添加数据

给指定字段添加数据

INSERT INTO 表名（字段1，字段2）VALUES(值1，值2);

给全部字段添加数据

INSERT INTO 表名 VALUES（值1，值2）;

批量添加数据

INSERT INTO 表名（字段1，字段2） VALUES （值1，值2）,(值1，值2)，（值1，值2）;

INSERT INTO 表名 VALUES（值1，值2）,（值1，值2）;

注：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
• 字符串和日期数据应该在包含在引号中
• 插入的数据应该在范围内

DML - 修改数据

UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2，[WHERE ...]

注：修改语句的条件可以有，也可以没有，如果么有条件，则会修改整张表的所有数据。

DML - 删除数据

DELETE FROM 表名 [WHERE 条件];

DQL:

语法：

DQL - 基本查询：

查询多个字段

SELECT 字段1，字段2，字段3 ... FROM 表名;

SELECT * FROM 表名

设置别名

SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DQL - 条件查询：

语法：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

-- --------------------------------------------------- > 查询需求 <------------------------------------------------ 基本查询
-- 1. 查询指定字段 name workno age 返回
select name,workno,age from emp;-- 2. 查询所有字段返回
select * from emp;-- 3. 查询所有员工的工作地址，起别名
select workaddress as '工作地址' from emp;-- 4. 查询公司员工的上班地址（不重复）
select distinct  workaddress '工作地址' from emp;-- 条件查询
-- A. 查询年龄等于 88 的员工
select * from emp where age = 88;-- B. 查询年龄小于 20 的员工信息
select * from emp where age <= 20;-- D. 查询没有身份证号的员工信息
select * from emp where idcard is null;-- E. 查询有身份证号的员工信息
select * from emp where idcard is not null;-- F. 查询年龄不等于 88 的员工信息
select * from emp where age <> 88;-- G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age >=15 && age<=20;
select * from emp where age >=15 AND age<=20;
select * from emp where age between 15 and 20; -- between 跟最小值-- H. 查询性别为 女 且年龄小于 25岁的员工信息
select  * from emp where gender = '女' and age <25;-- I. 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 || age = 20 || age = 40 ;
select * from emp where  age in(18,20,40);-- J. 查询姓名为两个字的员工信息 _ %
select * from emp where name like '__';-- K. 查询身份证号最后一位是X的员工信息
select * from emp where idcard  like '%X';

聚合函数：

分组查询：

排序查询：

分页查询：

DQL -聚合函数：

常见聚合函数

语法：

SELECT 聚合函数(字段列表) FROM 表名;

注：null值不参与所有聚合函数的运算

-- ------------------------------------> 聚合函数--
-- A. 统计该企业员工数量
select count(*) from emp;
select count(idcard) from emp;-- B. 统计该企业员工的平均年龄
select avg(age) from emp;-- C. 统计该企业员工的最大年龄
select max(age) from emp;-- D. 统计该企业员工的最小年龄
select min(age) from emp;-- E. 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';

DQL -分组函数：

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

where 和 having区别

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

-- 分组查询
-- A. 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender,count(*) from emp group by gender ;
-- B. 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender,avg(age) from emp group by gender;
-- C. 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress,count(*) address_count from emp where age <45 group by  workaddress having address_count >= 3;
-- D. 统计各个工作地址上班的男性及女性员工的数量
select workaddress,gender,count(*) '数量' from emp group by gender,  workaddress;

• 执行顺序：where > 聚合函数 > having
• 分组之后，查询字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL -排序查询：

语法：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

排序方式：

• ASC :升序
• DESC：降序

-- 排序查询
-- A.根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
select * from emp order by age desc ;
-- B. 根据入职时间, 对员工进行降序排序
select * from emp order by entrydate desc;
-- C. 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from  emp  order by  age asc, entrydate desc;

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL -分页查询：

语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

-- 分页查询
-- A. 查询第1页员工数据, 每页展示10条记录
select * from emp limit 10;
-- B. 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from emp limit 10,10;

练习：

-- 2.6.8 案例
-- 1). 查询年龄为20,21,22,23岁的员工信息。
select * from emp where gender = '女' and age in(20,21,22,23);-- 2). 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';-- 3). 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender,count(*) from emp where age < 60 group by gender;-- 4). 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name '姓名',age '年龄' from emp where age <= 35 order by age asc,entrydate desc ;-- 5). 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and (age between 20 and 40)  order by age asc,entrydate desc limit 5;

DQL -执行顺序：

DCL:

DCL - 管理用户：

1) 查询用户
select * from mysql.user; 1
2). 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 1
3). 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; 1
4). 删除用户
DROP USER '用户名'@'主机名' ;

注意事项:

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

DCL- 权限控制

函数

字符串函数

--  ----------------------------   函数 ------------------------------- --
-- conccat
select concat('Hello','Mysql');-- lower
select lower('HEllo');-- upper
select upper('hello');-- Lpad
select lpad('01',5,'-');-- rpad
select rpad('01',5,'-');-- trim
select trim(' Hello   MySql   ');-- substring
select substring('Hello  MySQL',1,5);update emp set workno = lpad(workno,5,'0');

数值函数

常见的数值函数如下：

-- 数值函数
-- ceil
select ceil(1.5);-- floor
select floor(1.1);-- mod
select mod(3,4);-- rand
select rand();-- round
select round(2.345,2);-- 生成六位随机验证码
select lpad(round(rand() * 1000000,0),6,'0') ;

日期函数

-- 日期函数
-- curdate
select curdate();
-- curtime
select curtime();
-- now
select now();-- Year,MONTH,DAY
select YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());-- DATE_ADD
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY );-- DATEDIFF(第一个时间减去第二个时间)
SELECT DATEDIFF('2021-12-01','2021-12-21');select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc ;

流程函数

-- 流程控制函数
-- -- IF
select if(true,'ok','Error');
select if(false,'ok','Error');-- IFNULL
select ifnull('ok','default'); -- ok
select ifnull(null,'default'); -- default-- CASE
SELECTNAME,(CASE workaddress WHEN '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
FROM emp;

-- 案例
selectid,name,(case  when math >=85 then '优秀' when math >=60 then '及格' else '不及格' end) '数学' ,(case  when english >=85 then '优秀' when english >=60 then '及格' else '不及格' end) '英语' ,(case  when chinese >=85 then '优秀' when chinese >=60 then '及格' else '不及格' end) '语文'from score;

约束

概述：

案例：

根据需求，完成表结构的创建

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 ('程梦雨',19,'1','女'),('Messi',20,'1','男');

外键约束：

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

语法：

添加外键：

CREATE TABLE 表名 (字段名  数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCE 主表(主表列名);

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 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表名 ON UPDATE CASCADE ON DELETE CASCADE;

-- 外键的删除和更新行为
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;

多表查询分类：

连接查询 - 内连接：

-- 内连接
-- 1.查询每一个员工的姓名及关联的部门的名称（隐式内连接实现）
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;-- 2. 查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
select emp.name , dept.name from emp inner join dept on emp.dept_id = dept.id;

连接查询 - 外连接：

-- 外连接
-- 1. 查询emp标的所有数据，和对应的部门信息（左外）
select emp.* ,d.name from emp left join dept d on d.id = emp.dept_id;-- 2. 查询dept表的所有数据，和相对应的员工信息（右外）
select d.* , emp.*  from emp right join  dept d on d.id = emp.dept_id;

连接查询 - 子连接：

-- 子链接
-- 1. 查询员工及其领导的名字
select e.name , b.name from emp e , emp b where e.managerid = b.id;-- 2. 查询员工及其领导的名字 ,如果没有领导，也要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询：

-- 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;

子查询：

标量子查询：（返回单个值）

-- 标量子查询
-- 1. 查询“销售部”的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');-- 2. 查询“房东白”入职之后的员工信息
select entrydate from emp where name = '方东白';select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询：（返回的结果是一列）

-- 列子查询
-- 1. 查询销售部和市场部的所有员工信息
select * from emp where dept_id in (select dept.id from dept where name = '市场部' or name = '销售部');-- 2. 查询比财务部所有人员工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id  = (select id from dept where name = '财务部'));-- 3. 查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id  = (select id from dept where name = '研发部');select * from emp where salary > any (select salary from emp where dept_id  = (select id from dept where name = '研发部'));

行子查询：

-- 行子查询
-- 1. 查询与“张无忌”工资和直属领导相同的信息
select salary , managerid from emp where name = '张无忌';select * from emp where (salary,managerid) = (select salary , managerid from emp where name = '张无忌');

表子查询：（返回多行多列）

-- 表子查询
-- 1. 查询路杖客，宋远桥的职位和薪资相同的员工信息
select salary , job from emp where name = '鹿杖客' or name = '宋远桥';select * from emp where (job,salary) in (select job, salary  from emp where name = '鹿杖客' or name = '宋远桥');-- 2.查询入职日期是2006-01-01之后的员工信息及其部门信息
select * from emp where entrydate > '2006-01-01';select e.* , d.* from (select * from emp where entrydate > '2006-01-01') e  left join dept d on e.dept_id = d.id;

练习：

-- 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name, e.age,e.job,d.name from emp e join dept d on d.id = e.dept_id;-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name,e.age,e.job,d.name from emp e join dept d on d.id = e.dept_id where e.age < 30;-- 3. 查询拥有员工的部门ID、部门名称
select distinct d.id,d.name from emp e,dept d where e.dept_id = d.id;-- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来-- 外连接select e.*,d.name from emp e left join dept d on d.id = e.dept_id where e.age > 40;-- 5. 查询所有员工的工资等级-- 表：emp salgrade-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisalselect e.* ,s.grade from emp e ,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;-- 6. 查询 "研发部" 所有员工的信息及 工资等级
select e.*, s.grade
from emp e,salgrade s
where e.dept_id = (select dept.id from dept where dept.name = '研发部')and (e.salary between s.losal and s.hisal);-- 7. 查询 "研发部" 员工的平均工资
select avg( e.salary) '平均工资' from emp e, dept d where e.dept_id = d.id and d.name = '研发部';-- 8. 查询工资比 "灭绝" 高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝');-- 9. 查询比平均薪资高的员工信息
select avg(salary) from  emp;
select * from emp where salary > (select avg(salary) from emp);-- 10. 查询低于本部门平均工资的员工信息
select avg(e1.salary) from emp e1 where e1.dept_id = 1;select *,(select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) '平均工资' 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. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

总结：

事务：

事务简介：

select @@autocommit;set @@autocommit = 0; -- 设置手动提交
-- 转账操作
-- 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;

--    ---------------- 方式二 ----------------
begin ;
-- 转账操作
-- 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 ;

事务的四大特性：

并发事务问题：

并发事务的隔离级别：

orcal的默认是 Read committted

事务隔离级别越高，数据效率越低

总结：