数据库DataBase

关系型数据库【SQL】

Oracle:Oracle公司的产品，收费的大型数据库，Oracle收购MySQL

SQLServer：Microsoft公司收费的中型数据库，C#、.net等语言常用

MySQL：开源的免费的小型数据库，被Oracle收购，收费

SQLite:嵌入式的小型数据库，应用在手机端

DB2:IBM公司的的数据库产品，收费的，用在银行系统中

常用数据库：MySQL

在web应用中，使用最多的就是MySQL数据库：

免费、开源

小巧、功能齐全

可运行在windows、linux操作系统

……
非关系型数据库【NoSQL】

Redis、MongoDB

1.2 MySQL

下载、安装

1.2.1 MySQL登录

mysql -u用户名 -p密码
mysql -uroot -proot

mysql -hIP地址 -u用户名 -p密码
mysql -h127.0.0.1 -uroot -proot

1.2.2 MySQL退出

exit或quit

1.2.3 MySQL目录结构

MySQL安装目录

bin:所有mysql数据库的可执行文件
data:用于存放一些日志文件以及数据库文件
include:用于放置一些头文件
lib:用于存放一些依赖、库文件
share:用于存放字符集等信息文件

my.ini 是MySQL的配置文件，一般不建议修改

1.3 SQL语言

1.3.1 概述

Structured Query Language 结构化查询语言

其实就是定义了操作所有关系型数据库的规则

1.3.2 SQL的通用语法

查看数据库
show databases;

单行注释： 
-- 注释的内容
多行注释：
/*   和java多行注释一样  */

1.3.3 SQL分类

DDL: data Definition Language,数据定义语言

用来定义数据库、数据库中的表、列等
DQL: Data Query Language,数据查询语言

用来查询数据库、表数据
DML:Data Manipulation Language,数据操作语言

用来对数据库中的表数据进行增删改操作
DCL:Data Control Language,数据控制语言【了解】

用来对数据的访问权限和安全级别进行操作

第二章 DDL

Data Definition Language【数据定义语言】

2.1 操作数据库【CRUD】

2.1.1 创建数据库【C】

create 【创建】
create database 数据库名称;

-- 判断不存在，则创建
create database if not exist 数据库名称;

-- 指定字符集
create database 数据库名称 character set 字符集名称；

2.1.2 查询数据库【R】

-- 查看所有的数据库
show databases;

-- 查看某个数据库的字符集
show create database 数据库名称;

2.1.3 修改数据库【U】

-- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称；

2.1.4 删除数据库【D】

-- 删除数据库
drop database 数据库名称;

-- 判断数据库存在，删除
drop database if exists 数据库名称；

基本操作

-- 使用数据库
use 数据库名称;

-- 查询当前正在使用的数据库
select database();

2.2 操作数据表

2.2.1 创建

-- 创建表
create table 表名(
  列名1 数据类型,
  列名2 数据类型,
  ……
  列名n 数据类型
);

-- mysql在自定义表名和列名时，规则：
必须以字母开头
长度不能超过30字符
不能使用mysql关键字，比如：mysql
只能使用：A-Z,a-z,0-9,$,下划线,但不能使用空格和单引号

-- 常用数据类型
1.整数类型： int(长度)
age int(3)
2.小数类型： double(长度)
score double(5,2)    -- 5表示最多位  2表示保留2位小数，最大值是：999.99
3.日期、时间类型：
date  包含年月日
表示：yyyy-MM-dd

datetime 包含年月日 时分秒 不赋值默认是null
表示：yyyy-MM-dd HH:mm:ss

timestamp:时间戳类型，包含年月日时分秒，其中的秒精确到小数后6位，银行可能会用，不赋值的，默认使用当前系统时间

4.字符类型： char(长度)
 name char(10)   欣宇 前四个字符，后添加6个空格补全
 
5.字符串类型： varchar(长度)
 name varchar(10)  欣宇 分配4个字符，可以省略空间
 

-- 创建学生表
 create table t_student(
     sid int,
     sname varchar(20),
     age int(3),
     birthday date,
     score double(4,1)
 );

2.2.2 查询

-- 查询某个数据库中所有的表名称
show tables;

-- 查询表结构
desc 表名称;

-- 查询创建表的SQL语句
show create table 表名；

2.2.3 修改

-- 修改表名
alter table 表名 rename to 新的表名；
alter table t_student rename to stu;

-- 修改表的字符集
-- 查看表的字符集  show create table 表名
alter table 表名 character set gbk;
 
-- 添加一列 
alter table 表名 add 列名 数据类型；
alter table t_student add sex char(2);

-- 修改列名、数据类型
alter table 表名 change 列名 新列名 数据类型;
alter table 表名 modify 列名 新数据类型；

alter table t_student change sex gender char(2);
alter table t_student change gender sex int;
alter table t_student modify sex char(2);

-- 删除列
alter table 表名 drop 列名;

2.2.4 删除

-- 删除表  
 drop table 表名;
 drop table tec;
 
 -- 存在则删除
 drop table if exists tec;
 
 drop table 表名 ； -- 删除   包括删除表、表结构、表数据

练习：

创建员工表【employee】：工号、姓名、年龄、性别、学历、毕业院校、专业、身份证号码、入职日期、部门编号
创建部门表【dept】:部门编号、部门名称、部门所在地

第三章 DML

数据操作语言

3.1 插入数据

-- 语法
insert into 表名(列名1，列名2，列名3……) values(值1，值2，值3……);
 
