SQL基础语法及运用

SQL

---

仓库 database

星级	需求	方法
★	查询所有数据库	SHOW DATABASES;
	查询某个数据库的创建语句	SHOW CREATE DATABASE 数据库名称;
	创建数据库	CREATE DATABASE 数据库名称;
★	创建数据库，判断、如果不存在则创建	CREATE DATABASE IF NOT EXISTS 数据库名称;
★	创建数据库、并指定字符集	CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
	修改数据库的字符集	ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
	删除数据库	DROP DATABASE 数据库名称;
★	删除数据库，判断、如果存在则删除	DROP DATABASE IF EXISTS 数据库名称;
	使用数据库	USE 数据库名称;
	查询当前使用的数据库	SELECT DATABASE();

---

---

数据表 TABLE

星级	需求	方法
	查询所有数据表	SHOW TABLES;
	查询表结构	DESC 表名;
	查询数据表的字符集	SHOW TABLE STATUS FROM 数据库名称 LIKE ‘表名’;
★	创建数据表	CREATE TABLE 表名( 列名 数据类型 约束,列名 数据类型 约束,…);
☆	修改表名	ALTER TABLE 旧表名 RENAME TO 新表名;
	修改表的字符集	ALTER TABLE 表名 CHARACTER SET 字符集名称;
★	给表添加列	ALTER TABLE 表名 ADD 列名 数据类型;
★	修改表中列的数据类型	ALTER TABLE 表名 MODIFY 列名 数据类型;
★	修改表中列的名称和数据类型	ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;
★	删除表中的列	ALTER TABLE 表名 DROP 列名;
★	删除表	DROP TABLE 表名;
★	删除表，判断、如果存在则删除	DROP TABLE IF EXISTS 表名;

总结

表格 使用

id INT, – 商品编号
NAME VARCHAR(30), – 商品名称
price DOUBLE, – 商品价格
stock INT, – 商品库存
insert_time DATE – 上架时间
);

---

---

数据的增删改

---

增

星级	需求	方法
★	给指定列添加数据	INSERT INTO 表名(列名1,列名2,…) VALUES (值1,值2,…);
★	给全部列添加数据	INSERT INTO 表名 VALUES (值1,值2,值3,…);
★	批量添加所有列数据	INSERT INTO 表名 VALUES (值1,值2,值3,…),(值1,值2,值3,…),(值1,值2,值3,…);

---

删

星级	需求	方法
★	删除表数据	DELETE FROM 表名 [WHERE 条件];

---

改

星级	需求	方法
★	修改表数据	UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,… [where 条件];

---

查(重点)

星级	需求	方法
★	查询全部数据	SELECT * FROM 表名;
★	查询指定列	SELECT 列名1,列名2,… FROM 表名;
★	去除重复查询	SELECT DISTINCT 列名1,列名2,… FROM 表名;
★	计算列的值	SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名;
★	起别名	SELECT 列名1,列名2,… AS 别名 FROM 表名;
★	条件查询	SELECT 列名列表 FROM 表名 WHERE 条件;
★	聚合函数	SELECT 函数名(列名) FROM 表名 [WHERE 条件];
	排序查询	SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
★	分组查询	SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
★	分页查询	LIMIT 当前页数,每页显示的条数;

说明:

1. 计算列的值:如果某一列为null，可以进行替换,ifnull(表达式1(表达式1：想替换的列),表达式2(表达式2：想替换的值))
2. 分页查询:公式：当前页数 = (当前页数-1) * 每页显示的条数

总结:

查询的标准语法：
SELECT 列名 FROM 表名
[WHERE 条件]
[GROUP BY 分组列名]
[HAVING 分组后条件过滤]
[ORDER BY 排序列名 排序方式]
LIMIT 当前页数,每页显示的条数;

---

• 条件分类

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内(都包含)
IN(…)	多选一
LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 |
NOT 或 !	非，不是

聚合函数

• 将一列数据作为一个整体，进行纵向的计算
• 聚合函数分类

函数名	功能
count(列名)	统计数量(一般选用不为null的列)
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值
GROUP_CONCAT(cid order by cid separator ‘,’)	将获取的值放入到新的

排序查询

• 排序分类
  • 注意：多个排序条件，当前边的条件值一样时，才会判断第二条件

关键词	功能
ORDER BY 列名1 排序方式1,列名2 排序方式2	对指定列排序，ASC升序(默认的) DESC降序

---

---

约束

用来约束表格的

