MySql数据库基本语法（DQL,DML,DDL,TCL,DCL）,CRUD操作

表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的可读性强；一个表包括行和列：行被称为数据/记录；列被称为字段
每一个字段应该包括哪些属性？
字段名，数据类型，相关的约束

DQL（数据查询语言）：查询语句，凡是select语句都是DQL
DML（数据操作语言）：insert，delete，update对表中的数据进行增删改
DDL（数据定义语言）：create，drop，alter对表结构的增删改
TCL（事务控制语言）：commit提交事务，rollback回滚事务
DCL（数据控制语言）：grant授权，revoke撤销权限等
CRUD操作：Create(增) Retrieve (检索) Update(修改) Delete(删除)

导入数据：dos命令窗口
第一步：登录mysql数据库管理系统
mysql -uroot -p
第二步：查看有哪些数据库
show databases; (这个不是SQL语句，属于MySQL的命令)
第三步：创建属于自己的数据库
create database 数据库名（bms）； (这个不是SQL语句，属于MySQL的命令)
第四步：使用（bms）数据
use 数据库名(bms); （这个不是SQL语句，属于MySQL的命令）
第五步：查看当前使用的数据库中有哪些表？
show tables； (这个不是SQL语句，属于MySQL的命令)
show tables from 数据库名；查看某个数据库中的表
第六步：初始化数据
source 文件路径
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，这种文件被称为sql脚本；直接使用source命令可以执行sql脚本，sql脚本中的数据量太大的时候无法打开，使用source命令完成初始化
删除数据库：drop database 数据库名
查看表结构：desc 表结构名；
查看表：select * from 表结构名
常用命令：select database(); 查看当前使用的是哪个数据库
select version(); 查看mysql的版本号
\c 结束一条语句
exit 退出mysql
查看创建表的语句：show create table 表结构名；

简单的查询语句（DQL）
语法格式：select 字段名1，字段名2，… from 表名；
任何一条sql语句都以";"结尾
sql语句不区分大小写
字段可以进行数学运算
sql语句的字符串用单引号括起来
给查询结果的列表重命名？
select ename，sal*12 as yearsal from 表名
查询所有字段？
select * from 表名；
条件查询：select 字段,字段… from 表名 where 条件；
执行顺序：先from，再where，再select，最后order by

排序：select 字段… from 表名 order by 字段 （asc/desc）,字段 （asc/desc）； 只有前面的字段相等时（即无法完成排序时），才能用得上后面的字段
默认是升序排序，如何指定升序或降序？
asc表示升序，desc表示降序
单行处理函数：输入一行输出一行
ifnull() 空处理函数：ifnull（可能为null数据，被当做什么处理）
所有数据库都规定：在数学表达式中，有NULL进行运算，结果都为NULL

分组函数/多行处理函数？
count计数 sum求和 avg 取平均 max 最大值 min最小值
所有的分组函数(只有5个)都是对“某一组”数据进行操作的；多行处理函数：输入多行，输出一行
分组函数自动忽略NULL
SQL语句规定分组函数不能直接用在where子句中，因为group by 是在where之后才能执行
count(*)和count(具体的某个字段)的区别：前者不是统计某个字段中数据的个数，而是统计总记录条数；后者表示统计某字段中不为NULL的数据总数量
分组函数也可组合使用
group by:按照某个字段或某些字段进行分组
having：对分组之后的数据进行再次过滤
例如：select max(sal) from 表名 group by job；
分组函数一般都会和group by 联合使用，并且任何一个分组函数都是在group by 语句执行结束之后才会被执行的；当一条sql语句没有group by时整张表的数据会自动成一组
select … from… where … group by … having … order by … limit…执行顺序：from，where，group by，having，select,order by，limit,
distinct：去除重复记录；它只能出现在所有字段的最前面
select distinct 字段1，字段2 from 表名：表示字段1字段2联合起来去重

