数据库 Database
@[toc]目录
#1 , 创建表 : create table 表名(字段名 字段类型(字段长度),字段2,字段3)
#1，新增: insert into 表名 values ("字段1的值"，"字段2的值"，...)
#2，删除数据: delete from 表名 (可加限定条件where)
#3，修改: update 表名 set 字段名=字段的新值
#1.自增(increment)、非空(not null)、唯一(unique)
distinct where like null between...and limit order by
#2.5 between and / limit / order by
#一、数据库的常见操作
#1，查询 : show databases
#2，新增 : create database 库名
#3，删除 : drop database 库名
#二、表的常见操作
#1 , 创建表 : create table 表名(字段名 字段类型(字段长度),字段2,字段3)
create table abc(id int(3),
name varchar(10),
age int(3)
);
show tables;
#2，删除表 : drop table 表名
#3，修改表 :alter
alter table tb_door add colum addr varchar(100);#4，查看表 : show tables;
#5，描述表 : desc tb_door;
#三、表记录的常见操作
#1，新增: insert into 表名 values ("字段1的值"，"字段2的值"，...)
#2，删除数据: delete from 表名 (可加限定条件where)
#3，修改: update 表名 set 字段名=字段的新值
update tb_door set addr = "shanghai";#4，查询: select 字段名 from 表名
select id,name from tb_door ;#四、字段约束
#1.自增(increment)、非空(not null)、唯一(unique)
#1，没有约束,id可重复,可以是null
create table a (id int);
#2，主键约束,id的值不能重复,不能是null
create table b (id int primary key);
#3，主键约束自增,id不能重复,不null,且自增
create table c (id int primary key auto_increment);
#4，没有非空约束 ,字段的值可以为空
create table d (name varchar(10));
#5，有非空约束 , 字段值不能为空
create table e (name varchar(10) not null);
#6，不用唯一约束 ,字段值可重复
create table f (name varchar(10));
#7，有唯一约束 ,字段的值不能重复
create table g (name varchar(10) unique);#五、Mysql的基础函数
#1，常见函数
lower–全转小写
upper–全转大写
length–求长度
concat–拼接串
substr–截取字符串
replace–替换字符串
ifnull–对null元素的替换
round–对小数四舍五入
ceil–对小数向上取整
floor–对小数向下取整
now–获取当前的年月日时分秒
year–获取日期里的年
month–获取日期里的月
day–获取日期里的日
hour–获取日期里的时
minute–获取日期里的分
second–获取日期里的秒
转义字符 ’
#2，测试:
#1,全转大写,小写
select dname ,lower(dname),upper(dname) from dept
#2,求长度,字母数字一字节,汉字三字节
select dname ,length(dname),loc ,length(loc) from dept
#3,截取字符串
select dname ,substr(dname,2,5) from dept #从第二个字符开始,截取出5个
#4,拼接字符串
select dname ,concat(dame,'hello') from dept #在原来的值后面拼接hello
#5,替换字符串
select dname ,repalce(dname,'a','666') from dept #把a都换成666
#6,ifnull 如果是null就替换
select comm,ifnull (comm,0) from emp
#7,round 四舍五入 & ceil向上取整 & floor向下取整
select comm, round(comm),ceil(comm),floor(comm) from emp
#8,now & year年 & month月 & day日 & hour时 & minute分 & second秒
select now() #2021-06-29 11:18:21
select now(),hour(now()),minute(now()),second(now())
#9,转义字符,SQL中包含着一些特殊字符.需要转义 \
select 'xi\'an' == select "xi'an"#六、条件查询
#1、概述
distinct where like null between...and limit order by
#2、distinct where
#2.1 查询部门地址: (去掉重复结果)
select distinct loc from dept#2.2 where 过滤查询
select * from dept where deptno =1
select * from dept where loc = "二区"
select * from dept where deptno =1 and loc ="二区"
select * from dept where loc ="一区" or deptno =3#2.3 like 查询
select * from dept where dname like "%o%" # 包含o的
select * from emp where name like "_悟%" # _表示一个字符 %多个字符
select * from dept where dname like "%ch" #ch结尾#2.4 null
select * from emp where comm is not null #奖金不为空的
select * ,sal+ifnull(comm,0) from emp #如果奖金为null替换成0#2.5 between and / limit / order by
select * from emp where sal>5000 and sal<10000
select * from emp where sal between 5000 and 10000 #这个包含5000 10000
#2.order by 排序 升序(默认就是升序) 、降序
select * from emp order by sal asc #升序
select * from emo order by sal desc #降序
#2.6 分页查询 limit
格式: limit x,y ---> x为跳过的条数 , y为请求的条数 (也表示每页条数)
跳过的条数 = (请求的页数 - 1) * y (请求的条数)
举例: 第一页的5条数据 limit 0 ,5
第二页的5条数据 limit 5 ,5
第8页的9条数据 limit 63 ,9 limit [(第几页)-1] , 每页条数 ;
第3页的4条数据 limit 8 ,4
#七、聚合函数
#1. 平均值 avg(字段名)
#2. 最大值 max(字段名)
#3. 最小值 min(字段名)
#4. 求和 sum(字段名)
#5. 计数 count(*)
#查询1号部门的平均工资
select avg(sal) from emp where deptid =1;
#查询程序员的最高工资
select max(sal) from emp where job ="程序员"
#查询2号部门的最低工资
select min(sal) from emp where deptid =2
#查询3号部门的工资总和
select sum(sal) from emp where deptid =3
#查询工资高于2000的员工
select count(*) from emp where sal >2000#6.聚合函数相关练习题(表名:emp)
1.查询和销售相关工作的工资总和
2.查询1号部门工资高于1500的员工人数
3.查询1号部门中名字包含僧的员工的人数和平均工资 起别名
select sum(sal) from emp where job like "%销售%";
select count(*) from emp where deptid =1 and sal >1500;
select count(*) 人数 ,avg(sal) 平均工资 from emp where name like "%僧%" and deptid=1;#7、分组查询 group by
7.1 分组查询可以将某个字段相同的值划分为为一组,以组为单位进行统计查询
7.2 需求中出现每个xxx或每种xxx一般都使用分组查询
练习:
1.查询每个部门的平均工资
2. 查询每种工作的人数
3. 查询每个部门工资大于2000的人数
select avg(sal) from dept group by deptId;
select count(*) from dept group by job;
select count(*) from dept where sal >2000 group by deptId;#8、having
8.1 where后只能写普通字段的条件,不能写聚合函数
8.2 having是专门用来写分组查询时 聚合函数条件的
8.3 having必须和 group by 结合使用
8.4 having 要写在 group by 后面
练习:
1. 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门信息
2. 查询每种工作的人数要求人数大于1个,并且只查询1号部门和2号部门的员工, 按照人数降序排序
3. 查询高于2000工资人数最多的工作
select deptId,avg(sal) a from dept
where sal between 1000 and 3000 group by deptId having a >=2000;
select job, count(*) c from dept
where deptId in (1,2) group by job having c>1 order by c desc;
select job,count(*) c from dept
where sal >2000 group by job order by c desc limit 0,1;#七、事务
#1. 概述
1.1能保证多条SQL要么全成功,要么全失败.
1.2 四特性: ACID 原子性,一致性,隔离性,持久性
#2.事务的处理
#Mysql默认就开启了事务，但是每条SQL一个事务
begin;#开启事务
insert into dept values(null,'java开发部','北京');
insert into dept values(null,'php开发部','上海');
commit;#提交事务--会对数据库产生持久影响
#rollback;#回滚事务--不会对数据库产生持久影响
commit; 提交事务--会对数据库产生持久影响
rollback; 回滚事务--不会对数据库产生持久影响
#八、字段约束
#1.三约束 :外键约束、默认约束、检查约束
外键约束: 把两张表之间的关系,通过两个表的主键来表示
默认约束: 给指定的字段设置默认值
检查约束: 给字段增加检查条件,符合才能操作,不符合不能操作
create table h(
id int primary key auto_increment,
sex char(3) default '女' #default设置默认值，给指定的字段设置默认值
)
create table i(
id int primary key auto_increment,
age int,
check(age>0 and age<=150)#检查约束：给字段增加检查条件，符合才能操作，不符合不能操作
)
#外键约束:
1.子表的id来自于主表的id
2. 要想删除表,必须先把关联的子表全删掉才行
#外键约束
create table tb_user(
id int primary key auto_increment,
name varchar(20),
age int
)
create table tb_user_addr(
user_id int primary key auto_increment,
addr varchar(200) ,
#描述了两张表之间通过哪个字段关联着--外键约束
# 外键 (子表的字段) 参考 主表名称(主表的字段)
foreign key(user_id) references tb_user(id)
)
#九、索引:
#1.概述:
1.1 好处：给加完索引的列，提高查询效率
坏处：索引本质上就是一张表，如果表大，比较占内存
1.2 主键本身就有索引.
1.3 分类：
单值索引(一个索引只包含着一个列)
复合索引(一个索引包含着多个列)
唯一索引(一个索引只包含着一个列，但是要求列的值不能相同)
#1. 创建 普通索引：
create index 索引名 on 表名(字段名)
#经常被查询的字段建议加索引,
create index dname_index on dept(dname)
#2. 查看索引(观察三列：表名/字段名/索引名)
show index from dept
#3.创建 唯一索引：alter table 表名 add unique(字段名)
alter table dept add unique(loc) loc的值已经重复了，不能使用唯一索引
#4.添加复合索引
alter table emp add index fuhe_index(job,ename)
#十、多表联查
#1.方式:
1. 笛卡尔积: 通过都好隔开多个表,写在from后面.通过where 连接表之间的关联关系
2. 表连接: 通过join 连接多个表, 分为内连接和外连接.通过on 描述表的关系,
通过where添加过滤条件.
3. 子查询: 把上一次的查询结果, 作为条件继续使用
#多表联查1：笛卡尔积，把多个表用逗号隔开
#使用指定的字段deptno来描述两个表之间的关系
select * from dept , emp
#表名.字段名
where dept.deptno=emp.deptno
#要明确的表明使用哪个表里的哪个字段
and dept.deptno=1
#2.表与表之间如何建立联系?
-
通过一个单独的字段指向另外一张表的主键
-
一对多的关系, 在多的一端添加建立关系的字段
-
一对一的关系, 在任何表中添加建立关系的字段
-
多对多的关系, 需要一个单独的关系表里面两个建立关系的字段
#十一、关联查询
等值连接、内连接、外连接
#1.等值连接
1.1 查询的是两张表的交集数据
1.2 格式: select * from A,B where 关联关系 and 其他条件
练习:
1. 查询工资高于2000的每个员工姓名和对应的部门名
2. 查询程序员的工作地点
select e.name , d.name from emp e, dept d where e.deptId=d.id and sal>2000;
select distinct loc from emp e, dept d where e.deptId= d.id and job ="程序员";#2.内连接
2.1 查的是两个表的交集数据
2.2 格式: select * from A join B on 关联关系 where 条件
练习:
1. 查询工资高于2000的每个员工姓名和对应的部门名
2. 查询程序员的工作地点
3. 在神仙部上班的销售都有谁
select e.name, d.name from emp e join dept d on e.deptId=d.id where sal>2000;
select distinct loc from emp e join dept d on e.deptId=d.id where job = "程序员";
select e.name from emp e join dept d on e.deptId=d.id
where d.name="神仙" and job="销售";#3.外连接
3.1 查的是 一张表的全部和另一张的表的交集
3.2 格式: select * from A left/right join B on 关联关系 where 条件
3.3 left join : join左侧的表是全部 右侧是交集
练习:
1. 查询所有员工姓名和对应的部门名
2. 查询所有部门名对应的员工姓名和工资
select e.name ,d.name from emp e left join dept d on e.deptId=d.id;
select e.name ,sal ,d.name from emp e right join dept d on e.deptId=d.id;