MySQL的基础学习（二）

最新推荐文章于 2023-03-31 16:12:19 发布
最新推荐文章于 2023-03-31 16:12:19 发布
阅读量681 收藏 1
点赞数
文章标签： mysql 学习 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_56078818/article/details/127170416
版权

文章目录

建表

一、数据类型

  • 整型
    • 整数类型有可选的unsigned属性，表示不允许负值，这可以使正数的上限提高一倍(还多1)。
    • 有符号和无符号类型使用相同的存储空间，并且具有相同的性能，因此可以根据实际情况选择合适的类型。
    • mysqI可以为整数类型指定宽度,例如INT(11), 对大多数应用来说这是没有意义的:它不会限制合法范围，只是规定了mysql的一些交互工具(例如mysq|命令行客户端) 用来显示字符的个数。
类型大小有符号大小无符号大小
TINYINT1字节-128 to 1270 to 255
SMALLINT2字节-32768 to 327670 to 65535
MEDIUMINT3字节-8388608 to 83886070 to 16777215
INT4字节-2147483648 to 21474836470 to 4294967295
BIGINT8字节-9223372036854775808 to 92233720368547758070 to 18446744073709551615
  • 浮点型
    • FLOAT和DOUBLE可表示的范围不是完全连续的浮点数
    • 表示数据格式: DECIMAL(M,D)， M表示总的数字，D表示小数位
    • FLOAT和DOUBLE类型只能使用标准的浮点运算进行近似运算，如果需要精确运算，则需要使用DECIMAL类型。
类型大小有符号范围无符号范围
FLOAT4字节-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+380, and 1.175494351E-38 to 3.402823466E+38
DOUBLE8字节-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+3080, and 2.2250738585072014E-308 to 1.7976931348623157E+308
DECIMAL？根据M和D的值根据M和D的值
  • 字符型
    • CHAR类型
      格式: char(n),其中n是数字， 是定长的，格式会根据定义的长度分配空间。当存储CHAR值时，mysq|会删除所有的末尾空格。
    • VARCHAR类型
      格式: VARCHAR(n),其中n是数字，VARCHAR用于 存储可变长的字符串，它比定长类型更节省空间。VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节，则只使用1个字节表示，如果列长度大于255字节，则需要使用2个字节表示长度。
    • BINARY和VARBINARY
      BINARY和VARBINARY它们存储的是二进制字符串
    • Blob和Text
      BLOB和TEXT都是为了存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储
