sql常用语句--DDL、DMl、DCL、DQL以及多表查询--数据库的约束--事务

mysql

DDL （数据定义语言）create drop alter truncate

数据定义语言 - Data Definition Language

show

-- 查看数据库
show databases
-- 查看数据库的信息 (数据库名,以及数据库的默认的编码格式)
show create database 数据库名;
-- 查看该库中的表
show tables;
-- 查看表的结构
desc 表名;

create

-- 在创建表之前,先使用这个库
use 数据库名
-- 创建数据库
create database 数据库名;
create database if not exists 数据库名
-- 创建数据库的时候直接修改了库的字符集
create database if not exists 数据库名 default character set 字符集;
-- 创建表
create table 表名{
	字段名称1 字段类型1,
	字段名称2 字段类型2,
	字段名称3 字段类型3,
	...
	字段名称n 字段类型n,
}
-- 字段类型
	varchar(m):m多少个字符 mysql字符串类型
	int:  默认int(11) 	int(num):参数指定长度 
	double(5,2)          表示小数5位,小数点后保留2位
	时间:
	date				  mysql的日期时间(仅仅是日期,不包含具体时间):  年月日
	datetime			  mysql的日期时间:是日期+具体时间  
	timestamp(时间戳)      具体哪个时间操作的表   

drop 删除索引、表和数据库。

-- 删除数据库
drop database 数据库名;
drop database if exists 数据库名; 如果存在,删除该库
-- 删除表
drop table 表名
drop table if exists teacher;

alter 增加删除修改列

-- 给表中新增一列 (加入一个新的字段)
alter table 表名 add 字段名 字段类型;
-- 删除表的一列
alter table 表名 drop 字段名
-- 修改表的数据类型
alter table 表名 modify 字段名 字段类型;
-- 修改表的名称 
alter table 表名 rename to 新表名;
-- 修改字段的名称change
alter table 表名 change 旧表名 新表名 字段类型;
-- 修改编码格式
alter database 数据库名 default character set 编码集

DML （数据操纵语言）update， insert， delete

数据处理语言 - Data Manipulation Language

update语句用于修改表中的数据

-- 修改表
-- 带条件修改单个字段
-- update 表名 set 字段名 = 赋值 where 字段 = 值;
-- 注意:update语句都需要带上where条件,否则就是"批量修改" 
-- 修改多个字段 
UPDATE student SET 字段名 = 赋值,字段名 = 赋值 WHERE 字段名 = 值 ;

insert into 语句用于向表格中插入新的行。

-- 插入字段值
insert into 表名 values(值1,值2,值3,.....)
-- 插入部分字段值
insert into 表名(字段名1,字段名2...) values(值1,....) ;

delete 语句用于删除表中的行。

-- 删除表的记录
delete from 表名 条件(where)
-- 删除全表数据:
DELETE FROM stu;
TRUNCATE TABLE stu ;
/*
    TRUNCATE table 表名 和 delete f rom 表名 有什么区别?
    1)	delete from 表名:删除全表数据  
    	TRUNCATE table 表名 :删除表以及表中的数据  创建一张一模一样的表
    2)是否对自增长的主键影响?
    delete from 表名 不会影响自增长的主键(删除全表的数据)(不会清除自增长主键!)
    TRUNCATE TABLE 表名 :直接删除了表(并且同时创建一张一模一样的表):自增长主键会清除掉!
*/

DCL （数据控制语言）

数据控制语言 – Data Control Language
如grant，revoke，if…else，while，begin transaction

--------待更新

DQL （数据查询语言）select

数据查询语言 – Data Query Language

select用于从表中选取数据。

--  查询全表数据
select * from 表名; 

-- 查询指定的字段
SELECT 字段名1,字段名2... FROM 表名

-- 条件查询
-- 条件:
-- 	 使用表达式符号 < , > , <= , <= , <>
--   并列关系:between xx and  xx
--   或的关系:or  ||
--   集合in(18,25,19)
--   条件: is null/is not null
SELECT * FROM 表名 条件(WHERE)

-- 模糊条件查询 关键字:like
-- %:表示多个任意字符
-- _:代表一个字符
select 字段名称 from 表名 where 某个字段 like '模糊符号..'

-- order by :排序查询
-- asc :升序排序
-- desc:降序排序
select 字段名称列表 from 表名 order by 排序的字段 排序规则;

-- 聚合函数
-- count函数,max函数,min函数,avg函数,sum函数
select * from 表名 where 函数名(字段名)
select 函数名(字段名) from 表名

-- 分组查询 group by 分组字段
-- 分组查询:查询的是哪些字段
-- 查询的字段:可以是分组字段,使用聚合函数
-- GROUP BY分组查询,如果带有条件,先where,后group by
select * from 表名 group by 字段名
select * from 表名 条件(where) group by 字段名 

