MYSQL基础操作

2022-12-01 09:48:58

MYSQL基础操作

目录

1.基本定义

1.1.关系型数据库系统

关系型数据库系统是建立在关系模型上的数据库系统

什么是关系模型呢？

1.数据结构可以规定，同类数据结构一致，就是一个二维的表格

2.2.数据之间的关系可以设置，实体之间的联系

1.2.关系型数据库

要把一个数据信息保存到数据库里面，是先有数据库，再有表，然后再有信息

数据库：数据的仓库，保存有多个表

表：数据保存在表内，一个表内，应有相同的数据格式

行&列：行用于记录，列用于规定数据的格式

记录：当列规定好保存的数据格式的时候，把信息分行保存。行内的数据，一行就是一个记录

字段：数据的某个列，字段表示指定该列的数据格式

SQL：数据库管理系统，管理数据库的语言，结构化查询工具。Structured Query Language

1.3.MySQL的架构

MYSQL是基于C/S架构的，即客户端/服务器

1.4.MySQL的启动和关闭

启动数据库：net start Mysql

关闭数据库：net stop Mysql

1.5.SQL语言

SQL的语言根据功能的不同，可以分为几大类:

1.数据库操作（管理）语言DML(CURD)

2.数据库定义语言（对保存数据的格式进行定义）DDL

3.数据库控制语言（针对数据库软件服务进行操作）DCL

2.数据库的操作

2.1.创建数据库

创建一个数据库：

CREATE DATABASE db_name;

数据库命名：

1.以分号结尾

2.见名只意

3.纯数字，特殊字符，用限定字符包起来（即反引号）

2.2.数据库查询

1.查询当前存在的数据库

SHOW DATABASES ;

注意：此句结尾要多一个S

此时可以查看到我们已经创建的数据库和内置的几个数据库

information_schema：mysql元数据，基础数据

mysql：mysql配置数据库，其中包含用户信息（用户名和密码，权限管理）

performance_schema：mysql数据库软件的运行数据，日志信息，性能数据

test：测试数据库，空的

2.查询数据库的创建语句

SHOW CREATE DATABASE db_name;

此句可以看到创建数据库的默认字符集

3.删除数据库

DROP DATABASE db_name;

删除指定的数据库

4.修改数据库的信息

ALTER DATABASE db_name [修改指令];

例如修改数据库默认字符集：ALTER DATABASE db DEFAULT CHARACTER SET gbk;

此方法可以修改数据库的属性信息，常见的就是修改数据库的默认字符集

3.表操作

3.1.创建表

语法：CREATE TABLE tbl_name(列1结构，列2结构);

例创建一个person表，有两个字段：CREATE TABLE person(class_no VARCHAR(20),data_start DATE);

在创建表时需要指定字段，表的字段用括号括起来，显示字段名，然后是空格接上字段的选项，然后是下一个字段，不同的字段之间用逗号隔开

3.2.为表指定数据库

数据信息是保存在表中的，而表是保存在不同的数据库中的，所以每创建一个表都需要为这个表指定一个数据库。为一个表指定一个数据库通常有两种方法：

1.设置默认数据库，即在创建表之前先指定对哪一个数据库进行操作。

USE db_name;

2.在没有指定对哪一个数据库进行操作的时候，可以用点语法来指定：数据库.表

db_name.tbl_name;

3.3.查看表

1.查看当前数据库中所有存在的表

SHOW TABLES;

该命令会显示当前数据库中所有的存在的表

2.使用有哪些符合匹配的表

语法：SHOW TABLES [LIKE 'pattern';]

例找到数据库中以stu开头的表：SHOW TABLES LIKE 'stu%';

其中like pattern部分，表示只获得哪种规则的表

% ：通配符，表示任意字符的任意个数的组合

3.查看某个表的表结构

DESC tbl_name;

例查看student表的表结构：DESC student;

DESC是describe的简写

查看某个表的创建信息

SHOW CREATE TABLE tbl_name;

例查看student表的创建信息：SHOW CREATE TABLE student;

可以看到创建这个表个命令

3.4.删除表

DROP TABLE tbl_name;

例删除person这个表：DROP TABLE person;

若该表不存在，则不能被删除

3.5.修改表

1.添加字段