-- 示例：
insert into stu(sid,sname,age,birthday,score) values(2,"xinyu",18,"2002-12-23",98.0);
 
-- 插入所有的字段
insert into stu values(值1，值2，值3……);
-- 示例
insert into stu values(3,"zhangyi",18,"2002-10-09",97.8);
 
-- 复制表
create table 新表名 like 被复制的表名；
create table student like stu; --  只是创建了表的结构，没有数据

-- 数据迁移
insert into student select * from stu;

-- 如果不想复制全部数据 sname，age,score
 insert into t_stu(sname,age,score) select sname,age,score from student;

3.2 删除数据

-- 语法1：
delete from 表名 【where 条件】

-- 如果不加条件限制，则删除整个表中所有的记录
delete from student;

-- 语法2：
truncate table 表名； -- 删除表中所有的记录，速度快，不写日志，先删除表，然后再给创建一张一样的表，无法数据回滚
truncate table student;

3.3 修改数据

-- 语法
update 表名 set 列名1=值1，列名2=值2，……【where 条件】

-- 修改一个字段
-- 修改name是xinyu的年龄
 update stu set age=20 where sname="xinyu";
 
 如果修改含有null值的数据 不能用=null 要用is null,is not null 进行判断

第四章 DQL

4.1 简单查询

-- 查询表中的数据
select * from 表名

SELECT * FROM stu;

-- 查询sname,age列
SELECT sname,age FROM stu;
 
-- 查询所有列
SELECT sid,sname,age,birthday,score FROM stu;

SELECT * FROM stu;


-- 查询学生的年龄age 
SELECT age FROM stu;

-- 去重【去掉重复的记录】
SELECT DISTINCT age FROM stu;

-- 起别名 as可以省略
SELECT sname AS 姓名,age AS 年龄 FROM stu;
SELECT sname 姓名,age 年龄 FROM stu;


-- 
SELECT age+2 age FROM stu;

-- 查询成绩，成绩为null,显示0
-- ifnull  判断字段是否为null,第二个参数是该字段为null后的替换值
SELECT IFNULL(score,0)+2  AS score FROM stu;

4.2 条件查询

select 字段列表 from 表列表 【where 条件列表】

运算符：
    >、<、>=、<=、<> 【!=】
    between...and  在一个范围之内
    and 或 &&
    or 或 ||
    not 或 ！
    in(集合)  -- 集合表示多个值，使用逗号隔开
    like :模糊查询
        占位符：
            _:单个任意字符
            %：多个任意字符
        
        
    is null  查询某一列为null
    

-- 条件查询

-- 
select * from student

-- 查询年龄大于20岁的学生信息
select * from student where age>20

-- 查询年龄在20~40之间的学生信息
select * from student where age>=20 and age<=40
SELECT * FROM student WHERE age>=20 && age<=40

select * from student where age between 20 and 40

-- 查询年龄不等于20的所有学生信息
select * from student where age<>20
select * from student where age!=20


-- 查询年龄<20 或 性别是女生的学生信息
select * from student where age<20 or gender='女'

SELECT * FROM student WHERE age<20 || gender='女'

-- 查询成绩=88.0  89.0 77.0的学员信息
select * from student where score=88.0 or score=89.0 or score=77.0

select * from student where score in(77.0,88.0,89.0)

4.3 聚合函数

/*

聚合函数：将一列数据作为一个整体，进行纵向计算的函数
    1.count:计算个数
        -一般选择非空的列：主键
        count(主键)
        count(*):只要一列数据有一个不为null,就算一行记录
            
    2.max:最大值
    3.min:最小值
    4.avg:平均值
    5.sum:计算和

*/

-- 查看一共有多少个学生
select count(sid) from student;
select * from student;


-- 查询学生的最高成绩
select max(score) from student;

-- 查询男生的最高成绩
select max(score) from student where gender='男'

-- 查询年龄小于20的学生的平均成绩
select avg(score) from student where age<20

-- 查询学生的总成绩
select sum(score) from student

4.4 分组查询

select 字段列表 from 表名列表 where 条件列表 group by 分组

/*

聚合函数：将一列数据作为一个整体，进行纵向计算的函数
    1.count:计算个数
        -一般选择非空的列：主键
        count(主键)
        count(*):只要一列数据有一个不为null,就算一行记录
            
    2.max:最大值
    3.min:最小值
    4.avg:平均值
    5.sum:计算和
    
    
分组查询：
    group by :用于对查询的结果分组统计
    having :子句用于限制分组之后的结果显示
    
    where 和 having:
        where :在分组之前进行限定，如果不满足条件，则不参与分组，where后边是不可以使用聚合函数
        having:对分组之后的结果显示进行限定，后边可以使用聚合函数

*/

-- 查看一共有多少个学生
SELECT COUNT(sid) FROM student;
SELECT * FROM student;


-- 查询学生的最高成绩
SELECT MAX(score) FROM student;

-- 查询男生的最高成绩
SELECT MAX(score) FROM student WHERE gender='男'

-- 查询年龄小于20的学生的平均成绩
SELECT AVG(score) FROM student WHERE age<20

-- 查询学生的总成绩
SELECT SUM(score) FROM student

-- 按照性别统计学生的平均分
SELECT gender,AVG(score) avgscore FROM student GROUP BY gender

-- 按照年龄统计学生的最高成绩、平均成绩、最低成绩
SELECT age,MAX(score),AVG(score),MIN(score) FROM student GROUP BY age

-- 按照性别统计年龄大于19岁的学生的平均成绩
SELECT gender,AVG(score) FROM student WHERE age>19 GROUP BY gender