-- 筛选查询 having
-- having 是需要放在group by 后面,不能放置在group by 之前
select * from 表名 条件(where) group by 字段名 having 条件
-- having和group by的区别
-- group by是在where条件后面用,  不能使用聚合函数的!但是查询的时候可以查询聚合函数
-- having是在group by 后面使用  ,后面使用聚合函数

-- 分页查询:limit关键字
-- 语法格式:limit 起始行数,每页显示的条数
select * from 表名 limit 起始行数,每页显示条数
-- 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数

-- 去重查询
-- 关键词 DISTINCT 用于返回唯一不同的值。
SELECT DISTINCT 列名称 FROM 表名称

数据库的约束

/*
    约束:通过一些特定的关键字 保证数据的安全性,正确性和有效性!

    数据库的约束
    默认约束 字段名称 类型 default '值'
    非空约束 not null
    唯一约束 unique
    主键约束 primary key
    自增长约束 auto_increment 
    外键约束 foreign key
*/
-- 部门表
-- 主表:没有外键的表
CREATE TABLE dept( 
	id INT PRIMARY KEY AUTO_INCREMENT, -- 部门表的主键+自增长id
	dept_name VARCHAR(10) -- 部门名称
 ) ;
 
-- 员工表:从表:外键是声明在从表上
 CREATE TABLE emploee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(5) ,
	dept_id INT,
    -- constraint(声明) 外键名称() foreign key 
	-- (员工表的dept_id)  reference(关联) 部门表的主键id 
	CONSTRAINT dept_emp_fk FOREIGN KEY (dept_id) REFERENCES  dept(id) 
 ) ;

数据库

# 创建部门表 
CREATE TABLE dept( 
id INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR(20) 
)

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); 


# 创建员工表 
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) 
-- 外键，关联部门表(部门表的主键)
-- 省略了constraint 外键声明
 )
DROP TABLE emp

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('zoom','男 ',7200,'2013-02-24',1); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('the shy','男 ',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('let me','男 ',5000,'2013-05-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('369','女 ',9000,'2020-01-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('bin','男 ',4500,'2001-11-02',2);
                                                             
                                                             
SELECT * FROM emp
SELECT * FROM dept

级联删除和级联修改(CASCADE)

ON UPDATE CASCADE
ON DELETE CASCADE
外键约束
员工表的部门id  关联部门表的主键id字段

-- 删除或者修改主表: 不能操作 -> 必须对从表数据进行删除或者修改

-- 加入级联操作-- 修改或者删除主表(从表中的数据会随着主表的操作而变化)
-- sql语句先将外键约束删除
ALTER TABLE emploee DROP FOREIGN KEY dept_emp_fk
-- 添加级联操作(级联修改和级联删除)
ALTER TABLE emploee ADD
	CONSTRAINT dept_emp_fk FOREIGN KEY (dept_id)
	REFERENCES dept(id) ON DELETE CASCADE ON UPDATE CASCADE
	
-- 级联修改
-- 将1号部门变成5号部门,跟1号部门相关的员工的部门编号也会变化
UPDATE dept SET id=5 WHERE id = 1
-- 删除2号部门,跟2号部门相关的员工也会被删除
DELETE FROM dept WHERE id = 2
SELECT * FROM emploee
SELECT * FROM dept

数据库设计原则:三大范式

范式	特点
1NF	原子性:表中每列不可再拆分。
2NF	不产生局部依赖,一张表只描述一件事情。
3NF	不产生传递依赖,表中每一列都直接依赖与主键,而不是通过其它列间接依赖于主键。

表和表之间的关系

表与表之间的三种关系
一对多:主外键的关系
多对多:中间表,两个一对多
一对一:1)特殊一对多,从表中的外键设置为唯一 2)从表中的主键又是外键

My SQL的多表查询

笛卡尔积

同时查询多个表之间的所有数据
 	存在问题:产生了一个笛卡尔乘积
 	A表中有n条数据,B表中有m条数据,select * from A,B; ----n*m=总条数
 解决上面笛卡尔积的方案
     满足条件:
         1)查询哪个表    员工表和部门表
         2)查询指定表中的哪些字段   查询所有
         3)这些表的连接接条件是什么 员工表的dept_id部门编号依赖于部门表的主键id

内连接

隐式内连接: 使用 where 后面连接条件
        select
            列名列表
        from	
            表名列表
        where
            连接条件;
显示内连接: select 字段 from 表名1 inner(可省略) join 表名2 on 连接条件
        select	
            列名列表
        from	
            表名1
        inner(可以省略) join
            表名2
        on
            连接条件

