mysql 第三章增删改查

插入数据

CREATE table if not EXISTS c1(
id TINYINT UNSIGNED key,
username VARCHAR(20) UNIQUE not NULL,
PASSWORD char(32) not NULL,
email VARCHAR(50) not null DEFAULT ‘505252216@qq.com’,
age TINYINT UNSIGNED DEFAULT 18
);

插入全部字段

insert c1 VALUE(1,‘king’,‘king’,‘king@qq.com’,20);

插入部分字段（保证字段名称和values的值对应）

insert c1(id,username,password) VALUE(2,‘hehe’,‘123’);

一次插入多行

insert c1 VALUE(1,‘king’,‘king’,‘king@qq.com’,20),
(2,‘kong’,‘kong’,‘kong@qq.com’,30);

insert set插入

insert c1 set id=10,username=‘haha’,password=‘456’;

将别的查询结果插入到表中(必须保证插入的字段一直)

insert uset select id,username from user1;
insert user(name) select name from user1;

update

UPDATE c1 set age=5 WHERE id=1;
UPDATE c1 set age=age-5 where age >10;

delete

DELETE from c1 where id=1;

truncate(清空表并重置自增长)

truancate c1;

查询

条件查询

where条件

SELECT * FROM 表名 where 条件;
SELECT 字段1,字段2,字段3... FROM 表名 where 条件;

范围

between and
in
not in

SELECT * FROM cms_user WHERE id IN (1,3,5);
SELECT * FROM cms_user WHERE username IN ('张三','章子怡');

模糊查询

like
LIKE条件中有两个通配符：
1、%，代表一个或多个字符，如查找姓张的人，LIKE ‘张%’
2、，代表一个字符，如查找名字是3个字的人，LIKE '__’（3个下划线）
LIKE一般都用于字符型数据。

分组查询（group by）

==只显示第一条结果
select * from cms_user GROUP BY proId;

–按照多个字段分组
select * from cms_user t GROUP BY sex,proId;

group concat（）得到分组详情

–查询一个组里都有哪些人

select id,sex,GROUP_CONCAT(username) from cms_user group by sex;

count（） 查看分组的个数，不统计null值

select id,sex,GROUP_CONCAT(username),count(*) from cms_user group by sex;

with rollup 对上面的结果进行统计

select id,sex,GROUP_CONCAT(username),count(*) from cms_user group by sex with ROLLUP;

having语句 对结果进行二次筛选

select sex,GROUP_CONCAT(username),count(*),max(sex),min(sex),AVG(sex) from cms_user group by sex having count(*)=5;

排序 order by

desc降序
asc升序

limit显示条数，控制分页

select * from cms_user limit 0,3;
select * from cms_user limit 3,3;

排序后更新

update cms_user set age=age+10 order by proId limit 3;

排序后删除

DELETE from cms_user where sex=‘男’ ORDER BY proId limit 1;

连接查询

内连接

INNER JOIN的注意事项：
1、需要指定on，也就是进行连接的两个表共有的字段
2、对于两个表中同名的字段，需要使用表的别名来区分

SELECT u.id,proId,username,proName 
from cms_user u INNER JOIN provinces p
ON proId=p.id

select n.id,n.title,c.cateName,a.username,a.role
from 
cms_news as n
JOIN
 cms_cate as c
 on n.id=c.id
 JOIN
 cms_admin a
 on a.id=c.id;

外链接

左外连接（保留左边的全部信息）

SELECT u.id,proId,username,proName 
from cms_user u LEFT JOIN provinces p
ON proId=p.id ORDER BY proId DESC LIMIT 5; 

右外连接（保留右表的全部信息）

SELECT u.id,p.id 省份编号,username,proName 
from cms_user u RIGHT JOIN provinces p
ON proId=p.id ORDER BY 省份编号 DESC; 

外键（主表和附表有关联关系的表）

只有INNODB引擎才支持外键，所以指定引擎，但一般默认就是INNODB

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键名称 FOREIGN KEY (depId) REFERENCES department(id)) #创建名为外键的外键
ENGINE=INNODB;

CASCADE示例：（删除父表信息时 子表也变化）

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键
FOREIGN KEY (depId)
REFERENCES department(id)
ON DELETE CASCADE #设置删除级联
ON UPDATE CASCADE)#设置更新级联
ENGINE=INNODB;

SET NULL示例：（父表删除的情况下，子表对应的值设置为null，子表用于外键的字段不能有 NOT NULL约束）

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键
FOREIGN KEY (depId)
REFERENCES department(id)
ON DELETE SET NULL #设置删除级联
ON UPDATE SET NULL) #设置更新级联
ENGINE=INNODB;