peace唠叨

Msql入门实战之下

前面一章主要讲解了mysql的select的用法,将select的大部分用法进行分别讲解。本章主要讲解Msql约束表的建立,以及存储过程的实现,附带其他介绍。暂时就算入门了,Mysql索引之后另开章节;

讲解不到位的地方欢迎大家指正:联系方式rlovep.com

全部代码下载(csdn):链接

Github链接:链接https://github.com/wpeace1212/Mysql

写文章不易,欢迎大家采我的文章,以及给出有用的评论,当然大家也可以关注一下我的github;多谢;

1.约束表的建立和插入

      数据类型告诉我们需要存储什么样子的数据,而约束告诉我们这些数据具体需要满足的规则。如:age int, 我们可以存储负整数,price float,我们可以存储负小数,但是负数是不满足实际规则的,因此我们需要约束。

常见约束:

  1. 非空约束:not null

  2. 唯一约束:unique

  3. 主键约束:primary key

  4. 外键约束:foreign key

1.1非空约束:

1.定义:

      在建立表的字段时,在后面带上not null就可,正如字面意思,代表当插入数据时该字段必须有值:

2.演示建立:

1
2
3
4
5
6
7
8
9
10
11
12

#创建一个名为goods的表,有两个字段商品名和商品价格:

create table if not exists goods(



goods_name varchar(20) not null comment '商品名',#设置不能为空

goods_price float not null comment '商品价格'

);

3.演示操作:

1
2
3
4
5
6

##插入:

insert into goods values('饼干1','2.3');

insert into goods(goods_price) values('23.0')##不能执行

1.2唯一约束:

1.定义:

      在建立表的字段时,在后面带上unique就可,正如字面意思,代表当插入数据时该字段不能有重复值

2.演示建立:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#创建一个名为goods的表,有两个字段商品名和商品价格:

drop table goods;

create table goods(

goods_id int not null unique comment '商品编号',#设置为非空,唯一



goods_name varchar(20) not null comment '商品名',#设置不能为空

goods_price float not null comment '商品价格'

);

3.演示操作:

1
2
3
4
5
6
7
8

##插入:

insert into goods values(001,'饼干1','2.3');

insert into goods values(001,'饼干2',2.5);##报错 Error Code: 1062. Duplicate entry '1' for key 'goods_id' 0.00022 sec

insert into goods values(002,'饼干2',2.5);

1.3主键约束:

1.定义:

      在建立表的字段时,在后面带上primary key就可;主键约束:不允许为空,不允许重复,主键一般自增长一起使用:auto_increment

2.演示建立:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#创建一个名为goods的表,有两个字段商品名和商品价格:

drop table goods;

create table if not exists goods(#判断表是否存在进行创建

goods_id int not null auto_increment primary key comment '商品编号',#设置为主键,自动增长

goods_name varchar(20) not null comment '商品名',#设置不能为空

goods_price float not null comment '商品价格'

);

3.演示操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

##插入:

insert into goods values(001,'饼干1','2.3');#标号001,没有用,生成的标号为1;标号按1增长

insert into goods values(null,'饼干2','2.3');#可以直接这样插入

insert into goods values('饼干3','2.3');#这样报错,提示值不匹配

insert into goods(goods_name,goods_price) values('饼干3','2.3');##这样可以正常插入

##删除主键:

alter table tablename drop primary key ;

1.3外键约束:

1.定义:

      在建立表的时候,在后面带上 foreign key(本表字段) references goods其他表(其他表字段)就可;外建约束是比较有用的,在建立多对一,多对多,一对一,继承等关系的数据库时都要用到。

作用:使一个表参考另一个表的字段,使两表建立关系。比如:商品表中有id号,在商品订单表中将商品id作为外建,这样就可以通过查找订单表中的商品id,从而通过连接查询查询到商品的详细信息;

2.演示建立:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

