MySQL数据操作
添加记录
添加记录：INSERT [INTO] tbl_name [(字段名,...)] {VALUE|VALUES} (值...);
不指定字段名称：
INSERT tbl_name VALUE(值...)
需要按照建表时的字段顺序给每一个字段赋值
列出指定字段：
INSERT tbl_name(字段名称,...) VALUE(值,...);
INSERT...SET的形式：
INSERT tbl_name SET 字段名称=值,...;
INSERT ... SELECT的形式：
INSERT tbl_name[(字段名称,...)] SELECT 字段名称,... FROM tbl_name[WHERE 条件];
从别的表中插入相应的字段值
一次添加多条记录：
INSERT tbl_name[(字段名称,...)] VALUES(值,...),
(值,....),
(值,...);
修改记录
修改记录：UPDATE tbl_name SET 字段名称=值，... [WHERE 条件];
如果不添加条件，整个表中的记录都会被更新
删除记录
DELETE FROM tbl_name [WHERE 条件];
如果不添加条件，表中所有的记录都会被删除
DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置为1（ALTER TABEL tbl_name AUTO_INCREMENT=1;)
彻底清空数据表：TRUNCATE [TABLE] tbl_name;
清除表中所有记录
会重置AUTO_INCREMENT的值
查询记录
SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]
查询表中所有记录:SELECT * FROM tbl_name;
指定字段的信息:SELECT 字段名称,... FROM tbl_name;
库名.表名:SELECT 字段名称,... FROM db_name.tbl_name;
给字段起别名:SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
给数据表起别名:SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
表名.字段名的:SELECT tbl_name.col_name,... FROM tbl_name;
WHERE 条件:会筛选出符合条件的记录
比较运算符:> >= < <= != <> <=>(<=> 和 =的区别:<=>可以检测NULL值)
IS [NOT] NULL:检测值是否为NULL或者NOT NULL
指定范围:[NOT] BETWEEN ... AND
指定集合:[NOT] IN(值,...)
逻辑运算符:AND(逻辑与),OR（逻辑与)
匹配字符: %(匹配任意长度字符)
_(匹配任意一个字符)
GROUP BY 分组：把值相同放到一个组中，最终查询出的结果只会显示组中一条记录
分组配合GROUP_CONCAT()查看组中某个字段的详细信息
配合聚合函数使用
COUNT()：统计记录总数，如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来，写COUNT(*)会统计NULL值
SUM()：求和
MAX():求最大值
MIN()：求最小值
AVG():求平均值
配合WITH ROLLUP关键使用：会在记录末尾添加一条记录，是上面所有记录的总和
HAVING子句对分组结果进行二次筛选
ORDER BY 排序：ORDER BY 字段名称 ASC|DESC
LIMIT 限制结果集显示条数：
LIMIT 值：显示结果集的前几条记录
LIMIT offset,row_count：从offset开始，显示几条记录,offset从0开始
SELECT select_expr,... FROM tbl_name[WHERE 条件][GROUP BY {col_name|position} HAVING 二次筛选][ORDER BY {col_name|position|expr} [ASC|DESC]][LIMIT 限制结果集的显示条数]
多表查询：
笛卡尔积形式：SELECT 字段名称，... FROM tbl_name1,tbl_name2
内连接的形式：SELECT 字段名称，...FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件;
外连接的形式：
左外连接：SELECT 字段名称，...FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 连接条件;
右外连接：SELECT 字段名称，...FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 连接条件;
外键约束
只有InnoDB存储引擎支持外键
创建外键
建表时指定外键：[CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
如果外键字段没有创建索引，MySQL会自动帮我们添加索引
子表的外键关联的必须是父表的主键
外键约束的参照操作：
CASCADE：从附表删除或更新，子表也跟着删除或者更新，级联的操作
SET NULL：从附表删除或者更新记录，并设置子表的外键列为NULL。
NO ACTION | RESTRICT:拒绝对父表做更新或者删除操作
动态指定外键
动态添加外键:ALTER TABLE tbl_name
[CONSTRAINT 外键名称] ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功
动态删除外键：ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;
子表的外键关联的必须是父表的主键
特殊形式的查询
子查询：SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
内层语句查询的结果可以做为外层语句查询的条件
由IN引发的子查询：SELECT 字段名称 FROM tbl_name WHERE col_name IN(SELECT col_name FROM tbl_name)
由比较运算符引出子查询:SELECT 字段名称 FROM tbl_name WHERE col_name 比较运算符(SELECT col_name FROM tbl_name)
由EXISTS引发的子查询:SELECT 字段名称 FROM tbl_name WHERE EXISTS (SELECT col_name FROM tbl_name)
ANY SOME ALL关键字：SELECT 字段名称 FROM tbl_name WHERE col_name={ANY|SOME|ALL}(SELECT col_name FROM tbl_name)
INSERT ... SELECT：INSERT tbl_name1（字段名称，...）SELECT （字段名称，...）FROM tbl_name2
可通过子查询将tbl_name2中的数据插入tbl_name1中
CREATE ... SELECT：CREATE TABLE tbl_name1(...)SELECT （字段名称，...） FROM tbl_name2;
可通过子查询在tbl_name1创建时就将tbl_name2的数据插入tbl_name1中
CREATE TABLE tbl_name1 LIKE tbl_name2;
创建一个与tbl_name2表结构一样的表
联合查询
UNION：SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称... FROM tbl_name2;
UNION ALL：SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称... FROM tbl_name2;
UNION ALL 是简单的合并，UNION会去掉表中重复记录
SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
自身连接查询
无限级分类的实现形式
以下为例子：
-- 测试自身连接 CREATE TABLE cate( id SMALLINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(100) NOT NULL UNIQUE, pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 ); INSERT cate(cateName,pId) VALUES('服装',0); INSERT cate(cateName,pId) VALUES('数码',0); INSERT cate(cateName,pId) VALUES('箱包',0); INSERT cate(cateName,pId) VALUES('男装',1); INSERT cate(cateName,pId) VALUES('女装',1); INSERT cate(cateName,pId) VALUES('内衣',1); INSERT cate(cateName,pId) VALUES('电视',2); INSERT cate(cateName,pId) VALUES('冰箱',2); INSERT cate(cateName,pId) VALUES('洗衣机',2); INSERT cate(cateName,pId) VALUES('爱马仕',3); INSERT cate(cateName,pId) VALUES('LV',3); INSERT cate(cateName,pId) VALUES('GUCCI',3); INSERT cate(cateName,pId) VALUES('夹克',4); INSERT cate(cateName,pId) VALUES('衬衫',4); INSERT cate(cateName,pId) VALUES('裤子',4); INSERT cate(cateName,pId) VALUES('液晶电视',10); INSERT cate(cateName,pId) VALUES('等离子电视',10); INSERT cate(cateName,pId) VALUES('背投电视',10); -- 查询所有的分类信息，并且得到其父分类 SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName FROM cate AS s LEFT JOIN cate AS p ON s.pId=p.id; -- 查询所有的分类及其子分类 SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName FROM cate AS s RIGHT JOIN cate AS p ON s.pId=p.id; -- 查询所有的分类并且得到子分类的数目 SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count FROM cate AS s RIGHT JOIN cate AS p ON s.pId=p.id GROUP BY p.cateName ORDER BY id ASC;
-- 测试自身连接CREATE TABLE cate( id SMALLINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(100) NOT NULL UNIQUE, pId SMALLINT UNSIGNED NOT NULL DEFAULT 0);INSERT cate(cateName,pId) VALUES('服装',0);INSERT cate(cateName,pId) VALUES('数码',0);INSERT cate(cateName,pId) VALUES('箱包',0);
INSERT cate(cateName,pId) VALUES('男装',1);INSERT cate(cateName,pId) VALUES('女装',1);INSERT cate(cateName,pId) VALUES('内衣',1);
INSERT cate(cateName,pId) VALUES('电视',2);INSERT cate(cateName,pId) VALUES('冰箱',2);INSERT cate(cateName,pId) VALUES('洗衣机',2);
INSERT cate(cateName,pId) VALUES('爱马仕',3);INSERT cate(cateName,pId) VALUES('LV',3);INSERT cate(cateName,pId) VALUES('GUCCI',3);
INSERT cate(cateName,pId) VALUES('夹克',4);INSERT cate(cateName,pId) VALUES('衬衫',4);INSERT cate(cateName,pId) VALUES('裤子',4);
INSERT cate(cateName,pId) VALUES('液晶电视',10);INSERT cate(cateName,pId) VALUES('等离子电视',10);INSERT cate(cateName,pId) VALUES('背投电视',10);
-- 查询所有的分类信息，并且得到其父分类SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateNameFROM cate AS sLEFT JOIN cate AS pON s.pId=p.id;
-- 查询所有的分类及其子分类SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateNameFROM cate AS sRIGHT JOIN cate AS pON s.pId=p.id;
-- 查询所有的分类并且得到子分类的数目SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS countFROM cate AS sRIGHT JOIN cate AS pON s.pId=p.idGROUP BY p.cateNameORDER BY id ASC;
