SQL

1.什么是SQL ?

structured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

---

2.SQL通用语法

1,SQL语句可以单行或多行书写，以分号结尾。
2,可使用空格和缩进来增强语句的可读性。
3, MysQL数据库的sQL语句不区分大小写，关键字建议使用大写。
4, 3种注释

单行注释:--注释内容或#注释内容(mysql 特有)
多行注释:/注释/

---

3.SQL分类

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

---

DDL操作数据库、表【CRUD】

数据库
C(Create)创建：
create database if not exists db4 character set gbk;如果db4数据库不存在，创建字符集为gbk的db4数据库
R(Retrieve)查询:
show databases;查看数据库名称
show database db4;查看db4的创建语句，可查到字符集
U(Updata)修改：
alter database db4 character set utf8;修改db4数据库的字符集utf-8
D(Delete)删除：
drop database if exists db2;如果db2数据库存在，删除db2数据库
使用：
select database();查询当前正在使用的数据库
use db1;使用db1数据库

表
C(Create)创建：
create table stu( id int, name varchar(32), score double(4,1), brith date, insert_time timestamp );
create table student like stu;创建一个student，结构和stu一样
R(Retrieve)查询:
show databases;查询数据库中所有的表名
desc user;查询user表的数据结构
U(Updata)修改：
alter table stu rename to student;修改表的名称
alter table student character set utf8;修改表的字符集
alter table student add sex varchar(1);添加一列
alter table student change genter sex varchar(1);修改列名以及列的数据类型。
alert table modify sex varchar(10);修改列的数据类型
alter table student drop sex;删除列
D(Delete)删除：
drop table if exists student;如果student表存在删除student表

---

常用数据库数据类型：
1,int :整数类型 age int
2,double:小数类型 score double(5,2) 长度5 保留两位，999.99
3, date:日期，只包含年月日,yyyy-MM-dd
4, datetime:日期，包含年月日时分秒yyyy-MM-dd HH:mm: ss
5, timestamp:时间错类型包含年月日时分秒yyyy-MM-ddHH: mm: ss
如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6 . varchar :字符串 name varchar(20) 姓名最大20个字符
zhangsan 8个字符 张三2个字符

DML增删改表中的数据【增，删，改】

增加数据
INSERT student(id,name,age) VALUES(1,'张无忌',18);增加相应字段对应的数据
INSERT student VALUES(2,'赵敏',98.8,null,null,18);增加所有字段对应的数据
INSERT student VALUES(3,'小明',98.8,'2004-9-8',null,18);除了数字以外其他数据都用引号
修改数据
UPDATE student set name='赵敏' where id=3;
删除数据
DELETE from student WHERE id=1;删除id=1的数据
DELETE from student;一条一条删除表中所有数据，效率低
TRUNCATE table student;--删除student表，赋值一个一模一样的空表。

---

DQL查询表中的数据【查】

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

