MySQL基础

SQL

数据库基本概念

1. 数据data
2. 数据库database
   以一定格式存放、能够实现多用户共享，与应用程序彼此独立的数据集合
3. 数据库管理系统DBMS( database management system)
   用来定义和管理数据的软件。主流的有：Oracle、MySQL、SQL Server、DB2等
4. 数据库应用程序DBAS(Database Application System)
   在数据库管理系统基础上，使用其语法，开发的直接面对用户的应用程序，如学生管理系统、图书管理系统等等

目前主要的数据库类型是：关系型数据库（二维表）和非关系型数据库NOSQL

PHP中提供了一整套的MySQL函数，对MySQL进行了全方位的强力支持

SQL语言

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
数据操作语言（Data Manipulation Language，DML）：

• insert：增加数据
• update：修改数据
• delete：删除数据

数据定义语言（DDL）：DDL主要用针对是数据库对象（数据库、表table、索引index、视图view、触发器、存储过程procedure、函数function）进行创建、修改和删除操作。其主要包括：

• create：创建数据库对象
• alter：修改数据库对象
• drop：删除数据库对象

数据控制语言（DCL）：数据库权限

• grant：授予用户某种权限
• revoke：收回授予的某种权限

事务控制语言（Transaction Control Language，TCL）：用于数据库的事务管理。

• start transaction：开启事务
• commit：提交事务
• rollback：回滚事务
• set transaction：设置事务的属性

数据类型

1.整数类型

只有整型会自动扩充宽度，如int(4)，超过4位仍会显示。

2.浮点数类型

宽度不会自动扩充。 double(4,1) 总宽度4位，小数部分为1位。
3.字符串类型

char(size)：容纳固定长度的字符串

varchar(size)：容纳可变长的字符串（size为最大长度）

