MYSQL

CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称字段类型[完整性约束条件（UNSIGNED表示数据不带符号|ZEROFILL用0补齐位数）] [NOT NULL] [DEFAULT默认值] [PRIMARY KEY] [UNIQUE KEY] [AUTO_INCREMENT]) ENGINE=引擎名称（默认INNODB） CHARSET=编码方式(默认UTF8)

字段类型

整数、字符串、浮点数、日期时间类型、二进制型

没有布尔型，用TINYINT(1)表示，0为假，非0为真

完整性约束条件

主键

主键字段自动非空，不能重复

主键有单一主键，直接在字段类型后面加PRIMARY KEY 也可以直接写KEY

复合主键（多字段主键），另起一行PRIMARY KEY(id,name),复合主键可以有一个字段重复，不能两个字段同时重复

自增长

AUTO_INCREMENT 自增长的一定是主键，主键不一定是自增长，在KEY后面写AUTO_INCREMENT

外键

非空

NOT NULL

默认值

DEFAULT

唯一性

UNIIQUE或者UNIQUE KEY

表示字段内容必须唯一，但是NULL不算不唯一

存储引擎

表的类型，可以根据不同存储方式和是否事务处理来进行选择

MyISAM 查询写入速度快

InnoDB 较高的事务处理

MEMORY 需要用临时表

修改表结构的操作

表重命名

ALTER TABLE 表名称 RENAME TO/AS/不写AS或者TO 新表名

RENAME TABLE 表名称 TO 新表名

两种写法都可

添加字段

ALTER TABLE tbl_name ADD 字段名称字段类型[完整性约束条件] 字段位置（FIRST/AFTER+字段名称）

一次性添加多个字段

ALTER TABLE 表名称

ADD 字段名称1 字段名称字段类型[完整性约束条件] 字段位置,

ADD 字段名称2...,

ADD 字段名称3...;

删除字段

ALTER TABLE tbl_name DROP 字段名称

ALTER TABLE tbl_name

DROP 字段名称1,

DROP 字段名称2,

DROP 字段名称3;

也可以删除和添加结合起来

修改字段

ALTER TABLE tbl_name MODIFY 字段名称字段类型[完整性约束条件] [FIRST/AFTER 字段名称]

修改字段名称

ALTER TABLE 字段名称 CHANGE 字段名称新字段名称字段类型[完整性约束条件] [FIRST/AFTER 字段名称]

添加默认值

ALTER TABLE 表名称 ALTER age SET DEFAULT 18 给age字段添加默认值18

删除默认值

ALTER TABLE 表名称 ALTER age DROP DEFAULT

添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol] PRIMARY KEY[index_type](字段名称，...)

删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

如果一个字段有主键和自增长两个属性，应该先去掉自增长属性，才能去除主键属性

添加和删除唯一

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol] UNIQUE [INDEX|KEY][索引名称](字段名称，...)

如果没有指定，会默认把字段名称作为索引名称

ALTER TABLE tbl_name DROP [INDEX|KEY][索引名称]

修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

设置自增长的值

ALTER TABLE tbl_name AUTO_INCREMENT=值

删除表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name...] 可以删除多个表

数据操作

插入数据

不指定具体字段

INSERT [INTO] tbl_name VALUES|VALUE(值...)

指定字段

INSERT [INTO] tbl_name（字段名称1，...） VALUES|VALUE(值1，...)

插入多条记录

INSERT tbl_name [(字段名称...)] VALUES（值...）（值...）

通过SET插入

INSERT tbl_name SET 字段名称=值,...

将查询结果插入表中

INSERT tbl_name[(字段名称)] SELECT 字段名称 FROM tbl_name [WHERE 条件] 注意复制过来的字段要一致

更新和删除记录

UPDATE tbl_name SET 字段名称=值，... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

彻底清空数据表 TRUNCATE [TABLE] tbl_name

查询数据

单表查询

SELECT 指定字段或者* FROM 表名（星号表示所有字段）

也可以 SELECT 表名称.指定字段或者* FROM 库名称.表名

给表名起别名

SELECT 指定字段或者* FROM 表名 [AS] a

给字段起别名

SELECT 指定字段或者* AS '编号' FROM 表名

带条件的查询

SELECT 指定字段或者* FROM 表名 WHERE 条件

!= 或者 <>都是不等于

注意，检测是否为NULL不能用=，要用<=> <=>用法比=多一个NULL

IS NULL 也可以检测NULL值

IS NOT NULL 检测不为空值

范围查询

SELECT 指定字段或者* FROM 表名 WHERE id NOT BETWEEN 3 AND 10

id不在3-10之间的

SELECT 指定字段或者* FROM 表名 WHERE id IN(1,3,5,7,9)

id是1，3，5，7，9

NOT IN 相反

模糊查询

%：代表0个1个或者多个任意字符

_:代表1个任意字符

SELECT 指定字段或者* FROM 表名 WHERE 字段名 LIKE ' '

不区分大小写

NOT LIKE 不包含某个内容

分组查询

SELECT 指定字段或者* FROM 表名 WHERE 条件 GROUP BY 字段名会显示字段中每个值的第一条记录

也可以不写字段名，写1/2/3/4... 第一个字段是1，第二个字段是2，以此类推

如果按多个字段来分，先按第一个字段分，再按后面的字段分

分组详情

查询id,sex,以及username的详情，按照性别分组

