数据库的基本操作
- 约束条件
- 整型中括号中得数字的作用
- 清空表的两种方式
- 补充一些其他的SQL语句
- 查询,分组(数据准备)
- 查询关键字之where
- 查询关键字之group by分组(前提)
- 查询关键字之group by分组
- 分组补充函数
- 关键字之having过滤
- 关键字之distinct去重
- 关键字之order by排序
- 关键字之limit分页
- 关键字之regexp正则
约束条件
default#默认值
create table t2(id int,name varchar(32) default 'kevin');
zerofill#0填充
create table 表名(id int zerofill);
unsigned#无符号
create table t3(id int unsigned);
not null # 非空
create table t5 (id int, name varchar(32) not null);
unique # 唯一
单列唯一
create table t6 (id int, name varchar(32) unique);
多列唯一
create table t7 (
id int,
ip varchar(32),
port varchar(32),
unique(ip, port)
);
主键(primary key)
主键单纯从约束上来看,它相当于是非空且唯一
create table t8 (id int primary key);
InnoDB存储引擎规定每一张表都要有一个主键,但是,我之前创建的表都没有指定主键, 表是怎么创建成功的?
是因为InnoDB存储引擎内部有一个隐藏的主键,这个主键我们看不到,它也不能够加快查询速度,仅仅是为了帮助我们把表创建成功. 所以,以后我们创建表的时候都主动的创建一个主键,我们自己创建的主键能够加快查询速度,因为是一个索引.
一般情况下,主键应该创建哪个字段? 大多都给id字段加了,所以,每一张表都要有一个id字段,并且一张表中不只是有一个主键,可以有多个主键,但是,大多数情况下,都只有一个
主键一般都给id aid sid uid pid ..
auto_incrment
# 自增:每一次主动比上一次加1
"""一般情况下,它配合主键使用"""
create table t9 (
id int primary key auto_increment,
name varchar(32)
);
整型中括号中得数字的作用
id int(10) # 数字不代表的是范围
name varchar(32) # 数字代表的就是存储的范围
create table t3(id int(9) zerofill);
insert into t3 values(9);
清空表的两种方式
1. delete from t; # 不会重置id值
2. truncate t9; # 清空表、重置id值
"""truncate:建议使用truncate,使用这个,万一你清空错了,还有机会恢复"""
mysql它有很多个日志文件,binlog日志-----》可以恢复数据,记录了你所有的SQL语句
补充一些其他的SQL语句
-
修改表名
ALTER TABLE 表名
RENAME 新表名;
-
增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
-
删除字段
ALTER TABLE 表名
DROP 字段名;
-
修改字段
# modify只能改字段数据类型完整约束,不能改字段名,但是change可以!
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
查询,分组(数据准备)
# 数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age smallint(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之where
在你刚开始接触mysql查询的时候,建议你按照查询的优先级顺序拼写出你的sql语句
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%se';
where筛选功能
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候,建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号,只能用is
select name,post from emp where post_comment = NULL; # 查询为空!
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
查询关键字之group by分组(前提)
针对5.6需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
# 重新链接客户端
select * from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
查询关键字之group by分组
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
# 数据分组应用场景:每个部门的平均薪资,男女比例等
# 1.按部门分组
select * from emp group by post; # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post; # 验证
"""
设置sql_mode为only_full_group_by,意味着以后但凡分组,只能取到分组的依据,
不应该在去取组里面的单个元素的值,那样的话分组就没有意义了,因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
# 强调:只要分组了,就不能够再“直接”查找到单个数据信息了,只能获取到组名
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
统计的时候只要是非空字段 效果都是一致的
这里显示age,salary,id最后演示特殊情况post_comment
分组补充函数
# group_concat 分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post;
select post,group_concat(name,'|',sex, '|', gender) from emp group by post;
select post,group_concat(distinct name) from emp group by post;
select post,group_concat(distinct name separator '%') from emp group by post;
# concat 不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;
关键字之having过滤
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
# 先筛选出年龄在30岁以上的
select * from emp where age > 30;
# 在进行分组,按照部门分组
select avg(salary) as avg_salary from emp where age > 30 group by post;
# 保留平均薪资大于10000的部门
select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;
关键字之distinct去重
distinct:去重
"""带主键的数据去重有没有意义? 没有,主键本身就是唯一的"""
select distinct id,age from emp;
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排
#先按照age降序排,在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
'''多字段排序,如果想让后面的字段排序生效,前提:前面的排序字段必须一样'''
# 统计各部门年龄在20岁以上的员工平均工资,并且保留平均工资大于1000的部门,然后对平均工资进行排序
# 20岁以上的员工
select * from emp where age > 20;
# 各部门的平均薪资
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000;
#
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;
关键字之limit分页
# 限制展示条数
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置,第二个参数表示的是条数,不是索引位置
select * from emp limit 5,5;
关键字之regexp正则
select * from emp where name regexp '^j.*(n|y)$';
![数据库的基本操作](https://picdm.sunbangyan.cn/2023/10/24/31ef884eab559b69c77d1be0bde9e744.png)