目录
一、概述
数据库:数据存储的仓库
数据库管理系统:操纵和管理数据库的大型软件
SQL:操作关系型数据库的编程语言,是一套标准
net start mysql80
net stop mysql80
二、SQL
- 单行注释:-- 注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
2.1 DDL
1. DDL——数据库操作
show databases ; //查询所有的数据库
select database(); //查询当前数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ; // 创建数据库drop database [ if exists ] 数据库名 ; //删除数据库use 数据库; //切换数据库
2.DDL——表操作:查询创建
show tables; // 查询当前数据库所有表desc 表名 ; //查看指定表结构:通过这条指令,我们可以查看到指定表的字段,字段的类型、是否可以为NULL ,是否存在默认值等信 息。show create table 表名 ; // 查询指定表的建表语句: 通过这条指令,主要是用来查看建表语句的,而有部分参数我们在创建表的时候,并未指定也会查询 到,因为这部分是数据库的默认值,如:存储引擎、字符集等。CREATE TABLE 表名 (字段1 字段 1 类型 [COMMENT 字段 1 注释 ],字段2 字段 2 类型 [COMMENT 字段 2 注释 ],字段3 字段 3 类型 [COMMENT 字段 3 注释 ],......字段n 字段 n 类型 [COMMENT 字段 n 注释 ]) [ COMMENT 表注释 ] ; //创建表结构, 注意 : [...] 内为可选参数,最后一个字段后面没有逗号
use sys;show tables;
3.数据类型
4.DDL——表操作:修改
1).添加字段
ALTER TABLE 表名 ADD 字段名 类型 ( 长度 ) [ COMMENT 注释 ] [ 约束 ];
案例: 为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
ALTER TABLE 表名 MODIFY 字段名 新数据类型 ( 长度 );
3). 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 ( 长度 ) [ COMMENT 注释 ] [ 约束 ];
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
ALTER TABLE 表名 DROP 字段名 ;
5). 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
5.DDL——表操作:删除
DROP TABLE [ IF EXISTS ] 表名 ;
2). 删除指定表, 并重新创建表
TRUNCATE TABLE 表名;
注意: 在删除表的时候,表中的全部数据也都会被删除。
2.2 DML
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
1.添加数据(INSERT)
1) 给指定字段添加数据
INSERT INTO 表名 ( 字段名 1, 字段名 2, ...) VALUES ( 值 1, 值 2, ...);
案例: 给employee表所有的字段添加数据 ;
insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');
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, ...) ;
注意事项:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
-
字符串和日期型数据应该包含在引号中。
-
插入的数据大小,应该在字段的规定范围内。
2.修改数据(UPDATE)
UPDATE 表名 SET 字段名 1 = 值 1 , 字段名 2 = 值 2 , .... [ WHERE 条件 ] ;
案例: 修改id为1的数据, 将name修改为小昭, gender修改为 女
update employee set name = '小昭',gender = '女' where id = 1;
3.删除数据(DELETE)
DELETE FROM 表名 [ WHERE 条件 ] ;
delete from employee where gender = '女';
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL即可)。
- 当进行删除全部数据操作时,datagrip会提示我们,询问是否确认删除,我们直接点Execute即可。
2.3 DQL
数据查询语言,用来查询数据库中表的记录。
1.基本语法
SELECT字段列表FROM表名列表WHERE条件列表GROUP BY分组字段列表HAVING分组后条件列表ORDER BY排序字段列表LIMIT分页参数
分为以下几个部分:
- 基本查询(不带任何条件)
- 条件查询(WHERE)
- 聚合函数(count、max、min、avg、sum)
- 分组查询(group by)
- 排序查询(order by)
- 分页查询(limit)
2.基本查询
1). 查询多个字段
SELECT 字段 1, 字段 2, 字段 3 ... FROM 表名 ;SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
SELECT 字段 1 [ AS 别名 1 ] , 字段 2 [ AS 别名 2 ] ... FROM 表名 ;SELECT 字段 1 [ 别名 1 ] , 字段 2 [ 别名 2 ] ... FROM 表名 ;
3). 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
案例:查询公司员工的上班地址有哪些(不要重复)
select distinct workaddress '工作地址' from emp;
3.条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
2). 条件
常用的逻辑运算符如下:
案例 :1.查询姓名为两个字的员工信息 _ %。
select * from emp where name like '__';
2.查询身份证号最后一位是X的员工信息 。
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
4. 聚合函数
2). 语法
SELECT 聚合函数(字段列表) FROM 表名 ;
5 .分组查询
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
2). where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having 。
- 支持多字段分组, 具体语法为 : group by columnA,columnB
案例:1.统计各个工作地址上班的男性及女性员工的数量 。
select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;
2.查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
6. 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段 1 排序方式 1 , 字段 2 排序方式 2 ;
- ASC : 升序(默认值)
- DESC: 降序
- 如果是升序, 可以不指定排序方式ASC ;
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
案例:根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;
7. 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询记录数 ;
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
案例:查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from emp limit 10,10;
8.执行顺序
2.4 DCL
1.管理用户
select * from mysql. user ;
2). 创建用户
CREATE USER ' 用户名 ' @ ' 主机名 ' IDENTIFIED BY ' 密码 ' ;
3). 修改用户密码
ALTER USER ' 用户名 ' @ ' 主机名 ' IDENTIFIED WITH mysql_native_password BY ' 新密码 ' ;
4). 删除用户
DROP USER '用户名'@'主机名' ;
- 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
-
主机名可以使用 % 通配。
-
这类SQL 开发人员操作的比较少,主要是 DBA (Database Administrator 数据库管理员)使用。
create user 'itcast'@'localhost' identified by '123456';
create user 'heima'@'%' identified by '123456';
alter user 'heima'@'%' identified with mysql_native_password by '1234'; 1
2. 权限控制
1). 查询权限
SHOW GRANTS FOR ' 用户名 ' @ ' 主机名 ' ;
GRANT 权限列表 ON 数据库名 . 表名 TO ' 用户名 ' @ ' 主机名 ' ;
REVOKE 权限列表 ON 数据库名 . 表名 FROM ' 用户名 ' @ ' 主机名 ' ;
- 多个权限之间,使用逗号分隔
- 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
show grants for 'heima'@'%';
grant all on itcast.* to 'heima'@'%';
revoke all on itcast.* from 'heima'@'%';
三.函数
3.1 字符串函数
案例:
//A. concat : 字符串拼接
select concat('Hello' , ' MySQL');
//B. lower : 全部转小写
select lower('Hello');
//C. upper : 全部转大写
select upper('Hello');
//D. lpad : 左填充
select lpad('01', 5, '-');
//E. rpad : 右填充
select rpad('01', 5, '-');
//F. trim : 去除空格
select trim(' Hello MySQL ');
//G. substring : 截取子字符串
select substring('Hello MySQL',1,5);
3.2 数值函数
// A. ceil:向上取整
select ceil(1.1);
//B. floor:向下取整
select floor(1.9);
//C. mod:取模
select mod(7,4);
//D. rand:获取随机数
select rand();
//E. round:四舍五入
select round(2.344,2);
select lpad(round(rand()*1000000 , 0), 6, '0');
3.3 日期函数
常见的日期函数如下:
//A. curdate:当前日期
select curdate();
//B. curtime:当前时间
select curtime();
//C. now:当前日期和时间
select now();
//D. YEAR , MONTH , DAY:当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());
//E. date_add:增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );
//F. datediff:获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
3.4 流程函数
//A. if
select if(false, 'Ok', 'Error');
//B. ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
//C. case when then else end 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , //其他 ----> 二线城市)
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;
案例:具体的SQL语句如下:
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.1 概述
4.2 约束演示
案例需求: 根据需求,完成表结构的创建。需求如下:
对应的建表语句为:
CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
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 '性别'
);
4.3 外键约束
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);
2). 删除外键
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 ;
我们发现,原来在子表中dept_id值为1的记录,现在也变为6了,这就是cascade级联的效果。
B. 删除父表id为6的记录
我们发现,父表的数据删除成功了,但是子表中关联的记录也被级联删除了。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;
五. 多表查询
5.1 多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
1.一对多
- 案例: 部门与员工的关系
- 关系: 一个部门对应多个员工,一个员工对应一个部门
- 实现: 在多的一方建立外键,指向一的一方的主键
2. 多对多
- 案例: 学生 与 课程的关系
- 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
3 .一对一
- 案例: 用户 与 用户详情的关系
- 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
5.2 多表查询分类
1.内连接
SELECT 字段列表 FROM 表 1 , 表 2 WHERE 条件 ... ;
SELECT 字段列表 FROM 表 1 [ INNER ] JOIN 表 2 ON 连接条件 ... ;
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
2.外连接
SELECT 字段列表 FROM 表 1 LEFT [ OUTER ] JOIN 表 2 ON 条件 ... ;
SELECT 字段列表 FROM 表 1 RIGHT [ OUTER ] JOIN 表 2 ON 条件 ... ;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
3. 自连接
1. 自连接查询
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
select a.name , b.name from emp a , emp b where a.managerid = b.id;
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表 A ...UNION [ ALL ]SELECT 字段列表 FROM 表 B ....;
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
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;
4. 子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
3) 标量子查询
select id from dept where name = '销售部';
select * from emp where dept_id = (select id from dept where name = '销售部');
select entrydate from emp where name = '方东白';
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
4) 列子查询
select id from dept where name = '销售部' or name = '市场部'; 1
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
select salary from emp where dept_id = (select id from dept where name = '财务部');
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
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 = '研发部') );
5) 行子查询
select salary, managerid from emp where name = '张无忌';
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
6)表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
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 ;
六、事务
6.1 事务简介
6.2 事务操作
1 .未控制事务
-- 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 = '李四';
2). 测试异常情况
-- 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 = '李四';
SELECT @@autocommit ;
SET @@autocommit = 0 ; #把事务设置成手动提交
2). 提交事务
COMMIT;
ROLLBACK;
START TRANSACTION 或 BEGIN ;
COMMIT;
ROLLBACK;
-- 开启事务
start transaction
-- 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;
6.3 事务四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立 环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
6.4 并发事务问题
6.5 事务隔离级别
SELECT @@TRANSACTION_ISOLATION
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }#SESSION 只设置本窗口,GLOBAL设置全部窗口