您现在的位置是：首页 > 数据库

当前栏目

数据库的使用（SQL）

数据库 SQL 使用

2023-09-11 14:22:31 时间

SQL简介

通用语法

SQL语句可以单行或多行书写，一分号结尾
MySQL数据库的SQL语句不去分大小写，关键字建议使用大写
注释
- 单行注释：-- 注释内容或 # 注释内容（MySQL特有）
- 多行注释： /* 注释内容*/

SQL分类

DDL 简单理解：操作数据库，表
DML ：对表中数据进行增删改
DQL ：数据查询语句
DCL : 对数据库进行权限控制

一、操作数据库

1.1查询

查询所有数据库

-- 查询所有数据库
show databases;

1.2创建数据库

创建数据库

-- 创建数据库
create database itzh;

创建数据库（判断，如果不存在则创建）

-- 创建数据库 ，判断
create databases if not exists itzh;

1.3 删除数据库

删除数据库

DROP DATABASE 数据库名称;
-- 删除数据库
drop databases iszh;

删除数据库（判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称;
-- 删除数据库，判断是否从在
drop databases if exists itzh;

1.4 使用数据库

使用数据库

USE 数据库名称;
-- 使用数据库
use itzh;

查看但前使用的数据库

SELECT DATABASE();
-- 查看当前使用的数据库
select database();

二、 DDL：操作表

操作数据，对应的数据表进行增、删、改、查

2.1查询表

查询当前数据库表名称
```
show tables ;
```

查询表结构

desc 表名;
-- 查看 表结构
desc zhit;

2.1创建表

创建表

create table 表名(
	字段名1 数据类型1,
	字段名2 数据类型2，
	……
	字段名n 数据类型n
);
	|注意：最后一行，不能加逗号

表模型

请添加图片描述

2.2数据类型

数值

tingint:  小整数，占一个字节
int：	  大整数，占四个字节
	eg: age int
double:		浮点类型
	使用格式： 字段名 double(总长度，小数点后保留俩位)
	eg: score double(5,2)

日期

date : 		日期值 。只包含年月日
	eg： birthday date
datetime：	混合日期和时间值，包含年月日时分秒

字符串

char : 		定长字符串
	优点： 存储性能高
	缺点： 浪费空间
	eg：name char（10） 如果存储的数据字符不足十个，
varchar ：	变长字符串
	优点： 节约空间
	缺点：存储性能低

2.3删除表

删除表

DROP TABLE 表名;
-- 将表名为student的表删除
drop table student ;

删除表时，判断表时否存在

DROP TABLE IF EXISTS 表名 ;
-- 判断是否有名为student的表， 有则删除
drop table if exists student;

2.4 修改表

修改表名 rename to

ALTER TABLE 表名 RENAME TO 新表名;
-- 将表名student 修改为 stu
alter table student rename to stu;

添加一列 add

ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列 address，给字段类型是varchar(50)
alter table stu addres varchar(50);

修改数据类型 modify

ALTER TABLE 表名 MODIFY 列名 新数据类型
-- 将stu表中address字段的类型给为char(50)
alter table stu modify address char(50);

修改列和数据类型 change

ALTER TABLE 表名 	CHANGE 列名 新列名 新数据类型
-- 将stu表中的address 字段名给为addr ，类型改为 varchar(50)
alter table stu change address addr varchar(50);

删除列 drop

ALTER TABLE 表名 DROP 列名;
-- 将stu 表中的addr字段，删除
alter table stu drop addr;

三、DML

DML主要书对数据进行，增、删、改

3.1添加数据

给指定列添加数据

INSERT INTO 表名(列名1，列名2……) VALUES(值1，值2……);
-- 给stu表，指定列添加数据
insert into stu (id ,name) values (1,'张三');

给全部列添加数据

