MySQL

最新推荐文章于 2023-05-07 00:10:37 发布

给阿姨倒杯卡布奇诺咖啡

最新推荐文章于 2023-05-07 00:10:37 发布

阅读量242

点赞数 1

本文链接：https://blog.csdn.net/qq_45954910/article/details/111305143

版权

1 SQL分类

DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库，表，列。关键字：create，drop,alter 等
DML(Data Manipulation Languange)数据库操作语言

用来对数据库中表的数据进行增删改查，关键字：insert，delete，update 等

DQL(Date Query language)数据库查询语言

用来查询数据库中表的记录（数据），关键字：select，where 等
DCl(Date Control Language)数据库控制语言（了解）

用来定义数据库的访问权限和安全级别，及创建用户。关键字：crant,revoke 等

1.1 DDL:操作数据库，表

1.1.1操作数据库：CRUD

C(Create):创建

创建数据库

#1 创建数据库
create database 数据库名称;

#2 创建数据库之前先判断是否存在
create database if not exists 数据库名称;

#3 创建数据库，设置字符集
create database 数据库名称 character set utf8/gbk;

#4 创建db4数据库，判断是否存在，并指定字符集为gbk
create database if not exists db4 character set gbk;

R(Retrieve):查询
- 查询所有数据库的名称
```
show databases;
```
- 查询某个数据库的字符集：查询某个数据库的创建语句
```
show create database 数据库名称;
```

U(Update):修改

修改数据库的字符集

# 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

D(Delete):删除

删除数据库

#1 删除数据库
drop database 数据库名称;
#2 判断数据库是否存在，存在再删除
drop database if exists 数据库名称;

使用数据库

查询当前正在使用的数据库名称

# 查询当前正在使用的数据库名称
select database();

#使用数据库
use 数据库名称;

1.1.2 操作表

C(Create):创建

#1 语法
	create table 表名(
    	列名1 数据类型1;
        列名2 数据类型2;
        列名3 数据类型3;
        ......
        列名n 数据类型n #最后一列不需要逗号(,) 
    );
#2 数据类型
	1 int:整数类型
    2 double:小数类型
    	score double(5,2),#表示总共有5位，保留2位
    3 date:日期，只包含年月日，yyy-mm-dd
    4 datetime:日期，包含年月日时分秒 yyy-mm-dd-HH:mm:ss
    5 timestamp:日期时间戳类型，包含年月日时分秒 yyy-mm-dd-HH:mm:ss
    	区别：timestamp 如果将来不给这个字段赋值，或赋值为null,则默认使用当前的系统时间，来自动赋值
    6 varchar():字符串,括号中代表最大字符
    
#3 创建一张表
create TABLE student(
	id int,
	name VARCHAR(32),
	age int,
	score DOUBLE(4,1),
	birthday DATE(expr),
	insert_time TIMESTAMP
);
#4 复制一张表
create table 创建的表 like 被复制的名称;

R(Retrieve):查询

#1 查询某个数据库中所有表名称
	show tables;
#2 查询表结构
	desc 表名;

U(Update):修改

# 修改表名
	alter table 表名 rename to 新的表名;
# 修改表的字符集
	alter table 表名 character set 字符集名称;
# 添加一列
	alter table 表名 add 列名 数据类型;
	eg:向student表中添加一列性别(gender)
		alter table student add gender varchar(32);
# 修改列名称 类型
	#方式一：alter table 表名 change 列名 新列明 数据类型;
	#方式二：alter table 表名 modify 列名 新数据类型
	eg:将student表中的gender列修改为sex 
		alter table student change gender sex varchar(10);
		alter table student modify sex varchar(10);
# 删除列
	alter table 表名 drop 列名;

D(Delete):删除

#1 删除表
	drop table if exists 表名;
	drop table 表名;

1.2DML：增删改表中的数据

1.2.1添加数据：

语法：

insert into 表名(列名1，列名2，列名3。。列名n) values(值1，值2。。。值n);

注意：

1，列名和值要一一对应。