SELECT id,sex,GROUP_CONCAT(username) FROM 表名 GROUP BY sex

如果是想得到多个字段的详情，可以写多个GROUP_CONCAT

聚合函数

查询编号，性别，用户名的详情，并且把用户名改为别名users,按照性别分组,统计各分组的总人数

SELECT id,sex,GROUP_CONCAT(username) AS users，COUNT(*) AS totalUsers FROM 表名 GROUP BY sex

统计总数

SELECT COUNT(*) FROM 表名 COUNT不统计NULL

SELECT COUNT(DISTINCT id) FROM 表名去掉id重复的记录

MAX() MIN() AVG() SUM()

GROUP BY 字段名之后可以加WITH ROLLUP 会在表格最后增加一行，在求和字段会自动求和，最大值字段会自动再求最大值

HAVING子句(一般放在GROUP BY后面，进行二次筛选)

GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>60 筛选每个值都大于2的记录，而且年龄最大值大于60

ORDER BY

SELECT *FROM 表名 ORDER BY id DESC(降序)/ASC(升序，默认)

SELECT *FROM 表名 ORDER BY RAND() 乱序排列

LIMIT限制查询结果显示条数

SELECT *FROM 表名 LIMIT 3 只显示前三条记录

SELECT *FROM 表名 ORDER BY id DESC LIMIT 5 得到降序的前五条记录

SELECT *FROM 表名 LIMIT 0,5 从0开始，偏移量为5，显示前五条记录

UPDATE 表名 SET age=age+10 LIMIT 3 前三条记录年龄都加3，注意在UPDATE和DELETE FROM里面LIMIT只能写一个参数

连接查询，找到表和表之间的关系，连接起来

SELECT id,username,proName FROM 表名1，表名2 WHERE 表1.proId=表2.id

以上语句可以用内连接表示

SELECT u.id,u.username,u.email.u.sex,p.proName FROM 表1 AS u INNER JOIN 表2 AS p ON u.proId=p.id

也可以

SELECT u.id,u.username,u.email.u.sex,p.proName FROM 表2 AS p CROSS JOIN 表1 AS u ON u.proId=p.id

INNER/CROSS可以省略

外连接

SELECT u.id,u.username,u.email.u.sex,p.proName FROM 表1 AS u LEFT JOIN 表2 AS p ON u.proId=p.id

把LEFT左边的表1作为主表，主表的信息都显示出来，然后把右表符合条件的数据也显示出来，这是左外连接，

右外连接同理

外键

外键所在字段的表为子表，被参照的表为主表，外键的作用是保持数据一致性和完整性，可以实现一对一和一对多

父表和子表必须都使用InnoDB,外键列和参照列要有相似数据类型，必须创建索引，不创建的话会自动创建索引

父表不能进行更新和删除操作，除非子表中没有相关联的数据

FOREIGN KEY(depId) REFERENCES department(id) 创建外键depId，借鉴父表的id字段

在FOREIGN前面加CONSTRAINT 外键名称可以给外键起名

子表中不能添加父表没有的外键字段内容

添加内容 INSERT employee(username,depId) VALUES(‘king’，3)

删除外键 ALTER TABLE employee DROP FOREIGN KEY 外键名称

添加外键 ALTER TABLE employee ADD CONSTRAINT 外键名称 FOREIGN KEY(depId) REFERENCES department

FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE 当父表中某一内容删除，对应的外键字段内容也会被删除

ON UPDATE CASCADE 更新父表时，子表相应也会更新

CASCADE 换为SET NULL，删除和更新父表时，相应的子表内容变为NULL

联合查询

查多个表的记录，合并到一起

UNION合并去掉相同记录，UNION ALL 简单合并到一起

SELECT a FROM 表1 UNION SELECT b FROM 表2

子查询

IN和NOT IN

SELECT id,username FROM 表1 WHERE depId IN(SELECT id FROM 表2)

NOT IN取反

使用比较运算符 = > < >= <= <> != <=>

EXISTS和NOT EXISTS 判断真假

使用ANY(也可以用SOME）或者ALL

=ANY 相当于IN

！=ALL 相当于NOT IN

INSERT 表1（字段1，字段2...） SELECT 字段1，字段2... FROM 表2

CREATE TABLE 表1（定义字段） SELECT 字段1，字段2... FROM 表2

正则表达式查询

SELECT id,username FROM 表1 WHERE username REGEXP '正则表达式'

运算符

算数运算符

3/0=NULL

1+NULL=NULL

比较运算符

结果只能为真或者假 1 0

逻辑运算符

2&&2=1 2&&0=0 2&&NULL=NULL 1||NULL=1 0||NULL=NULL !NULL=NULL

运算符优先级

函数

数学函数

字符串函数

日期时间函数

条件函数

系统函数

加密函数

其他函数

索引

创建索引

普通索引

CREATE TABLE 表名称（字段名称字段类型

INDEX|KEY 索引名称（字段名称））

唯一索引

作为完整性条件创建UNIQUE 也可以UNIQUE KEY uni_card(card)来创建

全文索引

FULLTEXT INDEX

单列索引

多列索引

空间索引

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

方式一：CREATE INDEX 索引名称 ON 表名（字段名称）

方式二：ALTER TABLE 表名 ADD INDEX 索引名称（字段名称）

删除索引

DROP INDEX 索引名称 ON 表名

ALTER TABLE 表名 DROP INDEX 索引名称

ksh1994

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录