MySQL数据库操作

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’);
下面这条语句可以执行：

-- okINSERT INTO student(gender) VALUES('boy');

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

-- 唯一约束CREATE TABLE student(id INT UNIQUE,NAME VARCHAR(20),gender VARCHAR(4));-- okINSERT INTO student(id,NAME,gender) VALUES(1,NULL,'boy');-- not okINSERT 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));-- okINSERT INTO student(NAME,gender) VALUES(NULL,'boy');-- not ok  默认为0，必须要实现自增长才可以不写，否则主键都是0，重复了INSERT INTO student(NAME,gender) VALUES('funny','boy');-- not ok 主键不能为0INSERT INTO student(id,NAME,gender) VALUES(NULL,'funny','boy');-- okINSERT 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));-- okINSERT INTO student(NAME,gender) VALUES(NULL,'boy');-- okINSERT 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.schoolNameFROM student stu,school schWHERE stu.schoolID=sch.id;-- 内连接的另一种语法SELECT stu.name,sch.schoolNameFROM student stuINNER JOIN school schON stu.schoolID=sch.id;

左外连接

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

-- 左外连接SELECT stu.name,sch.schoolNameFROM school schLEFT OUTER JOIN student stuON stu.schoolID=sch.id;

右外连接

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

-- 右外连接SELECT stu.name,sch.schoolNameFROM student stuRIGHT OUTER JOIN school schON 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 logCREATE TABLE stuLog(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(20));

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

-- insert logCREATE TRIGGER tri_stuInsert AFTER INSERT ON student FOR EACH ROW INSERT INTO stuLog(content) VALUES('do insert');INSERT INTO student(NAME) VALUES('周华健');

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

-- update logCREATE 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 logCREATE 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';