2，如果表名后，不定义列名，则默认给所有列添加值
```
insert into 表名 values(值1，值2。。。值n);
```
3，除了数字类型，其他类型需要引号引起来

1.2.2删除数据：

#1 语法格式
delete from 表名 [where 条件];

#2 注意
	1 如果不加条件，则删除表中所有记录。
	2 如果要删除所有记录
		1 delete from 表名; --不推荐使用，有多少条记录就会执行多少次删除操作
		2 truncate table 表名; --推荐使用，效率更高 先删除一张表，再创建一张一模一样的表

1.2.3修改数据：

#1 语法格式
update 表名 set 列名1 = 值1，列名2 = 值2 .... 列名n = 值n where 条件判断;

#2 注意
	1 如果不加条件，则会修改表中所有信息

1.2.4查询数据：

#1 语法格式
select * from 表名 --查询表中所有信息

1.3DQL 查询表中的记录

1.3.1 基础语法

# 语法格式
select 
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

1.3.2 基础查询

#1 去除重复关键字  distinct
	# eg:查询地区
	select distinct address from student;
#2 计算列
	#1 ifnull(表达式1，表达式2)：null参与的运算，计算结果都为null
		表达式1：代表哪个字段需要判断是否为null
		表达式2：替换可能为null的值
#3 起别名
	as as也可以省略不写

1.3.3 条件查询

#1 where子句后跟的条件
#2 运算符
	1 between。。。。and
        #eg:查询年龄在10 到20 之间的人数
        select * from stu where age between 10 and 20;
	2 in
		#eg:查询年龄为10，11，12的人
		select * from stu where age in (10,11,12);
	3 like
	4 is null
		#eg:查询english成绩为空的人
 		select * from stu where english is null; 
	5 is not null

模糊查询 like

#1 查询所有姓 马 的人
select * from student where name like "马%";

#2  查询第二个字是 化 的人
select * from student where name like "_化%";

#3 查询姓名是三个字的人
select * from student where name like "___";

#4 查询姓名中包含 马 的人
select * from student where name like "%马%";

1.3.4 查询语句

1 排序查询

# 语法格式 order by 子句
	order by 排序字段 排序方式
	#eg:查询表中学生数学成绩按升序来排序，
	select * from student ORDER BY math asc;#默认情况是desc 降序，asc 是升序
	
	#eg:如果数学成绩相同，比较英语成绩
	select * from student ORDER BY math asc,english asc;
# 排序可以含有多个条件
order by 排序字段1 排序方式1,排序字段2 排序方式2,....排序字段n 排序方式n;

2 聚合函数

定义：将一列数据作为一个整体，进行纵向的计算

注意：聚合函数的计算，排除null值

count:计算个数

select COUNT(name) 总和 from student;

max: 计算最大值
min:计算最小值
sum:计算和
avg:计算平均值

3 分组查询

语法：group by 分组字段；

注意：

#1 分组之后查询的字段：分组字段，聚合函数
select sex,avg(math),count(id) from student group by sex;

#2 查询成绩大于60的再参与分组
select sex,AVG(math),COUNT(id) from student where math>60 group by sex;

#3 查询分组后人数大于2位的平均分
select sex,AVG(math),COUNT(id) from student where math>60 group by sex having count(id)>2;

#4 where 和 having 的区别
	1：where 在分组之前进行限定，如果不满足条件则不参与分组。having在分组之后进行限定
	2：where 后不可以跟聚合函数，having可以进行聚合函数的判断

4 分页查询

分页查询语法：limit 开始的索引，每条查询的条数
公式：开始的索引 = （当前的页数 -1）* 每页显示的条数
```
#1 查询第一页
select * from student limit 0,3;
```
分页查询操作是一个Mysql的 “方言”

1.4 条件约束

对表中的数据进行限定，保证表中的正确性，有效性，完整性

1.4.1 条件约束分类

1.4.1.1主键约束

函数名称：primary key 含义：非空且唯一
注意事项：
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识

#1 方式一，创建表时创建主键约束
create table stu(
	id int primary key,
    name varchar(32)
);

# 删除主键
alter table stu drop primary key;

