数据库知识

一、SQL-DDL

1.数据库操作

1. 查询所有数据库

show databases;

2. 创建

create database [if not exists] test1;
create database test1 default charset utf8mb4;

3. 删除

drop database [if exists] test1;

4. 使用数据库

use test1;

5. 查询当前数据库

select database();

2.表操作

• 表操作-查询

1. 查询当前数据库所有表

show tables;

2. 查询表结构

desc user;

3. 查询指定表

show create table user;

• 表操作-创建

create table user(
	id int comment '编号',
	name varchar(50) comment '姓名',
	gender char(1) comment '性别'	
)comment '用户表';

• 表操作-修改

1. 添加字段

alter table user add age int comment '年龄';

2. 修改数据类型

alter table 表名 change 旧字段名 新字段名 类型(长度) comment '注释';
alter table user change name nickname varchar(30) comment '昵称';

3. 修改表名

alter table user rename to user1;

4. 删除字段

alter table user drop nickname;

5. 删除表

drop table [if exists] user1;

二、SQL-DML

1.添加数据

1. 给指定字段添加数据

insert into user(id,name,age,gender) value(1,'王某',18,'男');

2. 给全部字段添加数据

insert into user values(2,'李某',18,'男');

3. 批量添加数据

insert into user values(3,'张某',18,'男'),(4,'李某',18,'女'),(5,'王某',18,'女');

2.修改数据

update user set name = '王某某' where id = 1;
//修改编号为1的用户的姓名和年龄
update user set name = '张某', age = 30 where id = 1;
//将整张表的年龄修改为18
update user set age = 18; 

3.删除数据

delete from user where gender = '女';
//删除整张表中所有数据
delete from user;

三、SQL-DQL

1.基本查询

1. 查询多个字段

select id,name,age from user;
//查询所有字段
select * from user;

2. 设置别名

//查询员工姓名，通过as起别名
select name as '员工姓名' from user;

3. 去除重复记录

//查询员工姓名（去重distinct）
select distinct name from user;

2.条件查询

select * from user where age <= 18;
//查询没有地址的员工信息
select * from user where addr is null;
//查询有地址的员工信息
select * from user where addr is not null;
//查询年龄不等于18 的员工信息
select * from user where age != 18;
//查询15 - 20 岁之间的员工信息
select * from user where age >= 15 and age <= 20;
select * from user where age between 15 and 20;
//查询性别为男且年龄小于20
select * from user where gender = '男' and age < 20;
//查询年龄为20或30或40
select * from user where age = 20 or age = 30 or age = 40;
select * from user where age in(20, 30, 40);
//查询姓名为两个字的员工信息( _匹配单个字符 )
select * from user where name like '_ _';
//查询身份证号最后一位是x的员工(%匹配任意个字符)
select * from user where idcard like '%x';

3.聚合查询

//统计整张表的员工数量
select count(*) from user;
//统计整张表员工的平均年龄
select avg(age) from user;
//统计整张表员工的最大(最小min())年龄
select max(age) from user;
//统计西安地区员工的年龄之和
select sum(age) from user where addr = '西安';

4.分组查询

where是分组之前进行过滤，而having是分组之后对结果进行过滤。
执行顺序：where > 聚合函数 > having

//根据性别分组，统计男员工 和 女员工的数量 
select gender, count(*) from user group by gender;
//根据性别分组，统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from user group by gender;
//查询年龄小于45的员工，并根据地址进行分组，获取员工数量大于等于3的工作地址
select addr,count(*) from user where age < 45 group by addr having count(*) <= 3;

5.排序查询

//根据年龄对员工进行升序(降序desc)排序
select * from user order by age asc;
//根据年龄对员工进行升序排序，年龄相同，再按照入职时间进行降序排序(entrydata desc);
select * from user order by age asc, entrydata desc;

6.分页查询

select 字段列表 from 表名 limit

//查询第一页员工数据，每页展示十条记录
select * from user limit 0, 10;
//查询第二页员工数据，每页展示十条记录
select * from user limit 10, 10;

7.执行顺序

	select 字段列表  
	from 表名  		//第一步执行 
	where 条件列表
	group by 分组字段列表
	having 分组后条件列表
	//执行完having后执行select 然后再按顺序执行下面两条语句
	order by 排序字段列表
	limit 分页参数;

三、SQL-DCL

1.管理用户

1. 查询用户

use mysql;
select * from user;
select user,host from user;

2. 创建用户

create user '用户名'@'主机名' identified by '密码';
//创建用户 itcast, 只能够在当前主机localhost访问,密码为123456。(若想在任意地址访问讲localhost改为%)
create user 'itcast'@'localhost' identified by '123456';

