这是我的史上最细SQL实战系列的第一篇文章：基础知识篇。本系列文章核心不在于总结Mysql相关的八股（Mysql超全八股可见此文：https://www.nowcoder.com/discuss/583297999409889280?sourceSSR=search），本系列旨在：1.快速掌握sql知识，会写面试时常见的sql题目；2.了解在实际工作中需要注意的超多数据库和表设计相关的知识和坑点。

本系列文章分为3个部分：1基础知识篇；2sql笔试篇；3公司级数据库与表设计相关知识与坑点总结。

另外：

想要学习Java冲实习或冲春招的，我能助你一臂之力，我之前整理了高质量可速成的魔改外卖项目话术和7000字轮子项目话术，还有超全超精品八股大全专栏，怎么写简历，怎么包装实习经历，怎么0基础速成冲春招和实习等等等等精品帖子，大家可以去看看我的精品文章汇总帖子：https://www.nowcoder.com/discuss/721704696242536448?sourceSSR=users

我的八股大全、算法、项目话术全专栏（20w人学习，超千人订阅，牛客最受欢迎最高质量java八股专栏，内容包含： 1.八股大全：多一句没有少一句不行的最精简八股整理，完全可以应付校招社招的八股拷打！ 2.速成项目话术：目前有魔改苍穹外卖项目话术（额外扩展了很多技术亮点），能速成拿去面试，后面会更新魔改黑马点评、商城项目等等热门高质量项目话术 3.智力题超详细题解汇总； 4.面试时非技术问题话术整理，绝对震惊面试官一年； 5.算法lc hot100全题系列题解：绝对通俗易懂：https://www.nowcoder.com/creation/manager/columnDetail/j8ZZk0

---------------------------------------------------------------------------------

1.基础知识

1.1 什么是关系型数据库？

关系数据库管理系统RDBMS(Relational Database Management System)的特点：

• 数据以表格的形式出现
• 每行为各种记录
• 每列为记录所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成database

1.2 数据类型

MySQL 支持多种数据类型，包括：

• 数值类型：INT、FLOAT、DOUBLE、DECIMAL
• 字符串类型：CHAR、VARCHAR、TEXT、BLOB
• 日期与时间类型：DATE、TIME、DATETIME、TIMESTAMP

1.2.1 数值类型

DECIMAL说明：

1）M 表示数据的最大总长度(不包括小数点，范围为1～65)；D 表示：固定小数位（范围0～30，但不得超过M）；

例：decimal(5,2)

可以存储123.45 ，存入数据的时候，按四舍五入计算。

2）在计算总长度时要优先考虑小数位，也就是D的约束

例：DECIMAL(5,3)

1.2345 --- 小数点后最多3位，自动四舍五入数据截断后保存，1.235

1.2 --- 小数未满部分补0。按照1.200保存。

123.45 --- 因为小数部分未满3位，要补0.所以保存应该123.450。所以整个位数超出了5，有问题。

3）D不能超过M值，若D等于M，如DECIMAL(5,5)

最大存储值为0.99999

4）适用场景

适合保存货币值，比如话费就可以用decimal来装的

int(xx)说明：

int(11)不是限制int的长度为11位，而是字符的显示宽度，例如插入数据1，显示为00000000001。

在字段类型为int时，无论你显示宽度设置为多少，int类型能存储的最大值和最小值永远都是固定的。

1.2.2 日期类型

1.2.3 字符串类型

CHAR和VARCHAR都需要指定长度，他们的区别在于：

1）存储方式不同：

• char 对英文(ASCII)字符占用1个字节，对一个汉字占用2个字节，varchar 对每个英文(ASCII)字符都占用2个字节，对一个汉字也只占用两个字节。但对于utf8，一个字符都会占用3个字节。
• char(n)，如果实际使用字符不足n，会在后面用空格补全存入数据库中。varchar(n)则不会。
• 因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度，如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节。例如对于utf8：

2）char效率高于varchar

因此当在长度固定的场景，例如：身份证号，手机号，电话等用char效率高，空间也不浪费

1.2.4 二进制类型

2.数据库相关操作

查询所有数据库:
mysql> show databases;

创建数据库（tests）并编码（utf-8）：
mysql> create database tests character set utf8 collate utf8_general_ci;

选择(使用)数据库:
mysql> use tests;

