操作MySQL数据库

前言

• SQL语句分为：
  ·DQL:数据查询语言，用于数据进行查询，如select
  ·DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
  sql语句不区分大小写

• 特点：
  ·支持多种操作系统，有Linux、Windows、AIX、FreeBSD、HP-UX、MacOS等

一、数据类型，约束

建立数据库中的表

• 1、连接

• 2、创建数据库

• 

• 3、创建数据表（考虑类型、约束）

• 

• 注意：钥匙为主键，唯一能标识表中的唯一记录

1、 常用数据类型

• 整形：int,bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间：date,time,datetime
• 枚举类型（enum)

说明
（1）decimal标识浮点数，如decimal（5，2）表示存5位，小数两位。

2、约束

• 主键primary key:物理上存储的顺序
• 非空not null:此字段不允许填写空位
• 唯一unique:此字段不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign 可以：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值时，会到关联的表中查询辞职是狗存在，如果存在则填写成功，如果不存在则填写失败。
  

二、数据库操作

1、命令行连接

• 链接数据库：mysql -uroot -p
• 显示数据库版本select version();
• 退出数据库：exit/quit/ctrl +d
• 查看数据库：show databases;
• 显示时间select now();
• 创建数据库create database 数据库名 charset=utf8;
• 查看创建数据库的语句show crate database 数据库名
• 查看当前使用数据库select database(); ``
• 使用数据库use 数据库的名字
• 删除数据库drop database ‘数据库名’
• 注释 --某某某

2、数据表操作

• desc xxxx 查看表的结构

• 创建表

create table students(
	id int unsigned not null auto_increment primary key,
	name varchar(30),
	age tinyint unsigned default 0,
	high decimal(5,2),
	gender enum("男","女","中性","保密") default "保密",
	cls_id int unsigned
);

• 插入数据
  insert into students values(0,"老吴",24,165.23,"女",2);
• 查看表中数据
  select * from students;

2.1修改表

• 查看表的创建语句：show create table 表名；
• 查看表：select * from 表名
• 查看表结构：desc XXXX;
• 添加字段：alter table students add birthday datetime;
• 修改字段（不重命名版 修改类型）：alter table students modify birthday date;
• 修改字段（字段名和类型都改）：alter table students change birthday birth date default "2000-01-01"
• 删除字段:alter table students drop high;

2.2删除表

物理删除

• 删除数据库：drop database 数据库；
• 删除数据表：drop table 表名；
• 整个表数据全部删除：delete from students;
  -按条件删除 delete from students where name="老吴";
  逻辑删除
• 用一个字段来表示这条信息是否已经不能再使用了
  -给students表添加一个is_delete字段 bit类型： alter table students add is_delete bit default 0;
  -update table students set is_delete=1 where id=2;

3、增删改查（curd）

curd:代表创建（create）、更新（update）、读取（retrieve）、删除（delete）

3.1查询基本使用

• 查询所有列：select * from 表名；
• 查询name为安琪拉的所以信息：select * from students where name=”安琪拉“；
• 查询指定列：select name,gender from students; select * from students where id < 4;
• 可以使用as为列或表指定别名：select 字段[as 别名] from 数据表 where ....:select name as 姓名，gender as 性别 from students;
• 插入

3.2增加基本使用

• 全部插入：insert into 表名 values(...);
• 向students表中插入一个班级insert into students values(2,"老朱",25,"女",3,"1997-03-23");
• 部分插入： insert into students(name,gender) values ("安琪拉","女");
• 多行插入：insert into students(name,gender) values ("安琪拉","女"),("鲁班","男");

3.1更新基本使用

• update 表名 set 列1，列1=值1，列2=值2…where 条件；
• 整个数据表的这一字段全改update students set gender=1
  -按条件修改
  1、update students set gender=1 where name="安琪拉"；
  2、update students set gender=1 where id="1";
  3、update students set age=22,gender=2 where id=3;

三、数据库准备

1、数据库查询

数据准备、基本的查询

• 创建一个数据库：create database python_test;
• 使用一个数据库:use python_test;
• 显示当前使用的数据库是哪个：select database();
• 创建一个数据表：

create table students(
	id int unsigned primary key auto_increment not null,
	name varchar(20) default'',
	age tinyint unsigned default 0,
	high decimal(5,2),
	gender enum("男","女","中性","保密") default "保密",
	cls_id int unsigned default 0,
	is_delete bit default 0
);
create table classes(
	id int unsigned auto_increment primary key not null,
	name varchar(20) not null
);

准备数据（向students、classes表中插入数据）

