mysql常用命令

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

常用操作

客户端登录mysql服务器

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

执行sql脚本

mysql里:

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

shell里:

1
mysql -uroot -p -t < employees.sql

修改mysql密码

例如,把密码从123修改为123456

1
2
3
4
➜  ~ mysqladmin -uroot -p123 password 123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysqladmin: unable to change password; error: 'Column count of mysql.user is wrong. Expected 51, found 50. Created with MySQL 80013, now running 80015. Please use mysql_upgrade to fix this error.'

提示要先升级

1
2
3
4
5
6
7
8
9
10
11
12
13
➜  ~ sudo mysql_upgrade -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
...
Upgrade process completed successfully.
Checking if update is needed.
➜ ~ mysqladmin -uroot -p123 password 123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

修改成功

查看现有数据库

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;

顺序问题

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%