MySQL数据库

阅读量：276 次

发布时间：2019-03-03

本文共 17942 字，大约阅读时间需要 59 分钟。

1.数据库的基本使用

（1）数据库

数据库就是一种特殊的文件。

关系型数据库核心元素：

数据行（记录）

数据列（字段）

数据表（数据行的集合）

数据库（数据表的集合）

RDBMS（Relational Database Management System），主要产品有Oracle、mysql、ms sql server、sqlite

（2）SQL

SQL（Structured Query Language）：结构化查询语言，是一种用来操作RDBMS的数据库语言。

SQL语句主要分为：

DQL：数据查询语言，用于对数据进行查询，如select

DML：数据操作语言，用于对数据进行新增、修改、删除，如insert、update、delete

TPL：事务处理语言，对事务进行处理，如begin transaction、commit、rollback

DCL：数据控制语言，进行授权与权限回收，如grant、revoke

DDL：数据定义语言，进行数据库、表的管理，如create、drop

CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

SQL语句不区分大小写，SQL语句最后要有分号 ; 结尾

（3）数据完整性

在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表时，为表添加一些强制性的验证，包括数据字段的类型、约束。

（3.1）数据类型

常用数据类型：

整数：int，bit

小数：decimal

字符串：varchar，char，text

日期时间：date，time，datetime

枚举类型：enum

特别说明：

decimal(5,2)表示共存5位数，小数占2位

char表示固定长度的字符串，如char(3)，填充’ab’时会补一个空格变为’ab ‘

varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’

Text表示存储大文本，当字符大于4000时推荐使用

对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

使用数据类型的原则是：够用就行

（3.2）约束

主键primary key：物理上存储的顺序

非空not null：此字段不允许填写空值

唯一unique：此字段的值不允许重复

默认default：当不填写此字段时会使用默认值，如果有填写以填写为准

外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。
虽然外键约束可以保证数据的有效性，但是在进行数据的crud时，都会降低数据库的性能。

（4）数据库的操作

-- 连接数据库mysql -u root -p-- 退出数据库quitexit-- 查看数据库文件的存放位置show GLOBAL VARIABLES like '%datadir%';-- 显示数据库版本SELECT version();-- 显示时间SELECT now();-- 查看所有数据库show databases;-- 创建数据库create database python01;create database python02 charset=utf8;-- 查看创建数据库的语句show create database python01;show create database python02;-- 使用数据库use python01;-- 查看当前使用数据库selec	database();-- 删除数据库DROP database python01;

（5）数据表的操作

-- 查看当前数据库中的所有表show tables;-- 创建表-- create table 数据表名(字段 类型 约束，字段 类型 约束);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);create table classes(	id int UNSIGNED not null auto_increment primary key,	name varchar(30));-- 查看创建数据表的语句show create table students;show create table classes;-- 查看表结构desc students;desc classes;-- 修改表，添加字段-- alter table 表名 add 列名 类型及约束;alter table students add birthday datetime;-- 修改表，修改字段，重命名版-- alter table 表名 change 原列名 新列名 类型及约束;alter table students change birthday birth datetime not null;alter table students -- 修改表，修改字段，不重命名版-- alter table 表名 modify 列名 类型及约束;alter table students modify birth date not null;-- 修改表，删除字段-- alter table 表名 drop 列名alter table students drop birth;-- 删除表drop table students;

（6）数据的增删改查

-- 增加，主键字段可以用0/null/default来占位-- 全部插入：insert into 表名 values(...);insert into classes values(0,'1班');insert into students values(0,'小蓝',20,179.50,'男',1);insert into students values(null,'小红',21,160,'女',1);insert into students values(default,'小黄',22,169,3,1);-- 部分插入：insert into 表名 (列1,...) values(值1,...);insert into students (name,gender) values('小黑',2);-- 多行插入insert into students values(default,'小橙',23,171.50,'女',1),(default,'小绿',18,190.50,'男',1);insert into students (name,gender) values('小紫','女'),('小棕','男');-- 修改-- update 表名 set 列1=值1,列2=值2,...update students set gender=1;update students set gender=2 where name='小红';update students set gender=3,age=30 where id=4;-- 删除-- 物理删除delete from students;delete from students where name='小绿';-- 逻辑删除alter table students add is_delete int default 0;update students set is_delete=1 where id=5;-- 查询基本使用-- 查询所有列select * from students;-- 一定条件查询select * from students where name='小绿';-- 查询指定列select name,gender from students;-- 使用as为列或表指定别名select name as 姓名,gender as 性别 from students;

2.数据库查询

（1）数据准备