#MySQL创建关联表可以理解为是两个表之间有个外键关系,但这两个表必须满足三个条件

#1.两个表必须是InnoDB数据引擎

#2.使用在外键关系的域必须为索引型(Index)

#3.使用在外键关系的域两者数据类型需要相似

#创建关联表

#创建商品表:

create table if not exists goods(#判断表是否存在进行创建

goods_id int not null auto_increment primary key comment '商品编号',#设置为主键,自动增长

goods_name varchar(20) not null comment '商品名',#设置不能为空

gooods_price float not null comment '商品价格',

goods_maker varchar(20) default null comment '生产商',#morning为空

index(goods_id)#建立外键关系的域必须为索引类型

)engine=innodb character set utf8 collate utf8_general_ci auto_increment=1;#设置引擎以及字符集


#创建订单表

create table if not exists detil(

customer_id int not null primary key comment '客户id',#设置为主键

goods int not null comment '商品id',

count int not null comment '数量',

index(goods),#经测试此去的索引可以省去。

foreign key(goods) references goods(goods_id) on delete cascade on update cascade#建立外键,使用goods(goods_id)作为外键。

#on delete(update) cascade 意思为当goods表有相关记录删除(修改)时,detil想要的记录也会被删去(修改)。

)engine=innodb character set utf8 collate utf8_general_ci;#设置引擎以及字符集

3.演示操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

#1.插入演示:

#向goods表添加数据

insert into goods values(null,'饼干1','2.3','三无产品');

insert into goods values(null,'饼干2','2.3','三无产品');

insert into goods values(null,'饼干3','2.3','三无产品');

insert into goods values(null,'饼干4','2.3','三无产品');

insert into goods values(null,'饼干5','2.3','三无产品');

insert into goods values(null,'饼干6','2.3','三无产品');

insert into goods values(null,'饼干7','2.3','三无产品');

insert into goods values(null,'饼干8','2.3','三无产品');

#向detil表添加数据

#必须在goods中有goods_id的编号,能正确插入detil;

insert into detil values(001,1,2);

insert into detil values(002,2,2);

insert into detil values(003,3,2);

insert into detil values(004,4,2);

insert into detil values(005,1,2);

insert into detil values(006,1,2);

insert into detil values(007,1,2);

insert into detil values(008,1,2);

#insert into detil values(008,30,2);#30外键不存在会报错。

#2.删除演示:

##删除goods中的一行,detil对应的外键等于2的列也会被删去。

delete from goods where goods_id=2;

#3.查询演示:从订单表中查询对应的商品;

select * from goods where goods_id=(select goods from detil where customer_id=001)

#4.删除外键约束:

alter table tableName drop foreign key 外键名;

4.一个例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- 解决数据冗余高的问题:给冗余的字段放到一张独立表中

-- 独立设计一张部门表

CREATE TABLE dept(

id INT PRIMARY KEY,

deptName VARCHAR(20)

);


-- 添加员工表

CREATE TABLE employee(

id INT PRIMARY KEY,

empName VARCHAR(20),

deptId INT,-- 把部门名称改为部门ID

-- 声明一个外键约束

CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)

ON UPDATE CASCADE ON DELETE CASCADE -- ON CASCADE UPDATE :级联修改

-- 外键名称 外键 参考表(参考字段)

);


INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');

INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');

INSERT INTO dept(id,deptName) VALUES(3,'秘书部');

INSERT INTO employee VALUES(1,'张三',1);

INSERT INTO employee VALUES(2,'李四',1);

INSERT INTO employee VALUES(3,'王五',2);

INSERT INTO employee VALUES(4,'陈六',3);

-- 1)当有了外键约束,添加数据的顺序: 先添加主表,再添加副表数据

-- 2)当有了外键约束,修改数据的顺序: 先修改副表,再修改主表数据

-- 3)当有了外键约束,删除数据的顺序: 先删除副表,再删除主表数据

