MySQL

Posted on 2022-03-19 15:39  夜雨初凉  阅读(182)  评论(0)    收藏  举报

一、安装windows版本的mysql数据库

1、安装说明参考资料中的pdf文档

2、有些同学win10系统安装版装不上，只能装解压版，但是解压版找初始密码是个问题，这里附上mysql修改root账户密码说明

  • 停止mysql服务

image

  • 找到启动你的mysql服务的命令，右键该服务 -> 属性

把可执行文件的路径 下面整行复制出来

image

比如我的复制出来如下：

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

不难看出，其实这条命令就是去执行我的mysql安装目录下bin文件夹中的 mysqld.exe程序，然后还加入了 defaults-file 参数,该参数的含义是指定mysql配置文件 my.ini 路径而已，我们找到这个my.ini配置文件，用记事本打开它(注意：解压版my.ini配置文件是自己创建出来的)

  • 在my.ini配置文件中的第二个 [mysql] 这一行后面加入 skip-grant-tables,保存后再次启动mysql服务

参数 skip-grant-tables 意思是跳过授权表，也就是说不要密码也能登录mysql

配置文件中的#表示注释的意思

特别注意该文件要保证其文件编码为 ANSI ，使用高级记事本可修改普通文本文件的编码

image

再次启动mysql服务

image

  • 重启mysql服务后,输入cmd，以管理员身份进入命令行窗口，进入你的mysql安装目录中的bin文件夹

如：我的mysql安装在了 C:\Program Files\MySQL\MySQL Server 5.7目录中，使用cd 命令进入该目录中的bin文件夹

image

  • 输入如下命令登录数据库

mysql命令能登录数据, -u参数是指定用户名，这里指定的root，-p是指定密码,由于是 skip-grant-tables 方式启动的服务,不需要密码也可以登录，在输入密码的地方直接回车即可

mysql -u root -p

最后看到mysql> 表示登录成功

image

  • 接下来我们要修改root账户密码,root账户存储于 user表中，该表所属的数据库为 mysql库,所有我们要先切换至mysql数据库

切换至mysql库

use mysql;

image

修改user表 root账户密码,这里我将密码修改成了 123456,大家想改成其他的 自由改动即可。

update user set authentication_string = password('123456') where user = 'root';

image

退出登录

exit;

image

  • 最后，我们msyql服务目前启动的方式是 跳过授权表 的方式，我们需要恢复正常方式启动，即登录数据库是要有密码的，不然不安全嘛

去掉my.ini配置文件中的 skip-grant-tables 即可,这里我直接用 # 将其注释掉了

image

  • 重启mysql服务

image

  • 由于我们是首次登陆，需要再次使用alter user... 语句修改密码。

mysql命令登陆数据库，密码就是刚刚修改的123456

image

然后使用alter user 语句修改密码,我这里将密码修改为了123456

alter user user() identified by '123456';

image

刷新权限

flush privileges;

image

最后密码修改完成

  • 使用客户端工具（navicat）连接mysql，ok

image

3、mysql解压版安装后，执行语句时出错不显示错误信息

如下图：我的建表语句有问题，但是它不提示错误信息，让我很难排查语句是哪里有问题

image

在my.ini文件中指定mysql 启动/错误日志文件路径，示例：

[mysql]
default-character-set=utf8
[mysqld]
port = 3306
max_connections=200
#服务端字符集
character-set-server=utf8
#mysql存储引擎
default-storage-engine=innodb
#mysql更目录
basedir = E:/sorft-installed/servers/mysql-5.7.33-winx64/
#数据文件存放目录
datadir = E:/sorft-installed/servers/mysql-5.7.33-winx64/data
#错误日志文件存放位置
log-error = E:/sorft-installed/servers/mysql-5.7.33-winx64/data/LAPTOP-3680KG7C.err
#sql模式
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#lc-messages-dir= ./share
#skip-grant-tables
  • 注意：修改root账户的访问权限

二、SQL基础

重要：在维护数据库之前,一定要记得备份数据库

1、MySQL 创建数据库

带同学们了解一下终端

语法如下:

CREATE DATABASE 数据库名;

以下命令简单的演示了创建数据库的过程 ：

