MariaDB学习（二）-主键、数据类型、导入sql文件、distinct、null、比较运算符、逻辑运算符、in、between and、like、order by、limit、数值计算、别名、聚合函数

数据库相关SQL

查询所有 show databases;
创建 create database db1 charset=utf8/gbk;
查询数据库信息 show create database db1;
删除数据库 drop database db1;
使用数据库 use db1;

表相关SQL

创建 create table t1(name varchar(50),age int)charset=utf8/gbk;
查询所有表 show tables;
查询表信息 show create table t1;
表字段 desc t1;
删除表 drop table t1;
修改表名 rename table t1 to t2;
添加字段 alter table t1 add 字段名类型 first/after xxx;
删除字段 alter table t1 drop 字段名;
修改字段 alter table t1 change 原名新名新类型;

数据相关SQL

插入数据 insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2);
查询数据 select 字段信息 from t1 where 条件;
修改数据 update t1 set xxx=xxx,xxx=xxx where 条件;
删除数据 delete from t1 where 条件;

练习题

 1. 创建数据库db3 字符集utf8 并使用
 create database db3 charset=utf8;
 use db3;
 2. 建员工表emp字段:id,name,sal(工资),deptId(部门id)字符集utf8
 create table emp(id int,name varchar(50),sal int,deptId int)charset=utf8;
 3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
 create table dept(id int,name varchar(50),loc varchar(50))charset=utf8;
 4. 部门表插入以下数据: 1 神仙部 天庭       2 妖怪部  盘丝洞
 insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');
 5. 员工表插入一下数据:  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);
 6.查询工资6000以下的员工姓名和工资    
 select name,sal from emp where sal<6000;
 7. 修改神仙部的名字为取经部 
 update dept set name='取经部' where name='神仙部';
 8. 给员工添加奖金comm字段 
 alter table emp add comm int;
 9. 修改员工表中部门id为1的 奖金为500
 update emp set comm=500 where deptId=1;
 10. 把取经部的地址改成五台山
 update dept set loc='五台山' where name='取经部';
 11. 修改奖金字段为性别gender字段 类型为varchar
 alter table emp change comm gender varchar(5);
 12. 修改孙悟空和猪八戒性别为男  
 update emp set gender='男' where id<3;
 13. 删除男员工   14. 删除性别字段  15. 删除表  和 删除数据库
 delete from emp where gender='男';
 alter table emp drop gender;
 drop table emp;
 drop database db3;

主键约束 primary key

什么是主键: 表示数据唯一性的字段
什么是约束: 创建表时给表字段添加的限制条件
主键约束: 限制主键字段的值唯一且非空
格式:

create table t1(id int primary key,name varchar(50))charset=utf8;

insert into t1 values(1,'aaa');

insert into t1 values(1,'bbb'); //报错: 不能插入重复的数据

insert into t1 values(null,'ccc'); //报错: 不能为null

主键约束+自增

自增规则: 从历史最大值+1
格式:

create table t2(id int primary key auto_increment,name varchar(50));

insert into t2 values(null,'aaa'); id=1

insert into t2 values(null,'bbb'); id=2

insert into t2 values(10,'ccc'); id=10

insert into t2 values(null,'ddd'); id=11

delete from t2 where id>=10;

insert into t2 values(null,'eee'); id=12

数据类型

整数: int(m) 和 bigint(m) bigint等效java中的long m代表显示长度 m=5 存 18 查出来00018 用来补零需要结合zerofill关键字使用

create table t3(age int(5) zerofill);

insert into t3 values(18);

select * from t3;
浮点数: double(m,d)和float(m,d) m代表总长度 d代表小数长度 56.123 m=5 d=3 ,超高精度浮点数 decimal(m,d) 只有涉及到超高精度运算时使用

create table t4(salary double(5,3));

insert into t4 values(56.789);

insert into t4 values(34.56789);

insert into t4 values(345.678); //报错超出范围
字符串:
- char(m):固定长度 m=10 存abc 占10个字符长度好处:执行效率略高, 最大长度255
- varchar(m): 可变长度 m=10 存abc 占3个字符长度, 好处:节省空间, 最大长度65535 ,建议保存长度较小的数据时使用(低于255时使用)
- text(m): 可变长度最大长度65535 ,建议保存长度较大的数据时使用
日期:
- date: 只能保存年月日
- time: 只能保存时分秒
- datetime:年月日时分秒, 最大值9999-12-31, 默认值为null
- timestamp(时间戳,以举例1970年1月1日的毫秒数保存时间):年月日时分秒,最大值2038-1-19, 默认值为当前系统时间
- 举例:
  
  create table t5(t1 date,t2 time,t3 datetime,t4 timestamp);
  
  insert into t5 values('2020-11-20',null,null,null);
  
  insert into t5 values(null,'10:58:20','2019-10-20 10:20:30',null);

导入*.sql文件