连接查询分类：
按照连接方式：
内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接
全连接
笛卡尔积现象：当两张表进行连接查询时，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
表的别名的好处：执行效率高，可读性好
如何避免笛卡尔积现象？加条件进行过滤
避免了笛卡尔积现象不会减少记录的匹配次数，只不过显示的是有效记录
…
A
join
B
on
连接条件
where
…

	select
		e.ename,d.dname        //字段
	from 
		emp e				//表名
	(inner) join
		dept d				//表名
	on 
		e.deptno=d.deptno;

内连接之非等值连接：连接条件中的关系是非等量关系
自连接：一张表看作两张表，自己连接自己

	select
		a.ename as ' ',b.ename as ' '
	from 
		emp a
	join 
		emp b
	on
		连接条件

内连接：进行连接的两张表是平等的，表中能匹配上的记录才能查询出来
外连接：进行连接的表一张是主表，一张是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据没有和主表中的数据匹配上时，副表自动模拟出NULL与之匹配;即主表的数据无条件的全部显示
左外连接：表示左边的这张表是主表
右外连接：表示右边的这张表是主表
左连接有有链接的写法，右连接也有对应的左连接的写法

	//左连接
	select
		a.ename as ' ',b.ename as ' '
	from 
		emp a
	left (outer) join 
		emp b
	on
		连接条件
	//右连接
	select
		a.ename as ' ',b.ename as ' '
	from 
		emp b
	right join 
		emp a
	on
		连接条件

3张以上表的连接查询
A表和B表连接，连接之后A表/A与B连接之后的结果 继续和C表连接

	select 
		.....
	from 
		A
	join
		B
	on
		连接条件
	join
		C
	on
		连接条件

子查询：select语句当中嵌套select语句，被嵌套的select语句是子查询
子查询可以出现在

	select
		...(select)
	from
		...(select)
	where
		...(select)
	
	select
		t.*,s.grade
	from 
		(select deptno,arg(sal) as avgsal from emp groud by deptno) t
	join
		salgrade s
	on
		连接条件 

	select 
		e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname
	from
		emp e;

union:可以将查询结果集相加

	select ename,job from emp where job='MANAGER'
	union
	select ename,job from emp where job='SALESMAN';
	select ename,job from emp where job in('MANAGER' , 'SALESMAN');

limit: limit startIndex,length
startIndex表示起始位置，从0开始，0表示第一条数据
length：表示取几个

	//取出前5个
	select ename,sal from emp order by sal desc limit 0,5;
	select ename,sal from emp order by sal desc limit 5;

标准分页sql：
每页显示pageSize条记录：
第pageNo页：（pageNo-1）*pageSize,pageSize

	例如：每页显示3条记录
	第1页：0,3
	第2页：3,3
	第3页：6,3
	....

创建表：
create table 表名(
字段名1 数据类型，
字段名2 数据类型，
…
)；
表名在数据库中一般以t_或tbl_开始
insert语句插入数据：
insert into 表名（字段名1，字段名2，…）values （值1，值2，…);
要求：字段的数量和值的数量相同，并且数据类型要对应相同
insert into 表名 values （值1，值2，…);此时值的数量和顺序都必须和表中的字段名一致
insert into 表名（字段名1，字段名2，…）values （值1，值2，…)，（值1，值2，…)； 一次性插入多行
当一条insert语句执行成功之后，表格中必然会多一条记录，即使这一行记录当中某些字段是NULL，也没法再执行insert语句插入数据，只能使用update进行更新

	drop table if exists t_student;  //当这个表存在就删除
	create table t_student(
		no bigint,   //学号
		name varchar(255),
		sex  char default  ..,  //设定默认值
		classno varchar(255),
		birthday char(10)
	);
	insert into t_student(no,name,sex,classno,birthday) values (2,'zhal'，'12','1ban','1324-02-23');

表的复制：create table 表名 as select 语句；将查询结果当做表创建出来
将查询结果插入到一张表中：
insert into 表名 select语句； 字段数得相同

修改数据：
update 表名 set 字段名1=值1，字段名2=值2… where 条件；
注意：没有条件整张表数据全部更新
删除数据：
delete from 表名 where 条件；
注意：没有条件全部删除
删除大表：
truncate table 表名； //表永久丢失

