mysql加强

最新推荐文章于 2024-09-06 21:34:29 发布
最新推荐文章于 2024-09-06 21:34:29 发布
阅读量198 收藏
点赞数
分类专栏： Notes
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Spashi/article/details/82532985
版权

存储结构
– 一、数据约束
– 1.1 默认值(default)

CREATE TABLE test(
    NAME VARCHAR(20),
    gender VARCHAR(2) DEFAULT '男'
)

INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME,gender) VALUES('张三',NULL);

SELECT * FROM test;

– 需求：当不插入gender的时候，分配一个‘男’的默认值
– 注意： 1）当没有插入gender字段的时候，分配一个默认值

– 1.2 非空（not null）

CREATE TABLE test(
    NAME VARCHAR(20) NOT NULL,
    gender VARCHAR(2)
)
-- 需求： name字段一定要有值（不能不插入数据，不能是null），这是给name添加非空约束
-- 1)非空约束，不能不插入值
INSERT INTO test(gender) VALUES('男'); -- ERROR 1364 (HY000): Field 'NAME' doesn't have a default value

-- 2)非空约束，不能插入null
INSERT INTO test(NAME,gender) VALUES(NULL,'男'); -- ERROR 1048 (23000): Column 'NAME' cannot be null

– 1.3 唯一约束（unique）

CREATE TABLE test(
id INT UNIQUE,
NAME VARCHAR(20)
)
– 需求： id的值不能出现重复值。这时就要给id添加一个唯一约束。

INSERT INTO test(id,NAME) VALUES(1,'张三');
-- 1)不能插入重复的值
INSERT INTO test(id,NAME) VALUES(1,'李四'); -- Duplicate entry '1' for key 'id'
INSERT INTO test(id,NAME) VALUES(2,'李四');
-- 2)唯一约束，可以插入多个null。所以唯一约束不能约束null
INSERT INTO test(id,NAME) VALUES(NULL,'王五');
INSERT INTO test(id,NAME) VALUES(NULL,'陈六');

-- 1.4 主键约束(primary key)（唯一+非空）
-- 注意：
-- 1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性
-- 2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复。
-- 3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性。

CREATE TABLE test(
    id INT PRIMARY KEY,
    NAME VARCHAR(20)
)

-- 1)唯一性
INSERT INTO test(id,NAME) VALUES(1,'张三');
INSERT INTO test(id,NAME) VALUES(1,'张三'); -- Duplicate entry '1' for key 'PRIMARY'

-- 2）非空性
INSERT INTO test(id,NAME) VALUES(NULL,'张三'); -- ERROR 1048 (23000): Column 'id' cannot be null

-- 1.5 自增长约束（auto_increment）

CREATE TABLE test(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)
-- 自增长约束：初始值为0，每次递增1
-- 需求：id的值不需要我们开发者管理，交给数据库维护，这时给id添加自增长约束。
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME) VALUES('李四');
INSERT INTO test(NAME) VALUES('王五');


-- 删除数据
-- delete from test where id=2;
-- 使用truncate table删除数据的时候，可以把自增长的初始值置为0
TRUNCATE TABLE test;

SELECT * FROM test;

-- 1.6 外键约束
-- 外键作用： 约束两种表的数据
-- 问题： 约束哪种情况下的两种表呢？
-- 员工表
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptName VARCHAR(20)
)


INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');
INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');

-- 问题：每次插入员工数据时，部门名称数据会出现重复（冗余）,如果数据出现冗余，那么会浪费数据库存储空间。
-- 如何解决部门名称数据冗余的问题? 这时就可以设计一张独立的 部门表，把部门名称放到部门表中。

-- 员工表
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT
)

-- 部门表
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

SELECT * FROM employee;
SELECT * FROM dept;

INSERT INTO dept(NAME) VALUES('软件开发部');
INSERT INTO dept(NAME) VALUES('软件维护部');
INSERT INTO employee(NAME,deptId) VALUES('陈六',1);
INSERT INTO employee(NAME,deptId) VALUES('王五',1);

INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 问题：在插入员工表的部门id的时候，插入了不存在的部门id，如何防止这种非法数据的插入，这时就添加外键约束了。

-- 添加外键约束（foreign key）
-- 需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。
-- 员工表（副表： 被别的表约束。外键设置在副表）
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
    --          外键名称                     外键字段  参考          
)

-- 部门表（主表：约束别人的表）
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

SELECT * FROM employee;
SELECT * FROM dept;

