mysql udf python_python mysql

#### python中使用mysql

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AE%89%E8%A3%85mysql%E6%A8%A1%E5%9D%97)安装mysql模块

~~~

sudo apt-get install python-mysql

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%9C%A8%E6%96%87%E4%BB%B6%E4%B8%AD%E5%BC%95%E5%85%A5%E6%A8%A1%E5%9D%97)在文件中引入模块

~~~

import Mysqldb

from Mysqldb import *

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#connection%E5%AF%B9%E8%B1%A1)Connection对象

用于建立与数据库的连接 创建对象：调用connect()方法

~~~

conn=connect(参数列表)

~~~

* 参数host：连接的mysql主机，如果本机是'localhost'

* 参数port：连接的mysql主机的端口，默认是3306

* 参数db：数据库的名称

* 参数user：连接的用户名

* 参数password：连接的密码

* 参数charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码 对象的方法

* close()关闭连接

* commit()事务，所以需要提交才会生效

* rollback()事务，放弃之前的操作

* cursor()返回Cursor对象，用于执行sql语句并获得结果

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#cursor%E5%AF%B9%E8%B1%A1)Cursor对象

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%89%A7%E8%A1%8Csql%E8%AF%AD%E5%8F%A5-%E5%88%9B%E5%BB%BA%E5%AF%B9%E8%B1%A1%E8%B0%83%E7%94%A8connection%E5%AF%B9%E8%B1%A1%E7%9A%84cursor%E6%96%B9%E6%B3%95)执行sql语句 创建对象：调用Connection对象的cursor()方法

~~~

cursor1=conn.cursor()

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AF%B9%E8%B1%A1%E7%9A%84%E6%96%B9%E6%B3%95)对象的方法

* close()关闭

* execute(operation \[, parameters \])执行语句，返回受影响的行数

* fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组

* next()执行查询语句时，获取当前行的下一行

* fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

* scroll(value\[,mode\])将行指针移动到某个位置

1. mode表示移动的方式

2. mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动

3. mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AF%B9%E8%B1%A1%E7%9A%84%E5%B1%9E%E6%80%A7)对象的属性

* rowcount只读属性，表示最近一次execute()执行后受影响的行数

* connection获得当前连接对象

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%A2%9E%E5%8A%A0)增加

创建testInsert.py文件，向学生表中插入一条数据

~~~

#encoding=utf-8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cs1=conn.cursor()

count=cs1.execute("insert into students(sname) values('张良')")

print count

conn.commit()

cs1.close()

conn.close()

except Exception,e:

print e.message

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BF%AE%E6%94%B9)修改

创建testUpdate.py文件，修改学生表的一条数据

~~~

#encoding=utf-8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cs1=conn.cursor()

count=cs1.execute("update students set sname='刘邦' where id=6")

print count

conn.commit()

cs1.close()

conn.close()

except Exception,e:

print e.message

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%88%A0%E9%99%A4)删除

创建testDelete.py文件，删除学生表的一条数据

~~~

#encoding=utf-8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cs1=conn.cursor()

count=cs1.execute("delete from students where id=6")

print count

conn.commit()

cs1.close()

conn.close()

except Exception,e:

print e.message

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#sql%E8%AF%AD%E5%8F%A5%E5%8F%82%E6%95%B0%E5%8C%96)sql语句参数化

创建testInsertParam.py文件，向学生表中插入一条数据

~~~

#encoding=utf-8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cs1=conn.cursor()

sname=raw_input("请输入学生姓名：")

params=[sname]

count=cs1.execute('insert into students(sname) values(%s)',params)

print count

conn.commit()

cs1.close()

conn.close()

except Exception,e:

print e.message

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%85%B6%E5%AE%83%E8%AF%AD%E5%8F%A5)其它语句

cursor对象的execute()方法，也可以用于执行create table等语句 建议在开发之初，就创建好数据库表结构，不要在这里执行

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%9F%A5%E8%AF%A2%E4%B8%80%E8%A1%8C%E6%95%B0%E6%8D%AE)查询一行数据