ALTER TABLE student ADD [新增的列名] [新增列的属性];

例在studeng表中新增学生身高字段：ALTER TABLE student ADD heig INT;

新增列使用add

2.删除字段

ALTER TABLE student DROP [要删除的列名];

从student表中删除身高heig字段：ALTER TABLE student DROP heig;

删除列使用drop

3.修改字段名称

ALTER TABLE student CHANGE [需要修改的字段名] [改成什么字段名] [新字段名的类型参数];

例修改student表的身高heig为体重weight：ALTER TABLE student CHANGE heig weight INT;

修改字段名使用change

4.修改字段类型

ALTER TABLE student MODIFY [需要修改的字段名] [要修改成的类型];

例表示student的heig字段修改为carchar类型：ALTER TABLE student MODIFY heig VARCHAR(20);

5.修改表名称

RENAME TABLE [旧表名] TO [新表名] ;

例把student表的名字改为teacher：RENAME TABLE student TO teacher ;

表的重命名使用rename to

4.数据的操作CRUD

4.1.增：插入数据

插入数据分为两种，一种是插入所有字段，一种是只插入部分字段，全部字段的时候可以省略字段名，部分字段时要写出字段名，字段名要和插入的数据顺序一一对应

1.插入所有字段。一定按照表中的字段插入

INSERT INTO 表名 VALUES (依次填入字段对应的值);

往student表中插入所有字段：INSERT INTO student VALUES (2,"Eric","男","180");

注意：最后一个值的后面不需要再添加逗号，括号外面要使用分号，字段和值是一一按照顺序对应的。

2.插入部分字段，字段名和插入的值一一对应

INSERT INTO student(要插入的字段) VALUES (字段对应的值);

往student中插入部分字段INSERT INTO student(NAME, gender, heig) VALUES ("Rose","女","160");

4.2.删：删除数据

删除数据之后是不能恢复的，所以删除的时候一定要慎重，平时也一定要备份数据库

DELETE FROM tabl_name 删除条件;

例删除id=2的记录：DELETE FROM student WHERE id=2;

4.3.改：修改数据

UPDATE student SET 字段=值 更新条件;

例id=3的姓名和性别全部更改：UPDATE student SET NAME="Bob" ,gender="男" WHERE id=3;

4.4.查：查询数据

下的方法按照已经输入好的student表来操作

MYSQL基础操作

1.查询所有列

SELECT * FROM [表名];

查询student的所有列：SELECT * FROM student;

MYSQL基础操作

2.查询指定列

SELECT [指定字段列表] FROM [表名];

在student表中查询id和NAME两个字段：SELECT id,name FROM student;

MYSQL基础操作

3.查询时添加常量列 AS

需求：在查询student表时添加一个年级列，内容为”五年级“；

使用AS表示别名：

SELECT id,name,'五年级' AS '年级' FROM student;

MYSQL基础操作

4.查询时合并列 AS

将查询出来的结果合并到一个列里面，只需要把字段列表括起来并使用AS指定一个字段名就可以了

需求：查询每个学生chinese，english，math的总成绩，并显示在总成绩列里

SELECT id,name,(chinese+english+math) AS "总成绩" FROM student;

MYSQL基础操作

5.查询时去除重复记录 DISTINCT

DISTINCT关键字可以去除重复记录

需求：表中的英语成绩有分数一样的，过滤英语的重复数据

SELECT DISTINCT english FROM student;

MYSQL基础操作

6.条件查询 WHERE

条件查询使用关键字WHERE，条件放在WHERE后面，根据条件的中类，可以分为不同的几种类型：

1.逻辑条件： and，or

需求：查询id为2，或姓名为李一的学生

SELECT * FROM student WHERE id=2 OR name="李一";

MYSQL基础操作

2.比较条件：>, <, >=，<>（不等于）,between and（等价于>=且<=）

需求：查询数学成绩在75和90之间学生

SELECT id,name,math FROM student WHERE math BETWEEN 75 AND 90;

MYSQL基础操作

3.判空条件(null空字符串)：is null 或 is not null 或

需求：查询address字段为空或null的学生

SELECT * FROM student WHERE address IS NULL OR address='';

上面的语句包括了null和空字符

4.模糊查询：like