查询当前正在使用的数据库名称
mysql> select database();

创建数据库:
mysql> create database 数据库名称;

判断数据库不存在再创建: 
mysql> create database if not exists 数据库名;

删除数据库:
mysql> drop database 数据库名称;

判断数据库存在再删除:
mysql> drop database if exists 数据库名称;

3.数据表相关操作

创建表：

CREATE TABLE [if not exists] 表名(
属性名 数据类型 [完整性约束条件],
. .
属性名 数据类型 [完整性约束条件]

)[ENGINE=引擎名 AUTO_INCREMENT=自动累加起始值 CHARSET=编码格式;];

查看所有数据表：
mysql> show tables;

创建数据表：
mysql> create table 表名 (
	ID int not null primary key, # 不为空，设为主键
	name varchar(20)
	);

复制数据表：
mysql> create table 表名 like 来源数据表名;

查看表结构：
mysql> desc 数据表;
或
mysql> describe 数据表;
show create table 数据表；查看表详细结构，可以看到建表sql
修改表名:
mysql> alter table 表名 rename to 新表名;

修改表的字符集(编码):
mysql> alter table 表名 character set 字符集名称;

添加字段：
mysql> alter table 表名 add 字段 数据类型;

删除列:
mysql> alter table 表名 drop 列名;

删除表
mysql> drop table 表名;
或
mysql> drop table if exists 表名 ;

添加数据:
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...);

添加多条数据
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

删除数据:
mysql> delete from 表名 where 条件

如果有用到自增ID，又想删除全表的话，用delete删除的话有个弊端就是ID还是会从原有的基础上往上类。建议最好使用：
mysql> truncate 表名;
因为该方式效率较高，把原表直接drop掉然后再新建一张一模一样的。所以ID还是从1开始自增。

修改数据：
mysql> update 表名 set 字段1 = 设置值1, 字段2 = 设置值2,... where 条件;
如果不加任何条件，则全表修改。

部分查询数据条件：
SELECT * FROM test WHERE id >= 100;
SELECT * FROM test WHERE id >= 100 AND  id <=100;
SELECT * FROM test WHERE id BETWEEN 100 AND 100;
SELECT * FROM test WHERE id IN (1,3,4);
// 关于NULL
SELECT * FROM test WHERE name = NULL; 错误,因为null值不能使用=或（!=) 判断
SELECT * FROM test WHERE name IS NULL;(正确)
SELECT * FROM test WHERE name  IS NOT NULL;(正确)
// 查询姓林的有哪些？< like>
SELECT * FROM test WHERE NAME LIKE '林%';
// 查询姓名第二个字是中是的人
SELECT * FROM test WHERE NAME LIKE "_中%";
// 查询姓名是三个字的人（注：为三个下划线_）
SELECT * FROM test WHERE NAME LIKE '___';
// 查询姓名中包含中的人
SELECT * FROM test WHERE NAME LIKE '%中%';

4.约束

4.1 主键约束Primary Key

能够唯一确定一张表中的的一条记录,我们通过给某个字段添加约束, 可以使得这个字段不重复且不为空.

create table user (
	id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增	
	name varchar(20)
 );
-- 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空.
create table user2 (
 	id int,
 	name varchar(20),
 	password varchar(20),
 	primary key(id, name)
);

表创建完成后：

添加主键:
alter table user add primary key(id);
或
alter table user modify id int primary key;
删除主键:
alter table user drop primary key;

4.2 唯一约束unique

unique 约束修饰的字段的值不可以重复。

create table user1 (
 	id int primary key auto_increment,
  	phone_num varchar(20) unique
  	 );
 create table user2 (
 	id int primary key auto_increment,
  	name varchar(20),
  	unique(id, name) // 表示两个字段在一起不重复就可以
  	 );

也可以在表创建完成后, 通过

添加unique约束:
alter table user3 add unique(phone_num);
或
alter table user3 modify phone_num varchar(20) unique;
删除unique约束:
alter table user3 drop index phone_num;

4.2.3 主键（PRIMARY KEY）与UNIQUE 对比

1）UNIQUE 约束唯一标识数据库表中的每条记录。它 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

2）PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

3）每张表可以有多个 UNIQUE，但是每个表只能有一个PRIMARY KEY。

4）UNIQUE 可为空，而PRIMARY KEY不能

4.3 非空约束not null

