MySql基础总结

❝

Java程序员必备基础随笔记

❞

用户登录基本操作

登录客户端程序

mysql -h主机名 -u用户名 -p密码

注：如果服务器在本机的话，-h这个参数可以省略。最好不要在p后面直接跟上密码，可以在p后「回车」再输入密码

断开与服务器连接且关闭客户端

①quit ②exit ③\q

显示服务器状态信息

SHOW STATUS;

数据库基本操作

列出当前存在的数据库清单

SHOW DATABASES;

创建数据库

CREATE DATABASE 自定义数据库名称;

不清楚数据库是否创建

CREATE DATABASE IF NOT EXISTS 数据库名称;

注：若指定的数据库不存在就创建一个，「否则什么都不做」。

选择/切换数据库

use 已存在的数据库名称;

删除数据库

DROP DATABASES 数据库名称;

不清楚数据库是否删除

DROP DATABASES IF EXISTS 数据库名称;

查询正在使用的数据库

SELECT DATABASE();

表的基本操作

复制表

CREATE TABLE 表名 LIKE 被复制的表名;

列出当前数据库存在的表

SHOW TABLES;

创建表

CREATE TABLE 表名 (
  列名1 数据类型1 列的约束
  列名2 数据类型2 列的约束
  ...
  列名n 数据类型n 列的约束
);

列的约束

PRIMARY KEY：主键约束

其用于「唯一标识」表中的每一条纪录，主键列上不允许任何两行值重复，不允许为空，「一个表只能有一个PRIMARY KEY」。

UNIQUE：唯一约束

其修饰的字段下的值可以为空，「其余不能重复，一个表可以有多个UNIQUE KEY」。

NOT NULL：非空约束

有时候我们需要要求表中的某些列中「必须有值」。

自动增长

AUTO_INCREMENT

一个表中「最多有一个具有AUTO_INCREMENT属性的列」。

删除表

DROP TABLE 表名;

可以再添加表的注释信息

CREATE TABLE 表名 (
  各个列的信息...
) COMMENT'表的注释信息'

查看表结构

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW CREATETABLE 表名;

修改表名

修改单个表名

ALTER TABLE 旧表名 RENAME TO 新表名;

修改多个表名

RENAME TABLE 旧表名1 TO 新表名1，旧表名2 TO 新表名2,...旧表名n TO 新表名n;

修改表的字符集

ALTER TABLE 表名 CHARACTER SET 字符集名称;

增加列

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束条件;

添加列到指定位置|第一列

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束条件 FIRST;

添加到指定位置的后面

ALTER TABLE 表名 ADD COLUMN 列名 列的类型 约束条件 AFTER 指定列名;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

修改列信息

ALTER TABLE 表名 MODIFY 列名 新数据类型 新约束条件;

修改列排列位置

将列设为表的第一列

ALTER TABLE 表名 MODIFY 列名 列的类型 约束条件 FIRST;

将列放到指定位置的后面

ALTER TABLE 表名 MODIFY 列名 列的类型 约束条件 AFTER 指定列名;

简单的查询和插入

起个别名查询单列

SELECT 列名 [AS] 列的别名 FROM 表名;

查询多个列

SELECT 列名1,列名2,...列名n FROM 表名;

查询所有列

SELECT * FROM 表名;

插入

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);列名和值要一一对应，如果不写列名，则默认给素有列添加值，如：INSERT INTO 表名 VALUES(值1,值2,...);

批量插入

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...),...;

删除数据

DELETE FROM 表名 WHERE 条件;需要注意的是如果不加条件，则删除表中所有的记录，一般不推荐DELETE FROM 表名;这种操作有多少条记录就会多少次删除操作。TRUNCATE TABLE 表名;推荐使用，效率上更高，先删除表，再创建一张一样的表。

修改数据

UPDATE 表名 SET 列名1 = 值1，列名2 = 值2,... WHERE 条件;需要注意的是如果不添加任何条件，则表中所有的记录都会被修改。

单列查询结果去重

SELECT DISTINCT 列名 FROM 表名;

多列查询结果去重

SELECT DISTINCT 列名1,列名2,...列名n FROM 表名;

限制查询结果条数

LIMIT 开始行,限制条数;

