一、数据库介绍
1、数据库是啥
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较
慢。所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)的特点:
1.数据以表格的形式出现
2.每行为,各种记录名称
3.每列为,记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
2、RDBMS术语
在我们开始学习MySQL 数据库前,让我们先了解下RDBMS的一些术语:
数据库: 数据库是一些关联表的集合。.
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
冗余:存储两倍数据,冗余可以使系统速度更快。(表的规范化程度越高,表与表之间的关系就越多;查询时可能经常需要在多个表之间进行连接查询;而进行连接操作会降低查询速度。例如,学生的信息存储在student表中,院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称,必须从student表中查找学生所在院系的编号(dept_id),然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时,连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name,该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
3、Mysql数据库
Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
Mysql是开源的,所以你不需要支付额外的费用。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
Mysql对PHP有很好的支持,PHP是目前最流行的Web开发语言。
MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
Mysql是可以定制的,采用了GPL协议,你可以修改源码来开发自己的Mysql系统。
想要使用MySQL来存储并操作数据,则需要做几件事情:
a. 安装MySQL服务端
b. 安装MySQL客户端
b. 【客户端】连接【服务端】
c. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等)
二、安装MySQL
Windows安装MySQL
1、安装
MySQL Community Server 5.7.16http://dev.mysql.com/downloads/mysql/
2、解压
如果想要让MySQL安装在指定目录,那么就将解压后的文件夹移动到指定目录,如:C:\mysql-5.7.16-winx64
3、初始化
MySQL解压后的 bin 目录下有一大堆的可执行文件,执行如下命令初始化数据:
cd D:\mysql-5.7.16-winx64\bin
mysqld--initialize-insecure
4、启动MySQL服务
a.将MySQL可执行文件添加到环境变量中,从而执行执行命令即可
【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行,双击】--> 【将MySQL的bin目录路径追加到变值值中,用 ; 分割】
如此一来,以后再启动服务并连接时,仅需
#启动MySQL服务,在终端输入
mysqld#连接MySQL服务,在终端输入:
mysql -u root -p
b.将MySQL服务制作成windows服务
上一步解决了一些问题,但不够彻底,因为在执行【mysqd】启动MySQL服务器时,当前终端会被hang住,那么做一下设置即可解决此问题:
#制作MySQL的Windows服务,在终端执行此命令:
"c:\mysql-5.7.16-winx64\bin\mysqld" --install#移除MySQL的Windows服务,在终端执行此命令:
"c:\mysql-5.7.16-winx64\bin\mysqld" --remove
注册成服务之后,以后再启动和关闭MySQL服务时,仅需执行如下命令:
#启动MySQL服务
net start mysql#关闭MySQL服务
net stop mysql
centos7安装MySQL
1. 下载mysql的repo源
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
2. 安装mysql-community-release-el7-5.noarch.rpm包
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
安装这个包后,会获得两个mysql的yum repo源:/etc/yum.repos.d/mysql-community.repo,/etc/yum.repos.d/mysql-community-source.repo。
3. 安装mysql
sudo yum install mysql-server
4. 重置密码
mysql -u root
登录时有可能报这样的错:ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2),原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户:
sudo chown -R openscanner:openscanner /var/lib/mysql
然后,重启服务:
service mysqld restart
mysql -u root
接下来登录重置密码:
mysql > use mysql;
mysql > update user set password=password(‘1‘) where user=‘root‘;
5. 需要更改权限才能实现远程连接MYSQL数据库
mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| 127.0.0.1 | root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| localhost | | |
+-----------+------+-------------------------------------------+
4 rows in set (0.01 sec)
由此可以看出,只能以localhost的主机方式访问MySQL。
解决方法:
mysql> Grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; ( % 表示所有的IP都可以访问 )
mysql> flush privileges; (运行此句才生效,或者重启MySQL)
再次查看:
mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| 127.0.0.1 | root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| localhost | | |
| % | root | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-----------+------+-------------------------------------------+
4 rows in set (0.01 sec)
6. 远程连接mysql
mysql -h 192.168.219.22 -u user -p
三、数据库操作
管理MySQL的命令,以下列出了使用Mysql数据库过程中常用的命令:
use 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
show databases: 列出 MySQL 数据库管理系统的数据库列表。
show tables: 显示指定数据库的所有表,使用该命令前需要使用 use命令来选择要操作的数据库。
show columns from 数据表名: 显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
create database testdb charset "utf8": #创建一个叫testdb的数据库,且让其支持中文
drop database 数据库名: #删除数据库
show index from 数据表名:显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
1、显示数据库
SHOW DATABASES;
默认有的数据库:
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据
2、创建数据库
#utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;#gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
3、使用数据库
USE db_name;
显示当前使用的数据库中所有表:SHOW TABLES;
4、用户管理
创建用户
create user'用户名'@'IP地址' identified by '密码';
删除用户
drop user'用户名'@'IP地址';
修改用户
rename user'用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set passwordfor '用户名'@'IP地址' = Password('新密码')
PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
5、授权管理
show grants for '用户'@'IP地址' --查看权限
grant 权限 on 数据库.表 to'用户'@'IP地址' --授权
revoke 权限 on 数据库.表from '用户'@'IP地址' -- 取消权限
Grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
四、数据表操作
1、创建表
create table 表名(
列名 类型 是否可以为空,
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
数据库引擎:
innodb:支持事务,原子性操作。(事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。)
myisam: 存储速度快,支持全局索引
是否可以为空:
not null -不可空null - 可空,非字符串
默认值:
创建列时,可以指定默认值,当插入数据时,没有主动设置,则自动添加默认值
create table tb1(
nidint not null defalut 2,
numint not null)
自增:
如果设置某列为自增列,插入数据时无需设置此列数据,默认将自增(表中只能有一个自增列)
create tabletb1(
nidint not null auto_increment primary key,
numint null)
或create tabletb1(
nidint not nullauto_increment,
numint null,index(nid)
)
注意:1、对于自增列,必须是索引(含主键)。2、对于自增可以设置步长和起始值
基于会话的:
show session variableslike 'auto_inc%';set session auto_increment_increment=2;set session auto_increment_offset=10;
基于全局的:
shwo global variableslike 'auto_inc%';set global auto_increment_increment=2;set global auto_increment_offset=10;
主键:
一种特殊的唯一索引,不允许有空值,如果主键是单列,则它的值必须唯一,如果是多列,则它的组合必须唯一。
create tabletb1(
nidint not null auto_increment primary key,
numint null)
或create tabletb1(
nidint not null,
numint not null,primary key(nid,num)
)
外键:
一个特定的索引,只能是被指定的内容。
creat tablecolor(
nidint not null primary key,
namechar(16) not null)create tablefruit(
nidint not null primary key,
smtchar(32) null,
color_idint not null,constraint fk_cc foreign key (color_id) referencescolor(nid)
)
2、删除表
drop table 表名;
3、清空表
delete from表名; 清空之后,id自增的话,增加新数据id不是从1开始
truncate table 表名; 清空之后,id自增的话,增加新数据id是从1开始,如果数据量很大的话,速度会比上面的快
4、修改表
添加列:alter table 表名 add 列名 类型;
删除列:alter table 表名 drop column 列名;
修改列:alter table 表名 modify column 列名 类型;--类型
alter table 表名 change 原列名 新列名 类型;--列名,类型
添加主键:alter table 表名 add primary key(列名);
删除主键:alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键:alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除外键:alter table 表名 drop foreign key 外键名称
修改默认值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT1000;
删除默认值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5、基本数据类型
MySQL中定义数据字段的类型对数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型大小范围(有符号)范围(无符号)用途
TINYINT
1 字节
(-128,127)
(0,255)
小整数值
SMALLINT
2 字节
(-32 768,32 767)
(0,65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608,8 388 607)
(0,16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648,2 147 483 647)
(0,4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)
(0,18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
(-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)
0,(1.175 494 351 E-38,3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 字节
(1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2
依赖于M和D的值
依赖于M和D的值
小数值
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型大小
(字节)范围格式用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/
9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4
1970-01-01 00:00:00/2037 年某时
YYYYMMDD HHMMSS
混合日期和时间值,时间戳
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型大小用途
CHAR
0-255字节
定长字符串,
VARCHAR
0-65535 字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LONGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值。
BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。
char与varchar的区别:
1、char的长度是不可变的,而varchar的长度是可变的
2、定义一个char[10]和varchar[10],如果存进去的是‘abcd’,那么char所占的长度依然为10,除了字符‘abcd’外,后面跟六个空格,而varchar就立马把长度变为4了,取数据的时候,char类型的要用trim()去掉多余的空格,而varchar是不需要的
3、char的存取数度还是要比varchar要快得多,因为其长度固定,方便程序的存储与查找;但是char也为此付出的是空间的代价,因为其长度固定,所以难免会有多余的空格占位符占据空间,可谓是以空间换取时间效率,而varchar是以空间效率为首位的。
4、char的存储方式是,对英文字符(ASCII)占用1个字节,对一个汉字占用两个字节;而varchar的存储方式是,对每个英文字符占用2个字节,汉字也占用2个字节,两者的存储数据都非unicode的字符数据。
五、表内容操作
1、增
insert into 表 (列名,列名...) values (值,值,值...);
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);
insert into 表 (列名,列名...) select (列名,列名...)from 表;
2、删
delete from表
deletefrom 表 where id=1 and name='YL'
3、改
update 表 set name = 'alex' where id>1
4、查
select * from表
select* from 表 where id > 1select nid,name,gender as ggfrom 表 where id > 1
5、其他操作
a、条件
select* from 表 where id > 1 and name != 'YL' and num = 12;
select* from 表 where id between 5 and 16;
select* from 表 where id in (11,22,33)
select* from 表 where id not in (11,22,33)
select* from 表 where id in (select nid from表)
b、通配符
select* from 表 where name like 'ale%' -ale开头的所有(多个字符串)
select* from 表 where name like 'ale_' -ale开头的所有(一个字符)
c、限制
select* from 表 limit 5; -前5行
select* from 表 limit 4,5; -从第4行开始的5行
select* from 表 limit 5 offset 4 -从第4行开始的5行
d、排序
select* from 表 order by 列 asc -根据 “列” 从小到大排列
select* from 表 order by 列 desc -根据 “列” 从大到小排列
select* from 表 order by 列1 desc,列2 asc -根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
e、分组
select numfrom表 group by num
select num,nidfrom表 group by num,nid
select num,nidfrom 表 where nid > 10group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from表 group by num,nid
select numfrom 表 group by num having max(id) > 10特别的:group by 必须在where之后,order by之前
f、连表
无对应关系则不显示,笛卡尔积
select A.num, A.name, B.namefromA,B
Where A.nid=B.nid
无对应关系则不显示,出现null时,一行隐藏
select A.num, A.name, B.namefromA inner join B
on A.nid=B.nid
A表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.namefromA left join B
on A.nid=B.nid
B表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.namefromA right join B
on A.nid=B.nid
g、组合,上下连表
组合,自动去掉重合数据
select nicknamefromA
union
select namefromB
组合,不处理重合
select nicknamefromA
union all
select namefrom B
六、视图
视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。
临时表搜索:SELECT
*
FROM(SELECTnid,
NAMEFROMtb1WHEREnid> 2)ASAWHEREA. NAME= 'alex';
1、创建视图
--格式:CREATE VIEW 视图名称 AS SQL语句
CREATE VIEW v1 ASSELET nid,
nameFROMAWHEREnid> 4
2、删除视图
--格式:DROP VIEW 视图名称
DROP VIEW v1
3、修改视图
--格式:ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 ASSELET A.nid,
B. NAMEFROMALEFT JOIN B ON A.id =B.nidLEFT JOIN C ON A.id =C.nidWHEREA.id> 2
AND C.nid < 5
4、使用视图
使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。
select * from v1
七、触发器
当对某张表做“增删改”操作时,可以使用触发器自定义关联行为
1、创建触发器
# 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOREACH ROWBEGIN...END# 插入后CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOREACH ROWBEGIN...END# 删除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOREACH ROWBEGIN...END# 删除后CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOREACH ROWBEGIN...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOREACH ROWBEGIN...END# 更新后CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOREACH ROWBEGIN...END
插入数据前的触发:
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOREACH ROWBEGIN
IF NEW. NAME == 'yl' THEN
INSERT INTOtb2 (NAME)VALUES('aa')END IF
END//delimiter ;
插入数据后触发:
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOREACH ROWBEGIN
IF NEW. num = 666 THEN
INSERT INTOtb2 (NAME)VALUES('666'),('666') ;
ELSEIF NEW. num= 555 THEN
INSERT INTOtb2 (NAME)VALUES('555'),('555') ;END IF;END//delimiter ;
tips:
创建触发器的时候,需要将结束符临时更改,然后再改回去,用delimiter
NEW表示即将插入的数据行,OLD表示即将删除的数据行。
2、删除触发器
DROP TRIGGER tri_after_insert_tb1;
3、使用触发器
insert into tb1(num) values(666);
(插入,修改,删除)数据后,自动触发触发器
八、存储过程
存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。
优点:
实现程序代码与SQL的解耦,替代程序嵌入的SQL语句
基于网络传输,传输存储过程的名字数据量小,而直接传输SQL语句数据量大
缺点:
程序员扩展功能不方便
程序与数据库结合使用的三种方式:
#方式一:
MySQL:存储过程
程序:调用存储过程
#方式二:
MySQL:
程序:程序+SQL语句
#方式三:
MySQL:
程序:类和对象,即ORM(本质还是纯SQL语句)
1、创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
对于存储过程,可以接收参数,其参数有三类:
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
有参数的存储过程:
IN传入参数
delimiter//
create procedurep2(in n1 int,in n2 int)BEGIN
select * from blog where id >n1;END //delimiter ;
#在mysql中调用
call p2(3,2)
#在python中基于pymysql调用cursor.callproc('p2',(3,2))print(cursor.fetchall())
OUT 返回值
delimiter//
create procedurep3(in n1 int,
out resint)BEGIN
select * from blog where id >n1;set res = 1;END //delimiter ;
#在mysql中调用set @res=0; #0代表假(执行失败),1代表真(执行成功)
call p3(3,@res);select @res;
#在python中基于pymysql调用cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数,@p3_1代表第二个参数,即返回值print(cursor.fetchall())
INOUT 既可以传入又可以返回
delimiter//
create procedurep4(
inout n1int)BEGIN
select * from blog where id >n1;set n1 = 1;END //delimiter ;
#在mysql中调用set @x=3;
call p4(@x);select @x;
#在python中基于pymysql调用cursor.callproc('p4',(3,))print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p4_0;')print(cursor.fetchall())
返回结果集的存储过程:
delimiter //
create procedurep1()begin
select * fromv1;end //delimiter ;
返回结果集+out值的存储过程:
delimiter //
create procedurep2(in n1 int,
inout n3int,
out n2int,
)begin
declare temp1 int;declare temp2 int default 0;select * fromv1;set n2 = n1 + 100;set n3 = n3 + n1 + 100;end //delimiter ;
tips: 在存储过程中声明变量用declare
使用游标的存储过程:?
delimiter //
create procedurep3()begin
declare ssid int; --自定义变量1
declare ssname varchar(50); --自定义变量2
DECLARE done INT DEFAULTFALSE;DECLARE my_cursor CURSOR FOR select sid,sname fromstudent;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;openmy_cursor;
xxoo: LOOPfetch my_cursor intossid,ssname;if done thenleave xxoo;END IF;insert into teacher(tname) values(ssname);endloop xxoo;closemy_cursor;end //delimter ;
动态执行SQL的存储过程:
delimiter \\CREATE PROCEDUREp4 (in nid int)BEGIN
PREPARE prod FROM 'select * from student where sid > ?';EXECUTE prod USING @nid;DEALLOCATE prepareprod;END\\
delimiter ;
支持事物的存储过程:
#介绍
delimiter//
create procedurep4(
out statusint)BEGIN
声明如果出现异常则执行{set status = 1;rollback;
}
开始事务--A账户减去100
--B账户加90
--C账户加10
commit;
结束set status = 2;END //delimiter ;
#实现
delimiter//
create PROCEDUREp5(
OUT p_return_codetinyint)BEGIN
DECLARE exit handler forsqlexceptionBEGIN
--ERROR
set p_return_code = 1;rollback;END;DECLARE exit handler forsqlwarningBEGIN
--WARNING
set p_return_code = 2;rollback;END;
STARTTRANSACTION;DELETE fromtb1; #执行失败insert into blog(name,sub_time) values('yyy',now());COMMIT;--SUCCESS
set p_return_code = 0; #0代表执行成功END //delimiter ;
#在mysql中调用存储过程set @res=123;
call p5(@res);select @res;
#在python中基于pymysql调用存储过程cursor.callproc('p5',(123,))print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p5_0;')print(cursor.fetchall())
2、执行存储过程
在MySQL中执行:
--无参数
call proc_name()--有参数,全in
call proc_name(1,2)--有参数,有in,out,inout
set @t1=0;set @t2=3;
call proc_name(1,2,@t1,@t2)
在python中用pymysql执行:
#!/usr/bin/env python#-*- coding:utf-8 -*-
importpymysql
conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor= conn.cursor(cursor=pymysql.cursors.DictCursor)#执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))#获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result=cursor.fetchall()
conn.commit()
cursor.close()
conn.close()print(result)
3、删除存储过程
drop procedure proc_name;
九、函数
MySQL中提供了许多内置函数,例如:
一、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值
RAND()
返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
CHAR_LENGTH(str)
返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ,则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接(自定义连接符)
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
CONV(N,from_base,to_base)
进制转换
例如:
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。
例如:
SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos:要替换位置其实位置
len:替换的长度
newstr:新字符串
特别的:
如果pos超过原字符串长度,则返回原字符串
如果len超过原字符串长度,则由新字符串完全替换
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
REVERSE(str)
返回字符串 str ,顺序和字符顺序相反。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。
使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式,格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)
重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');-> 'Sunday October 2009'mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');-> '22:23:00'mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',-> '%D %y %a %d %m %b %j');-> '4th 00 Thu 04 10 Oct 277'mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',-> '%H %k %I %r %T %S %w');-> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');-> '1998 52'mysql> SELECT DATE_FORMAT('2006-06-00', '%d');-> '00'五、加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本,这个加密过程是不可逆转的,和UNIX密码加密过程使用不同的算法。
六、控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真,则返回resultN,否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等,则返回resultN,否则返回default
IF(test,t,f)
如果test是真,返回t;否则返回f
IFNULL(arg1,arg2)
如果arg1不是空,返回arg1,否则返回arg2
NULLIF(arg1,arg2)
如果arg1=arg2返回NULL;否则返回arg1
七、控制流函数小练习#7.1、准备表
/*Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version :50720Source Host : localhost:3306Source Database : student
Target Server Type : MYSQL
Target Server Version :50720File Encoding :65001Date:2018-01-02 12:05:30
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure forcourse-- ----------------------------DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(11) NOT NULL,
`c_name` varchar(255) DEFAULT NULL,
`t_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
--Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');-- ----------------------------
-- Table structure forscore-- ----------------------------DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(10) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`num` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
--Records of score-- ----------------------------INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');-- ----------------------------
-- Table structure forstudent-- ----------------------------DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL,
`s_name` varchar(255) DEFAULT NULL,
`s_age` int(10) DEFAULT NULL,
`s_sex` char(1) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
--Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');-- ----------------------------
-- Table structure forteacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(10) NOT NULL,
`t_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
--Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select score.c_id,
course.c_name,
sum(CASE WHEN num BETWEEN85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
sum(CASE WHEN num BETWEEN70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
sum(CASE WHEN num BETWEEN60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
sum(CASE WHEN num< 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;
重点掌握的函数:
#1基本使用
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');-> 'Sunday October 2009'mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');-> '22:23:00'mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',-> '%D %y %a %d %m %b %j');-> '4th 00 Thu 04 10 Oct 277'mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',-> '%H %k %I %r %T %S %w');-> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');-> '1998 52'mysql> SELECT DATE_FORMAT('2006-06-00', '%d');-> '00'#2准备表和记录CREATE TABLEblog (
idINT PRIMARY KEYauto_increment,
NAMECHAR (32),
sub_timedatetime);INSERT INTOblog (NAME, sub_time)VALUES('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
#3. 提取sub_time字段的值,按照格式后的结果即"年月"来分组SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
#结果+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)
1、自定义函数
!!!注意!!!
#函数中不要写sql语句(否则会报错),函数仅仅只是一个功能,是一个在sql中被应用的功能
#若要想在begin...end...中写sql,请用存储过程
eg:自定义无参函数:
delimiter //
create functionf1(
i1int,
i2int)returns int
BEGIN
declare num int;set num = i1 +i2;return(num);END //delimiter ;
eg:自定义有参函数
delimiter//
create functionf5(
iint)returns int
begin
declare res int default 0;if i = 10 then
set res=100;
elseif i= 20 then
set res=200;
elseif i= 30 then
set res=300;else
set res=400;end if;returnres;end //delimiter ;
2、删除函数
drop function func_name;
3、执行函数
# 获取返回值select UPPER('egon') into @res;SELECT @res;
# 在查询中使用select f1(11,nid) ,name from tb2;
十、事物
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。
create table user(
idint primary keyauto_increment,
namechar(32),
balanceint);insert into user(name,balance)values('wsb',1000),
('egon',1000),
('ysb',1000);
#原子操作
starttransaction;update user set balance=900 where name='wsb'; #买支付100元update user set balance=1010 where name='egon'; #中介拿走10元update user set balance=1090 where name='ysb'; #卖家拿到90元commit;
#出现异常,回滚到初始状态
starttransaction;update user set balance=900 where name='wsb'; #买支付100元update user set balance=1010 where name='egon'; #中介拿走10元
uppdateuser set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到rollback;commit;
mysql> select * from user;+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | wsb | 1000 |
| 2 | egon | 1000 |
| 3 | ysb | 1000 |
+----+------+---------+
rows in set (0.00 sec)
十一、索引,流程控制,数据备份,python操作mysql,SQLAlchemy