数据库笔记

DBMS Data Base Management System,数据库管理系统,俗称数据库软件
常见的DBMSMySQL: Oracle公司产品, 08年被Sun公司收购了,09年Sun公司被Oracle收购了,MySQL开源产品,市占率排名第一 Oracle: Oracle公司产品, 闭源产品,性能最强价格最贵, 市占率排名第二 SQLServer: 微软公司产品,闭源产品,市占率第三 DB2: IBM公司产品,闭源产品
SQLite:轻量级数据库

数据库相关的SQL

查询所有数据库
格式: show databases;

创建数据库

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

举例:

create database db1;

create database db2 charset=utf8;

create database db3 charset=gbk;

show databases;

查询数据库信息

格式: show create database 数据库名;

举例:

show create database db1;

show create database db2;

show create database db3;

删除数据库

格式: drop database 数据库名;

举例:

drop database db3;

drop database db2;

show databases;

使用数据库
使用完数据库之后再执行表相关或数据相关的SQL 否则会报错: No database selected
格式: use 数据库名;
举例:
```
use db1;
```

表相关

创建表

格式: create table 表名(字段1名类型,字段2名类型) charset=utf8/gbk;

举例:

create table person(name varchar(50),age int);

create table car(name varchar(50),type varchar(5),price int);

create table student(name varchar(50),age int,chinese int,math int,english int)charset=gbk;

查询所有表
格式: show tables;

查询表信息

格式: show create table 表名;

举例:

show create table person;

show create table student;

查询表字段信息
格式: desc 表名;
删除表
格式: drop table 表名;
举例:
```
drop table car;
show tables;
```
修改表名
格式: rename table 原名 to 新名;
添加表字段
最后面添加格式: alter table 表名 add 字段名类型;
最前面添加格式: alter table 表名 add 字段名类型 first;
在xxx字段的后面添加: alter table 表名 add 字段名类型 after xxx;
举例:
```
create database db4;
use db4;
create table teacher(name varchar(20));
alter table teacher add age int;
alter table teacher add id int first;
alter table teacher add salary int after name;
```
删除表字段
格式: alter table 表名 drop 字段名;
```
alter table teacher drop salary;
```
修改表字段
格式: alter table 表名 change 原名新名新类型;
```
alter table teacher change age salary int;
```

数据相关SQL

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

create database db5 charset=utf8;
use db5;
create table person(name varchar(50),age int);

插入数据

全表插入格式: insert into 表名 values(值1,值2);

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

举例:

insert into person values("tom",30);
insert into person(name) values('jerry');

批量插入:

insert into person values("liubei",20),("guanyu",18),("zhangfei",15);
insert into person(name) values('libai'),('liucangsong');

插入中文:

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

查询数据

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

举例:

select name from person;
select name,age from person;
select * from person;
select * from person where age>20;
select age from person where name='tom';
select name from person where age=15;

修改数据

格式: update 表名 set 字段名=值,字段名=值 where 条件;

举例:

update person set age=50 where name='libai';
update person set name='刘备' where age=20;

删除数据

格式: delete from 表名 where 条件;

举例:、

delete from person where name='刘德华';
delete from person where age<20;
delete from person where age is null;

主键约束 primary key

约束: 创建表时给表字段添加的限制条件

主键: 表示数据唯一性的字段称为主键

主键约束: 唯一且非空

举例:

create database day2db charset=utf8;
use day2db;
create table t1(id int primary key,name varchar(20));
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
insert into t1 values(2,'ccc');  报错:Duplicate entry '2' for key 'PRIMARY'
insert into t1 values(null,'ccc'); 报错: Column 'id' cannot be null

主键约束+自增 auto_increment

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

create table t2(id int primary key auto_increment,name varchar(20));
 insert into t2 values(null,'aaa');
 insert into t2 values(null,'bbb');
 insert into t2 values(10,'ccc');
 insert into t2 values(null,'ddd');
delete from t2 where id>=10;
 insert into t2 values(null,'eee');

与或非 and or not

and: 查询多个条件同时满足时使用

or : 查询多个条件满足一个条件时使用

not: 取反

between x and y 两者之间包含x和y

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