INSERT INTO 表名 VALUES (值1，值2……);
-- 给所有列添加数据
insert into stu (id, name ,sex ,birthday,score,email,tel,STATUS)
VALUES (2, '李四'，'男'，'1999-11-11',88.88,'lisi@itcat.cn' ,'1388888888',1);
-- 给所有列添加数据，列名的列表可以省略
insert into stu values(2,'李四'，'男'，'1999-11-11','lif@itzh.cn','17699990000',1);

批量添加数据

insert into 表名(列名1，列名2…… ) values (值1，值2……),(值1，值2……)……;
insert into 表名 values (值1，值2……)，(值1，值2……)，……;
-- 批量添加数据
insert into stu values
(2,'李四'，'男'，'1999-11-11','lif@itzh.cn','17699990000',1),
(3,'李四'，'男'，'1999-11-11','lif@itzh.cn','17699990000',1),
(3,'李四'，'男'，'1999-11-11','lif@itzh.cn','17699990000',1);

3.2修改数据

修改表数据

update 表名 set 列名1= 值1， 列名2=值2，…… [where 条件];
		/*
			注意： 1.修改语句，如果不加条件，则将所有数据都修改
				  2.像上面的语句的中括号， 表示写的SQL是可以省略的
		*/
-- 将张三的性别改为 女
update stu set sex = '女' where name='张三';
-- 将张三的生日给为2020-5-20，分数改为99
update stu set birthday = '2020-5-20', score = 99 where name='张三';

3.3删除数据

删除数据

delete from 表名 [where 条件]；
-- 删除张三的记录
delete from stu where name='张三'；
-- 删除stu 中所有的数据
delete from stu;

四、DQL

完整语法

select 
	字段列表
from 
	表名列表
where 
	条件列表
group by 
	分组字段
having 
	分足字段后条件 
order by 
 	排序字段
limit 
	分页限定

演示

-- 删除stu表
drop table if exists stu ;

-- 创建 stu 表
create table stu (
	id int , -- 编号
    name varchar(20), -- 姓名
    age int，-- 年龄
    sex varchar(5), -- 性别
    address varchar(100), -- 地址
    math double(5,2), -- 数学成绩
    english double(5,2), -- 英语成绩
    hire_date date -- 入学的时间
);

-- 添加数据
insert into stu (id ,name,age ,sex, address,math,english,hire_date) values 
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

4.1 基础查询

查询多个字段

select 字段列表 from 表名;
-- 查询所有数据
select * from stu ；

去除重复的记录

select distinct 字段列表 from 表名;
-- 去除重复记录
select distinct address from stu;

起别名

as：as 也可以省略
-- 查询起别名
select name,math as 数学成绩， english as 英语成绩 from stu;
select name,math 数学成绩， english 英语成绩 from stu  ;

4.2 条件查询

语法

select 字段列表 from 表名 where 条件列表

条件

-- 查询年龄大于20岁的学员信息
select * from stu where age > 20;
-- 查询年龄大于等于20岁的学员信息
select * from stu where age >=20;
-- 查询年龄大于等于20岁，并且年龄小于 30 岁的的学员信息
select * from stu where age >=20 and age<=30;
select * from stu where age >=20 && age<=30;

上面语句中得 && 和 and 都表示并且的意思，建议使用 and

也可以使用 between …and来实现上面的需求

 -- 查询英语成绩为null的学员
 select * from stu where englist is null
 select * from stu where englist is not null

模糊查询

模糊查询使用，like关键字，可以使用通配符进行占位

(1) _ ：代表单个任意字符

(2) % : 代表任意数字符

-- 查询姓'马'的学员信息
select * from stu where name like '马%';
-- 查询第二个字是'花' 的学员的信息
select * from stu where name like '_花%'
-- 查询名字中包含 '德'的学员信息
select * from stu where name like '%德%'

4.3排序查询

语法
```
select 字段列表 from 表名 order by 排序字段名1[排序方式1]，排序字段名2，[排序方式2]…;
```
排序方式有俩种，
- ASE : 升序(默认)
- DESC：降序排序
  
  注意：如果有多个排序条件，当前面条件值一样时，才会根据第二条件进行排序

练习