[root@host]# mysql -u root -p   
Enter password:******  # 登录后进入终端

mysql> create DATABASE RUNOOB;

2、建表最简单的要记得语句格式

create table 表名(
  列名1 类型1,
  列名2 类型2,
    ...
);

3、INSERT INTO 语句

作用：

  • INSERT INTO 语句用于向表中插入新记录。

语法：

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

示例：

insert into t_school values
(1000,'华瑞','岳麓区','各种软件培训课程....',1,2,99)

insert into t_school(school_name) values('湖南师大')

2、UPDATE 语句

特别注意：你们在维护数据库的时候 进行 修改/删除操作时 一定要记得带where条件

作用：

  • UPDATE 语句用于更新表中已存在的记录

语法：

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

示例：

update t_school set 
school_name = '华瑞IT教育',
intro = '培训java、.net、web、视频制作等课程'
where id = 1000

3、DELETE 语句

特别注意：你们在维护数据库的时候 进行 修改/删除操作时 一定要记得带where条件

建议在执行删除语句之前,先写好查询语句,肉眼确定查询结果没有问题,然后在编写好删除语句,将查询语句的where条件复制过去即可

作用：

  • DELETE 语句用于删除表中的行

语法：

DELETE FROM table_name
WHERE some_column=some_value;

示例：

delete from t_school where id = 1000

4、SELECT 语句

作用：

  • SELECT 语句用于从数据库中选取数据。
  • 结果被存储在一个结果表中，称为结果集。

语法：

SELECT column_name,column_name
FROM table_name;

与

SELECT * FROM table_name;

示例：

select * from t_school

select id,school_name,intro 
from t_school

5、WHERE 子句

作用：

  • WHERE 子句用于提取那些满足指定条件的记录。

语法：

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

WHERE子句中的运算符：

运算符 描述
= 等于
<> 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值

示例:

select * from t_course where 
cost BETWEEN 500 and 1000

select * from t_course where 
course_name like '%声乐%'

select * from t_course where 
cost in (400,960)

6、AND & OR 运算符

作用：

  • 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
  • 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

示例：

-- and 用法
select * from t_course where 
cost >= 500 and cost <= 1000

-- or 用法
select * from t_course where 
cost = 400 or cost = 960

7、ORDER BY 关键字

作用：

  • ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
  • ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，你可以使用 DESC 关键字。

语法：

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

示例 asc、desc：

select * from t_course 
where cost is not null
order by cost desc

示例 order by 多列:

select * from t_course 
order by cost,id

8、SELECT DISTINCT 语句

作用：

  • SELECT DISTINCT 语句用于返回唯一不同的值。

语法：

SELECT DISTINCT column_name,column_name
FROM table_name;

示例：

select DISTINCT school_name,area_code,intro from t_school

9、create table table1 as select * from table2

作用：

创建table1这张表，并将table2表中数据复制到table1中

语法：

create table targer_table as select * from source_table

10、inert into table1 select * from table2

作用：

将table2表中数据复制到table1中，table1必须存在

语法：

insert into target_table(column1,column2) select column1,column2 from source_table

11、SELECT TOP, LIMIT, ROWNUM 子句

作用：

  • SELECT TOP 子句用于规定要返回的记录的数目。
  • SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。

注意:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

MySQL 语法:

SELECT column_name(s)
FROM table_name
LIMIT number;

示例：

limit 后面第一个参数表示从第几条开始

第二个参数表示抓几条出来

select * from t_course limit 0,10

Oracle 语法:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

SQL Server / MS Access 语法:

SELECT TOP number|percent column_name(s)
FROM table_name;

12、like、in、BETWEEN

like：

  • LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

示例：

in :

  • IN 操作符允许你在 WHERE 子句中规定多个值。

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

示例：

between:

  • BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

示例：

13、join

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

image

13.1、inner join

语法:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释：INNER JOIN 与 JOIN 是相同的

image

示例：

select s.school_name,c.course_name from t_school s 
inner join t_course c 
on (s.id = c.school_id)

13.2、LEFT JOIN

语法:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

image

示例：

select s.school_name,c.course_name from t_school s 
left join t_course c 
on (s.id = c.school_id)

13.3、RIGHT JOIN

语法:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

image

示例：