1，基础查询
多个字段查询
SELECT * FROM stu; -- 查询所有字段数据
SELECT NAME,age from stu;-- 查询姓名和年龄
-- 去除重复
SELECT DISTINCT address FROM stu;-- 去除重复的结果集
-- 计算列
SELECT name,math,english,math+IFNULL(english,0) from stu;-- 计算总分
-- 起别名
SELECT name,math,english,math+IFNULL(english,0) AS 总分 from stu;
SELECT name 姓名,math 数学,english 英语,math+IFNULL(english,0) 总分 from stu;
2，条件查询
1. where手句后跟条件
2．运算符
-- 年龄大于等于20
select * from stu WHERE age>=20;
-- 年龄等于20
select * from stu WHERE age=20;
-- 年龄不等于20
select * from stu WHERE age!=20;
select * from stu WHERE age<>20;
-- 年龄大于等于20小于等于30
select * from stu WHERE age>=20 && age<=30;
select * from stu WHERE age>=20 and age<=30;
select * from stu WHERE age BETWEEN 20 and 30;
-- 年龄在其中
select * from stu WHERE age=22 OR age=18 OR age=20
select * from stu WHERE age IN(22,18,20)
-- 查询英语成绩是null
select * from stu WHERE english is NULL;
-- 查英语成绩不是null
select * from stu WHERE english is NOT NULL;
3，LIKE模糊查询
-- 姓名中姓马的人
select * from stu WHERE name LIKE '马%'
-- 姓名中第二个字是化的人
select * from stu WHERE name LIKE '_化%'
-- 姓名只有三个字的人
select * from stu WHERE name LIKE '___'
-- 姓名包含马的人
select * from stu WHERE name LIKE '%马%'
4，排序查询
SELECT * from stu ORDER BY math ASC -- 默认 升序
SELECT * from stu ORDER BY math DESC -- 降序
SELECT * FROM stu ORDER BY math asc,english asc -- 当第一条件相等时，会按照第二条件排序
5，聚合函数：列的纵向计算
COUNT计算数据数量
SELECT COUNT(name) FROM student;-- 计算个数 其中不包含null数据
SELECT COUNT(IFNULL(english,0)) FROM student;-- 计算个数 其中包含null数据
SELECT COUNT(*) FROM student;-- 不推荐 非空列的主键
SELECT COUNT(id) FROM student;-- 非空列的主键
MAX 数据中的最大值
SELECT MAX(math) FROM student;
MIN 数据中的最小值
SELECT MIN(math) FROM student;
SUM 数据的和
SELECT SUM(math) FROM student;
数据的平均值
SELECT AVG(math) FROM student;
6，分组查询
语法:group by 分组字段;
注意︰分组之后查询的字段:分组字段、聚合函数
where和having 的区别：
1，where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2，where后不可以跟聚合函数，having可以进行聚合函数的判断。
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex; -- 按照性别查数学的平均数 和男女总数
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;-- 按照性别查数学成绩大于70的平均数 和男女总数
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;-- 按照性别查数学成绩大于70的平均数 和男女总数大于两个
7，分页查询
1．语法:limit开始的索引,每页查询的条数;
-每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * EROM student LIMIT 6,3; -- 第3页
--公式:开始的索引=(当前的页码-1)*每页显示的条数
8，约束
概念:
对表中的数据进行限定，保证数据的正确性、有效性和完整性。
分类:
1.主键约束︰ primary key
含义∶非空且唯一，一张表只能有一个字段为主键，主键就是表中记录的唯一标识
在创建表时，添加主键约束
create table stu( id int primary key ,--给id添加主键约束name varchar(20) );
删除主键
alter table stu modify id int ;ALTER TABLE stu DROP PRIMARY KEY;
创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长∶
概念∶如果某一列是数值类型的，使用auto_increment可以来完成值得自动增长
2.非空约束:not null
创建表时添加约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL );-- name为非空
创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20)NOT NULL;
删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
3.唯一约束:unique:某一列的值不能重复
注意︰
唯一约束可以有NULL值，但是只能有一条记录为null
在创建表时，条件唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE); --手机号
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20)UNIQUE;
4.外键约束:foreign key,让表与表产生关系，从而保证数据的正确性。（不可随意删除或者添加表数据）
在创建表时，可以添加外键
*语法:
create table表名(
...
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称）REFERENCES 主表名称(主表列名称);
级联更新和删除：ON UPDATE CASCADE ON DELETE CASCADE
-- 级联更新，更新部门id，员工dep_id也跟着更改。
-- 添加外键的时候设置级联操作
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;-- 删除外键
-- 级联更新，更改部门的id，员工的dep_id也跟着更改，删除部门的一条记录，员工关联的部门id也会被删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE-- 添加外键和级联更新和级联删除操作

数据库的设计
多表之间的关系
1，一对一(了解)∶

如∶人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人。
实现：一对一关系实现，可以在任意―方添加唯―外键指向另一方的主键。

2．一对多(多对一):

如∶部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门。
实现：在多的一方创建外键，指向一的一方。

3．多对多︰

如：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择。
实现：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

案例：

-- 创建旅游线路分类表tab_categorycid
-- 旅游线路分类主键,自动增长
-- cname旅游线路分类名称非空，唯一，字符串100
CREATE TABLE  if not exists tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);

-- 创建旅游线路表tab_route
-- rid旅游线路主键，自动增长
-- rname旅游线路名称非空，唯一，字符串100
-- price价格
-- rdate 上架时间，日期类型
-- cid 外键，所属分类
create table  if not exists tab_route (
    rid int primary key auto_increment,
    rname varchar (100) not null unique,
    price double,
    rdate date,
    cid int,
    foreign key (cid) references tab_category (cid)
);

