MySQL笔记

CANCAT(str1,str2,....strn)	连接字符串str1、str2、.…strn为一个整体注意：当传入concat的参数其中一个为null，连接的结果就是为null；
INSERT(str,x,y,instr)	将字符串str从第x个位置开始，y个字符长的子串替换为instr
LEFT(str,x)	返回字符串str中最左边的x个字符
RIGHT(str,x)	返回字符串str中最右边的x个字符
LOWER(str)	把字符串str全部转为小写
UPPER(str)	把字符串str全部转为大写
LTRIM(str)	去掉字符串str左侧的空格；
RTRIM(str)	去掉字符串str右侧的空格；
TRIM(str)	去掉字符串str的前后的空格；
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
SUBSTRING(str,x,y)	截取字符串str从x位置开始的y位字符；
STRCMP(str1,str2)	比较字符串str1 和 str2 的ascii码值的大小

MySQL常用的数字函数

ABS(x) 返回绝对值

CEIL(x) 向上取整

FLOOR(x) 向下取整

MOD(x,y) 模运算（求余）

RAND() 返回0~1的随机数

ROUND(x,y) 将数值x四舍五入后保留y位小数

TRUNCATE(x,y) 将数值截断位y位小数

MySQL常用的日期和时间函数

CURDATE() 获取当前日期

CURTIME() 获取当前时间

NOW() 获取当前时间和日期

UNXI_TIMESTAMP(date) 获取日期date的UNIX时间戳

FROM_UNIXTIME() 获取UNIX时间戳的日期值

WEEK(date) 返回日期date为一年中的第几周

YEAR(date) 返回日期date的年份

HOUR(time) 返回时间time的小时值

MINUTE(time) 返回时间time的分钟值

MONTHNAME(time) 返回时间time的月份值

数据的操作

插入数据

插入部分记录

INSERT INTO table_name(field1,field2)

VALUES(value1,value2);

插入一条记录

INSERT INTO table_name(field1,field2,.......fieldn)

VALUES(value1,value2,.......valuen);

插入多条记录

INSERT INTO table_name(field1,field2,.......fieldn)

VALUES(value1,value2,.......valuen)，

.......

VALUES(value1,value2,.......valuen);

插入查询结果

在MySQL这，除了可以插入数据值到表中以外，还可以实现将另一张表的查询结果插入到表中

INSERT INTO table_name1(field11,field12,......field1n)

SELECT (fielf21,filed22,.....filed2n)

FROM table_name2

WHERE .....

更新数据

更新特定数据记录

UPDATAE table_name

SET field1 = value1,

field2 = value2,

field3 = value3

WHERE CONDITION 参数CONDITION指定更新满足条件的特定数据记录

注：

如果不用WHERE子句限定要更新的数据行，则会更新整个表的数据行

删除数据

删除特定数据记录

DELETE FROM table_name

WHERE CONDITION 参数CONDITION指定删除满足条件的特定数据记录

注：

如果不用WHERE子句限定要删除的数据行，则会删除整个表的数据行

DELETE 是按行删除数据，不是删除行在某些列的数据（即DELETE语句不能删除某一字段的值）

利用子查询来删除行

DELETE FROM table_name

WHERE SELECT 语句;

数据查询

数据查询有包括单表查询和多表查询

SELECT语句基本语法如下：

4 SELECT 【ALL | DISTINCT 】 < * | 字段>....

1 FROM <表名或视图名>【，<表名或视图名>】...

2 【WHERE <条件表达式>】

3 【GROUP UP <分组字段1>【,<分组字段2>】.....】

【HAVING <组条件表达式>】

5 【ORDER BY <排序字段1 [ASC | DESC]> [，<排序字段2 [ASC | DESC]>].....】

6 【LIMIT】

注：前面的数字为语句执行顺序

单表数据查询

查询所有字段数据行 ALL为默认，DISTINCT为去重

SELECT [ ALL | DISTINCT] * 其中，*代表所有字段