select s.school_name,c.course_name from t_school s 
right join t_course c 
on (s.id = c.school_id)

13.4、FULL OUTER JOIN(注意mysql不支持全连)

语法:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

image

示例：

13.5、笛卡儿乘积

这个语句的结果和内连接结果一模一样，但是建议大家使用 连接查询，因为连接查询速度快一点

select c.clz_name,s.stu_name  
from class c,student s 
where c.id = s.clz_id

13.6、自连接

SELECT
	*
FROM
	depart d1
INNER JOIN depart d2 ON (
	d1.dept_name = '高中组'
	AND d1.parent_id = d2.id
)

13.7、派生表

select * from 
(select stu_name from student 
group by stu_name) t1 
where t1.stu_name = '东郭'

14、UNION 操作符

作用：

  • UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
  • 请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

union 语法:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

union all 语法:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

示例：

三、函数

1、AVG() 函数

作用：

  • AVG() 函数返回数值列的平均值。

语法:

SELECT AVG(column_name) FROM table_name

示例：

select avg(cost) from t_course where cost in (960,400)

2、COUNT() 函数

作用：

  • COUNT() 函数返回匹配指定条件的行数。

语法:

​ COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

​ COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

​ COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name;

​ 注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。

示例：

select count(id) from t_course

select count(cost) from t_course

-- 里面加上DISTINCT 就会先去重复,在统计
select count(DISTINCT cost) from t_course

3、MAX() 函数

作用：

  • MAX() 函数返回指定列的最大值。

语法:

SELECT MAX(column_name) FROM table_name;

示例：

select max(cost) from t_course

4、MIN() 函数

作用：

  • MIN() 函数返回指定列的最小值

语法:

SELECT MIN(column_name) FROM table_name;

示例：

select min(cost) from t_course

5、SUM() 函数

作用：

  • SUM() 函数返回数值列的求和数 。

语法:

SELECT SUM(column_name) FROM table_name;

示例：

select sum(cost) from t_course

6、GROUP BY 语句

作用：

  • GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
  • GROUP BY 语句可结合一些聚合函数来使用

语法:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

示例：

select cost,count(id),sum(cost) from t_course 
group by cost

7、HAVING 子句

作用：

  • 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
  • HAVING 子句可以让我们筛选分组后的各组数据。

语法:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

示例：

select cost,count(id),sum(cost) from t_course 
group by cost 
having sum(cost) > 3000 and count(id) > 10

8、EXISTS 运算符

说明：

其实它和 in 、not in功能一模一样.

但是 in、not in效率较低,所以 EXISTS就出来了

作用：

  • EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False 。

语法:

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

示例：

select * from t_course c
where 
-- school_id in (12,14,15,16)
	EXISTS (
	select id from t_school s where 
	s.area_code = '430105000000' and 
	c.school_id = s.id
	)

EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：

示例：

9、日期函数

9.1、DATE_FORMAT(date，format) 日期转字符串函数

说明：

current_timestamp 是mysql的内置变量,表示系统当前时间

作用：

  • DATE_FORMAT函数可以将mysql的日期类型转换成指定格式的字符串

示例：

select DATE_FORMAT(current_timestamp,'%Y-%m-%d %H:%i:%s')

format参数中的占位符说明：

  • %Y 年, 数字, 4 位
  • %y 年, 数字, 2 位
  • %d 月份中的天数, 数字(00……31)
  • %m 月, 数字(01……12)
  • %H 小时(00……23)
  • %h 小时(01……12)
  • %i 分钟, 数字(00……59)
  • %s 秒(00……59)

9.2、STR_TO_DATE(str,format) 字符串转日期函数

示例：

select str_to_date(
'1980-12-17 00:00:00',
'%Y-%m-%d %H:%i:%s'
)

format参数中的占位符说明：

  • 同9.1

9.3、 Unix 时间戳、日期 转换函数

时间戳说明：

unix时间戳是从1970年1月1日 0时0分0秒（格林威治时间）开始所经过的秒数 ,需要给同学们做例子让其了解

  • js代码说明时间戳示例：

日期转时间戳函数：

unix_timestamp(date)
  • 示例：

时间戳转日期函数：

