sql语句

Structured Query Language：结构化查询语言

数据库的注释：

• 单行注释：-- 注释内容 #注释内容(mysql特有)
• 多行注释：/* 注释内容 */

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

数据库，表，列等。关键字：create, drop,alter 等

• create database

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

-- 查询某个数据库的创建语句
show create database databasename;

-- 查询msyql数据库的创建格式
show create database mysql

-- 创建数据库
create database if not exists ba1;

-- 创建数据库，并指定字符集
create database if not exists db1 character set utf8;

• update database

alter database db1 character set gbk;

show create database db1;

• drop

drop database();

drop database if exists db1;

• use

select database;
use db1;



-- 查询数据库中的表
show tables;

-- 查询表结构
DESC 表名

-- 查询字符集类表
show table status from 库名 like '表名'；

-- 创建表
create table student(
	id int primary key auto_increment,
	username char(20),
	password char(20),
	name char(20),
	age int
);

-- 复制表结构
create table teacher like student;

-- 修改表名称
alter table teacher rename to teachers;

-- 修改表结构
alter table teachers add tel varchar(11);

-- 修改列名称和数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

-- 删除列
alter table teacher drop tel;

2、DML(Data Manipulation Language)数据操作语言

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

-- 添加数据
	insert into student values(
		null,'root','root','屠洪刚',25
	);

-- 修改数据
 update student set name = '张韶涵',age = 18 where id = 3;

-- 删除数据
 delete from student where id = 3 limit 1;

3、DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等

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


-- 查询全部
select * from student;

-- 多个字段查询
select 列名1,列名2,... from 表名;

-- 去重查询
select distinct 列名1,列名2,... from 表名;

-- 计算列的值
select 列名1 运算符(+ - * /) 列名2 from 表名;
ifnull(表达式1,表达式2)
select NAME,IFNULL(stock,0)+10 from product;


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


-- 聚合函数
| 函数名      | 功能                           |
| ----------- | ------------------------------ |
| count(列名) | 统计数量(一般选用不为null的列) |
| max(列名)   | 最大值                         |
| min(列名)   | 最小值                         |
| sum(列名)   | 求和                           |
| avg(列名)   | 平均值                         |


-- 排序查询
ASC升序(默认的)  DESC降序
SELECT * FROM product ORDER BY stock ASC;

-- 分组查询
SELECT brand,SUM(price) FROM product GROUP BY brand;

SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000 ORDER BY getSum DESC;


-- 分页查询
SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式] LIMIT 开始索引,查询条数;
-- 公式：开始索引 = (当前页码-1) * 每页显示的条数

4、DCL(Data Control Language)数据控制语言

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

5、约束

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

• 普通约束

-- 添加约束
CREATE TABLE student(
	id INT PRIMARY KEY  AUTO_INCREMENT -- 给id添加主键约束
);

-- 删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 建表后单独添加主键
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

• 外键约束

-- 外键约束
让表和表之间产生关系，从而保证数据的准确性！

外键约束格式
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,                              -- 订单所属用户
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
);




-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;


-- 建表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);



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

-- 添加外键约束，同时添加级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;

6、多表设计

• 一对一
  在任意一个表建立外键，去关联另外一个表的主键

• 一对多
  在多的一方，建立外键约束，来关联一的一方主键

• 多对多
  需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

7、多表查询

• 笛卡尔积查询

SELECT
列名列表
FROM
表名列表
WHERE
条件…

• 内连接查询

显式内连接
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;

SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u          -- 用户表
JOIN
	orderlist o     -- 订单表
ON 
	u.`id` = o.`uid`;

隐式内连接
SELECT 列名 FROM 表名1,表名2 WHERE 条件;

SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u,		-- 用户表
	orderlist o     -- 订单表
WHERE
	u.`id`=o.`uid`;

• 外连接查询

左外连接：查询左表的全部数据，和左右两张表有交集部分的数据
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;


右外连接：查询右表的全部数据，和左右两张表有交集部分的数据
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

• 子查询

SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);

-- 子查询-结果是单行单列的
select  NAME,age from user where age = (select MAX(age) from user);

-- 子查询-结果是多行单列的
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 


-- 子查询-结果是多行多列的
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
select * from user u,(select * from orderlist where id>4) o where u.id=o.uid;

• 自关联查询:同一张表中有数据关联。可以多次查询这同一个表！

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
	员工姓名 employee表        直接上级姓名 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT
	t1.name,	-- 员工姓名
	t1.mgr,		-- 上级编号
	t2.id,		-- 员工编号
	t2.name     -- 员工姓名
FROM
	employee t1  -- 员工表
LEFT OUTER JOIN
	employee t2  -- 员工表
ON
	t1.mgr = t2.id;

8、 视图

概念：
- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
- 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

视图的好处
- 简单
  - 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
- 安全
  - 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
- 数据独立
  - 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
  - 
-- 标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
	city_country2 (city_id,city_name,cid,country_name) 
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;





-- 查询视图。查询这张虚拟表，就等效于查询城市和所属国家
SELECT * FROM city_country;

-- 查询指定列名的视图
SELECT * FROM city_country2;

-- 查询所有数据表，视图也会查询出来
SHOW TABLES;


-- 注意：视图表数据修改，会自动修改源表中的数据
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
DROP VIEW [IF EXISTS] 视图名称;


视图的好处

- 简单
- 安全
- 数据独立

9、备份与还原

mysqldump -u root -p 数据库名称 > 文件保存路径

重新创建名称相同的数据库,使用该数据库,
导入文件执行：
source 备份文件路径;use db1; source /root/mysql/db1.sql

10.事务

• 概念

一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

• 操作事务的三个步骤

1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
2. 执行sql语句：执行具体的一条或多条sql语句
3. 结束事务(提交|回滚)

开启事务  START TRANSACTION;
回滚事务	ROLLBACK;
提交事务	COMMIT;


-- 开启事务
START TRANSACTION;

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 回滚事务(出现问题)
ROLLBACK;

-- 提交事务(没出现问题)
COMMIT;

• 事务的提交方式

- 自动提交(MySQL默认为自动提交)
- 手动提交


-- 标准语法
SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交

-- 修改提交方式

SET @@AUTOCOMMIT=数字;

-- 修改为手动提交
SET @@AUTOCOMMIT=0;

-- 查看提交方式
SELECT @@AUTOCOMMIT;

• 事务的四大特征(ACID)

原子性(atomicity):子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
一致性(consistency):一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
隔离性(isolcation):隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性(durability):持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

• 事务的隔离级别
  四种隔离级别

1	读未提交	read uncommitted
2	读已提交	read committed
3	可重复读	repeatable read
4	串行化	serializable

可能引发的问题

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

--查询数据库隔离级别
SELECT @@TX_ISOLATION;

-- 修改数据库隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;


-- 查看隔离级别
SELECT @@TX_ISOLATION;   -- 修改后需要断开连接重新开

	隔离级别	名称	出现脏读	出现不可重复读	出现幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle / SQL Server
3	repeatable read	可重复读	否	否	是	MySQL
4	**serializable **	串行化	否	否	否

小结

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作
• 开启事务：start transaction;
• 回滚事务：rollback;
• 提交事务：commit;
• 事务四大特征
  • 原子性
  • 持久性
  • 隔离性
  • 一致性
• 事务的隔离级别
  • read uncommitted(读未提交)
  • read committed (读已提交)
  • repeatable read (可重复读)
  • serializable (串行化)