-- 按照性别统计年龄大于19岁的学生的平均成绩,且平均成绩大于90分

SELECT gender,AVG(score)  FROM student WHERE age>19 GROUP BY gender
HAVING AVG(score)>90

4.5 排序

排序：order by 排序字段 排序规则
     排序规则：
        升序：ASC「默认」
        降序：DESC
        
        
select * from student 
--   where age>20 and gender='女' and sname like  '张%' 
--   group by age
--   having score>=80
     order by age ,score desc
     limit 5,5

第五章数据表约束

5.1 概述

对表中的数据进行限定，保证数据正确性、有效性和完整性

分类：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

5.2 主键约束

/*
    主键的作用：
        用来唯一标识数据库表数据中的每一条记录
        
    primary key:
        非空且唯一
        一张表只能有一个字段为主键
    
*/

-- 创建表时，添加主键约束
CREATE TABLE stu(
    id INT PRIMARY KEY,
    NAME VARCHAR(40),
    age INT
);

CREATE TABLE `techer` (
  id INT ,
  NAME VARCHAR(40) ,
  age INT ,
  PRIMARY KEY (id)
) 


CREATE TABLE tec(
    id INT ,
    NAME VARCHAR(40),
    age INT
);

CREATE TABLE techer(
    id INT ,
    NAME VARCHAR(40),
    age INT
);

-- 创建表之后，添加主键
ALTER TABLE tec MODIFY id INT PRIMARY KEY
ALTER TABLE techer ADD PRIMARY KEY (id)


SHOW CREATE TABLE techer

-- 删除主键
ALTER TABLE tec DROP PRIMARY KEY

-- 插入重复的值
INSERT INTO stu VALUES(2,"张雄",18);  -- 主键不可以重复

-- insert into tec values(null,"佳勃",18);

/*
    自动增长：子增长只能使用在主键上
        主键如果让我们自己添加很有可能重复，我们通常希望在每次添加
        新的记录时，数据库可以帮我们自动生成主键字段的值
    
    auto_increment:可以帮我们完成自增
    
*/

-- 创建表时候，指定自增
CREATE TABLE  student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    age INT
);

INSERT INTO student VALUES(10,"张雄1",18);
INSERT INTO student(NAME,age) VALUES("洁涛1",19);

SELECT * FROM student;

-- 删除自增
ALTER TABLE student MODIFY id INT


-- 修改自增
CREATE TABLE peo(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
) AUTO_INCREMENT=2000;

INSERT INTO peo(NAME) VALUES("湘东");

SELECT * FROM peo

-- 给已经创建的表添加主键生成策略自增
ALTER TABLE techer  MODIFY id INT AUTO_INCREMENT;


INSERT INTO techer(NAME,age) VALUES("桂燕1",19);

SELECT * FROM techer;

-- 修改techer表，主键从1000开始
ALTER TABLE techer AUTO_INCREMENT=1000;

-- delete 和 truncate 对自增长的影响
/*
    delete ：删除所有的记录之后，对自增长没有影响
    truncate:删除以后，自增又重新开始
*/

DELETE FROM techer; 
TRUNCATE TABLE techer;

SELECT * FROM techer;
INSERT INTO techer VALUES(NULL,"张一",19);

5.3 非空约束

/*
    非空约束：
        数据表的某一列不能为null
*/

-- 创建表的时候添加非空约束
CREATE TABLE tab_1(
    id INT,
    NAME VARCHAR(20) NOT NULL,  -- name 设置了非空约束
    tel VARCHAR(11) NOT NULL
);

-- 创建表后，添加非空约束
ALTER TABLE tec MODIFY NAME VARCHAR(40) NOT NULL;

-- 删除字段的非空约束
ALTER TABLE tec MODIFY NAME VARCHAR(40) ;

-- 添加数据
INSERT INTO tab_1 VALUES(2,NULL,"18312345678");
SELECT * FROM tab_1

5.4 唯一约束

/*
    唯一约束：unique
        数据表中的添加该约束的列不能出现重复的值，必须保证值的唯一性
        
        
    tips:
        unique：不能有重复的值，null,没有数据，不存在重复的问题，所以说
            唯一约束是可以添加重复的null值

    主键约束与唯一约束：
        主键：一张表只能有一个主键，不能为null，可以添加自增长
        唯一：一张表可以有多个唯一约束,可以有多个null值，不可以添加自增
*/

-- 基本语法
-- 在创建表时，添加唯一约束
CREATE TABLE tab_2(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40) UNIQUE
);

-- 在创建表之后，添加唯一约束
CREATE TABLE tab_3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

ALTER TABLE tab_3 MODIFY NAME VARCHAR(20) UNIQUE;

-- 删除列的唯一约束
ALTER TABLE tab_3 MODIFY NAME VARCHAR(20);
ALTER TABLE tab_2 DROP INDEX NAME;  -- 没有数据类型


-- 添加数据
INSERT INTO tab_2 VALUES(NULL,"桂燕");

SELECT * FROM tab_2;

5.5 默认值


/*
    为某个字段设置默认值
*/
-- 基本语法
CREATE TABLE tab_4(
    id INT PRIMARY KEY ,
    NAME VARCHAR(20) NOT NULL,
    sex VARCHAR(10) DEFAULT '男'      -- default 默认值为男
);

-- 向表中添加记录
INSERT INTO tab_4 VALUES(3,"湘东2",DEFAULT);

SELECT * FROM tab_4;

5.5 外键约束

foreign key

/*
    外键约束：foreign key
*/