/*创建用户表tab_user
uid用户主键，自增长
username 用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别,定长字符串1
telephone手机号，字符串11
email工箱,宁符串长度100
*/
CREATE TABLE if not exists tab_user(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR (100) UNIQUE NOT NULL,
    password VARCHAR(30) NOT NULL,
    NAME VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT '男',
    telephone VARCHAR(11),
    email VARCHAR(100)
);

/*
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和 uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次 
*/
CREATE TABLE if not exists tab_favorite (
    rid INT,
    DATE DATETIME,
    uid INT,
-- 创建复合主键
    PRIMARY KEY(rid,uid),
    FOREIGN KEY(rid) REFERENCES tab_route(rid),-- 线路id
    FOREIGN KEY(uid) REFERENCES tab_user(uid)-- 用户id    
);

---

数据库设计的范式
概念

设计数据库时，需要遵循的一些规范。
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式:第一范式(1NF)、第二范式(NF)、第三范式(NF)、巴斯-科德范式(eCNF)、第四范式(AMF）和第五范式(SNF,又称完美范式)。

分类︰

第一范式(1NF)：每一列都是不可分割的原子数据项
第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖)
第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖)

几个概念︰
1，函数依赖

A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。(学号，课程名称)-->分数

2，完全函数依赖

A-->B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
例如︰(学号，课程名称)-->分数

3，部分函数依赖

A-->B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号，课程名称)-- >姓名

4，传递函数依赖

A-->B，B -- >C，如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过8属性（属性组)的值可以确定唯一(属性的值，则称C传递函数依赖于A
例如:学号-->系名，系名-->系主任

5，码

如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性值)为该表的码
例如:该表中码为:(学号，课程名称)

---

数据库的备份和还原
备份: mysqldump -u用户名-p密码>保存的路径
还原︰
1．登录数据库
2．创建数据库
3．使用数据库
4．执行文件。source文件路径

---

多表查询
笛卡尔积：集合A和集合B,取这两个集合的所有组成情况（集合A*集合B）
概念：多表查询就是消除无用的笛卡尔积。

1. 内连接
   -- 隐式内连接查询
   SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;-- 筛选笛卡尔积
   SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id=dept.id;-- 查询员工的名称，性别部门
   -- 给表起别名以及书写格式，方便写注释

SELECT 
    t1.name, -- 员工表姓名
    t1.gender, -- 员工表性别
    t2.name  -- 部门表名称
FROM 
    emp t1, -- 员工表别名
    dept t2 -- 部门表别名
WHERE 
    t1.dept_id=t2.id -- 条件员工表的外键=员工的id

2. 外连接
   -- 左外连接∶
   语法: select字段列表 from表1 left [outer] join表2 on条件;*查询的是左表所有数据以及其交集部分。
   SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id; -- 查询出所有的emp不管数据中是否有null
   -- 右外连接∶
   语法:select字段列表from表1 right [outer] join表2 on条件;
   SELECT t1.*,t2.name FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id=t2.id; -- 查询出所有的emp不管数据中是否有null

3. 子查询 查询中嵌套查询，这个嵌套的查询被称作子查询
   单行单列：子查询可作为条件，使用运算符判断。
   SELECT * FROM emp t1 WHERE t1.salary=(SELECT MAX(emp.salary) FROM emp); -- 查询工资最高的员工
   单行多列：子查询可作为条件，使用in
   SELECT emp.* FROM emp WHERE emp.dept_id in (SELECT id FROM dept WHERE name='财务部' OR name='市场部'); -- 查询财务部和市场部所有员工的信息
   多行多列：可以把查询结果作为一张虚拟表
   SELECT * from dept t1,(SELECT * from emp WHERE emp.join_date>'2011-11-11') t2 WHERE t2.dept_id=t1.id
SELECT * from dept t1,emp t2 WHERE t2.dept_id=t1.id and t2.join_date>'2011-11-11'; --普通查询

4. 多表查询练习

-- 需求:
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
    分析：员工信息emp表 职务job报表  两张表关联   条件emp.job_id=job.id
*/

SELECT 
    t1.id,t1.ename,t1.salary,t2.jname,t2.description
FROM

    emp t1,job t2

WHERE
    t1.job_id=t2.id;


-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT 
    t1.id,-- 员工编号
    t1.ename,-- 员工姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dname,-- 部门名称
    t3.loc-- 部门地址

FROM

    emp t1,job t2,dept t3

WHERE
    t1.job_id=t2.id AND t1.dept_id=t3.id;