-- ON CASCADE UPDATE :级联修改

UPDATE dept SET id=4 WHERE id=3;

2.存储过程:这里只进行简单介绍

      存储过程是一种存储在书库库中的程序(就像正规语言里的子程序一样),准确的来说,MySQL 支持的“ routines (例程)”有两种:一是我们说的存储过程, 二是在其他 SQL 语句中可以返回值的函数(使用起来和 Mysql 预装载的函数一样,如 pi() )。

2.1存储过程入门:

  1. 出现的缘由:

    1. 存储过程是可复用的组件

    2. 存储过程将被保存

    3. 存储过程可以 移植

    4. 存储过程会使系统运行更快

  2. 语法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DELIMITER $ /*定义分隔符*/


CREATE PROCEDURE procedure1/* name 存储过程名 */

(IN parameter1 INTEGER) /* parameters 参数 */

BEGIN /* start of block 语句块头 */

DECLARE variable1 CHAR(10); /* variables 变量声明 */


IF parameter1 = 17 THEN /* start of IF IF 条件开始 */

SET variable1 = 'birds'; /* assignment 赋值 */

ELSE

SET variable1 = 'beasts'; /* assignment 赋值 */

END IF; /* end of IF IF 结束 */

INSERT INTO table1 VALUES (variable1); /* statement SQL 语句 */

END $ /* end of block 语句块结束 */
  1. 简单实例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

#1.存储过程(方法建立)--

-- 创建存储过程--

DELIMITER $ #-- 声明结束符为$

create procedure pro_test()

begin

SELECT * FROM employee;


INSERT INTO employee(id,deptId) VALUES(5,1);
END $