创建testSelectOne.py文件，查询一条学生信息

~~~

#encoding=utf8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cur=conn.cursor()

cur.execute('select * from students where id=7')

result=cur.fetchone()

print result

cur.close()

conn.close()

except Exception,e:

print e.message

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%9F%A5%E8%AF%A2%E5%A4%9A%E8%A1%8C%E6%95%B0%E6%8D%AE)查询多行数据

创建testSelectMany.py文件，查询一条学生信息

~~~

#encoding=utf8

import MySQLdb

try:

conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')

cur=conn.cursor()

cur.execute('select * from students')

result=cur.fetchall()

print result

cur.close()

conn.close()

except Exception,e:

print e.message

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%B0%81%E8%A3%85)封装

创建MysqlHelper.py文件，定义类

~~~

#encoding=utf8

import MySQLdb

class MysqlHelper():

def __init__(self,host,port,db,user,passwd,charset='utf8'):

self.host=host

self.port=port

self.db=db

self.user=user

self.passwd=passwd

self.charset=charset

def connect(self):

self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)

self.cursor=self.conn.cursor()

def close(self):

self.cursor.close()

self.conn.close()

def get_one(self,sql,params=()):

result=None

try:

self.connect()

self.cursor.execute(sql, params)

result = self.cursor.fetchone()

self.close()

except Exception, e:

print e.message

return result

def get_all(self,sql,params=()):

list=()

try:

self.connect()

self.cursor.execute(sql,params)

list=self.cursor.fetchall()

self.close()

except Exception,e:

print e.message

return list

def insert(self,sql,params=()):

return self.__edit(sql,params)

def update(self, sql, params=()):

return self.__edit(sql, params)

def delete(self, sql, params=()):

return self.__edit(sql, params)

def __edit(self,sql,params):

count=0

try:

self.connect()

count=self.cursor.execute(sql,params)

self.conn.commit()

self.close()

except Exception,e:

print e.message

return count

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%B7%BB%E5%8A%A0)添加

创建testInsertWrap.py文件，使用封装好的帮助类完成插入操作

~~~

#encoding=utf8

from MysqlHelper import *

sql='insert into students(sname,gender) values(%s,%s)'

sname=raw_input("请输入用户名：")

gender=raw_input("请输入性别，1为男，0为女")

params=[sname,bool(gender)]

mysqlHelper=MysqlHelper('localhost',3306,'test1','root','mysql')

count=mysqlHelper.insert(sql,params)

if count==1:

print 'ok'

else:

print 'error'

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%9F%A5%E8%AF%A2%E4%B8%80%E4%B8%AA)查询一个

创建testGetOneWrap.py文件，使用封装好的帮助类完成查询最新一行数据操作

~~~

#encoding=utf8

from MysqlHelper import *

sql='select sname,gender from students order by id desc'

helper=MysqlHelper('localhost',3306,'test1','root','mysql')

one=helper.get_one(sql)

print one

~~~

* * *

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#mysql%E5%AE%89%E8%A3%85%E7%AE%A1%E7%90%86%E5%8F%8A%E5%AE%9E%E4%BE%8B%E8%AE%B2%E8%A7%A3)mysql安装管理及实例讲解

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AE%89%E8%A3%85)安装

~~~

sudo apt-get install mysql-server mysql-client

然后按照提示输入

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E7%AE%A1%E7%90%86%E6%9C%8D%E5%8A%A1)管理服务

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#1%E5%90%AF%E5%8A%A8)1.启动

~~~

service mysql start

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#2%E5%81%9C%E6%AD%A2)2.停止

~~~

service mysql stop

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#3%E9%87%8D%E5%90%AF)3.重启

~~~

service mysql restart

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#4%E5%85%81%E8%AE%B8%E8%BF%9C%E7%A8%8B%E8%BF%9E%E6%8E%A5)4.允许远程连接