from_unixtime(unix_timestamp)
  • 示例：

时间戳转字符串函数：

from_unixtime(unix_timestamp,format)
  • 示例：

四、sql语句执行顺序

  1. from 子句组装来自不同数据源的数据；
  2. where 子句基于指定的条件对记录行进行筛选；
  3. group by 子句将数据划分为多个分组；
  4. 使用聚集函数进行计算；
  5. 使用 having 子句筛选分组；
  6. 计算所有的表达式；
  7. select 的字段；
  8. 使用 order by 对结果集进行排序

五、mysql数据库备份

  • 重要：在实施项目升级或修改数据过程中，一定要先对现有的生产环境的数据进行备份。硬件有价，数据无价，如果在实施项目升级或修改数据的过程中出了问题，有备份的数据还可以还原。

1、利用navicat工具来备份

image

2、利用navicat工具来还原备份

image

六、索引、视图

1、什么是索引？

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

总结优缺点：

优点：

  • 通过创建唯一索引，保证数据库表每行数据的唯一性
  • 大大加快数据查询速度
  • 在使用分组和排序进行数据查询时，可以显著减少查询中分组和排序的时间

缺点：

  • 维护索引需要耗费数据库资源
  • 索引需要占用磁盘空间，索引文件可能比数据文件更快达到最大文件尺寸
  • 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

1.1、普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

  • 下面所有的列子均以此表为例：
create table book(
	book_id int not null comment 'id',
	book_name varchar(255) not null comment '书名',
  authors varchar(255) not null comment '作者',
  info varchar(255) null comment '书本简介',
  comment varchar(255) null comment '书本的评价',
  year_publication year not null comment '出版年份'
)comment = '书本表';
  • 直接创建：
CREATE INDEX indexName ON table_name (column_name)

示例：

-- 创建给book_name字段创建普通索引
CREATE INDEX idx_book_name ON book (book_name)
  • 修改表结构(添加索引)：
ALTER table tableName ADD INDEX indexName(columnName)

示例：

-- 修改book表结构，给book_name字段添加普通索引
alter table book add index idx_book_name(book_name)
  • 创建表的时候直接指定：
CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

示例：

create table book(
	book_id int not null comment 'id',
	book_name varchar(255) not null comment '书名',
  authors varchar(255) not null comment '作者',
  info varchar(255) null comment '书本简介',
  comment varchar(255) null comment '书本的评价',
  year_publication year not null comment '出版年份',

	-- 给book_name 指定索引
	index idx_book_book_name (book_name(255))
)comment = '书本表';
  • 删除索引的语法：
DROP INDEX [indexName] ON mytable;

示例：

-- 删除book表上索引名称为 idx_book_name 的索引
drop index idx_book_name on book;

1.2、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： 下面的我就不示例了,其实和创建普通索引方式差不太多

  • 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
  • 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
  • 创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);

1.3、ALTER 命令添加和删除主键

主键自带唯一索引，且不允许为空

语法：

  • 修改指定表的字段
ALTER TABLE table_name MODIFY column_name INT NOT NULL;

示例：

-- 修改book表中 book_id字段为 int类型 且不允许为null
alter table book modify book_id int not null;
  • 指定一列或多列为主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name1,column_name2,...);

示例：

-- 将book表中 book_id,book_name 作为联合主键
alter table book add PRIMARY key (book_id,book_name)
  • 删除主键：
ALTER TABLE table_name DROP PRIMARY KEY

示例:

-- 删除book表中的主键
alter table book drop PRIMARY key;

2、视图

2.1、为什么要用视图？

  • 安全性：一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定，这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。
  • 查询性能提高
  • 有灵活性的功能需求后，需要改动表的结构而导致工作量比较大，那么可以使用虚拟表的形式达到少修改的效果。这是在实际开发中比较有用的
  • 复杂的查询需求，可以进行问题分解，然后将创建多个视图获取数据。将视图联合起来就能得到需要的结果了。

2.2、创建视图

