数据库(SQL)_sql数据库

数据库的基本概念

1.数据库的英文单词：DataBase 简称：DB
2.什么是数据库？
用于存储和管理数据的长仓库。
3.数据库的特点：
     1.持久化存储数据的，其实数据库就是一个文件系统
     2.方便存储和管理数据
     3.使用了同一的方式操作数据库—SQL
4.常见的数据库软件：例如：Oarcle和MySQL

MySQL数据库软件
开元免费的数据库，小型的数据库，已经被Oracle收购了MySQL6.x版本也开始收费

MySQL服务启动和关闭

打开服务器页面：
方法一：右键点击电脑，点开管理
方法二：在cmd控制台输入services.msc

在cmd控制台打开和关闭服务器：(控制台默认编码是gbk，数据是u8)
net start mysql：启动mysql的服务
net stop mysql：关闭mysql服务
如果想要在cmd控制台操作管理，直接操作（会报错）：

需要以管理员的身份进入cmd：

MySQL的登录和退出

登录：
1.mysql -uroot -p密码
2.mysql -hip -uroot -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
退出：
1.exit
2.quit

通配符：
所有权限：all
所有表或者所有库：*
所有：所有主机

SQL

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

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。(在控制台需要;结尾，回车如果有分号表示结束，没有则下一行。SQLyog上面可以不加，因为它是选中执行)
2.可使用空格和缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.3种注释：
       1.单行注释：
              （1）–(空格)注释内容
       如果不加分号可以还是可以运行啊？
       但是没有结束，还需要让你继续运行，然后你再输入一个空格就错误了。
               （2）#注释内容（mysql特有）这个#后面可以不用加空格。
       2.多行注释:
       2.多行注释：/* 注释 */（和java里面的注释一样）

SQL分类

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

DDL：操作数据库、表

1.操作数据库(database)：CRUD
     1.C(Create)：创建
    创建数据库：create database 数据库名称;
    如果再创建已经存在的数据库？那么就要报错。
    所以要创建数据库前判断有没有该数据库，有不创建，没有不创建：create database if not exists 数据库名称;
    指定字符集：create database 数据库名称 character set 字符集名称(例如：gbk)
    创建db4数据库，判断是否存在，并指定字符集为gbk：create database if not exists db4 character set gbk;
     2.R(Retrieve)：查询
    查询所有数据库的名称：show databases;
    查询某个数据库的字符集（查询某个数据库的创建语句）(下载mysql的时候默认的是utf8)：show create database 数据库名称;
     3.U(Update)：修改
    修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
     4.D(Delete)：删除
    删除数据库：drop database 数据库名称;
    判断数据库存在，存在再删除：drop database if exists 数据库名称;
     5.使用数据库
    查询当前正在使用的数据库名称：select database();
    使用数据库：use 数据库名称;

2.操作表（table）
     1.C(Create)：创建
(1).语法：create table 表名

create table 表名(
	列名1 数据类型1,
	列名2 数据类型2,
	...
	列名n 数据类型n
);
1
2
3
4
5
6

数据类型：
a.  int ：整数类型，例如：age int,

b.  double：
score double(5,2),（5表示这个数一共有5位，2表示这个数小数点后有2两位）

c.  date：日期，只包含年月日
yyyy-MM-dd

d.  datetime：日期，包含年月日时分秒：
yyyy-MM-dd HH:mm:ss

e.  timestamp：时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss

f.  varchar：字符串
name varchar(20)：姓名最大20个字符
zhangsan  8个字符
张三  2个字符

create table student(
	id int,
	name varchar(32),
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);
1
2
3
4
5
6
7
8

     2.R(Retrieve)：查询（和数据库大同小异）
    查询某个数据库中所有的表名称：show tables;
    查询表结构：desc 表名;
    查询某个表的字符集（查询某个表的创建语句）(下载mysql的时候默认的是utf8)：show create table 表名;

     3.U(Update)：修改
1.修改表名：alter table 表名 rename to 新的表名;（不能想当然的认为可以使用if exsits）

2.修改表的字符集：alter table 表名 character set 字符集名;

3.添加一列：alter table 表名 add 列名 数据类型;

4.修改列名 类型
第一种方式：alter table 表名 change 列名 新列名 新数据类型;
第二种方式：alter table 表名 modify 列名 新数据类型;

5.删除列：alter table 表名 drop 列名;

     4.D(Delete)：删除
