MySQL总结

MySQL总结

创建表

create table student
(
	id int auto_increment,
	name varchar(100) null,
	age int null,
	class varchar(10) default 'Two' null,
	mysql int null,
	java int null,
	constraint student_pk
		primary key (id)
);

查询所有数据

select * from student;

插入数据，mysql中字符串需要用’'括起来

insert into student
values (1, 'Lone', 18, '二班');

根据给定字段出入数据

insert into student (name, age, class)
values ('Chloe', 17, '二班');

没有提供的字段取默认值或null(该字段必须可null)

insert into student (name, age)
values ('Lily', 17);

批量插入

insert into student (name, age)
values
('小明', 16),
('小许昂', 18),
('小黄', 16),
('小花', 17);

条件更新

# 更新所有班级为二班的为重点班
update student
set class='重点班'
where class='二班';

# 更新Lone的班级为一班，年龄为16
update student
set class = '一班',
    age = 16
where name = 'Lone';

条件删除

# 按条件删除表数据
delete from student
where name = '小明';

查询

简单查询

---

查询指定列

select id, name, age from student;

自定义列名（别名）

select id s_id, name s_name, age s_age from student;

合并列查询

# 合并列查询
select concat(class, '-', name) '班级-姓名'
from student;

# 查询学生的姓名和他的总成绩，列也支持算数运算
select name, (mysql+java) score from student;

#查询常量
select name, '东北育才' school from student;

去重查询

select distinct class from student;

条件查询

---

比较条件

# 查询年龄大于17的学生
select * from student where age > 17;

# between ... and 包含边界值
select * from student where mysql between 78 and 80;

逻辑条件

# （AND）查询一班mysql成绩80以上的学生 and可以连用
select * from student where class='一班' and mysql>80;

# (OR)查询一班、二班 
select * from student where class='一班' or class='二班';

select * from student where (class='一班' and mysql>=80) or (class='重点班' and java>60);

判空条件

# 查询name不是''空字符串的数据
select * from student where name != '';

# 查询name是''空字符串的数据
select * from student where name = '';

# 查询age是null的数据
select * from student where age is null;

# 查询age不是null的数据
select * from student where age is not null;

# <>就是非 包括not null
select * from student where class <> '一班';

模糊条件

符号	含义
like	包含
not like	不包含

配合“_”或“%”一起使用

_ 匹配0或1个字符

% 匹配0到多个字符

select * from student where name like 'M%c';

# binary 区分大小写查询
select * from student where binary name like '%L%';

聚合查询

---

需要计算哪个列的数据就只查询哪个列

# SUM  计算student表中mysql的分数总和
select sum(mysql) sum_mysql from student;

# AVG  计算student表中mysql的平均分数
select avg(mysql) from student;

# MAX 最大值
select max(mysql) from student;

# MIN 最小值
select min(mysql) from student;

# COUNT 行数（去除null）
select count(mysql)  from student;

# 可以同时使用多个聚合函数
select count(mysql), sum(mysql), avg(mysql) from student;

排序查询

---

# 默认以id排序
select * from student;
# 按id倒序
select * from student order by id desc ;
# 根据多个字段排序 先age正序后name倒序
select * from student order by age, name desc ;

# 如果where条件和order