模糊查询通常使用一下替换标记：

  • % ：表示任意个字符串
  • _ :表示一个字符

    需求：查询姓”李“，且名字只有两个字的学生

SELECT id,name FROM student WHERE name LIKE '李_';

MYSQL基础操作

7.聚合查询

聚合查询就是使用内置的几个函数来限定查询现实的结果

常用的聚合函数有：

求和的sum()

需求：查询所有学生的math课程的总成绩：

SELECT sum(math) AS "所有学生math课程的总成绩" FROM student;

MYSQL基础操作

求平均值的avg()

需求：查询班级学生math课程的平均分

SELECT avg(math) AS "math课程的班级平均分" FROM student;

MYSQL基础操作

求最大值的max()

需求：查询班级math的最高分

SELECT max(math) AS "math课程的班级最高分" FROM student;

MYSQL基础操作

求最小值的min()

需求：查询当前math的最低分

SELECT min(math) AS "math课程的班级最高分" FROM student;

MYSQL基础操作

求统计次数的count()

需求：统计当前班级有多少学生

SELECT count(id) AS "班级学生总人数" FROM student;

!

注意：

1.count()函数统计的数量不包含null的数据

2.使用count统计表的记录数，要使用不包含null的字段

8.分页查询 limit

limit能够获取指定位置的记录，通过设置获取记录的开始地址和从地址开始获取的数量

语法：limit 起始行,查询几行

起始行默认是从0开始的，也可以左右分页，limit当前页，每页显示多少条

分页查询当前页的数据的SQL：

SELECT * FROM student LIMIT (当前页-1)*每页显示多少条，每页显示显示多少条

需求：每页显示两条数据，查询第2页的数据，即查询第3第4条数据

SELECT * FROM student LIMIT 2,2;

MYSQL基础操作

9.查询排序:Order by

Order by的作用是按照字段值进行排序，默认是升序排列的。

语法：Order by 字段 升序(asc)/降序(desc)

需求：按照math成绩对学生进行降序排序

SELECT * FROM student ORDER BY math DESC ;

MYSQL基础操作

如果是多字段排列，先排第一个字段，再排第二个字段

注意：若是分组，则应使用对分组字段进行排序的groupby语法

10.分组查询：group by

group by:可以根据给定数列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表

往student中添加性别gender字段，并分配了性别。

需求：查询男女的人数

SELECT gender,count(*) AS '人数' FROM student GROUP BY gender;

MYSQL基础操作

11.分组查询后筛选： having

group by是分组，我们还可以在分组查询后再对数据进行筛选.此时就是使用having，这个就是对分组后的数据进行查询限制

需求：查询总人数大于3的性别

SELECT gender,count() AS '人数' FROM student GROUP BY gender HAVING count()>3 ;

MYSQL基础操作

5.MYSQL的数据类型

1.数值数据类型

整型

tinyint 1字节

smallint 2字节

mediumint 3字节

int 4字节

bigint 4字节

unsigned 标识，加上标识无符号

小数

float(m,d) 单精度浮点数，m总个数，d个小数位

double(m,d) 双精度

2.日期时间类型

data 2014-09-18类型

time 08:42:30类型

datatime 2014-09-18 08:42:30类型

year 年份

3.字符串类型

char(n) 固定长度

varchar(n) 不固定长度

text 可变长度

6.列属性（数据约束）

在创建一个表的时候，需要对字段添加数据类型，还需要对个别字段添加约束，约束就是限制这个字段，或者约束这个表跟其他表之间的关系

此处数据约束大多是在创建表的时候规定的。

6.1.默认值default

默认值作用：当用户对一个表进行插入数据，如果没有该字段没有数据插入，则在改行记录中显示默认值，如果该字段没有插入且也没有默认值，则显示null

注意：

1.对默认值字段插入null是可以的

2.对默认字段可以插入非null

3.只用DEFAULT '默认值'来标识

例如下面创建表的时候规定address的默认值为杭州，如果address没有字段插入，则自动分配为杭州

CREATE TABLE student(

	id INT,

	NAME VARCHAR(20),

	address VARCHAR(20) DEFAULT '杭州'  -- 默认值

)

6.2.非空

非空就是限制一个字段必须赋值

注意：

1.非空字符必须赋值

