- 今日收获:
约束
约束概述
作用
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类
单表:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
多表:
4. 外键约束:foreign key
非空约束
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
2. 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束
1. 在创建表时,添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号
);
2. 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
3. 在表创建完后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主键约束
1. 在创建表时,添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
2. 删除主键
-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
3. 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
主键自动增长
1. 在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
2. 删除自动增长
ALTER TABLE stu MODIFY id INT;
3. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束
数据准备
-- 创建部门表(id,dep_name,dep_location)
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
1. 在创建表时,可以添加外键
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
为什么要跟主键关联
主键是唯一并且非空的,根据主键就可以确定一条唯一的记录
先写部门表,在写员工表
主表:被引入外键的表 部门表
从表:引入外键的表 员工表
事务
概念
多条sql语句完成一个业务需求,该业务包含的sql语句,要么同时成功,要么同时失败。
操作:
1. 开启事务:start transaction;
2. 回滚:rollback;
3. 提交:commit;
案例:
//创建账户表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
//添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
//开启事务
START TRANSACTION;
//1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
//2. 李四账户 +500
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
//发现执行没有问题,提交事务
COMMIT;
应用
图书管理系统
1,系统读书,扫描书的二维码,并且录入用户的id
2,系统把书的状态变成借出
3,在图书-读者表中,增加该书和该读者的id
4,读者的借书数量+1;
执行了1,2之后,发生了异常,就会出现书被借出,但是没有人借走,用事务绑定这个几个动作,一旦出现异常,就直接回滚
提交
事务提交的两种方式:
* 自动提交:
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交:
* Oracle 数据库默认是手动提交事务
* 需要先开启事务,再提交
修改事务的默认提交方式:
* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式: set @@autocommit = 0;
特征
1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3. 隔离性:多个事务之间。相互独立。
4. 一致性:事务操作前后,数据总量不变
jdbc入门
用java语言来操作数据库
jdbc
sun公司提供给开发人员操作关系型数据库的API(一些类和接口)
DriverManager类
Connection
Statement
ResultSet
驱动:两个设备之间通信的桥梁
数据库驱动:jdbc的实现类,由数据库厂商提供
面向接口编程
通过jdbc里面的接口,从驱动中取出实现类,调用实现类的重写方法,由实现类来操作数据库
可以帮助我们屏蔽数据库之间的差异 Connection c = div.getConnection()
快速入门
1,注册驱动
2,获得连接
3,操作sql
编写sql语句
获取执行sql语句的对象
执行sql
处理结果
4,释放资源
//1,注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,获得连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
//3,操作sql
//编写sql语句
String sql = "update account set balance = 2000 where id = 1";
//获取执行sql语句的对象
Statement statement = conn.createStatement();
//执行sql
int count = statement.executeUpdate(sql);
// 处理结果
System.out.println(count);
//4,释放资源
statement.close();
conn.close();
各个接口或类的作用
DriverManager
注册驱动
获取连接
Connection
获取连接
管理事务
Statement
执行sql(executeUpdate 和 executeQuery)
多表关系
实体与实体之间的关系
一对多
建表原则:在多的一方建立外键指向一的一方的主键
一对一
建表原则:在任意一方添加唯一的外键指向另一方的主键
多对多 商品和订单
建表原则:要有中间表,至少有两个字段,分别作为外键指向两张表的主键
多表查询
交叉查询
内连接查询
外连接查询
子查询
#交叉查询:查的是两张表的所有组成情况
SELECT * FROM department,employee
#内连接查询:查的是两张表的公共部分
#1,隐式内连接(没有调用inner join关键字)
SELECT * FROM department,employee WHERE department.id = employee.dep_id
#2,显式内连接(调用inner join关键字)
SELECT * FROM department INNER JOIN employee ON department.id = employee.dep_id
#外连接查询
#1,左外连接:左边表的全部和两张表的交集
SELECT * FROM department LEFT JOIN employee ON department.id = employee.dep_id
SELECT * FROM department LEFT OUTER JOIN employee ON department.id = employee.dep_id
#2,右外连接:右边表的全部和两张表的交集
SELECT * FROM department RIGHT JOIN employee ON department.id = employee.dep_id
SELECT * FROM department RIGHT OUTER JOIN employee ON department.id = employee.dep_id
#子查询:sql语句嵌套sql语句
-- 结果是单行单列的:可以把子查询作为条件,使用运算符去判断(>,<,=)
#年龄最大的员工的信息
#先查最大的年龄
SELECT MAX(age) FROM employee
#再查员工年龄等于最大年龄的员工
SELECT *FROM employee WHERE age = ( SELECT MAX(age) FROM employee)
-- 结果是单行多列的:可以把把子查询作为条件,使用in,any,all,exist
#查询研发部和销售部的员工信息
#先查研发部和销售部的id
SELECT id FROM department WHERE dep_name='研发部' OR dep_name='销售部'
#再在员工表中找到符合对应id的员工
SELECT * FROM employee WHERE dep_id IN (SELECT id FROM department WHERE dep_name='研发部' OR dep_name='销售部')
-- 结果是多行多列的:可以把子查询作为一张虚拟的表参与查询
#查询员工年龄大于20岁的员工信息和部门信息
#先查询员工年龄大于20岁的员工信息
SELECT * FROM employee WHERE age > 20;
#再将查到的员工信息作为一张表,和部门表进行查询
SELECT * FROM department t1,(SELECT * FROM employee WHERE age > 20) t2 WHERE t1.id = t2.dep_id