mysql常用命令

关于mysql常用的增删查改,聚合查询,子查询,内连接外连接查询的总结。

常用操作

客户端登录mysql服务器

1
2
mysql -u root -p
// 再输入密码

查看现有数据库

1
show databases;

创建新的数据库

1
2
// 创建名为limengting的数据库
create database limengting;

删除数据库

1
2
// 删除名为limengting的数据库
drop database limengting;

使用某个数据库

1
2
// 使用名为limengting的数据库
use limengting;

查看当前在使用哪个数据库

1
status;

查看当前数据库中的现有表

1
show tables;

创建表

1
2
3
4
5
// 建名叫grades的表,有name,course,grade三个字段,分别是字符串、字符串、整型
// varchar(n)中的n代表字符数
create table `grades`(`name` varchar(100) not null, `course` varchar(100) not null, `grade` int);
// 建info表
create table `info`(`name` varchar(100) not null, `id` int, primary key (`id`));

注意:建表时,表名和字段名外面的符号 ` 不是单引号,而是英文输入法状态下的反单引号,也就是键盘左上角 esc 按键下面的那一个 ~ 按键

查看某个表的建表语句

1
2
// 查看grades表的建表语句
show create table grades;

查看表结构

1
2
3
4
// 查看grades表的表结构
desc grades;
// 或者
show columns from info;

删除表

1
2
// 删除grades表
drop table grades;

在表中插入一行

1
2
3
insert into grades (name, course, grade) values ("张三","语文",81);
// 如果插入的字段和要求的字段顺序一一匹配可以直接写成:
insert into grades values("张三","数学",75);

注意:如果数据是字符型,必须使用单引号或者双引号,如:“张三”。

在表中一次插入很多行

1
2
3
4
5
insert into grades values ("李四","语文",76),
("李四","数学",90),
("王五","语文",81),
("王五","数学",100),
("王五","英语",9);

查看表中的全部数据

1
2
// 查看grades表中的数据
select * from grades;

更新一行

1
2
// 更新王五的英语成绩为90
update grades set grade = 90 where name = "王五" and course = "英语";

删除一行

1
2
// 删除张三的语文成绩
delete from grades where name = "张三" and course = "语文";

通配符查询

1
2
// 查找名字为*五的人的数据
select * from grades where name like "%五";

调整两列的顺序

1
2
3
4
// 把info表中的id放在第一列
alter table info modify id int first;
// 把name列放在id列后面
alter table student modify name varchar(10) after id;

添加、删除表字段

1
2
3
4
// 在info表中添加class字段
alter table info add class int;
// 删除tmp字段
alter table info drop tmp;

修改字段类型及名称

1
2
3
4
// 修改class的类型为int(10)
alter table info modify class int(10);
// 修改class,新名字为class1,新类型为int(15)
alter table info change class class1 int(15);

修改字段的not null/null和default属性

1
2
3
4
// 修改class属性为not null,默认值为1
alter table info modify class int not null default 1;
// 修改class的默认值为2
alter table info alter class set default 2;

把两个查询语句的结果去重并降序输出

1
2
// 找grades和info中所有的name去重降序输出,不指明desc或者asc默认是asc(升序)
select name from grades union select name from info order by name desc;

将数据表按名字进行分组,并统计每个人有多少条记录

1
select name, count(*) from grades group by name;

常用组函数

1
2
3
4
5
avg(distinct expr)
count(distinct expr)
max(distinct expr)
min(distinct expr)
sum(distinct expr)

注意:组函数忽略列值为null的行,不参与计算;使用关键字distinct可以剔除字段值重复的条数

将数据表按名字进行分组,输出每个人的名字和分数>80的记录数量

1
select name, count(*) from grades where grade > 80 group by name;

查找分数全部大于80的学生的姓名

1
2
3
4
select name from grades group by name having min(grade) > 80;
// 或者
select distinct name from grades where name not in (select distinct name from grades where grade <= 80 or grade is null);
// is null/is not null不是==null/!=null

两个表以名字相同作为内连接条件输出大表

1
select * from grades join info on grades.name = info.name;

截取内连接后的大表中的某些字段输出

1
2
3
select a.id, a.name, b.grade from info a join grades b on a.name = b.name;
// 等价于
select a.id, a.name, b.grade from info a, grades b where a.name = b.name;

左连接与右连接

左连接,左边的数据会全部输出,如果右边字段没数据右边字段是null

右连接,右边的数据会全部输出,如果左边字段没数据左边字段是null

1
2
3
4
// 左连接
select a.id, a.name, b.grade from info a left join grades b on a.name = b.name;
// 右连接
select a.id, a.name, b.grade from info a right join grades b on a.name = b.name;

mysql下执行sql脚本

1
source /Users/sunnie/git/forum/df.sql

顺序问题

1
... where ... group by ... having ... order by ... limit ...

综合问题

  • 学生表student(id,name)
  • 课程表course(id,name)
  • 学生课程表student_course(sid,cid,score)

查询student表中重名的学生,结果包含id和name,按name升序

1
select id,name from student where name in (select name from student group by name having count(*)>1) order by name;

在student_course表中查询平均分不及格的学生,列出学生id和平均分

1
select sid, avg(score) as avg_score from student_course group by sid having avg(avg_score)<60;

where子句中不能用聚集函数作为条件表达式,但是having短语可以,where和having的区别在于对用对象不同,where作用于记录,having作用于组

在student_course表中查询每门课成绩都不低于80的学生id/姓名

1
2
3
4
5
6
// 输出id
select sid from student_course group by sid having min(score)>= 80;
// 输出id的第二种方法
select distinct sid from student_course where sid not in (select distinct sid from student_course where score < 80 or score is null);
// 输出姓名
select name from student where id in (select sid from student_course group by sid having min(score)>=80);

查询每个学生的总成绩,结果列出学生姓名和总成绩

1
select a.name,sum(score) from student a left join student_course b on a.id = b.sid group by a.id;

查询总成绩最高的学生,结果列出学生id和总成绩

1
2
3
4
// 效率低
select sid,sum(score) as sum_score from student_course group by sid having sum_score >= all(select sum(score) from student_course group by sid);
// 效率高
select sid,sum(score) as sum_score from student_course group by sid order by sum_score desc limit 1;

在student_course表查询课程1成绩第2高的学生,如果第2高的不止一个则列出所有的学生

1
2
limit a,b //由于第1行是0号行,所以是从第a+1行开始的b行[a+1,a+b]行;如果b=-1表示直到结束
select * from student_course where cid = 1 and score = (select score from student_course where cid = 1 order by score desc limit 1,1);

在student_course表查询各科成绩最高的学生,结果列出课程id和对应的成绩

1
select cid,max(score) from student_course group by cid;

使用了group by的查询字段只能是group by中的字段或者聚集函数或者是每个分组内均相同的字段

1
2
// 这样写是错误的,不能查sid
select cid,sid,max(score) from student_course group by cid;
谢谢小天使请我吃糖果
0%