CREATE TABLE emp(
    empno INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    ename VARCHAR(40), -- 员工姓名
    age INT, -- 年龄
    dname VARCHAR(40),    -- 部门名称
    Ioc  VARCHAR(100)   -- 部门所在地
);

-- 添加数据
INSERT INTO emp VALUES
(NULL,"洁涛",19,"基础研发部","陕西西安"),
(NULL,"佳勃",20,"基础研发部","陕西西安"),
(NULL,"张雄",19,"基础研发部","陕西西安"),
(NULL,"欣宇",18,"项目开发部","北京"),
(NULL,"张一",19,"项目开发部","北京"),
(NULL,"湘东",20,"项目开发部","北京")

SELECT * FROM emp;

单表存储数据存在的缺点：

1.数据重复，出现冗余数据

2.后期出现增删改时，存在问题

解决方案：拆分表，拆成2张，一张员工表，一张部门表

-- 部门表
CREATE TABLE dept(
    deptno INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
    dname VARCHAR(40),    -- 部门名称
    Ioc  VARCHAR(100)   -- 部门所在地
);

INSERT INTO dept VALUES(NULL,"项目开发部","北京"),(NULL,"基础研发部","陕西西安");

-- drop table emp;
-- 员工表
CREATE TABLE emp(
    empno INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    ename VARCHAR(40), -- 员工姓名
    age INT, -- 年龄
    deptno INT
);

INSERT INTO emp VALUES
(NULL,"宇航",19,3)
,(NULL,"佳勃",20,1)
,(NULL,"张雄",19,2)
,(NULL,"欣宇",18,1)
,(NULL,"湘东",18,2)
,(NULL,"桂燕",18,1)

SELECT * FROM dept;
SELECT * FROM emp;

外键约束的概念

用于定义主表「一方」和从表「多方」之间的关系，外键约束要定义在从表上，主表则必须具有主键约束，从而保证数据的正确性、有效性和完整性

主表：一方，用来约束别人的表

从表：多方，被别人约束的表


-- 创建表，添加外键约束
CREATE TABLE 表名(
    列名 数据类型,
    外键列
    CONSTRAINT 外键名称 FOREIGN KEY(外键列的名称) REFERENCES 主表名称(主表列名称)

);

-- drop table emp;
CREATE TABLE emp(
    empno INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    ename VARCHAR(40), -- 员工姓名
    age INT, -- 年龄
    deptno INT,  -- 部门编号
    -- 创建外键
    CONSTRAINT emp_fk FOREIGN KEY (deptno) REFERENCES dept(deptno)
);


INSERT INTO emp VALUES
(NULL,"宇航",20,3)
,(NULL,"张雄",19,2)
,(NULL,"欣宇",18,1)
,(NULL,"湘东",18,2)
,(NULL,"桂燕",18,1)

第六章数据库的备份和还原

6.1 命令行

备份：

mysqldump -u用户名 -p密码数据库名称 >保存的路径

还原：

1.登录数据库

2.创建数据库

3.使用数据库

4.执行文件

source 文件路径

6.2 图形化界面备份

省略

第七章三大范式

第一设计范式：1NF

要求：数据表中的每个字段都是不可再分割的

第二设计范式：2NF

要求：数据表中不存在非关键字段对任意一候选字段的部分函数依赖

解释：函数依赖、函数关系

所谓的函数依赖：指的是通过某几个字段可以成功推演出某个字段

create table 订单表(
    订单编号 int,
    订单日期 date,
    商品单价 int,
    商品数量 int,
    商品总价 int
);

商品总价=商品单价*商品数量

第三设计范式：3NF

设计原则：数据表中不存在非关键字段对任意一候选关键字段的传递函数依赖

员工编号	姓名	职位	部门编号	部门名称	部门所在地
1001	张一	项目经理	d01	项目开发部	西安

可以通过员工编号知道他的部门名称，但是通过部门名称，也可以找到部门所在地，这三个字段之间就有传递关系，所以这样的设计不符合第三范式

第一范式：是单表设计原则

第二范式：是多对多的关系

第三范式：是一对多的关系

在实际开发过程中，第三范式一定是优先考虑

练习：

-- 设计表存储以下信息

-- 「员工编号，员工姓名，职位，该员工上级领导编号，入职时间，薪资，奖金，部门名称，部门所在地，薪资等级」

第八章多表查询

多表查询是基于两张或两张以上的表的查询

笛卡尔积：两个集合A、B,所有组成情况

要完成多表查询需要消除无用的数据

多表查询的规定：

多表查询的条件是至少不能小于表的个数减1，所以两张表至少有一个条件，3张表至少两个条件，避免笛卡尔集的出现，笛卡尔集这个人发现的。

8.1 连接查询

/*
    连接查询
    分类：
        自连接、内连接、外连接、交叉连接
*/

-- 1.自连接 ：同一张表的连接查询，也叫自身连接
-- 查询SMITH员工的上级领导的姓名
SELECT e.ename,b.ename FROM emp e, emp b WHERE e.mgr=b.`empno`
AND e.ename='SMITH';

SELECT * FROM emp;

-- 2.内连接也分为等值连接、不等值连接、自然连接
-- 等值连接：关联条件的运算符是等号来连接 JOIN
-- inner join 替换， on替换where

-- 查询所有员工的姓名以及员工的部门名称
-- 隐式内连接
SELECT e.ename,d.dname FROM emp e,dept d
WHERE e.deptno=d.`deptno`