通过以下指令: 格式: source 路径;

source e:/emp.sql;
导入完成后测试查询

show tables; 查询出两个表 emp和dept

select * from emp; 里面会有一堆数据

去重distinct

查询员工表中出现了哪几种不同的工作

select distinct job from emp;
查询员工表里面有哪几个部门id

select distinct deptId from emp;

is null 和 is not null

当查询字段的值为空值时不能用等号进行判断,使用is

查询没有上级领导的员工信息;

select * from emp where manager is null;
查询有上级领导的员工信息;

select * from emp where manager is not null;

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

查询工资大于等于3000的员工姓名和工资

select name,sal from emp where sal>=3000;
查询1号部门的员工姓名和工作

select name,job from emp where deptId=1;
查询不是程序员的员工姓名,工资和工作 (用到上面两种不等的写法)

select name,sal,job from emp where job!='程序员';

select name,sal,job from emp where job<>'程序员';
查询有奖金的员工姓名和奖金

select name,comm from emp where comm>0;

and / or / not 与或非

and 类似Java中的 &&
or 类似Java中的||
not 类似Java中的!

查询1号部门工资高于2000块钱的员工信息

select * from emp where deptId=1 and sal>2000;
查询是程序员或者工资等于5000的员工信息

select * from emp where job='程序员' or sal=5000;
查询出CEO和项目经理的名字

select name from emp where job='CEO' or job='项目经理';
查询出奖金为500并且是销售的员工信息 select * from emp where comm=500 and job='销售';

in关键字

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

查询出工资为3000,1500和5000的员工信息

select * from emp where sal=3000 or sal=1500 or sal=5000;

select * from emp where sal in(3000,1500,5000);
查询工资不是3000,1500和5000的员工信息

select * from emp where sal not in(3000,1500,5000);
查询1号和2号部门工资大于2000的员工信息

select * from emp where deptId in(1,2) and sal>2000;

between x and y

查询数据在两者之间使用 , 包含x和y

查询工资在2000到3000之间的员工信息

select * from emp where sal>=2000 and sal<=3000;

select * from emp where sal between 2000 and 3000;

查询工资在2000到3000之外的员工信息

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

综合练习题

 1. 查询3号部门中有上级领导的员工信息
 select * from emp where deptId=3 and manager is not null;
 2. 查询2号部门工资在2000-3000之间的员工姓名,工资和部门编号
 select name,sal,deptId from emp where deptId=2 and sal between 2000 and 3000;
 3. 查询1号部门工资为800和1600的员工信息
 select * from emp where deptId=1 and sal in(800,1600);
 4. 查询1号部门中出现了哪几种不同的工作 
 select distinct job from emp where deptId=1;
 5. 查询1号和2号部门工资低于1000的员工信息
 select * from emp where deptId in(1,2) and sal<1000;

模糊查询like

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

查询姓孙的员工信息

select * from emp where name like "孙%";

查询工作中第二个字是售的员工信息

select * from emp where job like "_售%";
查询名字中以精结尾的员工姓名

select name from emp where name like '%精';
查询名字中包含僧的员工并且工资高于2000的员工信息

select * from emp where name like '%僧%' and sal>2000;
查询1号和2号部门中工作以市开头的员工信息

select * from emp where deptId in(1,2) and job like '市%';
查询有领导的员工中是经理的员工姓名

select name from emp where manager is not null and job like '%经理%';

排序 order by

格式: order by 排序字段名 asc升序(默认)或desc降序

查询所有员工的姓名和工资并安装工资升序排序

select name,sal from emp order by sal;
查询所有员工的姓名和工资并安装工资降序排序

select name,sal from emp order by sal desc;
查询所有员工姓名,工资和部门编号 , 安装部门编号升序排序,如果部门编号一致则按照工资降序排序

select name,sal,deptId from emp order by deptId,sal desc;

分页查询limit

格式: limit 跳过的条数,请求的条数(每页的条数)
跳过的条数=(请求的页数-1)*每页的条数
举例:
- 查询第一页的10条数据 limit 0,10
- 查询第二页的10条数据 limit 10,10
- 查询第5页的10条数据 limit 40,10
- 查询第8页的5条数据 limit 35,5
- 查询第7页的9条数据 limit 54,9

工资升序排序查询前三名

select * from emp order by sal limit 0,3;
查询员工表中工资降序排序第二页的3条数据

select * from emp order by sal desc limit 3,3;
查询1号部门中工资最高的员工信息

select * from emp where deptId=1 order by sal desc limit 0,1;
查询销售相关工作里面赚钱最少的员工姓名和工资

select name,sal from emp where job like '%销售%'

order by sal limit 0,1;
按照工资降序排序查询工资高于1000的所有员工姓名和工资, 查询第三页的两条数据

select name,sal from emp where sal>1000 order by sal desc limit 4,2;

数值计算 + - * / %

查询每个员工的姓名,工资和年终奖(年终奖=5*月工资)