语法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
  • CREATE：表示新建视图；
  • REPLACE：表示替换已有视图
  • ALGORITHM ：表示视图选择算法
    • ALGORITHM子句是对标准SQL的MySQL扩展。
    • ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。
    • 如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。
    • 对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
    • 对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。
    • 对于UNDEFINED，MySQL自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。
  • view_name ：视图名
  • column_list：属性列
  • select_statement：表示select语句
  • [WITH [CASCADED | LOCAL] CHECK OPTION]参数表示视图在更新时保证在视图的权限范围之内

示例：

-- 创建一张视图，名称为 v_book,如果v_book已经存在则替换掉以前的
create or replace view v_book as 
-- 视图语句
	select * from book;

七、存储过程、函数

1、存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

  • 存储过程可封装，并隐藏复杂的商业逻辑。
  • 存储过程可以回传值，并可以接受参数。
  • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  • 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

  • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

2、创建存储过程、函数

存储过程定义语法：
CREATE PROCEDURE 存储过程名称 ([定义参数[,...]])
begin
    -- 存储过程身体,是一个常规的sql语句
end;
参数定义语法和示例：
[ IN | OUT | INOUT ] 参数名称 参数类型

示例1：创建一个无参数的存储过程

-- 定义了一个存储过程，名称为demo_proc，无参数
CREATE PROCEDURE demo_proc ()
begin
	--内容sql块很简单，做了个查询
    select * from book;
end;

示例2：创建一个带参数的存储过程

-- 定义了2个变量
-- in p_book_id int：in表示该变量数据由外部调用者传入，变量名称 p_book_id,变量类型int
-- out p_book_name varchar(255):out表示该变量由内部存入数据传递给外部调用者使用，变量名称为 p_book_name,变量类型为varchar(255)
CREATE PROCEDURE demo_proc (in p_book_id int,out p_book_name varchar(255))
begin
		-- 这条sql语句的作用 根据 p_book_id 获取一条记录，
    -- 并且将这条记录的 book_name字段的值 存到 p_book_name变量中，传递给外部调用者
    select book_name into p_book_name from book where book_id = p_book_id;
end;

调用示例：

-- 定义了一个变量，名称为 @book_id，并赋值为1
-- 注意：@开头的变量为用户变量，这种变量不用指定类型就可以直接往里面存数据
set @book_id = 1;
-- 调用存储过程，将@book_id变量值传递给该过程，并且定义了一个用户变量 @book_name用于接收该存储过程运行后的值
call demo_proc(@book_id,@book_name);
-- 看看调用存储过程后 @book_name这个变量里面是什么
select @book_name;
Mysql定义变量示例：
-- DECLARE 关键字
-- r_name 变量名称
-- varchar(255) 变量类型
-- default '张三' ，默认值为 张三
DECLARE r_name varchar(255) default '张三';
Mysql创建函数示例：

​ 函数和存储过程非常相似，不同是的 函数有返回值，存储过程没有返回值。

​ 语法上：函数的关键词是function，特别注意：函数的参数不允许带 in、out、inout之类的

create function 存储函数名(参数) returns 变量类型  
begin
	-- sql语句块
	-- 函数可以有返回值
	return 变量;
end

示例：

-- 创建一个函数，名称为demo_fuc
-- p_book_id是参数，注意：该参数前面不能有in、out、inout之类的
create function demo_fuc(p_book_id int) 
-- 这个函数的返回值为 varchar(255)这个类型
returns varchar(255)
begin 
	-- 定义个变量
	DECLARE r_book_name varchar(255) DEFAULT null;
	-- 查询book_name值并存到 r_book_name 变量中
	select book_name into r_book_name from book where book_id = p_book_id;
	-- 返回 r_book_name 变量
	return r_book_name;
end;

函数调用示例：

-- 函数调用示例
-- 调用demo_fuc函数，并将结果赋值到@book_name变量中，注意，赋值关键词 set
set @book_name = demo_fuc(1);
-- 看看这个变量值
select @book_name;
循环、条件示例：
  • while循环
while 条件 do
    -- 循环体
end while

示例代码：

-- 累加函数
create function demo_fuc1(p_num int) returns int 
BEGIN
	-- 定义变量 i,初始值为 1
	declare i int default 1;
	-- 定义变量，用于存储累加的值
	declare sum int DEFAULT 0;
	-- 如果 i小于p_num值 继续循环
	while i<p_num do
    -- 累加，并赋值
		set sum = sum + i;
		-- i+1，没有这个就死循环了
		set i = i + 1;
	end while;
	-- 最后返回
	return sum;