-- 显式内连接
SELECT  e.ename,d.dname FROM emp e INNER JOIN dept d  
ON e.`deptno`=d.`deptno`

-- 不等值连接
-- 查询员工姓名以及员工的薪资等级
SELECT e.ename,s.grade FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal

SELECT e.ename,s.grade FROM emp e 
INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

/*
    自然连接：在两张表中查找数据类型和字段名都相同的字段，
    然后自动连接起来,也就是不用指定条件它们可以自动的查询所有
    符合的数据【了解】
*/  

-- 查询员工姓名、部门名称
SELECT e.ename,d.dname FROM emp e NATURAL JOIN dept d;

-- 查询员工姓名、部门名称以及员工的薪资等级
SELECT e.ename,d.dname,s.`grade`  FROM emp e
INNER JOIN dept d ON e.`deptno`=d.`deptno`
INNER JOIN salgrade s ON e.sal BETWEEN s.`losal` AND s.`hisal`

-- 1.查询职位是SALESMAN且薪资等级是2的员工信息
-- 2.查询领导是KING的员工姓名以及所在部门名称
-- 3.查询和CLARK同岗位的员工姓名以及薪资
-- 4.查询高于各部门的平均薪资的员工姓名以及薪资
-- 5.查询在1982年以后入职的员工姓名、薪资、所在部门名称以及薪资等级
SELECT * FROM emp;

/*
    外连接：左外连接和右外连接
    左外连接：
        A left join B on A.key=B.key
        查询的是左表所有数据以及其交集部分
        
    右外连接：
        A right join B on  A.key=B.key
        查询的是右表所有数据以及其交集部分
*/

-- 查询所有员工信息以及员工所在部门信息
SELECT e.*,d.* FROM emp e 
LEFT JOIN dept d ON e.`deptno`=d.`deptno`
-- 查询所有部门信息以及部门的员工信息
SELECT d.*,e.* FROM dept d
LEFT JOIN emp e ON d.`deptno`=e.`deptno`

-- 交叉连接：笛卡尔集【了解】
SELECT COUNT(*) FROM emp e CROSS JOIN dept d

8.2 子查询

/*
    子查询：
        嵌套查询，select中包含select
        子查询出现的位置
            where后：作为条件的一部分
            from后：作为被查询的一张表
            
    子查询的结果集的形式：
        单行单列【用于条件】
        单行多列【用于条件】
        多行单列【用于条件】
        多行多列【用于表】

*/
-- 单行单列： 返回的结果只有一个数据
-- 查询工资大于MARTIN员工的所有员工信息

-- 查询MARTIN员工薪资
-- select sal from emp where ename='MARTIN'

select * from emp where sal>
(
    select sal from emp where ename='MARTIN'
)

-- 查询与MARTIN员工同部门的所有员工信息
select * from emp 
where deptno =(
    select deptno from emp where ename='MARTIN'
)

-- 查询工资大于30部门平均薪资的所有员工信息

-- 1.查询30部门的平均薪资
select avg(sal) from emp where deptno=30

select * from emp where sal>
(
    SELECT AVG(sal) FROM emp WHERE deptno=30
)

-- 查询大于30部门所有人薪资的员工信息
select * from emp where sal>
(
    select max(sal) from emp where deptno=30
)

select * from emp where sal>
all(select sal from emp where deptno=30)

-- 查询比30部门任意一个员工的薪资高的员工信息
select * from emp where sal>
(
    select min(sal) from emp where deptno=30
)

select * from emp where sal>
any(select sal from emp where deptno=30)

-- 多行多列：子查询的结果有多行多列，当作一张表
-- 查询大于自己部门平均薪资的员工信息

-- 1.查询出各个部门的平均薪资
select deptno,avg(sal) from emp group by deptno

select e.*  from emp e,
(
    select deptno,avg(sal) asal from emp group by deptno
) ds where e.deptno=ds.deptno  and e.sal>ds.asal

练习：

-- 1.查询职位是SALESMAN且薪资等级是2的员工信息
-- 2.查询领导是KING的员工姓名以及所在部门名称
-- 3.查询和CLARK同岗位的员工姓名以及薪资
-- 4.查询高于各部门的平均薪资的员工姓名以及薪资
-- 5.查询在1982年以后入职的员工姓名、薪资、所在部门名称以及薪资等级
-- 6.查询所有和MARTIN同部门同岗位的员工信息

-- 查询所有和MARTIN同部门同岗位的员工信息
SELECT * FROM emp
WHERE deptno=(
    SELECT deptno FROM emp WHERE ename='MARTIN'
)
AND job =
(
    SELECT job FROM emp WHERE ename='MARTIN'
)

SELECT * FROM emp WHERE (deptno,job)=
(
    SELECT deptno,job FROM emp WHERE ename='MARTIN'
)

第九章 MySQL事务

9.1 概述

TCL语言：Transaction Control Language事务控制语言

事务：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

事务的特点-ACID属性【面试题】

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

9.2 事务的创建

隐式事务：事务就没有明显的开启事务和结束事务标记

insert、update、delete语句

SHOW VARIABLES LIKE 'autocommit';

-- 结果是 on 默认自动提交是开启的

事务的提交两种方式：

自动提交、手动提交

MySQL数据库中的事务默认是自动提交

Oracle数据库默认是手动提交，需要先开启事务，再提交

事务的提交方式：

-- 查看事务的默认提交方式
select @@autocommit;  -- 1. 代表自动提交  0 代表手动提交

-- 修改默认提交方式
set @@autocommit=0;
update account set money=20;

显式事务：事务具有明显的开启和结束的标记