LIMIT后边也可以直接跟限制条数，默认从第0行开始

按单个列的值进行排序

ORDER BY 列名 ASC|DESC(升序|降序)

按多个列的值进行排序

ORDER BY 列1 ASC|DESC,列2 ASC|DESC...

注：如果没有指定排序方向，则默认使用的是ASC，从小到大

简单搜索条件（置于WHERE后）

操作符：> >= < <= = !=或者<> BETWEEN NOT BETWEEN

判断某一列是否为空

IS NULL 与 IS NOT NULL

注：不能直接使用普通的操作符来与NULL值进行比较，必须使用IS NULL或者IS NOT NULL

在某集合内某一个

IN(集合)

例子：a IN(b1,b2,...),a是b1,b2,...中的某一个

多个搜索条件的查询

AND操作符

使用AND操作符可以连接多个搜索条件

OR操作符

符合某一个搜索条件就符合条件的纪录加入到结果集中

注：AND操作符的优先级高于OR操作符

通配符

有时候我们不能精确的描述我们要查询的东西，MYSQL支持下表这两个操作符来支持模糊查询

操作符	示例	描述
LIKE	a LIKE b	a匹配b
NOT LIKE	a NOT lIKE b	a不匹配b

%:代表任意一个字符串

_:代表任意一个字符

分组

GROUP BY，按照指定的字段进行分组，GROUP BY字句通常和COUNT()、SUM()等聚合函数一起使用。

分组之后条件过滤

HAVING，有GROUP BY才能有HAVING字句，分组后再筛选。

五种常用聚合函数

SUM():求和

AVG():求平均值

COUNT():计数

MAX():求最大值

MIN():求最小值``

注：COUNT(*)是对表中行的数目进行计算，不管列的值是不是NULL；而COUNT(列名)是对特定的列进行计数，会忽略掉该列为NULL的行

简单查询语句中各字句的顺序

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表]
[HAVING 分组过滤条件]
[ORDER BY 排列顺序]
[LIMIT 开始行，限制条数]

「注：SELECT字句中带[内容]不是必须使用的」

WHERE子句在聚合前先筛选记录，作用是在GROUP BY和HAVING字句的前面。HAVING子句在「聚合」后再对组记录进行** 筛选**。

WHERE和HAVING的区别

作用的对象不同：WHERE子句作用于表和视图；HAVING子句作用于组。

子查询

多表查询的需求

前面描述的语句都是查询单个表的，有时候我们需要多个表查询才能拿到我们需要的信息。比如我们要查询某位学生他的成绩。我们分两步走。

第一步：先从学生信息表中查到这位同学的学号SELECT number FROM student_info WHERE name = '吕小不';

第二步：再利用查询到的学号从学生成绩表中查到这位同学的成绩SELECT * FROM student_score WHERE number = 201730110008;

两步可以简化成一步：SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '吕小不');

注：()里面的查询语句被称为子查询或者内层查询，使用内层查询的结果作为搜索条件的操作数的查询则是外层查询。如果你在一个查询语句中需要利用更多的表进行子查询时，那么你可以在一个子查询中再嵌套另外一个子查询，顺序按从内到外。

❝

事实上，所有的子查询都必须使用小括号括起来，否则是非法的。

❞

标量子查询

标量子查询是指子查询返回的是单一值的标量，例如一个数字或者一个字符串，是子查询中最简单的返回形式。

比如我们查询到'吕小不'的学号结果就只有一个值，这种子查询就是「标量子查询」。

列子查询

列子查询是指查询返回的结果集不是一个单独的值，而是一个列（1列N行），该结果通常来自对表的某个字段查询的返回集合。

如果我们想查询'信息工程'专业的学生的成绩，我们可以这样写：SELECT * FROM student_score WHERE number IN(SELECT number FROM student_info WHERE major = '信息工程')

由于列子查询返回的结果是一个列，是一个结果集合，因此不能直接使用= > <等这些比较结果的操作符。而是使用到IN、NOT IN等操作符，后面跟的是某个集合。

行子查询

行子查询是指子查询返回的结果集是1行N列，该子查询的结果集是对表的某行数据进行查询而返回的结果集。

比如这样：SELECT * FROM stdent_score WHERE (number,subject,...) = (SELECT number,'自然与科学',... FROM student_info LIMIT 1);