end;

调用代码：

-- 调用函数
select demo_fuc1(3);
  • repeat循环
repeat
    --循环体
until 循环条件  
end repeat;

这个和while没什么太大区别，就不示例了。

  • loop循环

loop ·····endloop

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

示例代码：

-- 累加函数
create function demo_fuc1(p_num int) returns int 
BEGIN
	-- 定义变量 i,初始值为 1
	declare i int default 1;
	-- 定义变量，用于存储累加的值
	declare sum int DEFAULT 0;
	-- loop循环，前面必须顶一个标签名称
	lable:loop
		-- 如果 i大于p_num值
		if i > p_num then 
			-- 离开循环,到lable那里
			LEAVE lable;
		end if;
		-- 累加，并赋值
		set sum = sum + i;
		-- i+1，没有这个就死循环了
		set i = i + 1;
	end loop;
	-- 最后返回
	return sum;
end;

3、删除存储过程、函数

-- 删除存储过程
drop procedure 存储过程名称;
-- 删除函数
drop function 函数名称;

八、触发器

在MySQL中，触发器是一组SQL语句，当对关联表上的数据进行更改时会自动调用这些语句。可以定义触发器在INSERT，UPDATE或DELETE语句更改数据之前或之后调用。可以为每个表定义最多六个触发器。

  • BEFORE INSERT - 在将数据插入表格之前触发。
  • AFTER INSERT - 将数据插入表格后触发。
  • BEFORE UPDATE - 在更新表中的数据之前触发。
  • AFTER UPDATE - 更新表中的数据后触发。
  • BEFORE DELETE - 在从表中删除数据之前触发。
  • AFTER DELETE - 从表中删除数据后触发

1、语法

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN

        执行语句列表

END;
触发器类型 NEW和OLD使用
insert型触发器 NEW表示将要或已新增的数据
update型触发器 OLD表示将要或已被删除的数据，NEW表示将要或已新增的数据
delete型触发器 OLD表示将要或已被删除的数据

根据以上的表格，可以使用一下格式来使用相应的数据：

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据

示例：我这里给book表添加一个插入一行数据后 触发的 触发器，这个触发器的功能就是将当前年份作为book表中的出版年份

-- 创建一个在insert之前的触发器
-- 该触发器名称 trig_book_after_insert 
create trigger trig_book_before_insert before insert 
-- 在触发器建立在book表上，FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
on book for each row
BEGIN
		-- 设置 year_publication 字段值
	set NEW.year_publication = year(SYSDATE());
end;

测试：

-- 插入一条数据试试
insert into book(book_id,book_name,authors,info,comment) values
(10,'避邪剑法','林平之祖宗','葵花宝典改版，小成便可万邪不侵，切鸡如切菜','YYDS')

九、事物

事务是最小的工作单元，该单元由对数据库的若干操作组成，这些操作要么整体成功，要么整体失败，从而保证数据的一致性。

典型事例：银行转帐操作

对于一个MYSQL数据库（InnoDB），事务的开启与提交模式无非下面这两种情况：

  • 若参数autocommit=0，事务则在用户本次对数据进行操作时自动开启，在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。
  • 若参数autocommit=1（系统默认值），事务的开启与提交又分为两种状态：
  • 手动开启手动提交：当用户执行start transaction命令时（事务初始化），一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。
  • 自动开启自动提交：如果用户在当前情况下（参数autocommit=1）未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。
-- 设置手动提交事物
set autocommit=0;
-- 开启事物
start TRANSACTION;
-- 执行语句
update user set user_name = '猪八戒' where id = 2;

-- 提交事物，若无提交语句，自动回滚
commit;

1、事务的特征(ACID)

  • 原子性(A)

指事务中的操作，要么都完成，要么都取消。

  • 一致性(C)

指事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况。

  • 隔离性(I)

多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

  • 永久性(D)

一个事务一旦提交，他对数据库的修改应该永久保存在数据库中 。

操作数据库可能出现的情况

更新丢失：两个事务更新同一条数据，一个事务失败可能导致另一个事务失败

脏读取: 一个事务读取一条记录，但这条记录被另一事务更新，但未被提交

