数据库(MySQL)

最新推荐文章于 2022-07-10 20:55:58 发布

疯子♛

最新推荐文章于 2022-07-10 20:55:58 发布

阅读量216

点赞数

分类专栏： MySql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_44975742/article/details/119385158

版权

MySql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库

关系型数据库
SQL Server 微软
MySQL Oracle
Oracle Oracle
DB2 IBM
SQL语句
非关系型数据库NoSQL
关键词

DBMS
DBS
DBA:数据库管理员

数据表 table
记录行
字段列

每个数据库会有自己的方言

1) DDL(Data Definition Language)数据定义语言
	用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作语言
	用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询语言
	用来查询数据库中表的记录(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制语言(了解)
	用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

数据库的数据类型

java 数据库
字符串 varchar(50) 可变长度
char(50) 固定长度
数值 int
float
double
decimal
日期 date 年月日
time 时分秒
datetime 年月日时分秒

timestamp 可默认设置当前时间

类型	大小	范围（有符号）	范围（无符号）	用途
char(n)	n	0-255 bytes		定长字符串
varchar(n)	n	0-65536bytes		变长字符串
INT	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
decimal	对DECIMAL(M,D)，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值
date
time
datetime
timestamp

MySQL定义库基本语句

库

建库
```
create database tablename;
```
使用库
```
use tablename
```

表结构约束，定义基本表的语句

1.唯一 unique UK 值不能重复的，值可以为Null，可以多个值是null

create table tablename
(
colname1 varchar(20) primary key,
colname2 varchar(20) unique
);

2.非空 not null 该列值不能为空

create table tablename
(
colname1 varchar(20) primary key,
colname2 varchar(20) not null
);

3.主键 primary key PK 功能：是相当于唯一且非空，但是一个表只能设置一个主键的，但是这个主键不一定只有一列，可以由多列组成(复合主键)

create table tablename
(
-- 单列主键
colname1 varchar(20) primary key,
colname2 varchar(20),
colname3 varchar(20),
-- 创建多列主键
primary key (colname1,colname2)
);

4.外键 foreign key FK 外键名字可以不一样，但是类型必须一致（长度可不一致）

create table tablename1
(
colname11 varchar(20) primary key,
colname12 varchar(20),
colname13 varchar(20),
);

create table tablename2
(
colname21 varchar(20) primary key,
colname22 varchar(20),
colname23 varchar(20),
colname11 varchar(20),
-- 创表时加外键,fk_tablename1_tablename2(外键名fk_父表_子表)
constraint fk_tablename1_tablename2 foreign key colname11 references tablename1(colname11)
);
-- 创建表后添加外键
alter table tablename add constraint fk_tablename1_tablename2 foreign key colname11 references tablename1(colname11)

5.默认值 default (主键不能默认，因为它本身是不重复的且唯一)

create table tablename
(
colname1 varchar(20) primary key,
colname2 varchar(20) default '男'
-- 默认colname2 列为'男'
);

6.自动增长 auto_increment

create table tablename
(
colname int primary key auto_increment,
colname1 varchar(20) ,
colname2 varchar(20) default '男'
-- 默认colname2 列为'男'
);

操作表语句

建表

create table 表名(
字段名  字段类型  约束,
字段名  字段类型  约束,
字段名  字段类型  约束 
);

修改表名

alter table taboldname rename to newname;

修改表结构

3.1改列类型

alter table tabname modify colname coltype;

3.2改列名

alter table tabname change oldcolname newcolname coltype;

3.3添加列：如果表里已经有数据，增加的这一列只能允许Null。如果表里无数据，增加的这一列可以为Null，可以Not Null。

-- (在第一列|在xxx列之后) 
alter table tabname add newcolname coltype first | after xxx;
-- 或
alter table tablename
add colname colstype 约束;

3.4删除列：列被删除，整个列上的数据都将清空。如果该列正被其他列引用，则无法删除。

alter table tabname 
drop (column) colname;
-- column可选

3.5修改列的位置

alter table tabname modify colname coltype first|after xxx;

3.6修改列的数据类型：允许改小，但是可能会丢失数据。一般情况下改大。

alter table tablename
alter column colname 数据类型;

表记录的插入，修改与删除

4.1插入数据

insert into tablename values(value1,value2,...,value5);

--括号里必须包含所有不允许为Null的列
insert into tablename(sno,sname,ssex,sage,sdept) values(value1,value2,...,value5);

insert into tablename(sno) values(value1);

4.2修改数据

update table
set 做修改
where 条件是否满足

4.3删除数据

delete
from tablename
where 条件是否满足

删除表
```
drop table table1,table2,table3
```

操作数据语句

-- 表记录的插入，修改与删除
-- 1、插入数据
insert into 表名 
values(value1,value2,...,value5);

insert into 表名(sno,sname,ssex,sage,sdept) 
values(value1,value2,...,value5);

insert into 表名(sno) --括号里必须包含所有不允许为Null的列
values(value1);

-- 2、修改数据
update 表名
set 做修改
where 条件是否满足

-- 3、删除数据
delete
from 表
where 条件是否满足

查询select

运算符

比较：<**、<=、>、>=、=、<>等

确定范围：

Between A and B、Not Between A and B

**确定集合：**IN、NOT IN

**字符匹配：**LIKE、NOT LIKE

**空值：**IS NULL、IS NOT NULL

**多重条件：**AND、OR
Like

**字符匹配：**Like、Not Like

通配符

**% ：**匹配任意字符串

**_ ：**匹配任意一个字符

**[]：**匹配一定范围内的任意一个字符

**[^] ：**匹配不在一定范围内的任意一个字符

大小写不敏感
distinct

Distinct 是去掉重复的记录(元组)

例：列出选课学生的学号。

Select distinct sno

from sc;
多表连接

一、where子句连接：连接条件写在where之后

二、from子句连接：连接条件写在from…on上面
1、内连接（inner join）：查询结果完全等同于where子句连接的查询结果。
2、左外连接（left outer join）：查询结果=内连接的查询结果+左边表里的不符合条件的数据。
3、右外连接（right outer join）：查询结果=内连接的查询结果+右边表里的不符合条件的数据。
4、全外连接（full outer join）：查询结果=内连接的查询结果+左边表里的不符合条件的数据+右边表里的不符合条件的数据。
聚合函数

把一列中的值进行聚合运算，返回单值的函数

五个预定义的聚合函数

**平均值：**Avg( )

总和： Sum( )

**最小值：**Min( )

**最大值：**Max( )

计数： Count( )返回所选列中不为NULL的数据个数

Count(*)、Count(Distinct…)

聚合函数对Null的处理

Count：不计

Sum：不将其计入

Avg：具有Null的元组不参与

Max: 不参与
分组

Group by：
select查询中如果带有group by，意思是要分组，分组之后，所有操作只能对每一个组进行。
分组之后，select后面只能跟：
（1）出现在group by 之后的列才能出现在select之后
（2）跟聚合函数，count(*)和from后面的表里所有的数值列的max(),min(),avg()和sum()。

Having

针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集跟在Group By子句的后面，没有Group By则针对全表

Having 与 Where的区别

Where 决定哪些元组被选择参加运算，作用于关系中的元组

Having 决定哪些分组符合要求，作用于分组

聚合函数的条件关系必须用Having，Where中不应出现聚合函数
多值

子查询返回 多行一列

**运算符：**In、All

**多值比较：**多行一列

父查询与多值子查询之间的比较用All来连接

值s比子查询返回集R中的每个都大时，s>All R为True

All表示所有

> all、< all、<=all、>=all、<> all

<> all等价于 not in

例：找出年龄最小的学生

Select * From Student Where sage <= all (

Select sage From Student )
无

存储过程

-- 删除存储过程，如果它存在
drop procedure IF EXISTS `pro_parm`;
-- 创建存储过程
create procedure pro_parm()
begin
-- 这里可以写增删改查语句
update zy_subject set classhour =classhour + 1 where subjectid = '1';
end;
-- 展示存储过程
show procedure status;
-- 执行存储过程
call pro_parm();

#存储过程和函数的区别

比较	存储过程	函数
参数:	in，out，inout	in
返回值:	没	必须有
函数体:	DML、DQL	不能返回结果集
解决的问题	复杂的业务	针对性

变量__函数

定义变量

用户变量

以@开头

set @abc=1;
set @ct='aaa';
-- 设置并赋值
select @c:=1;
-- 查询赋值
select @c;
-- 也可赋于从数据库查询出来的值(查出来的值要唯一)
select @lisi_year:=years
from tablename
where name='lisi';

局部变量

在函数中定义，没有@符号

delimiter$$ -- 自定义结束符号
create
function 'schooldb'.'func_test'()
returns type
begin
end$$
delimiter; -- 把语句分割符改回;

系统变量

以@@开头

select @@tx_isolation

函数

-- 8.0MySQL需要开启权限
SET GLOBAL log_bin_trust_function_creators=TRUE;
-- 之后才能创建函数

-- 无参数
CREATE DEFINER = CURRENT_USER FUNCTION `getHumanNumber`()
	RETURNS int
BEGIN
	declare ss int default 0; 
	select count(*) into ss from student;

	RETURN ss;
END;

drop function `getHumanNumber`;

select getHumanNumber();


-- 有参数
CREATE DEFINER = CURRENT_USER FUNCTION `getManNumber`(_sex varchar(2))
	RETURNS int
BEGIN
	declare ss int default 0; 
	select count(*) into ss from student where s_sex = _sex;

	RETURN ss;
END;

drop function `getManNumber`;

select getManNumber('男');

循环

while 条件 do

end while;

loopname:loop
if 结束循环的条件 then
leave loopname;
end if;

end loop;

repeat

until cond end repeat;

-- 第一种
DELIMITER $$
    CREATE PROCEDURE pro_01(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        WHILE  _i<=num  DO
           SET _sum=_sum+_i;
           SET _i=_i+1;
        END     WHILE;
        SELECT _sum;
    END$$
DELIMITER ;
-- 第二种
DELIMITER $$
    CREATE PROCEDURE pro_02(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        a:LOOP
           IF _i>num  THEN 
               LEAVE a;
           END IF;
           SET _sum=_sum+_i;
           SET _i=_i+1;        
        END LOOP;
        SELECT _sum;
    END$$
DELIMITER ;
-- 第三种
DELIMITER $$
    CREATE PROCEDURE pro_03(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        REPEAT
             SET _sum=_sum+_i;
             SET _i=_i+1;
        UNTIL _i>num  END REPEAT;
        SELECT _sum;
    END$$
DELIMITER ;

判断语句

IF  cond1 THEN
  operate;
ELSEIF  cond2  THEN
  operate;
ELSE 
END IF;

游标

可以用于很多地方

游标保存查询的结果集，允许一行一行的读取效率比较低
使用场景:函数、存储过程、触发器使用步骤:

#1、定义declare游标名cursor for select语句

#2、打开游标

#3、获取数据

#4、关闭游标

-- 例如用于存储过程中
drop procedure IF EXISTS `pro_parm`;


create procedure pro_parm()
begin
	declare sid varchar(5);
	declare shour varchar(5);
	-- 1、定义declare游标名cursor for select语句
	declare cc cursor for select subjectid,classhour from zy_subject where classhour > 0;
	-- 2、打开游标
	open cc;
		-- #3、获取数据
		fetch cc into sid,shour;
		select sid,shour;
	-- 4、关闭游标
	close cc;
end;

show procedure status;

call pro_parm;

优化升级：加入循环判断是否有数据，没有，则跳出循环

drop procedure IF EXISTS `pro_parm`;

create procedure pro_parm()
begin
	-- 创建标志位
	declare flag int default 1;
	-- 创建接受一行数据的变量
	declare sid varchar(5);
	declare shour varchar(5);
	-- 1、定义declare游标名cursor for select语句
	declare cc cursor for select subjectid,classhour from zy_subject where classhour > 0;
	-- 监视器，看是否有数据
	declare continue handler for not found set flag = 0;
	-- 2、打开游标
	open cc;
	-- 创建为xx的循环(loop)
	xx:loop
		-- #3、获取数据
		fetch cc into sid,shour;
		-- 判断标志位
		if flag = 1 then
			select sid,shour;
		else  
			leave xx;-- 结束循环
		end if;
	end loop;
	-- 4、关闭游标
	close cc;
end;

-- 执行存储过程
call pro_parm;

触发器

一触即发
对一个表做更新【增删改】的时候，触发的事件
触发器：在哪个表触发
行级触发器：影响几条记录就会触发几次
语句级触发器:一条sql语句执行了，就会触发一次
before after
新值：新添加的值教新值
旧值：
update grade set gradename=‘新班级’ where gradeid=1;

一个表不要添加过多的触发器
oracle支持行级触发器以及语句级触发器
mysql:不支持语句级触发器

-- 行级触发  
DELIMITER $$
    CREATE TRIGGER tri_01 AFTER INSERT ON dept FOR EACH ROW
    BEGIN        
         INSERT INTO depts VALUES(NULL,new.deptid,'新增');
    END$$
DELIMITER ;

事务

事务： 把一系列操作作为一个整体来执行，独立不可分割的逻辑工作单元。

要么都执行成功，要么都不执行

begin;

update blank set num = num - 100 where id = 1;
出错
update blank set num = num + 100 where id = 2;
-- 失败，执行，不把此次数据改变加入表中
rollback;
-- 成功，执行，把操作成功的数据加入表中
commit;

开启事务方式

自动开启：

MySQL的DML语句都是自动开启事务

手动开启：

方式一：begin\ 操作\rollback\commit

begin;
-- 操作

-- 失败则回滚
rollback;
-- 成功，则结束
commit;

-- 事务打节点
savepoint A;

rollback A;-- 回滚到A

方式二：修改事务提交的默认值

1–自动提交

0–手动提交

-- 查询事务提交的默认值默认值语句
select @@autocommit;
-- 修改事务提交的默认值
select @@autocommit=0;-- 相当于开启事务
select @@autocommit=1;-- 相当于关闭事务(恢复自动提交模式)

事务的特征ACID

A原子性:事务是不可分割整体，要么执行，要么不执行

C一致性:事务执行之前之后，数据总量是一致的，守恒

I隔离性:事务是独立的，互不影响，互不依赖

D持久性:事务结束,结果持久到了数据库I

1、幻想读:事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的 where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。
2、不可重复读取:事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。
3、脏读:事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。为了处理这些问题，SQL标准定义了以下几种事务隔离级别

隔离级别：

隔离级别	幻读	不可重复读	脏读
read uncommited	可	可	可
read commited	可	可	不可
repeatable reade	可	不可	不可
serializable	不可	不可	不可

视图

**视图:**不同用户关注的数据不同

学生：自己的基本信息,选课信息，各科的成绩代课

老师：自己所带课程班级信息，学生成绩

班主任：学生的基本信息

视图是虚拟的表

根据用户需要看的数据拿出来虚拟一个视图

并不存储真正的数据

可以从一个表，或多个表组合数据

降低数据库使用难度，方便查询，但是不建议使用视图增删改

当表1被删除，那么基于表1创造的视图view1也会删除

-- 创建视图
create view view_tablename
as
select colname1,colname2 from tablename;
-- 可基于视图创建视图
create view view_view_tablename
as
select colname1 from view_tablename;
-- 删除视图,如果它存在
drop view if exists view_tabename;
-- 基于视图查询,也可加上查询条件
select * from view_tablename;

索引

**索引：**快速查找,提高数据库性能#字典拼音偏旁部首

索引存储分类:B树、哈希表

常用索引类型

主键索引–唯—、 not null,然而主键具备索引的功能；当创建或设置主键的时候，mysql会自动添加一个与主键对应的唯一索引，不需要再做额外的添加。

唯一索引–唯—

联合索引–多列

全文索引–模糊查询

普通索引–

-- 创建普通索引
create index index_tablename_colname on table(colname);
-- 创建联合索引(colname1,colname2)
create index index_tablename_col1_col2 on table (colname1,colname2);
-- 创建全文索引
create fulltext index index_colname on tablename (colname);
-- 唯一键索引
create unique index index_id on tablename(id);
-- 删除索引
drop index indexname on tablename
-- 查看索引
show index from tablename

创建索引的指导原则

频繁查询的列select

经常作为查询条件的列

where 列group列 order列

表连接的列

以下场景不适合

数据量不大

列值不多

写sql注意事项

where条件后，把有索引的列写在前面，提高速度

联合索引，只有索引列同时使用到或者索引第一列时，才会使用索引

索引不是越多越快

三大范式

1NF:每个列是原子性的，不可再分(在业务的角度)

2NF:在1NF基础上，每列都完全依赖主键列。每个表只描述─件事情

3NF:在2NF基础上，非主键列不传递依赖主键列

疯子♛

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库(MySQL)

数据库关系型数据库SQL Server 微软MySQL OracleOracle OracleDB2 IBMSQL语句非关系型数据库NoSQL关键词DBMSDBSDBA:数据库管理员数据表 table记录行字段列每个数据库会有自己的方言1) DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等2
复制链接

扫一扫