select * from emp where sal between 2000 and 3000;

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

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

in(x,y,z)

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

查询工资等于3000,1500和5000的员工信息

select * from emp where sal in(3000,1500,5000);

查询工作是程序员和销售的员工信息

select * from emp where job in('程序员','销售');

去重 distinct

查询1号部门中出现了哪几种不同的工作

select distinct job from emp where dept_id=1;

查询员工表中出现了哪几种不同的部门id

select distinct dept_id from emp;

模糊查询 like

_: 代表1个未知字符

%: 代表0或多个未知字符

举例:

以x开头 x%

以x结尾 %x

以x开头y结尾 x%y

包含x %x%

第二个字符是x _x%

第三个是x倒数第二个是y _x%y

查询姓孙的员工姓名

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

查询名字中包含僧的员工信息

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

查询名字以精结尾的员工姓名

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

查询工作中包含销售并且工资大于1500的员工信息

select * from emp where job like "%销售%" and sal>1500;

查询工作中第二个字是售的员工姓名和工作

select name,job from emp where job like "_售%";

查询1号和2号部门中工作以市开头的员工信息

select * from emp where dept_id in(1,2) 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 asc;

查询每个员工的姓名和工资,按照工资降序

select name,sal from emp order by sal desc;

查询工资高于2000的员工姓名和工资, 按照工资降序排序

select name,sal from emp where sal>2000 order by sal desc;

查询姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序排序

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

分页查询 limit

格式: limit 跳过的条数,请求的条数(每页的条数)

跳过的条数=(请求的页数-1)*每页的条数

举例:

第一页的5条数据 limit 0,5

第2页的5条数据 limit 5,5

查询所有员工id,姓名和工资,按照工资升序排序请求第一页的5条数据

select id,name,sal 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;

别名

select name as "名字" from emp;
select name "名字" from emp;
select name 名字 from emp;

聚合函数

可以对查询到的多条数据进行统计查询

平均值avg(字段名)

a. 查询1号部门的平均工资

select avg(sal) from emp where dept_id=1;

b. 查询销售的平均工资

select avg(sal) from emp where job='销售';

最大值max(字段名)

a. 查询最高工资

select max(sal) from emp;

最小值min(字段名)

a. 查询最低工资

select min(sal) from emp;

求和sum(字段名)

a. 查询程序员的工资总和

select sum(sal) from emp where job='程序员';

计数count(*)

a. 查询销售的数量

select count(*) from emp where job="销售";

聚合函数练习题

查询销售的平均工资

select avg(sal) from emp where job="销售";

查询程序员的最高工资

select max(sal) from emp where job="程序员";

查询名字包含精的员工人数

select count(*) from emp where name like "%精%";

查询和销售相关的工作一个月工资总和

select sum(sal) from emp where job like "%销售%";

查询2号部门的最高工资和最低工资起别名

select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=2;

分组查询 group by

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

查询每个部门的人数

select count(*) from emp where dept_id=1;
select count(*) from emp where dept_id=2;
select count(*) from emp where dept_id=3;
select dept_id,count(*) from emp group by dept_id;

查询每种工作的人数

 select job,count(*) from emp group by job;

查询每种工作的平均工资

select job,avg(sal) from emp group by job;

查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;

查询每个部门工资高于2000的人数

select dept_id,count(*) from emp where sal>2000 group by dept_id;

查询每种工作的最低工资

select job,min(sal) from emp group by job;

查询1号部门和2号部门的人数

select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;

查询平均工资最高的部门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;

having

where后面只能写普通字段的条件, 不能写聚合函数条件

having后面专门用来写聚合函数条件, 而且having要和group by 结合使用写在group by的后面

查询每个部门的平均工资,只查询出平均工资大于2000

select dept_id,avg(sal) from emp group by dept_id  having avg(sal)>2000;
select dept_id,avg(sal) a from emp group by dept_id  having a>2000;

查询每种工作的人数,只查询人数大于1的

select job,count(*) c from emp group by job having c>1;

查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.

select dept_id,sum(sal) s from emp 
where manager is not null group by dept_id having s>5400;

查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

select dept_id,avg(sal) a from emp 
where sal between 1000 and 3000 group by dept_id having a>=2000;