步骤：

1.开启事务
set @@autocommit=0;
START TRANSACTION;  
2.编写事务中的执行SQL
-- 洁涛给佳勃转账 500元
UPDATE account SET money=money-500 WHERE id=1;
-
UPDATE account SET money=money+500 WHERE id=2;
3.如果执行成功，就提交commit

4.如果有任何一条SQL语句执行失败，则回滚rollback

回滚：就是恢复到事务开启之前的最原始的状态

SELECT * FROM account



-- show variables like 'autocommit';

-- 1.开启事务
SET autocommit=0;
START TRANSACTION;  

-- 2. 编写执行sql语句【事务】
-- 洁涛给佳勃转账 500元
UPDATE account SET money=money-500 WHERE id=1;
- 
UPDATE account SET money=money+500 WHERE id=2;

-- 3. 结束事务
-- commit;
-- 或
ROLLBACK;  -- 回滚事务

9.3 事务的隔离级别

9.3.1 概述

多个事务之间是隔离的，相互独立的，如果多个事务操作同一批数据，会出现一些问题，比如脏读、虚读、幻读等问题，我们可以设置不同的隔离级别去解决。

存在的问题：

脏读：一个事务读取到令一个事务中没有提交的数据

不可重复读【虚读】：在同一个事务中，两次读取到的数据不一样

幻读：一个事务操作【DML】数据库中的所有记录,另一个事务添加了一条数据，则第一个事务查询不到自己的修改

9.3.2 隔离级别

read uncommitted; -- 读未提交

出现的问题：脏读、不可重复读、幻读

read committed; -- 读已提交

出现的问题：不可重复读、幻读

repeatable read; -- 可重复读【MySQL默认级别】

出现的问题：幻读

serializable；-- 串行化

上述所有问题解决

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

-- 隔离级别的查询
select @@tx_isolation; 

-- 更改事务的隔离级别
set global transaction isolation level 级别字符串；

serializable，可串行化，这是隔离的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读的问题，简言之：它是在每个读数据行上加共享锁。

通俗的讲，假如两个事务都操作同一个数据行，那么这个数据行就会被锁定，只允许第一个读取操作到数据行的事务优先操作，只有当事务提交了，数据行才会解锁，后一个事务才能成功操作这个数据行，否则只能一直等待。

第十章视图

10.1 概述

视图又称为虚拟表，和普通表的使用是一样的，MySQL5.1+之后的新特性

使用场景：

多个地方用到同样的查询结果
该查询结果使用的SQL语句比较复杂

视图的好处：

重用SQL;
简化复杂的查询，不必知道它的具体查询细节；
保护数据；

10.2 使用视图

创建视图

-- 语法
create view 视图名称 as 查询语句；

-- 创建视图
CREATE VIEW emp_view AS 
SELECT e.*,d.`dname`,s.`grade` FROM emp e 
INNER JOIN dept d  ON d.`deptno`=e.`deptno`
INNER JOIN salgrade s ON e.sal BETWEEN s.`losal` AND s.`hisal`

-- 使用视图
select * from emp_view;

tips:视图MySQL不允许有子查询

CREATE VIEW my_view1 AS 
SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno

CREATE VIEW my_view2 AS
    SELECT e.*,d.`dname`,s.`grade`,ROUND(a.avg_sal,2) AS avg_sal FROM emp e 
     INNER JOIN dept d  ON d.`deptno`=e.`deptno`
     INNER JOIN salgrade s ON e.sal BETWEEN s.`losal` AND s.`hisal`
    INNER JOIN 
    my_view1 a ON a.deptno=e.`deptno`

select * from my_view2;

修改视图

create or replace view 视图名称 as 查询语句；
或者
alter view 视图名称 as 查询语句；

CREATE OR REPLACE VIEW emp_view AS
SELECT * FROM salgrade;

ALTER VIEW emp_view AS SELECT * FROM dept;

删除视图

-- 语法
drop view 视图名，视图名……

DROP VIEW my_view1,emp_view;

查看视图

-- 和普通表一样
desc 视图名；
show create view 视图名；

视图数据的更新

-- 向视图中添加数据
insert into 视图名称 values(值……);

INSERT INTO emp_view VALUES(8888,"欣宇","基础研发",7902,"2022-06-13",8000.0,1000.0,30);

-- 删除视图中的数据
delete from 视图名 where 条件列表；
delete from emp_view where empno=8888;

注意：视图一般不做更新操作，只读数据；

10.3 视图与表的区别

表需要占用磁盘空间，视图不需要

视图不能添加索引

视图提高安全性，比如：不同用户查看不同的视图

视图：只是保存了SQL逻辑；表保存了数据

第十一章函数

11.1 自定义函数

/*
    函数：
        一组预先编译好的SQL语句的集合，理解成批处理
        1.提高代码的重用性
        2.简化操作
        3.减少了编译次数并且减少了和数据库服务的连接次数，提高效率

*/

-- 创建语法：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

/*
    参数列表 包括两部分：
    参数名 参数类型
    
    函数体：
        肯定有return 语句，如果没有会报错
        
        1.函数体如果仅有一句，则可以省略begin end
        2.使用delimiter语句设置结束标记

*/

-- 调用语法：
SELECT 函数名(参数列表)