FROM table_name;

查询指定字段的数据 ALL为默认，DISTINCT为去重

SELECT [ ALL | DISTINCT] field1,filed2,...... 其中，field代表指定字段

FROM table_name;

排序数据记录查询 (ORDER BY)

按照单字段排序

SELECT field1,field2,.....

FROM table_name

WHERE CONDITION 参数CONDITION表示查询满足条件的特定数据记录

ORDDR BY fileldm1 [ASC | DESC]

按照多字段排序

SELECT field1,field2,.....

FROM table_name

WHERE CONDITION 参数CONDITION表示查询满足条件的特定数据记录

ORDDR BY fileldm1 [ASC | DESC] ，

fileldm2 [ASC | DESC] ......

限制数据记录查询数量 (LIMIT)

SELECT field1,field2,.....

FROM table_name

WHERE CONDITION 参数CONDITION表示查询满足条件的特定数据记录

LIMIT OFFSET_ START,ROW_ COUNT

limit有两个参数，第一个参数表示从第几行数据开始查，第二个参数表示查几条数据，例“limit 3,2”表示从第四行数据开始，取两条数据。

注：通过关键字LIMIT来限制查询的结果数量，其中参数OFFSET_ START表示数据记录的起始偏移量(默认从0开始)，参数ROW_ COUNT表示显示的行数

不指定初始位置

如果不指定初始位置，则默认值为0

语句为

LIMIT ROW_ COUNT

指定初始位置

如果不指定初始位置，则默认值为

语句为

LIMIT OFFSET_ START,ROW_ COUNT

分组数据记录查询 (GROUP BY)

聚合函数

COUNT(*) 计算记录的个数

COUNT(列名) 对某字段中的值的个数计算

AVG(列名) 求某字段的平均值

SUM(列名) 求某字段的总和

MAX(列名) 求某字段的最大值

MIN(列名) 求某字段的最小值

注：聚合函数只能出现在所查询的列，ORDER BY子句、HAVING子句中，不能出现在WHERE子句、GROUP BY子句中

如果所操作的表中没用任何数据，则COUNT()函数返回0，其他函数返回NULL

除了COUNT(*)以外，其他聚合函数（包括COUNT（列名））都忽略对列值为null的统计

简单分组查询

SELECT field1，field2，.....

FROM table_name

WHERE CONDITION  -- 参数CONDITION表示查询满足条件的特定数据记录

GROUP BY field  -- 按照field字段分组

按照多个字段分组查询

SELECT field1，field2，.....

FROM table_name

WHERE CONDITION  --b参数CONDITION表示查询满足条件的特定数据记录

GROUP BY field，field2  -- 按照field、field2字段分组

实现HAVING子句限定分组查询

SELECT field1，field2，.....

FROM table_name

WHERE CONDITION  -- 参数CONDITION表示查询满足条件的特定数据记录

GROUP BY field，field2，......  -- 按照field、field2......字段分组

【HAVING 分组后的过滤条件;  】 -- 关键字HAVING是指定分组以后的条件

HAVING 和 WHERE 的区别

执行时机不同：

where是分组之前进行过滤，不满足where条件的不参与分组，

而having是在分组之后对结果进行过滤的

判断条件不同：

where不能对聚合函数进行判断，

而having可以

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段没有意义

多表数据查询

MySQL的多表之间的关系可以概括为：一对一、一对多或多对一、多对多

多表查询也称连接查询。它是指对两个或两个以上的表或视图的查询。连接查询是关系型数据库中最主要、最有实际意义的查询，是关系型数据库的一项核心功能。MySQL提供了4种类型的连接，即内连接、自身连接、不等连接、外连接（左外连接、右外连接）

子查询（嵌套查询）

在MySQL中通过连接查询的方式实现了多表查询数据记录，但是却不建议使用，这是因为连接查询的性能很差。因此出现了代替连接查询的子查询。在实际开发中，也推荐使用子查询来实现多表查询数据记录

