一、数据库操作
1、连接数据库
// -u 用户名 -p 密码
mysql -uroot -p
2、退出数据库
quit
exit
3、查看所有数据库
show databases;
4、显示数据库版本
select version();
5、查看当前使用的数据库
select database();
6、创建数据库
create database 数据库名 charset=utf8;
7、使用数据库
use 数据库名
8、删除数据库
drop database 数据库名
二、数据表操作
1、查看当前数据库中所有的表
show tables;
2、创建数据表数据类型
-- 无符号整形
int unsigned
-- 表示自动增长,跟主键在一起
auto_increment
-- 表示不能为空
not null
-- 表示主键
primary key
-- 默认值
default
3、创建数据表
create table 数据表名(字段 类型 约束[, 字段 类型 约束]);
4、查看表结构
desc 数据表名
5、修改数据表添加字段
alter table 表名称 add 字段名 类型;
6、修改数据表修改字段(不重命名)
alter table 表名称 modify 字段名 类型;
7、修改数据表修改字段(重命名)
alter table 表名称 change 原名 新名 类型;
8、修改数据表删除字段
alter table 表名称 drop 字段名;
9、删除数据表
drop table 表名称;
三、增删改查
1、增加
全列插入
insert into 表名称(字段1,字段2) values(值1,值2);
全部插入
insert into 表名称 values(值1,值2,值3,...);
部分插入
insert into 表名称(字段1,字段2,...) values(值1,值2,..);
多行插入
insert into 表名称(字段1) values(值1),(值2);
2、修改
全部修改
update 表名称 set 字段1=值1,字段2=值2;
按条件修改(同按条件修改多个值类似)
update 表名称 set 字段1=值1,字段2=值2,... where 条件;
3、查询
查询所有字段
select * from 表名称;
按照条件查询
select * from 表名称 where 条件;
查询指定字段
select 字段1,字段2 from 表名称;
使用 as 指定别名
select 字段 as 别名 from 表名称;
4、模糊查询
-- % 替换任意个
-- _ 替换1个
以模糊字段开始
select * from 表名称 where 字段名 like '模糊字段%';
包含模糊字段
select * from 表名称 where 字段名 like '%模糊字段%';
查询字段个数
-- 下划线个数表示字段个数
select * from 表名称 where 字段名 like '__';
查询字段至少有2个字
select * from 表名称 where 字段名 like '__%';
5、范围查询
in(1,3,8)表示在一个非连续的范围内
select * from 表名称 where 字段名 in (1,8);
or表示或者
select * from 表名称 where 字段名=1 or 字段名=8;
not in表示不非连续的范围之内
select * from 表名称 where 字段名 not in (1,8);
between…and…表示在一个连续的范围内
select * from 表名称 where 字段名 between 1 and 8;
not between…and…表示不在一个连续的范围内
select * from 表名称 where 字段名 not between 1 and 8;
6、空判断
is null表示判断为空
select * from 表名称 where 字段名 is null;
is not null表示判断非空
select * from 表名称 where 字段名 is not null;
7、排序
-- 字段
order by
-- 从小到大,即升序(默认)
asc
-- 从大到小,即降序
desc
18-34之间男性,年龄从小到大排序
-- order by 后面可不写,默认为asc升序
select * from 表名称 where 字段名 between 18 and 34 and 字段名='男' order by 排序字段名 asc;
多字段排序
18-34之间男性,年龄从小到大排序,年龄相同,身高从大到小排序
select * from 表名称 where 字段名 between 18 and 34 and 字段名='男' order by 排序字段名1 asc,排序字段名2 desc;
8、聚合函数
总数 count
select count(*) from 表名称 where 字段名='字段';
最大最小值 max min
-- 最大值
select max(字段) from 表名称;
-- 最小值
select min(字段) from 表名称;
-- 根据字段查询最大值
select max(字段) from 表名称 where 字段名='字段';
求和 sum
select sum(字段) from 表名称;
求平均值 avg
select avg(字段) from 表名称;
四舍五入 round(数据,小数位数),保留2位小数
select round(avg(字段),2) from 表名称;
聚合函数计算时不会计算null
9、分组
按照性别分组,查询所有的性别(同去重效果类似)
select 分组的字段 from 表名称 group by 分组字段;
计算每种性别的人数
select 分组的字段,count(*) from 表名称 group by 分组字段;
查询同种性别中的姓名 group_concat(…)
select 分组的字段,group_concat(姓名字段) from 表名称 group by 分组字段;
查询每组性别的平均年龄
select 分组的字段,avg(年龄字段) from 表名称 group by 分组字段;
having分组条件通常与聚合函数连用
-- 聚合函数作为条件,只能和having搭配,不能和where一起使用
select 分组的字段,avg(年龄字段),group_concat(姓名字段) from 表名称 group by 分组字段 having avg(年龄字段)>30;
10、分页
公式:limit (要显示第几页-1) * 每页分几条,展示第几页;
-- limit start count
-- limit 放在最后(注意)
-- 限制查询出来的个数
查询前五条数据
select * from 表名称 limit 5;
每页两条,显示第一页
-- (1-1) * 2,2为公式
select * from 表名称 limit (1-1) * 2,2;
-- 等同于
select * from 表名称 limit 0,2;
每页两条,显示第二页
select * from 表名称 limit 2,2;
每页两条,显示第三页
select * from 表名称 limit 4,2;
每页两条,显示第六页,按照年龄从小到大排序
-- 先排序再分页
select * from 表名称 order by 年龄字段 asc limit 10,2;
11、连接查询(多表查询)
select * from 表1 inner join 表2 on 连接的条件;
-- inner join ... on
-- select ... from 表1 inner join 表2;
查询能够对应班级的学生以及班级信息
简化方式可以使用 as 关键字给表取别名
select * from 表1 inner join 表2 on 表1名称.id=表2名称.id;
按照要求显示姓名和班级
select 表1名称.姓名,表2名称.班级 from 表1 inner join 表2 on 表1名称.id=表2名称.id;
查询学生及班级信息,显示学生所有信息,班级只显示班级名称
select 表1.*,表2.班级 from 表1 inner join 表2 on 表1名称.id=表2名称.id;
同上查询,将班级名称放在第一列
select 表2.班级,表1.* from 表1 inner join 表2 on 表1名称.id=表2名称.id;
查询学生及班级信息,按照班级名称排序
select 表2.班级,表1.* from 表1 inner join 表2 on 表1名称.id=表2名称.id order by 表2.班级 asc;
同上,按照学生的id进行从大到小排序
select 表2.班级,表1.* from 表1 inner join 表2 on 表1名称.id=表2名称.id order by 表2.班级 asc 表1.id desc;
12、子查询
查询高于平均身高的信息
select * from 表名称 where 身高字段>(select avg(求平均值字段) from 表名称);
查询学生班级号能对应的学生名字
select * from 表1名称 where 班级号 in (1,2);
select id from 表2名称;
select 表1名称.学生名称 from 表1名称 where 班级号 in (select id from 表2名称);
13、删除
物理删除
delete from 表名称 where 条件;
逻辑删除(添加状态字段0,1表示数据是否被删除)
update 表名称 set 状态=1 where 条件;