mysql命令操作

数据库操作

1. 连接数据库 mysql -urooot -p
2. 退出数据库 quit/exit
3. 查看当前使用的数据库 select databases()
4. 查看所有数据库 show databases;
5. 创建数据库 create database 数据库名 charset=utf8;
6. 查看创建的数据库 show create database 数据库名
7. 使用数据库 use 数据库名
8. 删除数据库 drop database 数据库名

数据表的操作

1. 查看当前数据库中所有表 show tables
2. 创建表 create table 表格名(name varchar(30) not null,age int unsigned)
3. 查看表格结构 desc 数据表名;
4. 创建classes表

create table classes(id int unsigned primary key auto_increment,name varchar(30) not null)

5. 创建students表

create table students(id int unsigned primary key auto_increment,name varchar(30) not null,age int unsigned,high descimal(3,2),gender enum('男','女','保密') default '保密',cls_id int unsigned)

修改表结构

1. 添加字段mascot alter table classes add mascot varchar(50)
2. 修改字段（重命名） alter table 表名 change 原名 新名 类型及约束
3. 修改字段（不重命名）alter table 表名 modify 列名 类型及约束
4. 删除字段 alter table 表名 drop 列名

插入数据

1. insert into 表名(id,name) values(1,“张三”)
2. 全部插入insert into 表名 values(值1，值2…)
3. 部分插入 insert into 表名(列1，…) values(值1…)
4. 多行插入 insert into 表名(列1) values(值),(值)

修改数据

1. 修改 update 表名 set 列1=值1，列2=值2
2. 全部修改 update e 表名 set 列1=值1
3. 按条件修改 update 表名 set 列1=值1 where id=3
4. 按条件修改多个值update 表名 set gender="",age="" where name="xxx"

查询

1. 查询所有列 select * from 表名
2. 查询students表中所有数据信息select * from students
3. 按条件查询 select * from 表名 where id=1
4. 查询指定列 select name,age from students
5. 字段顺序select age,name from students
6. 使用as为列或表指定别名select name as “姓名”,age from students

使用 as 给字段起别名
select 字段 as 名字… from 表名;
select name as n, age as a from students;

可以通过 as 给表起别名
select 别名.字段 … from 表名 as 别名;
select s.name, s.age from students as s;

消除重复行(查性别)

distinct 字段
select gender from students;
select distinct gender from students; – 使用distinct关键字的做法清除重复的记录
select gender from students group by gender; – 使用分组的做法清除重复的记录

删除

1. 物理删除 delete from 表名 where 条件
2. 逻辑删除

alter table 表名 add 字段 类型 default 默认值
alter table students add is_delete bit default 0
update students set is_delete=1 where id=1

设置查询缓存

show variables like ‘%sort_buffer_size%’;
SET GLOBAL sort_buffer_size = 10241024;
SET sort_buffer_size = 10242048;

条件查询

比较运算符

select … from 表名 where …
查询年纪大于18岁的学生信息
select * from students where age > 18;

查询年纪小于18岁的学生信息
select * from students where age < 18;

查询小于或者等于18岁的学生信息
select * from students where age <= 18;

查询年龄为18岁的所有学生信息
select * from students where age = 18;

查询年龄不为18岁的所有学生信息
select * from students where age != 18;
select * from students where age <> 18;

逻辑运算符

and / between … and …
18和28之间的所有学生信息
select * from students where age > 18 and age < 28; --不包含18 和28
select * from students where age >= 18 and age <= 28; --包含18 和28
select * from students where age between 18 and 28; --包含18 和28

18岁以上的女性的学生信息
select * from students where age > 18 and gender=“女”;
select * from students where age > 18 and gender=2; --枚举的数据类型可以用数字来表示，数字从1开始

or
18以上或者身高高过180(包含)以上的学生信息
select * from students where age > 18 or height >= 180.00;

not
不在 18岁以上的女性 这个范围内的信息
select * from students where not (age>18 and gender=2);
select * from students where not (age > 18 and gender=2);

模糊查询(where name like 要查询的数据)

like
% 替换任意个
_ 替换1个

查询姓名中 以 “小” 开始的名字的学生信息
select * from 表名 where name like “小%”;

查询姓名中 有 “小” 所有的名字的学生信息
select * from 表名 where name like “%小%”;

查询有2个字的名字的学生信息
select * from 表名 where name like “__”;

查询有3个字的名字的学生信息
select * from 表名 where name like “___”;

查询至少有2个字的名字的学生信息
select * from 表名 where name like “__%”;

范围查询

