数据库的学习
SQL通用语法
-
启动
net start mysql80
-
停止
net stop mysql80
分类 DDL,DML,DQL,DCL
DDL
[ ]里面的内容表示可以有也可以没有
-
数据库查询
-
查询所有数据库
show databases;
-
查询当前数据库
select database();
-
创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
-
删除
drop database [if exists] 数据库名;
-
使用
use 数据库名;
-
-
表查询
-
查询当前数据库所有表
show tables;
-
查询表结构
desc 表名;
-
查询指定表的建表语句
show create table 表名;
-
-
表创建
-
create 表名(
字段1 字段1类型 [comment 注释部分]
字段2 字段2类型 [comment 注释部分]
字段3 字段3类型 [comment 注释部分]
字段4 字段4类型 [comment 注释部分]
......
字段n 字段n类型 [comment 注释部分]
)[comment 注释部分]
-
-
数据类型
-
表修改
-
添加字段
alter tables 表名 add 字段名 类型(长度) [comment 注释][约束];
-
修改数据类型
aletr table 表名 modify 字段名 新数据类型(长度);
-
修改字段和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];
-
删除字段
alter table 表名 drop 字段名;
-
修改表名
alter table 表名 rename to 新表名;
-
删除表
drop table [if exists] 表名;
-
删除指定表并重新创建表
truncate table 表名;
-
DML
-
添加数据
-
给指定字段添加数据
insert into 表名(字段名1,字段名2,) values (值1,值2,...);
-
给全部字段添加数据
insert into 表名 values(值1,值2,...);
-
批量添加数据
insert into 表名(字段1,字段2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into 表名 values (值1,值2,...),(值1,值2,...),(值1,值2,...);
-
-
修改数据
-
update 表名 set 字段1 = 值1, 字段2 = 值2,...[where 条件];
-
修改语句的条件可以有,也可以没有,如果没有条件,则修改整张表的所有数据
-
-
-
删除数据
-
delete from 表名 [where 条件];
-
delete 语句条件可以有也可以没有,如果没有则删除整张表的数据
-
delete 语句不能删除某一字段的值(可以使用update)
-
-
DQL
-
查询
-
查询多个字段
select 字段1,字段2,字段3...from 表名;
-
select * from 表名;
-
设置别名
-
select 字段1[as 别名1],字段2[as 别名2]... from 表名;
-
-
去除重复记录
-
select distinct 字段列表 from 表名;
-
-
条件查询
-
select 字段列表 from where 条件列表;
-
-
条件
-
聚合函数
聚合函数将一列数据作为一个整体,进行纵向计算。
-
语法
select 聚合函数(字段列表) from 表名;
-
-
分组查询
-
select 字段列表 from [where 条件] group by 分组字段名 [having 分组过滤后体条件];
where——having的区别
-
执行时机不同:where是分组之前过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
-
判断条件不同:where不能对聚合函数进行判断,而having可以
-
执行顺序:where > 聚合函数 > having。
-
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意思
-
-
-
排序查询
-
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
排序方式
-
asc:升序(默认值)
-
desc:降序
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
-
-
-
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
-
注意事项
-
起始索引从0开始,起始索引= (查询页码-1)*每页显示记录数。
-
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是limit。
-
如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10。
-
-
DCL
-
管理用户
-
查询
use mysql;
select * from user;
-
创建
create user '用户名'@'主机名' identified by 密码;
-
修改密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-
删除
drop user '用户名'@'主机名';
-
主机名可以使用%通配。
-
这类SQL开发人员操作的比较少,主要是DBA ( Database Administrator 数据库管理员)使用。
-
-
-
权限控制
-
查询权限
show grants for '用户名'@'主机名';
-
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-
SQL函数
函数是指一段可以直接被另一段程序调用的程序或代码
用法
select 函数(参数);
字符串函数
数值函数
流程函数
日期函数
约束
约束是作用于表中字段上的规则,用于限制储存在表中的数据。
-
可以进行以下操作进行试验
create table user ( id int primary key auto_increment comment "主键", name varchar(10) not null unique comment "姓名", age int check(age > 0 && age <= 120) comment "年龄", status char(1) default '1' comment "状态", gender char(1) comment "性别" ) comment "用户表";
如果id字号想重置有以下方法:
删除data: DELETE FROM user WHERE 关键值1='字段1' OR 关键值1='字段1';
TRUNCATE TABLE user;(删除所有数据)
DELETE FROM user;(有外键)
重置id:ALTER TABLE user AUTO_INCREMENT = 1;
-
外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
-
准备父表数据
create table dept( id int auto_increment comment 'ID', name varchar(50) not null comment '部门名称', primary key (id) )comment = '部门表'; insert into dept (id, name) values (1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部'),(5, '总经办');
-
准备子表数据
create table emp( id int auto_increment comment 'ID', name varchar(50) not null comment '姓名', age int comment '年龄', job varchar(20) comment '职位', salary int comment '薪资', entrydate date comment '入职时间', managerid int comment '直属领导ID', dept_id int comment '部门ID', primary key (id) ) comment = '员工表'; dept_id是用来关联父表主键 insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id) values (1, '金庸', 66, '总裁', 28808, '2000-01-01', null, 5), (2, '张无忌', 28, '项目经理', 12500, '2005-12-05', 1, 1), (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1), (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
-
添加外键
语法:
add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);
添加:
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);
-
删除外键
语法;
alter table 表名 drop foreign key 外键名称;
删除:
alter table emp drop foreign key fk_emp_dept_id;
)
多表查询
概述:项目开发过程中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在以下各种联系:
一对多(多对一)
多对多
一对一
-
一对多(多对一)
-
实现两张表之间的关联,建立第三张表,中间的表有两个外键关联着两个主键
-
案例: # 创建学生信息表 create table studentlc ( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' ) comment '学生表'; # 插入信息 insert into studentlc (name, no) values ('黛绮丝', '2000100101'), ('谢逊', '2000100102'), ('殷天正', '2000100103'), ('韦一笑', '2000100104'); # 创建课程信息表 create table course ( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称' ) comment '课程表'; # 插入信息 insert into course (name) values ('Java'), ('PHP'), ('MySQL'), ('Hadoop'); # 创建关联表 create table student_course ( id int auto_increment primary key comment '主键', studentid int not null comment '学生ID', courseid int not null comment '课程ID', constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references studentlc (id) ) comment '学生课程中间表'; # 插入信息 insert into student_course (studentid, courseid) values (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 4);
-
一对一
在任意一方加入外键,关联另一方外键,并且设置外键唯一的(unique) ——实现方法和理解简单不多赘述
内连接
内连接查询的是两张表的交集的部分
-
隐式内连接
-
select 字段列表 from 表1,表2 where 条件...;
-
-
显示内连接
-
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
-
外连接
-
左外连接
-
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
-
-
右外连接
-
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
相当于查询表2(左表)的所有数据 包含 表1和表2交集部分的数据
-
自连接
自连接查询可以是内/外连接查询。
-
语法:
select 字段列表 from 表A 别名B on 条件...;
联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集,并且对查询的结果进行去重,union all则不去重。
对于此查询,查询的表的列数必须保持一致,字段类型也许保持一致。
-
语法:
select 字段列表 from 表A... union [all] select 字段列表 from 表B...;
子查询
概念:sql语句中嵌套select语句,称为嵌套语句,又称子查询
语法:
select * from t1 where column = (select coumn 1 from t2);
子查询外部语句的语句可以是insert / update /delete / select 的任何一个。
-
标量子查询(子查询结果为单个值)
-
常用的操作符:= <> > >= < <=
案例:
查询销售部的所有员工
a. 查询"销售部"部门ID SELECT id FROM dept WHERE name = '销售部';
b. 根据销售部部门ID,查询员工信息 SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
-
-
列子查询(子查询结果为一列(可多行))
-
常用操作符:in 、not in、any、some、all
案例:
查询 销售部和市场部的员工信息
a. 查询"销售部"和"市场部"的部门ID SELECT id FROM dept WHERE name = '销售部' OR name = '市场部';
``````b. 根据部门ID,查询员工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '销售部' OR name = '市场部');```
-
-
行子查询(子查询结果为一行(可多列))
-
操作符:= 、<> 、in、not in
案例:
查询与“张无忌”的薪资及直属领导相同的员工信息
a. 查询"张无忌"的薪资及直接领导 SELECT salary, managerid FROM emp WHERE name = '张无忌';
b. 查询与"张无忌"的薪资和直接上级领导相同的员工信息 SELECT * FROM emp WHERE (salary, managerid) IN (SELECT salary, managerid FROM emp WHERE name = '张无忌');
-
-
表子查询(子查询结果为多行多列)
-
操作符:in
案例:
查询“鹿杖客”,“宋远桥”的职位和薪资相同的员工的信息
-
a. 查询"鹿杖客"和"宋远桥"的职位和薪资 SELECT job, salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥';
b. 查询与"鹿杖客"和"宋远桥"的职位和薪资相同的员工信息 SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥');
事务
事务简介
-
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,这些操作要么同时成功,要么同时失败。
事务操作
-
数据准备
create table account( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', money int comment '余额' ) comment '账户表'; insert into account(id, name, money) values (null, '张三', 2000), (null, '李四', 2000);
-
转账操作
1.查询张三账户余额: select money from account where name = '张三';
2.将张三账户余额减去1000: update account set money = money - 1000 where name = '张三';
此处可以插入一个异常
3.将李四账户余额加上1000: update account set money = money + 1000 where name = '李四';
-
查看/设置事务提交方式:
-
select @@autocomit;
查看事务提交方式,1是自动,0是手动 -
set @@autocommit = 0;
-
-
提交事务
-
commit;
-
-
回滚事务
-
rollback;
-
-
开启事务
-
start transaction 或 begin;
-
事务四大特性
-
原子性(Atomicity):事务不可分割的最小操作单元,要么全部成功,要么全部失败。
-
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
-
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
-
持久性(Durability):事务一旦提交或滚回,它对数据库中的数据的改变就是永久的。
并发事务问题
-
脏读:一个事务读到另外一个事务还没有提交的数据。
-
不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
-
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”
事务隔离级别
事务隔离级别越高,数据越安全,但是性能越低。
-
查看事务隔离级别:
select @@transaction_isolation;
-
设置事务隔离级别:
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};