1.sql规范

1.1基本规则

• SQL可以下载一行或者多行。为了提高可读性，各字句分行写，必要时使用缩进
• 每条命令以；或\\g或\\G结束
• 关键字不能被缩写或也不能分行
• 关于标点符号
  • 必须保证所有的（）、单引号、双引号是成对出现的
  • 必须使用英文状态下的半角输入方式
  • 字符串和实践日期类型的数据可以用单引号表示
  • 列的别名，尽量使用双引号，而且不建议省略as

1.2SQL大小写规范

• 数据库名、表名、表的别名、字段名、字段别名等都小写
• SQL关键字、函数名、绑定变量都大写

1.3注释

#单行注释（mysql特有）#
-- 单行注释 --
/*多行注释*/

1.4命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z,a-z,0-9,_共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或者常用的方法冲突，如果坚持用，请在SQL语句中使用`引起来
• 保证字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性，加入数据类型在一个表里是整数，那在另一个表里可就被变成字符型

1.Mysql连接

mysql -uroot -p123456  #直接输入密码
mysql -uroot -p #二次输入密码

2.数据定义语言

2.1创建、删除、查询数据库

create databases 数据名库；
drop databases 数据库名；
show databases;

2.2修改表结构

向表中添加字段

例子：

alter table temp add sex varchar(50) NOT NULL comment \'男为0，女为1\'

修改某个字段的数据类型和约束

alter table temp modify sex int(1) not null

修改字段名字

alter table temp change sex xingbie int(1) not null

删除字段

alter table temp drop xingbie

选择数据库

use 数据库名

2.3创建表

例子：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.4删除表

例子：

DROP TABLE runoob_tbl ;

3.Mysql数据类型

3.1数值类型

3.2日期和时间类

3.3字符类型

3.4常见问题

char和varchar的区别:

char固定长度，存储的元素底层自动赋定义长度的存储空间，varchar可变长度，存储的元素自动计算元素的大小，赋一个相同大小的内存，大小不能超过定义的长度，一般情况下，字符串长度确定用char，不确定用varchar，char的效率高，但是浪费空间，varchar的效率低，但是空间利用率高

3.数据操作语言

3.1插入数据

例子：

insert into temp(id, username) values(1,\'张三\')

3.2修改数据

update 表明 set 字段1=值1，字段2等于值2… where 条件

update temp as t set username=\'修改后的张三\' where t.id = \'1\'

3.3删除数据

delete from 表名 where 条件(不建议给表使用别名）

delete from temp where id = \'1\'

delete语句如果想要使用别名，需要把语句改为以下形式

delete 别名 from 表名 别名 where 过滤语句

4数据查询语言

4.1基本格式

select * from 表名

4.2条件查询

select * from 表名 where 条件语句

4.3模糊查询

select * from 表明 where 字段名 like 模拟条件

%代表任意字符，_代表一个字符,如果要进行带有%或者_的查询时，要用转义字符’’

4.4联合查询

select * from 表名 where 条件
union
select * from 表名 where 条件

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的查询的每一列的类型和顺序最好是一致
• union关键字默认去重，如果使用union all 可以包含重复项

4.5排序

select * from 表名 where 条件 order by 顺序

ASC 表示升序，DESC表示降序

4.6分组

select * from 表名 where 条件 group by 字段名

4.7连接

• 内连接（inner join）：只有左右两张表匹配的行才能显示
• 外连接:副表不存在的数据赋值为null
  • 左外连接:左边的表不加限制
  • 右外连接:右边的表不加限制
  • 全外连接:myql已经删除

笛卡尔积现象

当两个表没有任何限制条件是，连接的次数就会是两个表的记录数之积

解决方案：设置限制条件，但是连接次数没有变，只是改变了显示的数据。

4.8Null值处理

• IS NULL ：当列的值为null，此运算符返回true
• IS NOT NULL：当列的值不为null，运算符返回true
• <=>:比较操作符，当比较的两个值相等或者都为null时返回true

4.9正则表达式

用法类似于like

select * from 表明 where 字段名 regexp \'正则表达式\'

4.10常见的查询语句

select * from 表
inner join 连接表
on 连接条件
...
where 过滤条件
group by 分组条件
having 过滤条件
order by 排序条件

4.11过滤

where

where 条件

having

having语句与where语句相似，但是having在group by后执行，where在前，having只有在使用了group by时才能用，效率低，因此能用where过滤掉的尽量用where过滤。

having 条件

4.12去重

select distinct comptemplate.* from comptemplate

5.事务

5.1事务概念

事务：最小的不可再分的工作单元；通常一个事务对应一个完整的业务。

在事务进行过程中，未结束之前，DML语句不会更改底层数据，它只是将历史数据记录一下，在内存中完成记录。只有在事务结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据。

5.1基本要素

• 原子性：一个事务中所有的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有被执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读已提交（read committed）、可重复读（repeatable read）和序列化（Serializable）。

读未提交：对方失误还没有提交，我们当前事务可以读取到对方未提交的数据
读已提交：对方事务提交之后的数据我方可以读取到，这种隔离级别解决了脏读现象
可重复读：读取的数据始终是第一次读入的数据，此数据是备份的数据，不是真实的数据，真实的数据实际上已经改变。
序列化读/串行化读：只有当该线程上的数据被提交，该数据才能被再次修改。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

5.2事务的并发问题

• 脏读：指一个线程中的事务读取到另外一个线程中未提交的数据。
• 不可重复读：是指在一个事务内，多次读取同一事务，前后两次读取到的结果不一致，导致了不可重复读。
• 幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。

5.3事务相关命令

set autocommit = 1 # 开启自动提交（默认，使用事务时需要关闭）
set autocommit = 0 # 禁止自动提交
begin #开启事务
rollback #事务回滚
commit #事务确认

5.4锁

• 悲观锁：如果一个事务正在对数据处理，那么在整个事务中，其他事物都无法对这个数据进行更新操作，知道该事务释放了这个锁。
• 乐观锁：提交更新请求之前，要先去检查读取这个数据之后该数据是否发生了变化，如果发生了变化，那么你此次提交就要放弃，如果没有变化就可以提交。

6.索引

6.1索引的定义

相当于给数据设置一个目录，提高数据的查询效率

6.2索引使用的场所

• 表中该字段中的数据量庞大
• 经常被检查，经常出现在where语句中的字段
• 不能用于经常改变的数据。
• 逐渐会自动添加索引，所以建议用主键作为查询条件

6.3创建和删除索引

创建索引

基本的创建索引

CREATE INDEX indexName ON table_name (column_name)

修改表结构时添加索引

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
	id INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [indexName] (username(length))  
); 

删除索引

DROP INDEX [indexName] ON mytable; 

6.4索引的类型

• 唯一索引：表上一个字段或者多个字段的组合建立的索引，这些字段组合起来能够确定唯一，允许存在空值（只允许存在一条空值）
• 非唯一索引：表上一个字段或者多个字段的组合建立的索引，可以重复，不需要唯一
• 主键索引：根据主键pk_clolum建立的索引，不允许重复，不允许空值。
• 聚合索引：表中记录的物理顺序与键值的索引顺序相同
• 非聚合索引：表中记录的物理顺序和兼职的索引顺序无关
• 全文索引：在某个字段设置全文索引后，根据特定于法查找满足条件的字段。
• 普通索引：用表中的普通列构建的索引，没有任何限制
• 组合索引：用多个列组合构建的索引，但是使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要
• Hash索引：通过索引列的值计算hashCode，之后在相应的物理位置存取索引列的值，由于hash Code的唯一性，因此hash索引不能进行范围查找或者顺序查找。

6.5索引实现的底层原理

mysql采用B+ Tree作为索引结构，通过B Tree缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据之后，获取到相关的物理地址之后，定位表中的数据，从而实现高效率。

6.6注意点

• 不要使用select * 作为查询条件，因为使用了select * 之后就相当于全表查询，索引就失去了意义。

7.临时表和序列（了解内容）

7.1临时表

只在当前连接可见，当关闭连接时，mysql会自动删除并释放所有空间。

7.2序列

mysql序列是一组整数：1，2，3…，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用Mysql序列

8.存储引擎

8.1什么是存储引擎

表的存储方式

8.2查看存储引擎

show engines \\G

8.3常见的存储引擎

• MyISAM

采用三个文件组织一张表，xxx.frm(存储表结构的文件)、xxx.MYD（存储表中数据的文件）、xxx.MYI（存储表中索引的文件），不支持事务，可被压缩，并且可以转换为只读表

• InnoDB

表结构在frm文件中，数据储存在tablespace表空间中，无法被压缩，无法转换为只读，在Mysql数据库崩溃之后提供自动恢复机制，支持级联删除和级联更新

• MEMORY

不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中的，查询速度快。

9.约束

9.1约束的含义

保证表的合法性，有效性，完整性

9.2常见的约束

非空约束(not null)

约束的字段不能为null

主键约束（promary key）

约束的字段不能为空，也不能重复，一个表中只能存在一个，其标识作用。
主键自动维护机制：字段后加一个auto_increment，这个字段将每存入一条数据，字段值自动加一。

外键约束（foreign key）

引用的字段名必须具有唯一性（是主键或者有unique约束均可），并且引用的字段类型必须要与被引用的字段名的数据类型相同。

10.视图

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，Java程序员只对视图对象进行CRUD；

11.数据库设计三范式

• 第一范式：任何一张表都有主键，每个字段具有原子性不可再分
• 第二范式：在第一范式的基础上，所有的非主键字段完全依赖主键，不能出现部份依赖
• 第三范式：在以上两种范式的基础上，所有的非主键字段直接依赖于主键，不能出现传递依赖。

设计模式

• 一对一设计模式

主键共享，外键唯一

• 一对多设计模式

两张表，多的表加外键

• 多对多设计模式

三张表，添加中间表