* 找到mysql配置文件修改

~~~

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf将bind-address=127.0.0.1注释

~~~

* 登录mysql,运行命令

~~~

grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;

flush privileges;

~~~

* 重启mysql

~~~

service mysql restart

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#mysql%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B)mysql中的数据类型

* 在mysql中包含的数据类型很多，这里主要列出来常用的几种

* 数字：int,decimal

* 字符串：varchar,text,char

* 日期：datetime

* 布尔：bit

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BD%BF%E7%94%A8%E5%91%BD%E4%BB%A4%E8%BF%9E%E6%8E%A5)使用命令连接

~~~

mysql -uroot -p

回车后输入密码，当前设置的密码为mysql

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C)数据库操作

1. 创建数据库

~~~

crate database 数据库名 charset=utf8;

~~~

2. 删除数据库

~~~

drop database 数据库名；

~~~

3. 切换数据库

~~~

use 数据库名；

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%A1%A8%E6%93%8D%E4%BD%9C)表操作

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

~~~

show tables;

~~~

2. 创建表 auto\_increment 表示自动增长

~~~

create table 表名(列及类型);

如：

create table students(

id int auto_increment primary key,

sname varchar(10) not null

);

~~~

3. 修改表

~~~

alter table 表名 add|change|drop 列名 类型;

如：

alter table students add birthday datetime;

~~~

4. 删除表

~~~

drop table 表名；

~~~

5. 查看表结构

~~~

desc 表名；

~~~

6. 更改表名称

~~~

rename table 原表名 to 新表名

~~~

7. 查看表的创建语句

~~~

show create table '表名'；

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%95%B0%E6%8D%AE%E6%93%8D%E4%BD%9C)数据操作

* 查询

~~~

select * from 表名

~~~

* 增加

~~~

全列插入：insert into 表名 values(...)

缺省插入：insert into 表名(列1,...) values(值1,...)

同时插入多条数据：insert into 表名 values(...),(...)...;

或insert into 表名(列1,...) values(值1,...),(值1,...)...;

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

~~~

* 修改

~~~

update 表名 set 列1=值1,... where 条件

~~~

* 删除

~~~

delete from 表名 where 条件

~~~

* 逻辑删除，本质就是修改操作update

~~~

alter table students add isdelete bit default 0;

如果需要删除则

update students isdelete=1 where ...;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D)备份与恢复

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%95%B0%E6%8D%AE%E5%A4%87%E4%BB%BD)数据备份

~~~

进入超级管理员

sudo -s

进入mysql库目录

cd /var/lib/mysql

运行mysqldump命令

mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;

按提示输入mysql的密码

~~~

##### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%95%B0%E6%8D%AE%E6%81%A2%E5%A4%8D)数据恢复

连接mysql，创建数据库

退出连接，执行如下命令

~~~

mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql

根据提示输入mysql密码

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%9F%A5%E8%AF%A2%E4%B9%8B%E6%9D%A1%E4%BB%B6)查询之条件

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%9D%A1%E4%BB%B6)条件

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中 语法如下：

~~~

select * from 表名 where 条件;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%AF%94%E8%BE%83%E8%BF%90%E7%AE%97%E7%AC%A6)比较运算符

* 等于 =

* 大于 >

* 大于等于 >=

* 小于 <

* 小于等于 <=

* 不等于 !=或<>

查询编号大于3的学生

~~~

select * from students where id>3;

~~~

查询编号不大于4的科目

~~~

select * from subjects where id<=4;

~~~

查询姓名不是“黄蓉”的学生

~~~

select * from students where sname!='黄蓉';

~~~

查询没被删除的学生

~~~

select * from students where isdelete=0;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E9%80%BB%E8%BE%91%E8%BF%90%E7%AE%97%E7%AC%A6)逻辑运算符

* and

* or

* not