insert into students values
(0,'小明',18,167.34,2,1,0),
(0,'小月月',24,187.34,2,3,1),
(0,'彭于晏',29,187.54,3,3,0),
(0,'刘德华',18,177.34,4,2,0),
(0,'黄蓉',18,183.37,2,4,1),
(0,'凤姐',19,178.54,3,2,1),
(0,'王祖贤',28,167.34,3,3,0),
(0,'周杰伦',23,189.34,2,1,0),
(0,'程坤',25,173.34,4,1,1),
(0,'刘亦菲',19,179.52,2,3,0),
(0,'金星',22,188.38,2,3,0),
(0,'静香',27,182.34,4,2,1),
(0,'郭靖',38,169.34,1,2,0),
(0,'周杰',21,179.34,2,1,1);

 insert into classes values(0,"python_01"),(0,"python_02");

多表查询

• select 表名.字段 from 表名；例：select students.name students.age from students;
• 可以通过as给表起别名；例：select s.name,s.age from students as s;
• 消除重复行：distinct字段：select distinct gender from students;

条件查询

• 比较运算符（select … from 表名 where …）
• 查询大于20岁的信息select * from students where age > 20;
• 查询小于20岁的信息select id,name,gender from students where age < 20;
• 查询大于等于20岁的信息select * from students where age >= 20;
• 查询小于等于20岁的信息select * from students where age <= 20;

逻辑运算符

• and:(查询20到30直接的所有信息)：select * from students where age>20 and age<30;
• and:(查询20岁以上女性的信息)：select * from students where age>20 and gender="女"；
• or:(20以上或者身高超过180)：select * from students where age>20 or high>180;
• not:(不在20以上的女性这个范围)：select * from students where not （age>20 and gender=“女”）;
• 年龄不是小于或者等于20 并且是女性：select * from students where not age<=20 and gender=“女”;

模糊查询

• like(% 替换1个或者多个):
  查询姓名中以 “小”开始的名字： select name from students where name like "小%";
  查询姓名中有 “小”开始的名字：select name from students where name like "%小%";

• _替换一个

   查询有2个字的名字：`select name from students where name like "__";`
   查询至少有2个字的名字：`select name from students where name like "__%";`
  

• rlike 正则
  查询以 周开始的姓名:select name from students where name rlike "^周.*";
  查询以 周开始、伦结尾的姓名:select name from students where name rlike "^周.*伦$";

范围查询

• in(1,3,8)表示再一个非连续的范围内
  查询 年龄为21,29的姓名：select name from students where age=21 or age=29;

• not in表示在一个非连续的范围内
  查询 年龄不是21,29之间的信息：select name,age from students where age not in(21,29);

• between…and…:表示在一个连续的范围内:
  查询 年龄不是21到29之间的信息：select name,age from students where age not between 21 and 29;

• 空判断
  判断is null:查询身高为空的信息 ：select name,age from students where high is null;
  判非空is not null:查询身高为空的信息 ：select name,age from students where high is not null;

排序

• order by 字段:默认从小到大排（asc从小到大哦排列，即升序；desc从大到小排序,即降序）
  查询年龄在20到30之间的女性，按照从小到大的顺序：select * from students where (age between 20 and 30) and gender=1 order by age;=select * from students where (age between 20 and 30) and gender=1 order by age asc;
  查询年龄在20到30之间的女性，按照身高从大到小的顺序：select * from students where (age between 20 and 30) and gender=2 order by high desc;

• order by多个字段：
  查询年龄在20到30之间的女性，按照身高从大到小的顺序，如果身高相同的情况下按照年龄从小到大的顺序：select * from students where (age between 20 and 30) and gender=2 order by high desc,age desc;
  按照年龄从小到大，身高从高到矮的顺序排序：select * from students where order by age asc,high desc;

聚合函数