2.非空字符不能赋值null

3.使用not null来标识

下面的例子中，gender被设置为非空，则在插入数据的时候必须有值插入

-- 需求： gender字段必须有值（不为null）

CREATE TABLE student(

	id INT,

	NAME VARCHAR(20),

	gender VARCHAR(2) NOT NULL -- 非空

)

6.3.自动增长

多用在id字段，作用是为每一条记录提供一个唯一的标识，本质上是在每次插入记录时，将某个字段的值自动增加1。

注意：

1.自增长字段可以不复制，自动递增

2.使用auto_increment来标识

2.使用自动增长时可以设置开始的初始值，默认为1.使用auto_increment n来设置初始值



CREATE TABLE student(

	id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从1开始

	NAME VARCHAR(20)

)

6.4.唯一

唯一是限制该字段下的数据不能有重复的值

注意;

1.唯一字段可以插入null

2.唯一字段可以插入多个null

3.使用unique来标识，用在id上最多

CREATE TABLE student(

	id INT UNIQUE, -- 唯一

	NAME VARCHAR(20)

)

6.5.主键

主键的作用是唯一标识一个记录。主键字段不能为空，且必须唯一

注意：

1.通常情况下，每张表都会设置一个主键字段，用于标记表中的每条记录的唯一性，也可以设置多主键字段

2.建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业余含义的id字段

主键的设置有两种方法，一种是在定义的字段的时候在主键字段后面标识：primary key；另一种方法是在定义好所有字段的时候再添加谁是主键：

#设置主键的第一种方法

CREATE TABLE student(

	id INT PRIMARY KEY, -- 主键

	NAME VARCHAR(20)

)

#设置主键的第二种方法：

CREATE TABLE student(

	id INT，

	NAME VARCHAR(20)，

     PRIMARY KEY(id) -- 设置主键

)

6.6.外键

1.不同表之间存在着一定的关系

1:1

1对1，两个实体表内存在相同的主键字段

如果一个表记录的主键值等于另一个表内记录的主键值，则两条记录1对1对应

1：N

1对多，一个实体对应多个其他实体

在多个那端，增加一个字段，用于指向该实体所属的另外的实体的标识

M：N

多对多，A实体对应多个B实体，返过来也相同

多对多时，要利用中间表来连两个表

2.外键

1.概念

如果A实体的某个字段，指向B实体的主键，则该字段叫做B实体的外键。

此时B表叫做主表或者父表，A表叫做从表或者字表

2.作用

用于约束处于关系内的实体

可以解决一个数据库表冗余过高的问题

例子：公司员工分属于不同的部门，如果在每个员工信息上写名具体部门信息，过于冗余，此时可以将部分单独做一张表，然后每个员工添加一个外键字段指向主表的主键

3.定义一个外键

使用关键字：foreign key

在从表上，增加一个外键字段，指向主表的主键

外键在从表上定义

4.简单例句

先建立一个部门表，然后在员工表上创建一个外键来关联部门表

# 部门表

CREATE TABLE dept(

  id INT PRIMARY KEY,

  deptName VARCHAR(20)

);

#员工表

CREATE TABLE employee(

  id INT PRIMARY KEY ,

  empName VARCHAR(20),

  deptId INT,

  #创建外键的方法

  FOREIGN KEY (deptId) REFERENCES dept (id)

);

总结外键的语法：

Foreign key (从表里的外键字段) references 主表名(主表主键) [主表记录删除时时从表的动作][主表记录更新时从表的动作];

6.7.级联操作

当两个表之间有了外键的约束，对其中一个表进行操作时，必须随时注意另外一个表的变化，甚至也要做相应的改变，这种改变太过于麻烦。所以定义了级联操作

级联操作就是在主表数据发生改变时，与之相关联的从表数据应该如何处理。

在主表发生变化时，从表有三种响应方式：

Restrict：拒绝主表的相关操作。

Set null：父表在更新或者删除的时候，子表的对应字段被设置为null.

CasCade：父表在更新或者删除时，从表也执行相应的操作。

以上三种方式，我们常用的是第三种：CasCade，即从表跟随主表的变化而变化,此处又分为两种，当主表更新或修改时，从表删除，或随之更新，所以

  • 级联修改操作是：on update cascade;
  • 级联删除操作是：on delete cascade;

    此操作在创建外键的时候设置。

    如6.6中的员工外键设置，设置为级联修改和级联删除分别是：