INSERT INTO employee(NAME,deptId) VALUES('陈六',1);
INSERT INTO employee(NAME,deptId) VALUES('王五',1);
-- 插入数据
INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 违反外键约束：a foreign key constraint fails 
-- 修改数据
UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails
-- 删除数据
DELETE FROM dept WHERE id=1; -- a foreign key constraint fails

-- 外键约束在什么情况下，会其作用？
-- 插入数据：当往副表插入了主表不存在的数据时，外键起作用
-- 修改数据：当往副表修改为主表不存在的数据时，外键起作用
-- 删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用

-- 当有了外键之后，应该如何管理数据呢？
-- 插入数据： 先插入主表的数据，再插入副表数据
-- 修改数据： 先修改主表数据，再修改副表数据
UPDATE employee SET deptId=3 WHERE id=5; 
UPDATE dept SET id=3 WHERE id=2;
-- 删除数据：先删除副表数据，再删除主表数据
DELETE FROM dept WHERE id=3;
DELETE FROM employee WHERE deptId=3;

-- 1.8 级联操作(CASCADE)
-- 员工表（副表： 被别的表约束。外键设置在副表）
DROP TABLE employee;
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- 添加级联修改： ON UPDATE CASCADE
    -- 添加级联删除： ON DELETE CASCADE 
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
    --          外键名称                     外键字段  参考          
)

– 部门表（主表：约束别人的表）

CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

SELECT * FROM employee;
SELECT * FROM dept;
INSERT INTO employee(NAME,deptId) VALUES('陈六',2);
INSERT INTO employee(NAME,deptId) VALUES('王五',2);

-- 只修改部门
UPDATE dept SET id=1 WHERE id=2;
-- 只删除部门
DELETE FROM dept WHERE id=1;

– 二、多表查询

TRUNCATE TABLE dept;
DELETE FROM dept;
TRUNCATE TABLE employee;


INSERT INTO dept(NAME) VALUES('软件开发部');
INSERT INTO dept(NAME) VALUES('软件维护部');
INSERT INTO dept(NAME) VALUES('秘书部');

INSERT INTO employee(NAME,deptId) VALUES('张三',4);
INSERT INTO employee(NAME,deptId) VALUES('李四',5);
INSERT INTO employee(NAME,deptId) VALUES('王五',4);

INSERT INTO employee(NAME,deptId) VALUES('陈六',5);

– 1.1 交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）
– 需求： 员工及其部门
SELECT employee.name,dept.name FROM employee,dept;

– 多表查询的步骤： 1）确定查询哪些表 2）确定查询哪些字段 3）确定连接条件(规则： 表数量-1)
– 内连接效果：只有满足连接条件的数据才会显示出来
– 1.2 内连接查询（使用最多）
SELECT e.name,d.name
FROM employee e,dept d
WHERE e.deptId=d.id;

– 另一种语法
SELECT e.name,d.name
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

– 1.3 左外连接查询（其次）
– 需求： 查询部门及其部门的员工（部门全部显示）
– 左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，
– 就显示满足条件的的数据，但是如果不满足连接条件，则显示null
– 预期结果：
– 软件开发部 张三
– 软件开发部 王五
– 软件维护部 李四
– 软件维护部 陈六
– 秘书部 null

```
SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;   
```

– 1.4 右外连接查询
– 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，
– 就显示满足条件的数据，但是如果不满足连接条件，则显示null
SELECT d.name,e.name
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;

– 1.5 自连接查询
– 需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)
– 预期结果：
员工 上司
– 张三 null
– 李四 张三
– 王五 李四
– 陈六 王五
SELECT e.name AS ‘员工’,b.name AS ‘上司’
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

– 三，mysql存储过程
– 创建存储过程
– 定义结束符号

DELIMITER $
CREATE PROCEDURE pro_test() 
BEGIN
    SELECT * FROM employee; 
END $

– 调用存储过程
CALL pro_test();

– 3.1 带有输入参数的存储过程
– 需求： 传入员工id查询对应的员工

DELIMITER $
CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型（IN） 参数名称 数据类型(int)
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $

– 调用
CALL pro_testByIn(2);

– 3.2 带有输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
BEGIN
    -- 修改变量n
    SET n = '输出参数';
END $

– 问题： 如何接收存储过程的输出参数？？？
– 定义变量去接收输出参数数据。

– mysql数据库三种变量：
– 1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！
– character_set_client: mysql接收的客户端的数据编码
– character_set_results： mysql使用什么编码输出给客户端数据
– 查看某个全局变量： select @@变量名
– 修改某个全局变量： set @@变量名=值
– 2) 会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！
– 查看某个会话变量： select @变量名
– 修改/定义某个会话变量： set @变量名=值
– 3) 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！
– 查看某个局部变量： select 变量名
– 修改某个局部变量： set 变量名=值
– 定义某个局部变量： declare 变量名 数据类型;