❝

想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，这时候应该使用LIMIT 1子句来限制记录数量。

❞

表子查询

表子查询，顾名思义，就是返回的结果集是多行多列的二维表，子查询的返回的结果当做是一个二维表来使用。

比如这样：SELECT * FROM student_score WHERE (number,subject) IN (SELECT number,'自然与科学' FROM student_info WHERE major = '信息工程')；这个子查询结果集中包含多行多列，那么这个子查询被称为表子查询。

EXISTS和NOT EXISTS子查询

有时候外层查询并不关心子查询的结果是什么，只关心子查询的结果集是不是空集，这时候就可以用到下边两个操作符:

操作符	示例	描述
EXISTS	EXISTS (SELECT ...)	当子查询结果集不是空集时表达式为真
NOT EXISTS	NOT EXISTS (SELECT ...)	当子查询结果集是空集时表达式为真

比如这样：SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 201730110008);

不相关子查询&&相关子查询

前面的子查询是可以独立存在的，可以独立运行并产生结果，与外层查询没有依赖关系，这种子查询就被称之为不相关子查询。

另外一种就是子查询不能当做一个独立的语句执行，有时候子查询的语句中会引用到外层查询的值，这种情况就属于相关子查询。

怎么理解呢，举个例子：SELECT number,name,id_number,major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);先执行外层查询得到student_info表的第一条记录，发现number值是201730110008，将number值当做参数传入到子查询，再判断student_score表中是否有这个number值，因此子查询的语句会涉及到外层查询的值，这里指的是number值。

❝

当student_info和student_score表里都有number列，因此如果写成number = number会让服务器无法区分，因此在number前边加了表名，这种显式的将所属的表名写出来的名称称之为该列的全限定名。

❞

对同一个表的子查询

前面的子查询，我们至少都涉及了两个表student_info表和student_score表，其实单个表的情况下也是会发生子查询的。

比如查看sutudent_score表的'自然与科学'这门课超过平均成绩的记录有哪些第一反应可能会是这样写SELECT * FROM student_score WHERE subject = '自然与科学' AND score > AVG(score);看过去好像没有什么问题，但是犯了一个错误，「聚合函数不能放到WHERE字句中！为什么呢？其实很简单，想想看，如果>右边单纯的只写AVG(score)，它统计的是所有课程成绩的平均分；然而根据>左边，我们想统计的只是关于自然与科学这门学科，在细节上就会出现歧义，显得不严谨。因此聚合函数都是用于分组之后的统计，经过where条件筛选后就是统计相同的课程平均分了。」

因此正确写法：SELECT * FROM student_score WHERE subject = '自然与科学' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '自然与科学');。>前面的语句都不变，就是将AVG(score)变成(SELECT AVG(score) FROM student_score WHERE subject = '自然与科学');。

MySQL三种连接查询

为解决驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集中，就有了内连接和外连接的概念。

内连接

INNER JOIN，就是连接两张表的数据，通过某个字段相等，查询出相关的记录数据。「对于内接连的两个表,驱动表中的记录在被驱动表中找不到匹配的记录时，该记录就不会添加到最后的结果集中，简言之，若所查询的记录为NULL不会显示出来。这种情况属于内连接。」

外连接(分左外连接和右外连接)

「对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集中」

左外连接

LEFT JOIN，选取左侧的表作为驱动表，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相匹配的，就会显示出来，如果没有则会补上NULL。

右外连接

RIGHT JOIN，选取右侧的表作为驱动表，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相匹配的，就会显示出来，如果没有则会补上NULL。

组合查询

基于单表的组合查询

对单个表可以执行多条查询语句，然后对多个结果集使用UNION语句合并成单个结果集返回。

基于不同表的组合查询

多数情况下，组合查询还是涉及不同表的查询的，然后对多个结果集使用UNION语句合并成单个结果集返回。

过滤重复

使用UNION来合并多个查询的记录会默认去除重复的记录

视图

❝

视图是在mysql5.1版本出现的新特性，它是一张虚拟表，和普通表一样的用法。

❞

创建视图