约束(Constraint):在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性，有效性，完整性
常见的约束有：
非空约束（not null）：约束的字段不能为NULL，只有列级约束，没有表级约束
唯一约束（unique）：约束的字段不能重复，但可以为NULL
主键约束（primary key）：约束的字段既不能为NULL，也不能重复，简称PK
外键约束（foreign key）：简称FK
检查约束（check）：Oracle有check约束，mysql没有

主键约束相关术语：主键约束，主键字段，主键值；表的设计三范式中要求任何一张表都应该有主键；一张表的主键约束只能有一个
主键的作用：主键值是这行记录在这张表中的唯一标识
主键的分类：
按主键字段的字段数量划分：单一主键，复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式 ）；
按主键性质划分：自然主键，业务主键（主键值最好是一个和业务没有关系的自然数，因为用业务作为主键，业务一旦发生改变时，主键值也可能需要发生改变，但有时可能因为变化可能导致主键值重复，而无法改变）
主键值自增

	create table t_user(
		int id not null,    
		username varchar(255),
		usercode varchar(255),
		unique(username,usercode)      //多个字段联合起来添加一个约束，表级约束
	);
	create table t_user(
		id int primary key auto_increment,   //主键值自动自增，从1开始
		username varchar(255) unique,   //列级约束
		usercode varchar(255) unique   
	);

外键约束相关术语：外键约束，外键字段，外键值
外键值可以为NULL；外键字段引用其他表的某个字段的时，被引用的字段不一定为主键，但必须具有唯一性，即至少具有unique约束
顺序要求：
创建表时，先创建父表，再创建子表
删除表时，先删除子表，再删除父表
删除数据时，先删除子表，再删除父表；
添加数据时，先添加父表，再添加子表
t_student中 classno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class表叫做父表

	drop table if exists t_student;
	drop table if exists t_class;
	create table t_class(
		cno int primary key
	);
	create table t_student(
		classno int,
		foreign key(classno) references t_class(cno) 
		...
	);

事务（Transaction）:一个事务是一个完整的业务逻辑单元，不可再分
和事务相关的语句只有：DML语句（insert，delete，update），因为DML语句都是和数据库表中的“数据”相关的，事务的存在是为了保证数据的完整性，安全性
如果所有的业务都能使用1条DML语句完成，那么不需要事务机制，但实际不是这样
事务的特性：ACID
A原子性：事务是最小的工作单元，不可再分
C一致性：事务必须保证多条DML语句同时成功或者同时失败
I隔离性：事务A和事务B之间具有隔离性
D持久性：持久性指最终数据必须持久化到硬盘中，事务才算成功结束
提交事务commit：将数据存储到硬盘中；回滚事务rollback：回到上一次事务提交点，不会改变硬盘中的数据
关于事务之间的隔离性：事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别：读未提交(read uncommitted)
对方事务还没提交，当前事务就可读取到对方未提交的数据
读未提交存在脏读(Dirty Read)现象：表示读到了脏数据(不稳定)
第二级别：读已提交(read committed)
对方事务提交之后的数据当前事务可以读取到
这种隔离级别解决了脏读现象
读已提交存在的问题是：不可重复读
第三级别：可重复读(repeatable read)
这种隔离级别解决了不可重复读问题,但存在问题：读取到的数据是幻象
第四级别：序列化读/串行化读(serializable)
解决了所有问题；但效率低，需要事务排队
mysql数据库默认的隔离级别是：可重复读
oracle数据库默认的隔离级别是：读已提交

	set global transaction isolation level read uncommitted;    //设置隔离级别
	select @@global.tx_isolation;   //查看隔离级别

mysql事务默认情况下是自动提交的，即只要执行任意一条DML语句则提交一次；使用start transaction;关闭自动提交

	drop table if exists t_user;
	create table t_user(
		id int primary key auto_increment,
		username varchar(255)
	);
	start transaction;
	insert....
	commit;
	...