不可重复读取：一个事务读取同一条记录多次返回多个不同结果。

两次提交： 两个事务提交同一记录，最后一次会造成第一次提交失败。

虚读(也叫幻度)：一个事务查询多次时，出现不同结果。即：在第一个事物 第二次读取数据之前，第二个事物修改了数据

2、数据库 锁

  • 锁概念

生活中的锁：
锁的作用就是保护用户私有的空间。

Oracle中的锁：
锁是数据库用来控制共享资源并发访问的机制。
锁用于保护正在被修改的数据
直到提交或回滚了事务之后，其他用户才可以更新数据

事物和锁之间的关系

​ 事务是一个单元的工作，要么全做，要么全不做

​ 事务保证操作的一致性和可恢复性

​ 实际使用的事务是用户定义的事务，它包括一系列操作或者语句

在多服务器环境中，使用用户定义的分布式事务，保证操作的一致性

​ 但是由于事物操作数据库可能出现 脏度、幻度....等问题，为了解决这些问题可以利用锁机制

2.1、锁分类

2.1.1、悲观锁

顾名思义，很悲观，就是每次拿数据的时候都认为别的线程会修改数据，所以在每次拿的时候都会给数据上锁。上锁之后，当别的线程想要拿数据时，就会阻塞，直到给数据上锁的线程将事务提交或者回滚。传统的关系型数据库里就用到了很多这种锁机制，比如行锁，表锁，共享锁，排他锁等，都是在做操作之前先上锁。

而且我们一般意义上讲的锁指的就是悲观锁

2.1.1.1、共享锁

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。

获取共享锁的事务只能读数据，不能修改数据。

共享锁无法阻止其他用户读取和修改表中的数据，只能阻止其他用户使用ALTER TABLE命令改变指定表的结构或用DROP TABLE命令删除指定表

注1：共享锁是表级的,没有所谓的行级共享锁，比如Select会对表加共享锁）

注2：select * from table for update 会给表加共享锁，每一行数据加排他锁(3级独占锁)

2.1.1.2、排他锁

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获取排他锁的事务既能读数据，又能修改数据。

注：排他锁一般是行级的，比如DML操作 insert update delete，在执行DML操作时分两步加锁，先加表级共享锁，后给被修改的数据加排他锁。在添加排他锁后，不能添加任何锁直至锁释放 commit或者rollback

2.1.2、乐观锁

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号等于数据库表当前版本号，则予以更新，否则认为是过期数据。

九、作业

1. Student、Course、SC、Teacher 表关系如下：

Student（sid，Sname，Sage，Ssex）学生表

Course（cid，Cname，tid）课程表

SC（sid，cid，score）成绩表

Teacher（tid，Tname）教师表

写出 sql 语句：

  • 查询课程“001“课程比”002“课程成绩高的所有学生的学号
  • 修改学号为 20131201 的语文成绩为 100‘
  • 插入一条名为“李四”的教师记录
  • 删除学习“叶平”老师课程的 sc 表记录

2. 员工信息 A、员工亲属信息表 B 表关系如下：

​ 员工信息表 A：员工标号（code，PK），员工姓名（name），员工性别（sex），联系电话（tel），备注（remarks）

​ 员工亲属信息表 B：亲属编码（recodecode，PK），员工编码（recode），亲属姓名（recodename），联系电话（recodetel），备注（remarks）

写出 sql 语句：

  • 向员工信息表中插入一条数据：（001，张三，男，010-62570007，北京市海淀区）
  • 查询出亲属数量大于 1 的员工编码，员工姓名，员工亲属数量有部分员工亲属信息重复录入（亲属编码不同，其他相同）

3. 部门表 dept、雇员表 emp 表关系如下：

​ 部门表 dept：部门标号（DEPTNO），部门名称（DNAME），所在位置（LOC）

​ 雇员表 emp：员工标号（Empno），员工名称（Emname），员工工位（Job），经理（Mgr），雇佣日期（Hiredate），薪水（Sal），部门编号（Deptno）