4.日期和时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rUZHXJRE-1646711846669)(C:\Users\cslin\OneDrive\typora\img\5.png)]

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：
(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。
(2) 如果TIMESTAMP类型的字段没有赋值或是NULL值，MySQL会将该字段赋值为系统当前的日期与时间。
(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。
(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

表

一行就是一条记录

单行注释##或--
多行注释/* */

建立一张用来存储学生信息的表
字段包含num、name、sex，age、enterdate，email信息
-- 创建数据库表：
create table t_student(
        num int(6), -- 6显示长度 
        name varchar(5), 
        sex char(1),
        age int,
        enterdate date,
        email varchar(15)
);
desc t_student;          		-- 查看表的结构：展示表的字段详细信息
select * from t_student; 		-- 查看表中数据
show create table t_student;	-- 查看建表语句：create table （）

复制一张表
create table t_2 as select * from t_1;
复制表的结构，值为null
create table t_3 as select * from t_1 where 1=2;
只要部分列，部分数据：
create table t_4 as
select sno,sname,age from t_student where sno = 2;
清空数据（表中数据置null）
delete from t_student;
truncate table t_student;

修改/删除数据(DML)

--字段依次为num、name、sex、age、enterdate、Email
insert into t_student values (1,'张三','男',18,'2022-5-8','123@126.com');
insert into t_student values (7,"李二",'男',18,now(),'123@126.com');
--添加部分字段数据
insert into t_student (num,name,enterdate) values (10,'李四','2023-7-5');
--修改数据
update t_student set sex='女' where sno=10;
--删除数据
delete from t_student where sno = 2;

注意事项

1. 时间的方式多样 ‘1256-12-23’ “1256/12/23” “1256.12.23”
2. 写入当前的时间 now() , sysdate() , CURRENT_DATE()

修改/删除表(DDL)

--增加一列
alter table t_student add score double(3,2);
alter table t_student add score double(3,2) first;
alter table t_student add score double(3,2) after sex;
--删除一列
alter table t_student drop score;
改字段类型
alter table t_student modify score float(3,1);
改字段名和类型
alter table t_student change score goal double(3,1);
删除表
drop table t_student;

表的完整性约束

防止不符合规范的数据录入数据库

约束从作用上分为两类：

• 列级约束：包含在列定义中，直接跟在该列的其他定义之后，空格隔开

auto_increment，not null，default只能用列级约束

• 表级约束：constraint约束表中一或多个字段，分为创建表时添加和创建表以后添加

create table t_student(
    no int(6) primary key auto_increment,     			-- 列级约束
    name varchar(5) not null, 
    sex char(1) default '男' check(sex='男'||sex='女'),
    age int(3) check(age>=18 and age<=50),
    enterdate date,
    constraint uq_stu email unique(email)				-- 表级约束
);
-- 创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (no);
alter table t_student modify no int(6) auto_increment;

约束条件	约束描述
primary key （主键约束	约束字段的值可唯一地标识对应的记录，不能为空
auto_increment（自增约束	约束字段的值自动递增，只有主键可以自增
default （默认值约束	约束字段的默认值
not null （非空约束	约束字段的值不能为空
unique （唯一约束	约束字段的值是唯一的
check （检查约束	限制某个字段的取值范围
foreign key （外键约束	约束表与表之间的关系

如果插入过程sql报错，可能主键就浪费了，后续插入的主键是不连号的，不过主键也不要求连号的

外键约束

外键是指表中某个字段依赖于另一张表中某个字段，被依赖的字段值必须唯一，被依赖的表为父表或主表。设置外键约束的表为子表或从表。

外键约束可以使两张表紧密结合，特别是针对修改或删除的级联操作时，会保证数据的完整性。

举例：

create table class(
	classnum int(2) PRIMARY KEY auto_increment,
	className varchar(5) not null
);
insert into class values(null,"初三九班"),(null,"初二六班");

create table stu(
	num int(2) PRIMARY KEY auto_increment,
	class int(2),
    constraint stu_classnum foreign key (class) references class (classnum)
);
insert into stu values(null,1),(null,2);

若在创建表以后添加外链，如下
alter table stu add CONSTRAINT _class foreign key(class) references class (classnum);
删除外键
alter table stu drop foreign key _class;

主表（父表）：班级表——班级编号——主键
从表（子表）：学生表——班级编号——外键

• cascade级联操作，操作主表同步影响从表，on update cascade on delete cascade

  alter table stu add CONSTRAINT avdv foreign key(class) references class (classnum) on update cascade on delete cascade;
  update t_class set cno = 5 where cno = 3;
  delete from t_class where cno = 5;
  

• 置空操作：on update set null on delete set null

alter table stu add CONSTRAINT avdv foreign key(class) references class (classnum) on update set null on delete set null;

• 混用：on update cascade on delete set null

单表查询

select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

-- 对emp表查询：
select * from emp; -- *代表所有数据
-- 显示部分列：
select empno,ename,sal from emp;
-- 显示部分行：where子句
select * from emp where sal > 2000;
-- 显示部分列，部分行：
select empno,ename,job,mgr from emp where sal > 2000;

-- 起别名(字段名和别名中间可以加上as)
select empno 员工编号,ename 姓名,sal 工资 from emp; 
select empno as '员工 编号',ename as "姓 名",sal as 工资 from emp;

-- 算术运算符：
select ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;
select ename,sal,comm,sal+ifnull(comm,0) from emp;   -- null+1=null，需要将null当作0

-- 去重操作：distinct
select distinct job from emp;
select distinct job,deptno from emp; -- 对后面的所有列组合去重，而不是单独的某一列去重

-- 排序：order by
select * from emp order by sal;     -- 默认升序asc
select * from emp order by sal desc; -- desc 降序
select * from emp order by sal asc ,deptno desc; -- sal升序，若sal相同，再按deptno降序排列

where子句

区分大小写
select * from emp where job = 'clerk';        -- 默认情况下不区分大小写 
select * from emp where binary job = 'clerk'; -- binary区分大小写

模糊查询：
-- 查询名字中带A的员工   %代表任意个字符 
select * from emp where ename like '%A%' ;
-- 一个“_”表示任意一个字符
select * from emp where ename like '__A%' ;   -- A前面有两个字符

集合查询
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');

运算符  and（&&），or（||），!=（<>），<=
select * from emp where sal > 1500 && sal < 3000; 
select * from emp where sal between 1500 and 3000; -- [1500,3000]

null的判断
select * from emp where comm is null;
select * from emp where comm is not null;

函数

1. 多行函数max，min，sum，avg，count（记录的条数）

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;

2. 单行函数

-- 字符串函数lower，upper，reverse，trim，length，substring(str,index,n)
select ename,lower(ename),upper(ename) from emp;
-- Math函数：abs，ceil,floor,PI(),POW(a,b),round(num,n)四舍五入保留n位小数
-- 日期函数：
curdate()返回当前日期		sysdate()返回函数执行时的日期和时间
curtime()返回当前时间		now()    返回当前日期和时间

流程函数

• if(condition,vt,vf)：如果condition为真，返回vt，否则返回vf

• ifnull(v1,v2)：如果v1不为null，返回v1；否则返回v2

• nullif(v1,v2)：如果v1=v2，返回null，否则返回v1

-- if相关
select ename,sal,if(sal>=2500,'A','B') as '等级' from emp;
select ename,sal,sal+ifnull(comm,0) from emp;
-- case相关
//case等值
select ename,
case job 
 when 'CLERK' then '店员'
 when 'SALESMAN'  then '销售'
 when 'MANAGER' then '经理'
 else '其他'
end '岗位',
from emp;
//case区间
select empno,ename,sal,
case 
 when sal<=1000 then '低'
 when sal<=2000 then '中'
 when sal<=3000 then '高'
 else 'D'
end '工资等级',
deptno from emp;

分组

group by（分组）—— having(分组后筛选)

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

-- 列出平均工资大于1200元的部门和工作搭配组合
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal) > 1200
order by deptno;

-- 统计[人数小于4的]部门的平均工资
select deptno,count(1),avg(sal)
from emp
group by deptno
having count(1) < 4

多表查询

• 自然连接

自动匹配所有同名列，且同名列只展示一次。但是有时候只需要匹配部分同名列，就不行了

select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e
natural join dept d;

• 内连接

  只能显示匹配成功的数据，匹配不到的被隐藏

-- 内连接——using           只能匹配同名列
select * 
from emp e
inner join dept d   -- inner可以不写
using(deptno);

-- 内连接——on	
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno);

• 外连接
  • 左外连接：左面的那个表的信息，即使不匹配也可以查看出效果
  • 右外连接：左面的那个表的信息，即使不匹配也可以查看出效果

select * 
from emp e
left outer join dept d			-- 左外连接，outer可以省略
on e.deptno = d.deptno;
/* right outer join dept d  */	-- 右外连接
/* full outer join */   -- 全外连接，mysql中不支持，在oracle中支持

select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union 					-- 并集union去重效率低，union all不去重 效率略高
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;

-- 多张表关联
select e.ename,e.sal,e.empno,e.deptno,d.dname,s.* 
from emp e
right outer join dept d
on e.deptno = d.deptno

inner join salgrade s 
on e.sal between s.losal and s.hisal
join……

• 自关联

select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

92语法中的多表查询：

-- 相当于99语法中的natural join 
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;

-- 自连接
select e1.ename,e1.job,e1.mgr ,e2.ename 
from emp e1,emp e2
where e1.mgr = e2.empno;

-- 多张表关联（两张以上）
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade 
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal;

子查询

一条SQL语句含有多个select，称为子查询。

//单行子查询
-- 举例： 查询和CLARK同一部门且比他工资低的雇员名字和工资。
select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK') 
      and 
      sal < (select sal from emp where ename = 'CLARK');
      
//多行子查询any，all
-- 举例 查询部门20中职务和部门10的雇员一样的雇员信息
select * from emp 
where deptno = 20 
and job in (select job from emp where deptno = 10)
/* and job = any(select job from emp where deptno = 10) */

-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select empno,ename,sal 
from emp 
where sal > all(select sal from emp where job = 'SALESMAN');
/* where sal > (select max(sal) from emp where job = 'SALESMAN'); */

• 不相关子查询：子查询可以独立运行，先运行子查询，再运行外查询。
• 相关子查询：子查询不能独立运行，先运行外查询，再运行子查询。

-- 例【2】查询本部门最高工资的员工  
-- 方法1：通过不相关子查询实现：
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)
-- 缺点：语句比较多，具体到底有多少个部分未知
-- 方法2： 相关子查询
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno;