#2 方式二：创建表后添加主键约束条件
alter table stu modify id int primary key;

1.4.1.2 非空约束

函数名称：not null

# 方式一：在创建表中添加约束
# eg:创建一张表，stu 设置name为非空
create table stu(

	id int,
	name varchar(32) not null
);

#2 删除表的约束条件
alter table stu modify name varchar(32);

#方式二：创建表之后添加约束条件非空
alter table stu modify name varchar(32) not null;

1.4.1.3 唯一约束

函数名称：unique – 表值不能重复

# 方式1 在创建表时进行约束条件 unique
create table stu(
	id int unique,
    name varchar(32)
);

# 删除约束条件
alter table stu drop index id int;

# 唯一约束中限定表中列的元素可以为多个null

# 方式二 在创建表之后添加约束条件
alter table stu modify id int unique;

1.4.1.4 外键约束

函数名称：foreign key
在创建表时，可以添加外键,让表与表产生关系，从而保证数据的正确性

# 语法
create table 表名(
    ......
	外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

#案例，创建两张表，一张存放部门编号和部门名称，另一张表存放员工表，两行表相互关联
create table department(
	id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);

#创建员工表，让此表和部门表产生关联
create table employee(
	id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,
    #外键约束条件
    constraint emp_dep_fk foreign key (dep_id) references department(id)
);

删除外键

# 删除外键格式
alter table 存在外键的表 drop foreign key 外键名称;
#如：
alter table employee drop foreign key emp_dep_fk;

在创建表后添加外键

alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id)

1.4.1.5 级联操作

添加级联操作

#语法：
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;

级联操作的分类
- 级联更新：on update cascade
- 级联删除：on delete cascade

1.4.1.6 自动增长

概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长

在创建表时，添加主键约束，并且完成主键自动增长

create table stu(
	id int primary key auto_increment,
    name varchar(32)
);

删除自动增长
```
alter table stu modify id int;
```

添加自动增长

alter table stu modify id int auto_increment;

2 数据库的设计

2.1 多表之间的关系

2.1.1 一对一(了解)

如：人和身份证

一张身份证只能对应一个人，一个人只能有一张身份证

2.1.2 一对多（多对一）

如：部门和员工

一个部门有多个员工，一个员工只能对应一个部门

2.1.3 多对多

如：学生和课程

一个学生可以选择多个课程，一个课程可以被多名学生所选择

2.2 实现方式

2.1 一对多（多对一）

如：部门和员工

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W1s4YHzQ-1608130803815)(C:\Users\xgh19\AppData\Roaming\Typora\typora-user-images\image-20201205155013449.png)]

2.2 多对多

如：学生选课

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMFI1siu-1608130803819)(C:\Users\xgh19\AppData\Roaming\Typora\typora-user-images\image-20201205154914805.png)]

3 数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。要遵循后面的范式，首先要遵循前面的范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
分类：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ojNwkISB-1608130803821)(C:\Users\xgh19\AppData\Roaming\Typora\typora-user-images\image-20201206153845896.png)]

4 数据库的备份与还原

4.1 通过命令行

语法
- 备份： mysqldump -u用户名 -p密码将要备份的数据库> 保存的路径
```
# 备份
mysqldump -uroot -pcmsxgh db1 > d://a.sql
```
- 还原：
  - 登录数据库
  - 创建数据库
  - 使用数据库
  - 执行文件。source 文件路径

4.2 可以通过图形化界面来实现

5 多表查询

准备数据

# 创建数据库

create table dept(
	id int primary key auto_increment,
	name varchar(20)
);

#添加数据
insert into dept (name) values("开发部"),("市场部"),("财务部");

# 创建数据库emp
create table emp(
	id int primary key auto_increment,
	name varchar(10),
	gender char(1),
	salary double,
	join_date date,
	dept_id int,
	foreign key (dept_id) references dept(id)
);

insert into emp (name,gender,salary,join_date,dept_id) values ('孙悟空','男',7200'2016-02-24',1);

insert into emp (name,gender,salary,join_date,dept_id) values ('猪八戒','男',4500'2018-01-14',2);


