数据库操作指南：轻松掌握CRUD和高级查询技巧，轻松实现数据操作

一、CRUD

注意，操作数据库的时候为避免名称可能存在关键字的影响，最好使用反引号包含起来；这样MySQL在做词法语法分析的时候，就不会把其作为关键字进行分析。

1.1、创建数据库

语法：

CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8;

示例：

create DATABASE `fly_test` DEFAULT CHARACTER set utf8;

执行信息：

create DATABASE `fly_test` DEFAULT CHARACTER set utf8
> OK
> 时间: 0 秒

1.2、选择数据库

语法：

USE `数据库名`;

示例：

use fly_test;

执行信息：

use fly_test
> OK
> 时间: 0 秒

1.3、删除数据库

语法：

DROP DATABASE `数据库名`;

示例：

DROP DATABASE `fly_test2`;

执行信息：

DROP DATABASE `fly_test2`
> OK
> 时间: 0 秒

1.4、CRUD的五大约束

1. not null，即非空约束。
2. Autoincrement，即自增约束。可以指定初始值，没有指定默认是0；插入节点的时候会自增。这对事务的一致性非常重要。
3. unique，即唯一约束。
4. primary，即主键约束。设计表的时候，每张表都需要一个主键约束；就算没有设置，MySQL也会自动帮生成一个主键。
5. foreign，即外键约束。用于表与表之间的联动关系。

1.5、创建表

语法：

CREATE TABLE `table_name` (column_name column_type);

创建表的时候，要有列名称、列类型、约束。

示例：

CREATE TABLE IF NOT EXISTS `fly_table1` (
`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
`course` VARCHAR(100) NOT NULL COMMENT '课程',
`teacher` VARCHAR(40) NOT NULL COMMENT '讲师',
`price` DECIMAL(8,2) NOT NULL COMMENT '价格',
PRIMARY KEY ( `id` )
)ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课程表';

ENGINE指定引擎，charset指定编码方式，COMMENT是注释。

执行信息：

CREATE TABLE IF NOT EXISTS `fly_table1` (
`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
`course` VARCHAR(100) NOT NULL COMMENT '课程',
`teacher` VARCHAR(40) NOT NULL COMMENT '讲师',
`price` DECIMAL(8,2) NOT NULL COMMENT '价格',
PRIMARY KEY ( `id` )
)ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课程表'
> OK
> 时间: 0.006 秒

1.6、删除数据表

1.6.1、删除表

语法：

DROP TABLE `table_name`;

删除表结构及其表数据。

1.6.2、清空数据表

语法：

TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引的话，从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加

TRUNCATE TABLE：截断表 以页为单位（至少有两行数据），有自增索引的话，从初始值开始累加。
DELETE TABLE ： 逐行删除，有自增索引的话，从之前值继续累加

1.6.3、区别

命令	速度	内容	事务回滚
drop	速度最快	删除整张表的表结构和表数据，包括索引，约束‘触发器等等。	不能回滚
truncate	速度较快	删除表数据，其他保留。	不能回滚
delete	速度较慢	删除部分或全部数据，其他保留。这是条件删除	能回滚

drop和truncate会阻塞其他操作。只有停机维护状态才使用这两个命令。

命令	删除原理	使用建议
drop	删除整张表	业务上最好不使用。一般在停机维护状态时，修改或设计表结构出现错误时使用。
truncate	以页为单位进行删除。	业务上最好不使用。一般在停机维护状态时，修改或设计表结构出现错误时使用。
delete	逐行删除。	推荐使用

因为delete是以行为单位，逐行删除的；而drop是以页（8K）为单位进行删除的；所以delete比drop慢。

1.7、增

语法：

INSERT INTO `table_name`(`field1`, `field2`, ...,`fieldn`) VALUES (value1, value2, ..., valuen);

如果列设置了自增组件，那么操作时可以不列出来，系统会将其自增的。

示例：

INSERT INTO fly_table1 (`course`,`teacher`,`price`) VALUES ('linux C/C++','fly',0.0);

执行信息：

INSERT INTO fly_table1 (`course`,`teacher`,`price`) VALUES ('linux C/C++','fly',0.0)
> Affected rows: 1
> 时间: 0.046 秒

1.8、删

语法：

DELETE FROM `table_name` [WHERE Clause];

示例：

DELETE FROM `fly_table1` where id = 2;

执行信息：

DELETE FROM `fly_table1` where id = 2
> Affected rows: 1
> 时间: 0.001 秒

1.9、改

语法：

UPDATE table_name SET field1=new_value1,field2=new_value2 [, fieldn=new_valuen]

示例：

UPDATE `fly_table1` SET price=price+100,course='linux MySQL' WHERE id =3;

执行信息：

UPDATE `fly_table1` SET price=price+100,course='linux MySQL' WHERE id =3
> Affected rows: 1
> 时间: 0.003 秒

1.10、查

语法：

SELECT field1, field2,...fieldN FROM table_name [WHERE Clause]