子查询也称嵌套查询

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个

合并查询（联合查询）

当两个SELECT查询结果的结构完全一致时，可以对这两个查询执行合并运算，运算符为UNION

联合查询基本语法如下

SELECT 语句1
    UNION [ALL]  -- 有all不会去除重复结果
SELECT 语句2;

注意：

对于联合查询的多张表列数必须一致，字段类型也需要保持一致

UNION ALL 会将得到的全部数据直接合并在一起

UNION 会将合并以后的数据进行去重

如果使用了ORDER BY进行排序，排序的字段必须来自第一个表，任何字段设置了别名，排序时也需要使用别名

关系数据操作

并（UNION）

笛卡尔积（CARTESIAN PRODUCT）

内连接（INNER JOIN ）

外连接（OUTER JOIN）

约束条件

操作表的约束

非空约束（NOT NULL，UK）

非空约束时指字段的指不能为空。对于使用了非空约束的字段，如果在添加数据时没有指定值，数据库就会报错。

在创建表时添加非空约束

CREATE TABLE table_name(

        字段名 数据类型 NOT NULL,              当插入的记录中该字段为空时，会报错

..........

);

在创建表后添加非空约束

ALTER TABLE table_name MODIFY 字段 类型 NOT NULL;

删除非空约束

与在创建表后添加非空约束很像，只需要不添加后面的NOT NULL即可

ALTER TABLE table_name MODIFY 字段 类型 ;

非空约束小结

添加非空约束的列不能为空

真正的空是NULL，不是”null“，也不是” “

默认约束（DEFAULT）

MySQL默认值约束用来指定某列的默认值

在创建表时添加默认约束

CREATE TABLE table_name(

        字段名 数据类型 DEFAULT 默认值,        当插入的记录中该字段为空时，该字段会采用默认值

..........

);

在创建表后添加默认约束

ALTER TABLE table_name MODIFY 字段名 类型 DEFAULT 默认值;

注：默认值类型要与字段类型一致

删除默认约束


ALTER TABLE tb_name MODIFY file 字段类型;

ALTER TABLE table_name CHANGE COLUMN <字段名> <类型> DEFAULT NULL;

默认约束小结

添加了默认约束的列，如果没有指定值，则为默认值

唯一约束（UNIQUE, UK）

唯一约束是指所有记录中的字段的值都不能重复出现。例如，为id字段加上唯一约束后，每条记录的id值都是唯一的，不能出现重复的情况。

在创建表时添加唯一约束

CREATE TABLE table_name(

        字段名 数据类型 UNIQUE ,        当插入的记录中该字段有重复时，会报错

..........                  -- 在MySQL中NULL和任何值都不相同，甚至和自己都不相同，因此唯一约束                                     后，添加多个NULL值不报错

);

注意: 在为约束设置标识符时，推荐使用“约束缩写_字段名”，ep：uq_dname;

在创建唯一约束时，没有指定约束名，默认就是字段名

在创建表后添加唯一约束

ALTER TABLE table_name ADD CONSTRAINT 约束名 UNIQUE(字段);

注：这里的约束名没有什么作用，只是在删除约束时使用，在创建表时添加的约束要删除时约束名就是字段名

删除唯一约束

ALTER TABLE table_name DROP INDEX <唯一约束名>;

唯一约束小结

添加唯一约束后，该列的值不能重复

虽然不能重复，但可以添加NULL，因为NULL不等于NULL

设置主键约束（PRIMARY KEY , PK）

MySQL主键约束是一列或者多个列的组合，其值能唯一的标识表中的每一行

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

每个表最多只允许一个主键

当创建主键的约束时，系统默认会在所在列和列组合上建立对应的唯一索引

单字段主键（单列主键）

  CREATE TABLE table_name(

                字段名 数据类型 PRIMARY KEY,      

        ..........

        );

当字段用PK约束，主键约束相当于非空约束+唯一约束，主键上的该字段不能有重复值

多字段主键（联合主键）

