mysql数据操作_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 TABLEcate(

idSMALLINT UNSIGNED AUTO_INCREMENT KEY,

cateNameVARCHAR(100) NOT NULL UNIQUE,

pIdSMALLINT 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 ASpCateNameFROM cate ASsLEFT JOIN cate ASpON s.pId=p.id;--查询所有的分类及其子分类

SELECT p.id,p.cateName AS pCateName,s.cateName ASsCateNameFROM cate ASsRIGHT JOIN cate ASpON s.pId=p.id;--查询所有的分类并且得到子分类的数目

SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count

FROM cate ASsRIGHT JOIN cate ASpON s.pId=p.idGROUP BYp.cateNameORDER 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;