外连接

左外链接:left outer(省略) join 
	将左表的数据全部查询并且还有查询交集部分(连接条件) 
        select	
            字段列表
        from
            左表(表名1)
        left outer join
            右表(表名2)
        on
            连接条件
右外连接right outer join...
	将右边全部显示以及多个表的交集部分数据(连接条件)
        select	
            字段列表
        from
            左表(表名1)
        right outer join
            右表(表名2)
        on
            连接条件

子查询

子查询情况1

-- 单行单列  使用聚合函数

    select
        聚合函数()
    from
        表名
- 单行多列
-- 员工工资最高的为:
SELECT MAX(salary) FROM emp
-- 员工工资为9000的信息
SELECT * FROM emp WHERE salary=9000	
-- 单行多列
	select 嵌套select语句
        select
            字段列表
        from
            表名1
        where 表达式(<,<=,>,>=)
            select
                字段列表
                    from 表名
                        where 表达式
-- 工资最高的员工信息
SELECT * FROM emp WHERE salary = (
                                    SELECT
                                        MAX(salary)
                                    FROM
                                        emp
                                    )

子查询情况2

-- 多行多列
select 字段列表 from 表名1 where 
							条件(
                            	select 字段列表 from 表名2 where
                                					条件()
                            )

-- 查询员工在财务部或者市房部的员工信息 -- 多行多列
-- 分步走
-- 1)查询财务部或者市场部的编号id是多少
SELECT id FROM dept WHERE
                        NAME= '市场部'
                        OR
                        NAME = '财务部'
-- 2)查询员工信息
SELECT * FROM  emp WHERE
                        dept_id = 2 
                        OR
                        dept_id = 3
-- 优化  or -->in(集合数据)  in()
SELECT * FROM  emp WHERE
						dept_id IN (2,3)
-- 一步走
SELECT * FROM  emp WHERE
                    dept_id IN (
								SELECT * FROM dept  WHERE
                                                NAME= '市场部'
                                                OR
                                                NAME = '财务部'
                        )

子查询情况3

将某个表查询出来的结果作为一个虚表(给虚表起别名),继续查询
select 语句 嵌套 select语句  (性能很差)
-- 将一个表中查询结果当成虚表来进行二次查询
-- 需求 查询员工表中入职日期大于'2001-11-02'的员工信息和部门信息

-- 分步走
-- 查询 join_date > '2001-11-02'的员工信息
SELECT * FROM emp WHERE
			join_date > '2001-11-02'

-- 将上面查询的结果当做虚表, 起一个别名,和部门表做一个联合查询
SELECT
                t1.name,
                t2.name,
                t2.gender,
                t2.salary,
                t2.join_date
FROM
                dept t1	,
                 (	SELECT * FROM emp WHERE
                                join_date > '2001-11-02'
                ) t2
WHERE
				t1.id = t2.dept_id

-- 优化
SELECT
	t2.name,
	t1.name,
	t1.gender,
	t1.salary,
	t1.join_date
FROM
	emp t1,
	dept t2
WHERE
	t1.join_date >= '2013-05-02'
	AND
	t1.dept_id = t2.id

数据库的备份和还原

备份:
    1)命令行的方式: 进入dos
        mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径
    2)可视化界面
        sqlyog工具-- 数据库上面右键, 选择备份-- 数据和结构一起备份
还原:
	1)进入dos ,登录mysql数据库---- 删除数据库
		source sql文件路径
	2可视化界面,--- 删除数据库
		sqlyog工具-- 数据库上面右键, 选择sql的转存文件来导入

mysql数据库的事务

传统事务
	将整个业务操作看成是一个整体,要么所有的业务数据同时成功/同时失败!
事务:将整个业务操作看成一个整体; 这些事务的执行要么同时成功,要么同时失败!
-- start transaction ;开启事务
-- rollback ; 回顾事务 -- 回滚到操作语句之前
-- commit; 提交数据	

事务的特性: ACID

事务特性	含义
原子性(Atomicity)	每个事务都是一个整体，不可再拆分，事务中所有的SQL 语句要么都执行成功， 要么都失败
一致性（Consistency）	事务在执行前数据库的状态与执行后数据库的状态保持一致
隔离性（Isolation)	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

数据库事务的级别

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

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的，这是事务update 时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是insert 或delete 时引发的问题

事务的隔离级别

读未提交 read uncommitted 安全性最低; 脏读,最危险的问题
读已提交 read committed  相当于前面安全性高,不能防止不可重复读
可重复读 repeatable read  mysql默认的隔离级别 有效防止了脏读和不可重复读
串行化 serializable 隔离级别最高

-- 隔离级别越高,性能越低!
-- set global transaction isolation level 隔离级别名称;