所谓联合主键，就是这个主键是是由多个字段组成

   CREATE TABLE table_name(

                字段名 数据类型    

                ..........

                 【CONSTARINT 约束名】PRIMARY KEY(字段1，字段2.....)

        );

CONSTARINT(约束) 关键字为约束命名

注：一张表只能有一个主键，联合主键也是一个主键

当主键由多个字段组成时，不能在字段后面声明主键约束

联合主键的每一列都不能为空

创建表后添加主键（通过修改表结构添加主键约束）

主键约束不仅可以在创建表时同时创建，也可以在修改表示添加单列主键或联合主键

ALTER TABLE table_name  ADD 【CONSTARINT 约束名】PRIMARY KEY (field1 [,field....]);

删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

主键约束小结

一个表只有一个主键（包含联合主键）

添加了主键约束得列不能为空，值不能重复

主键可以唯一的标识表中的一行数据，目的是提交查询的效率

设置字段值自动增加(AUTO_INCREMENT)

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当为数据库表中插入新纪录时，字段上的值会自动生成唯一的ID，数据库表中只能有一个字段使用该约束，该字段必须是整数类型。设置该约束的字段经常也会设为PK主键。

创建表时设置自增字段

  CREATE TABLE table_name(

                字段名 数据类型  AUTO_INCREMENT,

                .......

    );

指定自增字段的初始值

  CREATE TABLE table_name(

                字段名 数据类型  AUTO_INCREMENT,

                .......

    ) AUTO_INCREMENT = xx ;

创建表后指定自增字段的初始值

ALTER TABLE table_name AUTO_INCREMENT = xx;

删除自增长约束

ALTER TABLE table_name MODIFY file 字段类型;

delete和truncate在删除后自增列的变化

delete数据之后，自增值从断点处开始（即自增值会保留，在这个基础上继续增加）

truncate数据之后自增值从默认起始值开始

特点：

1.默认情况下，auto_increement的初始值为1，没增加一条记录，字段值自动增加1

2.一个表只能有一个字段使用auto_increment约束，且该字段必须有唯一索引（主键约束默认会创建唯一索引），以避免序号重复（即为主键或主键的一部分）

3.auto_increment约束的字段必须具备NOT NULL属性

4.auto_increment约束的字段必须是整数类型（TINYINT、SMALLINT、INT、BIGINT等）

5.auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效

自增长约束小结

自增长约束一般与主键约束一起使用

自增长约束默认是从1开始增长的

每次添加数据，自增长的值加1

设置外键约束(FOREIGN KEY, FK )

MySQL外键约束是表的一个特殊字段，经常与外键约束一起使用。对于两个具有关联关系的表而言，相关字段中主键所在表称为主表（父表），外键所在表称为从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束连个表中数据的一致性与完整性。

创建表时设置外键约束

  CREATE TABLE table_name(

                字段名 数据类型 ,

                字段名 数据类型 

                .......

                [CONSTRAINT <外键约束名>] FOREIGN KEY (从表字段) 【,从表字段2 ,.....】

                        REFERENCES <主表表名> <主表字段 [ ,主表字段2，......] >

    );

上述语句中，REFERENCES意思是依赖，“外键约束名”用来表示约束名，“从表字段”参数是子表中设置外键的字段名，“主表字段”参数是父表中设置主键约束的字段名

注：必须先给主表添加数据

给从表添加数据时，外键列不能随意写，必须依赖主表的主键列

创建表后添加外键约束

外键约束可以在创建表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主键列中的数据保持一致或是没有数据

ALTER TABLE table_name ADD CONSTRAINT <外键名> FOREIGN KEY <字段> REFERENCES <主表名> <主表字段>；

删除外键约束

外键约束一旦删除，就会解除主表与从表间的关联关系

ALTER TABLE table_name DROP FOREIGN KEY <外键约束名>;

外键约束小结

定义外键时,需要遵守以下规则：

主表必须是已经存在与数据库中，或者是当前正在创建的表。