-- 无参有返回值的函数
-- 写一个函数，返回员工个数
DELIMITER $$
CREATE FUNCTION my_fun1()  RETURNS INT
 BEGIN
        
    DECLARE total INT DEFAULT 0;   -- 定义变量
        
    SELECT COUNT(empno) INTO total  -- 赋值
    FROM emp;
     
     RETURN total;
    
 END  $$
 
 -- 调用函数
 SELECT my_fun1();
 
 -- 有参有返回值
 -- 函数：根据员工姓名，获取员工的薪资
 
 DELIMITER $$
 CREATE FUNCTION myfun2_byname(empName VARCHAR(40))  RETURNS DOUBLE
 BEGIN
        SET @sal=0;   -- 定义用户变量
        
        SELECT sal INTO @sal   -- 赋值
        FROM emp WHERE ename=empName;
        
        RETURN @sal;
    
 END$$
 
 -- 调用函数
 SELECT myfun2_byname('SMITH');
 
 -- 练习：根据员工姓名，获取员工的薪资等级

11.2 常用函数

# 字符函数
 # 1.length 获取参值的字符个数
 select length("asdddfggg");
 
 # 2.concat 拼接字符串
 select concat(ename,"-",job)  from emp;
 
 # 3.substr/substring  注意：索引是从1开始
 #  substr(str,pos):从索引处开始到字符串结尾
 select substr("今天学习MySQL数据库高级部分",9)  output
 # substr(str,begin,end):从索引处截取指定长度个字符
 SELECT SUBSTR("今天学习MySQL数据库高级部分",1,3) output
 
 select ename,substr(ename,2,2) as es from emp;
 
 # 4.instr 返回字符串中第一次出现的索引，如果不存在，返回0
 select instr("今天学习MySQL数据库高级部分","MySQL");
 
 # 5.lpad 用指定的字符实现左填充指定长度
 select lpad("MySQL",9,"**");
 
 # 6.rpad 右填充
 select rpad("MySQL",12,"abc");
 
 # 7.upper 转大写
 select upper(ename) from emp;
 # 8.lower 转小写
 select lower(ename) from emp;
 # 9.trim 去空
 select length(trim("                MySQL           "));
 # 10.replace 替换
 select replace("今天学习MySQL数据库高级部分","MySQL","Java");
 
 
 # 数学函数
 # round  四舍五入 
 select round(12.3456,3) 
 
 # ceil 向上取整
 select ceil(3.14)
 SELECT CEIL(-3.14)
 
 # 向下取整
 select floor(3.14)
select floor(-3.14) 

# truncate 截断
select truncate(1.23445,3);

# 日期函数
# 1.now() 获取当前系统时间
select now();

# curdate 返回当前系统时间，只到日期，不包含时间
 select curdate()

# curtime 返回当前系统时间,只是时间，没有日期
select curtime()

# datediff 获取两个时间之间的天数
select datediff(now(),"2022-06-01")

# 获取日期的年部分
select year(now()),month(now())

# str_to_date:将日期格式的字符串转换为指定格式的日期
SELECT STR_TO_DATE("06-13-2022",'%m-%d-%Y')
# date_format:将日期转换为字符串
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日")
 
 # 其他函数
--  version()、database()、user()
select version();
select user();
select database();

第十二章存储过程

/*
    存储过程：
        一组预先编译好的SQL语句的集合，理解成批处理
        1.提高代码的重用性
        2.简化操作
        3.减少了编译次数并且减少了和数据库服务的连接次数，提高效率
*/

# 1.创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体【一组合法的SQL语句】
END 

/*
    参数列表包含三部分：
    参数模式 参数名 参数类型
    IN ename varchar(40)
    
    参数模式：
        IN: 该参数可以作为输入，也就是说调用该存储过程的时候需要传入值
        OUT:该参数可以作为输出，也就是该参数可以作为返回值
        INOUT:该参数既可以作为输入，也可以作为输出，也就是该参数既可以传入值，也可以返回值

*/

# 调用存储过程
CALL 存储过程名(实参列表);

# 无参无返回值
# 1.向account表中添加3条数据

DELIMITER $
CREATE PROCEDURE mypro1_add()
BEGIN
    INSERT INTO account VALUES
    (NULL,"aaa",500.0),
    (NULL,"bbb",500.0),
    (NULL,"ccc",500.0);
END $

SELECT * FROM account;

# 调用存储过程
CALL mypro1_add();

# 创建带参的存储过程
# 案例：根据员工姓名查询对应的薪资信息

DELIMITER $
CREATE PROCEDURE mypro2_find(IN NAME VARCHAR(40))
BEGIN
      SELECT sal FROM emp WHERE ename=NAME;
END $

# 调用存储过程
CALL mypro2_find("SMITH");

# 案例：实现根据员工姓名和岗位查看是否退休【满40年，退休】
DELIMITER $
CREATE PROCEDURE mypro3_is(IN NAME VARCHAR(40))
BEGIN
    
    DECLARE age INT DEFAULT 0;  -- 声明并初始化
    SELECT  (YEAR(NOW())-YEAR(hiredate))  INTO age  -- 赋值
    FROM emp 
    WHERE ename=NAME ;
    
    SELECT IF(age>40,'已退休','在职');
END $

SELECT * FROM emp;

# 调用
CALL mypro3_is('ADAMS');

# 创建带out模式的存储过程
# 案例： 根据员工姓名，返回薪资等级

DELIMITER $
CREATE PROCEDURE mypro4_get_sal_grade(IN NAME VARCHAR(40),OUT sgrade INT)
BEGIN
    
    SELECT s.grade INTO sgrade
    FROM emp e,salgrade s
    WHERE e.sal BETWEEN s.losal AND s.hisal
    AND e.ename =NAME;