删除表：drop table 表名;
判断存在，存在再删除：drop table if exists 表名;

     5.复制表：create table 创建的表 like 已有的表;：将创建的表复制为已有的表。

DML：增删改表中数据

客户端图形化工具：SQLYog
在SQLYog中执行不看顺序，一般都是一行一行执行。

1.添加数据：insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意：
(1).列名和值要一一对应
(2).如果表名后，不定义列名，则默认给所有列添加值，但是还是要一一对应，后面的值必须要写完：inset into 表名 values(值1,值2,...值n);这里的n必须和列的个数相等。
(3).处理数字类型，其他类型需要使用引号(单双都可以)引起来，例如日期：

INSERT INTO stu VALUES(3,'张三丰',17,99.9,'1893-11-11',NULL);
1

2.删除数据：delete from 表名 where 条件;
例如将id=1的删除：

DELETE FROM stu WHERE id=1;
1

注意：
(1).如果不加条件，则删除表中所有记录。
(2).如果要删除所有的记录
a.delete from 表名;：不推荐使用，有多少条记录就会执行多少次删除操作。
b.truncate table 表名;：先删除表，然后再创建一张一样的表

3.修改数据：update 表名 set 列名1=值1,列名2= 值2,...where 条件;
例如：（将id=3的那一列的age改为117）

UPDATE stu SET age=117 WHERE id=3;
1

注意：如果不加任何where条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

select * from 表名;：查看该表中所有的数据(记录)（*可以想象成所有的意思，这里可以换成列名列表都可以，*不方便阅读，但是很简便）
例如：select name,age from student;
一.语法：
select：字段列表
from：表名列表
where：条件列表
group by：分组字段
having：分组之后的条件
order by：排序
limit：分页限定

二.基础查询：
1.多个字段的查询(将上面的所有数据查询改一下)

select 字段名1,字段名2... from 表名;
1

注意：如果查询所有字段，则可以使用*来代替字段列表

例如：select name,age from student;

2.去除重复
去除重复的结果集：

select distinct 列名 from 表名;
1

注意：例如：select distinct name,address from student;有多条列名的去重，需要指定列名都相同才能去除，有一个不同都不会去除。

3.计算列
例如计算math和English分数之和：

select name,math,English,math+english from student;
1

弊端：如果有NULL计算的结果都为NULL

改进：

select name,math,English,math+ifnull(english,0) from student;
1

(ifnull(a,b)：如果a为null，那么a就等于b)
但是这样一来，那一列的名字就是math+ifnull(english,0)：就显得很难看，需要起别名

4.起别名
例如在studnt表中，操作name和math：
第一种：加as：

select name as 名字,math as 数学 from student;
1

第二种：什么也不加，就直接空格：

select name 名字,math 数学 from student;
1

三。条件查询
1.where子句后面跟条件
2.运算符

> < <= >= = <>//和!=一样的含义（不等于）
BETWEEN...AND
IN(集合)
LIKE
IS NULL
and 或 &&
or 或 ||
not 或 !
//java语言中的好多逻辑运算符都可以使用，但是不推荐，直接使用英文
1
2
3
4
5
6
7
8
9

例如：
查询年龄等于20岁的：

select * from student where age = 20;
1

查询年龄不等于20岁：

select * from student where age !=20;
select * from student where age <> 20;
1
2

查询年龄大于等于20小于等于30：

select * from student where age >= 20 && age<=30;
select * from student where age >= 20 and age<=30;
select * from student where age between 20 and 30;
1
2
3

查询年龄22岁或者19岁或者25岁的信息

select * from student where age=22 or age=19 or age=25;
select * from student where age in(22,18,25);
1
2

查询英语成绩为null

select * from student where English =null;//错误，null值是不能使用=（!=）判断的
select * from student where English is null;
1
2

查询英语成绩不为null

select * from student where English is not null; 
1

like模糊查询

占位符：
_：单个任意字符
%：任意多个字符

查询性马的有哪些？like

select * from student where name like '马%';
1

查询姓名第二个字是化的人

select * from student where name like '_化%';
1

查询姓名是3个字的人

select * from student where name like '___';//三个“_”
1

查询姓名中包含马的人

select * from student where name like '%马%';
1

DQL单表查询----排序查询

语法：order by 子句

order by 排序字段1 排序方式1,排序字段2 排序方式2...//结尾可以加;也可以不加
1