索引：
在数据库中，查询一张表有两种检索方式：根据索引检索；全表扫描，
索引可以提高检索效率的最根本的原因是缩小了扫描的范围，索引虽可以提高检索效率，但不能随意的添加索引，因为索引也是数据库中的对象，也需数据库不断维护
什么时候考虑给字段加索引？
数据量庞大
该字段很少的DML操作
该字段经常出现在where子句中
注意：主键和具有unique约束的字段会自动添加索引
查看语句的执行计划：explain 语句；
创建索引对象：
create index 索引名称 on 表名(字段名);
删除索引对象：
drop index 索引名称 on 表名；
索引的分类：
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：unique约束的字段上会自动添加索引
…
索引什么时候失效？
模糊查询的时候，第一个通配符使用的是%，这时索引是失效的

视图：同一张表的数据，通过不同的角度去看待
创建视图：
create view 视图名 as select…；
drop view 视图名；
注意：只有DQL语句才能以视图对象的方式创建出来
对视图进行增删改查会影响到原表数据；通过视图影响原表数据，不是直接操作的原表，可以对视图进行CRUD操作
视图可以隐藏表的实现细节

数据库设计三范式：
设计范式：设计表的依据，按照三范式设计的表不会出现数据冗余
第一范式：任何一张表都应该有主键，并且每一个字段是原子性的不可再分
第二范式：建立在第一范式基础上，所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：建立在第二范式基础上，所有非主键字段直接依赖主键，不能产生传递依赖

MySQL中字段的常用数据类型？
int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 变长字符串
date 日期类型
BLOB 二进制大对象（存储图片，视频等流媒体信息）
CLOB 字符大对象（存储较大文本）

MySQL的数据类型
1.整数系列
TINYINT：非常小的整数
SMALLINT：小整数
MEDIUMINT：中整数
INT/INTEGER：整数
BIGINT：大整数
如何使用
（1）int 等价于int（x）
（2）int(M)：M表示宽度，需要结合另外的两个参数使用
unsigned：表示无符号，即没有负数；
zerofill：表示用0填充
例如：int（2） unsigned zerofill：表示每一个整数有5位数字，如果不足5位的 在左边添0；如果只是单独int(2)就不起作用
2.浮点小数
float：4个字节
double：8个字节
如何使用？
（1）float或double
（2）float(M,D)或double(M,D):M表示总位数，D表示精度，即小数点后几位
例如：double(3,2)表示的数据范围[-999.99,999.99]，超过这个范围会报错
3.定点小数
DECIMAL
DECIMAL(M,D)
4.日期时间类型
date：只能表示日期
time：表示时间
datetime：表示日期和时间
year：表示年
timestamp：表示日期和时间
（1）timestamp实际存储的毫秒值，显示时，显示根据毫秒值算出来一个本地化时间；datetime就是存储日期时间
（2）timestamp因为存储的毫秒值，所以在比较和计算时效率较高
（3）datetime和timestamp可以表示的时间范围是不同的
5.字符串（MySQL没有单字符类型，只有字符串类型）
char：定长字符串，长度[1,255]，char,char(1)长度为1个字符;
char(2)长度为2个字符
varchar:变长字符串
varchar(M):必须指定M，表示最长不超过M个字符
text：长文本，一般用于存储文字比较多的，又不经常检索的信息
char和varchar
（1）内存占用空间不同
char(M):M字符就占M个字符的空间,如果未存满，那么空的字符就存\u0000
varchar：实际几个字符+2个字节的空间
（2）varchar节省空间，但是每次存取都要数字符个数；
char:看起来浪费空间，但是每次存和取都按照固定的长度去处理的 ，效率较高；
6.位类型
二进制的位类型，BIT(M)类型允许存储M位值，M的范围为1~64，默认为1

运算符：> , <,=， >=,<=,<>/!=, and,or, in,
[12,30]:between 12 and 30; xx>=12 and xx<=30;
between and 使用时必须左小右大，除了可以使用在数字方面之外，还可以使用在字符串方面，between ‘A’ and ‘C’;左闭右开
在数据库中NULL不是一个值，代表什么也没有，不能用等号衡量，使用 is null或者is not null
in等同于or：in(100,200); //等于100或200
not in：不在这几个值当中
模糊查询like：在模糊查询中的两个符号，%，_，%代表任意多个字符， _代表任意一个字符
like ‘%o%’:含有o的； like ‘_A%’:第二个字母为A；
like ‘%_%’:含有下划线的