– 定义会话变量去接收输出参数数据
– set @n=’eric’;
CALL pro_testByOut(@n);
– 查看会话变量n
SELECT @n;

– 3.3 带有输入输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
BEGIN
    -- 查看n变量
    SELECT n;
    -- 修改n变量
    SET n = '500';
END $

-- 定义会话变量调用存储过程
SET @n='100';
CALL pro_testByInOut(@n);
-- 查看n
SELECT @n;

– 3.4 带有判断条件的存储过程
– 需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，num=3,输出‘星期三’，否则，输出‘错误参数’

DELIMITER $
CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str = '星期一';
    ELSEIF num= 2 THEN
        SET str ='星期二';
    ELSEIF num=3 THEN
        SET str = '星期三';
    ELSE
        SET str = '错误参数';
    END IF;
END $

CALL pro_testByIf(5,@str);
SELECT @str;

– 3.5 带有循环条件的存储过程
– 需求： 输入一个num，计算从1到num的总和。

DELIMITER $
CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)
BEGIN
    -- int result =0;
    -- for(int i=1;i<=100;i++){
    --  result += i;
    -- }

    -- 定义局部变量
    DECLARE i INT DEFAULT 1;
    DECLARE result INT DEFAULT 0;
    WHILE i<=num DO
        SET result = result + i;
        SET i = i+1;
    END WHILE;
    SET score = result;
END $


CALL pro_testByWhile(200,@score);
SELECT @score;

– 3.6 携带数据库的数据给输出参数(INTO)
– 需求： 传入员工id，查询对应的员工，输出员工姓名

DELIMITER $
CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))
BEGIN
    SELECT NAME INTO sname FROM employee WHERE id=eid;
END $

CALL pro_testByData(2,@sname);
SELECT @sname;

– 存储过程练习
在student表的数据基础上，设计一个存储过程：

1）计算出所有学生的英语平均分
2）
    如果平均分，大于80分，且小于等于100分，输出'优秀'
    如果平均分，大于等于60分，且小于等于80分，输出'良好'
    如果平均分，小于60分，输出'不及格'

USE day14;

SELECT * FROM student2;

DELIMITER $
CREATE PROCEDURE pro_testByAvg(OUT str VARCHAR(20),OUT tavg FLOAT)
BEGIN
    DECLARE savg FLOAT DEFAULT 0.0;
    SELECT AVG(english) INTO savg FROM student2;
    IF savg>80 AND savg<=100 THEN
        SET str = '优秀';
    ELSEIF savg>=60 AND savg<=80 THEN
        SET str = '良好';
    ELSE 
        SET str = '不及格';
    END IF;
    SET tavg = savg;    
END $

– 删除存储过程

DROP PROCEDURE pro_testByAvg;
CALL pro_testByAvg(@str,@tavg);
SELECT  @str;
SELECT @tavg;

– 四、触发器
– 员工日志表
CREATE TABLE emp_log(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
)

– 需求： 当往员工表插入一条数据时，往员工日志插入一条记录。

-- 创建增加触发器
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
    INSERT INTO emp_log(content) VALUES('员工被插入了一条记录');
-- 创建修改触发器
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
    INSERT INTO emp_log(content) VALUES('员工被修改了一条记录');
-- 创建删除触发器
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
    INSERT INTO emp_log(content) VALUES('员工被删除了一条记录');


DROP TRIGGER tri_empAdd;

SELECT * FROM employee;
SELECT * FROM emp_log;


INSERT INTO employee(NAME,deptId,bossId) VALUES('李七',5,1);
UPDATE employee SET NAME='张三' WHERE id=10;
DELETE FROM employee WHERE id=10;

– 五、mysql权限问题
– root用户： 超级管理员。 权限： 增删改查（数据库，表，数据）
– 给mysql数据库分配不同权限的不同用户

– mysql数据库：存放mysql配置信息，包括用户信息

USE mysql;
-- 用户表
SELECT * FROM USER;

-- 加密函数(md5算法--单向加密)
SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

-- 修改用户密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

-- 分配权限给不同的用户
-- 需求： 分配查询day15数据库的employee表的权限给eric用户，密码为‘123456’
GRANT SELECT ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

