MySql笔记

Anrue

2018-10-09

MySql

使用命令开启、连接

mysql.server start/stop
开启服务

mysql -u root -p
回车后输入密码，当前设置的密码为mysql

\q 退出 \c 清空 （语句后面加分号;）

远程连接

一般在公司开发中，可能会将数据库统一搭建在一台服务器上，所有开发人员共用一个数据库，而不是在自己的电脑中配置一个数据库
运行命令

1	mysql -hip地址 -uroot -p

-h后面写要连接的主机ip地址
-u后面写连接的用户名
-p回车后写密码

数据库、表、数据操作

数据库操作

查看所有数据库

1	show databases;

创建数据库

1	create database 数据库名 charset=utf8;

删除数据库

1	drop database 数据库名;

切换数据库

use 数据库名;

查看当前选择的数据库

1	select database();

表操作

查看当前数据库中所有表

1	show tables;

创建表
auto_increment表示自动增长

create table 表名(列及类型);
如：
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);

修改表

alter 是修改表结构，如对字段增加、删除、修改类型

1
2
3

alter table 表名 add|change|drop 列名 类型;
如：
alter table students add birthday datetime;

删除表

1	drop table 表名;

查看表结构

desc 表名;

更改表名称

1	rename table 原表名 to 新表名;

查看表的创建语句

1	show create table '表名';

数据操作

查询

1	select * from 表名

增加

全列插入：insert into 表名 values(...);
缺省插入：insert into 表名(列1,...) values(值1,...);
同时插入多条数据：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;
主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

修改

update用来修改表中的数据，修改某一行某一列的值

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

删除

1	delete from 表名 where 条件

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

1
2
3

alter table students add isdelete bit default 0;
如果需要删除则
update students isdelete=1 where ...;

备份与恢复

数据备份

运行mysqldump命令

1 2	mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql; 按提示输入mysql的密码

数据恢复

其实就是创建表，插入表的一个过程

连接mysql，创建数据库

退出连接，执行如下命令

mysql -u root –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码

查询

简介

查询的基本语法

1	select * from 表名;

from关键字后面写表名，表示数据来源于是这张表
select后面写表中的列名，如果是*表示在结果中显示表中所有列
在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中
如果要查询多个列，之间使用逗号分隔
消除重复行
在select后面列前使用distinct可以消除重复的行

1	select distinct gender from students;

条件

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

1	select * from 表名 where 条件;

比较运算符

等于=
大于>
大于等于>=
小于<
小于等于<=
不等于!=或<>

查询编号大于3的学生

1	select * from students where id>3;

查询编号不大于4的科目

1	select * from subjects where id<=4;

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

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

查询没被删除的学生

1	select * from students where isdelete=0;

逻辑运算符

1
2
3

and
or
not

查询编号大于3的女同学

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

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

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

模糊查询
like
%表示任意多个任意字符
_表示一个任意字符
查询姓黄的学生

* from students where sname like '黄%';

1
2
3

