MySQL数据库操作

最新推荐文章于 2023-05-17 17:07:27 发布

原创最新推荐文章于 2023-05-17 17:07:27 发布

· 588 阅读

2 ·

版权

文章标签：

#数据库 #mysql

基本代码总结专栏收录该内容

12 篇文章

订阅专栏

MySQL数据库操作2

数据约束

数据约束主要是给表添加约束，以此来约束用户的行为。
约束主要有
1. 默认值约束；
2. 非空约束；
3. 唯一约束；
4. 主键约束；
5. 自增长约束；
6. 外键约束；
下面分别进行说明。
MySQL的注释方式是 “– “,即两个-，再加一个空格；
1.默认值约束，关键字是DEFAULT：
首先创建一个数据库：

CREATE DATABASE testdb;

使用这个数据库

USE testdb;

默认值(default)

CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(4) DEFAULT '男'
);

这样，在数据库中插入一条数据，如果gender没有插入数据，会进行默认设置，如果设置的值为null，则该条数据的值为null；

INSERT INTO student(id,NAME) VALUES(1,'kite');

2.非空约束，关键字为 NOT NULL；
这里把上次创建的表删除，重新建立一个student表，同时下面每次创建Student表都是先删除原来的表：
DROP TABLE student;

CREATE TABLE student(
id INT,
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(4)
);

下面这条语句有错误，不能执行：
– not
INSERT INTO student(NAME,gender) VALUES(NULL,’boy’);
下面这条语句可以执行：

-- ok
INSERT INTO student(gender) VALUES('boy');

3.唯一约束，关键字是UNIQUE；

-- 唯一约束
CREATE TABLE student(
id INT UNIQUE,
NAME VARCHAR(20),
gender VARCHAR(4)
);
-- ok
INSERT INTO student(id,NAME,gender) VALUES(1,NULL,'boy');
-- not ok
INSERT INTO student(id,NAME,gender) VALUES(1,'tom','boy');

4.主键约束，关键字是PRIMARY KEY；

-- 主键约束(primary key)（唯一+非空）
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(4)
);

-- ok
INSERT INTO student(NAME,gender) VALUES(NULL,'boy');

-- not ok  默认为0，必须要实现自增长才可以不写，否则主键都是0，重复了
INSERT INTO student(NAME,gender) VALUES('funny','boy');

-- not ok 主键不能为0
INSERT INTO student(id,NAME,gender) VALUES(NULL,'funny','boy');

-- ok
INSERT INTO student(id,NAME,gender) VALUES(1,'tom','boy');

5.自增长约束，关键字为AUTO_INCREMENT，一般定义到主键上；

-- 自增长约束（auto_increment）
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(4)
);

-- ok
INSERT INTO student(NAME,gender) VALUES(NULL,'boy');
-- ok
INSERT INTO student(NAME,gender) VALUES('ded','boy');

6.外键约束，关键字为FOREIGN KEY；
外键约束：只有多种表才会有外键，一个表没有外键。
约束两种表的情况，出现外键是因为出现了冗余字段。
例如下面的这个表：一个学生表，有id，name,gender,schoolID,schoolName,schoolAdd这几个列，从这个表中可以看出，Student表只需要关联schoolID这个字段就可以了，因为通过这个字段可以找到学校的名字和地址。

当有了外键约束之后，操作数据的顺序如下：
                插入数据： 先插入主表的数据，再插入副表数据
                修改数据： 先修改主表数据，再修改副表数据
                删除数据： 先删除副表数据，再删除主表数据


CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(4),
schoolID INT,
schoolName VARCHAR(40),
schoolAdd VARCHAR(40)
);

INSERT INTO student(NAME,gender,schoolID,schoolName,schoolAdd) VALUES('wy','boy',1,'qinghua','beijing');
INSERT INTO student(NAME,gender,schoolID,schoolName,schoolAdd) VALUES('tom','boy',2,'peking','beijing');