关联查询*(重要)

等值连接

格式: select 字段信息 from A,B where A.xxx=B.xxx(关联关系) and 其它条件

查询每个员工的姓名\工资和对应的部门名

select e.name,sal,d.name
from emp e,dept d
where e.dept_id=d.id;

查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc
from emp e,dept d
where e.dept_id=d.id and sal>2000;

查询程序员部门地址

select distinct loc
from emp e,dept d
where e.dept_id=d.id and job="程序员";

内连接

等值连接和内连接的作用一样,查询的都是两个表的"交集数据"(存在关系的数据)

格式: select 字段信息 from A join B on A.x=B.x where 其它条件

查询每个员工的姓名\工资和对应的部门名

select  e.name,sal,d.name
from emp e join dept d on e.dept_id=d.id;

查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc
from emp e join dept d on e.dept_id=d.id where sal>2000;

查询程序员的部门地址

select distinct loc
from emp e join dept d on e.dept_id=d.id where job="程序员";

外连接

外连接查询到的是一张表的全部和另外一张表的交集数据

格式: select 字段信息 from A left/right join B on A.x=B.x where 其它条件

insert into emp(name,sal) values("灭霸",5);

查询所有的员工姓名和对应的部门名

select  e.name,sal,d.name

from emp e left join dept d on e.dept_id=d.id;

查询所有部门名和对应的员工姓名

select d.name,e.name
from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

如果需要同时查询多张表的数据使用关联查询

如果查询到的是两张表的交集数据使用等值连接或内连接(推荐)

如果查询的是一张表的全部和另外一张表的交集则使用外连接

SQL语句分类

DDL: 数据定义语言包括数据库相关和表相关的SQL语句

DML: 数据操作语言,包括增删改查.

DQL: 数据查询语言,包括查询相关

TCL: 事务控制语言,和事务相关的SQL语句.

DCL:数据控制语言, 指用户相关和权限分配相关.

数据类型

整数: int和bigInt bigInt等效Java中的long

浮点数: double(m,d) m代表总长度 d代表小数长度 , 25.444 m=5 d=3

字符串:

char(m): 固定长度, m=5 存"abc" 占5个最大长度255

varchar(m): 可变长度,m=5 存"abc" 占3个最大长度65535 内容少的用varchar

text(m):可变长度最大长度65535 内容长的用text

时间类型:

date: 只能保存年月日

time: 只能保存时分秒

datetime: 年月日时分秒, 默认值为null, 最大值9999-12-31

timestamp(时间戳,通过保存距离1970年1月1日的毫秒数来实现时间记录的): 年月日时分秒, 默认值为当前系统时间 , 最大值2038-1-19

表设计面试题

2021年过年时小明在这些天都收到了许多亲戚\朋友还有同事的红包,也发出了一些红包,有的是微信,有的是支付宝也有现金,请参考下面的题目帮小明设计表格保存红包的信息

设计表 (至少包含一张流水表)
列出需要保存的所有信息
关系,红包类型,金额,时间,性别,名字
流水表
金额,红包类型,时间

create table trade(id int primary key auto_increment,money int,type varchar(10),time date,p_id int);

人物表

名字,性别,关系

create table person(id int primary key auto_increment,name varchar(20),gender varchar(1),rel varchar(10));

 刘德华 微信 收1000 2021-03-20   
杨幂 现金 收500 发50 2021-04-14   
马云 支付宝 收20000 发5 2021-03-11   
特朗普 微信 收2000  2021-05-18   
 貂蝉 微信 发20000 2021-07-22
 insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');
insert into trade values(null,1000,'微信','2021-03-20',1),
(null,1000,'微信','2021-03-20',1),
(null,500,'现金','2021-04-14',2),
(null,-50,'现金','2021-04-14',2),
(null,20000,'支付宝','2021-03-11',3),
(null,-5,'支付宝','2021-03-11',3),
(null,2000,'微信','2021-05-18',4),
(null,-20000,'微信','2021-07-22',5);

统计2021年2月15号到现在的所有红包收益

select sum(money) from trade where time>"2021-02-15";