类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535字节变长字符串
BINARY0-255字节定长二进制
VARBINARY0-65535字节变长二进制
TINYBLOB0-255字节变长二进制
BLOB0-65535字节变长二进制
MEDIUMBLOB0-16.777,215字节变长二进制
LONGBLOB0-4,294,967,295 or4GB (232 - 1)字节变长二进制
TINYTEXT0-255字节变长字符串
TEXT0-65535字节变长字符串
MEDIUMTEXT0-16.777.215字节变长字符串
LONGTEXT0-4,294,967,295 or 4GB (232- 1)字节变长字符串
  • 枚举型
    格式: ENUM’(val1r,"vlul2…)， 枚举。-个仅有一个值的字符串对象，这个值式选自与值列表’value1’、‘value2’, …，或NUL。一个ENUM最多能有65535不同的值。
  • 集合型
    格式: ST('valuel,‘l2…)， -个集合。能有零个或多个值的一个字符串对象，其中每一个必须从值列表’value1’; 'value2;, …选出。-一个SET最多能有64个成员。
  • 位数类型
    BIT(1)定义一个包含1个位的数据，BIT(2)包含2个位， 以此类推，最大长度为64个位， 如值b’00111001’。
  • json类型
    存储ison格式字符串,对应的是json数据类型，json数据列会 自动验证json的数据格式，如果格式不正确会报错，最优化存储格式。json数据类型会把json格式的字符串转换成内部格式，能够快速的读取其中的元素。json对象单引号引起来。

二、创建表

2.1创建表语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_name [(
字段1 数据类型[约束条件] [默认值] [COMMENT '注解'],
字段2 数据类型[约束条件] [默认值] [COMMENT '注解'],
字段3 数据类型[约束条件] [默认值] [COMMENT '注解'],
 ......
[表约束条件]
)][engine=innodb] [default charset=utf8];
或
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_name
as subquery;

IF NOT EXISTS 关键字，则表示：如果当前数据库中不存在要创建的数据表,则创建数据表;如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。
必须指定：表名、列名(或字段名)、数据类型、长度
可选指定：约束条件、默认值

  • 创建学生表
--创建表
CREATE TABLE student (
-- int类型
id INT,
--名字最多保存20个字节
name varchar(20),
-- int类型
age int,
--最多保存11个字节
phone varchar(11)
)engine=innodb default charset=utf8mb4;
  • 创建用户表user，拥有编号、姓名、年龄、性别等字段。
create table user(
id int(2) auto_ increment comment '编号',
name varchar(20) not null comment '名字',
age int default 3 comment '年龄',
gender char(1) comment '性别',
primary key(id)
);
  • 基于员工表创建emp表，数据要求有编号、名字、薪水,新表列名和原来的表一致
create table emp
as
select id,first.name,salary
from s_emp;
  • 基于员工表创建emp1表，数据要求有编号、名字、薪水,新表列名userid、name、sal
create table emp1
as
select id as userid,first_name name,salary sal
from s_emp;
  • 创建用户表user1，性别要求只能是男、女，爱好要求是Football、Basketball、 rugby、 volleyball中选择一个或多个。 并完成数据的插入。
create table user1(
id int(2) primary key auto_increment comment '编号',
name varchar(20) not null comment '名字',
gender enum('男','女') comment '性别',
hoby set( 'Football', 'Basketball', 'rugby', 'volleyball') comment '爱好'
);
insert into user1 values(1,'lisi' ,'男','Football, Basketball, rugby');
  • 创建用户表user2，用户信息基于json数据存储，插入数据并查询。
create table user2(
person_data json
);
insert into user2 values('{"id":1, "name":"lisi","age":30, 
						   "addr":{"province":"jiansu","city":"kuns"}}');
select person_data->'$.id',person_data->'$.name' as name,person_data->'$.age'as age, 
	   person_data->' $.addr.province' as province,person_data->'$.addr.city' as city
from user2;
  • 创建测试表test_bit,存 储bit类型数据，并插入数据
create table test_bit (
f1 bit,
f2 bit(5)
);
insert into test_bit(f1) values(1);
insert into test_bit(f1) values(2) ;
insert into test_bit(f2) values(10);
insert into test_bit(f2) values(b'00111');
  • 创建测试表test_int,指定 宽度为5，插入数据并查询。
create table test_int(
f1 int(4),
f2 int(5) zerofill
);
insert into test_int values (12345,12345678);
insert into test_int values(12,128);
select * from test_int;
  • 修改test_int的引|擎为MyISAMM
alter table test_int ENGINE=MyISAM;
  • 创建表user4，user4要求是空表，表中列名为id,name,salary,类型来源于s_emp中的id、last_ name、salary。
create table user4
as
select id,last_name name,salary
from s_emp 
where 1=2;

2.2修改表结构

2.2.1 表中添加列

语法：
sql alter table tb_name add [column] col_name type [first|after col_name];
表：
sql create table user3(id int);

案例：

  • 在表user3中添加列name
alter table user3 add name varchar(20);
desc user3;
  • 在表user3中第一列的位置插入uid。
alter table user3 add column uid int first ;
desc user3;
  • 在表user3中id列之后插入age。
alter table user3 add column age int after id;
desc user3;
2.2.2 修改列

语法: alter table tb_name modify [column] col_name type [default 默认值];

案例：

  • 将表user3中的uid修改为字符串类型，默认值为1
alter table user3 modify column uid varchar(32) default '1';
2.2.3 列重命名

语法: alter table tb_name change [column] old_col_name new_col_name type;

案例:

  • 将表user3中的name修改为username类型，默认值为tom
alter table user3 change column name username text;
2.2.4 删除列

语法: alter table tb_name drop [column] col.name;

案例:

  • 将表user3中的age列删除
alter table user3 drop column age;
2.2.5 表重命名

语法: rename table old_tb_name to new_tb_name; 或 alter table old_tb_name rename [to] new_tb_name;

案例:

  • 将表user1修改名字为my_user1;
rename table user1 to my_user1;
  • 将表user2修改名字为my_user2;
alter table user2 rename to my_user2 ;

2.3 删除表

语法: drop table [if exists] tb_name [，tb_name1，.... tb_namen];
if exists 的含义为:如果当前数据库中存在相应的数据表，则删除数据表;如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

案例:

  • 删除用户表my_user1;
drop table if exists my_user1;
  • 删除表emp、emp1、test_bit、 test_int.
drop table emp,emp1,test_bit,test_int;

2.4 截取表

语法: truncate table tb_name;

  • 删除表中所有的数据
  • 释放表的存储空间

案例:

  • 清空并释放my_user2中数据及空间.
truncate table my_user2;

三、表数据操作

3.1 原始数据表

create table emp(
id int,name varchar(20),salary double
);

3.2 插入数据

3.2.1 为表的所有字段按默认顺序插入数据

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

语法: sql insert into tb.name values(value1,value2, ....);

案例:

  • 向emp表中插入一行数据
insert into emp values(1, 'lisi',30);
  • 向emp表中插入一行数据，薪水未知。
insert into emp values(2,'tom',null);
3.2.2 为表的指定字段插入数据

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

语法: insert into tb_name(col.name [,col_name2,... col_namen]) values (value1 [,value2,valuen]);

案例:

  • 向emp表中插入一行数据，数据只有编号和名字
insert into emp(id,name) values(3,'wangwu');
3.2.3 同时插入多条记录

语法:

insert into tb_name value(value1 [,value2,.... valuen]), 
						 (value1 [,value2,.... valuen]), 
						 ......
						 (value1 [,value2,.... valuen]);

或

insert into tb_name(col_name [,col_name2,.... col.namen]) value(value1 [,value2,.... valuen]), 
															   (value1 [,value2,.... valuen]),
																.......
															   (value1 [,value2,.... valuen]);

案例:

  • 向emp表中插入多行数据
insert into emp values(4,'xiaowang',1000),(5,'xiaoli',1200),(4,'xiaosong',2000);
3.2.4 将查询结果插入到表中

子查询中的值列表应与INSERT子句中的列名对应，如果自查询列名和插入表列名不一致。

语法:

insert into tb_name(tar_col_name1 [,tar_ col name2,.... tar_col namen]) 
SELECT (src_col_name1 [,src_col_name2,...,src_col_namen]) FROM src_tb_name 
[where condition]

案例:

  • 将员工表id为6-8的数据录入到emp表中
insert into emp
select id,last_name name,salary
from s_emp
where id <=8 and id >=6;
  • 将员工表id为9-10的数据录入到emp表中，薪水不录入
insert into emp(id,name)
select id,last_name
from s_emp
where id <=15 and id >=9;

3.3 数据更新

语法:
update tb_name set col_name=value1,col_name=value2,..,col.namen=valuen [where condition]
不加where条件表示对所有数据更新，where选出符合条件的数据更新。

案例:

  • 将员工表id为1-2的员工薪水统一定为2000.
update emp set salary=2000 where id<3;

3.4 数据删除

语法:
delete from tb_name[where condition]
不加where条件表示删除所有数据，where选出符合条件的数据删除。

案例:

  • 将员工表名字以xiao开头的员工删除
delete from emp where name like 'xiao%';
  • 将员工表数据清空
delete from emp;

3.5 整理表数据

语法: optimize table tb_name;

案例:

  • 将员工表闲置的资源回收
optimize table emp ;

四、约束

  • 数据完整性是指数据的精确性和可靠性。
  • 它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
  • 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。包括:
    • 实体完整性(Entity Integrity)
      如：同一个表中，不能存在两条完全相同无法区分的记录
    • 域完整性(Domain Integrity)
      如：性别男、女;星期为星期一~星期日
    • 引用完整性(Referential Integrity)
      如：员工能基于员工表找到部门，同样基于部门表可以找到区域
    • 用户自定义完整性(User-Defined Integrity)
      如：员工的名字不能不为空，唯一，员工薪水不能高于经理薪水
  • 约束的本质是对表中数据的限制和关系的维护。

4.1 约束分类

  • 表级约束
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型,
	......
	[constraint 约束名]表级约束
	[constraint 约束名]表级约束
	......
)]engine=innodb default charset=utf8
  • 列级约束
