MYSQL

最新推荐文章于 2024-06-02 14:54:06 发布

捡头发的小灰灰

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量515

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/l_zerol/article/details/108550511

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章内容输出来源：拉勾教育大数据开发高薪训练营
在这里插入图片描述

一、SQL

1. 什么是SQL？

Structured Query Language：结构化查询语言，是一种特殊目的的编程语言，是一种数据库
查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2. SQL通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。

可使用空格和缩进来增强语句的可读性。
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
3 种注释
- 单行注释: --空格：注释内容或 # 注释内容(mysql 特有)
- 多行注释: /* 注释 */

3. SQL分类

分类	说明
DDL(Data Definition Language)数据定义语言	用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言	用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言	用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言(了解)	用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

4. DDL:操作数据库、表

4.1 创建数据库

命令	说明
create database 数据库名；	创建指定名称的数据库。
create database 数据库名 character set字符集；	创建指定名称的数据库，并且指定字符集（一般都指定utf-8）

/* 方式1 直接指定数据库名进行创建 默认数据库字符集为：latin1 */
CREATE DATABASE db1; 
/* 方式2 指定数据库名称，指定数据库的字符集 一般都指定为 utf8,与Java中的编码保持一致 */
CREATE DATABASE db1_1 CHARACTER SET utf8;

4.2 查看/选择数据库

在这里插入图片描述
4.3 修改数据库

4.4 删除数据库

5. DDL 操作数据表

5.1 MySQL常见的数据类型
在这里插入图片描述
5.2 详细数据类型

注意： MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，区别在于：

char类型是固定长度的：根据定义的字符串长度分配足够的空间。
varchar类型是可变长度的：只使用字符串长度所需的空间。

比如：
x char(10) 占用10个字节
y varchar(10) 占用3个字节

适用于：

char类型适合存储固定长度的字符串，比如密码，性别一类
varchar类型适合存储在一定范围内，有长度变化的字符串

具体如下：
在这里插入图片描述
5.3 建表

复制表：create table 新表名 like 旧表名

5.4 查看表
在这里插入图片描述
5.5 删除表

5.6 修改表

5.6.1 改表名： rename table 旧表名 to 新表名

 #将category表 改为 category1
 RENAME TABLE category TO category1;

5.6.2 修改表的字符集：alter table 表名 character set 字符集

#将category表的字符集 修改为gbk 
alter table category character set gbk;

5.6.3 向表中添加列ADD：alert table 表名 add 字段名称字段类型

 #为分类表添加一个新的字段为 分类描述 cdesc varchar(20)
 # 为分类表添加一个新的字段为 分类描述 cdesc varchar(20)
  ALTER TABLE category ADD cdesc VARCHAR(20);

5.6.4 修改表中列的数据类型或长度 MODIFY：alter table 表名 modify 字段名称字段类型

#对分类表的描述字段进行修改，类型varchar(50)
ALTER TABLE category MODIFY cdesc VARCHAR(50);

5.6.5 修改列名称 CHANGE：alter table 表名 change 旧列名新列名类型(长度);

#对分类表中的 desc字段进行更换, 更换为 description varchar(30)
ALTER TABLE category CHANGE cdesc description VARCHAR(30);

5.6.6 删除列 DROP：alter table 表名 drop 列名

 删除分类表中description这列
 ALTER TABLE category DROP description;

6. DML 操作表中数据

6.1 插入：insert into 表名（字段名1，字段名2...） values(字段值1，字段值2...);

添加数据的三种方式：

方式1：插入全部字段，将所有字段名都写出来

INSERT INTO student (sid,sname,age,sex,address) 
VALUES(1,'孙悟空',20,'男','花果 山');

方式2：插入全部字段，不写字段名

INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球');

方式3：插入指定字段的值

INSERT INTO category (cname) VALUES('白骨精');

注意：

值与字段必须要对应，个数相同&数据类型相同；
值的数据大小，必须在字段指定的长度范围内；
varchar char date类型的值必须使用单引号，或者双引号包裹；
如果要插入空值，可以忽略不写，或者插入null；
如果插入指定字段的值，必须要上写列名；

在这里插入图片描述
6.2 更改

6.2.1 不带条件的修改：update 表名 set 列名 = 值

6.2.2 带条件的修改：update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值 ]

6.3 删除

6.3.1 删除所有：delete from 表名

6.3.2 删除指定数据：delete from 表名 [where 字段名 = 值]

在这里插入图片描述

7. DQL 查询表中数据

7.1 简单查询

select 列名 from 表名

别名查询，使用关键字 as

SELECT eid AS '编号', 
	   ename AS '姓名' , 
	   sex AS '性别', 
	   salary AS '薪资', 
	   hire_date '入职时间',
	    -- AS 可以省略 
	   dept_name '部门名称'
FROM emp;

-- 使用distinct 关键字,去掉重复部门信息 
SELECT DISTINCT dept_name FROM emp;

#将所有员工的工资 +1000 元进行显示
#运算查询 (查询结果参与运算)
SELECT ename , salary + 1000 FROM emp;