查询2021年2月15号到现在金额大于100 所有女性亲戚的名字和金额

select name,money
from trade t join person p on t.p_id=p.id
where time>"2021-02-15" and gender='女' and rel='亲戚' 
and money not between -100 and 100;

查询三个平台(微信,支付宝,现金)分别收入的红包金额

select type,sum(money) from trade where money>0 group by type;

面试题:

表设计之权限管理

创建三张主表, 用户表 , 角色表, 权限表

create database db8 charset=utf8;
use db8;
create table user (id int primary key auto_increment,name varchar(20));
create table role(id int primary key auto_increment,name varchar(20));
create table module(id int primary key auto_increment,name varchar(20));

创建两张关系表用户角色关系表 , 角色权限关系表

   create table u_r(uid int,rid int);
   create table r_m(rid int,mid int);

	准备数据
	用户表:   刘德华   ,  杨幂  ,习大大
	角色表:  男游客   女会员    管理员  
	权限表: 男浏览,男发帖,女浏览,女发帖  
	用户角色关系:  刘德华->男游客和女会员   杨幂->女会员    习大大->管理员
	角色权限关系:  男游客->男浏览        女会员->女浏览,女发帖  管理员->所有
insert into user values(null,'刘德华'),(null,'杨幂'),(null,'习大大');
insert into role values(null,'男游客'),(null,'女会员'),(null,'管理员');
insert into module values(null,'男浏览'),(null,'男发帖'),(null,'女浏览'),(null,'女发帖');
insert into u_r values(1,1),(1,2),(2,2),(3,3);
insert into r_m values(1,1),(2,3),(2,4),(3,1),(3,2),(3,3),(3,4);

RBAC（Role-Based Access Control）权限模型

ams_admin：管理员表
ams_permission：权限表
ams_role：角色表
ams_admin_role：管理员与角色的关联表
ams_role_permission：角色与权限的关联表

五表联合查询


-- 
-- 用户表（admin）：Id、UserName、UserPwd
-- 角色表（Role）：Id、RoleName
-- 权限表（permission）：Id、MenuName
-- 用户角色表（Admin_Role）：Id、UserId、RoleId
-- 角色权限表（Role_permission）：Id、RoleId、MenuId
-- 最关键的地方是，某个用户登录时，如何查找该用户的菜单权限？其实一条语句即可搞定：
-- 假如用户的用户名为Arthur，则他的菜单权限查询如下：

-- Select p.Id,p.MenuName 
-- from 
-- permission p ,
-- admin a, 
-- Admin_Role ar,
-- Role_permission rp 
-- Where
--  p.Id = rp.MenuId 
-- and 
-- ar.RoleId = rp.RoleId and ar.UserId = a.Id and a.UserName = ‘Arthur’


Select p.id,p.name
from 
ams_permission p ,
ams_admin a, 
ams_admin_role ar,
ams_role_permission rp 
Where
 p.id = rp.permission_id 
and 
ar.role_id = rp.role_id and ar.admin_id = a.id and a.id = 1

权限管理之关联查询

查询刘德华拥有的角色

select r.name
from user u join u_r ur on u.id=ur.uid
join role r on ur.rid=r.id 
where u.name='刘德华';

查询女会员这个角色对应的用户都有谁

select u.name
from user u join u_r ur on u.id=ur.uid
 join role r on ur.rid=r.id 
where r.name='女会员';

查询女会员拥有哪些权限

select m.name
from role r join r_m rm on r.id=rm.rid 
join module m on m.id=rm.mid
where r.name='女会员';

查询刘德华拥有哪些权限

select  m.name
from user u join u_r ur on u.id=ur.uid 
join r_m rm on rm.rid=ur.rid
join module m on m.id=rm.mid
where u.name='刘德华';

查询习大大拥有哪些权限

select  m.name
from user u join u_r ur on u.id=ur.uid 
join r_m rm on rm.rid=ur.rid
join module m on m.id=rm.mid
where u.name='习大大';

查询女发帖这个权限有哪些用户拥有

select u.name
from user u join u_r ur on u.id=ur.uid 
join r_m rm on rm.rid=ur.rid
join module m on m.id=rm.mid
where m.name='女发帖';