CREATE [ TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(
	字段1 数据类型 [constraint 约束名] [列级约束],
	字段2 数据类型 [constraint 约束名] [列级约束],
	字段3 数据类型 [constraint 约束名] [列级约束],
	......
)]engine=innodb default charset=utf8;

4.2 约束类型

查看s_emp表约束

select * from information_schema.table_constraints
where table_name ='s_emp';

（1）NOT NULL：非空约束，规定某个字段不能为空

  • 设置的列不能为空，换言之必须有值，所有类型的默认值都为n
  • NOT NULL只能用于列级约束不能用于表级约束
  • 所有类型和null进行运算都为null

案例:

  • 数字和null运算
select 4!=null;
  • 字符串和null拼接
select 'lisi'+null;
  • 创建表test_null，表有字段编号、名字、手机号码，其中name字段为非空
create table test_null(
id int,
name varchar(20) not null,
tel varchar(11)
);
insert into test_null values(1,'lisi','15098765005');
insert into test_null(id,tel) values (2,'18967546890');
  • 将表test_null中tel 添加非空约束。
alter table test_nu1l modify tel varchar(11) not null;
insert into test_null(id,name) values(2,'wangwu');
  • 给表test_null中name 移除非空约束。
alter table test_null modify name varchar(20) null;
insert into test_null(id,tel) values(2,'18987987656');

（2）UNIQUE：唯一约束，规定某个字段在整个表中是唯一的

