数据的增删改查
数据库创建
create database day3; # 创建练习数据库3
表创建
use day3; # 切换当前数据库为day3
create table t1(
id int unsigned primary key auto_increment, # id字段整数、无符号、自增、主键
name char(12) not null, # name名字字段固定长度12字符型、不能为空、可重复
gender enum('男','女') default '男', # gender性别字段单选“男”、“女”,默认“男”
hobby set('python','mysql','java') not null, # hobby爱好多选字段“python”、“mysql”、“java”
post char(10) not null, # post岗位字段固定长度10字符型,不能为空,可重复
salary int unsigned not null # salary月薪字段整数、无符号、不为空
);
增
- 一次增加单条数据:
insert into t1 value
(1,'张三','男','python,mysql','运行部',8800);
- 一次增加多条数据:
insert into t1 values
(2,'李四','男','python','技术部',8000),
(3,'王五','男','java','技术部',8000);
- 指定字段添加数据:
insert into t1
(name,hobby,post,salary) values
('赵六','mysql','运行部',7800),
('钱大','java,mysql','技术部',9000),
('钱二','java,mysql','技术部',8900),
('钱三','java,mysql','技术部',8800);
- 从其它表中导入数据:
create table t2(
id int,
name char(12)
); # 创建新表
insert into t2(id,name)
select id,username
from t1; # 把t1的数据导入t2
删
- 清空表但保留自增offset(偏移量)值:
delete from t2;
- 快速清空表且不保留自增offset值:
truncate table t2;
- 删除指定记录:
delete from t1
where id=7; # 筛选id=2的记录
删除指定数据必须带条件,delete from 表 where 条件;
建议先select查询数据,确认删除条件无误后再执行删除,切不可直接删除。
改
update t1
set salary=8888
where id=4; # 修改id为4的员工月薪为7888
update t1
set salary=8888,hobby='java,mysql'
where id=3; # 修改id为4的员工爱好为java和mysql,月薪为8888
查
select * from 表名; # 查全部数据
select id,name,gender
from t1; # 查指定字段
字段重命名
方式一:
select id 序号,name 姓名
from t1; # 字段空格新名字即表示对该字段重命名
方式二:
select id as 序号,name as 姓名
from t1; # 字段空格新名字即表示对该字段重命名
查询去重
select distinct gender
from t1; # distinct关键字表示查询结果去重
函数
select concat('ab','bc','cd'); # 字符串拼接函数,显示结果是abbccd
select concat(name,':',hobby)
from t1; # 拼接显示多个字段的内容
select concat_ws('|','张三','男','18岁'); # 指定|作为分隔符拼接多个字段
四则运算
select name 姓名,salary*12 年薪
from t1; # 对salary月薪乘以12计算年薪
条件判断
select(
case
when name = '张三' then
concat(name,'-副总监')
when name = '赵六' then
concat(name,'-总监')
else
concat(name, '-程序员')
end
) as new_name
from t1;
case语句块内可以对内容进行判断处理。case是条件判断语句块开始,end是条件判断语句块结束;when 后面跟判断条件,then后面接处理内容;as是给字段重命名
查表练习
1 查出所有员工的名字,薪资,格式为
<名字:name> <薪资:3000>
select
concat('<名字:',name,'>') 名字,
concat('<薪资:',salary,'>') 薪资
from t1;
2 查出所有的岗位(去掉重复)
select distinct post
from t1;
3 查出所有员工名字,以及他们的年薪,年薪的字段名为“年薪”
select
name 名字,
salary*12 年薪
from t1;
where条件
where可以使用的条件:
- 比较运算符<、>、=、<=、>=、!=查询薪资>8800的员工姓名
select name
from t1
where salary>8800; # 查询薪水大于8800的
- 范围between and,范围包头包尾
select *
from t1
where salary between 8900 and 10000; # 查询薪水在8900和1万之间的(含)
- in
select *
from t1
where salary in (8000,9000); # 查询薪水正好是8千和9千的
- 模糊匹配like:%表示任意长度的任意内容,_表示一个字符长度的任意内容,%和_只有在like字句内才是通配符。
select *
from t1
where name like '钱%'; # 查询名字是钱开头的
select *
from t1
where hobby like '%py%'; # 查询爱好包含py的
select *
from t1
where salary like '%8'; # 查询薪资以8结尾的
- regexp
注意:在sql中需要写双\转移符才生效,如想用\d匹配数字时必须写成\d才有效。
select *
from t1
where salary regexp '^9'; # 查询薪水是以数字9开头的
select *
from t1
where salary regexp '0#39;; # 查询薪水是以数字0结尾的
select *
from t1
where salary regexp '^88\\d\\d'; # 查询薪水是88开头的4位数的
- 逻辑运算,not优先级最高,or优先级最低。
select *
from t1
where salary not in (8000,9000); # 查询薪水不是8000和9000的
- and
select *
from t1
where salary not in (8000,9000) and post='技术部'; # 查询薪水不是8千和9千且部门是技术部的
- or
select *
from t1
where salary=8888 or post='运行部'; # 查询薪资是8888或部门是运行部的
group by分组
分组是查询表中指定字段不重复的值。例如:
select post
from t1
group by post; # 查看t1表post字段有几种值
输出:
运行部
技术部
注意,下面的语句会报错:
select *
from t1
group by post; # 查看t1表post字段有几种值
因为select的字段包含了group by里没有的字段。使用group by时select的字段只能是group by中有的字段。
再如,对hobby和post复合分组,此时group by包含hobby和post两个字段,那么select也可以使用hobby和post这两个字段:
select hobby,post
from t1
group by hobby,post; # 同时对hobby和post两个字段进行分组
聚合函数
聚合是搭配分组使用的,常见聚合函数及使用方式如下:
- count,求个数
select post,count(id)
from t1
group by post; # 按post字段统计不同post分别有多少人
- max,求最大值
select post,max(salary)
from t1
group by post; # 显示所有部门的最高工资
- avg,求平均值
select post,avg(salary)
from t1
group by post; # 显示所有部门的平均工资
- min,求最小值
select post,min(salary)
from t1
group by post; # 显示所有部门的最低工资
- sum,求和
select post,sum(salary)
from t1
group by post; # 显示各部门的工资和
- concat,聚合显示
select post,group_concat(name)
from t1
group by post; # 显示不同部门的所有人名
注意:使用分组聚合统计时,group by根据指定字段分组,可以对该分组求个数、最大值、最小值、平均值、求和,但这个值只是和分组内容对应,和其它字段不对应。
having筛选
having中可以使用聚合函数,在where中不行。having适合筛选符合条件的一组数据,而不是某一行数据。
例如,找出平均工资大于8800的部门:
select post,avg(salary)
from t1
group by post
having avg(salary)>8800;
order by排序
指定查询结果按指定字段排序,默认是升序asc,降序desc。
按工资从低到高排序,默认从小到大排序:
select *
from t1
order by salary;
按工资从高到低排序,desc指定从大到小排:
select *
from t1
order by salary desc;
limit限制
limit m,n:从m+1开始,取n个值。若只写1个数时该数被当成n且m默认为0。
limit n offsec m:从m项开始,取n个值。
找出工资最高的人:
select *
from t1
order by salary desc
limit 1;
找出工资最高的前三人:
select *
from t1
order by salary desc
limit 3;
找出工资第三高的那个人:
有两种写法:
select *
from t1
order by salary desc
limit 2,1; # 第一个数是表示从第三名开始取,第二个数表示次数
select *
from t1
order by salary desc
limit 1 offset 2; # 1表示取一次,2表示从第三名开始取。