7.2 条件查询

select 列名 from 表名 where 条件表达式

在这里插入图片描述

练习：

 # 查询员工姓名为黄蓉的员工信息
 SELECT * FROM emp WHERE ename = '黄蓉'; 
 # 查询薪水价格为5000的员工信息 
 SELECT * FROM emp WHERE salary = 5000;
 # 查询薪水价格不是5000的所有员工信息 
 SELECT * FROM emp WHERE salary != 5000; 
 SELECT * FROM emp WHERE salary <> 5000; 
 # 查询薪水价格大于6000元的所有员工信息 
 SELECT * FROM emp WHERE salary > 6000;

 # 查询薪水价格在5000到10000之间所有员工信息 
 SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000; 
 # 查询薪水价格是3600或7200或者20000的所有员工信息 
 -- 方式1: or 
 SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000; 
 -- 方式2: in() 匹配括号中指定的参数 
 SELECT * FROM emp WHERE salary IN(3600,7200,20000);

在这里插入图片描述

# 查询含有'精'字的所有员工信息 
SELECT * FROM emp WHERE ename LIKE '%精%'; 
# 查询以'孙'开头的所有员工信息 
SELECT * FROM emp WHERE ename LIKE '孙%'; 
# 查询第二个字为'兔'的所有员工信息 
SELECT * FROM emp WHERE ename LIKE '_兔%'; 
# 查询没有部门的员工信息 
SELECT * FROM emp WHERE dept_name IS NULL;
-- SELECT * FROM emp WHERE dept_name = NULL;错误
# 查询有部门的员工信息 
SELECT * FROM emp WHERE dept_name IS NOT NULL;

7.3 排序查询

order by 子句
* order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...

排序方式：
* ASC：升序，默认的。
* DESC：降序。

注意：
* 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

7.4 聚合函数

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

count：计算个数
一般选择非空的列：主键
count(*)

max：计算最大值
min：计算最小值
sum：计算和在这里插入代码片
avg：计算平均值

注意：聚合函数的计算，排除null值。
解决方案：
1. 选择不包含非空的列进行计算
2. IFNULL函数

7.5 分组查询

1. 语法：group by 分组字段；
	2. 注意：
		1. 分组之后查询的字段：分组字段、聚合函数
		2. where 和 having 的区别？
			1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
			2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

		-- 按照性别分组。分别查询男、女同学的平均分

		SELECT sex , AVG(math) FROM student GROUP BY sex;
		
		-- 按照性别分组。分别查询男、女同学的平均分,人数
		
		SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
		
		--  按照性别分组。分别查询男、女同学的平均分,人数 
		--  要求：分数低于70分的人，不参与分组
		SELECT sex , AVG(math),COUNT(id) 
		FROM student WHERE math > 70 GROUP BY sex;
		
		--  按照性别分组。分别查询男、女同学的平均分,人数 
		--  要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
		SELECT sex , AVG(math),COUNT(id) 
		FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
		
		SELECT sex , AVG(math),COUNT(id) 人数 
		FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

7.6 分页查询

limit 关键字的作用
limit是限制的意思,用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
limit 语法是 MySql的方言,用来完成分页

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

在这里插入图片描述
实现：

# 查询emp表中的前 5条数据 
-- 参数1 起始值,默认是0 , 参数2 要查询的条数 
SELECT * FROM emp LIMIT 5; SELECT * FROM emp LIMIT 0 , 5; 
# 查询emp表中 从第4条开始,查询6条 
-- 起始值默认是从0开始的. 
SELECT * FROM emp LIMIT 3 , 6;

-- 分页操作 每页显示3条数据 
SELECT * FROM emp LIMIT 0,3; -- 第1页 
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3 
SELECT * FROM emp LIMIT 6,3; -- 第三页 
-- 分页公式 起始索引 = (当前页 - 1) * 每页条数 
-- limit是MySql中的方言

8. SQL约束

8.1 主键约束 primary key

特点：不可重复唯一非空
创建方式：

# 方式1 创建一个带主键的表 
CREATE TABLE emp2( -- 设置主键 唯一 非空 
	eid INT PRIMARY KEY, 
	ename VARCHAR(20), 
	sex CHAR(1) 
);

-- 方式2 创建一个带主键的表 
CREATE TABLE emp2( 
	eid INT , 
	ename VARCHAR(20), 
	sex CHAR(1), 
	-- 指定主键为 eid字段 
	PRIMARY KEY(eid) 
);