用来限制某个字段/某列的值不能重复。

  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一
  • 创建唯一约束，不指定约束名字，mysql基于列名作为约束名
  • 唯一性约束允许列值为空
  • 唯一性约束默认自带索引

案例:

  • 创建表test_unique，表有字段编号、名字、手机号码，其中id字段为唯一列级约束方式创建
create table test_unique (
id int unique,
name varchar(20),
tel varchar(11)
);
insert into test_unique values(1, 'lisi','15098765005');
insert into test_unique values(1,'wangwu','18967546890');
  • 创建表test_unique1，表有字段编号、名字、手机号码，其中id字段为唯一表级约束方式创建
create table test_unique1 (
id int unique,
name varchar(20),
tel varchar(11),
constraint id_unique(id)
);
insert into test_unique1 values(1,'lisi','15098765005');
insert into test_unique1 values(1,'wangwu','18967546890');
  • 将表test_unique中的name 添加唯一约束
alter table test_unique add const raint name_unique(name);
insert into test_unique1 values(1,'wangwu','18967546890');

或

alter table test_unique modify name varchar(20) unique;
insert into test_unique1 values(1,'wangwu','18967546890');

或

alter table test_unique add const raint name_unique key(name);
insert into test_unique1 values(1,'wangwu','18967546890');

或

alter table test_unique modify name varchar(20) unique key;
insert into test_unique1 values(1,'wangwu','18967546890');
  • 创建用户表user4，表中字段有编号、用户名、密码，要求用户名和密码组合不能重复。
create table user4(
id int,
name varchar(20),
passwd varchar(20),
constraint name_passwd_unique(name,passwd)
);
insert into user4 values(1,'lisi','123');
insert into user4 values(2,'lisi','13');
insert into user4 values(3,'lisi','123');
  • 查询user4表中的约束有哪些
select *
from information_schema.table_constraints
WHERE table_name='user4';

或

show index from user4;
  • 删除user4表中的约束
alter table user4 drop index name passwd_un;
insert into user4 values(3,'lisi','123');
insert into user4 values(4,'lisi','123');

删除唯一约束只能通过删除唯一索引的方式删除
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名

（3）PRIMARY KEY：主键(非空且唯一)约束

用来唯一标识表中的一行记录，其特点：

  • 主键约束列不允许重复，也不允许出现空值。
  • 一个表最多只能有一个主键约束 主键约束对应着表中的一列或者多列，组合列同样不允许重复，也不允许出现空值
  • 创建主键约束时，系统默认会在所在的列或列组合，上建立对应的主键索引
  • 删除主键约束了，主键约束对应的索引就自动删除了

案例:

  • 创建表test_pk，列有编号、名字、薪水，编号能唯一标识一行数据，列级构建
create table test_pk(
id int primary key,
name varchar(20),
age int
);
insert into test_pk values(1,'lisi',1000);
insert into test_pk values(1,'tom',1200);
insert into test_pk(name,age) values('wangwu',1300);
  • 创建表test_pk1，列有编号、名字、薪水，编号能唯一标识一行数据，表级构建
create table test_pk1(
id int,
name varchar (20),
age int,
constraint test_pk_id_pk primary key(id)
);
insert into test.pk1 values(1,'lisi',1000);
insert into test.pk1 values(1,'tom',1200);
insert into test.pk1(name,age) values('wangwu',1300);
  • 创建表test_pk2，列有编号、名字、薪水，编号和名字组合能唯一标识一行数据
create table test_pk2(
id int,
name varchar(20),
age int,
constraint test_pk_id_name_pk primary key(id,name)
);
insert into test_pk2 values(1,'lisi',1000);
insert into test_pk2 values(1,'tom',1200);
insert into test_pk2(name,age) values('wangwu',1300);
insert into test_pk2 values(1,'tom',1200);

联合主键只能基于表级约束声明
联合主键，组合中列不能为空，且组合不能重复

  • 创建表test_pk3,列有编号、名字、薪水，先创建表在设置编号为主键
create table test_pk3 (
id int,
name varchar(20),
age int
);
alter table test_pk3 add constraint test_pk_id_ pk primary key(id);
  • 删除test_pk3表的主键
alter table test_pk3 drop primary key;
insert into test_pk3 values(1,'lisi',1000);
insert into test_pk3 values(1,'tom',1200);
insert into test_pk3(name,age) values('wangwu',1300);

删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

表之间关系： 一对多、一对一、多对多

表与表之间维护关系的列是外键

范式：
数据库范式主要是为解决关系数据库中数据冗余、更新异常、插入异常、删除异常问题而引入的设计理念。简单来说，数据库范式可以避免数据冗余，减少数据库的存储空间，并且减轻维护数据完整性的成本。是关系数据库核心的技术之一，也是从事数据库开发人员必备知识。
目前关系数据库有六种范式：第一范式(1NF) 、第二范式(2NF) 、第三范式(3NF) 、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF, 又称完美范式)。

  1. 第一范式(1NF) ：每个字段原子性，不可再分割
  • 不符合第一范式的表：属性还可以再分割。