3. 修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

4. 删除用户

drop user'用户名'@'主机名';

2.权限控制

1. 查询权限

show grants for '用户名'@'主机名';

2. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
//授予全部权限
grant all on test.* to 'heima'@'%';

3. 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

四、约束

非空约束 not null,
唯一约束 unique
主键约束 primary key

create table user(
	//主键自增
	id int primary key auto_increment comment '主键',
	//非空唯一
	name varchar(10) not null unique comment '姓名',
	//检查该字段大于0小于100
	age int check(age > 0 && age < 100) comment '年龄',
	//状态默认为1
	status char(1) default '1' comment '状态',
	gender char(1) comment '性别',
	index idx_name(name)      //在name列添加了一个名为idx_name的索引
)comment '用户表';

外键约束
constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)；

create table dept(
	id int primary key comment 'ID',
	name varchar(40) not null comment '部门名称'
)comment '部门表';

create table emp（
	id int primary key comment 'ID',
	name varchar(40) not null comment '姓名',
	age int comment '年龄',
	dept_id int comment '部门ID'
）comment '员工';

//添加外键
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

五、多表查询

1.内连接

查询两张表交集的部分

1. 隐式内链接
   select 字段列表 from 表1,表2 where 条件;

//查询每个员工的姓名,及关联的部门的名称;
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

2. 显式内链接
   select 字段列表 from 表1 [inner] join 表2 on 条件;

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

2.外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行

1. 左外链接
   先当与查询表1(左表)的所有数据，包含表1和表2交集部分的数据；
   select 字段列名 from 表1 left [outer] join 表2 on 条件；

//查询emp表的所有数据，和对应的部门信息
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

2. 右外链接
   先当与查询表2(右表)的所有数据，包含表1和表2交集部分的数据；
   select 字段列名 from 表1 right [outer] join 表2 on 条件；

//查询dept表的所有数据，和对应的员工信息
select d.*, e.* from emp e right join dept d on e.dept_id = dept.id;

3.自连接

select 字段列名 from 表a 别名A join 表a 表B on 条件；
自连接查询，可以是内连接查询，也可以是外连接查询。

//查询员工及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id;
select a.name, b.name from emp a join emp b on a.managerid = b.id;
//查询所有员工emp 及其领导的名字emp,如果员工没有领导也需要查出来;
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

4.联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集；
select 字段列表 from 表A … union[all] select 字段列表 from 表B…；

//将薪资低于5000的员工 和 年龄大于 50 的员工全部查询出来
select * from emp where salary < 5000
union
select * from emp where age > 50;

5.子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询
select * from t1 where column1 = (select column1 from t2)；

1. 标量子查询
   子查询返回的结果是单个值

//查询'销售部'的所有员工信息  a.查询"销售部"部门ID  b.根据销售部部门ID，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

2. 列子查询
   子查询返沪的结果是一列(可以是多行)

//查询"销售部"和"市场部"的所有员工信息 a.查询销售部和市场部的部门ID b.根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
//查询比财务部所有人工资都高的员工信息
select * from emp where salary > all(select * from emp where dept_id = (select id from dept where name = '财务部'))
//查询比研发部其中任意一个人工资高的员工信息
select * from emp where salary > any(select * from emp where dept_id = (select id from dept where name = '研发部'))

3. 行子查询
   子查询返回的结果是一行(可以是多列)

//查询与"张无忌"的薪资及直属领导相同的员工信息; a. 查询"张无忌"的薪资及直属领导 b.查询与"张无忌"的薪资及直属领导相同的员工信息;
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = "张无忌");

4. 表子查询
   子查询返回的结果是多行多列

//查询与"周芷若","宋远桥"的职位和薪资相同的员工信息; a.查询"周芷若","宋远桥"的职位和薪资; b.查询与"周芷若","宋远桥"的职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job,salary from emp where name = '周芷若' or name = '宋远桥');
//查询入职日期是"2023-01-01"之后的员工信息，及其部门信息; a.入职时间是"2023-01-01"之后的员工信息 b.查询这部分员工，对应的部门;
select e.*,d.* from (select * from emp where entrydate > '2023-01-01') e left join dept d on e.dept_id = d.id;

六、事务

1. 事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

set @@autocommit = 0;  //设置手动提交事务
//start transaction  也是设置手动提交事务

//案例转账操作(张三给李四转账1000)
//1.查询张三账户余额
select * from account where name = '张三';
//2.将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

----若这里抛异常则进行rollback操作----

//3.将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

//提交事务  
commit;

//回滚事务
rollback;

2.事务的四大特性(ACID)

• 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性：数据可系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：事务一旦提交或回滚，他对数据库中的改变就是永久的。