-- 创建数据库create database python01;-- 使用数据库use python01;-- 创建数据表studentscreate table students(	id int unsigned not null auto_increment primary key,	name varchar(30) default '',	age tinyint UNSIGNED default 0,	high decimal(5,2),	gender enum('男','女','中性','保密') default '保密',	cls_id int UNSIGNED,	is_delete int default 0);-- 创建数据表tablecreate table classes(	id int UNSIGNED not null auto_increment primary key,	name varchar(30) not null);-- 向students表中插入数据insert into students VALUES(0,'小周',10,150.00,2,1,0),(0,'小杰',18,180.00,1,2,0),(0,'小红',23,160.50,2,1,1),(0,'小蓝',33,170.00,1,2,0),(0,'小杨',26,187.00,3,1,0),(0,'小晴',41,178.50,4,3,1),(0,'小方',18,168.00,3,1,0),(0,'小花',22,159.00,2,2,0),(0,'小敏',9,147.00,2,1,0),(0,'小灰',39,169.00,1,3,0);-- 向classes表中插入数据insert into classes values(0,'1班'),(0,'2班'),(0,'3班');

（2）基本查询

-- 查询所有字段select * from students;select * from classes;-- 查询指定字段select name,age from students;select id,name from classes;-- 使用as给字段起别名select name as 姓名,age as 年龄 from students;-- select 表名.ziduan ...from 表名select students.name,students.age from students;-- 使用as给表起别名select s.name,s.age from students as s;-- 消除重复行select distinct gender from students;

（3）条件查询

（3.1）比较运算符

-- 比较运算符>、<、>=、<=、=、！=select * from students where age>=18;select name from students where age=18;

（3.2）逻辑运算符

-- 逻辑运算符and、or、not-- andselect * from students where age>18 and age<28;select * from students where age>18 and gender=2;-- orselect * from students where age>18 or high>=180;-- not-- 不在 18岁以上的女性 这个范围内的信息select * from students where not (age>18 and gender=2);-- 年龄不大于18 并且是女性的信息select * from students where not age>18 and gender=2;

（3.3）模糊查询

-- 模糊查询like、%(替换0个或多个)、_(替换1个)、rlike(正则)-- 查询姓名中以“小”开始的信息select * from students where name like '小%';-- 查询姓名中有“小”的信息select * from students where name like '%小%';-- 查询有2个字的信息select * from students where name like '__';-- 查询至少有2个字的信息select * from students where name like '__%';-- 查询以周开始的信息select * from students where name rlike '^周.*';-- 查询以周开始、伦结尾的信息select * from students where name rlike '^周.*伦$';

（3.4）范围查询

-- 范围查询in(在非连续的范围内)、not in(不在非连续的范围内)、between...and...(在连续的范围内)、not between...and...(不在连续的范围内)-- 查询年龄为18、32的信息select * from students where age in(18,32);-- 查询年龄不在18、32的信息select * from students where age not in(18,32);-- 查询年龄在18到32岁之间的信息select * from students where age between 18 and 32;-- 查询年龄不在18到32岁之间的信息select * from students where age not between 18 and 32;select * from students where not age between 18 and 32;

（3.5）空判断

-- 空判断is null、is not nullselect * from students where high is null;select * from students where high is not null

（4）排序

-- order by 字段，asc升序、desc降序select * from students where age between 18 and 32 and gender=1 order by age;select * from students where age between 18 and 32 and gender=1 order by age asc;select * from students where age between 18 and 32 and gender=1 order by age desc;-- order by 多个字段-- 查询年龄在18到32之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序select * from students where age between 18 and 32 and gender=2 order by high desc,age asc;

（5）聚合函数

-- 总数countselect count(*) as 男性人数 from students where gender=1;select count(*) as 女性人数 from students where gender=2;-- 最大值maxselect max(age) from students;-- 最小值minselect min(age) from students;-- 求和sumselect sum(age) from students;-- 平均值avgselect avg(age) from students;select sum(age)/count(*) from students;-- 四舍五入-- 计算所有人的平均年龄，保留2位小数select round(avg(age),2)from students;select round(sum(age)/count(*),2) from students;

（6）分组

-- group by-- 按照性别分组，查询所有的性别select gender from students group by gender;-- 计算每种性别中的人数select gender,count(*) from students group by gender;-- group_concat(expr)-- 查询同种性别中的姓名和年龄select gender,group_concat(name,"_",age) from students group by gender;-- having-- 查询平均年龄超过30岁的性别，以及姓名select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;-- 查询每种性别中的人数多于2个的信息select gender,group_concat(name,"_",age),count(*) from students group by gender having count(*)>2;

（7）分页

-- limit start, end-- 限制查询出来的数据个数select * from students limit 2;-- 查询前5个数据select * from students limit 0,5;-- 查询id为6~10的信息select * from students limit 5,5;-- 每页显示2个，第1个页面select * from students limit 10,5;

（8）连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回。

内连接查询：查询结果为2个表匹配到的数据。

右连接查询：查询结果为2个表匹配到的数据+右表特有的数据，对于左表中不存在的数据使用null填充。