排序方式：
第一种：ASC：升序，默认的
例如：对数学升序排列

select * from student order by math
select * from student order by math asc
1
2

第二种：DESC：降序：

select * from student order by math desc
1

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

案例：按照数学成绩升序排名，如果数学成绩一样，则按照英语成绩排名。

select * from student order by math asc, English asc;
1

DQL单表查询----聚合函数

含义：将一列数据作为一个整体，进行纵向计算。(例如计算平均值)
聚合函数计算结果都是单行单列的。

1.count：计算个数
一般用于：
（1）一把选择非空的列：主键
（2）select count(*) from student;*的意思是这列（横排）只要有一个不为null，就算一个。

查看英语那一列有多少个数据：

select count(English) from student;//有缺陷，排除null的值
select count(ifnull(English,0)) from student;
1
2

2.max：计算最大值
3.min：计算最小值
4.sum：计算和
5.avg：计算平均值

注意：聚合函数的计算，排除null值（计算不会管null）。
解决方法：
1.选择不包含非空的列进行计算。
2.ifnull函数。

DQL单表查询----分组查询

语法：group by 分组字段;

案例一：
按照性别分组，分别查询男、女同学的数学平均分。

select sex,avg(math) from stundent group by sex;
1

结果：

案例二：
按照性别分组、分别查询男、女同学的平均分，人数：

select sex,avg(math),count(id) from stundent group by sex;
1

结果：

注意：
1.数据最好都是分组字段，和聚合函数，其他的都没有意义。
例如：
案例三：
按照姓名、性别分组、分别查询男、女同学的平均分，人数：

select name,sex,avg(math),count(id) from stundent group by sex;
1

name这一列完全没有意义，只是把第一个女生的名字显示出来了。

案例四：
按照性别分组、分别查询男、女同学的平均分，人数，要求：分数低于70分的人，不参加分组

select sex,avg(math),count(id) from student where math>70 group by sex;
1

案例五：
按照性别分组、分别查询男、女同学的平均分，人数，要求：分数低于70分的人，不参加分组，分组之后，人数要大于2个人。

select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;
1

注意
2.：where和having的区别？
(1).where后不可以跟聚合函数的判断，having可以进行集合函数的判断。
(2).where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。

DQL单表查询----分页查询

语法：
1.limit开始的索引，每页查询的条数；
每页显示3条记录

select * from student limit 0,3;-- 第一页(和数组一样，索引从0开始，也就是从第一个数据开始，显示3条记录)
select * from student limit 3,3;-- 第二页
select * from student limit 6,3;-- 第三页
1
2
3

*2.公式：开始的索引 =（当前的页码-1）每页显示的条数
注意：最后一页的页数不够，还是有多少条记录就显示多少条。
3.分页操作时一个“方言”。只能在mysql里面使用。

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类：
1.主键约束：primary key
2.非空约束：not null
3.唯一约束：unique
4.外键约束：foreign key
非空约束：not null，某一列的值不能为null
1.在创建表时添加约束

CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL-- name为非空
);-- 如果添加name为null就不能添加
1
2
3
4

2.创建表完后，添加非空约束

alter table stu modify name varchar(20) not null;
1

3.删除name的非空约束

alter table stu modify name varchar(20);
1

唯一约束：unique，某一列的值不能重复
1.在创建表时，添加唯一约束：

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE-- 手机号不能重复
);
1
2
3
4

2.删除唯一约束：(和非空约束不一样)

ALTER TABLE stu MODIFY phone_number VARCHAR(20);-- 错误代码，唯一约束和非空不一样
alter table stu drop index phone_number;-- 正确写法
1
2

3.在表创建完后，添加唯一约束：

alter table stu modify phone_number varchar(20) unique;
1

注意：唯一约束可以有null值，但是只能有一条记录为null。

主键约束:primary key
注意：
1.含义：非空且唯一(重点)
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识

一、在创建表时，添加主键约束

create table stu(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);
1
2
3
4

二、删除主键：

alter table stu modify id int;//错误，虽然语法是正确的，但是删除主键不生效
alter table stu drop primary key;
1
2

三、创建完表后，添加主键：（和非空、唯一的添加方法一样）

alter table  stu modify id int primary key;
1

拓展
添加复合主键：

a int,
b int,
primary key(a,b)
1
2
3