需求	方法	约束
建表后单独添加主键约束	ALTER TABLE [表名] MODIFY [列名] INT PRIMARY KEY;	PRIMARY KEY;
建表后单独添加自增约束	ALTER TABLE [表名] MODIFY [列名] INT AUTO_INCREMENT;	AUTO_INCREMENT;
建表后单独添加唯一约束	ALTER TABLE [表名] MODIFY [列名] UNIQUE	UNIQUE
建表后单独添加非空约束	ALTER TABLE [表名] MODIFY [列名] [数据类型] NOT NULL;	NOT NULL;

说明:

• 自增约束:

1. 一般配合键(key)来使用
2. 加了自增约束后,就算删除了改条数据,在添加其他数据不会覆盖被删除的自增约束

总结:

• 自增和非空 删除和创建都是通过修改(MODIFY方法)完成的

---

---

外键约束

需求	方法	约束
外键约束	CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)	CONSTRAINT
删除外键约束	ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
建表后单独添加外键约束	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

外键级联

需求	方法	约束
添加外键约束，同时添加级联更新 标准语法：	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;	ON UPDATE CASCADE;
添加外键约束，同时添加级联删除 标准语法：	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
添加外键约束，同时添加级联更新和级联删除 标准语法：	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

---

---

sql常见语句

now	获取当前时间
concat	字符串拼接

---

---

存储过程

创建存储过程

-- ----------------------------------------------创建存储过程--------------------------------------------------------------
-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
    SQL 语句列表;
END$

-- 修改分隔符为分号
DELIMITER ;
-- ----------------------------------------------查询数据库中所有的存储过程--------------------------------------------------------------
	SELECT * FROM mysql.proc WHERE db='数据库名称';
	-- ----------------------------------------------删除存储过程--------------------------------------------------------------
	DROP PROCEDURE [IF EXISTS] 存储过程名称;
		-- ----------------------------------------------定义变量--------------------------------------------------------------
	-- 定义变量
	DECLARE 变量名 数据类型 [DEFAULT 默认值];
	-- 变量赋值-方式二
	SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
		-- ----------------------------------------------if语句--------------------------------------------------------------
-- if语句
	IF 判断条件1 THEN 执行的sql语句1;
	[ELSEIF 判断条件2 THEN 执行的sql语句2;]
	...
	[ELSE 执行的sql语句n;]
	END IF;
		-- ----------------------------------------------参数传递--------------------------------------------------------------
-- 参数传递
	CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
	BEGIN
		SQL 语句列表;
	END$
			-- ----------------------------------------------	while循环--------------------------------------------------------------
-- 	while循环

	初始化语句;
	WHILE 条件判断语句 DO
		循环体语句;
		条件控制语句;
	END WHILE;
	-- 调用pro_test6存储过程
call pro_test6();
			-- ----------------------------------------------创建存储函数--------------------------------------------------------------
-- 	创建存储函数
	CREATE FUNCTION 函数名称([参数 数据类型])
	RETURNS 返回值类型
	BEGIN
		执行的sql语句;
		RETURN 结果;
	END$
--  调用函数
	SELECT 函数名称(实际参数);
	
	-- 删除函数
	DROP FUNCTION 函数名称;

定义方法:

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
    SQL 语句列表;
END$

-- 修改分隔符为分号
DELIMITER ;

事务

:保证语句正确执行,一旦出问题,就会失败

说人话:就是将多条语句合并到一条,这样一个地方出错,就会执行失败

开启事务	start transaction	开启
回滚事务	rollback	回到代码之前
提交事务	commit	提交代码

---

事务提交方式

一旦修改完了提交方式,如果是0手动提交,他不会提交,我们看见的就只是暂存的数据

查看提交方式	select @@autocommit
设置提交方式	set @@autocommit=‘0/1’	0:手动提交,1自动提交

---

事务的四大特征:

原子性	同时成功,或者同时失败
一致性	物质守恒定论,需要改变的到改变之后的要保存一致
隔离性	出现并发操作时候,多个用户操作的数据相互独立
持久性	一旦提交后就永久性保存数据

四种隔离级别

read uncommitted	读未提交	脏,不可重复,幻读
read committed	读已提交	不可重复,幻读
repeatable read	可重复读	幻读
serializable	串行化

可能引发的现象

问题	现象
脏读	是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功o

1. 查询语句顺序	select	from	where	group by	having	oprder by	limit
   	查询	表	条件判断	分组	分组条件过滤	排序	分页