#级联修改：

  FOREIGN KEY (deptId) REFERENCES dept (id) ON UPDATE CASCADE;

#级联删除：

  FOREIGN KEY (deptId) REFERENCES dept (id) ON DELETE CASCADE;

注意：级联操作必须在设置外键的时候设置

7.关联查询(多表查询)

实际工作中，经常要在多表之间进行查询操作。但是基本步骤其实只有一个：

多表查询规则：

  • 1.确定查询那些表
  • 2.确定要显示哪些字段
  • 3.表与表之间的连接条件（规律：连接条件数量是表数量的-1）

为了方便下面的方法测试，先写了员工表和部门表，员工表包含部门表的外键，员工表里添加了Boss字段

员工表：

MYSQL基础操作

部门表

MYSQL基础操作

下面看根据需求的不同多表连接的几种类型：

7.1.内连接

内连接只有满足条件的结果才会被显示出来，也是使用最频繁的一种

需求：查询员工及其所在的部门（显示员工姓名，部门姓名）

根据多表连接的步骤，我们写出方法：

方法一：使用where

SELECT empName,deptName FROM employee,dept WHERE employee.deptId = dept.id;

方法二：使用inner join

SELECT empName,deptName FROM employee INNER JOIN dept ON employee.deptId = dept.id;

方法三：使用as别名

SELECT e.empName,d.deptName FROM employee AS e INNER JOIN dept AS d ON e.deptId = d.id;

三个方法的结果如下：

MYSQL基础操作

7.2.左外连接：使用左边表的数据取匹配右边表的数据，如果符合连接条件则结果显示，如果不符合连接条件则显示null。

需求：查询每个部门的员工（部门 员工），如果该部门没有员工则显示null

SELECT d.deptName,e.empName FROM dept AS d LEFT JOIN employee AS e ON d.id = e.deptId;

MYSQL基础操作

注意：

1.左连接，左边的数据一定会全部显示

2.左连接使用LEFT JOIN ……ON……方法

3.左边的数据库放在FROM后面

7.3.右外连接：右连接同左连接是一样的，只是在以右边的为准：使用右边表的数据取匹配左边表的数据，如果符合连接条件则结果显示，如果不符合连接条件则显示null。

需求：查询每个部门的员工（部门 员工），如果该部门没有员工则显示null

SELECT d.deptName,e.empName FROM employee AS e RIGHT JOIN dept AS d ON e.deptId = d.id;

显示同左连接是一样的

MYSQL基础操作

注意：右外连接时，右边的数据一定会完全显示

7.4.自连接查询:自然连接是一个表内的字段和另一个字段的关系

需求：在员工表的Boss字段中，假设Boss的1是2的领导，2是3的领导，null表示没有领导。那么查询员工及其上司（员工，领导）

SELECT e.empName,b.empName FROM employee AS e LEFT JOIN employee AS b ON e.Boss=b.id;

MYSQL基础操作

自连接的思想就是把自己的表当做两个不同的表来处理，中间是使用LEFT JOIN还是INNER JOIN都是可以的，取决于业务需求

8.子查询

有时候一个语句的查询是建立在另外一个语句的查询结果上的，这个时候就要使用到子查询，子查询需要使用括号括起来

根据子查询语句返回值的不同，可以对不同类型的子查询语句进行分类，通常子查询语句可以返回的值有：单一值，一列，多列，表等类型。每种类型都有对应的使用方法，下面分别说明：

8.1.返回单一值

此种情况下子查询语句返回的是单一值，此时，可以使用数学的关系运算符与返回的单一值进行判断。

例如：

selsct t_name,gender from teacher_class where days=(select max(days) from teacher_class);

8.2.返回一列

返回一列是指子查询语句返回的是一列数据，通常是多个行的一列

例：查询所有带过0228班的来时又带过那些班？

select t_name,c_name,days from teacher_class where t_name in (select t_name from teacher_class where c_name="0228");

思想是，子查询语句返回的是一列值，此时使用集合的操作符来完成，例如判断t_name的值是否在子查询语句返回的一列里面，使用in方法，注意列子查询时，where后面的字段和子查询返回的字段类型是一样的

