数据库

1. MySQL概述

为什么要用数据库（Database）？
（1）数据要持久化（从内存中把数据永久的保存到文件中）
（2）有结构的管理我们的数据：方便我们检索，查询等操作

2、几个名词：
DBMS：Database ManageMent System 数据库管理系统
DB：Database  数据库
SQL：Structe Query Language结构化查询语言

DBMS是管理DB，像我们说的MySQL，Oracle，SQL Server,Redis，....这些都是DBMS。
SQL是一种语言，我们程序员或DBA（数据库管理员）和DBMS交互的语言。

3、MySQL
（1）免费
（2）开源
（3）轻量级
（4）性能

MySQL是一款关系型数据库。内部的数据以二维表格存储。有行和列组成。

一个DBMS中会有很多个DB数据库，
一个DB中会有很多张表格table，
一个表格中会有很多行和列，把每一列称为一个字段，列名称为也称为字段名，每一行称为一条记录。


ORmapping：Object RelationShip mapping，对象与关系型数据库的映射
表->类
记录->对象
字段->属性
ORmapping的框架：hibernate,mybatis等

4、数据库
数据库分为服务器端和客户端
我们安装和使用的MySQL是服务器端，服务器端就会有一个服务


回忆：网络编程，TCP服务器端程序时，第一步，开启服务ServerSocket

其他命令行客户端
mysql -h mysql服务器IP地址 -P 端口号 -u 用户名 -p回车
Enter password:输入密码

mysql -h localhost -P 3306 -u root -p回车
Enter password:输入密码

说明：
（1）如果连接本机，那么-h localhost可以省略
（2）如果端口号默认是3306，那么-P 3306可以省略
（3）-h -P -u后面可以有空格也可以没有空格，但是-p后面不要有空格

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1
编码的问题：

SHOW VARIABLES LIKE 'character_set_%';
show variables like 'character_set_%';
set names gbk;   设置客户端的字符编码为gbk，这句话只对一次连接有用。

其他它改的是两个变量的值：
character_set_client     | gbk
character_set_connection | gbk

2. 基本数据类型

基本数据类型
Java中：
1、基本数据类型
byte,short,int,long,float,double,char,boolean

2、引用数据类型
类、接口、枚举、数组、注解

if(xx == null)

BigInteger
BigDecimal

MySQL中：
1、整数类型
tinyint：微小整数
smallint：小整数
mediumint：中整数
int,integer：整数
bigint：大整数

int unsigned：无符号整数，没有负数
int(M) ：如果单独使用，没意义
int(M) zerofill：这样指定宽度才有意义  
例如：int(4) zerofill   当我存储的整数值是20，那么它会实际存储 0020
如果你写了zerofill，就意味着无符号

2、浮点型
float,double
float(M,D)：M是说总的宽度，D是指定小数点后几位
double(M,D)

float(5,2)：表示的数据范围是在[-999.99,999.99]范围。

也可以加unsigned，
float(5,2) unsigned：无符号小数，没有负数，它表示的范围[0,999.99]

 

3. 语法和命名规则

一、SQL：Structure Query Language结构化查询语言
1、DDL：（Data Definition Languages)：数据定义语言
包括：建库、建表、修改表结构等
主要关键字：create,drop,alter等

2、DML：（Data Manipulation Language）：数据操作语句
包括：增、删、改、查
主要关键字：insert,delete,update,select

3、DCL（Data Control Language）：数据控制语句
包括：grant、revoke、transaction

二、语法规则
1、mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写
create database java1111;
insert into stu values(1,'zhangsan',45);

2、值，除了数值型，字符串型和日期时间类型使用单引号（''）

3、字段别名，尽量使用双引号（""），特别是别名中间有空格，而且不建议省略as

4、所有标点符号使用英文状态下的半角输入方式

5、必须保证所有(),单引号，双引号是成对结束的
insert into stu values(2,'李四,'男');

6、可以使用（1）#单行注释 （2）--空格单行注释 （3）/*  多行注释  */

#插入语句练习
insert into stu values(2,'zhangsan','女');

-- 删除数据

三、命名规则
1、数据库、表名不得超过30个字符，变量名限制为29个

2、必须只能包含 A–Z, a–z, 0–9, _共63个字符

3、不能在对象名的字符间留空格

4、必须不能和用户定义的其他对象重名
不同的数据库下，表格是否可以重名
不同的表格下，字段是否可以重名

5、必须保证你的字段没有和保留字、数据库系统或常用方法冲突

6、保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。
假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了，就不合适
一张表格是部门表：did是int
did  dname
1     财务部
另一张表格是员工表：deptno是字符串，不合适
eid ename deptno   
1   张三   1

1、DDL  

（Data Definition Languages)：数据定义语言

包括：建库、建表、修改表结构等; 操作表结构的sql语句都要加table

（一）操作数据库
1、创建数据库
CREATE DATABASE 数据库名;

create database 数据库名;

例如：create database bigdata1111;

2、查看当前用户能够看到的所有的数据库
show databases;

3、使用某个数据库
use 数据库名;

4、删除数据库
drop database 数据库名;

（二）操作表格
1、查看当前数据库下都有什么表格，当前用户能够看到所有表格
show tables; #前面得有use语句，否则会报错No database selected
show tables from 数据库名;

2、创建表格（简单版）
create table 表名称(  

    字段名1 数据类型,
    字段名2 数据类型,
    ...
);