写出 sql 语句：

  • 找出部门名称为 ACCOUNTING 的部门下的所有员工名称？
  • 找出部门名称为 SALES 的部门下每月需要发出的薪水总额？
  • 找出部门名称为 SALES 的部门的部门经理？
  • 找出部门名称为 RESEARCH 的部门下雇佣日期为 1980-12-17 的员工？

4. Student、course、Student_course表关系如下：

student(sno,sname,age,sdept) 学生表

course(cno,cname,teacher) 课程表

Student_course(sno,cno,grade)选课表

写出 sql 语句：

1)查询所有课程都及格的学生号和姓名

2)查询平均分不及格的课程号和平均成绩

3)找出各门课程的平均成绩，输出课程号和平均成绩

  1. 找出没有选择 c2 课程的学生信息

5. 现有一张用户信息表user_info 数据如下，按照要求编写sql语句

uid(用户ID) user(用户名) post(帖子数) hits(点击数) date(日期)
1 小张 90 983 2012-03-04
2 小王 123 243 2014-06-21
3 小三 998 100 2014-06-21
4 小三 782 983 2014-06-03
5 小三 344 334 2014-07-02 
  1. 请用一条 SQL语句查询出发帖数大于 5点击数由高到低排序？

  2. 用一条 SQL语句将“日期”为 21日的记录帖子数全部设置为 0？

  3. 用一条 SQL筛选出 2014 年的记录且 post大约 500 的数据，将其 hits减少 50

6. SQL 操作，有两张表，如下所示

订单表：A

Order_id User_id Add_time
11701245001 10000 1498882474
11701245002 10001 1498882475

订单明细表：B

id Order_id Goods_id price
1 11701245001 1001 10
2 11701245001 1002 20
3 11701245002 1001 10 
  1. 用 SQL查询购买过 goods_id为 1001的用户的 user_id

  2. 用 SQL查询 2017年 7月 1号后（含 7月 1号）购买过 1001这个商品的 user_id和 oeder_id, goods_id和price

  3. 用 SQL查询出订单所含商品明细总金额〉=50 的 order_id和 user_id

7. 题目

下面是学生成绩表（score）结构说明

字段名称 字段解释 字段类型 字段长度 约束
Sc_number 学号 字符 8 Pk
Sc_name 姓名 字符 50 Not null
Sc_sex 性别 字符（男：1，女：2） 2 Not null
Sc_courseid 课程号 字符 5 Pk
Sc_score 分数 数值 3 Not null
Sc_ismkaeup 当前考试是否为补考 字符（补考：1，非补考：0） 2 Not null

下面是课程表（course）说明

字段名称 字段解释 字段类型 字段长度 约束
Co_id 课程号 字符 5 Pk
Co_name 课程名 字符 3 Not null
Co_desc 课程介绍 字符 60 
  1. 请说明主键，外键的作用，以及建立索引的好处及坏处。
  • 主键：能够唯一标识数据表中每条数据的字段，并且该字段非空值，就可以用primary key 声明该字段为主键
  • 外键：如果T表中的t1字段和F表中主键是对应的，就可以用foreign key声明t1字段为外键

建立索引的优点：

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以加速表和表之间的连接。
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

索引有一些先天不足：

  • 建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。
  • 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
  • 实践表明，不恰当的索引不但于事无补，反而会降低系统性能
  1. 请写出课程表中建表 SQL语句。

  2. 如果学号的前两位表示年级，要查找 98级女生的姓名，请写出相应的 SQL语句。

要查找所需要补考（小于 60分）的学生姓名和这门课程的名称和成绩，请写出相应的 SQL语句。

查询每个学生需要补考（小于 60分）的课程的平均分，并以平均分排序

8. 懒投资首页的懒人播报，统计了在懒投资平台的投资富豪榜，对应的库表简化如下：

用户表：user

Id(int) 用户id
Name(varchar) 用户名
gender

投资订单表：orders

Id(int) 订单号
User_id(int) 用户ID
Amount(int) 该笔订单投资额
Add_time

富豪榜统计需求：

  1. 请给出，所有投资用户中，投资总额排名前 10位的用户，按投资总额倒序排列，输出项如下，

例如：

用户名，投资总额

张三，9000000

李四，8000000

...

  1. 给出富豪榜第一名的用户的单笔平均投资额

9、提前将下次课的 VMware 安装好

博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3