Day3
MySQL的数据类型
- 命名规则
和java一样
- 常用的数据类型
数据类型 | 解释 |
---|---|
INT | 从-2^31到 2^31-1的整形数据,存储大小为4个字节 |
CHAR(size) | 定长字符数据,若未指定,默认为1个字符,最大长度为255。注意:与java不同,数据库中的char也可以放字符串,但是一般用varchar放字符串 |
VARCHAR(size) | 可变长字符数据,根据字符串实际长度保存,必须指定长度 |
FLOAT(M,D) | 单精度,M = 整数位+小数位, D= 小数位 ,默认M+D<=6。比如:FLOAT(5,3),5代表整数位+小数位的长度为5,3代表小数位的长度为3, 则整数位的长度为2 |
DOUBLE(M,D) | 双精度,M = 整数位+小数位, D= 小数位,默认M+D<=15。与单精度类似 |
DATE | 日期型数据,格式”YYY-MM-DD“ |
BLOB | 二进制形式的长文本数据,最大可达4G。可存视频、图片等的地址 |
TEXT | 长文本数据,最大可达4G。可存文本等的地址 |
- 问题
- char定长字符数据和varchar可变长字符数据的区别?
答:举例说明:假如我现在对char和varchar的长度size都设置为5,此时分别向char和varchar中写了一个字“男”,此时在char中该字符实际占用的长度为5,但在varchar中其实际占用的长度为1。一般情况下,字符用char,字符串用varchar
数据库事务
- 事务定义:
是一组逻辑操作单元,是数据从一种状态变换到另一种状态。
- 数据库事务的组成:
- 一个或多个DML语句
- 一个DDL语句(数据定义语言)
- 一个DCL语句(数据控制语言)
- 事务开启
SET autocommit = FALSE;
------禁止自动提交
SET autocommit = TRUE;
------允许自动提交
- 事务提交
COMMIT;
- 事务回滚
ROLLBACK;
注意:
1、一旦事务结束一定要允许自动提交,否则后续的操作会仍处在事务中;
2、一旦进行事务提交则就不可以进行回滚了
举例说明
AA给CC转账1000元
代码如下:
AA减去1000元的操作
System.out.println(1/0);
CC加上1000元的操作;
**遇到得到问题:**由以上代码可以看出,第一句代码可以顺利执行,但第二句代码执行会失败从而导致第三句代码无法执行
**解决思路:**为了解决该错误,可以利用事务将AA和CC的操作看成一个整体,即要么都成功执行,要么都失败。
解决如下:
try{
事务开启(禁止自动提交);
AA减去1000元的操作;
System.out.println(1/0);
CC加上1000元的操作;
事务提交;
}catch(Exception e)(
事务回滚;
)
代码说明:
**事务开启:**首先尝试执行try函数体中的代码(在事务开启后开始执行)
若“AA减去1000元的操作;”执行成功,而后续执行出错则会跳到catch函数体中执行事务回滚操作;若try函数体中的代码都能成功执行则进行事务提交
**事务回滚:**将try函数体中已经成功执行的操作给撤销掉
**事务提交:**代表该次操作执行成功,利用数据库的事务时,事务提交后才代表所作的操作真正生效,否则并不生效
什么时候用事务?
当进行多个操作且需要将多个操作绑在一块,要么都成功,要么都失败。此时才可以用事务。
库的操作
SHOW DATABASES;
------查看所有库
USE 库名;
------选库-----USE myemployees;
SHOW CREATE DATABASE 库名;
------查看库的信息------SHOW CREATE DATABASE myemployees;
ALTER DATABASE 库名 CHARACTER SET '编码集';
—修改库的编码集—ALTER DATABASE myemployees CHARACTER SET 'utf8';
SHOW VARIABLES LIKE '%char%';
------查看系统中的属性和值
DROP DATABASE [IF EXISTS] 库名;
------删除库
加上和不加IF EXISTS的区别: 在删除库时如果该库本来就不存在(或已经被删除),则加上IF EXISTS后不会报错
CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET '编码集'];
------创建库
加上关键字IF NOT EXISTS代表: 若该库名不存在则创建该库;若存在则不会报错且不会再创建该库
注意:当不加该关键字时,你创建的库若存在则会报错加上关键字CHARACTER SET '编码集’代表: 在创建库时指定库的编码集
表的操作
注意: 表的默认编码集和库的编码集一致
SHOW TABLES FROM 库名;
------查看某一库下的所有的表------SHOW TABLES FROM myemployees;
DESC 表名;
------查看表的结构------DESC employees;
SHOW CREATE TABLE 表名;
------查看表的信息------SHOW CREATE TABLE employees;
创建表并指定表的编码集
- 方式一
CREATE TABLE [IF NOT EXISTS] 表名(
字段名1 类型1,
字段名2 类型2,
...
字段名n 类型n
)CHARACTER SET '编码集';
- 方式二
CREATE TABLE IF NOT EXISTS 表名
AS
查询语句;
方式二示例如下:
当你在选中db1库时输入一下代码来查询myemployees库中的employees表中的信息时会报错,因为此时db1中不存在employees表,该表是在myemployees库中的。
SELECT first_name, salary
FROM employees;
因此你可以将该表的查询结果生成一个表存在db1中,如下:
CREATE TABLE emp1
AS
SELECT first_name, salary
FROM myemployees.`employees`; #代表取myemployees库中的employees表
此时即可在db1中生成表emp1,按F5刷新即可,如下所示
- 方式三:基于现有的表结构创建一张新表(表中没有内容)
CREATE TABLE [IF NOT EXISTS] 新建表名 LIKE 库名.表名;
# 如下所示
CREATE TABLE emp2 LIKE myemployees.`employees`;
- 方式四:右键表—>创建表,如图所示,然后填入相应内容点击保存即可。
DROP TABLE [f NOT EXISTS] 表名;
------删除表------DROP TABLE db1.emp1;
ALTER TABLE 原表名 RENAME TO 新表名;
------修改表名------ALTER TABLE emp1 RENAME TO reemp1;
数据的增、删、改
首先创建一个空表来进行后续数据的增、删、改
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
age INT,
sid INT
)
数据的增
插入单行数据
INSERT INTO 表名(字段名1,字段名2,...) values(值1,值2,...);
- 插入单行数据(所有字段)
INSERT INTO student(id, NAME, age, sid) VALUES(1,'libai', 18, 1000);
运行以上代码然后刷新后,如下数据已插入完毕。
注意:若插入的是全字段则可以省略表名后的字段名
INSERT INTO student(id, NAME, age, sid) VALUES(3,'dufu', 19, 2000);
#等同于
INSERT INTO student VALUES(3,'dufu', 19, 2000);
- 插入单行数据中的某几个字段
INSERT INTO student(id, NAME) VALUES(2,'sushi');
插入多行数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...), (值1,值2,...), ...;
INSERT INTO student VALUES(4,'zhangsan', 20, 1003), (5,'lisi', 21, 1004);
将查询的结果插入表中
INSERT INTO 表名(字段名1,字段名2,...) SELECT 字段名1, 字段名2, ... FROM ...;
INSERT INTO student(id, NAME)
SELECT employee_id, first_name
FROM myemployees.`employees`;
==注意:==查询的字段的类型和个数要和被插入的字段的类型和个数保持一致,否则会报错
数据的删除
DELETE FROM 表名 WHERE 过滤条件;
------根据条件删除数据
DELETE FROM 表名;
------删除表中的所有数据
TRUNCATE TABLE 表名;
------删除表中的所有数据,该SQL代码无法开启事务
==注意:==当使用”TRUNCATE TABLE 表名;
“时,则无法开启数据库事务。即在数据库事务情况下利用该SQL代码进行删除数据时,无法进行事务回滚(即无法恢复删除的数据);而数据库事务对“DELETE FROM 表名;
”是生效的。
DELETE FROM 表名;
和TRUNCATE TABLE 表名;
的区别?
1、
DELETE FROM
可以进行事务回滚,而TRUNCATE TABLE
则不可以2、若确定删除后不需要回滚则可以使用
TRUNCATE TABLE
(效率高)
例题1:删除id= 1的所有数据
#删除id= 1的所有数据
DELETE FROM student WHERE id = 1;
数据的修改
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,... WHERE 过滤条件;
------根据条件修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,... ;
------修改所有表中所有数据
例题1:将id = 2的人的年龄改为12,sid改为1002
#将id = 2的人的年龄改为12,sid改为1002
UPDATE student SET age = 12, sid = 1002 WHERE id = 2;
例题2:修改表中的所有内容
UPDATE student SET age = 10, sid = 1000;
对列的操作
向表中添加、删除、修改列
-
向表中添加列
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型;
例子:
首先利用DESC emp1;来查看表的信息,如下,可看出表中现在只有两列,字段名分别为id和ename
然后向表emp1中添加列ALTER TABLE emp ADD COLUMN child_num INT;
然后输入DESC emp1;
查看表的信息,可看出此时表中多了一列,其字段名为child_num
- 修改列的名字
ALTER TABLE 表名 CHANGE COLUMN 原字段名 新字段名 原数据类型;
如下:
ALTER TABLE emp1 CHANGE COLUMN child_num age INT;
DESC emp1;#查看表修改后的信息
由图可看出,已将字段名child_name改为age
- 修改字段的类型
ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型;
由修改列的名字中的运行截图可看出,age的数据类型为int,现将其修改为字符串类型,如下:
ALTER TABLE emp1 MODIFY COLUMN age VARCHAR(20);
如图所示,此时已将age的数据类型由int改为了varchar类型
- 删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE emp1 DROP COLUMN age;
如图所示,此时字段名age已被删除
约束
- 约束定义:
- 为了保证数据的一致性和完整性,SQL规范以约束的方式对表数据进行额外的条件限制
- 约束是表级的强制规定
- 可以在创建表时规定约束(通过CREATE TABLE语句),或者在表创建之后也可以(通过ALTER TABLE 语句)
- 有六种约束:
NOT NULL
------非空约束,规定某个字段不能为空
UNIQUE
------唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY
------主键约束(非空且唯一),相当于非空和唯一约束的结合
FOREIGN KEY
------外键约束
CHECK
------检查约束**(MySQL不支持)**
DEFAULT 默认值
------默认值约束
注意:
非空约束和默认值约束只有列级约束中有,而表级约束中没有;
一个表中只能有一个主键约束,若有多个代表是表级约束,详见“创建表时添加表级约束”
- 约束的分类
列级约束:同时只能约束一列
表级约束:同时可以约束多列。
举例对两种约束分类进行说明:
id | age |
---|---|
1 | 10 |
1 | 20 |
如上表所示,若对id进行列级唯一约束,则此时第一行的id和第二行的id不能一样,此处一样则为错误;若对id和age进行表级唯一约束,则此时第一行中的id=1,age=10和第二行中的id=1,age=20并未完全一样,此时则为正确。
- 添加约束的方式有两种
1、创建表时添加约束
- 创建表时添加列级约束
- 创建表时添加表级约束
2、创建表后添加约束(了解即可)
创建表时添加列级约束
举例说明
首先创建一个新库db3并创建一个新表来进行列级约束,以便于能够理解
CREATE DATABASE db3;#创建库
#库中创建表
CREATE TABLE emp(
id INT PRIMARY KEY,#主键约束
NAME VARCHAR(20) NOT NULL,#非空约束
sid INT UNIQUE,#唯一约束
age INT DEFAULT 18#默认约束
)
DESC emp;#创建完成后查看表的结构
为空表插入数据(注意:此时未给id=2的人插入年龄数据,则系统会自动默认约束为18)
INSERT INTO emp VALUES(1,'aa',1000,20);
INSERT INTO emp(id, NAME, sid) VALUES(2,'bb', 1001);
- 测试约束为主键约束的id
测试为表中插入id也为2的数据,报错如图所示
INSERT INTO emp(id, NAME, sid) VALUES(2,'cc', 1002);
测试为表中插入id为Null的数据,报错如图所示
INSERT INTO emp(id, NAME, sid) VALUES(NULL,'cc', 1002);
- 测试约束为非空约束的NAME,报错如图所示
INSERT INTO emp(id, NAME, sid) VALUES(3,NULL, 1002);
- 测试约束为唯一约束的sid,报错如图所示
INSERT INTO emp(id, NAME, sid) VALUES(3,'cc', 1001);
自增(AUTO_INCREMENT)
- 自增定义
利用MySQL提供的自动增长功能来自动生成主键的值,防止插入的值重复导致插入失败。自动增长功能通过auto_increment
来实现,基本语法格式如下:字段名 数据类型 auto_increment;
- 注意事项
1、一个表中只能有一个自动增长字段,该字段的数据类型是整数类型,且必须定义为键,如unique key、primary key。
2、如果为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段,则该字段就会使用自动增长值;如果插入的是一个具体的值,则不会使用自动增长值。
3、自动增长值从1开始自增,每次加1。如果插入的值大于自动增长的值,则下次插入的自动增长值会自动使用最大值加1;如果插入的值小于自动增长值,则不会对自动增长值产生影响。
4、使用DELETE删除记录时,自动增长值不会减小或填补空缺。5、自增一般用在主键约束中
6、自增时不管代码是否执行成功,被赋予自增的那个字段的数据都会自增一次
举例说明
- 创建一个空表emp4,并将id设置为主键约束
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);
- 向表中加入数据并执行该代码多次(此处假定为5次),结果如图所示
INSERT INTO emp4(NAME) VALUES('aa');
==问题:==对字段名id设置为主键约束后,其值应该是非空且唯一的,但此处代码并未给它赋值,为什么不会报错?
答:因为字段id已经被设置为自增。如果为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段,则该字段就会使用自动增长值,所以不能认为在插入数据时id的数据是空的,所以不会报错,正常执行。
创建表时添加表级约束
可进行主键约束和唯一约束
- 主键约束在表级约束中的使用方法:
CONSTRAINT 索引名 PRIMARY KEY(字段名1,字段名2,...)
- 唯一约束在表级约束中的使用方法:
CONSTRAINT 索引名 UNIQUE(字段名1,字段名2,...)
表级约束中的主键约束
在db3中添加新表emp2
#创建表emp2
CREATE TABLE emp2(
id INT,
sid INT,
NAME VARCHAR(20),
CONSTRAINT emp2_id_sid PRIMARY KEY(id, sid)
);
DESC emp2;#创建完成后查看表的结构
在空表emp2中插入数据
INSERT INTO emp2(id,sid) VALUES(1,10);
INSERT INTO emp2(id,sid) VALUES(1,20);
==注意:==此时id虽一样但是在表级约束中id和sid组合起来不一样,所以满足主键约束的条件
表级约束中的唯一约束
在db3中添加新表emp3
CREATE TABLE emp3(
id INT,
sid INT,
NAME VARCHAR(20),
CONSTRAINT emp3_id_sid UNIQUE(id, sid)
);
DESC emp3;#创建完成后查看表的结构
在空表emp3中插入数据
INSERT INTO emp3(id,sid) VALUES(1,10);
INSERT INTO emp3(id,sid) VALUES(1,20);
外键约束
- 基本定义
MySQL的外键约束用来在两个表数据之间建立链接,其中一张表的一个字段被另一张表中对应的字段约束。也就是说,设置外键约束至少要有两种表,被约束的表叫做从表(子表),另一张叫做主表(父表),属于主从关系。其中主表中的关联字段叫做主键,外表中的关联字段叫做外键。
主键中有的数据才能在从表中的外键中添加,否则会出错
- 代码实现
CONSTRAINT 索引名 FOREIGN KEY(本表的字段名) REFERENCES 建立约束的表的表名(建立约束的表的字段名)
- 注意事项
1、创建表时先创建主表再创建从表
2、添加数据时先往主表中添加数据再往从表中添加数据
3、删除数据时先删除从表中的数据再删除主表中的数据
- 举例说明
先创建主表再创建从表
#部门---主表
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,#部门id
dept_name VARCHAR(20)
);
#员工---从表
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,#员工id
last_name VARCHAR(15),
dept_id INT,
#添加外键约束
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES dept(dept_id)
);
#添加数据时先往主表中添加数据再往从表中添加数据,图一为部门表,图二为员工表
INSERT INTO dept(dept_id, dept_name) VALUES(10, 'HR');
INSERT INTO emp(last_name, dept_id) VALUES('libai', 10);
删除10号部门的所有员工
删除数据时先删除从表中的数据再删除主表中的数据,图一为员工表,图二为部门表
DELETE FROM emp WHERE emp_id = 1;
DELETE FROM dept WHERE dept_id = 10;
级联删除(ON DELETE CASCADE)–慎用
-
解释:
不用在删除数据时先删除从表中的数据再删除主表中的数据,可以直接删除主表和从表中的相关数据,
当主表中的列被删除时,从表中相应的列也被删除
-
代码定义:
CONSTRAINT 索引名 FOREIGN KEY(本表的字段名) REFERENCES 建立约束的表的表名(建立约束的表的字段名) ON DELETE CASCADE
- 例子说明
创建主表和从表
#部门---主表
CREATE TABLE dept1(
dept_id INT AUTO_INCREMENT PRIMARY KEY,#部门id
dept_name VARCHAR(20)
);
#员工---从表
CREATE TABLE emp1(
emp_id INT AUTO_INCREMENT PRIMARY KEY,#员工id
last_name VARCHAR(15),
dept_id INT,
#添加外键约束及级联删除
CONSTRAINT emp1_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES dept(dept_id) ON DELETE CASCADE
);
向主表和从表中添加数据,添加完后的主表如图一所示,从表如图二所示
删除10号部门的所有员工,此时会将主表和从表中10号部门的数据删除,主表如图一所示,从表如图二所示
DELETE FROM dept1 WHERE dept_id = 10;
创建表后添加约束(了解即可)
- 创建一个无任何约束的空表
CREATE TABLE student(
id INT,
sid INT,
NAME VARCHAR(20)
);
DESC student;#查看表的信息
主键约束(PRIMARY KEY)
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
—添加约束
ALTER TABLE 表名 MODIFY 字段名 类型 PRIMARY KEY;
—修改约束
ALTER TABLE 表名 DROP PRIMARY KEY;
—删除约束
举例说明
- 将student中的字段id添加主键约束
ALTER TABLE student ADD PRIMARY KEY(id);
DESC student;
- 删除student中的字段id的约束
ALTER TABLE student DROP PRIMARY KEY;
DESC student;
- 修改student中的字段id的约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;
DESC student;
唯一约束(UNIQUE)
ALTER TABLE 表名 ADD UNIQUE(字段名);
—添加约束
ALTER TABLE 表名 ADD CONSTRAINT 自己指定的索引名 UNIQUE(字段名);
—添加约束
ALTER TABLE 表名 MODIFY 字段名 类型 UNIQUE;
—修改约束
ALTER TABLE 表名 DROP INDEX 索引名;
—删除约束
注意:
1、使用第一种方法添加约束时,则在删除约束时的索引名默认为字段名
举例说明
- 将student中的字段sid添加唯一约束
ALTER TABLE student ADD UNIQUE(sid);
DESC student;
- 删除student中的字段sid的唯一约束
ALTER TABLE student DROP INDEX sid;
DESC student;