通过sql删除外键约束
-- alter table 表名 drop foreign key 外键名称 ;
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk;
CREATE TABLE employee (
字段名称1 字段类型1,
字段名称2 字段类型2,
....,
dept_id INT, -- 外键要作用的字段名称
-- 所在的部门编号
-- 声明
CONSTRAINT
外键名称 -- 外键名称:主表名_从表名_fk
FOREIGN KEY (外键作用的从表的字段名称) -- 外键作用在哪个字段上
REFERENCES -- 关联
主表名(主键字段)-- 主表的主键字段(就是id)
on update cascade
on delete cascade 通过sql 添加外键约束
alter table 表名 add
constraint 外键名称
foreign key (外键作用从表的字段名称)
references 主表名(主键字段) ;
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
-- 员工编号
NAME VARCHAR (10),
-- 员工姓名
gender VARCHAR (3),
-- 员工性别
salary DOUBLE,
-- 工资
dept_id INT,
-- 所在的部门编号
-- 声明
CONSTRAINT -- 外键名称:主表名_从表名_fk
dept_emp_fk -- 外键作用在哪个字段上
FOREIGN KEY (dept_id) -- 关联
REFERENCES -- 主表的主键字段(就是id)
dept(id)
ON UPDATE CASCADE -- 级联修改
ON DELETE CASCADE -- 级联删除
) ;
删除5号部门
DELETE FROM dept WHERE id = 5;
-- 将部门表id为2的部门修改为 id=4的部门
UPDATE dept SET id = 4 WHERE id = 2 ;ALTER TABLE employee
ADD CONSTRAINT de_em_fk FOREIGN KEY (dept_id) REFERENCES dept(id) ;
级联操作:CASCADE(修改以及删除主表数据,从表数据随之改变)
-- 级联删除和级联修改
-- 情况1:创建表的时候添加外键的同时后面添加级联删除以及级联修改
通过sql添加外键的同时,设置级联操作(级联修改和级联删除)
alter table 表名 add
constraint 外键名称
foreign key (外键作用从表的字段名称)
references 主表名(主键字段)
on update cascade
on delete cascade ;
表和表的关系问题
一般情况:两张表一个外键
两张表以上以上,可以设置多个外键
一对多
部门和员工的关系
从部门维度--->一个部门中包含多个员工
从员工维度---> 一个员工从属于某个部门
用户和订单
一个用户(用户维度):一个用户可以下多个订单
订单(订单维度):一个订单在某个时刻属于某个用户的
一个订单可以可以从属于多个用户的
多对多
学生表和选课表
一个学生可以选择多个课程
一个课程被多个学生选择
用户表和角色表 (用户表和角色表中间表)
从用户维度--看角色:一个用户有多个角色
从角色维度--看用户:一个角色赋予多个用户
角色表和权限表 (角色表和权限表中间表)
从角色表维度--看权限:一个角色可以多个权限
CEO:crud(添加,查询,修改和删除)
从权限维度---看角色:一个权限可以被多个角色赋予
查询权限:
CEO
hr
root管理员
普通管理员
数据库的三大范式:(理论)
1NF:保证数据库表中的每一列是不能在拆分的原子数据项(单独某一列)
-- 2NF:在1NF的基础上,数据库表中每一个非主键字段必须完全依赖主键字段
-- 1)一张表示描述一件事情
-- 2) 非主键字段必须完全依赖主键字段
-- 3NF:在2NF基础上,数据库表中每一个非主键字段不能产生传递依赖(必须在2NF上满足非主键字段必须完全依赖主键字段)
-- 表中A字段依赖于B字段,B字段依赖于C字段, ---->A字段依赖于C字段(字段冗余非常大了)
多表查询
笛卡尔乘积查询:
A表 3条记录
B表 5条件记录
不设置多张表的连接条件---->15条数据
内连接
隐式内连接---where 条件1 and 添加2 and 添加3....
显示内连接 表名1 (inner) join 表名2 on 连接条件
外连接
左外: left (outer) join
right (outer) join
子查询
select 嵌套 select
利用聚合函数--查询出来的单行单列的数据和比较运算符 (完成嵌套)
条件字段名称 in(嵌套selct...)
(select 指定字段列表 from 表名)----> 虚表 和另一种张进行连接条件
创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
NAME VARCHAR(20) -- 部门名称
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10), -- 员工名称
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) (省略constraint 外键名称)
) ;
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男
',7200,'2021-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男
',3600,'2022-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES
('唐僧','男',9000,'2020-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女
',5000,'2021-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女
',4500,'2021-11-14',1);
多表查询:
最基本的语法:
select 指定字段名称 from 表名1 ,表名2;
查询员工表中的id,姓名,性别,工资,入职日志以及部门表的部门名称
SELECT
emp.`id` '员工编号',
emp.`name` '员工姓名',
emp.`gender` '员工性别',
emp.`salary` '员工工资',
emp.`join_date` '入职日期',
dept.`name` '部门名称'
FROM
emp,
dept;
-- 问题: 员工表就5个人,查询出来15条数据---错误写法
-- 原因: 没有查询的时候,指定两张表的关联关系(存在外键),造成数据紊乱;
-- 迪卡尔乘积:两张表没有建立连接关系,A表的记录数 * B表的记录数----等于总记录数
-- .关系数据库中提供了多表查询之内连接---解决笛卡尔乘积问题多表查询的分析思路:
1)要查询哪张表
2)要查询指定表中的哪些字段名称
3)表和表关系问题 (需要建立连接条件)
内连接的隐式内连接:就是利用where语句完成条件 的拼接(&&,或者and关键字)
select 字段列表 from 表名1 ,表名2 where 连接条件1 and....连接条件2....
查询员工表中的id,姓名,性别,工资,入职日志以及部门表的部门名称
SELECT
e.`id` '员工编号',
e.`name` '员工姓名',
e.`gender` '性别',
e.`salary` '工资',
e.`join_date` '入职日期',
d.`name` '部门名称'
FROM
emp e,
-- 给表起一个别名
dept d -- d是部门表的别名
WHERE e.`dept_id` = d.`id` ;显示内连接 --inner join
select 字段列表 from 表名1 (inner) join 表名2 on 连接条件1 and 连接条件2.....
需求:查询员工表名的姓名,工资,入职日期以及部门表的部门名称
SELECT
t1.`name` '员工姓名',
t1.`salary` '工资',
t1.`join_date` '入职日期',
t2.`name` '部门名称'
FROM
emp t1
INNER JOIN dept t2
ON t1.`dept_id` = t2.`id` ;多表查询之外连接
-- 左外连接/右外连接
-- 一般比较常用的左外连接: 将左边的数据全部查询以及两张表之间的交集数据(连接条件的数据)全部查询!
select
指定字段列表
from 表名1
left outer join 表名2 / right outer join 右外连接
on 连接条件
需求:查询员工表的所有数据以及部门的部门名称数据,没有部门的员工信息也得查询
SELECT
t1.*, -- 查询员工的所有数据
t2.`id` '部门编号',
t2.`name` '部门名称' -- 部门名称
FROM
emp t1
LEFT OUTER JOIN
dept t2
ON
t1.`dept_id` = t2.`id` ; 多表查询里面的子查询 (就是select嵌套select语句)
-- 情况1:利用聚合函数查询出来的单列的数据和select语句进行嵌套
需求:查询最高工资的员工信息以及员工所在的部门信息
-- 1)查询出最高工资是多少
-- select max(salary) from emp ; -- 9000
-- 2)查询员工工资是9000的员工信息以及部门信息
/*
select
t1.* , -- 员工所有信息
t2.* -- 部门所有信息
from
emp t1,
dept t2
where
t1.`salary` = 9000 and t1.`dept_id` = t2.`id`;
*/
-- 一步走:
SELECT
t1.*,
-- 员工所有信息
t2.* -- 部门所有信息
FROM
emp t1,
dept t2
WHERE t1.`dept_id` = t2.`id`
AND t1.`salary` =
(SELECT
MAX(salary)
FROM
emp) ;数据库的事务
什么是数据库的事务?
事务就是DBA(数据库管理员)操作多个sql的时候,可能有一条执行语句可能异常了,其他sql不执行了,就会导致出现紊乱,
将整个sql(多个sql)的执行看成一个"整体",要么同时执行成功,要么同时执行失败!
始终保证的数据的一致性(高并发中,读/写)
创建一个账户表account
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id
NAME VARCHAR(10) , -- 账户名称
balance INT -- 账户余额
);
-- 插入两个账户信息
INSERT INTO account(NAME,balance) VALUES('zhangsan',1000),('lisi',1000) ;
-- zhangsan 给 lisi转账500
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
-- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
UPDATE account SET balance = 1000 ;
-- 将 "转账业务"看成一个整体,
-- 开启事务
START TRANSACTION ; -- 将自动提交切换为手动提交
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
-- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
-- 有问题回滚;事务回滚:撤销之前的所有操作,默认回滚到执行更新之前的数据
ROLLBACK ;
-- 没有问题,正常提交事务
COMMIT ; -- 不提交,不会永久性的更改数据---
传统事务特点(重点)ACID
-- 原子性:执行的某业务操作,同时操作多个sql(增删改),要么同时执行成功,要么同时执行失败!
-- 一致性:在实际开发中,多个线程同时去写和读的时候,数据必须一致性
-- 隔离性:事务和事务之间(业务和业务之间)相互独立的, 互不影响!
-- 持续性:就是对增删改这些sql语句,一旦事务提交了,对数据库中表的操作是永久性,即使关机了,数据永久保存!
传统事务的隔离级别:
查询数据库的隔离基本
SELECT @@tx_isolation; -- mysql8.0服务器版---SELECT @@transaction_isolation
-- 设置隔离级别
-- set global transaction isolation level (级别的名称) ;
-- 执行效率从大到小,安全性:从低到高
-- 读未提交:read uncommited;
一个事务读取到另一个没有没有提交的事务(最不安全),出现"脏读" (最严重的问题!)
-- 读已提交:read committed;
有效防止脏读! 会出现另一种问题"不可重复读",
读取到自己本身没有提交的事务的数据前后两次不一致,本身这个事务没有提交,多次读取到的数据必须一致!
-- 可重复读 :mysql的默认隔离级别 (repetable-read)
有效防止脏读,不可重复读,可能出现"幻读"
-- 串行话:serializable
jdbc入门
jdbc的本质:
就是数据库厂商提供的一个普通的java类------>实现sun公司提供的接口(java.sql.Driver)的类
口(java.sql.Driver)的类
//1)导入驱动包
//2)注册驱动
Class.forName("com.mysql.jdbc.Driver") ;//mysql-5.1xxxjar包
//DriverManager.registerDriver( new com.mysql.jdbc.Driver());
//3)准备sql语句
String sql = "insert into account(name,balance) values('文章',1000);" ;
//4)获取数据库的连接对象
//jdk提供了类DriverManager:管理jdbc服务的
/**
* public static Connection getConnection(String url, 统一资源定位符:连接的具体库
* String user, 用户名
* String password)登录mysql的密码
* throws SQLException
*/
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee2302_db_2",
//jdbc协议:数据库协议://域名:端口号/库名
//如果使用mysql8.0的jar包(mysql-server:安装的是8.0)此时后面带上一些参数:时区以及编码格式
//jdbc:mysql://localhost:3306/myee2302_db_2?characterEncoding=utf-8&timezone=utc
"root",
"123456");
//5)通过数据库的连接对象获取执行对象
//java.sql.Connnection接口---->Statement createStatement()
Statement stmt = conn.createStatement();
//6)执行对象执行sql
//java.sql.Statemnt--->通用方法:更新操作(插入/修改/删除)
//int executeUpdate(String sql)throws SQLException
int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
//7)释放资源
stmt.close() ;
conn.close();
}
}jdbc操作DDL语句,创建一张表 student
public class JdbcDemo2 {
public static void main(String[] args) {
//捕获异常
//1)导包
//2)注册驱动
Connection conn = null ;
Statement stmt = null ;
try {
Class.forName("com.mysql.jdbc.Driver") ;
//3)获取数据库连接对象
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee2302_db_2",
"root",
"123456");
//4)准备sql
String sql = "create table student(" +
"id int primary key auto_increment," +
"name varchar(10)," +
"age int," +
"gender varchar(3)," +
"address varchar(50));";
//5)通过连接对象获取执行对象
stmt = conn.createStatement();
//6)执行更新操作 DDL语句 executeUpdate(String sql)
int count = stmt.executeUpdate(sql);
System.out.println(count);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
if(stmt!=null){
try {
stmt.close() ;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close() ;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
Statement执行对象操作DML语句之修改操作
public class Statement_Operator_DML {
public static void main(String[] args) {
Connection conn = null ;
Statement stmt = null ;
try {
//获取数据库连接对象
conn = JdbcUtils.getConnection();
//准备sql
String sql = "update student set name = '赵又廷',age = 35,address='西安市南窑国际' where id = 1;" ;
//获取执行器对象Statement
stmt = conn.createStatement();
//执行sql
int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.close(stmt,conn);
}
}
}
使用Statment执行对象来操作DQL语句,并去将所有的结果集数据表打印在控制台上
public class Statement_DQL {
public static void main(String[] args) {
Connection conn = null ;
Statement stmt = null ;
ResultSet rs = null ;
try {
//注册驱动并获取连接对象
conn = JdbcUtils.getConnection() ;
//准备sql语句
String sql = "select * from student;" ;
//通过连接对象获取执行对象,准备发送sql到数据库
stmt = conn.createStatement() ;
//执行通用的查询操作
rs = stmt.executeQuery(sql) ;
//第一次获取
System.out.println("编号\t姓名\t\t\t年龄\t性别\t地址") ;
//有下一行数据获取
//boolean next() ;
/* if(rs.next()){
//列的索引值获取
int id = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
String gender = rs.getString(4);
String address = rs.getString(5);
System.out.println(id+"\t"+name+"\t\t"+age+"\t"+gender+"\t"+address);
}
//第二次获取
//通过列的名称获取
if(rs.next()){
int id = rs.getInt("id") ; //id名称
String name = rs.getString("name") ; //name名称
int age = rs.getInt("age") ; //age名称
String gender = rs.getString("gender") ; //gender
String address = rs.getString("address") ; //address
System.out.println(id+"\t"+name+"\t\t"+age+"\t"+gender+"\t"+address);
}
//第三次获取
//通过列的名称获取
if(rs.next()){
int id = rs.getInt("id") ; //id名称
String name = rs.getString("name") ; //name名称
int age = rs.getInt("age") ; //age名称
String gender = rs.getString("gender") ; //gender
String address = rs.getString("address") ; //address
System.out.println(id+"\t"+name+"\t\t\t"+age+"\t"+gender+"\t"+address);
}*/
//当不明确多少个结果,while循环
while(rs.next()){
//有数据,获取
int id = rs.getInt("id") ; //id名称
String name = rs.getString("name") ; //name名称
int age = rs.getInt("age") ; //age名称
String gender = rs.getString("gender") ; //gender
String address = rs.getString("address") ; //address
System.out.println(id+"\t"+name+"\t\t\t"+age+"\t"+gender+"\t"+address);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.close(rs,stmt,conn);
}
}
}
什么是视图?
就是一个虚拟表:只是定义视图,表的数据来源于基础表中的(数据库中真实存在表)
-- 作用:保证数据的安全性/简单性(快速查看到真实表的结构,类型....)
查看本身这个表的数据内容
SELECT * FROM employee ;
-- 视图的基础语法:如何创建视图,修改实体,更新视图等等
-- 创建视图
-- CREATE VIEW <视图名> AS <SELECT语句>
CREATE VIEW view_employee AS
SELECT
id,
NAME,
gender,
age,
salary,
address,
birthday
FROM
employee ;
-- 查看视图的内容
SELECT * FROM view_employee ;
-- 查看视图的结构---查询表的结构语法相同
-- 查询基础表(employee) 它的结构
DESC employee ;
-- 查询指定视图的结构
DESCRIBE view_employee ; -- 简写成功desc 视图名称;
-- 查询当前库中的所有表以及视图的名称
SHOW TABLES ;
-- 查询当前库中的所有表以及视图的名称以及后面的table_type:表类型
SHOW FULL TABLES ;
-- 修改视图的内容
-- ALTER VIEW <视图名> AS <SELECT语句>
ALTER VIEW view_employee
AS
SELECT
id,NAME,salary FROM employee ;
-- 更新视图(视图添加数据/修改数据/删除数据,会直接影响基础表的内容,
-- 一般很少去使用视图去更新基础表的内容)
UPDATE view_employee SET NAME = '姚笛' WHERE id = 2 ;
SELECT * FROM view_employee ;
-- 删除视图:就是drop view 视图名称,多个视图名称;
-- 只是的删除视图的定义,不会影响基础表的任何内容
DROP VIEW view_employee ;
-- 查询employee基础表的内容
SELECT * FROM employee ;
-- 重命名视图跟表的重命名语法一样
-- renname table 视图名 to 新视图名;
CREATE VIEW my_view AS SELECT * FROM employee ;
SELECT * FROM my_view ;
RENAME TABLE my_view TO view_emp;
面试题:
Statement和PreparedStatement的区别?
```markdown
1)共同点:
都是可以发送sql到数据库的,都是执行对象,后者继承前者(java.sql.包下的接口)
2)不同点:
2.1)是否会造成sql注入
Statement永远执行的是静态sql语句:语句中存在"硬编码",存在SQL的字符串拼接,就造成sql注入,不安全!
PreparedStatement:永远执行的是参数化的sql语句,全部参数都是"?",占位符号,有效防止sql的拼接,预防sql注入,提高执行sql的安全性!
2.2)是否会提高sql执行效率
Statement:不会提高sql执行效率,
获取执行对象,然后将sql语句发送数据库(没写一个sql,发一次!),频繁的操作访问数据库
PreparedStatement:大大提高SQL执行效率
参数化sql是在获取PreparedStatement,就已经发送给数据库了,然后在PreparedStatement赋值不同的值;
使用Statement的弊端
```java
使用Statement完成用户登录---------->有一张user表
id username password
键盘录入用户名和密码,
User类:private int id;
private String username;
private String password;
定义一个UserDao接口---->boolean isLogin(String username,String password) ;
userDaoImpl接口实现类----->实现这个业务
select * from user where username = 'xxx' and passwod='xxxx' ;
纯sql拼接会出现安全漏洞!
select * from user where username = 'xxx' and passwod='xxxx' or '1'='1' ;
如果查询到了数据---->结果集对象.next()--->返回true
--- 效率低
--- 造成sql注入(用户名和随便输入,依然登录成功)
开发中使用:PreparedStatement---执行预编译sql语句
select * from user where username = ? and passwod=? ; //? 占位符符号
Druid连接池的使用步骤:
1)导包 druid-1.1.10.jar包
* 2)准备好连接池的配置文件
* 3)从连接池获取连接对象
* 提供jar包--->com.alibaba.druid.pool.DruidDataSource--->本质实现了一个接口javax.sql.DataSource
* --->Connection getConnection()
*
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//3)创建数据源DruidDataSource对象
//德鲁伊提供的数据源工厂:DruidDataSourceFactory提供静态方法,返回值就是DataSource
//public static DataSource createDataSource(Properties properties)
//创建属性集合列表
Properties prop = new Properties() ;
//读取src下配置文件
InputStream inputStream = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
//将字节输入流加载进来
prop.load(inputStream) ;
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
for(int x = 1 ; x <=11 ; x++){
//System.out.println(dataSource.getConnection());
Connection conn = dataSource.getConnection() ;
if(x==3){
conn.close(); //暂时释放,归还连接池中
}
System.out.println(conn);
}
/**
* 上面这个方法底层已经集成好了
* public static DataSource createDataSource(Map properties) throws Exception {
* DruidDataSource dataSource = new DruidDataSource(); //创建德鲁伊的数据源
* config(dataSource, properties); //封装数据:将配置文件的内容封装到dataSource数据源对象汇总
* return dataSource;//返回
* }
*/
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
