MySQL学习笔记(上)

1.MySQL概述

数据库：数据存储的仓库 DB

数据库管理系统：操纵和管理数据库的大型软件 DBMS

SQL：操作关系型数据库的编程语言 SQL

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：1.使用表存储结构，格式统一，便于维护 2.使用SQL语言操作，标准统一，使用方便

2.SQL

SQL通用语法

1.单行或多行，以分号结尾

2.用空格/缩进来增强可读性

3.SQL语句不分大小写，关键字建议使用大写

4.注释：

1.单行注释 --注释 #注释

2. 多行注释：/注释/

SQL分类

数据类型

MySQL 数据类型 | 菜鸟教程

数值类型

日期和时间类型

字符串类型

定长：char() 性能好

变长：varchar() 性能差

DDL-数据定义语言

数据库操作

查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SHOW DATABASE();

创建

SELECT DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则] #方括号中可选

删除

DROP DATABASE [IF EXISTS] 数据库名；

使用

USE 数据库名;

表操作

查询

1.查询所有表

SHOW TABLES;

2.查询表结构

DESC 表名;

3.查询指定表的建表语句

SHOW CREATE TABLE 表名;

创建

CREATE TABLE 表名{
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	字段3 字段3类型[COMMENT 字段3注释],
	......
	字段n 字段n类型[COMMENT 字段n注释],
}[COMMENT 表注释];

create table employee
(
    id     int comment '学号',
    name   varchar(30) comment '姓名',
    gender char(1) comment '性别',
    id_card int comment '身份证号'
)comment '员工表';

修改

添加字段

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

alter table employee add grade smallint comment '成绩';

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新的数据类型(长度)；

alter table employee modify id_card char(18);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

DROP TABLE [IF EXISTS] 表名;

DML-数据操作语言

添加数据

1.给指定字段添加数据

INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2...);

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,...);

insert into employee(id,name,gender,id_card,grade) values(1,'张三','男','1234567',100),(2,'李四','男','1236666',60);

修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];

删除数据

DELETE FROM 表名 [WHERE 条件]

DQL-数据查询语言

SELECT
	字段列表
FROM 
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询

1.查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;
​
SELECT * FROM 表名;

2.设置别名

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

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1.语法

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

2.条件

聚合函数

注意：null值不参与计算

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

select sum(age) from emp where workaddress = '西安';

分组查询

分组之后查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

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

select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >=3;

where和having的区别

执行时机：where在分组前过滤，having是在分组之后对结果进行过滤

判断条件不同：where不能对聚合函数进行判断，having可以

排序查询

1.语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
​
select *from emp order by age asc; #默认升序
select *from emp order by age desc;
​
select * from emp order by age asc, entrydate desc; #多字段，先按照age升序排序，age一样时才会根据entrydate降序排序

2.排序方式

ASC：升序

DESC：降序

多字段排序时，当第一个字段相同时才会按照第二个排序

分页查询

1.语法

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

DCL-数据控制语言

1.管理用户

主机名可以用通配符%

USE mysql;
SELECT *FROM user;

2.创建用户

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

3.修改用户密码

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

4.删除用户

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

权限控制

1.查询权限

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

2.授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
​
grant all on itcast.* to 'heima'@'%';

3.撤销权限

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

3.函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

select concat('Hello','MySQL');

select lower('Hello');

select lpad('01',5,'-') #---01

select rpad('01',5,'-') #01---

select trim(' Hello MySQL ') #Hello MySQL

将员工工号变为5位，不足补0

update emp set workno = lpad(workno,5,'0');

数值函数

生成一个6位数的随机验证码

select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

查询入职天数，根据天数倒序排序

select name, datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

需求：查询emp表的员工姓名和工作地址，如果是北京上海则显示一线城市，否则显示二线城市

select 
	name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;	

需求：对成绩进行分级

select 
	id,
	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;

4.约束

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

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

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

外键约束

两张表之间建立连接，保证数据的一致性和完整性。

子表：具有外键的表

父表：外键关联的表

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

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

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

添加外键

alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除外键

alter table emp drop foreign key fk_emp_dept_id;

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

5.多表查询

多表关系

各个表结构之间的联系：一对多、多对多、一对一

一对多

部门一，员工多

实现：再多的一方建立外键，指向一的一方

多对多

学生和课程的关系

实现：建立第三张表，中间表至少包含两个外键，分别关联两方主键。

一对一

用户和用户详情的关系，多用于单表拆分

实现：在任意一方加入外键，关联另一方主键，并且设置外键为唯一的。

多表查询概述

指从多张表中查询数据

笛卡尔积：两个集合A和B中的所有数据组合情况，在多表查询时，需要消除无效的笛卡尔积

SELECT *FROM 表名1, 表名2 WHERE 条件;

多表查询分类

查询连接：内连接、外连接、子查询

子查询

内连接

内连接查询的是两张表的交集部分

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...；

select e.name, d.name from emp e, dept d where e.dept_id = d.id; #起了别名后就不能再用表名了

显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

select e.name,d.name from emp e inner join dept d on e.dept_id= d.id;

外连接

左外连接

包含表1的所有数据和两表的交集

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
​
select e.*, d.name from emp e left outer join dept d on e.dept_id= d.id;

包含表2的所有数据和两表的交集

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
​
select d.*, d.* from emp e right outer join dept d on e.dept_id= d.id;

自连接

可以是内连接，也可以是外连接

SELECT 字段列表 DROM 表A 别名A JOIN 表A 别名B ON 条件...;
​
#内连接
select a.name, b.name from emp a, emp b where a.mangerid= b.id; 
​
#外连接，左外，包含员工表所有信息
select a.name '员工', b.name '领导' from emp a left join emp b on a.mangerid= b .id;

联合查询

把多次查询结果合并

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

select *from emp when salary < 5000;
union all #有重复
select * from emp where age > 50;

select *from emp when salary < 5000;
union #去重
select * from emp where age > 50;

子查询

嵌套SELECT语句，称为嵌套查询，又称子查询

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

标量子查询

子查询返回结果是单个值，如一个部门的id，一个员工的入职时间

select * from emp where dept_id= (select id from dept where name= '销售部'); #查询销售部的人
​
select* from emp where entrydate> (select entrydate from emp where name  = '方东白');

列子查询

子查询返回结果是一列

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

select * from emp where dept_id in (select id from dept where name = '销售部' or name= '市场部');

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

行子查询

子查询返回的结果是一行

表子查询

子表返回的结果是多行多列

select * from emp where (job,salary) in (select job, salary from emp where name= 'zhangsan' or name= 'lisi');

综合应用

查询工资所有低于本部门员工平均薪资的员工信息

select * from emp e2 where e2.salary <(select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);    

6.事务

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

事务操作

select @@autocommit;
--设置为提交方式 0位手动 1位自动
set @@autocommit = 0;
--转账操作
start transaction;
​
select * from account where name= '张三';
​
update account set money = money - 1000 where name = '张三';
​
update account set money = money + 1000 where name= '李四';
​
--提交事务
commit;
--回滚事务
rollback;

事务四大特性

原子性：事务是不可分割的最小操作单元，要么全成功，要么全失败

一致性：事务完成时，必须使所有的数据都保持一致状态。

隔离性：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

并发事务问题

事务隔离级别

--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

--设置事务隔离级别
SET [SESSION\GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];