查询编号大于3的女同学

~~~

select * from students where id>3 and gender=0;

~~~

查询编号小于4或没被删除的学生

~~~

select * from students where id<4 or isdelete=0;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2)模糊查询

* like %表示任意多个任意字符 \_表示一个任意字符

查询姓黄的学生

~~~

select * from students where sname like '黄%';

~~~

查询姓黄并且名字是一个字的学生

~~~

select * from students where sname like '黄_';

~~~

查询姓黄或叫靖的学生

~~~

select * from students where sname like '黄%' or sname like '%靖%';

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%8C%83%E5%9B%B4%E6%9F%A5%E8%AF%A2)范围查询

* in表示在一个非连续的范围内

查询编号是1或3或8的学生

~~~

select * from students where id in(1,3,8);

between ... and ...表示在一个连续的范围内

~~~

查询学生是3至8的学生

~~~

select * from students where id between 3 and 8;

~~~

查询学生是3至8的男生

~~~

select * from students where id between 3 and 8 and gender=1;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E7%A9%BA%E5%88%A4%E6%96%AD)空判断

注意：null与''是不同的 判空is null

查询没有填写地址的学生

~~~

select * from students where hometown is null;

~~~

判非空is not null

查询填写了地址的学生

~~~

select * from students where hometown is not null;

~~~

查询填写了地址的女生

~~~

select * from students where hometown is not null and gender=0;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BC%98%E5%85%88%E7%BA%A7)优先级

小括号，not，比较运算符，逻辑运算符 and比or先运算，如果同时出现并希望先算or，需要结合()使用

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%81%9A%E5%90%88)聚合

1. 为了快速得到统计数据，提供了5个聚合函数 count(\*)表示计算总行数，括号中写星与列名，结果是相同的

查询学生总数

~~~

select count(*) from students;

~~~

2. max(列)表示求此列的最大值

查询女生的编号最大值

~~~

select max(id) from students where gender=0;

~~~

4.min(列)表示求此列的最小值

查询未删除的学生最小编号

~~~

select min(id) from students where isdelete=0;

~~~

5. sum(列)表示求此列的和

查询男生的编号之后

~~~

select sum(id) from students where gender=1;

~~~

6. avg(列)表示求此列的平均值

查询未删除女生的编号平均值

~~~

select avg(id) from students where isdelete=0 and gender=0;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%88%86%E7%BB%84)分组

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%AF%AD%E6%B3%95)语法：

~~~

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

~~~

查询男女生总数

~~~

select gender as 性别,count(*)

from students

group by gender;

~~~

查询各城市人数

~~~

select hometown as 家乡,count(*)

from students

group by hometown;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%88%86%E7%BB%84%E5%90%8E%E7%9A%84%E6%95%B0%E6%8D%AE%E7%AD%9B%E9%80%89)分组后的数据筛选

语法：

~~~

select 列1,列2,聚合... from 表名

group by 列1,列2,列3...

having 列1,...聚合...

having后面的条件运算符与where的相同

~~~

查询男生总人数

* 方案一

~~~

select count(*)

from students

where gender=1;

~~~

* * *

* 方案二：

~~~

select gender as 性别,count(*)

from students

group by gender

having gender=1;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AF%B9%E6%AF%94where%E4%B8%8Ehaving)对比where与having

* where是对from后面指定的表进行数据筛选，属于对原始数据的筛选

* having是对group by的结果进行筛选

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%8E%92%E5%BA%8F)排序

语法：

~~~

select * from 表名

order by 列1 asc|desc,列2 asc|desc,...

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推

默认按照列值从小到大排列

asc从小到大排列，即升序

desc从大到小排序，即降序

~~~

查询未删除男生学生信息，按学号降序

~~~

select * from students

where gender=1 and isdelete=0

order by id desc;

~~~

查询未删除科目信息，按名称升序

~~~

select * from subject

where isdelete=0