`

4.存储过程的操作:

1

-- 执行存储过程-- 

CALL  pro_test();

drop procedure pro_test;#删除存储过程

2.2创建带输入参数的函数(存储过程) :

前面我们建立的函数,参数列表是空的。Mysql数据库的存储过程是可以带参数的,包括输入输出参数,输入参数的声明方式:

1
2
3
4
5


CREATE PROCEDURE p1

([IN] name data-type) ...

1.简单演示创建:

1
2
3
4
5
6
7
8
9
10
11
12
13


-- 创建带输入参数的函数

delimiter $

create procedure pro_findById(in eid int) -- in:输入参数 类型为int

begin

select * from employee where id=eid;

end $

2.调用:

1
2
3
4
5


-- 调用带输入参数的方法--

call pro_findById(4);

2.3创建带输出参数的函数(存储过程) :

同样可以代输出参数,相当于创建了变量,使得在函数结束后,可以获得变量的值;输出参数的声明方式:

1
2
3
4

CREATE PROCEDURE p2

([out] name data-type) ...

1.简单演示创建:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 创建带有输出参数的函数

delimiter $

create procedure pro_testout(out str varchar(20), out sid int)-- 定义两个输出参数,str存储员工名字,id存储最小的员工编号

begin

select min(id) into sid from employee;-- 使用into,给输出变量赋值


select empName from employee where id=3 into str;

end $

2.带输出参数的函数调用:

1
2
3
4
5
6
7
8

-- 带输出参数的函数调用

-- 变量用@name表示

call pro_testout(@str,@sid);

select @str,@sid;#使用输出变量

2.4变量介绍:

  1. 全局变量(内置变量):

    mysql数据库内置的变量 (所有连接都起作用)

    1. 查看所有全局变量: show variables

如: character_set_client: mysql服务器的接收数据的编码

character_set_results:mysql服务器输出数据的编码

  1. 查看某个全局变量:

    select @@变量名

    select @@character_set_client;输出:utf8

  2. 修改全局变量:

    set 变量名=新值

  1. 会话变量: 输出参数属于会话变量

    只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!

    1. 定义会话变量:

    set @变量=值

    1. 查看会话变量:

    select @变量

  2. 局部变量:

在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失!!

  1. 定义局部变量:

    DECLARE 变量名 INT DEFAULT 1;#默认值为1

    DECLARE i INT DEFAULT 1;

  2. 查看局部变量:

    select 变量

    select i;

2.5带有输入输出参数的存储过程:

参数既是输入参数,也是输出参数,输出参数的声明方式:

1
2
3
4

CREATE PROCEDURE p2

([INTOUT] name data-type) ...

1.简单演示创建:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DELIMITER $

CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT: 输入输出参数

BEGIN

-- 查看变量

SELECT n;


SET n =500;-- 修改变量

END $

2.简单调用:

1
2
3
4
5
6
7
8

-- 调用

SET @n=10;

CALL pro_testInOut(@n);

SELECT @n;#显示修改后的值

2.6,带条件循环的存储过程:

Mysql的存储过程中可以使用if,while,case等语句:

  1. 带if的存储过程:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 带有条件判断的存储过程

-- 需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;

DELIMITER $

CREATE PROCEDURE pro_testIf(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_testIf(4,@str);


SELECT @str;

drop procedure pro_testIf;

2.带有while的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 带有循环功能的存储过程

-- 需求: 输入一个整数,求和。例如,输入100,统计1-100的和

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 $

#调用

CALL pro_testWhile(100,@result);

drop procedure pro_testWhile;

SELECT @result;

3.带有case的存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21


CREATE PROCEDURE p13 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;


SET variable1 = parameter1 + 1;

CASE variable1

WHEN 0 THEN INSERT INTO t VALUES (17);

WHEN 1 THEN INSERT INTO t VALUES (18);

ELSE INSERT INTO t VALUES (19);

END CASE;

END; $

3.其他介绍:

3.1触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- 当进行 update,insert,delete的前后触发一个事件;

-- 创建日志表

create table test_log(

id int primary key auto_increment,

content varchar(20)

);


-- 需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据

-- 1.创建触发器(添加)After

create trigger tri_empAdd After insert on employee for each row

insert into test_log(content) values('员工插入了一条记录');


-- 插入数据;

insert into employee values(7,'peace3',1,3);

##显示触发的数据:

select * from test_log;

-- 2.创建触发器(修改) before

CREATE TRIGGER tri_empUpd before UPDATE ON employee FOR EACH ROW -- 当往员工表修改一条记录时

INSERT INTO test_log(content) VALUES('员工表修改了一条记录');


-- 修改

UPDATE employee SET empName='eric' WHERE id=7;

##显示触发的数据:

select * from test_log;

-- 3.创建触发器(删除)before

CREATE TRIGGER tri_empDel before DELETE ON employee FOR EACH ROW -- 当往员工表删除一条记录时

INSERT INTO test_log(content) VALUES('员工表删除了一条记录');


-- 删除

DELETE FROM employee WHERE id=7;

##显示触发的数据:

SELECT * FROM employee;

SELECT * FROM test_log;

3.2mysql权限问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- ***********mysql权限问题****************

-- mysql数据库权限问题:root :拥有所有权限(可以干任何事情)

-- 权限账户,只拥有部分权限(peace)例如,只能操作某个数据库的某张表

-- 如何修改mysql的用户密码?

-- password: md5加密函数(单向加密)

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

-- mysql数据库,用户配置 : user表

USE mysql;

show tables;

-- 查看所有使用者

select user from user;

-- 创建用户账号

create user peace identified by '1234';

-- 授权 select,insert,其他一样--

grant select,insert on day01.student to peace;

-- 授权所有:grant all on *.* to peace;

-- 设置与更改用户名--

set password for peace = password('123456');

-- 撤销用户权限--

-- 命令:

revoke insert on day01.student from peace;

-- 展示权限--

show grants for peace;

-- 删除用户--

drop user peace;

本章介绍到这里:

来自伊豚wpeace(rlovep.com)

Peace wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!