-- 查询学生信息，按照年龄升序排序
select * from stu order by age;
-- 查询学生信息，按照数学成绩降序排序
select * from stu order by math desc;
-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，在按照英语成绩升序排列
select * from stu order by math desc, englist asc;

4.4聚合函数

== 将一列数据作为一个整体，进行纵向计算。 ==

请添加图片描述

语法

select 聚合函数名(列名) from 表名；

注意： null 不参与聚合函数运算

练习

-- 统计班级一共有多少学生
select count(id) from stu ;
select count(englist) from stu;

上面，如果给字段某一行的值为null ，将不会被统计，

所以用count(*), 来实现，

‘星号’ 表示所有字段数据吗一行中，也不能可所有数据都为空，所以建议使用count(*)

-- 查询数学成绩的最高分
select max(math) from stu;
-- 查询数学成绩的最低分
select min(math) from stu;
-- 查询数学总成绩的
select sum(math) from stu;
-- 查询数学成绩色平均分
select avg(math) from stu;

4.5 分组查询

语法

select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

练习

-- 查询男同学和女同学各自的 数学成绩的平均分
select sex, avg(math) from stu group by sex;
-- 查询男同学和女同学各自的数学成绩平均分，以及各自人数
select sex,avg(math), count(*) from stu group by sex;
-- 查询男同学和女同学各自的数学成绩平均分，以及各自的人数，要求：分数不低于70 的不参与分组
select sex， avg(math), count(*) from stu where math > 70 group by sex;

where 和 having 区别：

执行时机不一样： where是分组之前进行限定，不满足where条件，则不参与分组

having是分组织后对结果进行过滤。

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

4.6分页查询

语法

select 字段列表 from 表名 limit 起始索引 ，查询条目;

注意：上述语句的起始索引是从0开始的

练习

-- 从零开始查询，查询3 条数据
select * from stu limit 0,3;
-- 每页显示3条数据，查询第一页数据；
select * from stu limit 0 , 3;
-- 每页显示3条数据，查询第三页数据
select * from stu limit 6 , 3;

起始索引 = (当前页面 - 1) * 每页显示的条数

五、约束

5.1概念

约束：作用于表中列上的规则，用于限制加入表的数据

5.2分类

5.2.1非空约束

概念

关键字： not null

非空约束：保证所有数据不能有null

语法

添加约束

-- 创建表示添加非空约束
create table 表名 (
	列名 数据类型 not null，
    ……
);

-- 建完 表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;

删除约束

alter table 表名 modify 字段名，数据类型;

5.2.2 唯一约束

概念

关键字： unique

唯一约束：用于保证列中数据各不相同

语法

添加约束

-- 创建表的时候添加
create table 表名(
	列名 数据类型 unique [auto_incrment],	-- auto_incrment:当不指定的时候自动添加
    ……
);

create table 表名(
	列名 数据类型，
    ……
    [counstraint] [约束名称]  unique(列名)
);

-- 建完表后添加	唯一约束
alter table 表名 modify 字段名 数据类型 unique;

删除约束

alter table 表名 drop index 字段名;

5.2.3 主键约束

概念

关键字： primary key

主键约束：一张表只能有一个主键，

是一行数据的唯一标识，要求非空唯一

语法

添加约束

-- 创建表时添加主键约束
create table 表名(
	列名 数据类型 primary key [auto_increment],
    ……
);

create table 表名(
	列名 数据类型，
    [counstraint] [约束名称]  primary key (列名)
);

-- 建完表后添加主键约束
alter table 表名 add primary key(字段名);

删除约束
```
alter table 表名 drop primary key;
```

5.2.4 默认约束

概念

关键字： default

默认约束：保存数据时，未指定值采用默认值

语法

添加约束

-- 创建表示添加默认约束
create table 表名 (
	列名 数据类型 default 默认值，
    ……
);

-- 建完表后添加默认约束
alter table 表名 alter 列名 set default 默认值;

删除约束

alter table 表名 alter 列名 drop default;

5.2.5外键约束

概念