CREATE VIEW 视图名 AS 查询语句视图有什么好处呢？如果你在写sql语句的时候一直重复写某条冗长的查询语句，这时候可以使用视图，「视图其实就相当于某个查询语句的别名，视图的使用可以简化语句的书写」。

查看视图

SHOW CREATE VIEW 视图名;

删除视图

DROP VIEW 视图名;

更新视图

视图是可更新的，就是在视图上执行增删改(INSERT、DELETE、UPDATE)的语句，语法和操作表的时候是类似的，本质上这些语句是对该视图对应的底层表的数据进行增删改。但子几个特点的条件下，视图不支持更新，比如生成视图的时候使用了聚合函数、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL等语句时就无法更新。

存储函数

❝

存储函数其实就是一种函数，只不过在这个函数里边可以执行MySql的语句。函数的概念大家都不陌生，它封装了某个问题的处理过程，方便我们调用函数解决问题。

❞

CREATE FUNCTION 存储函数名称([参数列表])
RETURN 返回值类型
BEGIN
  函数体内容
END

**例子**
CREATE FUNCTION avg_score(s VARCHAR(10))
  RETURN  DOUBLE
BEGIN
  RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

存储函数的调用

都是在函数后加小括号()表示函数调用，如SELECT avg_score("输入你想要查询的平均分");

查看和删除存储函数

查看我们定义了多少个存储函数

SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]

查看某个函数的定义

SHOW CREATE FUNCTION 函数名

删除某个存储函数

DROP FUNCTION 函数名

存储过程

❝

存储函数和存储过程都属于存储例程，都是对某些语句的一个封装.存储函数侧重于执行这些语句并返回一个值，而存储过程则侧重于单纯的去执行这些语句。

❞

创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
  需要执行的语句
END
**例子**
CREATE PROCEDURE t1_opereation(
  m1_value INT,
  n1_value CHAR(1)
)
BEGIN
  SELECT * FROM t1;
  INSERT INTO t1(m1,n1)VALUES(m1_value,n1_value);
  SELECT * FROM t1;
END 

存储过程的调用

存储函数执行语句并返回一个值，所以常用在表达式中。存储过程偏向于执行某些语句，并不能用在表达式中，我们需要通过CALL语句来调用一个存储过程。 「CALL 存储过程([参数列表])」，如当我们调用CALL t1_operation(3,'d');时就是向t1表的m1和n1列插入3和'd'。

查看和删除存储过程

SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]

查看某个存储过程的定义

SHOW CREATE PROCEDURE 存储过程名称

删除存储过程

DROP PROCEDURE 存储过程名称

触发器

触发器会在你执行增删改语句的前后自动执行一条MySql语句。

创建触发器

CREATE TRIGGER 触发器名
**BEFORE:表示在具体的语句执行之前就开始执行触发器的内容**
{BEFORE|AFTER}
**表示具体的增删改语句**
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

❝

因为MySQL服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同： 对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。 对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。 对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

❞

CREATE TRIGGER bi_t1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  IF NEW.m1 < 1 THEN
    SET NEW.m1 = 1;
  ELSEIF NEW.m1 > 10 THEN
    SET NEW.m1 = 10;
END IF;
END

它表示t1表插入新记录之前，对准备插入的每一条记录都会执行BEGIN ... END之间的语句，NEW.列名表示当前待插入记录指定列的值。若此时我执行插入语句：INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z');就会触发bi_t1触发器的执行内容。很明显100最终值变为10。

❝

注：上边定义的触发器名bi_t1的bi是before insert的首字母缩写，t1是表名。虽然对于触发器的命名并没有什么特殊的要求，但是习惯上还是把它定义我上边例子中的形式，也就是bi_表名、bd_表名、bu_表名、ai_表名、ad_表名、au_表名的形式。对DELETE和UPDATE操作设置BEFORE或者AFTER触发器的过程是类似的。

❞

查看和删除触发器

SHOW TRIGGERS;

查看某个触发器的定义

SHOW CREATE TRIGGER 触发器名;

删除触发器

DROP TRIGGER 触发器名;

参考文献

MySQL 是怎样使用的：从零蛋开始学习 MySQL

MySQL数据库—SQL汇总

史上最全的mysql基础教程

客官，这里有一份《MySQL 必知必会》读书笔记，请您笑纳!