四、自动增长
概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长。（一般是配合int类型的主键来使用，例如id）

1.在创建表时，添加主键约束，并且完成主键自增长

create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);
1
2
3
4

再使用：（自动增长时候，数据只跟上一条数据有关系，中间不连续也没关系）

insert into stu values(null,'张三');//这样id那一列不会是null，而是自己增长的int类型的数据
1

2.删除自动增长

alter table stu modify id int;//上面主键约束的删除不是这样删除的，所以这样不会删除主键约束，这只能删除自动增长
1

再执行：insert into stu values(null,‘张三’);就会报错了，自动增长没有了，id就不能赋值null了

3.创建表后，添加自动增长：

alter table stu modify id int auto_increment;
1

备注：自动增长一般和主键一起使用，但是也有不和主键一起使用的。

外键约束：foreign key
1.在创建表时，可以添加外键
语法：

create table 表名(
	...
	外键列
	constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
)
1
2
3
4
5

例如：
作用(好处)：
（1）.删除部门表时，如果员工表的外键有对应部门表的主键，就不能删除该部门
（2）.添加员工表的外键数据时，对应的部门表的关系列（一般都是主键）没有该数据，则向员工表添加该数据失败。

CREATE TABLE department(-- 部门表
	id INT PRIMARY KEY AUTO_INCREMENT,	
	dep_name VARCHAR(20),
	dep_locationion VARCHAR(20)
);
CREATE TABLE employee(-- 员工表
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,-- 外键（需要它对应主表的主键）
	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)-- 添加外键约束
);
1
2
3
4
5
6
7
8
9
10
11
12

2.删除外键：

alter table 表名 drop foreign key 外键名称;
1

3.创建表之后，添加外键：

alter table employee add foreign key emp_dept_fk foreign key (dep_id) references department(id);
通用：
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
1
2
3

验证外键约束成功：
点击最右上角的按钮（架构设计器），然后将有关联的两张表拖入架构设计器，如果两张表有线连接，这说明两表外键约束成功。

外键约束_级联操作

目的：改部门id，但是id是被关联到员工表里面了，所以首先要将员工表中改动部门id相同的id置为空，然后才能改动部门id

例如：（将id=1的部门id改为5）
第一步：将员工表中等于1的id全部置为null：(员工表中的id可以为null，但是不可以是外键部门表中不存在的id值)

update employee set dep_id=null where dep_id=1;
1

第二步：再将部门表中id=1的改为id=5

第三步：再将员工表中为null的数据设置成5

update employee set dep_id=5 where dep_id is null;-- null的判断用is
1

级联更新：on update cascade
上述操作十分复杂，所以，在添加外键约束的时候就设置级联更新。
添加外键，设置级联更新

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade;
1

这样操作了，两个连接表其中一个改了，另外一个相同的数据也改了。

级联删除：on delete cascade
也是在添加外键的时候
添加外键，设置级联更新，设置级联删除：

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;
1

作用：删除一条记录，另外和这个记录有关的表的记录都要被删除。

数据库的设计(多表关系，范式)

1.多表之间的关系

（1）一对一（了解：没意义，直接合成一张表就可以表示）：
如：人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人
（2）.一对多(多对一)：
如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门

（3）.多对多：（需要借助第三张表）
如：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

2.数据库设计的范式
概念：设计数据库时，需要遵守一些规范。要遵循后面的范式要求，必须先遵循前边的所有范式要求。
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式(INF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。

分类：
1.第一范式（1NF）：每一列都是不可分割的原子数据项

2.第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
概念：
(1).函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A
例如（前者A是属性，后者是属性组）：学号–>姓名。（学号，课程名称）–>分数
(2).完全函数依赖：A–>B,如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。
例如：(学号，课程名称)–>分数
(3).部分函数依赖：A–>B,如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。
例如：(学号，课程名称)–>姓名
(4).传递函数依赖：A–>B,B–>C,如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性(属性组)的值也可以确定唯一C属性的值，则成为C传递函数依赖于A。
例如：学号–>系名，系名–>系主任
(5).码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性值)为该表的码。通俗点：通过该属性（属性组）可以确定唯一的其他属性，就称为码。
例如：该表中码为：（学号，课程名称）
备注：
主属性：码属性组中的所有属性
非主属性：除了码属性组的属性

第二范式的意思：消除部分依赖
先分成非主属性完全依赖于主属性(码)的几张表，再删除重复的数据。