select name,sal,5*sal from emp ;
查询2号部门中的员工姓名,工资和涨薪5块钱之后的工资

select name,sal,sal+5 from emp where deptId=2;
让员工表中3号部门的员工每人涨薪5块钱

update emp set sal=sal+5 where deptId=3;

别名

select name as '姓名' from emp;

select name '姓名' from emp;

select name 姓名 from emp;

综合练习题

 1. 查询有领导并且工资在1000到3000之间的员工信息
 select * from emp where manager is not null and sal between 1000 and 3000;
 2. 查询名字中包含精或者工作中包含序的员工姓名和工作 
 select name,job from emp where name like '%精%' or job like '%序%';
 3. 查询1号部门和2号部门的员工信息工资升序，查询第3页的2条数据
 select * from emp where deptId in(1,2) order by sal limit 4,2;
 4. 查询上级领导编号是8工资最高的3个人的信息
 select * from emp where manager=8 order by sal desc limit 0,3;

聚合函数

可以对查询的多条数据进行统计查询
包括的统计方式有:
- 平均值 avg(字段名)
- 最大值 max(字段名)
- 最小值 min(字段名)
- 求和 sum(字段名)
- 计数 count(字段名或*)

平均值avg(字段名)
- 查询1号部门的平均工资
  
  select avg(sal) from emp where deptId=1;
最大值max(字段名)
- 查询程序员的最高工资
  
  select max(sal) from emp where job='程序员';
最小值min(字段名)
- 查询2号部门的最低工资
  
  select min(sal) from emp where deptId=2;
求和sum(字段名)
- 查询3号部门的工资总和
  
  select sum(sal) from emp where deptId=3;
计数count(字段名或*)
- 查询1号部门的人数
  
  select count(*) from emp where deptId=1;

综合练习题

 1. 查询和销售相关工作的工资总和
 select sum(sal) from emp where job like '%销售%';
 2. 查询1号部门工资高于1500的员工人数
 select count(*) from emp where deptId=1 and sal>1500;
 3. 查询1号部门中名字包含僧的员工的人数和平均工资 起别名
 select count(*) 人数,avg(sal) 平均工资 from emp where deptId=1 and name like '%僧%';
 4. 查询上级领导是唐僧的员工中的最高工资
 select max(sal) from emp where manager=4;

晚课作业: 7:30 公布答案

 1. 查询没有上级领导的员工id，姓名，工资
 select id,name,sal from emp where manager is null;
 2. 查询有奖金的员工姓名和奖金
 select name,comm from emp where comm>0;
 3. 查询名字中包含精的员工姓名和工资
 select name,sal from emp where name like '%精%';
 4. 查询名字中第二个字是八的员工信息
 select * from emp where name like '_八%';
 5. 查询1号部门工资大于2000的员工信息
 select * from emp where deptId=1 and sal>2000;
 6. 查询2号部门或者工资低于1500的员工信息
 select * from emp where deptId=2 or sal<1500;
 7. 查询工资为3000，1500，5000的员工信息按照工资升序排序
 select * from emp where sal in(3000,1500,5000) order by sal;
 8. 查询3号部门的工资总和
 select sum(sal) from emp where deptId=3;
 9. 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序
 select * from emp order by deptId,sal desc;
 10. 查询有领导的员工，按照工资升序排序，第3页的2条数据
 select * from emp where manager is not null 
 order by sal limit 4,2;
 11. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页的2条数据
 select name,sal from emp where sal>2000 order by sal limit 2,2;
 12. 查询和销售相关的工作的工资总和
 select sum(sal) from emp where job like '%销售%';
 13. 查询销售人数 
 select count(*) from emp where job like '%销售%';
 14. 查询1号部门中有领导的员工中的最高工资
 select max(sal) from emp where deptId=1 and manager is not null;
 15. 查询2号部门的最高工资和最低工资 起别名
 select max(sal) 最高工资, min(sal) 最低工资 from emp where deptId=2;
 16. 查询1号部门里面名字中包含空字的员工姓名
 select name from emp where deptId=1 and name like '%空%';

posted @ 2021-07-22 23:35 Coder_Cui 阅读(433) 评论(0) 编辑收藏举报

刷新页面返回顶部

越努力，越幸运，2022，小崔，加油！

MariaDB学习（二）-主键、数据类型、导入sql文件、distinct、null、比较运算符、逻辑运算符、in、between and、like、order by、limit、数值计算、别名、聚合函数

数据库相关SQL

表相关SQL

数据相关SQL

练习题

主键约束 primary key

主键约束+自增

数据类型

导入*.sql文件

去重distinct

is null 和 is not null

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

and / or / not 与或非

in关键字

between x and y

综合练习题

模糊查询like

排序 order by

分页查询limit

数值计算 + - * / %

别名

综合练习题

聚合函数

综合练习题

晚课作业: 7:30 公布答案

公告