order by stitle;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%88%86%E9%A1%B5)分页

语法

~~~

select * from 表名

limit start,count

从start开始，获取count条数据

start索引从0开始

~~~

示例：

~~~

已知：每页显示m条数据，当前显示第n页

求总页数：此段逻辑后面会在python中实现

查询总条数p1

使用p1除以m得到p2

如果整除则p2为总数页

如果不整除则p2+1为总页数

求第n页的数据

select * from students

where isdelete=0

limit (n-1)*m,m

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%A4%96%E9%94%AE)外键

思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？ 答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证

为stuid添加外键约束

~~~

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

~~~

此时插入或者修改数据时，如果stuid的值在students表中不存在则会报错

在创建表时可以直接创建约束

~~~

create table scores(

id int primary key auto_increment,

stuid int,

subid int,

score decimal(5,2),

foreign key(stuid) references students(id),

foreign key(subid) references subjects(id)

);

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%A4%96%E9%94%AE%E7%9A%84%E7%BA%A7%E8%81%94%E6%93%8D%E4%BD%9C)外键的级联操作

在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常 推荐使用逻辑删除，还可以解决这个问题 可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作

语法

~~~

alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E7%BA%A7%E8%81%94%E6%93%8D%E4%BD%9C%E7%9A%84%E7%B1%BB%E5%9E%8B%E5%8C%85%E6%8B%AC)级联操作的类型包括：

* restrict(限制)：默认值，抛异常

* cascade(级联)：如果主表的记录删掉，则从表中相关联的记录都将被删除

* set null：将外键设置为空

* no action：什么都不做

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2)连接查询

连接查询分类如下：

* 表A inner join 表B：表A与表B匹配的行会出现在结果中

* 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充

* 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充

查询学生的姓名、平均分

~~~

select students.sname,avg(scores.score)

from scores

inner join students on scores.stuid=students.id

group by students.sname;

~~~

查询男生的姓名、总分

~~~

select students.sname,avg(scores.score)

from scores

inner join students on scores.stuid=students.id

where students.gender=1

group by students.sname;

~~~

查询科目的名称、平均分

~~~

select subjects.stitle,avg(scores.score)

from scores

inner join subjects on scores.subid=subjects.id

group by subjects.stitle;

~~~

查询未删除科目的名称、最高分、平均分

~~~

select subjects.stitle,avg(scores.score),max(scores.score)

from scores

inner join subjects on scores.subid=subjects.id

where subjects.isdelete=0

group by subjects.stitle;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AD%90%E6%9F%A5%E8%AF%A2)子查询

查询各学生的语文、数学、英语的成绩

~~~

select sname,

(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='语文' and stuid=stu.id) as 语文,

(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='数学' and stuid=stu.id) as 数学,