左连接查询：查询结果为2个表匹配到的数据+左表特有的数据，对于右表中不存在的数据使用null填充。

-- 内连接inner join...on-- 查询有能够对应班级的学生以及班级信息select * from students inner join classes on students.cls_id=classes.id;select students.*,classes.name from students inner join classes on students.cls_id=classes.id;select students.name,classes.name from students inner join classes on students.cls_id=classes.id;select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;-- 查询有能够对应班级的学生以及班级信息，按照班级进行排序，班级相同时按照id排序select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;-- 左连接 left join...on-- 查询每位学生对应的班级信息select * from students as s left join classes as c on s.cls_id=c.id;-- 查询没有对应班级信息的学生select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

（9）子查询

-- 子查询-- 查询出大于平均年龄的信息select * from students where age>(select avg(age) from students);-- 查询年龄最大的男生信息select * from students where age=(select max(age) from students where gender=1);-- 查询学生的班级号能够对应的学生信息select * from students where cls_id in (select id from classes);

3.练习

（1）准备数据

-- 创建数据表create database jing_dong charset=utf8;-- 使用“京东”数据库use jing_dong;-- 创建一个商品goods数据表create table goods(	id int unsigned not null auto_increment primary key,	name varchar(100) not null,	cate_name varchar(40) not null,	brand_name varchar(40) not null,	price decimal(10,3) not null default 0,	is_show int not null default 1,	is_saleoff int not null default 0);-- 向goods表中插入数据insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);insert into goods values(0,'y400n 14.0英寸笔记本','笔记本','联想','4999',default,default);insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);insert into goods values(0,'x240 超级本','超级本','联想','4888',default,default);insert into goods values(0,'u330p 13.3英寸超级本','超级本','联想','4299',default,default);insert into goods values(0,'svp13226scb 触控超级本','超级本','索超','7999',default,default);insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);insert into goods values(0,'ideacentre c340 20英寸一体电脑','台式机','联想','3499',default,default);insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);insert into goods values(0,'at7-7414lp 台式电脑 Linux','台式机','宏碁','3699',default,default);insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);insert into goods values(0,'mac pro专业版台式电脑','服务器/工作站','苹果','28888',default,default);insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);

（2）SQL演练

（2.1）查询演练

-- 查询cate_name为“超级本”的商品名称，价格select name,price from goods where cate_name='超级本';-- 显示商品的种类select cate_name from goods group by cate_name;select distinct cate_name from goods;-- 求所有电脑产品的平均价格，并且保留2位小数select round(avg(price),2) as '平均价格' from goods;-- 显示每种商品的平均价格select cate_name,avg(price) from goods group by cate_name;-- 查询每种类型的商品中最贵、最便宜、平均价、数量select cate_name,max(price),min(price),avg(price),count(*) 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 		from goods group by cate_name	) as new_goods_info	on goods.cate_name=new_goods_info.cate_name and goods.price=new_goods_info.max_price;

（2.2）同步表数据

-- (1)创建商品分类表goods_catescreate table if not exists goods_cates(	id int unsigned not null auto_increment primary key,	name varchar(40) not null);-- （2）将goods表中查询出来的商品种类写入到goods_cates表中insert into goods_cates(name) select cate_name from goods group by cate_name;-- （3）创建商品品牌表goods_brands，并且同时写入记录create table if not exists goods_brands(	id int unsigned not null auto_increment primary key,	name varchar(40) not null	)select brand_name as name from goods group by brand_name;-- （4）通过goods_cates和goods_brands数据表来更新goods表，向goods表中的cate_name写入商品分类id，向goods表中的brand_name写入商品品牌idupdate goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;-- （5）修改表goods的结构，将cate_name名称改为cate_id并改成int类型，将brand_name名称改为brand_id并改成int类型alter table goods change cate_name cate_id int unsigned not null,change brand_name brand_id int unsigned not null;-- （6）对表googs表中的cate_id和brand_id增加外键约束alter table goods add foreign key (cate_id) references goods_cates(id);alter table goods add foreign key (brand_id) references goods_brands(id);

4.MySQL与Python交互

（1）Python中操作MySQL步骤

在这里插入图片描述

（2）增删改查

