SQL命令及MariaDB(一)

数据库相关SQL语句

• 以分号结尾
• 可以有空格和回车换行
• SQL语句不区分大小写

相关语句

1.通用登录

mysql -uroot -p
1

2.查询所有数据库

show databases;
1

3.创建数据库

格式:create database 数据库名 charset=utf8/gbk

create database db1;
create database db2 charset=utf8;
create database db2 charset=gbk;
1
2
3

4.查询数据库

格式:show create database 数据库名;

show create  databse db1;
show create  databse db2;
show create  databse db3;
1
2
3

5.删除数据库

格式: drop database 数据库名;

drop database db3;
1

表相关的SQL语句

• 执行表相关的SQL语句必须使提前用了某个数据库
• 格式: use 数据库名

use db1;
1

1.创建表

• 格式:create table 表名

crate table person(name varchar(50),age int)
create table car(name varchar(50),price int,type varchar(10))charset=utf8;
create table tearcher(name varchar(50),age int,gender varchar(1))charset=gbk; 
1
2
3

2.查询所有表

• 格式:show tables;
  

3.查询表信息

• 格式:show create table 表名;

show create table car;
show create table teacher;
1
2

4.查询表字段

• 格式:desc 表名;

desc teacher;
desc car;
1
2

5.删除表

• 格式:drop table 表名

drop table teacher;
1

6.修改表名

• 格式:rename table 原名 to 新名

rename table person to per;
1

7.添加表字段

• 最后面添加样式:alter table 表名 add 字段名 类型;
• 最前面添加格式:alter table 表名 add 字段名 类型 first;
• 在xxx字段后面添加格式:after table 表名 add 字段名 类型 after xxx;

alter table per add gender varchar(1);
alter table per add id int first;
alter table per add salary int after name;
1
2
3

8.删除表字段

• 格式:alter table 表名 drop 字段名

alter table per drop salary;
1

9.修改表字段

• 格式:alter table 表名 change 原名 新名 新类型;

alter table per change gender salary int;
1

数据相关的SQL语句

• 执行数据相关的SQL语句之前必须使用了某个数据库,而且已经创建好了保存数据的表

create database mydb1 charset=utf8;
use mydb1;
create table person(name varchar(50),age int);
1
2
3

1.插入数据(增)

• 全表插入格式:insert into 表名 vaues(值1,值2,值3);
• 指定字段插入格式:insert into 表名(字段1,字段2)values(值1,值2);

insert into person values('tom',18);
insert into person(name)values('jerry');
1
2

• 批量插入数据

insert into person values('liubei',38),('guanyu',50),('zhaoyun',40);
insert into person(name)values('lilei'),('hanmeimei');
1
2

• 插入中文

insert into person values('刘德华',17);
1

2.查询数据

• 格式：select 字段信息 from 表名 where 条件;

select name from person;
select name,age from person;
select * from person;
select name from person where age>20;
select age from person where name='tom';
1
2
3
4
5

3.修改数据

• 格式:update 表名 set 字段名=值,字段名=值 where

update person set age=50 where name='tom';
update person set name='李雷' where name='lilei';
1
2

4.删除数据

• 格式:delete 表名 from where 条件;

delete from person where name='李雷';
1

删除空值

delete from person where age is null;
1

综合练习(1)

创建数据库newdb1, 字符集utf8并使用
create database newdb1 charset=utf8;
use newdb1;
在数据库中创建员工表emp 字段:id,name,sal(工资),deptId(部门id) 字符集utf8
create table emp(id int,name varchar(50),sal int,deptId int);
创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
create table dept(id int,name varchar(20),loc varchar(20));
部门表插入以下数据: 1 神仙部 天庭       2 妖怪部  盘丝洞
insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');
员工表插入一下数据:  1 悟空 5000 1  ,   2 八戒  2000  1  ,3 蜘蛛精 8000  2  ,  4 白骨精 9000  2
insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);
查询工资6000以下的员工姓名和工资
select name,sal from emp where sal<6000;
修改神仙部的名字为取经部
update dept set name='取经部' where name='神仙部';
给员工表添加奖金comm字段
alter table emp add comm int;
修改员工表中部门id为1的 奖金为500
update emp set comm=500 where deptId=1;
把取经部的地址改成五台山
update dept set loc='五台山' where name='取经部';
修改奖金字段为性别gender字段 类型为varchar(5)
alter table emp change comm gender varchar(5);
修改孙悟空和猪八戒性别为男
update emp set gender='男' where deptId=1;
删除没有性别的员工
delete from emp where gender is null;
删除性别字段
alter table emp drop gender;
删除表和删除数据库
drop table emp;
drop table dept;
drop database newdb1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