(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英语' and stuid=stu.id) as 英语

from students stu;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#mysql%E4%B8%AD%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0)mysql中字符串函数

查看字符的ascii码值ascii(str)，str是空串时返回0

~~~

select ascii('a');

~~~

查看ascii码值对应的字符char(数字)

~~~

select char(97);

~~~

拼接字符串concat(str1,str2...)

~~~

select concat(12,34,'ab');

~~~

包含字符个数length(str)

~~~

select length('abc');

~~~

截取字符串

1. left(str,len)返回字符串str的左端len个字符

2. right(str,len)返回字符串str的右端len个字符

3. substring(str,pos,len)返回字符串str的位置pos起len个字符

~~~

select substring('abc123',2,3);

~~~

去除空格

* ltrim(str)返回删除了左空格的字符串str

* rtrim(str)返回删除了右空格的字符串str

* trim(\[方向 remstr from str)返回从某侧删除remstr后的字符串str，方向词包括both、leading、trailing，表示两侧、左、右

~~~

select trim(' bar ');

select trim(leading 'x' FROM 'xxxbarxxx');

select trim(both 'x' FROM 'xxxbarxxx');

select trim(trailing 'x' FROM 'xxxbarxxx');

~~~

返回由n个空格字符组成的一个字符串space(n)

~~~

select space(10);

~~~

替换字符串replace(str,from\_str,to\_str)

~~~

select replace('abc123','123','def');

~~~

大小写转换，函数如下

~~~

lower(str)

upper(str)

select lower('aBcD');

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%95%B0%E5%AD%A6%E5%87%BD%E6%95%B0)数学函数

求绝对值abs(n)

~~~

select abs(-32);

~~~

求m除以n的余数mod(m,n)，同运算符%

~~~

select mod(10,3);

select 10%3;

~~~

地板floor(n)，表示不大于n的最大整数

~~~

select floor(2.3);

~~~

天花板ceiling(n)，表示不小于n的最大整数

~~~

select ceiling(2.3);

~~~

求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0

~~~

select round(1.6);

~~~

求x的y次幂pow(x,y)

~~~

select pow(2,3);

~~~

获取圆周率PI()

~~~

select PI();

~~~

随机数rand()，值为0-1.0的浮点数

~~~

select rand();

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E6%97%A5%E6%9C%9F%E6%97%B6%E9%97%B4%E5%87%BD%E6%95%B0)日期时间函数

获取子值，语法如下

~~~

year(date)返回date的年份(范围在1000到9999)

month(date)返回date中的月份数值

day(date)返回date中的日期数值

hour(time)返回time的小时数(范围是0到23)

minute(time)返回time的分钟数(范围是0到59)

second(time)返回time的秒数(范围是0到59)

select year('2016-12-21');

日期计算，使用+-运算符，数字后面的关键字为year、month、day、hour、minute、second

select '2016-12-21'+interval 1 day;

日期格式化date_format(date,format)，format参数可用的值如下

获取年%Y，返回4位的整数

*　获取年%y，返回2位的整数

*　获取月%m，值为1-12的整数

获取日%d，返回整数

*　获取时%H，值为0-23的整数

*　获取时%h，值为1-12的整数

*　获取分%i，值为0-59的整数

*　获取秒%s，值为0-59的整数

~~~

日期格式化date\_format(date,format)

~~~

select date_format('2016-12-21','%Y %m %d');

~~~

当前日期current\_date()

~~~

select current_date();

~~~

当前时间current\_time()

~~~

select current_time();

~~~

当前日期时间now()

~~~

select now();

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%A7%86%E5%9B%BE)视图

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E5%AE%9A%E4%B9%89%E8%A7%86%E5%9B%BE)定义视图

~~~

create view stuscore as

select students.*,scores.score from scores

inner join students on scores.stuid=students.id;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E8%A7%86%E5%9B%BE%E7%9A%84%E7%94%A8%E9%80%94%E5%B0%B1%E6%98%AF%E6%9F%A5%E8%AF%A2)视图的用途就是查询

~~~

select * from stuscore;

~~~

#### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BA%8B%E5%8A%A1)事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回 使用事务可以完成退回的功能，保证业务逻辑的正确性

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BA%8B%E5%8A%A1%E5%9B%9B%E5%A4%A7%E7%89%B9%E6%80%A7%E7%AE%80%E7%A7%B0acid)事务四大特性(简称ACID)

* 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行

* 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致

* 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的

* 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务

查看表的创建语句

~~~

show create table students;

~~~

修改表的类型

~~~

alter table '表名' engine=innodb;

~~~

###### [](https://note.youdao.com/md/?defaultMode=view&fileId=WEB37964e4ede0a01eb3e8a99992f51f950#%E4%BA%8B%E5%8A%A1%E8%AF%AD%E5%8F%A5)事务语句

* 开启begin;

* 提交commit;

* 回滚rollback; 示例1

步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表

* * *

终端1： select \* from students;

终端2：begin;

insert into students(sname) values('张飞');

* * *

步骤2

终端1：select \* from students;

* * *

步骤3

终端2： commit;