```

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

select * from students where sname like ‘黄_’;


* 查询姓黄或叫靖的学生

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

范围查询

in表示在一个非连续的范围内
查询编号是1或3或8的学生

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

between … and …表示在一个连续的范围内
查询学生是3至8的学生

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

查询学生是3至8的男生

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

空判断

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

查询没有填写地址的学生

1	select * from students where hometown is null;

判非空is not null

查询填写了地址的学生

1	select * from students where hometown is not null;

查询填写了地址的女生

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

优先级

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

聚合

为了快速得到统计数据，提供了5个聚合函数

count(*)表示计算总行数，括号中写星与列名，结果是相同的

1 2	查询学生总数 select count(*) from students;

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

1 2	查询女生的编号最大值 select max(id) from students where gender=0;

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

1 2	查询未删除的学生最小编号 select min(id) from students where isdelete=0;

sum(列)表示求此列的和

1 2	查询男生的编号之后 select sum(id) from students where gender=1;

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

1 2	查询未删除女生的编号平均值 select avg(id) from students where isdelete=0 and gender=0;

分组

按照字段分组，表示此字段相同的数据会被放到一个组中
分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计，做聚合运算

1 2	语法： 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;

分组后的数据筛选

语法：
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;
对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选

排序

为了方便查看数据，可以对数据进行排序

1
2
3

语法：
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;

获取部分行(分页)

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

语法
select * from 表名
limit start,count
从start开始，获取count条数据
start索引从0开始

示例：分页

已知：每页显示m条数据，当前显示第n页
求总页数：此段逻辑后面会在python中实现
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
求第n页的数据

1
2
3

select * from students
where isdelete=0
limit (n-1)*m,m

完整的select语句

distinct *

from 表名
where ....
group by ... having ...
order by ...
limit star,count

执行顺序为：

表名

where ....
group by ...
select distinct *
having ...
order by ...
limit star,count

ps ：实际使用中，只是语句中某些部分的组合，而不是全部

高级

实体与实体之间有3种对应关系，这些关系也需要存储下来
在开发中需要对存储的数据进行一些处理，用到内置的一些函数
视图用于完成查询语句的封装
事务可以保证复杂的增删改操作有效

关系

创建成绩表scores，结构如下
- id
- 学生
- 科目
- 成绩
思考：学生列应该存什么信息呢？
答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息
同理，科目表也是关系列，引用科目表中的数据
关系
创建表的语句如下

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);

外键

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

1 2	为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)
);

外键的级联操作

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

语法

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

级联操作的类型包括：
- restrict（限制）：默认值，抛异常
- cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
- set null：将外键设置为空
- no action：什么都不做

连接

先看个问题

问：查询每个学生每个科目的分数
分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示在同一个结果集中呢？
答：当查询结果来源于多张表时，需要使用连接查询
关键：找到表间的关系，当前的关系是
- students表的id—scores表的stuid
- subjects表的id—scores表的subid
则上面问题的答案是：

select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

结论：当需要对有关系的多张表进行查询时，需要使用连接join

连接查询

连接查询分类如下：
- 表A inner join 表B：表A与表B匹配的行会出现在结果中
- 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
- 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
在查询或条件中推荐使用“表名.列名”的语法
如果多个表中列名不重复可以省略“表名.”部分
如果表的名称太长，可以在表名后面使用’ as 简写名’或’ 简写名’，为表起个临时的简写名称

练习

查询学生的姓名、平均分

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;

自关联

子查询

查询支持嵌套使用
查询各学生的语文、数学、英语的成绩

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;

视图

对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
解决：定义视图
视图本质就是对查询的一个封装
定义视图

1
2
3

create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

视图的用途就是查询

1	select * from stuscore;

事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
使用事务可以完成退回的功能，保证业务逻辑的正确性
事务四大特性(简称ACID)
- 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
- 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
- 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
- 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务

查看表的创建语句

1	show create table students;

修改表的类型

1	alter table '表名' engine=innodb;

事务语句
- 开启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;
------------------------
终端1：
select * from students;

示例2

步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表
终端1：
select * from students;
------------------------
终端2：
begin;
insert into students(sname) values('张飞');
步骤2
终端1：
select * from students;
步骤3
终端2：
rollback;
------------------------
终端1：
select * from students;

于python交互

交互类型

在文件中引入模块

1	import Mysqldb

Connection对象

用于建立与数据库的连接

创建对象：调用connect()方法
conn=connect(参数列表)
- 参数host：连接的mysql主机，如果本机是’localhost’
- 参数port：连接的mysql主机的端口，默认是3306
- 参数db：数据库的名称
- 参数user：连接的用户名
- 参数password：连接的密码
- 参数charset：通信采用的编码方式，默认是’gb2312’，要求与数据库创建时指定的编码一致，否则中文会乱码
对象的方法
- close()关闭连接
- commit()事务，所以需要提交才会生效
- rollback()事务，放弃之前的操作
- cursor()返回Cursor对象，用于执行sql语句并获得结果
  Cursor对象
  执行sql语句
创建对象：调用Connection对象的cursor()方法

1	cursor1=conn.cursor()

对象的方法
- close()关闭
- execute(operation [, parameters ])执行语句，返回受影响的行数
- fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
- next()执行查询语句时，获取当前行的下一行
- fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
- scroll(value[,mode])将行指针移动到某个位置
- mode表示移动的方式
- mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动
- mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0
- 对象的属性
- rowcount只读属性，表示最近一次execute()执行后受影响的行数
- connection获得当前连接对象
  增加
  创建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

修改

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

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

删除

创建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
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

其它语句

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

封装

import pymysql


class MySqlhelper:

    def __init__(self, host, db, user, pwd, port=3306, charset="utf8"):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = pwd
        self.charset = charset

    def connect(self):
        self.conn = pymysql.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()

    # private api
    def __edit(self, sql, param):
        count = 0
        try:
            self.connect()
            self.cursor.execute(sql, param)
            self.conn.commit()
            self.close()
        except Exception as result:
            print(result)
        else:
            return count

    # 插入
    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 get_one(self, sql):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        else:
            return result

    def get_all(self, sql):
        result = []
        try:
            self.connect()
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        else:
            return result

自定义转场动画

常见图片格式