from pymysql import *class JD(object):    def __init__(self):        # 创建connection连接        self.conn = connect(host='localhost', port=3306, user='root', password='123456', database='jing_dong',                            charset='utf8')        # 获取cursor对象        self.cursor = self.conn.cursor()    def __del__(self):        # 关闭cursor对象        self.cursor.close()        # 关闭connection对象        self.conn.close()    def execute_sql(self, sql):        # 执行查询语句        self.cursor.execute(sql)        for temp in self.cursor.fetchall():            print(temp)    def show_all_items(self):        """显示所有商品"""        sql = 'select * from goods;'        self.execute_sql(sql)    def show_cates(self):        """显示所有商品分类"""        sql = 'select name from goods_cates;'        self.execute_sql(sql)    def show_brands(self):        """显示所有商品品牌"""        sql = 'select name from goods_brands'        self.execute_sql(sql)    def add_brands(self):        """添加商品分类"""        item_name=input('请输入新商品分类的名称')        sql="""insert into goods_cates (name) values ("%s")"""%item_name        self.cursor.execute(sql)        self.conn.commit()    def get_info_by_name(self):        """根据名字查询商品"""        find_name=input('请输入要查询的商品的名字')        # 以下语句存在sql注入的漏洞，find_name=' or 1=1 or '时查出所有数据        # sql="""select * from goods where name='%s';"""%find_name        # self.execute_sql(sql)        # 防sql注入        sql="select * from goods where name=%s"        self.cursor.execute(sql,find_name)        for temp in self.cursor.fetchall():            print(temp)    def print_menu(self):        print("..........京东..........")        print("1: 所有的商品")        print("2: 所有的商品分类")        print("3: 所有商品品牌分类")        print("4: 添加一个商品分类")        print("5: 根据名字查询一个商品")        return input("请输入功能对应的序号：")    def run(self):        while True:            num = self.print_menu()            if num == "1":                # 查询所有商品                self.show_all_items()            elif num == "2":                # 查询商品分类                self.show_cates()            elif num == "3":                # 查询商品品牌分类                self.show_brands()            elif num=="4":                # 添加商品分类                self.add_brands()            elif num=="5":                # 根据名字查询商品                self.get_info_by_name()            else:                print("输入有误，请重新输入...")def main():    # 1. 创建一个京东商城对象    jd = JD()    # 2. 调用这个对象的run方法，让其运行    jd.run()if __name__ == '__main__':    main()

（3）参数防SQL注入

find_name=input('请输入要查询的商品的名字')# 以下语句存在sql注入的漏洞，find_name=' or 1=1 or '时查出所有数据sql="""select * from goods where name='%s';"""%find_nameelf.execute_sql(sql)# 防sql注入sql="select * from goods where name=%s"self.cursor.execute(sql,find_name)

5.MySQL高级

（1）视图

视图是对若干张基本表的引用，是一条select语句执行后返回的结果集，是一张虚拟的表。

基本表数据发生了改变，视图也会跟着改变。

视图的用途就是查询。

-- 定义视图-- create view 视图名称 as select语句;-- 查询3张表的信息select * from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;select g.*,c.name as cate_name,b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;-- 将以上的查询结果，作为一个虚拟的表，就是视图create view v_goods_info as select g.*,c.name as cate_name,b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;-- 查看视图show tables;-- 使用视图，用于查询select * from v_goods_info;-- 视图不支持更新-- 以下语句会报错，update v_goods_info set name='小霸王学习机' where id=20;-- 删除视图drop view v_goods_info;

（2）事务

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务四大特性（ACID）

Atomicity原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中的一部分操作。

Consistency一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。

Isolation隔离性：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

Durability持久性：一旦事务提交，则其所做的修改会永久保存到数据库。

-- 开启事务begin;start transaction;-- 执行修改命令-- 提交事务commit;-- 回滚事务rollback;

（3）索引

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

索引的目的在于提高查询效率。

要注意的是，建立太多的索引会影响更新和插入的速度，也会占用过多的磁盘空间。

-- 查看索引-- show index from 表名show index from test_index;-- 创建索引-- create index 索引名称 on 表名(字段名称(长度))，如果字段类型不是字符串可以不写长度部分;-- 为表title_index的title列创建索引create index title_index on test_index(title(10));-- 删除索引-- drop index 索引名 on 表名drop index title_index on test_index;

索引Demo

-- 创建测试表test_indexcreate table test_index(	title varchar(10));-- 通过pymysql模块，向表中加入十万条数据from pymysql import *;def main():    # 创建connection连接    conn = connect(host='localhost', port=3306, user='root', password='123456', database='jing_dong')    # 获取cursor对象    cursor = conn.cursor()    # 插入10万条数据    for i in range(100000):        cursor.execute("""insert into test_index values ('ha-%d')""" % i)    # 提交数据    conn.commit()if __name__ == '__main__':    main()-- 查询-- 开启运行时间监测set profiling=1;-- 查找第1万条数据ha-99999select * from test_index where title='ha-99999';-- 查看执行的时间show profiles;-- 创建索引-- create index 索引名称 on 表名(字段名称(长度))，如果字段类型不是字符串可以不写长度部分;-- 为表title_index的title列创建索引create index title_index on test_index(title(10));-- 查找第1万条数据ha-99999select * from test_index where title='ha-99999';-- 再次查看执行的时间show profiles;

在这里插入图片描述