第二范式消除了第一个问题。

3.第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖），

数据库的备份和还原

1.命令行：
备份：mysqldump -u用户名 -p密码 > 保存的路径

还原：
(1).登录数据库
(2).创建数据库
(3).使用数据库
(4).执行文件。source 文件路径

2.在SQLyog里面直接操作

多表查询概述

单表查询：select * from 表;
擦寻两张表：select * from 表1,表2;（结果是表1和表2的笛卡尔积）
多表查询的目的：消除无用的数据。
例如：
dept表：

emp表：

emp和dept表笛卡尔积后无用的数据：

笛卡尔积：有两个集合A，B，取这两个集合的所有组成情况。
多表查询的分类：
1.内连接查询：
2.外连接查询：
3.子查询：

多表查询_内连接

隐式内连接和显示内连接查询的结果是一样的，这是写法不同

1.隐式内连接
使用where条件消除无用数据
查询所有员工共信息和对应的部门信息

select * from emp,dept where emp.'dept_id'=dept.'id';-- ''单引号可加可不加
1

查询员工表的名称，性别，部门表的名称

select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id=dept.id;-- 要加前缀，要不然name区分不开是哪个表的
1

改进：(用别名)

select
	t1.name,-- 分行有好处：方便加注释
	t1.gender,
	t2.name
from-- 取别名
	emp t1,
	dept t2
where
	t1.dept_id=t2.id;
1
2
3
4
5
6
7
8
9

2.显式内连接：

select 字段列表 from 表名1 inner join 表名2 on 条件-- inner是可以省略不写的
1

查询所有员工共信息和对应的部门信息:

select * from emp inner join dept on emp.'dept_id'=dept.'id';
1

内连接重点：要找到条件是什么。

多表查询_外连接

1.左外连接：查询的是左边所有数据以及其交集部分。（内连接就是查询的是交集部分）

select 字段列表 from 表1 left outer join 表2 on 条件;-- outer可以不写
1

在emp表添加一个小白龙，让他的部门id为null

查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

select
	t1.*,t2.name
from
	emp t1,dept t2
where
	t1.dept_id=t2.id;
1
2
3
4
5
6

显示结果没有加的小白龙那一行数据，null被舍去了。
改正：

select t1.*,t2.name from emp t1 left join dept t2 on t1.dept_id=t2.id;
1

由于小白龙是左表emp的数据，所以成功显示出来

2.右外连接： 查询的是右边所有数据以及其交集部分。

select 字段列表 from 表1 right outer join 表2 on 条件;-- outer可以省略
1

由于小白龙在左边中，右外连接就不能显示小白龙。

注意：其实左外（右外）两个表换个位置等于右外（左外）了。

多表查询_子查询

概念：
查询中嵌套查询，称嵌套查询为子查询。
查询工资最高的员工信息：
不适用子查询：（分两步）
1.查询最高的工资是多少 9000

select max(salary) from emp;-- 得到最高工资
1

2.查询员工信息，并且工资等于9000的

select * from emp where emp.salary=9000;
1

一条sql就完成这个操作(子查询：嵌套)

select * from emp where emp.salary=(select max(salary) from emp);
1

子查询不同情况：
1.子查询的结果是单行单列的：
子查询可以作为条件，使用运算符去判断。(运算符：> >= < <= =)

（上面的例子就是单行单列）
查询员工工资小于平均工资的人：

select * from emp where emp.salary<(select avg(salary) from emp);
1

2.子查询的结果是多行单列的：(是一种集合的判断)
子查询可以作为条件，使用运算符in来判断

查询’财务部’和’市场部’所有的员工信息

select id from dept where name='财务部' or name = '市场部';-- 得到的dept_id是3、2
select * from emp where dept_id=3 or dept_id=2;-- 或者是 dept_id in(3,2);
1
2

一条sql（子查询）：

select * from emp where dept_id in(select id from dept where name='财务部' or name = '市场部');
1

3.子查询的结果是多行多列的：
子查询不能作为条件，可以作为一张虚拟表

查询员工入职日期是2011-11-11日之后的员工信息和部门信息
子查询：

select * from dept t1,(select * from emp where emp.join_date>'2011-11-11') t2;
where t1.id=t2.dept_id;
1
2

普通的内连接：

select * from emp t1,dept t2 where t1.dept_id=t2.id and t1.join_date > '2011-11-11'
1