必须是主表定义主键。

主键不能包含空值，但允许外键出现空值。也就是说，只要外键的每个非空值出现在指定的主表中，这个外键的内容就是正确的。

在主表的后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

外键中列的数据必须和主表的主键中列数目相同。

外键中列的数据类型必须和主表的主键中对应列数据类型相同。

零填充约束（ZEROFILL）

1.插入数据时，当该字段的值的长度小于定义的长度时，会在该值前面补充0

2.zerofill默认为int(10)

3.当使用zerofill时，默认会自动加UNSIGNED（无符号）属性,使用unsigned后，数值范围是原来值得两倍。例如，有符号为-128~127 ，无符号为 0~256

在创建表时添加零填充约束

  CREATE TABLE table_name(

                字段名 数据类型  ZEROFILL ,

                字段名 数据类型 ,

              ........

    );

删除零填充约束

ALTER TABLE table_name MODIFY 字段 类型;

索引

索引与书的目录非常相似，主要是为了提高从表中检索数据的速度。

索引的存储类型 B型树索引(BTREE)

哈希索引(HASA)

注： InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为前者索引

数据库对象索引的出现，除了可以提高数据库管理系统的查找速度，而且还可以保证字段的唯一性，从而实现数据库表的完整性。

MySQL支持的6种索引：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

索引的利弊：创建索引可以提高查询速度，但过多的索引则会占据许多的磁盘空间。

以下情形适合创建索引：

表上的INSERT/DELETE/UPDATE操作较少

经常被查询的字段，即在WHERE子句中出现的字段

在分组的字段，即在GROUP BY 子句中出现的字段

存在依赖关系的子表和父表之间的联合查询，即主键或外键字段

设置唯一完整性约束的字段

以下情形不适合创建索引：

在查询中很少被使用的字段

用于很多重复值得字段

创建索引

创建和查看普通索引

1.在创建表时创建普通索引

CREATE TABLE table_name(

字段名数据类型 ,

字段名数据类型，

.......

字段名数据类型，

INDEX | KEY [索引名] (字段名1 [(长度)] [ ASC | DESC ])

);

注：索引名一般采用表名_列名_idx 的方式

2.在已经存在的表上创建普通索引

CREATE INDEX 索引名

ON 表名 (字段名 [ (长度)] [ASC | DESC ]) ;

3.通过SQL语句ALTER TABLE 创建普通索引

ALTER TABLE table_name

ADD INDEX | KEY 索引名 (字段名 [(长度)] [ ASC | DESC ])；

创建和查看唯一索引

1.在创建表时创建索引

CREATE TABLE table_name(

字段名数据类型 ,

字段名数据类型，

.......

字段名数据类型，

UNIQUE INDEX | KEY [索引名] (字段名1 [(长度)] [ ASC | DESC ])

);

2.在已经存在的表上创建唯一索引

CREATE UNIQUE INDEX 索引名

ON 表名 (字段名 [ (长度)] [ASC | DESC ]) ;

3.通过SQL语句ALTER TABLE 创建唯一索引

ALTER TABLE table_name

ADD UNIQUE INDEX | KEY 索引名 (字段名 [(长度)] [ ASC | DESC ])；

创建和查看全文一索引

1.在创建表时创建全文索引

CREATE TABLE table_name(

字段名数据类型 ,

字段名数据类型，

.......

字段名数据类型，

FULTEXT INDEX | KEY [索引名] (字段名1 [(长度)] [ ASC | DESC ])

);

2.在已经存在的表上创建全文索引

CREATE FULTEXT INDEX 索引名

ON 表名 (字段名 [ (长度)] [ASC | DESC ]) ;

3.通过SQL语句ALTER TABLE 创建全文索引

ALTER TABLE table_name

ADD FULTEXT INDEX | KEY 索引名 (字段名 [(长度)] [ ASC | DESC ])；

创建和查看多列一索引

1.在创建表时创建多列索引