将上面的sutdent表重新设计为student表和school表：

– 由于上面的表有冗余，现在需要把这个表分成两个表，一个学生表，一个学校表，这两个表是有关系的，比如学生一定是有所属学校的
– 不存在学生的学校不在学校表中。

– 只能先创建school表

CREATE TABLE school(
id INT PRIMARY KEY,
schoolName VARCHAR(40),
schoolAddress VARCHAR(40)
);

– 后创建学生表，不然这个外键没有位置，这里student表有个外键，
CONSTRAINT stu_school_fk FOREIGN KEY(schoolID) REFERENCES school(id)
*其中CONSTRAINT 是约束
stu_school_fk是外键名字；
FOREIGN KEY(schoolID)是本表的外键；
REFERENCES参照；
school(id) 参照school表的id字段。*

CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(4),
schoolID INT,
CONSTRAINT stu_school_fk FOREIGN KEY(schoolID) REFERENCES school(id)
);

给这两个表插入数据：

INSERT INTO school VALUES(1,'npu','shanxixiaan');
INSERT INTO school VALUES(2,'beijingdaxue','beijing');
INSERT INTO school VALUES(3,'qinghua','bejing');
INSERT INTO school VALUES(4,'nanda','nanjing');
INSERT INTO school VALUES(5,'zheda','nanjing');

INSERT INTO student(id,NAME,gender,schoolID) VALUES(1,'wy','boy',1);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(2,'tom','boy',2);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(3,'jack','boy',3);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(4,'tommy','jack',3);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(5,'yuhck','boy',4);

两个表的数据操作

内连接

内连接就是将两个表满足一定条件的连接起来：

    -- 内连接
SELECT stu.name,sch.schoolName
FROM student stu,school sch
WHERE stu.schoolID=sch.id;

-- 内连接的另一种语法
SELECT stu.name,sch.schoolName
FROM student stu
INNER JOIN school sch
ON stu.schoolID=sch.id;

左外连接

左外连接是把LEFT OUTER JOIN这几个关键字左边表的所选字段全部显示，如果右边的表没有相应的内容，则用null；即如果学生的schoolName没有值，使用null显示；

-- 左外连接
SELECT stu.name,sch.schoolName
FROM school sch
LEFT OUTER JOIN student stu
ON stu.schoolID=sch.id;

右外连接

右外连接类似，只是关键字RIGHT OUTER JOIN右边的表所选字段全部显示，左边的表的字段如果没有相应的值，使用null显示；

-- 右外连接
SELECT stu.name,sch.schoolName
FROM student stu
RIGHT OUTER JOIN school sch
ON stu.schoolID=sch.id;

级联技术

级联技术是连接两个表，如果其中一个表删除了一条数据，如果另一表中还在使用该条数据，则在不通知他的情况下直接操作相关的数据条目；
例如下面两个表，一个学生表，一个学校表，学生表在delete操作上级联school表，因此当删除学校id为3的时候，学生表中学校id为3的学生也直接删除；
因此级联技术要谨慎使用；

-- 级联技术

CREATE TABLE school(
id INT PRIMARY KEY,
schoolName VARCHAR(40),
schoolAddress VARCHAR(40)
);

CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(4),
schoolID INT,
CONSTRAINT stu_school_fk FOREIGN KEY(schoolID) REFERENCES school(id) ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO school VALUES(1,'npu','shanxixiaan');
INSERT INTO school VALUES(2,'beijingdaxue','beijing');
INSERT INTO school VALUES(3,'qinghua','bejing');
INSERT INTO school VALUES(4,'nanda','nanjing');
INSERT INTO school VALUES(5,'zheda','nanjing');


INSERT INTO student(id,NAME,gender,schoolID) VALUES(1,'wy','boy',1);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(2,'tom','boy',2);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(3,'jack','boy',3);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(4,'tommy','jack',3);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(5,'yuhck','boy',4);
INSERT INTO student(id,NAME,gender,schoolID) VALUES(5,'wwl','girl',4);

