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 CREATEPROCEDUREproQueryNameById(INsIdINT,OUTsNameVARCHAR(20))BEGINSELECTNAMEINTOsNameFROMstudentWHEREid=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';