其他常用的集合类操作符还有：

in， not in， any(任何一个)， all(所有)， some

8.3.返回一行（返回多列）

此处的意思是子查询返回的是只有一行但是有字段的数据，就是一行记录。

此时的操作是将要比较的字段用括号括起来跟返回的值进行比较

例：查询根0115班李白老师同性别，且教过同一个班的老师的信息

select t_name,gender,c_name from teacher_class where (gender,c_name) = (select distinct gender,c_name from teacher_class);

此方法是行子查询语法，注意在参与比较时，使用括号构建一行（field1,field2）

8.4.返回表

此时子查询语句返回的是一个表

如果用于from子句内，from自居内要求使用一个表而不是一个结果，应该给查询结果起一个别名

例如：

select * from (select t_name,c_name,days from teacher_class where days >15) as temp where t_name like '李%';

9.触发器

9.1.触发器的作用

当操作了某张表时，希望同时处罚一些动作或行为，可以使用触发器完成

例如：当向员工表插入一条记录时，希望同时往日志表插入数据

9.2.触发器的操作

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

1.创建一个日志表

CREATE TABLE test_log(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(100));

2.创建触发器（添加）

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES ('员工表插入了一条记录');

TRIGGER是触发器，tri_empAdd是我们自定义的触发器的名字

当往员工表插入一条记录时，往日志表中插入信息

3.创建触发器（修改）

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

当往员工表修改一条记录时,往日志表中插入信息

4.创建触发器（删除）

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

当往员工表删除一条记录时,往日志表中插入信息

10.事务使用

1.定义

一组sql语句操作单元，组内多有sql语句完成一个业务，如果整组成功，意味着全部sql都实现。

但是如果其中任何一个失败，意味着整个操作都失败，失败意味着整个过程都是没有意义的，应该回到操作前的状态

这种特性就叫做事务

需求：

1.失败后可以回到开始的位置

2.没有成功之前，别的用户(进程，会话)是不能看到操作内的数据修改的

2.思路

可以在一组操作之间，设计一个记号备份点。

利用Innodb存储引擎的事务日志功能

SQL的执行分为2个阶段：

1.执行阶段

2.将执行阶段的结果提交到数据库阶段

其中我们的事务日志就是保存执行阶段的结果，如果用于选择提交，则才将执行结果提交到数据库

默认的执行方式叫：自动提交，执行完毕，自动完成提交工作

因此需要关闭自动提交功能

3.方法

存在一个系统变量，autocommint，可以对自动提交进行配置

set autocommit=1；表示自动提交开启，=0表示关闭

show variables like 'autocommit';可以查看变量的值

关闭后，执行的sql结果不会被传递到数据库

在此基础上，执行完所有的sql语句

判断是否成功，（出现错误，包括语法错误，逻辑错误，和服务器错误）

成功则使用commit则将结果提交

失败则使用rollback回滚到操作单元的初始位置

注意：

如果这样操作，必须保证操作表的表属性里存储引擎用的是Innodb，即Engine=InnoDB.

最后：set autocommit=1，重新开启自动提交

4.常见的事务指令

1，开启事务

start transaction;

此命令关闭自动提交，如果事务结束了，成功或者失败，都将会自动提交机制，回到start时的状态

2.成功：

commit

3.失败

rollback

5.事务的特点

1.原子性：功能上不可再分，所有成则成，一条败则败

2.一致性：我这边修改完之前，别人不能进行修改

3.隔离性：事务之间不会相互影响

4.持久性：提交数据库之后，不能再回退

合起来叫：ACID

11.存储过程

11.1.什么是存储过程

在之前的sql语句中，只能输入一句执行一句，没有判断，没有循环。比较单调，有时候用起来会比较麻烦。所以为sql提供一种流程控制，就是存储过程。

存储过程就是带有逻辑的sql语句，即为sql带上流程控制语句，如if，while

存储过程的特点

1.执行效率非常快！存储过程是在数据库的服务器端执行的

2.移植性很差，不同数据库的存储过程是不能移植的

11.2.存储过程语法