CREATE TABLE table_name(

字段名数据类型 ,

字段名数据类型，

.......

字段名数据类型，

INDEX | KEY [索引名] (字段名1 [(长度)] [ ASC | DESC ] ,

.......

字段名n [(长度)] [ ASC | DESC ]

);

2.在已经存在的表上创建多列索引

CREATE INDEX 索引名

ON 表名 ( 字段名 [ (长度)] [ASC | DESC ] ,

...........

字段名n [(长度)] [ ASC | DESC ]

)

3.通过SQL语句ALTER TABLE 创建全文索引

ALTER TABLE table_name

        ADD  INDEX | KEY 索引名   (字段名  [ (长度)]   [ASC  |  DESC ] ,

                                                     ...........

                                                     字段名n  [(长度)]   [ ASC | DESC ]

)

删除索引

DROP INDEX index_name

                ON table_name；

视图

视图是一种虚拟的存在的表。视图中的数据并不在数据库中实际存在。

触发器

创建有一条执行语句的触发器

CREATE TRIGGER trigger_name

BEFORE | AFTER trigger_EVENT

ON TBALE_NAME FOR EACH ROW trigger_STMT

其中参数trigger_name表要创建的触发器名字（建议触发器名字为trigger_xxx 或 tri_xxx）， BEFORE 和 AFTER 表示在触发器事件之前（之后）执行触发器语句

trigger_EVENT 表示触发事件，即触发器执行条件（包含DELETE、INSERT、UPDATE）

TBALE_NAME 表示触发事件操作表的名字

FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器

trigger_STMT 表示激活触发器后被执行的语句

创建包含多条执行语句的触发器

CREATE TRIGGER trigger_name

BEFORE | AFTER trigger_EVENT

ON TBALE_NAME FOR EACH ROW

BEGIN

trigger_STMT

END

其中比“只有一条执行语句的触发器”语句多出来了关键字BEGIN和 END ，在这两个关键字之间为所要执行的的多个执行语句的内容，执行语句之间用分号隔开

注

在MySQL中，一般情况下用“；”作为语句的结束符号，但在创建触发器时，需要用到“；”作为执行语句的结束符号。为了解决这个问题，可以使用关键字DELIMITER，例如 “DELIMITER$$”，可以用来实现将结束符号设置为“$$”

例

DELIMITER $$
CREATE TRIGGER trigger_diarytime2

        AFTER INSERT

                ON t_dept FOR EACH ROW

                       BEGIN

                           INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                           INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        END        
                        $$
DELIMITER ;

上述语句中，首先通过“DELIMITER $$”语句设置为结束符号“$$”，然后再关键字 BEGIN 和END之间编写了执行语句列表，最后通过“DELIMITER ；”语句将结束符号还原为默认结束符号“；”

通过SHOW TRIGGERS 语句查看触发器

SHOW TRIGGERS;

通过SHOW TRIGGERS; 语句执行完后会显示一个列表

其中，Trigger 表示触发器名称

Event 表示触发器的激活事件

Table 表示触发器对象触发事件所操作的表

Statement 表示触发器激活时所执行的语句

Timing 表示触发器所执行的时间

还有一些其他的参数，暂时不需要掌握

通过查询系统表triggers 实现查看触发器

DESC triggers;

查看系统表triggers中所有记录

SELECT * FROM triggers;

查看系统表triggers中所有记录

SELECT * FROM triggers

WHERE TRIGGER_NAME = "XXX";

通过DROP TRIGGER 语句删除触发器

DROP TRIGGER trigger_name;

书香恋仁心

关注

4
点赞
踩
33

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记

查看所有数据库 show databases；查看当前数据库 show database(); 注意括号创建数据库 create database [if not exists] mydb1;选择数据库 use mydb1；查看当前数据库的所有表 show tables；删除数据库 drop database 数据库名；删除当前数据库中的表 drop table 表名;查看表结构 desc 表名；查询指定代表创建表语句 show create table 表名；修改数据.
复制链接

扫一扫