• 总数(count):查询男性有多少人，女性有多少人：select count(*) from students where gender=2;增加可读性：select count(*) as 男性人数 from students where gender=2;
• 最大数（max）:查询最大年龄 ： select max(age) as 最大年龄 from students;查询女性的最高： select max(age) as 最大年龄,max(high) as 最高身高 from students where gender=2;
• 最小数（min）：
• 求和（sum）:计算所有人的年龄总和：select sum(age) from students;
• 平均值（avg）:计算平均年龄：select avg(age) from students;或：select sum(age)/count(*) from students;
• 四舍五入round(a,b）：a为结果，b为保留几位小数：
  计算所有人的平均年龄，保留2位小数：select round(avg(age),2) from students;
  计算女性的平均身高，保留2位小数：select round(avg(high),2) from students where gender=2 ;

分组

• group by,按照性别分组，查询所有的性别：select gender from students group by gender;
  计算每种性别中的人数：select gender count(gender) from students group by gender;
  计算女性的人数： select gender,count(*) as 人数 from students where gender=2 group by gender;

• group_concat(…)：查询同种性别中的人数、姓名：select gender,count(*) as 人数,group_concat(name) from students group by gender;
  查询同种性别中的姓名：select gender,group_concat(name) from students group by gender;
  查询同种性别中的姓名、年龄、id:select gender,group_concat(name,"_",age,"_",id) from students group by gender;

• having:查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30:select gender,group_concat(name) from students group by gender having avg(age)>30;
  查询每种性别中的人数多于2个的信息：select gender,group_concat(name) from students group by gender having count(gender)>2;
  除重

• 显示不同年龄select distinct age from students;

分页

• limit start,count:
  限制查询出来的数据个数：select * from students where gender=2 limit 2;
  查询前5个数据:select * from students where limit 0，5;
  查询前id6-10数据:select * from students limit 5,5;
  每页显示2个，第一个页面：select * from students limit 0,2;
  每页显示2个，第二个页面：select * from students limit 2,2;
  第N页显示X个：select * from students limit （N-1）*X,X;
  每页显示2个，第6个页面,按照年龄从大到小排序：select * from students order by age asc limit 10,2;

链接查询

• inner join … on（内链接）:
  select… from 表A inner join 表B:select * from students inner join classes;
  查询 有能够对应班级的学生以及班级信息:select * from students inner join classes on students.cls_id=classes.id;
  按照要求显示学生所有信息、班级：select students.*,classes.name cls_id from students inner join classes on students.cls_id=classes.id;
  
  只显示姓名、班级：select students.name,classes.name cls_id from students inner join classes on students.cls_id=classes.id;
  
  给数据表起名：select s.name,c.name cls_id from students as s inner join classes as c on s.cls_id=c.id;
  在以上查询中将班级姓名显示在第一列：select c.name,s.name cls_id from students as s inner join classes as c on s.cls_id=c.id; select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
  查询 有能够对应班级的学生以及班级信息，按照班级进行排序：select classes.name,students.* from students inner join classes on students.cls_id=classes.id order by classes.name;
  当同一个班级的学生暗战学生的id进行从小到大排序：select classes.name,students.* from students inner join classes on students.cls_id=classes.id order by classes.name asc,id asc;

• left join:
  查询每位学生对应的班级信息：select * from students left join classes on students.cls_id=classes.id;
  查询没有对应班级信息的学生：： select * from students left join classes on students.cls_id=classes.id having classes.id is null;

• right join on:将数据表名字互换位置，用left join完成

自关联

• 创建areas表：

create table china(
	id int primary key,
	atitle varchar(20),
	pid int
);

	sql文件传入数据表：`source china.sql`
	查询山东省id号：`select id from china where atitle="山东省";`
	查询山东省下的市：select * from china where pid=370000;
	查询青岛市下的区：select * from china where pid=370200;
	查询山东省下的市（一次查询）：` select * from china as province inner join china as city on city.pid=province.id having province.atitle="山东省";`
	或`select province.atitle,city.atitle from china as province inner join china as city on city.pid=province.id having province.atitle="山东省";`

子查询

• 标量子查询：查询出最高的女生信息：select * from students where high=(select max(high) from students);

2、数据库设计

2.1、范式

对设计数据库提出了一些规范，这些规范被称为范式：
（1）第一范式（1NF）：强调的是列的原子性，即列不能够再分称其他几列。
（2）第二范式(2NF)：首先是第一范式，另外包含两部分内容，一是表必须有一个主键；2是没有包含在主键中的列必须完全依赖于主键，不能只依赖于主键的一部分。
（3）第三范式(3NF)：首先是2NF,另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B、非主键列B依赖于主键的情况。

2.2、E-R模型

• E表示entry实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
• R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括一对一、一对多、多对多
• 关系也是一种数据，需要通过一个字段存储在表中
• 实体A对实体B为一对一，则在表A或表B中共创建一个字段，存储零一分表的主键值。

四、MySQL与python交互

4.1、数据的准备、基本查询：

• 创建数据库： create database jing_dong charset=utf8;
• 使用jing_dong数据库：use jing_dong;
• 创建数据表goods:
  注意：设计数据库可参考（58到家数据库30条军规解读）

create table goods(
	id int unsigned primary key auto_increment not null,
	name varchar(150) not null,
	cate_name varchar(40) not null,
	brand_name varchar(40) not null,
	price decimal(10,3) not null default 0,
	is_show bit not null default 1,
	is_saleoff bit not null default 0
);

• 插入数据:

insert into goods values (0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default),
                         (0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default),
                         (0,'g150th 15.6英寸笔记本','游戏本','雷神','8499',default,default),
                         (0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default),
                         (0,'x240 超级本','超级本','联想','4880',default,default),
                         (0,'u330p 13.3英寸超级本','超级本','联想','4299',default,default),
                         (0,'svp13226scb 触控超级本','超级本','索尼','7999',default,default),
                         (0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default),
                         (0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default),
                         (0,'ipad mini 配备retina显示屏','平板电脑','苹果','2788',default,default),
                         (0,'ideacentre c340 20英寸一体电脑','台式机','联想','3499',default,default),
                         (0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default),
                         (0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default),
                         (0,'at7-7414lp 台式电脑 linux','台式机','苹果','3699',default,default),
                         (0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default),
                         (0,'powereedge ii服务器','服务器/工作站','戴尔','5388',default,default),
                         (0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default),
                         (0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default),
                         (0,'商务双肩背包','笔记本配件','索尼','99',default,default),
                         (0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default),
                         (0,'商务双肩背包','笔记本配件','索尼','99',default,default);

sql语句的强化：

• 查询类型cata_name为‘超级本’的商品名称和价格： select name,price from goods where cate_name="超级本";
• 显示商品种类：select distinct cate_name from goods;或者select cate_name from goods group by cate_name;
• 求所有电脑产品的平均价格，并且保留两位小数：select cate_name,round(avg(price),2) from goods;
  -显示每种商品的平均价格并保留两位小数：select cate_name,round(avg(price),2) from goods group by cate_name;
• 查询没中过类型的商品中最贵、最便宜、平均价、数量： select cate_name,max(price) as 最贵,min(price) as 最便宜,avg(price) as 平均价,count(*) as 数量 from goods group by cate_name;
• 查询所有价格大于平均价格的商品，并且按照降序排列： select * from goods where price>(select avg(price) from goods) order by price desc;
  
• 查询每种类型中最贵的电脑信息：

select * from goods inner join
	(
		select 
		cate_name, 
		max(price) as max_price,
		min(price) as min_price,
		avg(price) as avg_price,
		count(*) from goods group by cate_name
		) as goods_new_info 
	on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;

或

select goods.cate_name,goods.name,goods.price from goods inner join
	(
		select 
		cate_name, 
		max(price) as max_price,
		min(price) as min_price,
		avg(price) as avg_price,
		count(*) from goods group by cate_name
		) as goods_new_info 
	on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price order by goods_new_info.cate_name;

4.2、数据的操作演练：拆为多个表：

创建商品分类表

create table if not exists goods_cates(
	id int unsigned primary key auto_increment,
	name varchar(40) not null
);

• 查询goods表中商品的种类：select cate_name from goods group by cate_name;

• 将分组结果写入到good_cates数据表： insert into goods_cates (name) select cate_name from goods group by cate_name;

• 通过goods_cates数据表来更新goods表：update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

• 通过alter table修改表结构： alter table goods change cate_name cate_id int unsigned not null;

• 给goods表设置外键：alter table goods add foreign key (cate_id) references goods_cates(id);
  创建商品品类表

• 创建goods_brand表并将goods表中的brand_name分组同时将分组数据插入到goods_brand表中：

create table if not exists goods_brands(
	id int unsigned primary key auto_increment,
	name varchar(40) not null
) select brand_name as name from goods group by brand_name;

• 通过goods_brands数据表来更新goods数据表： update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;
• 修改表结构：alter table goods change brand_name brand_id int unsigned not null;
• 将brand_id设置为外键：alter table goods add foreign key (brand_id) references goods_brands(id);

如何取消外键约束

• 先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称：show create table goods;
  
• 获取名称之后就可以根据名称来删除外键约束：alter table goods drop foreign key 外键名称；

4.3、Python中操作MySQL步骤

• 在py文件中引入pymysql模块：from pymysql import *;
• Connection对象
  （1）用于建立于数据库的连接
  （2）创建对象：调用connect（）方法

conn = connect(参数列表)
#参数host:连接的MySQL主机，本机是‘localhost’
#参数port:连接的MySQL主机的端口，默认是3306
#参数database:数据库的名称
#参数user:连接的用户名
#参数password：连接的密码
#参数charset：通信采用的编码方式，推荐使用utf

• 对象的方法

close（） #关闭连接
commit() #提交
cursor() #返回Cursor对象，用于执行sql语句并获得结果

• 游标对象
  1）用于执行sql语句，使用频度最高的语句select、insert、update、delete
  2）获取Cursor对象：调用Connection对象的cursor()方法

cs1=conn.cursor()

• 查询数据:

from pymysql import *

def main():
    conn = connect(host='localhost',port=3306,user='root',password='wu97330453822',database='jing_dong')
    cs1 = conn.cursor()
    count = cs1.execute('select id,name from goods where id>=4')

    print("查询到%d数据：" % count)

    for i in range(count):
        result = cs1.fetchone()

        print(result)

    cs1.close()
    conn.close()

if __name__=='__main__':
    main()

• 防注入

    def get_info_by_name(self):
        find_name=input("请输入要查询的商品的名字：")
        #sql="""select * from goods where name='%s';""" % find_name
        sql = "select * from goods where name=s%"
        self.cursor.execute(sql,[find_name])

五、MySQL高级

5.1、视图