-- 方式3 创建一个带主键的表 
CREATE TABLE emp2( 
	eid INT , 
	ename VARCHAR(20), 
	sex CHAR(1) 
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置 
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

删除主键约束：ALTER TABLE emp2 DROP PRIMARY KEY;
主键自增：AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
修改主键自增的起始值，默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式：

-- 创建主键自增的表,自定义自增其实值 
CREATE TABLE emp2( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	ename VARCHAR(20), 
	sex CHAR(1) 
)AUTO_INCREMENT=100;

DELETE和TRUNCATE对自增长的影响

在这里插入图片描述
8.2 非空约束 not null

特点：某一列不许为空

# 非空约束 
CREATE TABLE emp2( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	-- 添加非空约束, ename字段不能为空 
	ename VARCHAR(20) NOT NULL, 
	sex CHAR(1) 
);

8.3 唯一约束 unique

特点：表中的某一列的值不能重复( 对null不做唯一的判断 )

#创建emp3表 为ename 字段添加唯一约束 
CREATE TABLE emp3( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	ename VARCHAR(20) UNIQUE, 
	sex CHAR(1) 
);

在这里插入图片描述
8.4 外键约束 foreign key

8.4.1.外键

外键指的是在从表中与主表的主键对应的那个字段,比如员工表的 dept_id,就是外键
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性。

注：多表关系中的主表和从表

主表: 主键id所在的表, 约束别人的表
从表: 外键所在的表多, 被约束的表

8.4.2 创建外键约束

1. 新建表时添加外键
 [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) 
 REFERENCES 主表名(主键字段名)

2. 已有表添加外键
 ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称]
 FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

8.4.3 删除外键约束

-- 删除employee 表中的外键约束,外键约束名 emp_dept_fk 
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

8.4.4 外键约束的注意事项

从表外键类型必须与主表主键类型一致否则创建失败；
添加数据时, 应该先添加主表中的数据；
删除数据时,应该先删除从表中的数据；

8.4.5 级联删除 ON DELETE CASCADE

CREATE TABLE employee( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	ename VARCHAR(20), 
	age INT, 
	dept_id INT, 
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id) 
	-- 添加级联删除 
	ON DELETE CASCADE 
);

8.5. 默认值

字段名 字段类型 DEFAULT 默认值

9. 数据库事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败；

比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.

回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

9.1 模拟转账操作

-- tom账户 -500元 
UPDATE account SET money = money - 500 WHERE NAME = 'tom'; 
-- jack账户 + 500元 
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元，数据就出现问题了。我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务.

9.2 mysql事务操作

两种提交方式：手动提交方式和自动提交方式

9.2.1 手动提交事务
手动提交事务的流程
事务回滚
如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚；
如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。
如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
9.2.2 自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务；MySQL默认是自动提交事务。
取消自动提交
a. 查看autocommit状态：SHOW VARIABLES LIKE 'autocommit';
b. on:自动提交； off:手动提交；
c. 把 autocommit 改成 off; SET @@autocommit=off;
9.2.3 事务的四大特性ACID
9.2.4 事物的隔离级别

i. 数据并发访问
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性;

ii. 并发访问会产生的问题
事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题;

在这里插入图片描述

9.2.5 四种隔离级别

在这里插入图片描述
查看隔离级别：select @@tx_isolation;

设置隔离级别：set global transaction isolation level 级别名称;

10. 多表

表之间的关系
一对一、一对多(多对一)、多对多

10.1 多表查询

隐式内连接：SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
显式内连接：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 -- inner 可以省略
左外连接：使用 LEFT OUTER JOIN , OUTER 可以省略；SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

左外连接特点：以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据；如果匹配不到, 左表中的数据正常展示, 右边的展示为null
右外连接：与左外连接相反；

10.2 子查询

子查询概念
一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询的特点
1.子查询必须放在小括号中
2.子查询一般作为父查询的查询条件使用
子查询常见分类
1.where型子查询: 将子查询的结果, 作为父查询的比较条件
2.from型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用
3.exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
总结：
1.子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
2.子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名)

11. 数据库范式

概念
三范式就是设计数据库的规则.
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了。

11.1 第一范式 1NF

原子性, 做到列不可拆分；
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

11.2 第二范式 2NF

在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
一张表只能描述一件事.
比如：学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息，如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了；

11.3 第三范式 3NF

消除传递依赖；
表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放；

12. 视图

12.1 概念

视图是一种虚拟表。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
视图向用户提供基表数据的另一种表现形式.

12.2 视图的作用

权限控制时可以使用
比如,某几个列可以运行用户查询,其他列不允许,可以开通视图查询特定的列, 起到权限控制的作用；
简化复杂的多表查询
视图本身就是一条查询SQL,我们可以将一次复杂的查询构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)，视图主要就是为了简化多表的查询；

12.3 创建视图

create view 视图名 [column_list] as select语句; 

view: 表示视图 
column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，
             默认情况下，与SELECT语句中查询 的属性相同 
as : 表示视图要执行的操作 
select语句: 向视图提供数据内容

12.4 视图与表的区别

视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果,不允许更新）
删除视图，表不受影响，而删除表，视图不再起作用

捡头发的小灰灰

关注

0
点赞
踩
2

收藏

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

文章内容输出来源：拉钩教育大数据开发高薪训练营一、SQL1. 什么是SQL？ Structured Query Language：结构化查询语言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。2. SQL通用语法1. SQL 语句可以单行或多行书写，以分号结尾。可使用空格和缩进来增强语句的可读性。MySQL 数据库的
复制链接

扫一扫