DELETE FROM school WHERE id=3;

数据库设计三范式

数据库设计有一定的规范，按照要求级别不同，分为范式。

        第一范式为：    要求表的每个字段必须独立的不可分割的单元；

例如学生表的name字段，里面有firstname和lastname，那么则需要将name字段分开为两个字段；

第二范式： 在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。一张表应该只表达一个意思。

如上面的学生表和学校表，需要分开；

第三范式：在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。

如上面的学生表中，只能存在学校id，如果再加入学校地址，则这个地址既可以通过学校id找到，又可以通过学生ID找到，有冗余，第三范式不允许。

MySQL存储过程

存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程。

特点：
1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程
2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。
3）存储过程的移植性非常差的！
语法：

DELIMITER 结束符号
CREATE PROCEDURE 存储过程名称 (形式参数列表) 
BEGIN
    多个sql语句
END 结束符号

– 调用存储过程

CALL 存储过程名称(实际参数列表);

参数类型：
IN：输入参数，可以携带数据到存储过程中
OUT: 输出参数，可以携带数据到存储过程外面。
INOUT: 输入输出参数。

mysql数据库三种变量：
1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！
        -- show variables:产看全局变量
        -- character_set_client: mysql接收的客户端的数据编码
        -- character_set_results： mysql使用什么编码输出给客户端数据
        -- 查看某个全局变量： select @@变量名
        -- 修改某个全局变量： set @@变量名=值

2) 会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！   
        -- 查看某个会话变量： select @变量名
        -- 修改/定义某个会话变量： set @变量名=值 
        -- 案例：演示查询和更改会话变量

3) 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！
        -- 查看某个局部变量： select 变量名
        -- 修改某个局部变量： set 变量名=值 
        -- 定义某个局部变量： declare 变量名 数据类型;

下面使用会话变量来接收输出参数；

有输入输出参数的存储过程
创建存储过程：
DELIMITER $CREATE PROCEDURE pro_QueryNameById(IN sId INT,OUT sName VARCHAR(20)) BEGIN SELECT NAME INTO sName FROM student WHERE id=sId; END$

调用存储过程：

CALL pro_QueryNameById(2,@sName);
SELECT @sName;

删除存储过过程

DROP PROCEDURE pro_QueryNameById;

触发器

触发器是当用户进行了某项操作，会出发它，进行日志记录，这样可以记录用户进行了哪些操作，以便进行了错误操作进行回滚。
首先创建一个日志表，用于保存用户进行了哪些操作；

-- create a student operation log
CREATE TABLE stuLog(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);

下面是定义一个插入触发器，当用户进行插入操作，往日志表中插入一条数据：’do insert’

-- insert log
CREATE TRIGGER tri_stuInsert AFTER INSERT ON student FOR EACH ROW 
INSERT INTO stuLog(content) VALUES('do insert');

INSERT INTO student(NAME) VALUES('周华健');

下面是定义一个更新触发器，当用户进行更新操作，往日志表中插入一条数据：’update’

-- update log
CREATE TRIGGER tri_stuUpdate AFTER UPDATE ON student FOR EACH ROW 
INSERT INTO stuLog(content) VALUES('update');
UPDATE student SET NAME='wwwww' WHERE id=3;

下面是定义一个删除触发器，当用户进行删除操作，往日志表中插入一条数据：’delete’

-- delete log
CREATE TRIGGER tri_stuDelete AFTER DELETE ON student FOR EACH ROW 
INSERT INTO stuLog(content) VALUES('delete');
DELETE FROM student WHERE id=3;

数据库的权限问题

分配查询testdb数据库的student表的权限给eric用户，密码为‘123456’

GRANT SELECT ON testdb.student TO 'eric'@'localhost' IDENTIFIED BY '123456';