-- 3.查询员工姓名，工资，工资等级
SELECT 
    
    t1.ename,-- 员工姓名
    t1.salary,-- 工资
    t2.grade-- 工资等级
FROM
    emp t1,salarygrade t2
WHERE
    t1.salary >t2.losalary AND t1.salary <t2.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT 
    t1.ename,-- 员工姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dname,-- 部门名称
    t3.loc,-- 部门地址
    t4.grade-- 工资等级
FROM
    emp t1,job t2,dept t3,salarygrade t4

WHERE
    t1.job_id=t2.id 
    AND t1.dept_id=t3.id 
    AND t1.salary >t4.losalary 
    AND t1.salary <t4.hisalary;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*分析:
1.部门编号、部门名称、部门位置dept表。部门人数emp表
2.使用分组查询。按照emp.dept_id完成分组，查询count (id)
3.使用子查询将第2步的查询结果和dept表进行关联查询
*/

SELECT 
    t2.dept_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;

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*分析:
1.姓名emp，直接上级的姓名emp
*emp表的id和mgr是自关联
2.条件emp.id = emp.mgr
3.查询左表的所有数据，和交集数据
使用左外连接查询

*/
SELECT
t1.ename,t1.mgr,t2.id,t2.ename
FROM emp t1
LEFT JOIN emp t2
ON t1.mgr =t2.id;

---

事务

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

• 开启事务：start transaction;
• 回滚：rollback;
• 提交：commit;

START TRANSACTION; -- 开启事务
UPDATE account SET balance=balance-500 WHERE `NAME`='zhangsan';
-- 出错了。。。
UPDATE account SET balance=balance+500 WHERE `NAME`='lisi'
COMMIT; -- 如果成功提交
ROLLBACK; -- 如果出错回滚

• MysQL数据库中事务默认自动提交

事务提交的两种方式:

1. 自动提交：
   mysql就是自动提交的 一条DML(增删改)语句会自动提交一次事务。
2. 手动提交:│ .
   oracle数据库默认是手动提交事务 需要先开后事务，再提交
3. 修改事务的默认提交方式：
   查看事务的默认提交方式:SELECT@Oautocommit;
   代表自动提交0代表手动提交*修改默认提交方式: set @@autocommit = 0;

3. 事务的四大特征

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

4. 事务的隔离级别（了解)

概念:多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题∶
1．脏读:一个事务，读取到另一个事务中没有提交的数据
2．不可重复读(虚读):在同一个事务中，两次读取到的数据不一样。
3．幻读:一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别:
1,read uncommitted :读未提交产生的问题:脏读、不可重复读、幻读
2,read committed :读已提交(oracle)产生的问题:不可重复读、幻读
3,repeatable read :可重复读(MysQL默认)产生的问题∶幻读
4,serializable :串行化 解决所有的问题
注意:隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别∶
select @@tx_isolation;
数据库设置隔离级别∶
set global transaction isolation level级别字符串;
演示︰
set global transaction isolation level read uncommitted;start transaction;
--转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

---

DCL管理用户，授权管理用户

• 管理用户
  添加用户∶CREATE USER‘用户名'@'主机名’IDENTIFIED BY‘密码';
  删除用户:DROP USER '用户名'@"主机名';
  修改用户密码∶UPDATE USER SET PASSWORD = PASSWORD( '新密码') WHEREUSER = '用户名';或者 SET PASSWORD TOR '用户名'@'主机名’=PASSWORD('新密码');

mysql中忘记了root用户的密码?
1,停止mysql服务:net stop mysql
2,管理员运行cmd,使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3,打开新的cmd窗口,直接输入mysql命今，敲回车。就可以登录成功
4,use mysql;
5,update user set password = password('你的新密码') where user = 'root';
6,关闭两个窗口
7,打开任务管理器，手动结束mysqld.exe的进程
8,启动mysql服务
9,使用新密码登录。

查询用户:-切换到mysql数据库USE myql;查询user表SELECT*FROM USER;
通配符:%表示可以在任意主机使用用户登录数据库

• 授权
  查询权限：SHOW GRANTS FOR'用户名'@'主机名';
  授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

GRANT SELECT,DELETE,UPDATE ON db3.account TO 'lisi'@'%' ;给用户lisi db3数据库的account表的查询，删除，更新权限。
GRANT ALLON*.*TO 'zhangsan'@ ' localhost '; 给张三用户授予所有权限，在任意数据库任意表上

撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名'@'主机名﹔