MySQL数据库（二：外键 约束 主键约束 非空非负唯一 级联更新与删除 内连接 外连接 子查询 多对多 存储过程 函数 视图 触发器 权限与账户 远程登录 表的设计规范 ）
文章目录
约束
作用:
为了保证数据的有效性和完整性
mysql中常用的约束:
主键约束(primary key)
自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用
唯一约束(unique)
非空约束(not null)
外键约束(foreign key)
sex ENUM('男','女') -- 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果
unsigned 不允许插入负数的约束
TINYINT -128--127带符号的范围是-128到127 无符号的范围是0到255
-- 约束：
-- 主键约束：PRIMARY KEY 非空且唯一，一张表中只能有一个主键 （联合主键）
-- 自增长约束:AUTO_INCREMENT 配合整数类型的字段，而且该字段是主键 也就是主键自增长。
-- 唯一约束 UNIQUE
-- 非空 约束
-- 外键约束：FOREIGN KEY REFERENCES
-- 非负约束 unsigned
-- enum 枚举类型
- 主键约束
CREATE TABLE san9 (
id PRIMARY KEY,
-- 不允许负数值 这个值的范围0-255
tname VARCHAR (10),
tage INT
) ;
- 自增长约束
-- 自增长约束 针对整数型的字段 并且这个字段是主键
-- 自增长约束要配合主键来使用
CREATE TABLE san3 (
id INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR (10)
) ;
INSERT INTO san3 (tname)
VALUES
('x0') ;
INSERT INTO san3 (id, tname)
VALUES
(NULL, 'x1') ;
- 非负约束
-- 非负约束
CREATE TABLE san1 (
id TINYINT UNSIGNED,
-- 不允许负数值 这个值的范围0-255
tname VARCHAR (10),
tage INT
) ;
INSERT INTO san1
VALUES
(55, 'x0', 10) ;
- 枚举约束
-- enum 列的数据类型 可以起到约束作用
CREATE TABLE san2 `san2` (
id TINYINT UNSIGNED,
-- 不允许负数值 这个值的范围0-255
tname VARCHAR (10),
sex ENUM ('男', '女') -- 枚举类型 只能插入列举的值
) ;
INSERT INTO san2
VALUES
(0, 'x0', '男') ;
INSERT INTO san2
VALUES
(1, 'x1', '女') ;
- 唯一约束
CREATE TABLE san9 (
id UNIQUE,
-- 不允许负数值 这个值的范围0-255
tname VARCHAR (10),
tage INT
) ;
- 非空约束
CREATE TABLE san9 (
id NOT NULL,
-- 不允许负数值 这个值的范围0-255
tname VARCHAR (10),
tage INT
) ;
外键约束
- 引出
-- 表和表之间的关系
-- 实体与实体之间的关系
-- 要做一个项目 项目肯定会有多张表 表和表之间会有一些关系
-- 商城项目 要的数据模型 用户 商品 订单
-- 用JAVA做的话
-- class USER{}
-- class PRODUCTS{}
-- class ORDER{}
-- 数据库中一个实体类对应一张表
-- 实体和实体之间对应关系
-- 一对一
-- 一对多
-- 多对多
-- 设计表时 我们要考虑周全
-- 画出ER图
- 概念
-- 外键约束：为了数据的有效性和完整性 添加外键约束 可以让两张表之间相互制衡 来避免不合理的操作
-- 如何添加外键约束
-- 一般 我们在多表一方 添加外键约束 去关联（主表，单标）一方的主键
-- 添加外键约束的特点：主表一方 不能删除 从表还在引用的数据 多表一方 不能添加 主表没有描述的数据
- 添加外键约束的方式
方式一
CREATE TABLE userx (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (10)
) ;
CREATE TABLE orderx (
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE,
user_id INT -- 外键 可以看出两个实体之间的对应关系
) ;
-- 添加外键约束
ALTER TABLE orderx ADD FOREIGN KEY (user_id) REFERENCES userx(id);
方式二
CREATE TABLE userx1 (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (10)
) ;
CREATE TABLE orderx1(
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE,
user_id INT , -- 外键 可以看出两个实体之间的对应关系
FOREIGN KEY (user_id) REFERENCES userx1(id)
) ;
- 删除 先删除订单 再删除用户
删除主键约束
删除主键约束 分两步
-- 1：删除唯一 还有非空
-- 2：删除非空
ALTER TABLE test DROP PRIMARY KEY;
ALTER TABLE test MODIFY tname VARCHAR(15) NULL;
多对多查询
-- 多对多
-- 对于 多对多的关系 我们要引入一张中间表 进行描述 方便查询
-- 引入中间表 将多对多 转换成一对多 中间作为了多表
-- 订单表与商品表
-- 一个订单里面包含多个商品
-- 一个商品可以属于多个订单
CREATE TABLE userc (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (10)
) ;
CREATE TABLE orderc(
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE,
user_id INT -- 外键 可以看出两个实体之间的对应关系
) ;
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (10),
cprice INT
) ;
CREATE TABLE midx(
oid INT,
pid INT
) ;
-- 用户表和订单表
ALTER TABLE orderc ADD FOREIGN KEY(user_id) REFERENCES userc(id);
ALTER TABLE midx ADD FOREIGN KEY(oid) REFERENCES orderc(id);
ALTER TABLE midx ADD FOREIGN KEY(pid) REFERENCES product(id);
级联更新与级联删除
- 级联删除：我们直接删除单表中 从表还在引用的数据 默认删除不了
-- 级联删除：我们直接删除单表中 从表还在引用的数据 默认删除不了
CREATE TABLE userx2 (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (10)
) ;
CREATE TABLE orderx2(
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE,
user_id INT , -- 外键 可以看出两个实体之间的对应关系
-- 方式二：建表的时候加上级联删除
FOREIGN KEY (user_id) REFERENCES userx1(id) ON DELETE CASCADE
) ;
-- 级联更新
CREATE TABLE userx3 (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (10)
) ;
CREATE TABLE orderx3(
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE,
user_id INT , -- 外键 可以看出两个实体之间的对应关系
-- 方式二：建表的时候加上级联删除
FOREIGN KEY (user_id) REFERENCES userx3(id) ON DELETE CASCADE ON UPDATE CASCADE
) ;
多表查询
- 多表查询 将多张表联合起来进行查询
SELECT userc.*,orderc.* FROM userc,orderc;
这种查询叫做 笛卡尔积 只是简单的将多张表关联起来 进行查询 查出的数据没有意义
-- 笛卡尔积：多张表的列数相加 行数相乘得到的结果
-
多表查询：
- 内连接：不符合条件的数据，不展示
- 隐式内连接：select a.,b. from a,b where 关联条件
- 显示内连接：select a.,b. from a inner join b on 关联条件
-
外连接：左外连接和右外连接 select a.,b. from a left outer join b on 关联条件
-
子查询：一个主查询的条件，来自于另一条查询的结果。
-
自查询：在一张表中进行查询，把一张表看做两张表来查询。
内连接
- 隐式内连接：select a.,b. from a,b where 关联条件
- 显示内连接：select a.,b. from a inner join b on 关联条件
1.查询所有用户的订单信息
-- 内连接 不符合条件的数据不展示
-- 隐式内连接
SELECT userc.*,orderc.* FROM userc,orderc WHERE userc.`id`=orderc.`user_id`;
-- 显式内连接
SELECT userc.*,orderc.* FROM userc INNER JOIN orderc ON userc.`id`=orderc.`user_id`;
-- 给表起别名 as
SELECT userc.id AS 编号,userc.`username` AS 姓名,orderc.* FROM userc INNER JOIN orderc ON userc.`id`=orderc.`user_id`;
编号 姓名 id price user_id
1 银灰 1 1700 1
2 夜莺 2 1300 2
3 大叔 3 1100 3
4 小九 4 400 4
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
外连接
-
左外连接：用left来区分左右
SELECT userc.*,orderc.* FROM userc LEFT OUTER JOIN orderc ON userc.`id`=orderc.`user_id`;id username id price user_id 1 银灰 1 1700 1 2 夜莺 2 1300 2 3 大叔 3 1100 3 4 小九 4 400 4 5 陈蕊 8 1300 5 5 陈蕊 9 2100 5 6 塞雷亚 \N \N \N -
右外连接
SELECT userc.*,orderc.* FROM userc RIGHT OUTER JOIN orderc ON userc.`id`=orderc.`user_id`;id username id price user_id 1 银灰 1 1700 1 2 夜莺 2 1300 2 3 大叔 3 1100 3 4 小九 4 400 4 5 陈蕊 8 1300 5 5 陈蕊 9 2100 5 \N \N 10 9999 \N -
左外连接和右外连接 可以互换 查出同样的结果
SELECT userc.*,orderc.* FROM userc LEFT OUTER JOIN orderc ON userc.`id`=orderc.`user_id`;
SELECT userc.*,orderc.* FROM orderc RIGHT OUTER JOIN userc ON userc.`id`=orderc.`user_id`;
id username id price user_id
1 银灰 1 1700 1
2 夜莺 2 1300 2
3 大叔 3 1100 3
4 小九 4 400 4
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
6 塞雷亚 \N \N \N
子查询
-- 子查询
-- 一个主查询他所需要的条件 来自另外一个查询 主要解决的是不能一步查出来的 分好几步查询
-- 1. 查询用户为张三的订单详情
-- one：在用户表查询张三的id
-- two：拿着id去订单表里查询
SELECT id FROM userc WHERE username = '陈蕊' ;
查询结果：
id
5
SELECT * FROM orderc WHERE user_id = 5 ;
查询结果：
id price user_id
8 1300 5
9 2100 5
-- 合二为一
-- 主查询的 条件 来自另外一个查询的结果
-- 语法：注意 子查询要用括号括起来
SELECT
*
FROM
orderc
WHERE user_id =
(SELECT
id
FROM
userc
WHERE username = '陈蕊') ;
查询结果：
id price user_id
8 1300 5
9 2100 5
-- 加上姓名等用户信息
-- 把临时查询出来的表 作为一张临时表和其他表再关联起来查询
SELECT
userc.id AS 编号,
userc.username AS 姓名,
sanwa.id AS 订单标号,
sanwa.price AS 价格,
sanwa.user_id AS 外键编号
FROM
userc,
(SELECT
*
FROM
orderc
WHERE user_id =
(SELECT
id
FROM
userc
WHERE username = '陈蕊')) AS sanwa
WHERE userc.`id` = sanwa.`user_id` ;
查询结果：
编号 姓名 订单标号 价格 外键编号
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
-- 查询订单表中价格大于1300的用户信息
-- 1.查询订单表中价格大于1300的user_id
-- 2.用查询出来的user_id在用户表里查询
SELECT
user_id
FROM
orderc
WHERE orderc.`price` > 1300 ;
user_id
1
5
\N
SELECT
*
FROM
userc
WHERE id = 1
OR id = 5 ;
id username
1 银灰
5 陈蕊
-- 合二为一
SELECT
*
FROM
userc
WHERE id IN
(SELECT
user_id
FROM
orderc
WHERE orderc.`price` > 1300) ;
id username
1 银灰
5 陈蕊
-- 加上用户订单信息
SELECT
sanwa.id AS 编号,
sanwa.username AS 姓名,
orderc.id AS 订单编号,
orderc.price AS 价格,
orderc.user_id AS 外键
FROM
orderc,
(SELECT
*
FROM
userc
WHERE id IN
(SELECT
user_id
FROM
orderc
WHERE orderc.`price` > 1000)) AS sanwa
WHERE sanwa.id=orderc.`user_id` ;
编号 姓名 订单编号 价格 外键
1 银灰 1 1700 1
2 夜莺 2 1300 2
3 大叔 3 1100 3
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
有条件的多表查询
-- 复制表中的数据与字段到领一张表
-- 复制表头与数据
CREATE TABLE sansan AS
SELECT
*
FROM
userc ;
-- 复制部分数据
CREATE TABLE sansan0 AS
SELECT
userc.`username`
FROM
userc ;
-- 复制表头 不复制数据 条件给false
CREATE TABLE sansan1 AS
SELECT
*
FROM
userc
WHERE 1 = 2 ;
-- 复制表头 不复制数据 条件给true
CREATE TABLE sansan2 AS
SELECT
*
FROM
userc
WHERE 1 = 1 ;
-- 可以复制多表子查询的结果表
CREATE TABLE sansan3 AS SELECT
sanwa.id AS 编号,
sanwa.username AS 姓名,
orderc.id AS 订单编号,
orderc.price AS 价格,
orderc.user_id AS 外键
FROM
orderc,
(SELECT
*
FROM
userc
WHERE id IN
(SELECT
user_id
FROM
orderc
WHERE orderc.`price` > 1000)) AS sanwa
WHERE sanwa.id = orderc.`user_id` ;
username
银灰
夜莺
大叔
小九
陈蕊
塞雷亚
id username
1 银灰
2 夜莺
3 大叔
4 小九
5 陈蕊
6 塞雷亚
编号 姓名 订单编号 价格 外键
1 银灰 1 1700 1
2 夜莺 2 1300 2
3 大叔 3 1100 3
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
-- 复制表中的字段和数据到另一张表中
-- 我创建一张表，这张表中的字段和数据，是来自一条查询语句。
CREATE TABLE myemp AS SELECT * FROM emp;
--
CREATE TABLE myemp2 AS SELECT emp.`empno`,emp.`ename`,emp.`job` FROM emp;
-- 我只想复制表头，不要表中的数据
CREATE TABLE myemp3 AS SELECT * FROM emp WHERE FALSE;
CREATE TABLE myemp5 AS SELECT * FROM emp WHERE TRUE;
-- 我只想复制表头，不要表中的数据 条件给假
CREATE TABLE myemp3 AS SELECT * FROM emp WHERE 1=2;
-- 我只想复制表头，还要表中的数据 条件给真
CREATE TABLE myemp5 AS SELECT * FROM emp WHERE 1=1;
-- 可以复制多表查询出来的结果
SELECT user.*,orders.id AS oid,orders.`totalprice` FROM USER,orders WHERE user.`id`=orders.`user_id`;
CREATE TABLE myuser AS SELECT user.*,orders.id AS oid,orders.`totalprice` FROM USER,orders WHERE user.`id`=orders.`user_id`;
创建一张表,表的字段和数据来一条查询语句
语法：create table 表名 as select * from 另一张表 where 1=1; -- 创建一张表会把另一张表中的字段和对应的数据全部复制过去
如果我们只想要字段，不要数据
create table 表名 as select * from 另一张表 where 1=2;
如果我只想还要个别字段
create table 表名 as select 表名.字段名，表名.字段名2 from 另一张表 where 1=2;
我创建的这张表也可以来自一个子查循
create table 表名 as 子查询
存储过程 procedure
-
概念：
- 类似于JAVA中 可以封装一段sql语句 并且是带有逻辑性 可以实现一个功能
- 运行在服务器 可以重复调用
存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似与我们Java中的方法` -
语法
DELIMITER $$ CREATE PROCEDURE `performance_schema`.`myTestPro`() BEGIN END$$ DELIMITER ; 注意：创建存储过程需要管理员分配权限 补充：delimiter是mysql定义结束标记的，在mysql客户端中结束标记默认是分号（；）。 如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的结束符。 delimiter $$ 表示mysql用$$表示mysql语句结束，过程结束后肯定会有一句delimiter ； 表示恢复成默认的。 例子： DELIMITER $$ CREATE PROCEDURE san () BEGIN -- 封装sql语句 DELETE FROM san0 WHERE id = - 55 ; DELETE FROM san0 WHERE id = - 1 ; END $$ DELIMITER ; 调用存储过程 CALL san() -
参数
in:输入参数 out：输出参数 inout：输入输出参数例1：in out DELIMITER $$ CREATE PROCEDURE `wa`.`wa2` (IN e INT,OUT i VARCHAR(20) ) BEGIN IF e=1 THEN SET i='星期一'; ELSEIF e=2 THEN SET i='星期二'; ELSEIF e=3 THEN SET i='星期三'; ELSEIF e=4 THEN SET i='星期四'; ELSEIF e=5 THEN SET i='星期五'; ELSEIF e=6 THEN SET i='星期六'; ELSEIF e=7 THEN SET i='星期七'; END IF; END $$ DELIMITER ; -- 定义变量 SET @ss2=0; CALL wa2(2,@ss2); SELECT @ss2; 运行结果： @ss2 星期二例2： DELIMITER $$ CREATE PROCEDURE `wa`.`wa` (IN e INT,OUT i INT ) BEGIN DELETE FROM san3 WHERE id = e ; SELECT COUNT(*) FROM san3 INTO i; END $$ DELIMITER ; -- 定义变量 SET @ss=0; CALL wa(22,@ss); SELECT @ss; DELIMITER $$ CREATE PROCEDURE `wa`.`wa0` (INOUT i INT ) BEGIN DELETE FROM san3 WHERE id = i ; SELECT COUNT(*) FROM san3 INTO i; END $$ DELIMITER ; -- 定义变量 SET @sos=9; CALL wa0(@s0s); SELECT @sos; -
调用存储过程 call
语法：call myTestPro(9527,@rr) 查询结果： select @rr -
删除存储过程 drop
语法： drop procedure myTestPro; -
查看存储过程 show
show procedure status\G; -- 查看所有的存储过程状态 show create procedure 存储过程名字\G; -- 查看创建存储过程的语句 -
带有IF逻辑的存储过程 if then elseif else
DELIMITER $$ CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20)) BEGIN IF num=1 THEN SET str='星期一'; ELSEIF num=2 THEN --注意elseif 连在一块 SET str='星期二'; ELSEIF num=3 THEN SET str='星期三'; -- 注意要用分号结束 ELSE SET str='输入错误'; END IF; -- 注意要结束if 后面有分号 END $$ -
带有循环的存储过程 while do
DELIMITER $ CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT) BEGIN -- 定义一个局部变量 DECLARE i INT DEFAULT 1; DECLARE vsum INT DEFAULT 0; WHILE i<=num DO SET vsum = vsum+i; SET i=i+1; END WHILE; -- 要记得结束循环 SET result=vsum; END $ -
其他循环的写法
例如： REPEAT 循环 DELIMITER $ CREATE PROCEDURE proRepeat(OUT outnum INT) BEGIN DECLARE i INT DEFAULT 0; REPEAT SET i=i+1; UNTIL i>=5 -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件 END REPEAT; -- 记着结束 SET outnum=i; END $ CALL proRepeat(@num); SELECT @num; 例如：loop 循环 DELIMITER $$ CREATE PROCEDURE `mydemo3`.`proLoop`(OUT outnum INT) BEGIN DECLARE i INT DEFAULT 0; myloop:LOOP -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错 SET i=i+1; IF i>=5 THEN LEAVE myloop; -- 根据我的循环标号中断循环 leave 是中断循环的意思 END IF; END LOOP; SET outnum=i; END$$ DELIMITER ; CALL proLoop(@num); SELECT @num; -
控制循环的两个关键字
leave 相当于java中的 breakiterate相当于java中的continue
MySQL变量
-- mysql的变量
-- 全局变量 也叫内置变量 mysql安装好之后 这些变量就存在了
-- 会话变量：建立会话之后 然后定义的变量 会话变量的作用域只存在这次会话当中
-- 会话：客户端和mysql服务端建立的依次连接 称之为会话
-- 局部变量：定义在存储过程中 或者自定义函数中的变量称之为局部变量
-- 查看所有的内置变量
SHOW VARIABLES ;
-- 查看某个内置变量
SELECT @@version ;
查询结果：
@@version
5.5.27
SELECT @@character_set_client ;
查询结果：
@@character_set_client
utf8
SET @@character_set_client = utf8 ;
SET @@character_set_client = gbk ;
全局变量（内置变量）：可以在多个会话中去访问他
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码
SET @@character_set_client=gbk –设置数据库编码
set @@character_set_results=gbk
SET @@character_set_client=utf8; –设置数据库编码
set @@character_set_results=utf8;
SELECT @@character_set_client --查看数据库编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
-- 定义会话变量: set @变量=值
-- 查看会话变量： select @变量
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
定义局部变量的语法：DECLARE i INT DEFAULT 1;
给变量设置值 set i=10;
触发器 Trigger
-
概念
触发器：数据库中的一个对象，相当于JS中的监听器，触发器可以监听 增 删 改 三个动作 比如说我想监听一张表，只要我增删改了这张表中的数据，我就可以触发这个触发器，去往另外一张表中记录一下日志 -
语法
DELIMITER $$ CREATE TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE ON `mytestdb`.`<Table Name>` FOR EACH ROW BEGIN END$$ DELIMITER ; BEFORE 行为发生之前就触发 AFTER 行为发生之后触发 FOR EACH ROW 行级触发，每操作一行就触发 -
简单演示
-- 删除监听 DELIMITER $$ CREATE TRIGGER `wa`.`san` AFTER DELETE ON `wa`.`san3` FOR EACH ROW BEGIN -- 当你删除了san3表中的数据 就会触发这个触发器 当触发了出触发器 在此进行操作 INSERT INTO jian(id,content,timex) VALUES (NULL,'删除了一条数据',NULL); END$$ DELIMITER ; id content timex 1 删除了一条数据 2020-10-10 11:19:06 2 删除了一条数据 2020-10-10 11:19:09 3 删除了一条数据 2020-10-10 11:19:12 4 删除了一条数据 2020-10-10 14:43:24 5 删除了一条数据 2020-10-10 14:43:37 6 一树梨花压海棠 2020-10-10 15:00:56-- 增加监听 DELIMITER $$ CREATE TRIGGER `wa`.`wa` AFTER INSERT ON `wa`.`san3` FOR EACH ROW BEGIN -- 当你删除了san3表中的数据 就会触发这个触发器 当触发了出触发器 在此进行操作 INSERT INTO jian1(id,content,timex) VALUES (NULL,'增加了一条数据',NULL); END$$ DELIMITER ; id content timex 1 增加了一条数据 2020-10-10 11:23:14 2 增加了一条数据 2020-10-10 11:23:19 3 增加了一条数据 2020-10-10 11:23:25
两张表的监听
-
old和new
old.字段 可以获取到被监听的表中的字段的旧值 new.字段 可以获取到被监听表中更新后的字段的新值 比如插入新值或者修改旧值 例如：我往一张表t1中添加一条数据，另一张表t2也要添加一条同样的数据 DELIMITER $$ CREATE TRIGGER `mytestdb`.`myTri6` AFTER INSERT ON `mytestdb`.`t1` FOR EACH ROW BEGIN INSERT INTO t2 VALUES(new.id,new.username,new.age); END$$ DELIMITER ; 例如：我修改一张表t1中的数据，另一张表t2中的数据也要修改 DELIMITER $$ CREATE TRIGGER `mytestdb`.`MyTri7` AFTER UPDATE ON `mytestdb`.`t1` FOR EACH ROW BEGIN UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id; END$$ DELIMITER ;两张表的监听
- 数据增加监听
-- 两张表的数据增加监听 DELIMITER $$ CREATE TRIGGER `wa`.`wa0` AFTER INSERT ON `wa`.`san0` FOR EACH ROW BEGIN -- 当你删除了san3表中的数据 就会触发这个触发器 当触发了出触发器 在此进行操作 INSERT INTO san1(id,tname,tage) VALUES (new.id,new.tname,new.tage); END$$ DELIMITER ;-
两张表的数据修改监听
-- 两张表的数据修改监听 DELIMITER $$ CREATE TRIGGER `wa`.`wa2` AFTER UPDATE ON `wa`.`san0` FOR EACH ROW BEGIN -- 当你删除了san3表中的数据 就会触发这个触发器 当触发了出触发器 在此进行操作 UPDATE san1 SET id=new.id,tname=new.tname,tage=new.tage WHERE id=old.id; END$$ DELIMITER ;
视图
-- 视图的最大作用就是简化查询
-- 视图的数据来自于基表
-- 单表视图：它的数据来源是来自一张表
-- 多表视图：数据来自于多表查询
1.概念
视图:有结构(有行有列),但没有结果(结构中不真实存储数据)的虚拟的表,
虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图数据的来源)
2.创建视图语法
create view 视图名称 as select语句(这个语句可以是一张或多张表的的普通查询,或多表查询)
例如:创建单表视图
create view my_v1 as select * from student;
例如:创建多表视图 注意:不要查询两张表中的同名字段 不然会报错
create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;
注意：MySQL中视图不支持封装子查询查出来的数据
3.查看视图 其实视图是一张 虚拟表 那关于查询表的语句 对视图都是可以用的
比如:show tables; desc my_v1
只是在查看视图创建语句的的时候 把table 改成view
如:show create view my_v1;
4.视图一旦创建,系统会在视图对应的数据库文件夹下,创建一个对应的结构文件:frm文件.
5.视图的使用: 视图的使用,只是为了简化查询,你可以把 视图当作表一样去使用 例如:select * from my_v1;
视图的执行:其实本质就是执行封装的select语句
7.删除视图: drop view 视图名称
例如:drop view my_v1
6.修改视图:视图本身不可以修改,但是视图的来源是可以修改的(其实就是修改select 语句)
语法: alter view 视图名字 as 新的select语句
7.视图的意义:(1)视图可以节省SQL语句,将一条复杂的查询语句,使用视图进行保存,以后可以直接对视图进行操作.
(2)数据安全,视图操作注意是针对查询语句的,如果对视图结构进行处理(比如删除),不会影响基表的数据.
所以相对来说数据比较安全
(3)视图往往是在大项目中去使用,而且是多系统中去使用.我可以对外提供一些有用的数据,隐藏一些关键的数据.
(4)视图对外可以提供友好的数据:不同的视图提供不同的数据,对外提供的数据好像是经过专门设计的一样.
(5)视图可以更好的进行权限控制 比如对外隐藏我的一些基表的名称
8.视图数据的操作:视图是可以进行数据操作的(比如 增,删,改,视图中的数据),但是有很多限制
视图插入数据:
(1)多表视图不能插入数据
(2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
(3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)
视图删除数据
(1):多表视图不能删除数据
(2):单表视图可以删除数据,也会影响到基表
视图更新数据
(1):单表视图,多表视图都可以更新数据
更新限制:with check option
例如:create view my_v1 as select * from student where age>30 with check option;
表示视图数据的来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到
数据age>30的学生 改成age<30 的.
那么:update my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制
- 案例
-- 视图
-- 创建视图
CREATE VIEW sanview AS
SELECT
*
FROM
product ;
-- 查询视图
SELECT
*
FROM
sanview ;
SELECT
userc.*,
orderc.*
FROM
userc,
orderc
WHERE userc.`id` = orderc.`user_id` ;
CREATE VIEW sanview1 AS
SELECT
userc.id AS 编号,
userc.username AS 姓名,
orderc.id AS 订单编号,
orderc.price AS 价格,
orderc.user_id AS 外键
FROM
userc,
orderc
WHERE userc.id = orderc.`user_id` ;
SELECT
*
FROM
sanview1 ;
-- 删除视图
CREATE VIEW sanview2 AS
SELECT
*
FROM
product ;
DROP sanview2;
-- 修改视图
ALTER
编号 姓名 订单编号 价格 外键
1 银灰 1 1700 1
2 夜莺 2 1300 2
3 大叔 3 1100 3
4 小九 4 400 4
5 陈蕊 8 1300 5
5 陈蕊 9 2100 5
函数（方法）
- 概念
-- 函数 (内置函数 自定义函数)
-- 函数必须要有返回值
函数：包括内置函数，和自定义函数
- 语法
自定义函数语法
DELIMITER $$
CREATE
FUNCTION `mytestdb`.`myFun`(num INT)
RETURNS INT
BEGIN
DECLARE i INT DEFAULT 100;
SET i=i+num;
RETURN i;
END$$
DELIMITER ;
- 调用
函数的调用 select 函数名();
- 区别
函数和存储过程的区别
1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
2.存储过程有in out inout 这几个参数类型 函数的参数全是用来收实参
- 例子
-- 内置函数
-- 字符串的处理函数
-- 字符串的截取
SELECT
SUBSTRING('一树梨花压海棠', 1, 4) ;
-- 函数单独调用意义不大
-- 配合语句来使用
-- 大写转换小写
SELECT
LOWER('ABCDEFG') ;
-- 向上取整
SELECT
CEIL(sal) AS 取整
FROM
sal ;
-- 字符长度
SELECT
CHAR_LENGTH('一树梨花压海棠') ;
SELECT
LENGTH('一树梨花压海棠') ;
-- 查找指定字符
SELECT
INSTR('一树梨花压海棠', '花') ;
-- 日期和时间函数
-- 获取当前日期
SELECT NOW();
INSERT INTO jian(content,timex) VALUES('一树梨花压海棠',NOW());
-- 日期的切割展示
SELECT id AS 编号,content AS 内容,SUBSTRING(timex,1,11)AS 操作时间 FROM jian;
-- 日期的格式化
SELECT DATE_FORMAT (NOW(),'%Y年-%m月-%d日');
SELECT STR_TO_DATE('2033-03-13 13:33:33','%Y-%m-%d %H-%i-%s');
SELECT CURDATE();
SELECT CURTIME();
SELECT UTC_TIME();
SELECT DATE(NOW());
SELECT TIME(NOW());
-- 系统信息函数
SELECT @@version;
SELECT VERSION();
SELECT USER();
SELECT LAST_INSERT_ID();-- 返回最后一次插入的id
- 自定义函数例子
-- 自定义函数 （自己封装sql语句来调用）
DELIMITER $$
-- 声明函数的返回数据类型
CREATE FUNCTION `wa`.`sanfun` (num INT) RETURNS INT(11)
BEGIN
DECLARE w INT DEFAULT 0 ;
DELETE
FROM
san3
WHERE id = num ;
SELECT
COUNT(*)
FROM
san3 INTO w ;
RETURN w ;
-- 返回结果
END $$
DELIMITER ;
SELECT sanfun(27);
结果：
sanfun(27)
20
数据库的权限（DCL）DBA 数据库管理员
- 数据库权限
-- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情）
-- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表
-- mysql数据库，用户配置 : user表
USE mysql; -- 使用数据库
Select password(‘root’ ) 查询用户密码
SELECT * FROM USER; -查询数据库用户
-- 分配权限账户
权限： select insert delete update drop create/ 或,all
@ 后面可以是localhost 也可以是ip 也可以给% 那%代表任意一台计算机都可以连接上来
语法
GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
注意分配多个权限用逗号隔开
GRANT DELETE,SELECT,UPDATE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
--方式2：分配账户和权限
INSERT INTO USER
(HOST, USER, PASSWORD,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
删除用户
Delete FROM user Where User='eric' and Host='localhost';
--- 数据库备份和还原 注意备份还原不需要登陆数据库
备份:mysqldump -uroot -p day02 > d:/back.sql
C:\Documents and Settings\Administrator>mysqldump -uroot -p day02 > d:/back.sql
Enter password: ****
恢复：mysql -uroot -p day02 < d:/back.sql
注意恢复之前 先创建跟你原来一样的名称的数据库 相当于一个空的数据库,然后再还原
C:\Documents and Settings\Administrator>mysql -uroot -p day02 < d:/back.sql
Enter password: ****
- 数据库密码相关
-- 如何修改mysql的用户密码？
-- password:
password('123456')
md5加密函数(单向加密)
SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
-- MySQL5.7 查询root用的密码
SELECT authentication_string FROM USER WHERE USER='root';
-- MySQL5.5修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
-- MySQL5.7修改密码
UPDATE USER SET authentication_string=PASSWORD('123456') WHERE USER='root';
远程登录数据库
1.切换库
use mysql;
2.授权远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
3.刷新
flush privileges;
4.然后退出mysql
exit;
5.启动服务
service mysqld start
6.查看状态
service mysqld status
7.远程登录：mysql -h 192.168.17.123 -P 3306 -u root -p123456
数据库表设计
数据库设计
引入
需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型
图书模型：图书名称，版本号，作者，出版社
学生模型： 学号，学生姓名 手机号码
......
角色：学生 老师，图书管理员
《需求说明书》
需求设计 -
概要设计：
抽取实体：业务模型 -> 实体模型（java 类 c++类）内存
class Book{ name, bookNo,author }
数据库设计:
业务模型/实体模型 - > 数据模型 (硬盘)
数据库表设计
问题： 如何设计？
详细设计
类详细，属性和方法
三大范式
设计原则： 建议设计的表尽量遵守三大范式。
第一范式： 要求表的每个字段必须是不可分割的独立单元
student : name -- 违反第一范式
张小名|狗娃
sutdent ： name old_name --符合第一范式
张小名 狗娃
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
employee（员工）: 员工编号 员工姓名 部门名称 订单名称 --违反第二范式
员工表：员工编号 员工姓名 部门名称
订单表： 订单编号 订单名称 -- 符合第二范式
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
员工表： 员工编号（主键） 员工姓名 部门编号 部门名 --符合第二范式，违反第三范式 （数据冗余高）
员工表：员工编号（主键） 员工姓名 部门编号 --符合第三范式（降低数据冗余）
部门表：部门编号 部门名
补充
-- 条件选则 比如给不同职位的人加不同的工资 用 case when then end 语句来完成
-- 例如:
SELECT ename,job,sal AS 涨前,
CASE job
WHEN 'PRESIDENT' THEN sal+1000
WHEN 'MANAGER' THEN sal+800
ELSE sal+300
END AS 加后
FROM emp;
定义一个变量，作为一个临时展示的字段
SELECT (@i:=@i+1) AS id,ename,job,sal FROM emp,(SELECT @i:=0) AS init;
说明：mysql 这句话什么意思，SELECT @i:= 0
它的意思是为变量@i赋值（如：set @i=0;）。
在mysql中用户变量赋值有两种方式，一种是=另一种是:=
其中区别在于使用set赋值时两种方式都可以使用，使用select赋值时只能使用:=。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