create table 表名称(字段名1 数据类型,字段名2 数据类型,...);

例如：
员工表：编号、姓名、年龄、薪资、生日
create table employee(
    eid int,
    ename varchar(20),
    age int,
    salary double(10,2),
    birthday date    
);

3、查看表结构
desc 表名称;
show create table 表名称;

4、删除表格
drop table 表名称;

5、修改表结构  
　　（1）修改表名称 
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;
例如：
alter table employee rename emp;
rename table emp to employee;

　 （2）增加一列
A:alter table 表名称  add 【column】字段名 数据类型;
　　例如：alter table employee add tel char(11);

B:alter table 表名称  add 【column】字段名 数据类型 after 另一个字段;
　　例如：alter table employee add gender char after ename;

C:alter table 表名称  add 【column】字段名 数据类型 first;

   （3）删除一列
alter table 表名称  drop 【column】字段名;
　　例如：alter table employee drop tel;

   （4）修改列的数据类型
alter table 表名称  modify 【column】字段名 新数据类型 【after 另一个字段/first】;
　　例如：alter table employee modify gender char(2);

   （5）修改列的名称
alter table 表名称  change 【column】旧字段名 新字段名 数据类型 【after 另一个字段/first】;
　　例如：alter table employee change gender sex char(2);

 

DML语句

（Data Manipulation Language）：数据操作语句 （增删改查）

增删改查语句都不加table

mysql> desc employee;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| eid      | int(11)      | YES  |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int(11)      | YES  |     | NULL    |       |
| salary   | double(10,2) | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

1、添加语句（insert into 表名 ( 字段名 ) values（ ）），不加table
（1）insert into 表名称 values(值列表);
要求：值列表的个数、类型、顺序和表结构对应
例如：insert into employee values(1,'张三','男',23,10000,'1990-9-9');

如果出现：ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'ename' at row 1
说明字符编码的问题： set names gbk;

（2）insert into 表名称(字段列表) values(值列表);
要求：值列表的个数、类型、顺序和(字段列表)对应
例如：
insert into employee (eid,ename) values(2,'李四');

（3）同时插入多行
insert into 表名称 values(值列表1),(值列表2),(值列表2)...;
insert into 表名称(字段列表) values(值列表1),(值列表2),(值列表2)...;

例如：
insert into employee (eid,ename) values(3,'王五'),(4,'赵六'),(5,'钱七');

insert into employee (eid,ename)values
(3,'王五'),
(4,'赵六'),
(5,'钱七');

2、修改语句   （ update 表名 set 字段名1=值1，，，，）
（1）update 表名称 set 字段名1 = 值1,字段名2 = 值2 ....  【where 条件】;
说明：如果没有where，就表示修改所有行
例如：
update employee set salary = 15000 ;
update employee set salary = 10000 where  eid = 1;

（2）update 表名称1,表名称2 set 表名称1.字段名1 = 值1,表名称2.字段名2 = 值2 ....  【where 条件】;

create table department(
    did int,
    dname varchar(20)
);
alter table employee add did int;

insert into department values(1,'财务'),(2,'开发');

#需求，把“开发”修改为“开发部”，并且把“张三”的部门修改为2，即安排在开发部
update employee,department set did = 2,dname = '开发部' ;
ERROR 1052 (23000): Column 'did' in field list is ambiguous 模糊不清的

update employee,department set employee.did = 2,department.dname = '开发部' ;

update employee,department set employee.did = 2,department.dname = '开发部' 
where employee.ename='张三' && department.did = 2;

3、删除数据，不删表结构 （ delete from 表名 ；  truncate 表名 ）
（1）delete from 表名称 【where 条件】;
　　例如：delete from department;
      delete from employee where ename ='钱七';
      
（2）truncate 表名称; 作用是清空表数据，表结构还在
　　例如：truncate employee;

delete from与truncate的区别？
（1）truncate只能用于清空表，不能只删除部分数据，而delete可以加条件，只删除部分数据
（2）truncate清空表的速度很快，而delete清空表很慢，原因，truncate是把整个表drop掉，然后新建一张新表，表结构和原来一样。delete是逐条删除。
（3）truncate语句是无法回滚的，delete可以撤销（当然前提是事务没提交时可以回滚）

4、查询数据（简单查询） （select ，， from 表名 ）
（1）select * from 表名称;   用于显示整张表的数据
　　例如：select * from employee;

（2）select 字段列表 from 表名称;  用于显示部分列
　　例如：select eid,ename from employee;

（3）select 字段列表 from 表名称 where 条件;  用于显示部分行和列
　　例如：select eid,ename from employee where eid = 3;

（4）select * from 表名称 where 条件; 用于显示部分行所有列
　　例如：select * from employee where eid = 3;

约束 （主键、唯一键、外键、非空、默认值、检查）

约束：约束的是数据

1、主键约束
2、唯一键约束
3、外键约束
4、非空约束
5、默认值约束
6、检查约束：mysql暂时不支持

一、主键约束 
要求：每张表都应该有主键约束，并且一个表只有一个主键约束
作用：通过主键约束，来唯一的确定一行
特点：非空并且唯一
分类：单列主键，复合主键

（一）单列主键
1、如何创建主键约束
建表时
create table 表名称(
    字段名1 数据类型 primary key,
    字段名2 数据类型,
    ...
);

create table employee(
    eid int primary key,
    ename varchar(20),
    age int,
    salary double(10,2),
    birthday date    
);