视图

视图本质上就是select查询出来的虚拟表，只是显示，并不是存在这样的表，封装了select语句。

用法：
create or replace view myview01  -- 若已经存在这个名字的视图，就不再创建
as
select 语句                 -- 可以用上述的各种查询，单表/多表查询

举例：
create or replace view myview01  -- 若已经存在这个名字的视图，就不再创建
as
select empno,ename,job,deptno 
from emp
where deptno = 20
with check option; -- 保证插入或修改的数据满足where限制条件

存储过程

将一系列SQL命令封装，提高效率。当客户端请求数据，直接调用对应封装的SQL函数。

-- out 表示返回值；in表示参数，可以省略
create procedure mypro02(in name varchar(10),out num int(3))
begin
    if name is null or name = "" then
        select * from emp;
    else
        select * from emp where ename like concat('%',name,'%');
    end if;	
        select found_rows() into num;
end;

call mypro02(null,@num);   -- 调用存储过程：
select @num;               -- 查询返回值
drop procedure mypro01;    -- 删除存储过程：

事务

事务Transaction是用来维护数据库完整性的，保证一系列Mysql操作要么全部执行，要么全不执行。

举例：转账，一个账户减少，另一个账户增加

create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double
);
insert into account values (null,'丽丽',2000),(null,'小刚',2000);

update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
默认一个DML语句是一个事务，所以上面的操作执行了2个事务
必须让上面的两个操作控制在一个事务中：
-- 手动开启事务：
start transaction;
-- 手动回滚：刚才执行的操作全部取消：
rollback;
-- 手动提交：
commit;
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

事务并发问题

1. 脏读dirty read

当一个事务正在访问修改数据，但是还未提交commit到数据库，这时另外一个事务也进行访问这个数据。

2. 不可重复读unrepeatableread

一次事务内两次读到的数据不一样的情况（事务B commit之后）。

3. 幻读

数据记录条数发生改变。

事务隔离级别

隔离级别由低到高依次为：read uncommitted，read committed，repeatable read，serializable

• read uncommitted：脏读，不可重复读，幻读问题都可能出现

• read committed：解决脏读

• repeatable read：解决脏读和不可重复读

• serializable：解决所有问题

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read  
select @@transaction_isolation;  
-- 设置事务的隔离级别   （设置当前会话的隔离级别）
set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  
set session transaction isolation level serializable;