实际使用中最好不要使用select *的方式。

示例：

SELECT price FROM fly_table1 WHERE course='linux C/C++';

执行信息：

SELECT price FROM fly_table1 WHERE course='linux C/C++'
> OK
> 时间: 0.001 秒

1.11、去重的方式

（1）group by column。
（2）select distinct column。

1.12、条件判断类型

（1）… where condition。
（2）group by column having condition。
（3）… join … on condition。

其中，condition是条件，column是列名。

二、高级查询

做一下准备，为下面的查询操作建立数据库。

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=innoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=innoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=innoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=innoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=innoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

innoDB 有外键约束，myisam 注释的作用。

2.1、基础查询

-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `sname`, `class_id` FROM student;
-- 别名 列明 不要用关键字
SELECT `sname` AS '姓名' , `class_id` AS '班级ID'
FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `class_id` FROM student;

2.2、 条件查询

-- 查询姓名为 fly1 的学生信息
SELECT * FROM `student` WHERE `sname` = 'fly1';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=5;

2.3、范围查询

-- 查询班级id 1 到 3 的学生的信息
SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;

2.4、判空查询

is null 判断造成索引失效。

# 索引 B+ 树
SELECT * FROM `student` WHERE `class_id` IS NOT NULL; #判断不为空
SELECT * FROM `student` WHERE `class_id` IS NULL;#判断为空


SELECT * FROM `student` WHERE `gender` <> '';
#判断不为空字符串
SELECT * FROM `student` WHERE `gender` = '';
#判断为空字符串

2.5、模糊查询

使用 like关键字，"%"代表任意数量的字符，”_”代表占位符。

-- 查询名字为 m 开头的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '谢%';
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

2.6、分页查询

分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用。

使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。

-- 查询第二条到第三条内容
SELECT * FROM `student` LIMIT 1,2;

2.7、查询后排序

关键字：order by field。
asc:升序。
desc:降序

SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多个字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC,`num` DESC;

2.8、聚合查询

聚合函数	描述
sum()	计算某列的总和
avg()	计算某列的平均值
max()	计算某列的最大值
min()	计算某列的最小值
count()	计算某列的行数

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

2.9、分组查询

分组加group_concat。

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

三、联表查询

可以把表想象成集合。
联表查询分为内联（inner join）和外联（left join，right join，full join）。内联类似交集，full join类似并集。
如果只写了join，那么默认是内联。

3.1、INNER JOIN

只取两张表有对应关系的记录。

SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;

3.2、LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录。

select course.cid from `course` left join `teacher` on course.teacher_id = teacher.tid;

3.3、RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录。

select course.cid from `course` right join `teacher` on course.teacher_id = teacher.tid;

四、子查询/合并查询

4.1、单行子查询

select * from course where teacher_id = (select tid from teacher where tname = 'lucien')

4.2、多行子查询

多行子查询即返回多行记录的子查询。

IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（ true ），否则，将返回一个假值（ false ）。当返回的值为 true 时，外层查询语句将进行查询；当返回的为false 时，外层查询语句不进行查询或者查询不出任何记录。
ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。

在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。

示例：

select * from student where class_id in (select cid from course where teacher_id = 6);

select * from student where exists(select cid from course where cid = 5);


select student_id,sname FROM (SELECT * FROM score WHERE course_id = 5 OR course_id = 2) AS A LEFT JOIN student ON A.student_id = student.sid;

4.3 正则表达式

使用关键字：REGEXP。

选项	说明（自动加匹配二字）	例子	匹配值示例
^	文本开始字符	'^b’匹配以字母b开头的字符串	book, big,banana,bike
.	任何单个字符	'b.t’匹配任何b和t之间有一个字符	bit, bat,but, bite
*	0个或多个在它前面的字符	'f*n’匹配字符n前面有任意多个字符f	fn, fan,faan, abcn
+	前面的字符一次或多次	'ba+'匹配以b开头后面紧跟至少一个a	ba, bay,bare, battle
<字符串>	包含指定字符串的文本	‘fa’	fan, afa,faad
[字符集合]	字符集合中的任一个字符	'[xz]'匹配x或者z	dizzy,zebra, xray,extra
[^]	不在括号中的任何字符	'[^abc]'匹配任何不包含a、b或c的字符串	desk, fox,f8ke
字符串{n}	前面的字符串至少n次	b{2}匹配2个或更多的b	bbb, bbbb,bbbbbb
字符串{n,m}	前面的字符串至少n次，至多m次	b{2,4}匹配最少2个，最多4个b	bb, bbb,bbbb

SELECT * FROM `teacher` WHERE `tname` REGEXP '^long';

总结

1. 在实际使用中最好不要使用select *的方式查询数据，这种查询方式既不好分析数据，也会使查询效率降低。
2. SQL查询中，如果不清楚名称是不是关键字，最好使用反引号括起来，避免在词法语法分析时被当成关键字处理。
3. group by会去重，group_concat会分组。