insert into employee (eid,ename) values(1,'张三');
如果违反主键约束：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

建表后：
create table employee(
    eid int ,
    ename varchar(20),
    age int,
    salary double(10,2),
    birthday date    
);

alter table 表名称 add primary key(主键列);
例如：alter table employee add primary key(eid);

（二）复合主键
建表时：
create table 表名称(
    字段名1 数据类型 ,
    字段名2 数据类型,
    ...
    primary key(复合主键的字段列表)
);
建表后：
alter table 表名称 add primary key(复合主键的字段列表);

create table stu(
    sid int primary key,
    sname varchar(20)
);
insert into stu values(1,'小路'),(2,'小周');
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 小路    |
|   2 | 小周     |
+-----+-------+
create table course(
    cid int primary key,
    cname varchar(20)
);
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | javase |
|   2 | mysql  |
+-----+--------+
insert into course values(1,'javase'),(2,'mysql');
create table xuanke(
    sid int,
    cid int,
    score int,
    primary key(sid,cid)
);

insert into xuanke values(1,1,90),(1,2,89);
方法一：增加一列，无逻辑意义的列，只为了区别行，序号列
no sid cid score
1    1    1   90
2    1    2   89
3    2    1   89
4    2    2   67
方法二：复合主键，(sid,cid)一起决定
sid cid score
1    1   90
1    2   89
2    1   89
2    2   67


二、唯一键约束
要求：不是所有表都有，并且可能一张表有很多唯一键约束
特点：唯一，但是允许为空
分类：单列唯一，复合唯一
这里复合唯一和主键唯一的创建方式一样

1、如何创建唯一键约束
建表时
create table 表名称(
    字段名1 数据类型 primary key,
    字段名2 数据类型 unique key,
    字段名3 数据类型 unique key,
    ...
);

create table employee(
    eid int primary key,
    ename varchar(20),
    cardid char(18) unique key,
    tel char(11) unique key
);
insert into employee values
(1,'张三','111111','111111'),
(2,'李四','222222','222222');

建表后：
alter table 表名称 add unique key(字段名);

create table employee(
    eid int primary key,
    ename varchar(20),
    cardid char(18),
    tel char(11)
);

alter table employee add unique key(cardid);
alter table employee add unique key(tel);

三、非空约束
特点：某列的值不允许为空
1、如何创建非空约束
建表时
create table 表名称(
    字段名1 数据类型 primary key,
    字段名2 数据类型 not null,
    字段名3 数据类型 not null,
    ...
);

create table employee(
    eid int primary key,
    ename varchar(20) not null,
    cardid char(18) not null,
    tel char(11) not null
);

insert into employee (eid,ename) values(1,'张三');

建表后：
create table employee(
    eid int primary key,
    ename varchar(20) ,
    cardid char(18),
    tel char(11) 
);
alter table 表名称 modify 字段名 数据类型  not null;

alter table employee modify ename varchar(20) not null;

四、默认值约束
某一列的值没有指定值的话，用默认值

1、如何指定默认值约束
建表时
create table 表名称(
    字段名1 数据类型 primary key,
    字段名2 数据类型 default 默认值,
    字段名3 数据类型 not null,
    ...
);

create table employee(
    eid int primary key,
    ename varchar(20) not null,
    gender char default '男'
);
insert into employee(eid,ename) values(1,'张三');
insert into employee values(2,'李四',default);
insert into employee values(3,'李梅','女');

建表后：
alter table 表名称 modify 字段名 数据类型  default 默认值;

删除约束

删除约束：
1、主键约束
alter table  表名称 drop  primary key;

2、删除唯一键约束
alter table  表名称 drop index 唯一键的索引名;

解释：因为像主键、唯一键、外键三个键约束建立时，会自动增加一个索引（用来提高查询速度），
因为删除唯一键，它用的是删除索引。

show index  from 表名称;

alter table employee drop index cardid;

3、删除非空和默认值约束
alter table employee modify ename varchar(20) ; #去掉非空和默认值

4、删除外键
alter table  表名称 drop foreign key 外键约束名;
例如：alter table employee drop foreign key employee_ibfk_1;

如何查看外键约束：
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

SELECT * FROM information_schema.table_constraints WHERE table_name = 'employee';

建立外键约束

约束：  主键约束、唯一键约束、外键约束、非空约束、默认值约束、检查约束(check)

外键约束：主表（父表）和从表（子表）两列值的引用（参考）关系。

部门表：部门编号、部门名称、部门简介
员工表：员工编号，员工姓名，薪资，...，所在的部门编号

部门表是主表，员工表示从表，员工表的"所在的部门编号"参考（引用）部门表的"部门编号"

建立外键后有什么不同？
（1）当我们给从表添加和修改记录时，那么外键列的值，要受到主表的约束
例如：员工表选员工所在部门编号时，要看部门表是否有这个部门

（2）当我们修改或删除主表的被参考列的值时，要受到从表的约束
例如：当修改部门表的部门编号时，要看这个部门编号是否在员工表中用过

（3）删除主表，要受到从表的约束
例如：要删除部门表时，因为员工表引用它，参考它，所以是无法删除


1、如何建立外键
建表时建立外键：
前提：先建立主表，在建立从表
create table 主表名(
    字段1 数据类型 primary key,
    字段2 数据类型 [unique key],
    字段3 数据类型 [not null] [default 默认值]
    ....
);

