MySQL（二）：增删改查

新增

insert into 表名 (字段) values(值)

单条语句

INSERT INTO USER ( id, name, age, sex )
VALUES
	( 7, '李琦', 18, '女' );

多条语句

INSERT INTO USER ( id, NAME, age, sex )
VALUES
	( 8, '学霸', 18, '男' ) ,( 9, '宁长久', 19, '男' )

删除

单条删除

delete from user where id=5;

批量删除

delete from user where id in (4,5);

更新

更新

//update 表名 set 字段=新值 where 条件
UPDATE USER 
	SET NAME = '李七' 
WHERE
	id = 7;

查询

基础查询

查询所有

//select * from 表名
SELECT	* FROM USER;

指定字段查询

//select 字段 from 表名
select name,age from user;

去重

//对年龄进行去重查询
select distinct  age from user ;

起别名

//求学生的总分，字段 as 别名
select *,(chinese+math+english) as '总分' from student;

逻辑运算

//查询语文大于80的人
select * from student where chinese>80;
//多条件查询 and，查询语文大于80并且数学大于70的
select * from student where chinese>80 and math>70;
//模糊查询，查询语文大于50的姓张的学生
//% 通配符 代表0~n个字符
select * from student where name like '张%' and chinese>80;
//_ 通配符  表示单个任意字符
// between and  ，语文在80到90之间
select * from student where chinese between 80 and 90;
//in和or，查询年龄是18，19的
select * from user where age in (18,19);
select * from user where age=18 or age=19;

排序

//select 字段 from 表 order by 要排序的字段 asc|desc
//asc 升序（默认）、desc降序

分组查询

使用group by对列进行分组
使用having对分组后的结果进行过滤
having子句中也可以使用别名进行过滤

查询男生和女生的平均年龄

SELECT
	sex AS '性别',
	avg( age ) AS '平均年龄' 
FROM
USER 
GROUP BY
	sex;

显示男生平均年龄

SELECT
	avg( age ) AS '男生平均年龄' 
FROM
USER 
GROUP BY
	sex 
HAVING
	sex = '男'

复杂查询

分页查询

//从start加1行开始，取rows行。start从0开始计算
select ... limit start,rows

查询用户信息，id降序排列，每页显示3条数据

select * from user order by id desc limit 0,3;

if条件

if(expr1，expr2，expr3)  //expr1为true，显示expr2，否则显示expr3

统计所有人数、男生人数、女生人数

SELECT
	count( * ) AS '所有人数',
	count( IF ( sex = '男', 1, NULL ) ) AS '男生人数',
	count( IF ( sex = '女', 1, NULL ) ) AS '女生人数' 
FROM
	USER;

count（字段）只会统计字段值不为null的

多条件

如果select语句中同时含有group by,having,limit,order by那么顺序应该是
group by,having,order by,limit

统计男生、女生的平均年龄，并且平均年龄大于10，并且平均年龄按从高到低排序

SELECT
	sex AS '性别',
	avg( age ) AS avg_age 
FROM
USER 
GROUP BY
	( sex ) 
HAVING
	avg_age > 10 
ORDER BY
	avg_age DESC;

多表查询

查询学生姓名、性别、语文成绩

select user.name,sex,chinese from user,grades;

SELECT USER.NAME,sex,chinese FROM USER,grades WHERE USER.NAME = grades.NAME;

• 查询多表时如果表里有相同的字段名，为了避免查询时不知道查询哪一个表的该字段。可以给字段前面加上表。
• 查询多表的关键是写出正确的过滤条件，去除无用的数据
• 多表查询时，查询条件不能少于表的个数-1

自连接

• 自连接是指在同一张表上的连接查询，本质也是多表查询，这多张表都是该表本身。
• 自连接查询时要给表起一个别名，这样就相当于查询两张（多张）不同的表

select * from user as user1,user as user2;

显示公司员工名字，及其上级的名字

select worker.name as '职员名',boss.name as '上级名'
       from emp as worker,emp as boss
	   where worker.mgr=boss.empno;

单行子查询

显示用秦一性别相同的所有学生信息

SELECT * FROM USER 
WHERE sex = ( SELECT sex FROM USER WHERE NAME = '秦一' )

多行子查询

查询和部门10的工作相同的雇员的名字和岗位，但是不含10部门自己的雇员

SELECT	ename,job FROM emp 
WHERE
	job IN ( SELECT DISTINCT job FROM emp WHERE deptno = 10 ) 
	AND deptno != 10

临时表

将子查询的结果当作临时表进行使用

查询商品表中各个类别中价格最高的商品名称、价格、所属类别

SELECT
	goods_name,
	goods_price,
	goods_cate 
FROM
	( SELECT goods_cate, max( goods_price ) AS max_price FROM goods GROUP BY goods_cate ) temp, -- 将查询结果临时命名为一张临时表 
	goods 
WHERE
	temp.goods_cate = goods.goods_cate 
	AND temp.max_price = goods.goods_pricw

all操作符

显示年龄比2018级所有学生的年龄都要大的学生姓名、年龄

select name,age from user 
where
	age > all ( select age from user where grade = '2018级' )

any操作符

显示年龄比2018级其中一个学生的年龄大的学生姓名、年龄

select name,age from user 
where
	age > any ( select age from user where grade = '2018级' )

多列子查询

查询性别和年级都与秦一性别和年级相同的学生信息

select	* from user 
where
	( sex, grade ) = ( select sex, grade from user where name = '秦一' );

合并查询

union、union all 可以用于合并多个select语句的查询结果
union all 将两个查询结果合并，不会去重
union 将两个查询结果合并并去重

查询年龄小于20，并且性别是男的学生

SELECT	* FROM USER WHERE	age < 20
UNION ALL
SELECT	* FROM USER WHERE	sex = '男'

SELECT	* FROM USER WHERE	age < 20
UNION 
SELECT	* FROM USER WHERE	sex = '男'

外连接

外连接分为左外连接和右外连接两种外连接方式。左外连接就是查询结果左侧全部显示，右外连接同理，一般左外连接用的较多

//左外连接，表1就是左表，表2就是右表
select ... form 表1 left join 表2 on 条件
//右外连接
select ... form 表1 right join 表2 on 条件

使用左连接显示所有人的成绩，如果没有成绩也要显示学生的姓名和id，成绩显示为空

select user.name,chinese from user left join grades on user.name=grades.name;