SHOW TABLES;
Spashi
关注
专栏目录
Mysql增强半同步模式_MySQL半同步复制与增强半同步复制详解及安装
weixin_35555531的博客
02-08 1826
一、基础1.目前MySQL主要有三种复制方式1)异步复制2)半同步复制3)增强半同步复制推荐使用：对性能要求较高的推荐使用异步复制 ，如果运行的金融类业务推荐使用增强半同步复制，并使用ROW+GTID+5.7以上1.1)异步复制MySQL复制的默认方式2.1)半同步复制的优缺点 rpl_semi_sync_master_wait_point=after_commit缺点1： 幻读当用户发起一个事务...
mysql connector net 7.0.6 官方版
11-02
mysql connector net 6.9.9 官方版
MySQL基础和加强（高级）
07-20
mysql基础 1）mysql存储结构： 数据库 -> 表 -> 数据 sql语句 2）管理数据库： 增加： create database 数据库 default character utf8; 删除： drop database 数据库; 修改： alter database 数据库 default character gbk; 查询： show databases / show create database 数据库; 3) 管理表： 选择数据库：use 数据库; 增加： create table 表(字段名1 字段类型,字段名2 字段类型......); 删除： drop table 表; 修改： 添加字段： alter table 表 add [column] 字段名 字段类型; 删除字段: alter table 表 drop [column] 字段名; 修改字段类型： alter table 表 modify 字段名 新的字段类型; 修改字段名称 ： alter table 表 change 旧字段名 新字段名 字段类型; 修改表名称： alter table 表 rename [to] 新表名; 查询：
MySql数据库(二)
xulimessage的专栏
01-27 690
2.1什么数据约束 对用户操作表的数据进行约束 2.2 默认值 作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。 注意： 1）对默认值字段插入null是可以的。 2）对默认值字段可以插入非null -- 1.1 默认值 CREATE TABLE student( id INT,
MySQL学习--加强
最新发布
qq_56798493的博客
09-06 268
分页查询。
CMS程序TinaCMS v1.2 MYSQL加强版-tinacms-1.3(mysql).zip
03-09
【CMS程序TinaCMS v1.2 MYSQL加强版-tinacms-1.3(mysql).zip】是一款基于MySQL数据库的网站内容管理系统（CMS）的升级版本。TinaCMS旨在为用户提供一个易于使用、功能强大的平台，用于构建和管理网页内容。在v1.2的...
mysql加强 布尔教育 1-13 共46集
05-07
这套"mysql加强 布尔教育 1-13 共46集"的教程旨在深入强化对MySQL的掌握，涵盖了一系列关键概念和操作。 首先，从"数据库概念 布尔教育.mp4"开始，我们了解到数据库是组织和存储数据的系统，而MySQL是一种开源、...
[CMS程序]TinaCMS v1.2 MYSQL加强版_tinacms_1.3(mysql).zip
03-15
【CMS程序】TinaCMS v1.2 MYSQL加强版_tinacms_1.3(mysql).zip 是一个针对内容管理系统（CMS）的软件包，专为MySQL数据库优化设计。TinaCMS是一个轻量级且功能强大的开源CMS系统，旨在帮助用户快速搭建网站，管理...
dbforgemysql_7.0.54的破解版
11-20
dbforgemysql_7.0.54, 好用，主要是用于mysql的存储过程，函数等脚本的调试分析，可以断点，跟踪执行的具体过程. 用于日常开发调试非常给力
Mysql -5.7.0
11-18
Mysql-5.1至8.0的所有驱动jar包，欢迎下载使用，解决你的数据库连接问题
MySQL加强
hello_word_demo1的博客
04-05 1722
一、表的三范式 1.第一范式 （1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值（一个列中只能存储一个值） 帮助理解： 一个列不能有多个值。 必须遵循 2.第二范式 （2NF）要求数据库表中的每个实例或行必须可以被唯一的区分（主键） 帮助理解： 一张表中需要有主键 可遵循可不遵循 3.第三范式 （3NF）要求一个数据库表中包含已在其它表中已包含的主键段信息 帮助理解：一张表的列不能关联别的表的非主键
MySQL数据库加强
tideseng的博客
05-03 358
1 数据约束 1.1 什么数据约束 对用户操作表的数据进行约束 1.2 默认值(DEFAULT) 作用： 只有当用户对使用默认值的字段不插入值的时候，就使用默认值。 注意： 1）对默认值字段插入null是可以的。 2）对默认值字段可以插入非null CREATE TABLE student( id INT, NAME VARCHAR(20), add
mysql 增强半同步配置
05-03
MySQL增强半同步复制是MySQL的一种高可用性解决方案，可以提供更可靠的数据同步和更短的数据恢复时间。以下是MySQL增强半同步复制的配置步骤： 1. 安装MySQL增强半同步插件 在MySQL的主从服务器上分别安装MySQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值