not null 修饰的字段不能为空NULL

create table user3 (
	id int primary key auto_increment,
	name varchar(20) not null
	);
删除非空约束:
alter table user3 modify name varchar(20);

4.4 默认约束default

当我们插入字段值时候,如果对应的字段没有插入值,则会使用默认值.如果传入了值,则不会使用默认值.

create table user4(
	id int primary key auto_increment,
	age int default 18,
	name varchar(20) not null
	);

4.5 外键约束：foreign key

MySQL的外键约束用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。

其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。

外键约束主要作用就是能够让表与表之间的数据建立关联，使数据更加完整，关联性更强。

create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
// 班级
create table classes(
	id int primary key,
	name varchar(20)
	);	
// 学生表
create table student (
		id	int primary key,
		name varchar(20),
		class_id int,
		constraint 'fk' foreign key(class_id) references classes(id)
		);

4.5.1 主键与外键

1）主键是唯一标识一条记录，不能有重复的，不允许为空

2）外键可以有重复的, 可以是空值，用来和其他表建立联系用的。

关于完整性，可以通过一个例子来说明

假如有两种表，一张用户账户表（用于存储用户账户），一张是账户信息表（用于存储账户中的信息）。

1）我不小心将用户账户表中的某个用户删除了，那么账户信息表中与这个用户有关的数据就变成无源数据了，找不到其属于哪个用户账户，导致用户信息不完整。

2）我在账户信息表中随便添加了一条数据，而其在用户账户表中没有对应的用户，这样用户信息也是不完整的。

如果有了外键约束，将用户账户表设为主表，账户信息表设为从表，那么就无法直接往账户信息表中插入数据，在用户账户表中删除用户，账户信息表中的用户信息也会被删除。外键约束的方式可以使两张紧密的联系在一起，保障数据完整性和一致性的同时，日常维护也更加方便。 外键创建规则： 1）必须有主表才可以设置从表。

2）主表必须实际存在。

3）必须为主表定义主键。

4）外键列的数据类型必须和主键列的数据类型相同。

5）外键列的数量必须和主键列的数量相同。

6）外键可以不是外表中的主键，但必须和主表关联字段相对应。

7）主从表创建时，存储引擎必须是InnoDB。

4.5.2 外键的使用条件

1）两个表必须是InnoDB表，MyISAM表暂时不支持外键

2） 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

3）外键关系的两个表的列必须是数据类相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以

5.查询语句基本操作

查询所有记录
例如:查询student表中的所有记录.
select * from student;

查询指定字段
例如:查询student中的sname,ssex,class.
select sname,ssex,class from student;

查询教师表中所有的单位即不重复的depart列. <排除重复distinct>
select distinct depart from teacher;

查询score表中成绩在60到80之间的所有记录 <查询区间 between…and…>
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;

查询score表中成绩为85,86或88的记录<(not)in范围查询>
select * from score where degree in(85, 86, 88);

查询student表中’95031’班或性别为’女’的同学记录. <or 表示或者>
select *from student where class = '95031' or sex = '女';

以class降序查询student表的所有记录 <降序:desc, 升序asc,默认升序(省略)>.
select * from student order by class desc;

查询student表中第二个为'好'的同学记录. <like模糊匹配>
“%”代表任意字符且任意长度，“_” 代表任意单个字符
select *from student where name like '_好%';

查询"95031’班的学生人数 <统计 count>
select count(*) from student where class = '95031';

5.1 group by分组查询

在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。

1）与 GROUP_CONCAT()函数一起使用

GROUP_CONCAT将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

例：将所有学生按年级分组

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

2）与COUNT函数一起使用

例：查询各年级的学生人数

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

3）与 HAVING 一起使用(限制输出的结果)

HAVING 子句可以让我们筛选分组后的各组数据。它的用法与where类似，但是在GROUP BY后只能用HAVING不能用WHERE。

例：

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

4）与 WITH ROLLUP 一起使用(最后加入一个总和行)；

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

5.2 sql聚合函数

 AVG() – 返回一组值的平均值。
 COUNT() – 返回一组中项目数目。
 MAX() – 返回一组中的最大值。
 MIN() – 返回一组中的最小值
 SUM() – 返回一组中所有值或不同值的和

5.3 limit分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

注意：

1.起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