上面说过，存储过程是放在数据库的服务器端执行的，所以第一步就先是先创建一个存储过程然后以表的形式添加到数据库的服务器端，第二步是在需要的时候调用这个存储过程，并传入参数或者接收返回值

创建一个简单存储过程的示例：

# 1.创建存储过程

DELIMITER $  #声明存储过程的结束符

CREATE PROCEDURE pro_test()  #存储过程名称(参数列表)

BEGIN #开始

#此处可以写多个sql语句，主要是sql语句+流程控制

SELECT * FROM employee;

END $ #结束 结束符

#2.执行存储过程

CALL pro_test(); #call存储过程名称(参数)

#3.删除存储过程

DROP PROCEDURE pro_test;

存储过程中的参数：

pro_test()的括号里面可设置参数，主要有三种：

IN：表示输入参数，可以携带数据到存储过程中

#IN：带有输入参数的存储过程

#需求：传入一个员工的id，查询员工的信息

DELIMITER $

CREATE PROCEDURE pro_testIN(IN eid INT)

  BEGIN

    SELECT * FROM employee WHERE id=eid;

  END $

#调用

CALL pro_testIN(4);

OUT：表示输出参数，可以从存储过程中返回结果

#OUT：带有输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testOUT(OUT str VARCHAR(20))

  BEGIN

    #给参数赋值

    SET str="hellojava";

  END $

#调用存储过程

#接收输出的方法：1.定义一个会话变量name。2.使用会话变量接收存储过程的返回值

CALL pro_testOUT(@NAME);

# 查看变量值

SELECT @NAME;

INOUT：表示输入输出参数，既可以输入功能，也可以输出功能

#INOUT：带有输入输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testINOUT(INOUT n INT)

  BEGIN

    #查看变量

    SELECT n;

    #给变量赋值

    SET n=50;

  END $

SET @n=10;

CALL pro_testINOUT(@n);

SELECT @n;

11.3.MYSQL的变量

mysql数据库中也是存在变量的，根据变量产生的时间和消失的时间，可以分为三种：

1.全局变量:全局变量是内置变量，就是不管在什么时候都是存在的，所有与数据库的连接都起作用，一般是用来保存数据库的信息的

查看所有全局变量

SHOW VARIABLES ;

查看某个全局变量:使用两个@

SELECT @@变量名;

修改全局变量名：

set 变量名=新值

涉及编码问题的两个全局变量

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

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

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

定义会话变量：

set @变量=值

查看会话变量：

select @变量

3.局部变量：在存储过程中使用的变量就叫局部变量，只要存储过程执行完毕，变量就会全部消失

声明局部变量：

DECLARE i INT DEFAULT 1;

声明i，并默认为1

注意：

1.全局变量使用@@变量，会话变量使用@变量，局部变量直接使用变量

2.在存储过程中的输出时，一般是使用会话变量

3.在全局变量和会话变量之间，优先选择会话变量

11.4.存储过程的流程控制

1.使用if语句:带有条件判断的存储过程

if语句使用时，使用if作为开头，end if作为结尾

例：

需求：输入一个整数，如果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(1,@str);

SELECT @str;

2.使用while：带有循环功能的存储过程

需求： 输入一个整数，求和。例如，输入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);

SELECT @result;

12.mysql权限和备份

12.1.mysql权限问题

mysql数据库用户权限：

root：拥有所有权限（可以做任何事情）

权限账户：在公司中为了保证数据的安全，只有管理员才拥有root权限，一般都是权限账户，权限账户只拥有部分权限（CRUD），例如只能操作某个数据库的某张表

如何修改mysql的用户密码？

可以使用password函数来修改

mysql的用户都在mysql.user中。

修改密码命令为：

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

分配权限账户：

语法：GRANT 权限 ON 数据库.表 TO '用户名'@'用户类型' IDENTIFIED BY '密码'；

权限有:select,insert,delete,update,drop,create,或all

例子：GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

12.2.数据备份

下面的操作直接在命令行终端运行就可以了，不需要先连接mysql再运行。

备份

mysqldump -uroot -p db_name > /opt/back.sql;

使用的时候要指明数据库，保存地址可以改

恢复

mysql -uroot -p bd < /opt/back.sql;

上一篇：MySQL基础(非常全)


下一篇：快捷切换hosts的小工具：SwitchHosts!