in (1, 3, 8)表示在一个非连续的范围内
查询 年龄为18或34的姓名的学生信息
select * from 表名 where age in (18,34)

not in 不非连续的范围之内
年龄不是 18或34岁的学生信息
select * from 表名 where age not in (18,34)

between … and …表示在一个连续的范围内
查询 年龄在18到34之间的学生信息
select * from 表名 where age between 18 and 34;

not between … and …表示不在一个连续的范围内
查询 年龄不在18到34之间的学生信息
select * from 表名 where age not between 18 and 34;

空判断

判空is null
查询身高为空的学生信息
select * from students where height is null

判非空is not null
select * from students where height is not null

排序

order by 字段
asc
asc从小到大排列，即升序
desc
desc从大到小排序，即降序

查询年龄在18到34岁之间的男性，按照年龄从小到大到排序
select * from students where gender=1 and age between 18 and 34 order by age asc;

查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where gender=2 and age between 18 and 34 order by height desc;

order by 多个字段
查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from students where gender=2 and age between 18 and 34 order by height desc,age asc;

如果年龄也相同那么按照id从大到小排序
select * from students where gender=2 and age between 18 and 34 order by height desc,age asc,id desc;

聚合函数

总数
count
查询男性有多少人
select count(*) from students where gender=1;

最大值
max
查询最大的年龄
select max(age) from students;

查询女性的最高 身高
select max(height) from students where gender=2;

最小值
min
select min(height) from students where gender=2;

求和
sum
计算所有人的年龄总和
select sum(age) from students;

平均值
avg
计算平均年龄
select avg(age) from students;

计算平均年龄 sum(age)/count()
select sum(age)/count() from students;

四舍五入 round(123.23 , 1) 保留1位小数
计算所有人的平均年龄，保留2位小数
select round(avg(age),2) from students;

计算男性的平均身高 保留2位小数
select round(avg(height),2) from students where gender=1;

聚合函数计算的时候不会把null计算进去

分组

分组的口诀
select 分组的字段 from 表名 group by 分组字段;

group by
按照性别分组,查询所有的性别
select gender from students group by gender;

每种 每类

计算每种性别中的人数
select gender,count(*) from students group by gender;

group_concat(…)
查询同种性别中的姓名group_concat(name)
select gender,group_concat(name) from students group by gender;

查询每组性别的平均年龄
select gender,avg(age) from students group by gender;

having(注意having和group by 连用 having后通常也要跟 聚合函数)
查询平均年龄超过30岁的性别，以及姓名
select gender,avg(age),group_concat(name) from students group by gender having avg(age)>30;

查询每种性别中的人数多于2个的信息
select gender, count() from students group by gender having count() > 2;

分页

limit start, count
limit 放在最后面(注意)
limit (要显示第几页-1) * 每页分多少个, 每页分多少个;

限制查询出来的数据个数
查询前5个数据
select * from students limit 5;

每页分2个，要显示第1页
select * from students limit 2;

通用写法
select * from students limit 0,2;

每页分2个，要显示第2页
select * from students limit 2,2

每页分2个，要显示第3页
select * from students limit 4,2

每页分2个，要显示第4页
select * from students limit 6,2

每页分2个，显示第6页的信息, 按照年龄从小到大排序
select * from students order by age asc limit 10,2 ;

select * from students where
select 分组 from students group by 分组字段 having 条件查询
select * from 表a inner join 表b on 连接的条件

连接查询

inner join … on
select … from 表A inner join 表B;
查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=id;

按照要求显示姓名、班级
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;

给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
select s.name as ‘姓名’,c.name as ‘班级’ from students as s inner join classes as c on s.cls_id=c.id;

查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.，只显示班级名称 classes.name.
select s.,c.name from students as s inner join classes as c on s.cls_id=c.id;

在以上的查询中，将班级名显示在第1列
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;

查询 有能够对应班级的学生以及班级信息, 按照班级名进行排序

select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;

当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id asc;

子查询

标量子查询: 子查询返回的结果是一个数据(一行一列)
列子查询: 返回的结果是一列(一列多行)
行子查询: 返回的结果是一行(一行多列)

查询出高于平均身高的信息(height)
select * from students where height > (select avg(heihgt) from students);

查询学生的班级号能够对应的 学生名字
select * from students where cls_id in(1,2);
select id from classes;
select * from students inner join classes on students.cls_id = classes.id;
select students.name from students where cls_id=1 or cls_id =2;
select students.name from students where cls_id in (1,2);
select students.name from students where cls_id in (select id from classes);