2.分页查询是数据库的方言，不同的数据库的实现方式不同，mysql中用的是LIMIT。

3.如果查询的是第一页的数据，起始索引可以省略，直接简写成limit 10（这个10是每页显示的数据数）。

查询第11，12条数据
SELECT * FROM t_student LIMIT 10,2;

6.连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

1 Join(Inner Join)内连接 查出两表完全匹配的部分。（交集） 2 Left Join左连接 返回左表所有的行，右表返回匹配行，不匹配的返回NULL 3 Right Join右连接 返回右表所有的行，左表返回匹配行，不匹配的返回NULL 4 Full Join全连接 只要其中一个表存在匹配，则返回行

表t_book:

表t_booktype:

SELECT * FROM t_booktype,t_book;

6.1 内连接查询

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book as tb join t_bookType as tby ON tb.bookTypeId=tby.id;

6.2 外连接查询

外连接可以查出某一张表的所有信息，其他表多没有的字段可以置NULL

SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2；

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

6.3 全连接查询

MySQL 不识别 FULL join，所以可以通过 union

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id
UNION
SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

7.子查询

新增一个表t_pricelevel

7.1 带 In 关键字的子查询

只有子查询返回的结果列包含一个值时，比较运算符才适用。假如一个子查询返回的结果集是值的列表，这时比较运算符就必须用IN运算符代替。

IN运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

7.2 带比较运算符的子查询

子查询可以使用比较运算符，这里的子查询必须为可比较的值。

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

7.3带 Exists 关键字的子查询

使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。

SELECT * FROM t_book tb WHERE EXISTS (SELECT * FROM t_booktype tby WHERE tb.id=tby.id);

7.4带 Any 关键字的子查询

ANY关键字表示满足其中任意一个条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

7.5带 All 关键字的子查询

ALL关键字表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

7.6 将select结果保存为临时的表，作为另一个select的数据源

SELECT *FROM (SELECT * FROM t_book ) AS test WHERE test.id=1;

这里必须给临时表去一个别名，否则报错。

8.合并查询结果

使用 UNION 关键字是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录

SELECT id,price FROM t_book UNION SELECT id,booktypename FROM t_booktype;

使用 UNION ALL，不会去除掉相同的记录

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

9.事务

事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT （COMMIT WORK）。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

CREATE TABLE test(
	id INT(5)
) ENGINE=INNODB;
SELECT * FROM test;#查询为空
begin; # 开始事务
insert into test value(5);
insert into test value(6);
commit;
select * from test;#查询到两数据

begin; # 开始事务
insert into test values(7);
rollback; #回滚
select * from test; # 因为回滚所以数据没有插入

9.1 事务的ACID

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

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

10.视图

什么是视图？

• 视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表，通常由一个 SQL 查询语句定义。
• 数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。
• 一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样对视图的更新，会影响到原来表的数据。

视图的作用？

作用一：使操作简便化

比如原本表里有20个字段，视图可以只取我们需要的几个字段，方便操作，例如我们要频繁获取user表中的name和group表中的gname。我们可以创建一个视图，包含这两个字段，使用一条select *语句就能获取想要的内容。

作用二：对数据库重构，却不影响程序的运行。

例1，A表创建了视图V1，之后A表添加了新的字段，但是我们对V1的操作不受影响，只要保证V1使用的字段还在就可以。

例2，假如因为某种需求，需要将user拆分成表usera和表userb，该两张表的结构如下：

测试表:usera有id，name，age字段

测试表:userb有id，name，sex字段

这时如果使用sql语句：select * from user;那就会提示该表不存在，这时我们就可以通过创建视图来解决。

作用三：提高了安全性能。

例1，可以对不同的用户，设定不同的视图。如某用户只能获取user表的name和age数据，不能获取sex数据。则可以创建视图。使用sql语句：select * 语句最多就只能获取name和age的数据，其他的数据就获取不了了。

例2，我们对视图进行操作，只会影响我们关系的几个字段，其他的字段比如某些重要字段不会因为误操作而受到影响。

10.1视图的创建

CREATE VIEW view_name
 AS
    SELECT column_name(s) FROM table_name
WHERE condition

举例：

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book; SELECT * FROM v3;
创建视图后对字段取别名

10.2查看视图

DESCRIBE v5;看表结构
SHOW CREATE VIEW v5;看详细建表语句

10.3修改视图

