python之路 mysql 博客园_Python之路--Python基础14--MySQL

一、数据库介绍

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值