- 表的操作
- 创建表
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型
)
- 查看表的结构
DESCRIBE table_name;
SHOW CREATE TABLE table_name;
可使用’;’或\g或\G结束,为了美观,使用\G结束
- 删除表
DROP TABLE table_name
- 修改表(ALTER TABLE)
- 修改表名
ALTER TABLE old_table_name RENAME new_table_name
-
- 增加字段
ALTER TABLE table_name ADD 属性名 数据类型
-
- 第一个位置增加字段
ALTER TABLE table_name ADD 属性名 数据类型 FIRST
-
- 在指定字段之后增加字段
ALTER TABLE table_name ADD 属性名 数据类型 AFTER 属性名
-
- 删除字段
ALTER TABLE table_name DROP 属性名
-
- 修改字段数据类型
ALTER TABLE table_name MODIFY 属性名 数据类型
-
- 修改字段名字
ALTER TABLE table_name MODIFY 旧属性名 新属性名 旧数据类型
-
- 修改字段名字和数据类型
ALTER TABLE table_name MODIFY 旧属性名 新属性名 新数据类型
-
- 修改字段顺序
ALTER TABLE table_name MODIFY 属性名1 属性1数据类型 FIRST|AFTER 属性名2
- 表的约束条件
- 设置非空约束
CREATE TABLE table_name(
属性名,数据类型 NOT NULL
)
-
- 设置字段的默认值
CREATE TABLE table_name(
属性名,数据类型 DEFAULT ‘hello’
)
-
- 设置唯一约束条件
CREATE TABLE table_name(
属性名,数据类型 UNIQUE
)
-
- 设置主键约束条件
CREATE TABLE table_name(
属性名,数据类型 PRIMARY KEY’
)
-
- 设置多字段约束条件
CREATE TABLE table_name(
属性名,数据类型 ,
CONSTRAINT con_name PRIMARY KEY (属性名1,属性名2)
)
-
- 设置字段值自动增加
CREATE TABLE table_name(
属性名,数据类型 AUTO INCREAMENT
)
-
- 设置外键约束
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
CONSTRAINT 外键约束名 FOREIGN KEY(属性名1)
REFERENCES 表名(属性名2)
)
注:属性名1参数是子表中设置外键约束的字段名,属性名2参数是父表中设置主键约束的字段名
- 数据操作
- 插入数据
- 插入完整数据记录
INSERT INTO table_name (field1,field2...fieldn)
VALUES(value1,value2...valuen )
或 INSERT INTO table_name
VALUES(value1,value2,...valuen)
-
- 插入部分数据记录
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)
VALUES(value1,value2)
(value1,value2)
(value1,value2)
-
- 插入查询结果
INSERT INTO table_name (field1,field2...fieldn)
SELECT (field21,field22...field2n)
FROM table_name2
WHERE ...
- 更新数据记录
UPDATE table_name
SET field1=value1
WHERE CONDITION
- 删除数据记录
DELETE FROM table_name
WHERE CONDITION
- 单表数据记录查询
- 简单数据查询
SELECT * FROM table_name
SELECT field1,field2 FROM table_name
- 避免重复数据查询(DISTINCT)
SELECT DISTINCT field1
FROM table_name
- 实现数学四则运算数据查询(+加法,-减法,* 乘法,/(DIV)除法,% (MOD)求余)
SELECT field*12 otherfield1 FROM table_name
SELECT field*12 AS otherfield1 FROM table_name
- 设置显示格式数据查询
SEKECT CONCAT(name1,name2,name3) field1
FROM table_name
- 条件数据记录查询
- 带关系运算符和逻辑运算符的条件数据查询
比较运算符:> < = !=或<> >= <=
逻辑运算符:与(AND &&)或(OR ||)异或(XOR)非(!)
SELECT name FROM table_name WHERE job=’CLERK’
SELECT name FROM table_name WHERE job=’clerk’ && sal>800
-
- 带BETWEEN AND关键字的范围查询
SELECT field1,field2..fieldn
FROM table_name
WHERE field1 BETWEEN VALUE1 AND VALUE2
或SELECT ename FROM t_employee
WHERE sal NOT BETWEEN VALUE1 AND VALUE2
-
- 带IS NULL关键字的空值查询
SELECT field1, field2, field3.. FROM table_name
WHERE field IS NULL
SELECT field1, field2, field3...FROM table_name
WHERE field IS NOT NULL
-
- 带IN 关键字的集合查询
SELECT field1, field2,...
FROM table_name
WHERE field IN (value1, value2, value3)
SELECT field1, field2,...
FROM table_name
WHERE field NOT IN (value1, value2, value3)
注:
1 in (value1, value2,..., NULL) 当查找值为NULL 时不影响查询结果
-
- 带LIKE关键字的模糊查询
‘_’ 匹配单个字符 ‘%’匹配多个字符
SELECT field1, field2,..
FROM table_name
WHERE field LIKE value
或SELECT field1, field2,..
FROM table_name
WHERE field NOT LIKE value
Value举例 ‘_s’ 和 ‘%5%’
-
- 排序数据记录查询
SELECT field1, field2,... fieldn
From table_name
WHERE CONDITION
ORDER BY fieldm1 [ASC|DESC] [,fieldm2 [ASC|DESC]]
ASC表示按升序, DESC表示按降序,默认升序
也可按照多字段排序
-
- 限制数据记录查询数量
SELECT field1,field2,...
FROM table_name
WHERE CONDITION
LIMIT OFFSET_START, ROW_COUNT
例如:LIMIT 2 限制2条数据
LIMIT 0,5 从第一条开始,限制5条数据
- 统计函数和分组数据记录查询
SELECT FUNCTIN(field) FROM table_name WHERE CONDITION
-
- 统计数据条数-COUNT
COUNT(*)不管表字段中包含的是NULL 还是非NULL
COUNT(field)忽略NULL 值
-
- 统计平均值-AVG
- 统计求和-SUM
- 统计最大值和最小值 MAX和MIN
- 简单分组数据查询
SELECT function() FROM table_name
WHERE CONDITION
GROUP BY field
-
- 实现统计功能的分组数据查询
SELECT GROUP_CONCAT(field)
FROM table_name
WHERE CONDITION
GROUP BY field
-
- 实现HAVING 子句限定分组查询
SELECT function(field)
FROM table_name
WHERE CONDTION
GROUP BY field1, field2..
HAVING CONDTION
HANVING指定分组后的条件
- 多表数据记录查询
- 关系数据操作
并 | 把具有相同字段数目和字段类型的表合并到一起,去重 | |
笛卡尔积 | 字段数=两表字段数之和 数据记录数=两表数据记录数之积 | |
内连接 保留所有匹配数据记录,舍弃不匹配 | 自然连接 | 根据表关系中相同名称的字段自动进行记录匹配,去掉重复字段 字段数=两表字段数之和-重复字段数 数据记录数=两表数据记录之积-相同字段值不等记录数 |
等值连接 | 选择匹配值相等的数据记录 字段数=两表字段数之和 数据记录数=两表数据记录之积-相同字段值不等记录数 | |
不等连接 | 表关系的笛卡尔积中,选择所有匹配字段值不相等的数据记录 字段数=两表字段数之和 数据记录数=两表数据记录之积-相同字段值相等记录 | |
外连接 不仅保留表关系中所有匹配的数据记录,而且还保留部分不匹配的数据记录 | 左外连接 | 表关系的笛卡尔积中,除了选择项匹配的数据记录,还包含关联左边表中不匹配的数据记录 字段数=两表字段数之和 数据记录数=两表数据记录之积-相同字段值不等记录+左表未匹配数据 |
右外连接 | 表关系的笛卡尔积中,除了选择项匹配的数据记录,还包含关联右边表中不匹配的数据记录 字段数=两表字段数之和 数据记录数=两表数据记录之积-相同字段值不等记录+右表未匹配数据 | |
全外连接 | 表关系的笛卡尔积中,除了选择项匹配的数据记录,还包含关联左右边表中不匹配的数据记录 字段数=两表字段数之和 数据记录数=两表数据记录之积-相同字段值不等记录+左右表未匹配数据 |
- 内连接查询
两种方式,一种是在FROM字句中利用逗号区分多个表,在WHERE字句中通过逻辑表达式来实现匹配条件,另一种是ANSI连接语法形式,在FROM字句中使用JOIN ON关键字,而连接条件写在关键字ON字句中
SELECT field1,field2,...
FROM join_tablename1 INNER JOIN join_tablename2 [ INNER JOIN join_tablenamen]
ON join_condition
- 外连接查询
SELECT field1,field2,...
FROM join_tablename1 LEFT|RIGHT|FULL [OUT] JOIN join_tablename2 [ INNER JOIN join_tablenamen]
ON join_condition
注:默认JOIN ON是外连接,内连接需完整INNER JOIN ON
- 合并查询数据记录
SELECT * FROM table_name1
UNION
SELECT * FROM table_name2
注:上写法去重
SELECT * FROM table_name1
UNION ALL
SELECT * FROM table_name2
注:上写法不去重
- 子查询
在多表查询中做笛卡尔积时由于数据量较大,会造成死机等不可执行的情况,通过子查询,即在一个SELECT语句的WHERE 或FROM字句中包含另一个SELECT 查询语句,外层的SELECT 为主查询,内层的SELECT为子查询,也被称为嵌套查询
WHERE子句查询:一般返回单行单列,多行单列,单行多列数据记录
FROM子句查询:一般返回多行多列数据记录,可做临时表,
-
- 返回结果为单行单列子查询
SELECT *
FROM table_name
WHERE filed1>(
SELECT field1
FROM table_name
WHERE field2=value)
-
- 返回结果为单行多列子查询
SELECT *
FROM table_name
WHERE (field1,field2)=(
SELECT field1,field2
FROM table_name
WHERE field2=value)
-
- 返回结果为多行单列子查询
- 带有关键字IN的子查询(IN| NOT IN)
- 返回结果为多行单列子查询
主查询的条件是(不是)子查询的查询结果时
SELECT * FROM table_name1
WHERE field1 IN(
SELECT field1
FROM table_name2)
-
-
- 带有关键字ANY的子查询
-
主查询的条件未满足子查询返回结果中任意一条数据记录
三种匹配方式, =ANY,>ANY,<ANY
SELECT ename,sal
FROM t_employee
WHERE sal >ANY(
SELECT sal
FROM t_employee
WHERE job=’MANAGER’)
-
-
- 带有关键字ALL的子查询
-
主查询的条件满足子查询返回查询结果中所有数据记录
>ALL,<ALL
SELECT ename,sal
FROM t_employee
WHERE sal >ALL(
SELECT sal
FROM t_employee
WHERE job=’MANAGER’)
-
-
- 带有关键字EXISTS的子查询
-
查询时EXISTS对外表采用遍历方式逐条查询,每次查询都会比较EXIST的条件语句,当EXISTS里的语句返回记录行时则条件为真,此时返回遍历结果,反之亦反。
SELECT * FROM table_name
WHERE EXISTS(
SELECT * FROM table_name2
WHERE CONDTION)
-
- 返回结果为多行多列子查询
当子查询的返回结果为多行多列数据记录时,该子查询一般会在主查询的语句的FROM子句里,被当做一张临时表的方式来处理。
SELECT *
FROM table_name1 INNER JOIN(
SELECT * FROM table_name2)
ON value1=value2