create table 从表名(
    字段1 数据类型 primary key,
    字段2 数据类型 [unique key],
    字段3 数据类型 [not null] [default 默认值]
    ....
    
    foreign key(从表的某个字段) references 主表(被参考的字段)
);
说明：主表(被参考的字段)，必须是一个主键列或唯一键列。

例如：
create table department(
    did int primary key,  #部门编号
    dname varchar(20) not null,
    description varchar(50)
);

create table employee(
    eid int primary key,
    ename varchar(20) not null,
    salary double not null,
    deptno int,  #所在部门编号
    foreign key(deptno) references department(did)
);

insert into department values(1,'财务','发钱的');
insert into employee values(1,'张三',10000,1);
insert into employee values(2,'李四',20000,2);

update department set did = 2 where dname ='财务';


2、建表后再建立外键 --->>>
alter table 从表名 add foreign key (从表的某个字段) references 主表名(被参考的字段);

如： alter table t_job add foreign key (job_id) references t_employee (job_id) on update cascade on delete set null;   
    
alter table t_employee add foreign key (eid) references t_salary (eid)  on update cascade on delete set null;
   
foreign key (card_id) references t_salary (eid) on update cascade on delete set null
    

3、外键的约束关系可以多种情况
（1）默认的：严格约束
        on update restrict 
        on delete restrict
（2）级联约束：cascade 
        on update cascade  
        on delete cascade
（3）置空：set null        
        on update set null 
        on delete set null
（4）无动作：no action         
        on update no action 
        on delete no action
在mysql中，no action     等价于  restrict

说明：on update的策略和on delete可以不同

create table department(
    did int primary key,  #部门编号
    dname varchar(20) not null,
    description varchar(50)
);

create table employee(
    eid int primary key,
    ename varchar(20) not null,
    salary double not null,
    deptno int,  #所在部门编号
    foreign key(deptno) references department(did) on update cascade on delete cascade
);    

insert into department values(1,'财务','发钱的');
insert into employee values(1,'张三',10000,1);
insert into employee values(2,'李四',10000,null);
update department set did = 2 where dname ='财务';
或
delete from department where did = 2; 

create table department(
    did int primary key,  #部门编号
    dname varchar(20) not null,
    description varchar(50)
);

create table employee(
    eid int primary key,
    ename varchar(20) not null,
    salary double not null,
    deptno int,  #所在部门编号
    foreign key(deptno) references department(did) on update set null on delete set null
);    
insert into department values(1,'财务','发钱的');
insert into employee values(1,'张三',10000,1);
insert into employee values(2,'李四',10000,null);
update department set did = 2 where dname ='财务';
或
delete from department where did = 2; 

例如：
订单表与订单明细表
    订单表是主表，订单明细表是从表。
    订单明细表中建立外键，  foreign key(订单编号) references 订单表(订单编号) on update cascade on delete cascade

部门表和员工表
    部门表示主表，员工表是从表
    员工表中建立外键， foreign key(部门编号) references 部门表(部门编号) on update cascade on delete restrict
    员工表中建立外键， foreign key(部门编号) references 部门表(部门编号) on update cascade on delete set null
    
    

#从部门编号的引用关系看，部门表是主表，员工表是从表
create table t_department(
    did int primary key,
    dname varchar(100) not null unique key,
    description varchar(200),
    manager_id int  #部门的管理者编号
);

#从职位编号的引用关系看，职位表是主表，员工表是从表
create table t_job(
    job_id int primary key,
    job_name varchar(100) not null,
    description varchar(200)
);

#从领导编号的引用关系看，主表是员工表，从表也是员工表
create table t_employee(
    eid int primary key,
    ename varchar(100) not null,
    gender char not null default '男',
    card_id char(18) unique key,
    tel char(11),
    job_id int,
    mid int, #员工的领导编号
    birthday date,
    hiredate date,
    address varchar(100),
    dept_id int,
    foreign key(dept_id) references t_department(did),
    foreign key(job_id) references t_job(job_id),
    foreign key(mid) references t_employee(eid)
);

#从薪资的员工编号的引用关系看，员工表是主表，薪资表是从表
create table t_salary(
    eid int primary key,
    basic_salary decimal(10,2),
    performance_salary decimal(10,2),
    commission_pct decimal(10,2),
    deduct_wages decimal(10,2),
    foreign key(eid) references t_employee(eid)
);

alter table t_salary add foreign key(eid) references t_employee(eid)
on update cascade on delete;
#从部门表的领导编号引用关系看，部门表是从表，员工表是主表。

alter table t_department add foreign key(manager_id) references t_employee(eid);

 

自增长（auto_increament）

自增长列的要求：auto_increment
1、一个表只能有一个自增长列
2、自增长列必须是键列，唯一键和主键
3、自增长列必须是整数类型

create table t_stu(
    sid int primary key auto_increment,
    sname varchar(100) not null,
    gender char default '男' not null,
    card_id char(18) unique key,
    birthday date,
    address varchar(200)
);

insert into t_stu values(3,'张三',default,'123456789123456780','1990-1-1','宏福苑');
insert into t_stu values(null,'李四',default,'123456789123456781','1990-2-2','宏福苑');
insert into t_stu values(0,'王五',default,'223456789123456781','1990-2-2','宏福苑');
insert into t_stu(sname,card_id) values('赵六','223456456123456781');

当添加自增长列的值时 ，如果指定具体的值，就按具体值来，如果指定null或0就按自增长来。

 

 运算符（算术、比较、逻辑、模糊查询）