使用 ALTER VIEW 可以修改已创建的视图。请注意，视图的修改通常仅限于修改查询语句，而不能改变视图的结构。

ALTER VIEW employee_view AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = 10;

10.4 删除视图

DROP VIEW employee_view;

10.5更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

以下操作同对表的操作，语法同对表操作一样

1） 插入(INSERT)

INSERT INTO v1 VALUE(NULL,'java test', 120, 'test2', 1);

2） 更新(UPDATE)

UPDATE v1 SET bookName='Java hello',price=122 WHERE id=5;

3） 删除(DELETE)

DELETE FROM v1 WHERE id=5;

10.6视图和表的区别

1. 视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化表，而表不是；
2. 视图（除过索引视图）没有实际的物理记录，而基本表有；
3. 表是内容，视图是窗口；
4. 表占物理空间，而视图不占物理空间，视图只是逻辑概念的存在；
5. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全角度说，视图可以防止用户接触数据表，从而不知表结构；
6. 表属于全局模式的表，是实表；视图数据局部模式的表，是虚表；
7. 视图的建立和删除只影响视图本身，不影响对应的基本表。

11.常用函数

11.1 日期和时间函数

1）CURDATE() 返回当前日期

2）CURTIME() 返回当前时间

3）MONTH(d) 返回日期 d 中的月份值，范围是 1~12

11.2 字符串函数

1）CHAR_LENGTH(s) 计算字符串 s 的字符数；

2）UPPER(s) 把所有字母变成大写字母；

3）LOWER(s) 把所有字母变成小写字母

11.3 数学函数

1）ABS(x) 求绝对值

2）SQRT(x) 求平方根

3）MOD(x,y) 求余

11.4 加密函数

1）PASSWORD(str) 一般对用户的密码加密 不可逆

2）MD5(str) 普通加密 不可逆

3）ENCODE(str，pswd_str) 加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它；

4）DECODE(crypt_str，pswd_str) 解密函数；

11.5 判断函数

1）if(条件判断,值1,值2)

如果条件判断是true，就等于值1，false就等于值2，有点像三元表达式

例，把salary表中的女改成男，男改成女:

update salary set sex = if( sex = '男','女','男');

2）ifnull(值1, 值2)

ifnull里有两个数，如果值1不是null则取值1，否则取值2

例：

SELECT IFNULL(NULL,"11"); 结果：11
SELECT IFNULL("00","11"); 结果：00
SELECT IFNULL(NULL,NULL); 结果：NULL

12.触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。

当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

例如：你在一张表插入了一条数据，业务上要求在另一张表也需要插入，这时候就可以使用触发器

12.1创建触发器

1）创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

触发事件： INSERT、UPDATE 、 DELETE

实例：

在t_book插入一条数据后，t_bookType里的bookNum相应记录累加

CREATE TRIGGER trig_book AFTER INSERT ON t_book FOR EACH ROW
	UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_bookType.id;

INSERT INTO t_book VALUE(NULL, 'java新书', 100, 'aaa', 1);
SELECT * FROM t_bookType;

这里涉及到两个过渡变量：new和old，指代新插入的或即将删除的那条记录。

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在NEW及OLD同时使用。

说明：

for each row代表行级触发器，任意一行受影响都会触发的，叫行级触发器。

在oracle 触发器中，分“行级触发器”和“语句级触发器”，“语句级触发器”可不写for each row，无论影响多少行都只执行一次。

mysql不支持语句触发器，所以必须写for each row。

2）创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句1; ... 执行语句N; END

注：mysql语句执行是以分号做判断，这个多行触发器里带有多条语句，因此需要使用DELIMTTER重新定义结束符为 |（也可以是别的）。

例如下面这个例子中，当从t_book中删除一条语句，会触发三条语句：

DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END
|
DELIMITER ;

12.2显示和删除触发器

SHOW TRIGGERS

DROP TRIGGER 触发器名;

12.3Inserted和Deleted表

SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。

• 这两个表由系统来维护，它们存在于内存中而不是在数据库中，可以理解为一个虚拟的表。
• 这两个表的结构总是与被该触发器作用的表的结构相同。
• 触发器执行完成后，与该触发器相关的这两个表也被删除。
• Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
• Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

13.索引

索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度；