事务

1.概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2.操作：
(1).开启事务：start transaction;
(2).回滚：rollback;（在哪开的事务，就回滚到那里之前）
(3).提交：commit;
3.例子：

USE db1;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME,balance) VALUES('zhangsan',1000),('lisi',1000);
SELECT * FROM account;
1
2
3
4
5
6
7
8
9

需要实现张三给李四转账500：
直接转账可以的：

-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
1
2
3
4

但是假如中间有异常：

-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
1
2
3
4
5

只会执行张三转账代码，就不会执行李四收账代码
就要用事务：

-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 都还原为1000
UPDATE account SET balance=1000;

-- 发现出问题了，回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9
10
11
12

如果没有问题了，就不用回滚事务，需要提交：
注意（重点，如果没有进行提交）：数据会临时变化（没有持久化更新），没有真正变化，就是会自动回滚，将SQLyog关闭再打开，会发现数据还是事务前的数据，所以需要进行提交。

-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 都还原为1000
UPDATE account SET balance=1000;

-- 发现事务没有问题
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12

4.MySQL数据库中事务默认自动提交
自动提交：
(1).mysql就是自动提交的
(2).一条MDL（增删改）语句会自动提交一次事务。
手动提交：
(1).Oracle数据库默认是手动提交事务的。（执行完代码，都需要commit，否则不会生效）
(2).需要先开启事务，再提交

查看事务的默认提交方式：select @@autocommit;1 代表自动提交，0 代表手动提交。
修改默认提交方式：set @@autoconmmit=0;再执行mysql语句都是临时变化，需要手动提交。
5.事务的四大特征：
1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2.持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3.隔离性：多个事务之间，相互独立。
4.一致性：事务操作前后，数据总量不变。

事务的隔离级别（了解）

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在的问题：
1.脏读：一个事务，读取到另一个事务中没有提交的数据。
2.不可重复读：在同一个事务中，两次读取到的数据不一样。
3.幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：
1.read uncommitted：读未提交
产生的问题：脏读、不可重复读、幻读
比喻说用户1打开事务借了钱给用户2，但是没有提交事务，用户2查询账单发现账户钱已经到账，写了欠条后，用户1又回滚事务，最后用户2的账户钱有回到了以前，被骗了。

2.read committed：读已提交（Oracle默认）
产生的问题：不可重复读、幻读

3.repeatable read：可重复读（MySQL默认）（不管事务1提交没有，事务2中显现的数据都不会变化，只有提交事务2才会显现）
产生的问题：幻读

4.serializable:串行化（锁表操作，意思是一批数据只能由一个事务处理，很好用，但是效率低）
可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
数据库查询隔离级别：select @@tx_isolation;
数据库设置隔离级别：set global transaction isolation level 级别字符串;（再次打开编译器就会生效）

DCL管理用户，授权

DBA：数据库管理员
管理用户：
1.添加用户:create user '用户名'@'主机名' identified by '密码';

create user 'zhangsan'@'localhost' identified by '123'
登录试一下：
mysql -uzhangsan -p123
1
2
3

2.删除用户：drop user '用户名'@'主机名';
3.修改用户密码：
方式一：

UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
1

方式二：（DCL特有的方式）

SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
1

mysql中忘记了root用户的密码？
（1）.用管理员的身份进入cmd，输入net stop mysql 停止MySQL服务
（2）.使用无验证方式启动mysql服务：mysqld --skip-grant-tables
（3）.（不要关闭上一个窗口）然后打开一个新的cmd，只需要输入mysql，回车就登录了。
（4）.修改密码。

use mysql;
update user set password =password('新密码') where user='root';
1
2

（5）.关闭两个窗口，在任务管理器中手动结束mysqld.exe进程。
（6）.然后再以管理员的身份进入cmd，启动MySQL服务：net start mysql
4.查询用户：
（1）切换到MySQL数据库：use mysql;
（2）查询user表：select * from user;
备注：通配符：%表示可以再任意主机使用用户登录数据库。

权限管理：
1.查询权限：show grants for '用户名'@'主机名';
2.授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
例如：
给李四授予查询db1中stu表的权限：

grant select on db1.stu to 'lisi'@'%';
1

给张三用户授予所有权限，在任意数据库任意表上

grant all on *.* to 'zhangsan'@'localhost';
1

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