关键字： foreign key

外键约束：用来让俩个表的数据之间建立链接，保证数据的一致性和完整性

语法

添加外键约束

-- 创建表时添加外键约束
create table 表名(
	列名 数据类型，
    [constraint] [外键名称] foreign key (外键列名)  references 主表(主表列名)
);

-- 建完表后添加外键约束
alter table 表名 add coustraint 外键名称  foreign key (外键字段名称) references 主表名称(主表列名);

删除外键约束

alter table 表名 drop foreign key 外键名称;

约束案例

根据需求，为表添加合适的约束

-- 员工表
CREATE TABLE emp (
	id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

验证主键约束，非空且唯一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据，如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

从上面结果可以看到，1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

验证非空约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

可以看到，ename 字段的非空约束生效了。

验证唯一约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

从结果可以看到，ename 字段的唯一约束生效了。

验证默认约束

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

执行完上面语句后查询表中数据，如下图可以看到王五这条数据的bonus列就有了默认值0。

注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。

如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完上面语句后查询表中数据，如下图可以看到赵六这条数据的bonus列的值是null。

验证自动增长： auto_increment 当列是数字类型并且唯一约束

重新创建 emp 表，并给id列添加自动增长

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

根据上述语法创建员工表和部门表，并添加上外键约束：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

六、数据库设计

6.1 设计简介

数据库设计概念
- 数据库设计就是业务系统个具体需求，结合我们所需要的DBMS，为这个业务系统构造出数据存储模式
- 建立数据库中得表结构以及表与表直接的关联关系
- 有哪些表？表里有那些字段，表与表之间有什么关系
数据库设计步骤
- 需求分析
- 逻辑分析
- 物理设计
- 维护设计

6.2表关系

6.2.1一对多

一对多
- 一个部门对应对个员工，一个员工对应多个部门
实现方式
- 再多的一方建立外键，指向一的一方的主键

请添加图片描述

建立语句

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

6.2.2多对多

多对多
- 一个商品对应多个订单，一个订单对应多个商品
实现方式
- 建立第三张中间表，中间表最少包含俩个外键，分别关联俩方的外键
案例

请添加图片描述

6.2.3一对一

一对一
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表
  
  将不经常用使用的字段放另一张表，用于性能提升
实现方式
- 在任意一方的键入外键，关联另一方主键，并且设置外键的唯一性(unigue)
案例

请添加图片描述

6.3 数据库设计案例

七、多表查询

7.1 连接查询

7.1.1 内连接

语法

-- 隐式内连接
select 字段列表 from 表1,表2 … where 条件;

-- 案例
select * from emp ,dept where emp.dep_id = dept.did;

-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;
-- 案例
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上述语句inner可以省略
select * from emp join dept on emp.dep_id = dept.id;

内连接，相当于查询 A B交集数据

请添加图片描述

案例

-- 案例  隐式内连接
select * from emp ,dept where emp.dep_id = dept.did;


-- 案例	显示内连接
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上述语句inner可以省略
select * from emp join dept on emp.dep_id = dept.id;

7.1.2 外连接

语法

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接：相当于查询A表所有数据和交集部分数据

右外连接：相当于查询B表所有数据和交集部分数据

请添加图片描述

左外连接

-- 查询emp表所有的数据和对应的部门信息(左外连接)
select * from emp left join dept on emp.dep_id = dept.did;

右外连接

-- 查询dept表所有数据和对应的员工信息(右外连接)
select * from emp right join dept on emp.dep_id = dept.dsid;

7.2子查询

概念

子查询：查询中嵌套查询语句，称嵌套查询为子查询

语法

/*
	需求： 查询工资高于猪八戒的员工信息
		第一步：先查出猪八戒的工资
		第二步：查询工资高于猪八戒的员工信息
*/

-- 子查询
select * from emp where salary > (select salary from emp where name = '猪八戒');

子查询根据查询结果不同，作用不同
- 子查询语句的结果：单行单列，子查询语句做为：条件值，使用= != > < 等条件语句
- 子查询语句的结果：多行单列，子查询语句做为：条件值，使用 in等关键字进行判断
- 子查询语句的结果：多行多列，子查询语句做为：虚拟表，

案例

环境准备：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
				
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp,
	job
WHERE
	emp.job_id = job.id;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp
INNER JOIN job ON emp.job_id = job.id;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp,
	job,
	dept
WHERE
	emp.job_id = job.id
	and dept.id = emp.dept_id
;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id

查询员工姓名，工资，工资等级

/*
	分析：
		1. 员工姓名，工资 信息在emp 员工表中
		2. 工资等级 信息在 salarygrade 工资等级表中
		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.ename,
	emp.salary,
	t2.*
FROM
	emp,
	salarygrade t2
WHERE
	emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
		6. 工资等级 信息在 salarygrade 工资等级表中
		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	t2.grade
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数

/*
	分析：
		1. 部门编号、部门名称、部门位置 来自于部门 dept 表
		2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
		3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t1.count
FROM
	dept,
	(
		SELECT
			dept_id,
			count(*) count
		FROM
			emp
		GROUP BY
			dept_id
	) t1
WHERE
	dept.id = t1.dept_id

八、事务

8.1 概念

数据库的事务：是一种机制、一个操作序列，包含了数据库操作命令

事务：把所有命令作为一个整体一起向系统提交或撤销操作请求 [ 要么同时成功，要么同时失败]

事务：是一个不可分割的工作逻辑单元

请添加图片描述

8.2 语法

开启事务
```
START TRANSACTION;
或者  
BEGIN;	
```
提交事务
```
commit;
```
回滚事务
```
rollback;
```

8.3代码

环境准备

DROP TABLE IF EXISTS account;

-- 创建账户表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name varchar(10),
	money double(10,2)
);

-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);

不加事务演示问题

-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。

添加事务sql如下：

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

8.4 四大特性

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：
SELECT @@autocommit;
查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
set @@autocommit = 0;

猜你喜欢

【FAQ】HarmonyOS 从API8开始不再支持java作为开发语言，从开发角度看该如何选择
C# Winform 服务器和多个客户端通讯
【3.3】mysql链接服务器，Federated存储引擎实现远程表，相当于sql server的连接服务器
python 1 cmd进入工作
Matlab中绘制日期和持续时间图
简单来说云服务器是什么？有哪些品牌？
Java调用 webService 接口报错
gitlab环境搭建
System Properties 系统环境变量
【python】or【php】网页中字符编码转换，将反斜杠u u字符串转为unicode/utf8

相关主题

网站数据库
SQL修改数据库
数据库规范
数据库的回滚
牛B的大数据库
sqlite数据库使用
SQLServer数据库优化
mysql---备份数据库
Oracle数据库导出

当前栏目

数据库的使用（SQL）

SQL简介

通用语法

SQL分类

一、操作数据库

1.1查询

1.2创建数据库

1.3 删除数据库

1.4 使用数据库

二、 DDL：操作表

操作数据，对应的数据表进行 增、删、改、查

2.1查询表

2.1创建表

2.2数据类型

2.3删除表

2.4 修改表

三、DML

3.1添加数据

3.2修改数据

3.3删除数据

四、DQL

4.1 基础查询

4.2 条件查询

4.3排序查询

4.4聚合函数

4.5 分组查询

4.6分页查询

五、约束

5.1概念

5.2分类

5.2.1非空约束

5.2.2 唯一约束

5.2.3 主键约束

5.2.4 默认约束

5.2.5外键约束

约束案例

六、数据库设计

6.1 设计简介

6.2表关系

6.2.1一对多

6.2.2多对多

6.2.3一对一

6.3 数据库设计案例

七、多表查询

7.1 连接查询

7.1.1 内连接

7.1.2 外连接

7.2子查询

案例

八、事务

8.1 概念

8.2 语法

8.3代码

8.4 四大特性

相关文章

操作数据，对应的数据表进行增、删、改、查