类似于图书的目录，方便快速定位，寻找指定的内容；

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，可以大大加快查询的速度，使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于书的目录，可以根据目录中的页码快速找到所需的内容。

13.1索引原理

索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。

记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段（也就是说，不包含唯一的访问入口）， 那么需要在 N 个数据块上搜索整个表格空间。

但是对于一个有序字段，可以运用二分查找（BinarySearch），这样只需要访问 log2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。索引需要额外的磁盘空间。

对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

13.2索引创建

1、创建表的时候创建索引

CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件], 属性名 数据类型 [完整性约束条件], .... 属性名 数据类型 [UNIQUE | FULLTEXT | SPATIAL ] INDEX|KEY [别名] (属性名1 [(长度)] [ASC | DESC]) );

说明：

• UNIQUE | FULLTEXT | SPATIAL 为可选参数，分别表示唯一索引、全文索引、空间索引
• INDEX和KEY是同义词，可以替换使用
• 只有字符串类型的字段才能指定索引长度，其中如果是BLOB和TEXT类型，必须指定 length，如果是CHAR，VARCHAR类型，长度可以小于字段实际长度，也可以不指定长度。
• asc或desc指定升序或降序的索引值存储

2、在已经存在的表上创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [(长度)] [ ASC | DESC]);

3、用 ALTER TABLE 语句来创建索引

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC]);

ALTER TABLE和CREATE INDEX的区别

• CREATE INDEX必须提供索引名，对于ALTER TABLE，如果你不提供将会自动创建；
• CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如：

ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);

• 只有ALTER TABLE 才能创建主键

13.3索引分类

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

13.3.1 普通索引

这类索引可以创建在任何数据类型中，它没有任何限制

1）创建表的时候同时创建索引：

CREATE TABLE t_test1( 
id INT, userName TEXT(20),
pwd VARCHAR(20), 
des VARCHAR(20), 
INDEX (userName(20)) );

2）CREATE方式创建索引

CREATE INDEX index_pwd ON t_test1(pwd);

3）ALTER方式创建索引

ALTER TABLE t_test1 ADD INDEX index_des (des);

13.3.2 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

第一种：在字段外创建
mysql> create table info2 (id int(4) not null,name varchar(10) not null,address varchar(50) default '未知',primary key(id));

第二种：在字段内创建
mysql> create table info2 (id int(4) not null primary key,name varchar(10) not null,address varchar(50) default '未知');

13.3.3唯一索引

使用 UNIQUE 参数可以设置，与普通索引类似不同之处在于，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

1）创建表的时候同时创建索引

CREATE TABLE t_test2( id INT, userName TEXT(20), pwd VARCHAR(20), des VARCHAR(20), UNIQUE INDEX (userName(20)) );

2）CREATE方式创建索引

CREATE UNIQUE INDEX index_pwd ON t_test2(pwd);

3）ALTER方式创建索引

ALTER TABLE t_test2 ADD UNIQUE INDEX index_des (des);

13.3.4全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。只有 MyISAM 引擎支持该索引。

1）创建表的适合添加全文索引

CREATE TABLE t_test4( id INT, userName TEXT(20), pwd VARCHAR(20), des VARCHAR(20), FULLTEXT (userName) );

2）CREATE方式创建索引

CREATE FULLTEXT INDEX index_des ON t_test4(des);

3）ALTER方式创建索引

ALTER TABLE t_test4 ADD FULLTEXT index_pwd(pwd);

13.3.5联合索引

指多个字段上创建的索引，当我们的where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引。

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable (
  ID INT NOT NULL,
  username VARCHAR (16) NOT NULL,
  city VARCHAR (50) NOT NULL,
  age INT NOT NULL
) ;

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

 ALTER TABLE mytable ADD INDEX name_city_age (username(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

[ usernname,city,age] 、[ usernname,city]、[ usernname]

最左前缀

为什么没有 city，age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。

简单的理解就是只从最左面的开始组合。组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，这个索引才会被用到，因此并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州" 　　SELECT * FROM mytable WHREE city="郑州"

注意

索引的字段可以是任意顺序的，如：

假设在test表有组合索引(col1,col2)

SELECT * FROM test WHERE col1=“1” AND clo2=“2”SELECT * FROM test WHERE col2=“2” AND clo1=“1”AI写代码java运行12

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。

13.4索引的显示与删除

显示索引信息

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名 DROP INDEX 索引名;