END $
SELECT * FROM emp;
# 调用
CALL mypro4_get_sal_grade('JONES',@sgrade);
SELECT @sgrade;

# 带inout模式参数的存储过程
# 案例： 传入两个值num1,num2,翻倍并返回
DELIMITER $
CREATE PROCEDURE mypro5_doublenum(INOUT num1 INT,INOUT num2 INT)
BEGIN
    SET num1=num1*2;
    SET num2=num2*2;
END $

# 调用
SET @m=12;
SET @n=13;
CALL mypro5_doublenum(@m,@n);
SELECT @m,@n

# 删除存储过程
# 语法 drop procedure 存储过程名
DROP PROCEDURE mypro1_add;

/*
    函数与存储过程：
    存储过程：可以有0个返回值，也可以有多个返回值，适合批量做添加、批量更新操作
    函数：有且仅有1个返回值，适合做批量处理数据后返回一个结果
*/

第十三章流程控制

/*
    顺序结构：
    分支结构：
    循环结构：
    
*/
-- 分支结构：
/*
    1. if函数:实现简单的双分支
        if(表达式1，表达式2，表达式3)
        
    2.case结构
    2.1 类似于java中的switch语句
        语法：
            case 变量|字段|表达式
            when 要判断的值 then 语句1;
            when 要判断的值 then 语句1;
            ……
            else 要返回的值n 或语句n;
            end case;
            
    2.2 类似于java中的多重if语句
        语法：
            case 
            when 要判断的条件1 then 要返回的值1；
            ……
            else 要返回的值n或语句n;
            end case;   
            
    3.if结构【用在begin...end中】
        if 条件1 then 语句1；
        elseif 条件2 then 语句2；
        ……
        else 语句n;
        end if;
        
    
        
*/
# 案例：传入一个成绩，根据成绩显示等级90-100 A  80-90 B ……
DELIMITER $
CREATE PROCEDURE test_if(IN score INT) 
BEGIN 
    CASE 
    WHEN score>=90 AND score<=100 THEN SELECT  'A';
    WHEN score>=80  THEN  SELECT 'B';
    WHEN score>=70  THEN  SELECT 'C';
    WHEN score>=60  THEN  SELECT 'D';
    ELSE  SELECT 'E';
    END CASE;
END $ 



DELIMITER $
CREATE FUNCTION myfun3_if(score INT) RETURNS CHAR
BEGIN
    IF score >=90 AND score<=100 THEN RETURN 'A';
    ELSEIF score>=80 THEN RETURN 'B';
    ELSEIF score>=70 THEN RETURN 'C';
    ELSEIF score>=60 THEN RETURN 'D';
    ELSE RETURN 'E';
    END IF;
END $ 

DROP FUNCTION test_if;

-- 调用函数
SELECT myfun3_if(88);


/*
    循环结构：
    while、loop、repeat
    
    
    1.while
    
    【语法：】
    while 循环条件 do
        循环体;
        
    end while
        
*/

# 案例：批量插入，根据传入的次数，往account表中添加多条记录

DELIMITER $
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN

        DECLARE i INT DEFAULT 1;
        WHILE i<=insertCount DO
            INSERT INTO account VALUES(NULL,"欣宇",5000.0);
        SET i=i+1;
        END WHILE;
END $

CALL pro_while(10);

-- drop procedure pro_while;

SELECT * FROM account;




-- drop function test_if;

# 调用函数
SELECT test_if(88);

第十四章 DCL

数据控制语言，操作管理用户、授权

14.1 管理用户

添加用户

-- 语法：
create user 用户名@'主机ip地址' identified by '密码'；

create user xinyu@'127.0.0.1'  identified by '123';

create user xinyu@'%' identified by '123';  -- 用户可以在任意ip地址上登录

查看用户

-- 必须切换到mysql数据库下

use mysql;

select * from user;

select host,user,password from user;

删除用户

-- 语法：
drop user 用户名@'主机名/IP地址'；

drop user zhoukun@'127.0.0.1';

修改用户密码

-- 语法：
update user set password=password('新密码')  where user='用户名'；

update user set password=password('333')  where user='xinyu';

set password for 用户名@'主机名'= password('新密码')   


-- 注意：修改完成后进行刷新权限【必须】
flush privileges;

修改root密码

用户root的密码忘记
1）.停掉数据库服务   net stop mysql;
2).使用无验证方式启动 mysql  命令： mysqld --skip-grant-tables
3).打开新的cmd窗口，直接输入mysql命令，就可以无密码登录
4).user mysql 切换到mysql数据库
5).update user set password=password('新的密码') where user='用户名'；
6).关闭两个窗口
7).打开任务管理器，手动结束mysqld.exe进程
8).启动mysql服务
9).使用新的密码进行登录mysql数据库

14.2 授权管理

查看权限

-- 语法
show grants for 用户名@'主机IP地址'；

-- 查看指定用户的权限有哪些
show grants for xinyu@'127.0.0.1';

授予权限

-- 语法
grant 权限列表 on 数据库.表名 to 用户名@'主机地址'；

-- 赋给xinyu用户 查询test12.emp表的权限
grant select on test12.emp to xinyu@'127.0.0.1';

-- 赋给用户多个权限
grant select,update,insert,delete on test12.emp to xinyu@'127.0.0.1';

-- 给用户授予所有的权限
grant all on *.* to 用户名@'主机名';

撤销权限

-- 语法：
revoke 权限列表 on 数据库.表名 from 用户名@'主机名';

-- 撤销用户所有的权限
revoke all on 数据库.表名 from 用户名@'主机名';