insert into emp (name,gender,salary,join_date,dept_id) values ('蜘蛛精','女',12000'2019-02-10',2);


insert into emp (name,gender,salary,join_date,dept_id) values ('唐生','男',5000'2016-02-14',3);

笛卡尔积：
- 有两个集合A，B,取这两个集合的所有组成情况
- 要完成多表查询，需要消除无用的数据

5.1多表查询的分类

5.1.1 内连接方式

隐式内连接:使用where条件消除无用数据

#1 查询所有员工的信息
select * from emp,dept where emp.dept_id = dept.id; 

#2 查询员工的姓名，性别，部门
select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = dept.id; 

#3 通用方式 起别名
select 
	e1.name,e1.gender,
	d1.`name`
FROM
	emp e1,
	dept d1
where 
	e1.dept_id = d1.id;

显式内连接

语法：select 字段列表 from 表名1 inner join 表名2 on 条件；

#1 外连接
SELECT * from emp INNER JOIN dept ON emp.dept_id = dept.id;

#2 查询单个语句
SELECT emp.`name`,emp.salary,dept.`name` from emp INNER JOIN dept ON emp.dept_id = dept.id;

#3 通用格式方案
select e.`name`,e.salary,d.`name`
from 
	emp e
INNER JOIN
  dept d
ON
	e.dept_id = d.id;

5.1.2 外连接查询

左外连接

#语法：
select 字段列表 from 表1 left [outer] join 表2 on 条件；

#如：
select * from emp LEFT JOIN dept ON emp.dept_id = dept.id;

查询的是左表所有数据以及其交集部分

右外连接

#语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；

#如
select * from emp right JOIN dept ON emp.dept_id = dept.id;

查询的是右表所有数据以及其交集部分

5.1.3 子查询

概念：查询中嵌套查询，称嵌套查询为子查询

# 如：查询员工表中工资最高的
select * from emp where salary = (select max(salary) from emp);

5.1.3 子查询不同情况

5.1.3.1 子查询的结果是单行单列的

# 如：查询员工的工资大于平均工资的员工
SELECT AVG(salary) FROM emp;

select * from emp where salary > (SELECT AVG(salary) FROM emp);

5.1.3.2 子查询的结果是多行单列的

子查询可以作为条件，使用运算符 in 来判断条件

# 查询所有财务部和市场部的员工信息
select id from dept where name = "财务部" or name = "市场部";

# 两者相互结合得到答案
SELECT * from emp where emp.dept_id in (select id from dept where name = "财务部" or name = "市场部");

5.1.3.3 子查询的结果是多行多列的

子查询可以作为一张虚拟表参与查询

# 查询员工的工资大于10000的员工
select * from emp where salary >10000;

select d1.`name`,d2.* from dept d1,(select * from emp where salary >10000) d2 where d1.id = d2.dept_id;

# 可以使用普通的内连接来实现
select * from dept d1,emp d2 WHERE d1.id = d2.dept_id AND salary >10000;

5.2 多表查询练习

#准备数据
#创建部门表
CREATE table dept(
	id int PRIMARY KEY, 
	dname VARCHAR(50),
	loc VARCHAR(50)
);
#插入数据
insert into dept (id,dname,loc) values(10,'教研部','北京');
insert into dept (id,dname,loc) values(20,'学工部','上海');
insert into dept (id,dname,loc) values(30,'销售部','广州');
insert into dept (id,dname,loc) values(40,'财务部','深圳');

#创建职位表
create table job(
	id int PRIMARY key,
	jname VARCHAR(20),
	description VARCHAR(50)
);

#插入数据
insert into job(id,jname,description) values (1,'董事长','管理公司，接单');
insert into job(id,jname,description) values (2,'经理','管理部门员工');
insert into job(id,jname,description) values (3,'销售员','向客人推销产品');
insert into job(id,jname,description) values (4,'文员','使用办公软件');

#创建员工表
create table emp(
	id int primary key,
	ename VARCHAR(50),
	job_id int,
	mgr int,
	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_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);


