MySQL基础
一:MySQL安装及启动
-
MySQL启动
net start mysql80 net stop mysql80
-
MySQL客户端连接
MySQL自带命令行 mysql [-h 127.0.0.1] [-p 3306] -u root -p
二:SQL
-
SQL通用语法
- SQL语句可以单行或多行进行书写,以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释:
- 单行注释:–注释内容 或 #注释内容(MySQL特有)
- 多行注释:/* 注释内容 */
-
SQL分类
分类 全称 说明 DDL Data Definition Language 数据定义语言,用来定义数据库对象(数据库,表,字段) DML Data Manipulation Language 数据操作语言,用来对数据库表中的数据进行增删改 DQL Data Query Language 数据查询语言,用来查询数据库中表的记录 DCL Data Control Language 数据控制语言,用来创建数据库用户,控制数据库的访问权限 -
DDL-表操作-查询
- 查询当前数据库的所有表
SHOW TABLES;
- 查询表结构
DESC 表名;
- 查询指定表的建表语句
SHOW CREATE TABLE 表名;
-
DDL-表操作-创建
CREATE TABLE 表名( 字段1 字段类型[COMMENT 字段1注释], 字段2 字段类型[COMMENT 字段2注释], 字段3 字段类型[COMMENT 字段3注释] )[COMMENT 表注释] --注意最后一个字段不加逗号 /*例:员工表 char:定长字符串,效率高 varchar:变长字符串,效率低 unsigned:无符号(都为正数,无负数) */ create table emp( id int comment '编号', worknum varchar(10) comment '工号', name varchar(10) comment '姓名', gender char(1) comment '性别', age tinyint unsigned comment '年龄', entrydate date comment '入职时间' ) comment '员工表';
-
DDL-表操作-修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束] --例: alter table emp add nickname varchar(20) comment '昵称';
-
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束]; --例: alter table emp change nickname username varchar(30) comment '用户名';
-
删除字段
ALTER TABLE 表名 DROP 字段名; --例: alter table emp drop username;
-
修改表名
ALTER TABLE 表名 RENAME TO 新表名; --例: alter table emp rename to employee;
-
删除表
--删除表: --其中的数据都被删除 DROP TABLE[IF EXISTS] 表名; --删除指定表,并重新创建该表: --只留下表名,其中的数据都被删除 TRUNCATE TABLE 表名;
-
-
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),(值1,值2);
-
-
DML-修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值, [WHERE 条件];
-
DML-删除数据
DELETE FROM 表名 [WHERE 条件] -- 注意: delete 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据 delete 语句不能删除某一个字段的值(可以使用update)
-
DQL-查询(编写顺序)
名称 后接 SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数 -
查询多个字段
SELECT 字段1,字段2,字段3, FROM 表名; SELECT * FROM 表名;
-
设置别名
SELECT 字段1[AS 别名1],字段2[AS 别名2] FROM 表名;
-
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
-
-
DQL-条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <>或!= 不等于 BETWEEN…AND… 在某个范围之内(含最小、最大值) IN(…) 在in之后的列表中的值,多选一 LIKE 占位符 模糊匹配(_匹配单个字符,%匹配任意个字符) IS NULL 是NULL AND 或 && 并且(多个条件同时成立) OR 或 || 或者(多个条件任意一个成立) NOT 或 ! 非,不是 _代表一个字符,%代表任意多个字符
-
DQL-聚合函数
-
介绍
将一列数据作为一个整体,进行纵向计算
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
SELECT 聚合函数(字段列表) FROM 表名;
--null不计入其中 select count(*) from emp; select avg(age) from emp; select min(age) from emp;
-
-
DQL-分组查询(GROUP BY)
-
语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
-
where 与 having 区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件不参与分组,而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
-
select gender,count(*) from emp group by gender; select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3;
-
注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
-
-
DQL-排序查询
-
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式,字段2 排序方式2; /*asc升序 desc倒序 */ --先按年龄进行升序排序,如果年龄相同,则按照入职时间进行降序排序(默认为asc) select * from emp order by age asc ,entrydate desc ;
-
-
DQL-分页查询
-
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数; select * from emp limit 0,10; select * from emp limit 10,10;
-
注意
- 起始索引从0开始,起始索引=(查询页码-1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是limit。
- 如果查询的是第一页的数据,起始索引可以省略,直接简写为limit 10。
-
-
DQL-练习
select * from emp where gender="女" and age in(17,18); select * from emp where gender="男" and age between 18 and 40 and name like '___'; select gender,count(*) from emp where age<60 group by gender;
-
DCL-介绍:
用来管理数据库用户、控制数据库的访问权限
-
DCL-管理用户
-
查询用户
USE mysql SELECT * FROM user
-
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; --在任意主机都能访问 CREATE user 'itcast'@'%' identified by '123456';
-
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
-
删除用户
DROP USER '用户名'@'主机名';
注意:
- 主机名可以使用%通配
- 这类SQL开发人员操作的比较少,主要是DBA(数据库管理人员)使用
-
-
DCL-权限控制
权限 说明 ALL,ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库/表/视图 CREATE 创建数据库/表 -
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-
-
三:函数
- 字符串函数
MySQL中内置了很多字符串函数,常用的几个如下:
函数 | 功能 |
---|---|
CONCAT(S1,S2,…SN) | 字符串拼接,将S1,S2,…SN拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串全部转换为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
-
用法
update emp set workNum =(lpad(workNum,5,'0'));
-
数值函数
常见的数值函数:
函数 功能 CEIL(x) 向上取整 FLOOR(x) 向下取整 MOD(x,y) 返回x/y的模 RAND() 返回0-1内的随机数 ROUND(x,y) 求参数x的四舍五入的值,保留y位小数 随机生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');
-
日期函数
常见的日期函数:
函数 功能 CURDATE() 返回当前日期 CURTIME() 返回当前时间 NOW() 返回当前日期和时间 YEAR(date) 获取指定date的年份 MONTH(date) 获取指定date的月份 DAY(date) 获取指定date的日期 DATE_ADD(date,INTERVAL expr type) 返回一个日期/时间值上加一个时间间隔expr后的时间值 DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数 select date_add(now(),interval 70 day ); --两个日期之间的间隔 select datediff(now(),'2021-01-01'); select name,datediff(now(),entrydate) as 'interval' from emp order by `interval` desc ;
-
流程函数
流程函数可以在SQL语句中实现条件筛选,从而提高语句的效率。
函数 功能 IF(value,t,f) 如果value为true,则返回t,否则返回f IFNULL(value1,value2) 如果value1不为空,返回value1,否则返回value2 CASE WHEN [val1] THEN [res1] … ELSE [default] END 如果val1为true,返回res1,… 否则返回default默认值 CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END 如果expr的值等于val1,返回res1. …否则返回default默认值 实例:
-- 查询emp表的员工姓名和工作地址(北京/上海 ----> 一线城市 其他 ----> 二线城市) select name, (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;
四:约束
-
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
-
目的:保证数据库中数据的正确、有效性和完整性
-
分类:
约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一,不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空切唯一 PRIMARY KEY 默认约束 保存数据是,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK 外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY 注意:
约束是作用于表中字段的,可以在创建表/修改表的时候添加约束
-
实例:
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 '性别' );
-
添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id);
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
-
-
外键的删除/更新行为
行为 说明 NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null) SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持) 语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)ON UPDATE CASCADE/set null ONDELTE ACSCADE
五:多表查询
概述:项目开发中,进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种练习,基本上分为三种:
- 一对多
- 多对一
- 一对一
-
一对多
- 案例:部门与员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向一的一方的主键
-
多对多
-
案例:学生与课程的关系
-
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
-
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
-
-- 相关代码 create table student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' ) comment '学生表'; create table course( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称' ) comment '课程表'; create table student_course( id int auto_increment comment '主键' primary key , studentid int not null comment '学生id', coureseid int not null comment '课程id', constraint fk_courseid foreign key (coureseid) references course(id), constraint fk_studentid foreign key (studentid) references student(id) ) comment '学生课程中间表';
-
-
一对一
-
案例:用户与用户详情的关系
-
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升工作效率
-
实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的
-
相关代码:
create table tb_user( id int auto_increment primary key comment '主键id', name varchar(10) comment '姓名', age int comment '年龄', gender char(1) comment '1:男,2:女', phone char(11) comment '手机号' ) comment '用户基本信息表'; create table tb_user_edu( id int auto_increment primary key comment '主键id', degree varchar(20) comment '学历', marjor varchar(50), primaryschool varchar(50), middleschool varchar(50), university varchar(50), userid int unique comment '用户id', constraint fk_userid foreign key (userid) references tb_user(id) ) comment '用户教育信息表';
-
-
多表查询:
概述:指从多张表中查询数据
-
相关代码:
select * from dept ,user where dept.id = user.dept_id;
-
分类:
-
连接查询
内连接:相当于A,B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
-
子查询
-
内连接(查询的是两张表交集的部分)
-
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件 -- 实例 select dept.name,user.name from dept,user where user.dept_id = dept.id; -- 起别名(当起别名后不可以使用原名) select u.name,d.name from user u,dept d where u.dept_id = d.id
-
显示内连接
SELECT 字段列表 FROM 表1,[INNER] JOIN 表2 ON 连接条件 -- inner可以省略 select u.name,d.name from user u inner join dept d on u.dept_id = d.id;
-
-
外连接
-
左外连接(相当于查询表1(左表)的所有数据,包含表一和表二交集部分的数据)
SELECT 字段列表 FROM 表1 LEFT[OUTER] JOIN 表2 ON 条件... -- [OUTER]可以省略 select u.*, d.name from user u left join dept d on d.id = u.dept_id;
-
右外连接(相当于查询表1(右表)的所有数据,包含表一和表二交集部分的数据)
SELECT 字段列表 FROM 表1 RIGHT[OUTER] JOIN 表2 ON 条件...
-
-
自连接
-
语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 -- 必须起别名(内连接) select u.name ,b.name from user u,user b where u.managerid = b.id;
-- 查询员工与领导(外连接) select u.name '员工',d.name '领导' from user u left join user d on d.id = u.dept_id;
-
-
联合查询-union,union all
对于union查询,就是把多次拆线呢的结果合并起来,形成一个新的查询结果集
SELECT 字段列表 FROM 表A UNION[ALL] SELECT 字段列表 FROM 表B; -- UNION ALL直接将查询结果合并 -- 而 UNION 则会去重 -- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
把两个select的结果集合并起来
-
子查询
-
概念:SQL语句中嵌套SELECT 语句,称为嵌套查询,又称子查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); -- 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个
-
根据子查询结果不同,分为:
-
标量子查询(子查询结果为单个值)
-- 查询研发部的所有员工信息 select id from dept where name = '研发部'; -- 根据研发部部门id,查询员工信息 select * from user where dept_id = 1; select * from user where dept_id = (select id from dept where name = '研发部');
-
列子查询(子查询结果为一列)
select * from user where dept_id in(select id from dept where name = '研发部' or name = '市场部'); --all比所有的都大,any比其中任意一个大即可
-
行子查询(子查询结果为一行)
-- 对应 select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');
-
表子查询(子查询结果为多行多列)
select * from emp where (job,salary) in (select job salary from emp where name='Eternally' or name ='Siri') -- 查询入职日期是 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;
-
-
根据子查询位置,分为:WHERE之后,FROM 之后,SELECT之后
-
-
-
六:事务
- 事务简介
- 事务操作
- 事务四大特性
- 并发事务问题
- 事务隔离级别
-
概念:事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务
-
事务操作
-
方法一:
- 查看/设置事务提交方式
-- 1 为自动提交,0为手动提交 SELECT @@autocommit; SET @@autocommit=0;
- 提交事务
COMMIT
- 回滚事务
ROLLBACK
- 具体代码
SELECT @@autocommit; set @@autocommit = 0; -- 设置为手动提交 select * from account where name = '张三'; update account set money = money - 1000 where name = '张三'; aaaaa update account set money = money + 1000 where name = '李四'; -- 提交事务 只有这样才会改变数据 commit ; -- 回滚事务(出现异常) rollback ;
-
方法二:
-
开启事务
START TRANSACTION 或 BEGIN
-
提交事务
COMMIT
-
回滚事务
ROLLBACK
-
具体代码
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 ;
-
-
-
事务四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,他对数据库中的数据的改变就是永久的
-
并发事务问题(多个事务一起执行时产生的问题)
问题 描述 脏读 一个事务读到另外一个事务还没有提交的数据 不可重复读 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影 -
事务隔离级别
隔离级别 脏读 不可重复读 幻读 Read uncommitted √ √ √ Read committed × √ √ Repeatable Read(MySQL默认) × × √ Serializable × × × -- 查看事务隔离级别 SELECT @@TRANSACTION_ISOLATION; -- 设置事务隔离级别 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL{Read uncommitted | Read committed | Repeatable Read | Serializable} -- 如果是session仅对当前客户端窗口有效,而global对所有客户端的窗口有效
事务的隔离级别越高,数据越安全,但是性能越低!