mysql with python

前言：

数据库为人类解决了三大问题：持久化存储、优化读写、数据标准化。

MySQL它不是数据库，它是管理数据库的软件。MySQL管理了很多数据库。是典型的服务型数据库，需要TCP/IP去连接。

MySQL的包含关系：最外面是MySQL，里面有一些数据库，数据库中有一些表。

安装MySQL：

（一）.Linux篇

python web应用都是在Linux中跑的，数据库也是在Linux中的。所以一定要学会敲原生态的SQL代码。

Linux下已有高手老师打包好了MySQL，先用命令"ss -tnl"看下有没有3306端口，有的话，MySQL服务就是在正常运行。

如果没有，打命令："sudo service mysql start"来启动MySQL服务。

（二）.windows篇

服务都是跑在Linux下的，windows只是作为测试或者学习用。

MySQL的安装方案，去看这篇文章：http://www.cnblogs.com/20gg-com/p/6116258.html，此文写得很详细，照着做就可以了。学习用的话，完全够了！

另外，自己去安装个MySQL Workbench（可视化管理工具），不用再看着黑乎乎的命令行了。

注意建库时要选择好编码！还有字段也要指定编码！

一、MySQL基础命令

下面这些命令都是在Xshell中敲的。每条SQL语句的结尾都必须要有分号";"

MySQL对大小写不敏感，但是库名/表名/字段严格区分大小写，只是关键字命令不区分大小写。不过为了检查方便，建议把关键字大写了。

（一）.进入MySQL："mysql -uroot -p" 回车，然后输入密码。

（二）.退出："exit"

（三）.查看所有数据库："show databases;"。其中：这四个不要去动它，不懂的话，动坏了会出一些相当“灵异”的问题。

（四）.建库："create database 名字 charset utf8;" 一定要带上编码，不然中文问题有得你头疼了。注意：MySQL是"utf8"中间没有"-"

(1).可以使用"show create database 名字;"来查看数据库的信息。

（五）.切换数据库："use 数据库名字;" 切换成功会显示"Database changed"

（六）.建表："create table 表名 (字段1 数据类型 [约束] charset utf8 , 字段2 数据类型 [约束] charset utf8 , ...) charset utf8;"

(1).SQL语言是重语言，非常严谨。每个字段必须指定数据类型。

(2).MySQL中的数据类型：

整型：int/bool

实数：double/decimal

文本：char/varchar（char是大文本）

时间：date/datetime

(3).常用约束：

非空：not null

默认：default

唯一：unique

主键：primay key

外键：foreign key perferences

（七）.增

(1).全列插入：insert into 表名 values (值1,值2,...);

(2).插入一列：insert into 表名 (字段1,字段2,...) values (值1,值2,...);

(3).指定列插入：insert into 表名 values (值1,值2,...),(值1,值2,...),...;

（八）.删

一般来说，真正做开发的时候，宁可让数据废了，也不会去删它。你敢保证你一辈子都不会删错数据吗？删错准备好陪80个亿了...

delete from 表名 where 条件;

（九）.改

update 表名 set 字段名=新值 where 条件;

写的时候也注意点，忘记加条件了，就会把全表都更新了！

（十）.查

往下看...

二、MySQL高级查询

（一）.比较运算符

等于、不等于、大于、小于、is null、is not null

（二）.逻辑运算符

and、or、not 与python一样的逻辑。

（三）.范围查询

(1).连续范围：between 值 and 值

(2).间隔返回：in

例：where id in (1,3,5) 只会返回id为1,3,5的数据。

（四）.模糊查询：like

如果有多个模糊条件，可用or、and来组合。例：where 字段 like 值 or/and 字段 like 值

(1).百分号"%" 匹配多个字符

(2).下划线"_" 只匹配一个字符

（五）.排序：order by 字段（必须放在最后）

默认是asc升序，可以用desc来指定降序

（六）.常用的聚合函数

(1).统计个数：count(*)

(2).最大/小值：max/min(字段)

(3).求和：sum(字段)

(4).平均值：avg(字段)

（七）.分组查询

select 要分组的字段 from 表名 group by 前面那个要分组的字段;

在分组的情况下，不应出现其他列，因为会无法聚合。

（八）.聚合筛选

就是在group by后面多了个having语句。

select 要分组的字段 from 表名 group by 前面那个要分组的字段 having字段=值;

where与having的区别：where是对表中所有数据进行筛选，针对原始的数据集。having是对group by进行筛选，先聚合再筛选。

（九）.分页

MySQL的分页是限制查询的结果数量，不是上网时看到的那种分页哈！

(1).限制指定条：select * from 表名 limit count; 只会显示count条数据。（count需要一个int型正数）

(2).限制范围：select * from 表名 limit start,count; 从指定的start开始，显示count条。（下标从0开始）

三、关联查询

（一）.约束

首先，在创建表的时候就把约束定好，可以省去后续很多不必要的麻烦。

(1).主键约束：可以在设置字段的后面直接跟上关键字"primary key"，主键唯一且非空。

(2).唯一约束：关键字"unique"，这一列的值就不允许有重复的了。

(3).非空约束：关键字"not null"，这一列不能出现空值。

(4).默认值："defalut 值"，在没有给定该列值的情况下，使用默认值。