数据类型

1.整数

int(m)和bigint,bigint等效Java中的long,m代表显示长度 用来补零

create database day2db charset=utf8;
use day2db;
create table t1(age int(5) zerofill);
insert into t1 values(18);
select * from t1;
1
2
3
4
5

2.浮点数

double(m,d) m代表总长度 d代表小数长度,23.645 m=5 d=3

create table t2(price double(5,3));
insert into t2 values(23.345);
insert into t2 values(23.3456789);
insert into t2 values(234.34);--报错,超出范围
1
2
3
4

3.字符串

• char(m):固定长度,m=5 存""刘德华"占5个字符长度,执行效率略高,最大值255
• varchar(m):可变长度,m=5 存"刘德华" 占3个字符长度,节省空间,最大值65535但是建议保存255以内的数据,超过255的建议使用text
• text(m):可变长度,最大值65535

4.日期

• date:保存年月日
• time:保存 时分秒
• datetime:保存年月日时分秒,默认值为null,最大值9999-12-31
• timestamp:(时间戳:距离1970年1月1日的毫秒数)保存年月日时分秒,默认值为当前系统时间,最大值2038-1-19

create table t3(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t3 values("2022-5-20",null,null,null);
insert into t3 values(null,"10:38:40","2008-10-20 10:20:30",null);
1
2
3

主键约束 primary key

• 主键:表示数据唯一性的字段称为主键
• 约束:创建表时给表字段添加的限制条件
• 主键约束:限制主键的值 唯一且非空

create table t4(id int primary key,name varchar(20));
insert into t4 values(1,'aaa');
insert into t4 values(2,'bbb');//报错不能重复
insert into values(null,'ccc');//报错不能为空
1
2
3
4

主键约束+自增 primary key auto_increment

• 自增规则:从历史最大基础上+1
• 此时空值反而成了触发的条件

create table t5(id int primary key auto_increment,name varchar(20));
insert into t5 values(null,'aaa');
insert into t5 values(null,'bbb');
insert into t5 value(10,'ccc');
insert into t5 value(null,'ddd');
delete from t5 where id>=10;
insert into t5 values(null,'eee');
1
2
3
4
5
6
7

导入*.sql批处理文件

• 执行命令 source 路径;

source E:/emp.sql--注意反斜杠
1

• 如果数据显示乱码

set names utf8;
1

比较运算符 > < >= <= = !=和<>

1. 查询工资小于等于3000的员工姓名和工资
2. 查询程序员的名字
3. 查询2号部门的员工姓名,工资和工作
4. 查询不是人事的员工姓名和工作(两种写法)
1
2
3
4

select name,sal from emp where sal<=3000;
select name from emp where job='程序员';
select name,sal,job from emp where dept_id=2;
select name,job from emp where job!='人事';
select name,job from emp where job<>'人事';
1
2
3
4
5

and和or not

• 查询多个条件同时满足使用and
• 满足一个条件时使用or

1. 查询1号部门工资高于2000的员工信息
2. 查询三号部门或工资等于5000的员工信息
3. 查询有上级领导的员工姓名 
4. 查询出CEO和项目经理的名字
5. 查询有奖金的销售名字和奖金
1
2
3
4
5

select * from emp where dept_id=1 and sal>2000;
select * from emp where dept_id=3 or sal=5000;
select name from emp where manager is not null;
select name from emp where job='CEO' or job='项目经理';
select name,comm from emp where job='销售' and comm>0;
1
2
3
4
5

between x and y 包含x和y

1. 查询工资在2000-3000之间的员工信息
2. 查询工资小于2000并且大于3000的员工信息
1
2

select * from emp where sal between 2000 and 3000;
select * from emp where sal not between 2000 and 3000;
1
2

in

• 查询某个字段的值为多个值的时候使用in

1. 查询工资等于3000,1500和5000的员工信息
2. 查询人事和销售的信息
3. 查询工作不是人事也不是销售的信息
1
2
3

select *from emp where sal in(3000,1500,5000);
select distinct dept_id from emp;
SELECT * FROM emp WHERE job NOT IN('人事','销售');
1
2
3

去重distinct

1. 查询1号部门中出现了哪几种不同的工作
2. 查询员工表中出现了哪几种不同的部门id
1
2

select distinct job from emp where dept_id=1;
select distinct dept_id from emp;
1
2

综合练习(2)

1. 查询2号部门工资高于1000的员工信息
2. 查询3号部门或工资等于5000的员工信息
3. 查询工资在1000到2000之间的员工姓名和工资
4. 查询工资不等于3000和5000的员工信息
5. 查询3号部门有哪几种不同的工作
6. 查询1号部门没有领导的员工姓名
7. 查询出所有销售,人事和程序员
8. 查询出刘关张三个人的工资
1
2
3
4
5
6
7
8

SELECT * FROM emp WHERE sal>1000 AND dept_id=2;
SELECT * FROM emp WHERE dept_id=3 OR sal=5000;
SELECT NAME,sal FROM emp  WHERE sal BETWEEN 1000 AND 2000;
SELECT * FROM emp WHERE sal NOT IN(3000,5000);
SELECT  DISTINCT job FROM emp WHERE dept_id=3;
SELECT NAME FROM emp WHERE dept_id=1 AND manager IS NULL;
SELECT * FROM emp WHERE job IN('销售','人事','程序员');
SELECT NAME,sal FROM emp WHERE NAME IN('刘备','关羽','张飞');
1
2
3
4
5
6
7
8

模糊查询 like

• _:代表1个未知字符
• %:代表0或多个未知字符
• 举例:
  ○ 以x开头 x%
  ○ 以x结尾 %x
  ○ 以x开头y结尾 x%y
  ○ 包含x %x%
  ○ 第二个字符是x _x%
  ○ 第三个是x倒数第二个是y _x%y

1. 查询姓孙的员工姓名
2. 查询名字中包含僧的员工信息
3. 查询名字以精结尾的员工姓名
4. 查询工作中包含销售并且工资大于1500的员工信息
5. 查询工作中第二个字是售的员工姓名和工作
6. 查询1号和2号部门中工作以市开头的员工信息
1
2
3
4
5
6

SELECT * FROM emp WHERE NAME LIKE '%僧%'; 
SELECT NAME FROM emp WHERE NAME LIKE '%精';
SELECT * FROM emp WHERE sal>1500 AND job LIKE '%销售%';
SELECT NAME,job FROM emp WHERE job LIKE '_售%';
SELECT * FROM emp WHERE job LIKE'市%' AND dept_id IN(1,2);
1
2
3
4
5

排序 order by

• 格式:orderby 字段名 asc升序(默认)/desc降序

查询每个员工的姓名和工资,并按照工资升序排序
查询工资高于2000的员工姓名和工资, 按照工资降序排序
查询每个员工的姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序
1
2
3

select name,sal from emp order by sal;
select name,sal from emp order by sal asc;
select name,sal from emp order by sal desc;       //降序

select name,sal from emp where sal>2000 order by sal desc;
select name,sal,dept_id from emp order by dept_id,sal desc;
1
2
3
4
5
6

综合练习(3)

查询有领导的员工信息,按照入职日期(hiredate) 升序排序
查询1号部门中名字中包含八的员工信息
查询2号和3号部门中工资低于1500的员工信息
查询人事和程序员中工资高于2500的员工姓名,工资和工作
查询不是CEO的员工中工资高于2000的员工姓名,工资和工作,并且按照工资降序排序
1
2
3
4
5

select * from emp where manager is not null order by hiredate;
select * from emp where dept_id=1 and name like "%八%";
select * from emp where dept_id in(2,3) and sal<1500;
select name,sal,job from emp where job in("人事","程序员") and sal>2500;
select name,sal,job from emp where job!='CEO' and sal>2000 order by sal desc;
1
2
3
4
5

分页查询limit

• 格式:limit跳过的条数,请求的条数(每页的条数)
• 跳过的条数=(请求的页数-1)*每页的条数
  • 第1页的5条数据 limit 0,5
  • 第2页的5条数据 limit 5,5
  • 第3页的5条数据 limit 10,5
  • 第4页的10条数据 limit 30,10
  • 第8页的7条数据 limit 49,7
  • 第3页的9条数据 limit 18,9

- 查询id,姓名,工资和工作,按照工资升序排序 请求第1页的5条数据
  select id,name,sal,job from emp order by sal limit 0,5;
- 查询所有员工的姓名和工资,按照工资升序排序,请求第2页的5条数据
  select name,sal from emp order by sal  limit 5,5;
- 查询工资最高的员工信息   
  select * from emp order by sal desc limit 0,1;
- 按照入职日期排序 查询第2页的3条数据
  select * from emp order by hiredate limit 3,3;
- 按照工资升序排序查询第3页的2条数据       
  select * from emp order by sal limit 4,2;
1
2
3
4
5
6
7
8
9
10

别名

select name as '姓名' from emp;
select name '姓名' from emp;
select name 姓名 from emp;
1
2
3

综合练习(4)

1. 查询员工表中 3 号部门工资高于 1500 的员工信息

   select * from emp where dept_id=3 and sal>1500;

2. 查询 2 号部门员工或者没有领导的员工信息

   select * from emp where dept_id=2 or manager is null;

3. 查询有领导的员工姓名,工资按照工资降序排序

   select name,sal from emp where manager is not null order by sal desc;

4. 查询 2 号和 3 号部门的员工姓名和入职日期 hiredate 按照入职日期降序排序

   select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;

5. 查询名字中包含僧和包含精的员工姓名

   select name from emp where name like "%僧%" or name like "%精%";

6. 查询工资高于 2000 的工作有哪几种? 

   select distinct job from emp where sal>2000;

7. 查询工资升序第 4 页的 2 条数据

   select * from emp order by sal limit 6,2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

聚合函数

• 可以对查询的多条数据进行统计查询，统计方式包括：求平均值，求最大值，求最小值，求和，计数

1. 平均值avg(字段名)
   - 查询1号部门的平均工资
     select avg(sal) from emp where dept_id=1;
2. 最大值max(字段名)
   - 查询2号部门的最高工资
     select max(sal) from emp where dept_id=2;
3. 最小值min(字段名)
   - 查询2号部门的最低工资
     select min(sal) from emp where dept_id=2;
4. 求和sum(字段名)
   - 查询3号部门的工资总和
     select sum(sal) from emp where dept_id=3;
5. 计数count(*)
   - 查询1号部门的人数
     select count(*) from emp where dept_id=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select avg(sal) from emp where dept_id=1;
select max(sal) from emp where dept_id=2;
select min(sal) from emp where dept_id=2;
select sum(sal) from emp where dept_id=3;
select count(*) from emp where dept_id=1;
1
2
3
4
5

聚合函数练习题

1. 查询销售的平均工资
   select avg(sal) from emp where job='销售';
2. 查询程序员的最高工资
   select max(sal) from emp where job='程序员';
3. 查询名字中包含精的人数
   select count(*) from emp where name like "%精%";
4. 查询和销售相关的工作,工资总和是多少
   select sum(sal) from emp where job  like "%销售%";
5. 查询2号部门的最高工资和 最低工资 起别名
   select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=2;
1
2
3
4
5
6
7
8
9
10

分组查询

• 可以将某个字段相同的数据划分为一组，然后以组为单位进行统计查询

1. 查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;
2. 查询每个部门的人数
select dept_id,count(*) from emp group by dept_id;
3. 查询每种工作的人数 
select job,count(*) from emp group by job;
4. 查询每个部门工资高于 2000 的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;
5. 查询 1 号部门和 2 号部门的人数
select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;
6. 查询平均工资最高的部门 id 和平均工资
select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;
select dept_id,avg(sal) a from emp group by dept_id order by a desc limit 0,1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

总练习

1. 查询工资大于等于 3000 的员工姓名和工资
2. 查询 1 号部门的员工姓名和工作
3. 查询不是程序员的员工姓名和工作(两种写法)
4. 查询奖金等于 300 的员工姓名,工资和工作
5. 查询 1 号部门工资大于 2000 的员工信息
6. 查询 3 号部门或工资等于 5000 的员工信息
7. 查询出 CEO 和项目经理的名字
8. 查询工资为 3000,1500 和 5000 的员工信息
9. 查询工资不等于 3000,1500 和 5000 的员工信息
10. 查询工资在 1000 到 2000 之间的员工信息
11. 查询工资在 1000 到 2000 以外的员工信息
12. 查询有领导的员工姓名和领导 id
13. 查询没有领导的员工姓名和工资
14. 查询员工表中出现了哪几种不同的工作
15. 查询员工表中出现了那几个部门的 id
16. 查询姓孙的员工姓名
17. 查询名字最后一个字是精的员工信息
18. 查询工作中包含销售的员工信息
19. 查询工作中第二个字是售的员工信息
20. 查询名字中包含僧的员工并且工资高于 2000 的员工信息
21. 查询 1 号和 2 号部门中工作以市开头的员工信息
22. 查询所有员工的姓名和工资 按照工资升序排序
23. 查询所有员工的姓名和工资 按照工资降序排序
24. 查询所有员工姓名 工资和部门 id 按照部门 id 降序排序,如果部门 id 一致则按照工资升序排序
25. 查询员工表中 3 号部门工资高于 1500 的员工信息
26. 查询 2 号部门员工或者没有领导的员工信息
27. 查询有领导的员工姓名,工资按照工资降序排序
28. 查询 2 号和 3 号部门的员工姓名和入职日期 hiredate 按照入职日期降序排序
29. 查询名字中包含僧和包含精的员工姓名
30. 查询工资高于 2000 的工作有哪几种?
31. 查询工资最高的前三个员工
32. 查询员工表按照 id 排序, 第 2 页的 5 条数据
33. 查询员工表按照 id 排序, 第 3 页的 4 条数据
34. 查询 3 号部门工资最低的员工姓名和工资
35. 查询工作不是人事的员工中工资降序第二页的 3 条数据
36. 查询没有领导的员工和 3 号部门的员工,工资降序取前三条
37. 查询 2 号部门的最高工资
38. 查询有领导的员工中工资在 1000 到 2000 之间的人数
39. 查询 3 号部门的工资总和
40. 查询程序员和销售的总人数
41. 查询 1 号部门有领导的员工的平均工资
42. 查询 1 号部门的最低工资和最高工资
43. 查询和销售相关的工作人数
44. 查询工资不是 1500 和 3000 的员工人数
45. 查询 1 号部门出现了哪几种工作
46. 查询名字包含精的员工数量
47. 查询和销售相关的工作一个月工资总和
48. 查询 2 号部门的最高工资和最低工资起别名
49. 查询每种工作的平均工资
50. 查询每个部门的最高工资
51. 查询每种工作的最低工资
52. 查询每个部门工资高于 2000 的人数
53. 查询每个部门有领导的员工人数
54. 查询 1 号部门每种工作的最低工资
55. 查询平均工资最高的部门 id 和平均工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

/*1-20*/
select name,sal from emp  where sal>=3000;
select name,job from emp where dept_id=1;
select name,job from emp where job !='程序员';
select name,job from emp where job <>'程序员';
select name,sal,job from emp where comm=300;
select * from emp where sal>2000 and dept_id=1;
select * from emp where sal=5000 or dept_id=3;
select name from emp where job in('ceo','项目经理');
select * from emp where sal in(3000,1500,5000);
select * from emp where sal not in(3000,1500,5000);
select * from emp where sal between 1000 and 2000;
select * from emp where sal not between 1000 and 2000;
select name,manager from emp where  manager is not null;
select name,job from emp where manager is null;
select distinct job from emp;
select distinct dept_id from emp;
select name from emp where name like '孙%';
select * from emp where name like '%精';
select * from emp where job like '_售%';
select * from emp where name like '%僧%' and sal>2000;
/*21-30*/
select * from  emp where job like '市%' and dept_id in(1,2);
select name,sal from emp order by sal;
select name,sal from emp order by sal desc;
select name,sal,dept_id from emp order by dept_id desc,sal;
select * from emp where sal>1500 and dept_id=3;
select * from emp where manager is null or dept_id=2;
select name,sal from emp where manager is not null  order by sal desc;
select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;
select name from emp where name like '%僧%' or name like '%精%';
select distinct job from emp where sal>2000;
/*31-40*/
select name,sal from emp order by sal desc limit 0,3;
select * from emp order by id limit 5,5;
select * from emp order by id limit 8,4;
select name,min(sal)  from emp where dept_id=3;
select name,sal  from emp where dept_id=3 order by sal limit 0,1;
select * from emp where job<>'人事' order by sal desc limit 3,3;
select * from emp where manager is null or dept_id=3 order by sal desc limit 0,3;
select name,max(sal) from emp where dept_id=2;
select count(*) from emp where manager is not null and sal between 1000 and 2000;
select sum(sal) from emp where dept_id=3;
select count(*) from emp where job in('程序员','销售');
/*41-50*/
select avg(sal) from emp where manager is not null and dept_id=1;
select max(sal),min(sal) from emp where dept_id=1;
select count(*) from emp where job like '%销售%';
select count(*) from emp where sal not in (1500,3000);
select distinct job from emp where dept_id=1;
select count(*) from emp where name like '%精%';
select sum(sal) from emp where job like '%销售%';
select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=2;
select job,avg(sal) from emp group by job;
select dept_id,max(sal) from emp group by dept_id;
/*51-55*/
select job,min(sal) from emp group by job;
select dept_id,count(*) from emp where sal>2000 group by dept_id;
select dept_id,count(*) from emp where manager is not null group by dept_id;
select job,min(sal) from emp group by job;
select avg(sal),dept_id from emp group by dept_id order by avg(sal) desc limit 0,1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61