insert into emp(id,ename,job_id,mgr,salary,bonus,dept_id) values (1002,'卢俊义',3,1006,'16000.00','3000.00',30),
(1003,'林冲',3,1006,'12500.00','5000.00',30),

(1004,'唐僧',2,1009,'29750.00',null,20),

(1005,'李逵',4,1006,'12500.00','14000.00',30),

(1006,'宋江',2,1009,'28500.00',null,30),

(1007,'刘备',2,1009,'24500.00',null,10),

(1008,'猪八戒',4,1004,'30000.00',NULL,10),

(1009,'罗贯中',1,null,'50000.00','0.0',30);

#创建工资等级表
create table salarygrade(
	grade int PRIMARY KEY,
	losalary int,
	hisalary int
);

insert into salarygrade(grade,losalary,hisalary) values (1,7000,12000),
(2,7000,14000),
(3,12010,20000),
(4,20010,70000),
(5,30010,90000);

#案例1 查询所有员工信息，查询员工编号，员工姓名，工资，职位名称，职务描述
select e.id,e.ename,e.salary,j.jname,j.description from emp e, job j WHERE e.job_id = j.id;

#案例二：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc from emp e, job j,dept d WHERE e.job_id = j.id and d.id = e.dept_id;

#案例三：查询员工姓名，工资，工资等级
select e.ename,e.salary,s.grade from emp e,salarygrade s WHERE e.salary between s.losalary and s.hisalary; 

#案例四：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select
	t1.ename,t1.salary,t3.jname,t3.description,t2.dname,t2.loc,t4.grade
from 
	emp t1,dept t2,job t3,salarygrade t4
where 
	t1.job_id = t2.id 
	and t1.dept_id = t3.id 
	and t1.salary BETWEEN t4.losalary and t4.hisalary;

#案例五：查询出部门编号，部门名称，部门位置，部门人数
#先分组
select 
	dept_id,COUNT(id) total
	
from emp

GROUP BY dept_id;

#在查询
SELECT 
	t1.id,t1.dname,t1.loc,t2.total
FROM
	dept t1,(
	SELECT
		dept_id,
		COUNT( id ) total 
	FROM
		emp 
GROUP BY
	dept_id) t2
where t1.id = t2.dept_id;

#案例六：查询所有员工的姓名及其直接上级的姓名，没有领导的员工也要查询
select 
	t1.mgr,t1.ename,t2.id,t2.ename
from emp t1
LEFT JOIN emp t2 #使用做查询来实现查询所有情况
ON t1.id = t2.mgr;

6 事务

1 事务的基本介绍

概念：

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：
- 开启事务： start transaction;
- 回滚：rollback;
- 提交：commit;

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

select * from account;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

update account set balance = 1000;

#开启事务
START TRANSACTION;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';

出现错误了。。。。。
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

#程序无异常，提交事务
COMMIT;

#发现问题，回滚事务
ROLLBACK;

2 事务的四大特征（重要）

原子性：是不可分割的最小操作单位，要么成功，要么失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间，相互独立。
一致性：事务操作前后，数据总量不变。

3 事务的隔离级别

3.1 事务的概念

多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：
- 开启事务： start transaction;
- 回滚：rollback;
- 提交：commit;

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

select * from account;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

update account set balance = 1000;

#开启事务
START TRANSACTION;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';

出现错误了。。。。。
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

#程序无异常，提交事务
COMMIT;

#发现问题，回滚事务
ROLLBACK;

2 事务的四大特征（重要）

原子性：是不可分割的最小操作单位，要么成功，要么失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间，相互独立。
一致性：事务操作前后，数据总量不变。

3 事务的隔离级别

3.1 事务的概念

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

给阿姨倒杯卡布奇诺咖啡

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL

1 SQL分类DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列。关键字：create，drop,alter 等DML(Data Manipulation Languange)数据库操作语言用来对数据库中表的数据进行增删改查，关键字：insert，delete，update 等DQL(Date Query language)数据库查询语言用来查询数据库中表的记录（数据），关键字：select，where 等
复制链接

扫一扫