(5).自增长："auto_increment"，当有数据插入，每次自动加1，无需给值。要求用在主键上。（别乱给值，不然就失去了自增长的意义）

大神向我建议：把id作为主键、自增长。这样可以省去很多不必要的麻烦，大不了再多加些其他字段。其他列设为主键，后期如果遇到麻烦，是相当头疼的。

(6).外键：我有的，你一定有。你没有的，我绝对没有。

外键一般建在一对多，多的那张表上。

语法："foreign key (本表的字段) references 要关联的表(关联表的字段)"

（二）.子查询

例：select score from scores where stuid=(select id from students where name="张三");

（三）.连接查询

根据表与表之间的关系，多张表拼在一起。关键字："inner join"

四、练习

例1：

创建student表
CREATE TABLE student(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4),
birth YEAR,
department VARCHAR(20),
address VARCHAR(50)
);

创建score表
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL,
c_name VARCHAR(20),
grade INT(10)
);

INSERT INTO student VALUES
(901,'张老大', '男',1985,'计算机系', '北京市海淀区'),
(902,'张老二', '男',1986,'中文系', '北京市昌平区'),
(903,'张三', '女',1990,'中文系', '湖南省永州市'),
(904,'李四', '男',1990,'英语系', '辽宁省阜新市'),
(905,'王五', '女',1991,'英语系', '福建省厦门市'),
(906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

INSERT INTO score VALUES
(NULL,901, '计算机',98),
(NULL,901, '英语',80),
(NULL,902, '计算机',65),
(NULL,902, '中文',88),
(NULL,903, '中文',96),
(NULL,904, '计算机',70),
(NULL,904, '英语',92),
(NULL,905, '英语',94),
(NULL,906, '计算机',90),
(NULL,906, '英语',85);
            
1.查询student表的所有信息
SELECT * FROM student;

2.查询student表的第2条到第4条记录
SELECT * FROM student LIMIT 1,3;

3.从student表查询所有学生的学号(id),姓名(name),院系(department)的信息
SELECT id,name,department FROM student;

4.从student表中查询计算机和英语系的学生的信息
SELECT * FROM student WHERE department="计算机系" OR department="英语系";
SELECT * FROM student WHERE department in ("计算机系","英语系");

5.从student表中查询年龄18-22岁的学生信息
SELECT * FROM student WHERE (2018-birth) BETWEEN 18 AND 22;
SELECT id,name,sex,2013-birth AS age,department,address FROM student WHERE 2013-birth BETWEEN 18 AND 22;

6.从student表中查询每个院系有多少人
SELECT department as 院系,count(*) AS 人数 FROM student GROUP BY department;            

7.从score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

8.查询李四的考试科目(c_name)和考试成绩
SELECT c_name,grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name="李四");

9.用连接的方式查询所有学生的信息和考试信息
SELECT student.id,name,sex,birth,department,address,c_name,grade FROM student,score WHERE student.id=score.stu_id;

10.计算每个学生的总成绩
SELECT student.id,name,SUM(grade) FROM student,score WHERE student.id=score.stu_id GROUP BY id;

11.计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) FROM score GROUP BY c_name;

12.查询计算机成绩低于95的学生信息
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE ERE c_name="计算机" AND grade<95);

13.查询同时参加计算机和英语考试的学生信息
SELECT a.* FROM student a, score b, score c WHERE a.id=b.stu_id AND b.c_name="计算机" AND a.id=c.stu_id AND c.c_name="英语";

14.将计算机考试成绩按从高到低进行排序
SELECT stu_id,grade FROM score WHERE c_name="计算机" ORDER BY grade DESC;

15.从student表和score表中查询出学生的学号,然后合并查询结果
SELECT id FROM student UNION SELECT stu_id FROM score;

16.查询姓张或者姓王的同学的姓名,院系和考试科目及成绩
SELECT * FROM student,score WHERE (name LIKE "张%" OR name LIKE "王%") AND student.id=score.stu_id;

17.查询都是湖南的学生的姓名,年龄,院系和考试科目及成绩
SELECT * FROM student,score WHERE (address LIKE "湖南%") AND student.id=score.stu_id;            

数据库导入、导出：
导出：mysqldump -uroot -p 数据库名 > /home/.../name.sql  # ">"是重定向，后面接路径
导入：source /home/.../name.sql  # 后面接的就是备份的那个路径

Python3操作MySQL

一般来说，python web开发中，不会去写下面那些这么麻烦的方案。Django有ORM（映射框架），只需要传参数，它就会去执行SQL语句了。tornado也有第三方库可以引入使用。下面这些方案做个了解。

（一）.连接数据库

记得把编码也给指定了。不然链接的时候还是"latin-1"，你sql语句中有中文就悲剧了！

注意：sql语句中，values()括号内的占位符写法需要与表中的字段类型一一对应起来。（尽量使用.format()来格式化，这样比较看得清楚。不然字段一多，百分号还不看花眼了（— —！））

表中的字段类型是char，需要'%s'；字段是int，需要%d。

而且，char字段的占位符，必须引号引起来，'%s'；int可以不用引号。

(2).删。（一般来说，实际应用中，宁可让数据作废也不会去删。因为你有可能会删错，万一你删错了数据，你只能跑路了。。。）