MYSQL基础（一) --- 学习笔记

一.基础操作

启动与关闭：net start mysql80，net stop mysql80

客户端连接：mysql -u root -p

关系型数据库：建立在关系模型基础上，由多张相互连接的二维表（由行和列组成的表）组成的数据库

二.SQL

通用语法：

分类：DDL,DML,DQL,DCL

DDL语句：定义数据库对象(数据库，表， 字段)

数据库操作：

1.查询所有数据库：show databases ;

2.查询当前数据库：select database() ;

3.创建一个xxx数据库：create database [if not exists] xxx [default charset utf8mb4] [collate 排序规则];

4.删除一个xxx数据库：drop database [if exists] xxx;

5.切换数据库：use xxx；

表操作：

1.查询当前数据库所有表：show tables;

2.查看指定表结构：desc 表名 ;

3.查询指定表建表语句：show create table 表名；

4.创建表：

表数据类型：

1.数值类型：

tinyint	1B
smallint	2B
mediumint	3B
int、integer	4B
bigint	8B
float	4B--指明精度（总位数）与标度（小数点后位数）
double	8B-指明精度（总位数）与标度（小数点后位数）
decimal	依据精度（总位数）与标度（小数点后位数）

2.字符串类型：

3. 日期时间类型：

5.添加字段： ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]；

6.修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

7，修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

8.删除字段：ALTER TABLE 表名 DROP 字段名;

9.修改表名：ALTER TABLE 表名 RENAME TO 新表名;

10.删除表：DROP TABLE [ IF EXISTS ] 表名;

11.删除指定表, 并重新创建表：TRUNCATE TABLE 表名;（在删除表的时候，表中的全部数据也都会被删除）

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

4. 修改数据：UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;（修改语句如果没有条件，则会修改整张表的所有数据。）

5. 删除数据：DELETE FROM 表名 [ WHERE 条件 ] ;（DELETE 语句如果没有条件，则会删除整张表的所有数据。 DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即 可)。）

DQL：用来查询数据库中表的记录

语句编写顺序：

1.基本查询（select from）：

查询多个字段：SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;（* 查询所有字段）

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

去除重复记录：SELECT DISTINCT 字段列表 FROM 表名;

2.条件查询（where）：

 

select * from emp where idcard is not null;

select * from emp where age in(18,20,40);

#查询姓名为两个字的
select * from emp where name like '__';

#查询身份证号最后一位是X的
select * from emp where idcard like '%X';

3.分组查询（group by）：

聚合函数（作用于某字段）：SELECT 聚合函数(字段列表) FROM 表名 [where 条件];（NULL值是不参与所有聚合函数运算的）

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

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

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

*可以理解为将整张表依据分组条件划分为多个子表（记录），分别对每个子表整体作查询操作

select workaddress, gender, count(*) '数量' from emp group by gender , workaddress 
;

4.排序查询（order by）：

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

#ASC : 升序(默认值，可省略)； DESC: 降序；如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

5.分页查询 ( limit ) ：

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

#：起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。如果查询的是第一页数据，起始索引可以省略。limit作用在查询语句最后部分。

DQL语句执行顺序：

DCL语句：管理数据库用户、控制数据库的访问权限

1.查询用户：select * from mysql.user;

2.创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';（主机名可使用 % 通配）

3.更改密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

4.删除用户：DROP USER '用户名'@'主机名' ;

5.权限控制：

查询权限： SHOW GRANTS FOR '用户名'@'主机名' ;

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

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

#：多个权限之间，使用逗号分隔；授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

三.函数： 

MySQL中函数：字符串函数、数值函数、日期函数、流程函数

1.字符串函数：

#start位置从下标1开始

2.数值函数：

#生成一个六位数的随机验证码。
select lpad(round(rand()*1000000 , 0), 6, '0');

 3.日期函数：

 

#入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by 
entrydays desc;

4.流程函数：条件筛选

#when可以多次出现，从前往后依次匹配即可（类似if-else）

#成绩评级
select
 id,
 name,
 (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) 
'数学',
 end ) '英语',
 end ) '语文'
 (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' 
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' 
from score;

四.约束：

作用于表中字段上的规则，用于限制存储在表中的数据（创建表/修改表的时候添加约束）

自增约束：AUTO_INCREMENT（插入时该字段可以省略）

外键约束：含有外键的表是子表，外键关联的表为父表

添加外键：

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

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

删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

五.多表查询：

1：n（在多的一方建立外键，指向一的一方的主键）

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

1：1（多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)）

笛卡尔积：两个集合A集合 和 B集合的所有组合情况；去除无效的笛卡尔积， 可以给多表查询加上连接查询的条件

1.内连接：查询A、B交集部分数据（即满足连接条件）

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

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

2.外连接： 

左外连接：查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接：查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

3.自连接：是把一张表连接查询多次，可以是内连接查询，也可以是外连接查询

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

4.联合查询：多次查询的结果合并起来，形成一个新的查询结果集

 SELECT  字段列表   FROM   表A  ...  
UNION [ ALL ]
 SELECT  字段列表  FROM   表B  ....;
#对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
#union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

5.子查询：

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询；子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个（子查询位置-where，from，select之后）

根据子查询结果分类：

1. 标量子查询（子查询结果为单个值-一行一列）:常用的操作符：= <> > >= < <=

2. 列子查询(子查询结果为一列):IN 、NOT IN 、( ANY 、SOME 、 ALL-可结合上述操作符运用)

3. 行子查询(子查询结果为一行):= 、<> 、IN 、NOT IN

select * from emp where (salary,managerid) = (select salary, managerid from emp 
where name = 'xxx');

4. 表子查询(子查询结果为多行多列,类似行子查询的延伸)：in

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

#from之后的例子-即将其作为表进行连接查询
 select e.*, d.* from (select * from emp where entrydate > '2024-01-01') e left 
join dept d on e.dept_id = d.id ;

#补充select之后的子查询例子
#查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' 
from dept d;

六.事务：

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

事务操作：

1.设置为事务手动提交：

SELECT  @@autocommit ;

SET   @@autocommit = 0 ;

COMMIT;

ROLLBACK;

1.明确开启事务：

START  TRANSACTION   或  BEGIN ;

COMMIT;

ROLLBACK;

事务四大特性（ACID）：

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

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

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

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

并发事务问题：

赃读：一个事务读到另外一个事务还没有提交的数据

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在

事务隔离级别：

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

#设置事务隔离级别
SET  [ SESSION | GLOBAL ]  TRANSACTION  ISOLATION  LEVEL  { READ UNCOMMITTED | 
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

#事务隔离级别越高，数据越安全，但是性能越低