idnamemessage
001张三13岁，五年级
002李四14岁，六年级
003王五15岁，七年级
  • 修改后的表：
idnameageclass
001张三13岁五年级
002李四14岁六年级
003王五15岁七年级
  1. 第二范式(2NF)
  • 在满足1NF的前提下，表中不存在部分依赖，非主键列要完全依赖于主键。(主要是说在联合主键的情况下，非主键列不能只依赖于主键的一部分)
stu_id (学号)kc_id (课程号)score (分数)kc_name (课程名)
00110185高数
00110279C语言
00210179高数

注意:这张表中的stu_id和kc_id构成了联合主键。score没问题，它完全依赖于联合主键，stu_id和kc_id两个值才能决定score的值。但是kc_name只依赖于kc_id，这是部分依赖关系，不符合第二范式。

  • 修改后的表：
stu_ id (学号)kc_ id (课程号)score (分数)
00110185
00110279
00210179
kc_ id (课程号)kc_ name (课程名)
101高数
102C语言
  1. 第三范式(3NF)
  • 在满足2NF的前提下，不存在传递依赖。(A->B,B->C, A~>C)
idnamesex_codesex_descphone
001张三1男178XXXXXXXX
002李四1男177XXXXXXXX
003王五0女188XXXXXXXX

注意: sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。(id->sex_code->sex_desc)

修改后的表：

idnamesex_codephone
001张三1178XXXXXXXX
002李四1177XXXXXXXX
003王五0188XXXXXXXX
sex_codesex_desc
1男
0女

（4）FOREIGN KEY：外键约束，维护表与表之间的关系
语法：