1、字段的别名：  
字段名 as 别名
（1）如果没有空格，""可以省略，建议保留
（2）as可以省略，建议保留
    如：dname AS "部门名称", dname AS "部门名称"

飘号：`
SELECT `id`,`name` FROM `t_stu`;

去重：distinct
如--> SELECT DISTINCT `mid` FROM t_employee; 

算术运算符：
加：+
减：-
乘：*
除：/（可以保留小数部分）    div（不保留小数部分）
取模：%  mod
如：salary / 30 AS "日薪"

比较运算符:
大于：>
小于：<
大于等于：>=
小于等于：<=
不等于：!=   <> ；
等于：=
安全等于：<=>  可以用于判断null值，建议null的判断使用is null和is not null；  不能用salary=null，要用 salary is null或者 <=>安全等于

设计表的时候，要考虑null，可以设计为非空，再结合默认值，字符串可以默认为''，数值类型可以默认为0
在查询和计算时，也要考虑null
    判断用<=>或is null或is not null
    计算时可以使用 ifnull(xx,value)

逻辑运算符
逻辑与：&&   and
逻辑或：||    or
逻辑非：!
逻辑异或：xor
#查询要么薪资低于10000，但奖金比例非空   或  薪资高于或等于10000，奖金比例为空
SELECT * FROM t_employee WHERE salary < 10000 XOR commission_pct IS NULL

表示区间范围或集合范围
区间范围：between ..  and ..
       not between .. and ..
集合范围：in(...)
    not in (...)
salary IN(10000,12000,15000)

模糊查询：用于查询字符串类型 like  
_：代表一个字符
%：代表0~n个字符
#查询名字中有一个“冰”字
SELECT * FROM t_employee WHERE ename LIKE '%冰%'; //冰前边和后边可有任意个字符
SELECT * FROM t_employee WHERE ename LIKE '_冰_'; //冰前边和后边只能有一个字符

not like

关联查询 

 

A 与 B 联合查询
结果有七种：
（1）A
（2）A - A ∩ B
（3）B
（4）B - A ∩ B
（5）A ∩ B
（6）A ∪ B
（7）A ∪ B - A ∩ B

联合查询的分类：
1、内连接  INNER JOIN     A ∩ B
2、外连接
（1）左外连接，简称左连接  LEFT JOIN
（2）右外连接，简称右连接  RIGHT JOIN
（3）全外连接（mysql暂时不支持）  FULL JOIN

联合查询要避免笛卡尔积：
如：SELECT * FROM t_employee, t_department; 会产生 两个表的行数相乘的情况;    加联合查询的条件就可以避免笛卡尔积
当n多张表联合查询时，联合查询的条件是n-1个    
例如：A与B联合查询，关联条件是1个
     A与B、C联合查询，关联条件是2个
     ....


一、内连接
1、方式一：
select 字段列表  
from A表名 , B表名
where A表与B表的关联条件 and 其他筛选条件
....
SELECT * FROM t_employee, t_department
WHERE t_employee.did = t_department.did;
2、方式二：
select 字段列表 
from A表名 INNER JOIN B表名 
on A表与B表的关联条件
where 其他的筛选条件
...
SELECT * FROM t_employee INNER JOIN  t_department
ON t_employee.did = t_department.did  #关联条件
WHERE t_department.`dname` = '教学部'   #筛选条件
AND t_employee.`gender` = '男';  #筛选条件

二、左连接 left join   --->> A
1、查询结果是A表
select 字段列表
from A表名 left join B表名
on A表与B表的关联条件
where 其他筛选条件
#查询所有的员工，包括那些没有安排部门的员工，如果有安排部门的员工，显示对应的部门信息
#查询结果是A
SELECT * FROM t_employee LEFT JOIN t_department
ON t_employee.`did` = t_department.`did`;
-->交集 + A表中有的，B中没有，即把A表全部展现出来。

2、查询结果是 A - A∩B 
select 字段列表
from A表名 left join B表名
on A表与B表的关联条件
where A表的关联字段 is null
其他筛选条件
#查询那些没有安排部门的员工
SELECT * FROM t_employee LEFT JOIN t_department
ON t_employee.`did` = t_department.`did`
WHERE t_employee.`did` IS NULL; 
-->>查询结果就是把t_employee员工表的员工没有部门的人的信息查出来了（只显示没有部门的，有部门的不显示）


三、右连接
1、查询结果是B表
select 字段列表
from A表名 right join B表名
on A表与B表的关联条件
where 其他筛选条件
SELECT * FROM t_employee RIGHT JOIN t_department 
ON t_employee.`did` = t_department.`did`;
-->查出的信息都是B的

2、查询结果是B - A∩B
select 字段列表
from A表名 right join B表名
on A表与B表的关联条件
where A表的关联字段 is null
其他筛选条件
SELECT * FROM t_employee RIGHT JOIN t_department
ON t_employee.`did` = t_department.`did`
WHERE t_employee.`did` IS NULL;
-->>只查询出t_department表中没有人的信息，只有一个部门，没有员工。

四、全连接
mysql不支持FULL JOIN，但是可以实现
（6）A ∪ B   和  （7）A ∪ B - A ∩ B

（6）A ∪ B
select 字段列表
from A表名 left join B表名
on A表与B表的关联条件
where 其他筛选条件
union  #合并
select 字段列表
from A表名 right join B表名
on A表与B表的关联条件
where 其他筛选条件
#查询所有员工和所有部门，包括没有部门的员工和没有员工的部门，如果有对应的信息的就对上
SELECT * FROM t_employee LEFT JOIN t_department
ON t_employee.`did` = t_department.`did`
UNION
SELECT * FROM t_employee RIGHT JOIN t_department
ON t_employee.`did` = t_department.`did`;

（7）A ∪ B - A ∩ B
select 字段列表
from A表名 left join B表名
on A表与B表的关联条件
where A表的关联字段 is null
其他筛选条件
union
select 字段列表
from A表名 right join B表名
on A表与B表的关联条件
where A表的关联字段 is null
其他筛选条件
#查询那些没有部门的员工和没有员工的部门
SELECT * FROM t_employee LEFT JOIN t_department
ON t_employee.`did` = t_department.`did`
WHERE t_employee.`did` IS NULL
UNION
SELECT * FROM t_employee RIGHT JOIN t_department
ON t_employee.`did` = t_department.`did`
WHERE t_employee.`did` IS NULL;



五、自连接：
    两个表的关联查询。
    这两个表本质上是一张表，虚拟成两张表。
给表取别名
#查询所有员工的编号，姓名，以及他的领导的编号和姓名，不包括那些没有领导的员工     
SELECT * FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.`eid` = mgr.`mid`; 
SELECT emp.`eid`, emp.`ename`, mgr.`mid`,mgr.`ename` FROM t_employee AS emp #t_employee AS emp意思是t_employee作为员工表使用
INNER JOIN t_employee AS mgr #t_employee AS mgr意思是t_employee作为领导表使用
ON emp.`eid` = mgr.`mid`;  #员工的领导编号 = 领导的员工的编号

#查询所有员工的编号，姓名，以及他的领导的编号和姓名，包括那些没有领导的员工
SELECT emp.`eid`, emp.`ename`, mgr.`mid`,mgr.`ename` FROM t_employee AS emp
LEFT JOIN t_employee AS mgr
ON emp.`eid` = mgr.`mid`; 

 select语句的5个字句、分组函数

select语句：
select 字段列表
from 表列表
where 筛选条件
group by 分组字段列表
having 筛选条件
order by 排序字段列表
limit m,n;

5个子句：
（1）where
（2）group by
（3）having
（4）order by
（5）limit

每一个select语句，5个子句有顺序要求，从（1）到（5）

分组函数
（1）count()
    a：count(*)  统计的是行数 
    b：count(常量) 如count(1)、count("嘿嘿")，它们都是常量
    c：count(字段名) 统计是非NULL的行数， 统计的是行数 如统计COUNT(commission_pct)这个字段一列非null的行数
　　SELECT COUNT(*) AS "总人数"  FROM t_employee;

（2）sum()
（3）avg()
（4）max() #分组函数不一定必须是在分组之后才能使用
（5）min()

SELECT SUM(salary), MAX(salary) AS "最高工资", MIN(salary) AS "最低工资", AVG(salary) AS "平均工资" FROM t_employee;

SELECT did,ename,COUNT(*) 
FROM t_employee
GROUP BY did;  #错误的，ename和分组无关,它把每个组人的第一个人(也不一定)的名字拿出来了,ename不是唯一的

SELECT did, gender, MAX(salary), MIN(salary),AVG(salary)
FROM t_employee GROUP BY did,gender;

group by ，结合  关联查询  要写清楚不然Column 'did' in field list is ambiguous
#查询每个部门编号，部门的名称，部门的平均工资
SELECT t_employee.did,dname,AVG(salary)
FROM t_employee INNER JOIN t_department
ON t_employee.`did` = t_department.`did`
GROUP BY t_employee.did;


having 子句，也是条件的筛选
where和having的区别：
（1）where后面不允许使用分组函数
因为分组函数是用于统计结果，where后面的条件是从原表中筛选，不是从计算结果中筛选
having用于计算结果的筛选
（2）having只用于group by分组统计语句
#查询每个部门的平均工资，并且只显示平均工资低于12000
SELECT did, AVG(salary)
FROM t_employee GROUP BY did,
HAVING AVG(salary) < 12000;
where,group by,having一起使用

#查询每个部门的女员工的平均工资，显示高于12000的
SELECT did,AVG(salary)
FROM t_employee
WHERE gender = '女'
GROUP BY did
HAVING AVG(salary)>12000


order by：排序 
order by 字段列表
（1）如果字段名后面没有写（desc,asc）默认就是升序
（2）每一个字段后面可以单独指定是升序（asc）还是降序（desc）

#查询员工的编号、姓名、薪资，按照薪资降序排列，如果薪资相同，按照编号升序排列
SELECT eid,ename,salary FROM t_employee
ORDER BY salary DESC,eid ; 

#查询每一个部门女员工的平均工资，要求只显示平均工资高于12000，并且按照平均工资升序排列
SELECT did,AVG(salary)
FROM t_employee
WHERE gender = '女'
GROUP BY did
HAVING AVG(salary)>12000
ORDER BY AVG(salary)


limit 分页：
limit m,n
m：起始行下标
n：查询的记录数

设第几页为page，每页的显示记录数为count
m = (page-1)*count
n = count
#查询所有女员工的信息，每页显示3条，第4页
SELECT * FROM t_employee
LIMIT 9,3;  #(4-1)*3,3; 这样子写是错误的
#查询员工的信息，每页只显示10条，第三页
SELECT * FROM t_employee LIMIT 20,10;
#查询每个部门编号和名称，每个部门的女员工的平均工资，并且按照部门编号升序排列，
#每页只显示2条，查询第二页
SELECT t_employee.`did`, t_department.`dname`, AVG(salary)
FROM t_employee INNER JOIN t_department
ON t_department.`did` = t_employee.`did`
WHERE t_employee.`gender` = '女'
GROUP BY t_employee.`did`
ORDER BY t_employee.`did`
LIMIT 2,2;

子查询

子查询：嵌套在其他SQL语句中的查询，叫查询。
子查询可以嵌套在另一个查询中，也可以嵌套在另一个update等语句中。

子查询分两类：
1、where型；子查询嵌套在where的后面
    单行操作符：=,>=,>,<,<=，!=  这些运算符后面是接一个值
    多行操作符：in(...)  
    单行操作符 all(...) 或  单行操作符 any(...) 
2、from型；子查询嵌套在from的后面
把子查询的结果作为一张临时表，给这个结果取别名，作为一张表使用，再做联合查询。

# where型子查询 如：查询孙红雷的领导的工资
SELECT salary FROM t_employee
WHERE eid IN(
SELECT `mid` FROM t_employee WHERE ename = '孙红雷'); where型的多行操作符

SELECT * FROM t_employee
WHERE salary > (
SELECT salary FROM t_employee WHERE ename = '范冰冰');

SELECT * FROM t_employee WHERE did IN ( 
SELECT did FROM t_employee WHERE ename IN('孙红雷', '谢娜'));

SELECT * FROM t_employee WHERE did = ANY ( 
SELECT did FROM t_employee WHERE ename IN('孙红雷', '谢娜'));
#这两种是一样的，in ，any

#查询最高工资的员工的信息
SELECT * FROM t_employee WHERE salary = (
SELECT MAX(salary) FROM t_employee );
#换成 > 就不可以了
SELECT * FROM t_employee WHERE salary >= ALL(SELECT salary FROM t_employee);

#from型子查询：如查询比本部门的平均工资高的员工

　　SELECT t_employee.did, t_employee.ename,t_employee.`salary`, temp.avg_salary
　　FROM t_employee INNER JOIN (
　　SELECT did, AVG(salary) AS avg_salary FROM t_employee GROUP BY did)AS temp
　　ON t_employee.`did` = temp.did
　　WHERE salary > temp.avg_salary;

#查询部门信息，该部门必须有员工，不查没有员工的部门
SELECT DISTINCT t_department.* #不加distinct会有重复的did
FROM t_department RIGHT JOIN t_employee
ON t_department.`did` = t_employee.`did`;
SELECT * FROM t_department WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.did = t_department.did);
#运行过程：把主查询t_department的每一条记录，代入子查询，看是否有结果，有就保留这个记录
　　

　函数

单行函数：
（1）数学函数
（2）日期时间函数
（3）字符串处理函数
（4）流程控制函数
（5）加密等函数
/*
一、数学函数
ceil：往上取整
floor：往下取整
round：四舍五入
  round(x)：舍去小数位
  round(x,d)：保留小数点后D位四舍五入
TRUNCATE(x,d)：直接截掉，小数点后保留d位

SELECT CEIL(2.2), FLOOR(2.8),ROUND(2.6),ROUND(3.1415926, 3),TRUNCATE(3.1415926, 4);
*/

字符串函数
CONCAT(S1,S2,......,Sn):字符串拼接，不能使用+
LENGTH(s)：字符串的字节数
CHAR_LENGTH(s)：字符串的字符数； 如孙红雷 -->9个字节，3个字符
INSERT(原字符串,开始位置，几个字符，新字符串）：把原字符串从开始位置的几个字符替换为新字符串。
    字符串的开始位置是1
SELECT INSERT (ename,2,1,'雷') FROM t_employee WHERE eid=1;
首字母大写：
SELECT CONCAT(UPPER(LEFT(sname, 1)), RIGHT(sname,CHAR_LENGTH(sname)-1)) FROM student;
#把ename凑够3个字符，不够3个字符在左边填充xx
SELECT LPAD(sname,3, '_') FROM student;
SELECT TRIM(BOTH '*' FROM '***hello   world   ');


日期时间函数
SELECT NOW() ->年月日时分秒, CURDATE() --> 年月日, CURTIME()-->时分秒 
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())
#WEEK(NOW())当前是一年中的第几周；weekday，周一是0；DAYOFWEEK，周日是1，周一是2;DAYNAME(NOW())是正确的，周日是Sunday
SELECT WEEK(NOW()),WEEKDAY(NOW()),DAYOFWEEK(NOW()),DAYNAME(NOW())
#时间间隔
SELECT DATEDIFF('2019-01-01', NOW()); #9
SELECT TIMEDIFF(STR_TO_DATE('12:00:00', '%H:%i:%S'), CURTIME())


SELECT STR_TO_DATE('2019年1月3','%Y年%c月%e')#2019-01-03
SELECT DATE_FORMAT(CURDATE(),'%Y年%c月%e'); #2018年12月23

#今天再过20天是什么日期
SELECT DATE_ADD(NOW(),INTERVAL 20 DAY); #2019-01-12 16:28:19

CURDATE() 或 CURRENT_DATE()    返回当前日期
CURTIME() 或 CURRENT_TIME()    返回当前时间
NOW()

4、流程控制函数
（1）ifnull(x,value)：如果x为null，那么就用value
SELECT ename, salary + salary * IFNULL(commission_pct, 0) FROM t_employee;
SELECT ename, ROUND( salary + salary * IFNULL(commission_pct, 0), 2) FROM t_employee

（2）if(x,value1,value2)：如果x为true，就用value1，否则用value2
SELECT IF(15%2=0, '偶数', '奇数');

（3）case 
   when 条件  then 值
   when 条件  then 值
   when 条件  then 值
   ...
   else 值
   end
   相当于java的if..else
SELECT ename,CASE
WHEN salary >= 20000 THEN '高薪'
WHEN salary >= 15000 THEN '正常'
WHEN salary >5000 THEN '底薪'
ELSE '低保'
END AS "薪资水平"
FROM t_employee;


  （4）case 表达式
  when 常量值 then 值
  when 常量值 then 值
  ...
  else 值
  end
  相当于Java的switch..case

SELECT ename, CASE eid
WHEN 2 THEN '二'
WHEN 4 THEN '四'
ELSE eid
END
FROM t_employee;

===>>分组之后显示'合计'这栏
SELECT gender, COUNT(*)
FROM t_employee
GROUP BY gender WITH ROLLUP; #会有合计这一项

SELECT IFNULL(gender, '合计'), COUNT(*)
FROM t_employee
GROUP BY gender WITH ROLLUP;

SELECT CASE
WHEN gender IS NULL THEN '合计'
ELSE gender END,
COUNT(*) FROM t_employee 
GROUP BY gender WITH ROLLUP;

SELECT IFNULL(did, '合计'),
COUNT(*) FROM t_employee
GROUP BY did WITH ROLLUP;


加密函数
INSERT INTO  USER VALUES('kk', PASSWORD('123'), NOW())
INSERT INTO  USER VALUES('kk', MD5('123'), NOW())

SELECT * FROM USER WHERE `password` = MD5('123');

 事务

事务：一组操作（SQL）要么同时成功要么同时失败。
例如：
    张三给李四转账500元，原来张三的余额是1000，李四的余额是1000
    （1）张三的账号余额减少500元
    （2）李四的账号余额增加500元
    （1）和（2）的操作要么同时成功，有一个失败都回滚（还原到最初）

事务分为：
1、同一个数据库中的不同表或同一个表   2、分布式事务：不同数据库的事务    

事务的特点：ACID  
A(Atomicity):原子性：事务中的操作不可再分了，一组是在一起。
    要么同时成功要么同时失败。
C(Consistency):一致性
    要么都成功，成功后的状态，张三的余额是500，李四的余额是1500
    要么都失败，还原到最初的状态，张三的余额是1000，李四的余额是1000
I(Isolation)：隔离性
    张三给李四转账的事务，是否成功与王五给李四转账的事务无关
D(Durability)：持久性
    一旦事务提交了，就无法回滚了
    
事务：
1、手动提交
2、自动提交：默认    
  每执行成功一句SQL，就提交了，无法回滚
    
如何开始事务？    
1、开始手动提交模式  set autocommit= false; 全局性 
每一组操作SQL,一直到commit或rollback就是一个事务。
    
SET autocommit=FALSE;
INSERT INTO USER VALUES('aa','345',NOW()); #这是一组事务
COMMIT;#提交事务；手动开启了事务，只有提交了事务才能真正的插入数据库（在当前的表中可以看到插入的数据，但在另外一个连接却看不到，commit提交后可以看到）
INSERT INTO USER VALUES('bb','567',NOW());     #这是另一组事务

2、单独开启一个事务
START TRANSACTION; #开启单次事务
INSERT INTO USER VALUES('cc','123',NOW());
COMMIT; #这次事务结束
INSERT INTO USER VALUES('dd','123',NOW()); #如果当前是自动提交模式，那么就会自动提交

事务的提交与回滚：
(1)提交：commit;
(2)回滚：rollback;  在commit之前rollback是有效的。

事务的安全性：
事务的隔离性是通过多个线程来实现的。即每一个事务是单独的线程来完成的。

多个线程就会有安全问题。表现的现象：
（1）脏读：事务1，读取到了事务2没有提交的数据
（2）不可重复读：事务1，读取到了事务2已提交的数据，在事务1中前后两次读取，结果不一样，因为被事务2给修改了某些记录的值
（3）幻读：事务1，读取到了事务2已提交的数据，在事务1中前后两次读取，结果不一样，因为事务2增加或删除了数据，多了记录或少了记录

为了解决不同程度的安全问题，mysql提供了不同的隔离级别。
mysql一共有四个等级：
（1）READ-UNCOMMITTED：可以读取到未提交的数据；这个隔离级别是最低，它会出现脏读、不可重复读、幻读
（2）READ-COMMITTED：可以避免脏读；它会出现不可重复读，幻读
    
（3）REPEATABLE-READ：默认的 可以避免脏读和不可重复读；它本来会出现幻读，但是mysql5.几之后，优化了，可以避免幻读
（4）SERIALIZABLE：能避免脏读，不可重复读，幻读

在mysql中REPEATABLE-READ和SERIALIZABLE：能避免脏读，不可重复读，幻读
　　区别：REPEATABLE-READ锁的是某几行记录，行锁
      SERIALIZABLE：锁的是表，表锁，其中一个事务在操作一张表时，另一个事务就不能操作。

    
如何设置事务的隔离级别：
    SET tx_isolation ='READ-UNCOMMITTED'
如何查看事务的隔离级别：
    SELECT @@tx_isolation