create table tb_name(
col_name type primary key,
col_name1 type
);
create table tb_name(
col_name type primary key,
col_name1 type,
col_name2 type
[constraint fg_name] foreign key(col_name2) references tb_name(col_name) [on update
[cascade|set null][on delete [cascade|set null]]
);
  • 外键用于维护表与表之间的关系，外键必须引用另外一张表的主键或唯一约束列。
  • 创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名。
  • 创建表时就指定外键约束的话，先创建主键约束的表，再创建外键的表。
  • 删表时，先删外键的表，再删除主键表，删除数据同理。
  • 一张表可以创建多个外键，引用多个不同的表的主键维护关系。
  • 创建外键约束时，系统默认会在所在的列.上建立对应的普通索引。但是索引名是外键的约束名。
  • 删除外键约束后，必须手动删除对应的索引
  • 外键和主键类型必须一致

案例：

  • 创建夫妻表（一对一）
create table hus(
id int primary key,
name varchar(20)
);

create table wife(
id int primary key,
name varchar(20),
hus_id int,
constraint hus_fk foreign key(hus_id) references hus(id)
);
insert into hus values(1,'jake');
insert into wife values(1,'rose',1);
insert into wife values(2,'lili',2);

外键值必须在另外一张表存在，且是主键或唯一键
一对一关系可以在任意一方维护(外键可以建任意一方)

  • 创建员工表和部门表（一对多）
create table dept(
id int primary key,
name varchar(20)
);
create table emp(
id int primary key,
name varchar(20),
salary double,
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into dept values(1,'bigdata');
insert into emp values(1,'lisi',3000,1);
insert into emp values(2,'wangwu',3200,1);
insert into emp values(3,'zhansan',2800,1);
  • 创建学生表和课程表（多对多）
create table stu(
id int primary key,
name varchar(20)
);
create table course(
id int primary key,
name varchar(20)
);
create table stu_cou(
stu_id int,
course_id int,
foreign key(stu_id) references stu(id),
foreign key(course_id) references course(id),
primary key(stu_id,course_id)
);
insert into stu values(1,'lisi');
insert into stu values(2,'zhansan');
insert into course values(1,'java');
insert into course values(2,'xml');
insert into course values(3,'hadoop');
insert into course values(4,'hbase');
insert into course values(5,'zookeeper');
insert into course values(6,'kafka');
insert into stu_cou values(1,1);
insert into stu_cou values(1,2);
insert into stu_cou values(1,3);
insert into stu_cou values(1,4);
insert into stu_cou values(2,1);
insert into stu_cou values(2,3);
insert into stu_cou values(2,4);
insert into stu_cou values(2,5);
insert into stu_cou values(2,6);
  • 现有部门门表和员工表，其中两者之间并没有维护关系，现动态加入关系
create table dept1(
id int primary key,
name varchar(20)
);
create table emp1(
id int primary key,
name varchar(20),
salary double
);

alter table emp1 add dept_id int;
alter table emp1 add foreign key(dept_id) references dept1(id);

insert into dept1 values(1,'bigdata');
insert into dept1 values(2,'java');
insert into emp1 values(1,'lisi',3000,1);
insert into emp1 values(2,'wangwu',3200,1);
insert into emp1 values(3,'zhansan',2800,2);

delete from dept1 where id=1;

update dept1 set id=3 where id=1;

drop table dept1
  • 删除数据必须先删除外键所对应的数据，才能删除外键弓|用的数据
  • 主键被其他表引用主键数据不能被修改，除非主键不被其它表引用
  • 主键被其他表引用主键所在表不能被删除，除非主键不被其它表引用
  • 创建员工表和部门表，员工表构建外键，修改部门表主键数据，员工表外键同时修改，删除部门表数据，对应员工表外键值为null
drop table emp1;
drop table dept1;
create table dept1(
id int primary key,
name varchar(20)
);
create table emp1(
id int primary key,
name varchar(20),
salary double,
dept_id int,
foreign key(dept_id) references dept1(id) on update cascade on delete set null
//创建员工表和部门表，员工表构建外键，修改部门表主键数据，员工表外键为null， 删除部门表数据，对应员工表外键值整行数据删除
foreign key(dept_id) references dept1(id) on update set null on delete cascade
//创建员工表和部门表，员工表构建外键，修改部门表主键数据，员工表外键同时修改，删除部门表数据，对应员工表外键值整行数据删除
foreign key(dept_id) references dept1(id) on update cascade on delete cascade
);
insert into dept1 values(1,'bigdata');
insert into dept1 values(2,'java');
insert into emp1 values(1,'lisi',3000,1);
insert into emp1 values(2,'wangwu',3200,1);
insert into emp1 values(3,'zhansan',2800,2);

update dept1 set id=3 where id=1;

delete from dept1 where id=2;
  • 员工表和部门表通过外键维护关系，现删除外键
select *
from information_schema.table_constraints
WHERE table_name = 'emp1'

alter table emp1 drop foreign key emp1_ibfk_1;

insert into emp1 values(4,'zhaoliu',2800,10);

（5）CHECK：检查约束
自增主键：

  • 案例：创建表，主键要求按照数字自动增加，步长为1
create table test_auto_key(
id int primary key auto_increment
);
insert into test_auto_key values(0);
insert into test_auto_key values(0);
insert into test_auto_key values(0);
insert into test_auto_key values(0);

insert into test_auto_key values(10);
insert into test_auto_key values(0);
insert into test_auto_key values(0);

auto_ increment修饰的列必须是key
案例：

  • 创建baby表，字段编号唯一且不为空、 名字不能为空，年龄不能超过3岁且大于0。
create table baby(
id int primary key auto_increment,
name varchar(20) not null,
age int check(age<=3 and age >0)
);
insert into baby values(1,'lisi',2);
insert into baby values(2,'wangwu',10);
  • 如果表中列建表没有添加约束，使用:
    alter table 表名称 modify 字段名 数据类型 [constraint约束名] check(条件);
    alter table 表名称 add [constraint约束名] check(条件);
  • 查看约束名:
    select * from information_schema.TABLE_CONSTRAINTS whre table_name='表名';
  • 删除约束:
    alter table 表名称 drop constraint 约束名;

（6）DEFAULT：默认值约束
给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。默认值约束-般不在唯一键和主键列上加。
案例:

  • 创建用户表user5,编号非空且唯一，名字非空，默认名字lisi, 年龄未填写，默认为15,性别未填写，默认男;
create table user5(
id int primary key auto_increment,
name varchar(20) not null default 'lisi',
age int default 15,
gender enum('男','女') default '男'
);
insert into user5(id) values(1);
  • 如果表中列建表没有添加约束，使用:
    alter table 表名称 modify 字段名 数据类型 default 默认值;
  • 删除约束:
    alter table 表名称 modify 字段名 数据类型;

五、事物

事务.(Transaction)是访问和更新数据库的程序执行单元，是一个最小的不可分割的工作单元，能保证一个业务的完整性。
事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。

5.1 事务的特点

数据库事务必须具备ACID特性，ACID是Atomic (原子性)、Consistency (一致性)、**Isolation (隔离性)和Durability (持久性)**的英文缩写。

  • 原子性(Atomicity)
    一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。
    如:张三转账给李四钱，转账过程中停电了，应该转账不生效(回退)，事物的原子性
  • 一致性(Consistency)
    事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。
    如:张三转账给李四钱，转帐之前和转账之后，账务总额应该保持不变。
  • 隔离性(Isolation)
    指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。 由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
    如:张三转账过程，李四看不到张三账户的变化
  • 持久性(Durability)
    指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
    如:如果转到错误的人的账户上，钱是退不回来的，事务一旦完成，事务中所涉及的数据，不能再被此次事务所更改。

5.2 查看事物

mysql默认是开启事务，即autocommit=1，自动提交事务。即执行insert、update、 delete操作， 立刻提交。

select @@autocommit;

5.3 开启事物

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
语法:

--开启事物
start transaction
--执行DML语句
sql...
[SAVEPOINT 名字;]
sql...
--不会结束事物
[rollback to 名字] 
sql...
--事物结束
commit/rollback

有一些语句会影响事务的结束，事务没结束开启下一次事务

DDL语句：ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、 ALTER TABLE、 ALTER VIEW、CREATE TABLE、 DROP TABLE、 RENAME TABLE、TRUNCATE TABLE等
修改MYSQL架构的语句：CREATE USER、 DROP USER、 GRANT、 RENAME USER、 REVOKE、 SET PASSWORD
管理语句：ANALYZE TABLE、 CACHE INDEX、 CHECK TABLE、 LOAD INDEX INTO CACHE、 OPTIMIZE TABLE、 REPAIR TABLE等。

案例： 针对表test_tran,所有sql执行成功在提交数据;

create table test_tran(
id int primary key,
name varchar(20),
salary double
);
start transaction;
savepoint a;
insert into test_tran values(1,'lisi',2000);
savepoint b;
insert into test_tran values(2,'tom',2300);
savepoint c;
insert into test_tran values(3,'lisi',1200);
savepoint d;
insert into test_tran values(4,'wangwu',2100);
rollback to c;
commit;

5.4 事物的隔离级别

事务隔离级别指的是隔离性的强弱，主要是针对以下三个问题，有时候在逻辑中是允许出现，那就要求隔离不那么强，有时候在这个问题在逻辑中不允许出现，那就要求隔离性比较强。

  • 不可重复读
    在同一事务中，两次读取同一数据，得到内容不同。
  • 幻影读
    同一事务中，用同样的操作读取两次，得到的记录数不相同。
  • 脏读
    会话1读到会话2未提交的数据。
  • READ UNCOMMITTED幻影读、不可重复读和脏读都允许。
  • READ COMMITTED允许幻影读、不可重复读，不允许脏读
  • REPEATABLE READ (默认)允许幻影读，不允许不可重复读和脏读
  • SERIALIZABLE幻想读、不可重复读和脏读都不允许

案例：

  • 查看当前数据库中的默认隔离级别
show variables like '%isolation%';
  • 修改数据库中的隔离级别
set session transaction isolation level READ COMMITTED;
Ｓmile____゛定格774
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
快速学习MySQL基础知识
09-08
快速学习MySQL基础知识涵盖了SQL的基本用法，包括SQL语句的规范、数据库类型、SELECT语句的执行过程以及常用的查询技巧。 首先，SQL（Structured Query Language）是数据库管理的核心语言，分为四个主要部分：DDL...
MySQL基础学习5
12-14
文章目录聚合函数sum求和avg平均数max最大值min最小值year求年龄count查询数据个数group by分组with rollup数据总汇having分组筛选join多表查询inner join内连接[outter] join外连接left [outer] join左连接right ...
MySQL数据库对数据库表的创建和DML操作
欢迎来到爱吃熊掌的鱼的博客
03-31 1201
MySQL数据库对数据库表的创建和DML操作
mysql 生成uid函数
ladymaidu
10-24 150
uuid（）产生的字符串的形式的uuid
新建一个用户表：用户id 用户名 密码 新建一个角色表：角色id 角色名——sql初始化几个角色，员工 管理员 组长等新建一个角色用户中间表：1个用户可以有多个角色 1个角色可以对应多个用户
weixin_53056046的博客
03-24 1488
新建一个用户表：用户id 用户名 密码 新建一个角色表：角色id 角色名——sql初始化几个角色，员工 管理员 组长等 新建一个角色用户中间表：1个用户可以有多个角色 1个角色可以对应多个用户 主键 用户id 角色id 功能： 新增用户表信息， 查询管理员角色的id，用户新增返回的主键 给用户初始化一个角色：管理员——入库到数据中 package com.jdbc.day04.utils; import java.io.IOException; import java.io.In..
mysql对表中添加属性_MySQL 表字段属性
weixin_39675728的博客
12-29 897
MySQL 表字段属性主键表的主键(primary key，主关键字)是表中的一个或多个字段，它的值用于惟一地标识表中的某一条记录。一个表不能有多个主关键字，并且主关键字的列不能包含空值和重复值。主关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。语法：PRIMARY KEY (column_name)uidusernamepasswordemailreg...
mysql uid uuid_MySql 之UUID()
weixin_31280837的博客
02-07 329
mysql中做了个定时执行的事件，发现原来起作用，现在不行了。调用/var/lib/mysql中的错误日志文件，发现一句：Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a sy...
MySQL学习基础
12-14
MySQL数据库学习   一.为什么要学习数据库 二.数据库的相关概念 DBMS：数据库管理系统（datebase Management System），数据库是通过DBMS创建和操作的内容； DB :数据库（datebase）：存储数据的仓库，他保存了一...
MySQL基础学习文档
最新发布
06-19
以下是一份详细的MySQL基础知识概述，特别针对大学生入门和期末复习。 首先，安装和配置MySQL可以通过观看视频教程来完成。安装过程中，你可能需要下载并安装MySQL Server以及一个图形化工具，如Navicat，它可以...
MySQL的基础大纲学习
06-03
在进行MySQL的基础学习时，了解其基础大纲、基本命令和语法规则是至关重要的。以下是一些关于MySQL的基础知识点： 1. **MySQL基础大纲**： - 数据库概念：理解数据库是什么，以及它在数据管理和存储中的作用。 - ...
生成唯一uid的思路
mmmMMM12345的博客
07-07 570
一. 通过mysql主键索引生成 通过mysql的主键自增来获取uid。如果有要求比如从10000开始，那么可以设置自增起始值为10000。还可以设置自增长度，比如自增长度为2，那么获取到的值为1,3,5...... 二.通过redis INCR命令生成 通过redis的INCR命令的返回值获得自增的id ...
mysql 生成uid_MySQL死锁与存储过程生成UID
weixin_27960253的博客
01-19 141
I have a stored procedure generating UID's from a "ticket" table, but under load I'm getting lots of deadlocks. I'm calling this procedure many times from multiple concurrent connections whenever my t...
往mysql中插入 自增id不连续（曾经的坑）
热门推荐
SolidCocoi的博客
04-20 3万+
往mysql中插入 自增id不连续
软件测试mysql面试题：mysql、表“test”，两个字段 id 和 uid。查看数据表结构、插入一条记录、删除一条记录。
一亿并发的博客
04-21 315
mysql、表“test”，两个字段 id 和 uid。查看数据表结构、插入一条记录、删除一条记录。
mysql生成uuid_MySQL生成UUID
weixin_36481965的博客
01-18 1万+
UUID函数在MySQL中，可以用uuid()函数来生成一个UUID，如下图：replace函数默认生成的uuid含有'-'，我们可以使用replace函数替换掉'-'，SQL如下：select replace(uuid(),"-","") as uuid;结果如下：Insert语句中使用UUID如果一个表中id字段使用uuid来作为主键，那我们可以使用下面的语句来插入数据：INSERT INTO...
数据库 ID 生成方案：百度 UidGenerator
happyJared
11-29 876
UidGenerator 是用 Java 语言实现的基于 Snowflake 算法的唯一 ID 生成器。 UidGenerator 是以组件形式工作在应用项目中, 支持自定义 workerId 位数和初始化策略, 从而适用于 Docker 等虚拟化环境下实例自动重启、漂移等场景。在实现上，UidGenerator 通过借用未来时间来解决 Sequence 天然存在的并发限制；采用 ...
Mysql生成UUID 亲测
大飞的博客
11-20 315
SELECT REPLACE(UUID(),"-",""); UUID(); 函数会生成一个字符串，里面带 ”-“ 特殊字符。 用REPLACE 函数去掉”-“ 就好啦。
mysql 0基础学习
04-29
学习 MySQL 的 0 基础，你需要掌握以下几个方面： 1. 安装 MySQL 数据库 你需要下载并安装 MySQL 数据库，可以从官方网站下载，也可以使用一些集成环境，如 XAMPP 或 WAMP，这些工具会自动安装并配置 MySQL。 2. 学习 SQL 语言 SQL 是 MySQL 数据库的查询语言，你需要学习 SQL 语言的基础语法，包括 SELECT、INSERT、UPDATE、DELETE 等命令，以及一些基本的数据类型、表的创建和删除等操作。 3. 熟悉 MySQL 工具 MySQL 提供了一些工具来管理和操作数据库，包括 MySQL 命令行客户端、MySQL Workbench、phpMyAdmin 等，你需要熟悉这些工具的使用和操作。 4. 学习 MySQL 数据库的设计和优化 在学习 MySQL 数据库的过程中，你需要了解数据库的设计和优化原则，包括如何设计表、如何优化查询语句等方面。 总之，学习 MySQL 的 0 基础需要你掌握 MySQL 的基础知识，这些知识包括安装、SQL 语言、MySQL 工具和数据库的设计和优化。你可以通过阅读相关的教程、参加培训或者自学来掌握这些知识。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值