首页  思维导图  详情

MySQL数据库——从0到入门

2020-05-14 15:22:58   0  举报





AI智能生成

MySQL数据库保姆级教程及归纳，帮助你快速准确的把握及了解MySQL。

MySQL

mysql的安装配置

数据分析

作者其他创作

大纲/内容

1、数据库的介绍和环境搭建

1、数据库介绍

数据存储

传统记录数据的缺点： <ul><li>不易保存</li><li>备份困难</li><li>查找不便</li></ul>

现代化手段——文件 <ul><li>对于数据容量较大的数据，不能够很好的满足，而且性能较差</li><li>不易扩展</li></ul>

数据库： <ul><li>持久化存储</li><li>读写速度极高</li><li>保证数据的有效性（通过约束条件来保证）</li><li>对程序的支持性非常好，容易扩展 </li></ul>

关系型数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。

包括MySQL、Oracle、MsSqlserver等等

设计原则： <ol><li>命名规范化</li><li>数据的一致性和完整性</li><li>减少数据冗余</li><li>范式理论（3NF）</li></ol>

优点： <ol><li>复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。</li><li>事务支持使得对于安全性能很高的数据访问要求得以实现。</li></ol>

非关系型数据库

非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）

类型： <ul><li>列模型：存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，数据即索引，IO很快，主要是一些分布式数据库）——Hbase</li><li>键值对模型：存储的数据是一个个“键值对”，比如name:liming,那么name这个键里面存的值就是liming——redis,MemcacheDB</li><li>文档类模型：以一个个文档来存储数据，有点类似“键值对”。——mongoDB</li></ul>

优点： <ol><li>性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。</li><li>可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。</li></ol>

理解数据库

<ul><li>数据列：字段</li><li>数据行：记录</li><li>数据表：数据行的集合</li><li>数据库：数据表的集合</li><li>单元格：存储的数据</li><li>ID主键：能够唯一标识某个记录的</li><li>外键：字段所存储的数据是别的表的主键</li></ul>

MySQL

简介： MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品。

特点： <ol><li>使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性</li><li>支持多种操作系统，如Linux、Windows、AIX等</li><li>为多种编程语言提供了API，如C、C++、Python、Java等</li><li>支持多线程，充分利用CPU资源</li><li>优化的SQL查询算法，有效提高查询速度</li><li>提高多语言支持，常见的编码如GB2312、BIG5、UTF8</li><li>提供TCP/IP、ODBC和JDBC等多种数据库连接途径</li><li>提供用于管理、检查优化数据库操作的管理工具</li><li>大型的数据库，可以处理上千万条记录</li><li>支持多种储存引擎</li><li>MySQL使用标准的SQL数据语言形式</li><li>MySQL是可以定制的，采用了GPL协议，你可以修改源代码来开发自己的MySQL系统</li><li>MySQL软件采用了双授权政策，分为商业版和社区版，由于其体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库</li><li>复制全局事务标识</li><li>复制无崩溃从机</li><li>复制多线程从机</li><li>在线DDL更改功能</li></ol>

存数据——快、持久化存储、数据有效性、扩展好

2、环境搭建

MySQL的安装和配置

分为客户端和服务端，安装的是服务端。 然后通过cmd/cmder连接、操作。（充当了MySQL的客户端） 都是命令行的方式来操作数据库。（重点）

直接安装（不建议用） <ul><li>下载地址：www.mysql.com/downloads MySQL社区（GPL）下载  —》适用于Windows的MySQL Installer  —》出来俩，下载哪个都可以，大的更全面  —》得到.msi文件</li></ul><ul><li>安装：https://jingyan.baidu.com/article/0aa223751ed91188cc0d643f.html</li></ul>

集成安装 <ul><li>phpstudy——官网下载，安装和QQ很像，可以开启MySQL服务</li><li>xampp</li></ul>

图形化管理工具 <ol><li>PHPmyadmin</li><li>Navicat</li><li>SQLyog——操作数据库</li></ol>

SQLyog

<ul><li>打开SQLyog</li></ul>连接到我的SQL主机 新建-保存的连接-命名即可 我的SQL主机地址：127.0.0.1或localhost 用户名：root（默认，不修改） 密码：root（默认，不修改） 端口：3306（默认，不修改）

<ul><li>打开后右键创建数据库</li></ul>基字符集：utf8 数据库排序规则：utf8_general_ci

<ul><li>打开数据库右键创建表</li></ul>引擎：InnoDB 字符集：utf8 核对：utf8_general_ci

一般工作时不会接触到图形化管理工具，原因：安全、权限。所以用于辅助学习

选中语句后按F9执行；注释符号为--

建议搭配：phpstudy+SQLyog+cmder（比cmd好用）

cmder的安装和配置

<ul><li>下载地址：http://cmder.net/</li><li>设置环境变量，CMDER_HOME=cmder.exe所在目录，并在path中增加%CMDER_HOME%。 右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建（变量名是：CMDER_HOME，变量值可以浏览目录找到cmder.exe所在目录） 同时编辑用户变量的path，添加%CMDER_HOME%</li></ul><ul><li>这样可以在运行中输入cmder，打开cmder了</li></ul>

phpstudy下载安装完成后，设置环境变量 需要将MySQL文件夹下的bin文件路径（即。。phpStudy\PHPTutorial\MySQL\bin）添加到环境变量中才能使用cmd/cmder操作。 右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建

为什么要配置环境变量？ https://blog.csdn.net/qq_42707118/article/details/85337235

2、数据类型及约束

1.SQL介绍&常见的数据类型

SQL

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。 当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作Oracle，SQL server，MySQL等关系型数据库。

SQL语句主要分为 <ul><li>DQL：（data query language）数据查询语言，用于对数据进行查询，如select</li><li>DML：（data manipulation language）数据操作语言，对数据进行增加、修改、删除，如insert、update、delete</li><li>DDL：（ data definition language）数据定义语言，进行数据库、表的管理等，如create、drop</li></ul>

重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作。

数据完整性

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

常见的数据类型

1.数值类型（12）

整数类型（6）：int=integer

tinyint, smallint, mediumint, int/integer, bigint

定点数（2）：decimal和numeric

<ul><li>decimal(M,D)其中M表示十进制数字总的个数，D表示小数点后面数字的位数 如decimal(5,2)表示共存5位数，小数占2位，即XXX.XX</li></ul>

浮点数（3）：float，double和real

BIT（1）：bit

<ul><li>BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。 BIT其实就是存入二进制的值，类似010110。 如果存入一个BIT类型的值，位数少于M值，则左补0.</li></ul>

2.字符串类型（14）

char和varchar类型（2）

char和varchar类型声明的长度表示你想要保存的最大字符数 <ul><li>char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '；</li><li>varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'；</li></ul>

通常情况下varchar更好，存储空间需要的更小

binary和varbinary类型（2）

binary和varbinary类型类似于char和varchar类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。

blob和text类型（8）

blob是一个二进制大对象，可以容纳可变数量的数据。 <ul><li>有四种blob类型：tinyblob，blob，mediumblob，longblob</li><li>有四种text类型：tinytext，text，mediumtext，longtext</li></ul>

blob和text与varbinary和varchar的相似点： <ul><li>blob列被视为二进制字符串。text列被视为字符字符串，类似char和binary。</li><li>在大多数方面，可以将blob列视为能够足够大的varbinary列。同样，可以将text列视为varchar列。</li><li>当保存或检索blob和text列的值时不删除尾部空格。(这与varbinary和varchar列相同）。</li></ul>不同点： <ul><li>比较时将用空格对text进行扩充以适合比较的对象，正如char和varchar。</li><li>对于blob和text列的索引，必须指定索引前缀的长度。对于char和varchar，前缀长度是可选的。</li><li>blob和text列不能有默认值。</li></ul>

enum（1）

enum是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。    如size enum('x-small','small',medium','large','x-large')

如果输入的不是枚举值，可以插入空字符串""或NULL或第1个元素： <ul><li>如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。</li><li>如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。</li><li>如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。</li></ul>

set类型（1）

set是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。     例如，指定为set('one', 'two') NOT NULL的列可以有下面的任何值： <ul><li>' '</li><li>'one'</li><li>'two'</li><li>'one,two'</li></ul>SET值按数字顺序排序。NULL值排在非NULL SET值的前面。

3.日期时间类型（5）

date，datetime，timestamp类型（3）

<ul><li>date：用'YYYY-MM-DD'格式检索和显示，支持的范围是'1000-01-01'到 '9999-12-31'。</li><li>datetime：以'YYYY-MM-DD HH:MM:SS'格式检索和显示，支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。</li><li>timestamp：包含日期和时间，范围从'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。</li></ul>函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。

time类型（1）

  MySQL以'HH:MM:SS'格式检索和显示time值(或对于大的小时值采用'HHH:MM:SS'格式)。

<ul><li>范围可以从'-838:59:59'到'838:59:59'。</li></ul>小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

year类型（1）

year类型是一个单字节类型用于表示年。

MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

4.几何类型（8）

对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径 更全的数据类型参考https://blog.csdn.net/anxpp/article/details/51284106

各种数据类型占用的存储

数值类型

取够用的就行，尽量减小存储空间

字符串

日期时间类型

2.数据库约束

<ul><li>主键primary key：物理上存储的顺序，必须非空。只有主键才有自增属性</li><li>非空not null：此字段不允许null</li><li>唯一unique：此字段的值不允许重复</li><li>无符号unsigned：非负数</li><li>默认default：当不填写时使用默认值（在索引中设置）</li><li>自增auto_increment：插入新数据时，插入数据时无需设置此列，默认将自增，表中只有一个自增，中间项被删除后仍按原顺序增加</li><li>外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常</li></ul>

3、数据库与数据表的基本操作

1.数据库操作

<ol><li>数据库的连接——mysql -uroot -p或mysql -u root -proot（不推荐，不安全）连接后可直接看到数据库的版本</li><li>显示数据库版本——select version();</li><li>查看所有数据库——show databases;</li><li>显示时间——show now();</li><li>退出数据库——exit 或 quit</li></ol>

<ol><li>创建数据库——create database 数据库名 charset=utf8；</li><li>使用数据库——use 数据库名；</li><li>查看创建数据库的命令——show create database 数据库名；</li><li>查看当前使用的数据库——select database();</li><li>删除数据库——drop database 数据库名；【不可使用！！】</li></ol>

sql语句最后需要有分号；结尾    MySQL不区分大小写

2.数据表操作

<ol><li>查看当前数据库中所有表——show tables;</li><li>创建表——create table 数据表名 (字段类型约束 [ ,字段类型约束]); </li><li>查看表结构——desc 数据库表名；</li><li>查看表的创建语句——show create table 表名字；</li><li>删除表——drop table 表名；【不可使用！！】</li><li>退出表——表不用退出，因为没有use,同一个库中的table不能同名</li></ol>

创建students表（id，name，age，high，gender，cls_id） create table students( id int primary key not null auto_increment, name varchar(30), age tinyint unsigned default18, high decimal(5,2), gender enum('男','女') default '保密', cls_id tinyint );

<ul><li>修改表-添加字段——alter table 表名 add 列名类型及约束；</li><li>修改表-修改字段：不重命名版——alter table 表名 modify 列名类型及约束；</li><li>修改表-修改字段：重命名版——alter table 表名 change 原名新名类型及约束；</li><li>修改表-删除字段——alter table 表名 drop 列名；</li></ul>

4、数据的基本操作：增删改查（curd）

新增

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

......表示按列名顺序输入信息，数据之间用逗号，隔开，中文加上引号 例如 <ul><li>向students表中插入一个学生信息insert into students values(0,'shell',20,181,'男',2)；</li></ul>主键字段用0/null/default来占位 --若插入的是别的函数得到的结果集，则不需要使用values() insert into 表名(列名)【结果集】； insert into 表名(字段) select 字段名 from 表名 ......;

枚举类型插入：下标是从1开始的

比如 <ul><li>说向students表中插入一个学生信息insert into students values(null,'shell',20,181,1,2)；</li></ul> 此时gender的插入没有用'男'，而是用了数字1

部分插入——insert into 表名(列1,......) values(值1,......);

例如： insert into students(`name`,`gender`) values('shell',1) 注意前面列名用的是````,后面数据信息中的字符串用的是''''，``的作用是防止转译。

想要部分插入成功，【非空】且【无默认值】约束的列名必须要有信息输入

多行插入——insert into 表名 values(......),(......),(......);

例如： insert into students values(0,'shell',20,181,'男',2),(0,'shell',20,181,1,2),(0,'666',20,181,'男',2);

修改

语法：update 表名 set 字段1=新值1，字段2=新值2... where 条件;

不加where条件时，全部都会发生改变！ 例如： <ul><li>将名字为shell的改为python</li></ul>update students set name='python' where name='shell'; <ul><li>将id=3的姓名及年龄做修改</li></ul>update students set name='none',age=19 where id=3;

删除

物理删除——delete from 表名 where 条件；

不加where条件时，该表内的数据将会被全部删掉 例如： <ul><li>将students表中id=3的记录删掉delete from students where id=3；</li></ul>

删除的是数据，几乎不这么操作！

逻辑删除

由于直接删除无法找回原来的数据记录，故添加字段is_delete 用1 （是） 0（否）表示是否删除； 则需要删除一个记录时直接将其is_delete列数据修改为0即可

数据不是被删除了，而是被隐藏了

例如， <ul><li>在students表中新建is_delete字段 alter table add is_delete tinyint default 0 comment'0是未删除，1是已删除';</li><li>将id=4的记录删除 update students set is_delete=1 where id=4；</li></ul><ul><li>查找表students所有被删除的字段 select * from students where is_delete=1；</li></ul>

5、MySQL查询

1.基本语法

<ul><li>查询完整语法：</li></ul>select 去重选项字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句]; <ol><li>查询所有字段</li><li>查询指定字段</li><li>使用 as 给字段起别名</li><li>可以通过 as 给表起别名</li><li>在select后面列前使用distinct可以消除重复的行</li></ol>

2.简单查询

查询所有列——select * from 表名；

很少这么做，数据量过大

查询指定列——select 列1,列2,... from 表名;

例如，select name,age,gender from students

去重复字段的查询——select distinct 列1,列2... from 表名；

例如， <ul><li>查询students表中所有的不同的名字select distinct name from students；</li></ul>

distinct去重，当查询多列时，只有当所查找的列名1,列名2....内容都相同才会只显示一条（即整个查询的行是否重复）

使用as为查询的结果的列或表指定别名

当一些字段名称或表名称太长或过于复杂可以为其起别名，一般两个表联合查询时才会给表取别名。 例如，将查询的结果中name显示为姓名 select name as 姓名,age,gender from students where name='shell';（条件中依旧是原名称 name）

3.条件查询

基本语法

语法： select ... from 表名 where 条件； 例如， select name from students where id=3; select * from students where name<>'shell';

having 条件表达式：用来分组查询后指定一些条件来输出查询结果 having作用和where一样，where作用于原始数据集；having作用于分组后的结果集

比较运算符

比较运算符： <ul><li>等于=</li><li>大于></li><li>大于等于>=</li><li>小于<</li><li>小于等于<=</li><li>不等于 !=或<></li></ul>

条件写成类似于18<age<28无意义，不会报错，但按默认没加该条件的情况运行

逻辑运算符

逻辑运算符： <ul><li>and交——查询18岁以上的女性select * from students where age>18 and gender='女';</li><li>or并——查询编号小于4或没被删除的学生select * from students where id<4 or is_delete=0；</li><li>not取反，补——加括号以确定优先级，括号里的先计算     查询不是18岁女性的学生select * from students where not (age=18 and gender='女');     查询年龄不是18岁的女性select * from students where not age=18 and gender=2; MySQL数据库中优先级：not>and>or，一般通过加括号的形式解决，且可读性更高。 </li></ul>

模糊查询

like'......' %表示任意个字符串 _表示一个字符串

<ul><li>查询姓名以“小”开始的学生信息select * from students where name like '小%';</li><li>查询姓名有“小”的学生信息select * from students where name like'%小%';</li><li>查询姓名是两个字的学生信息select * from students where name like'__';</li><li>查询姓名至少是两个字的学生信息select * from students where name like'__%';</li></ul>

数据量大时不使用，like的使用效率低。

rlike

用法与like相同，但rlike支持正则表达式

正则表达式：正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。 .     匹配任意单个字符串 *    匹配0个或多个前一个得到的字符 []    匹配任意一个[]内的字符，[ab]*可以匹配空字符串、a、b、或由任意个a和b组成的字符串。 ^    匹配开头，如^s匹配以s开头的字符串 $    匹配结尾，如s$匹配以s结尾的字符串 {n}   匹配前一个字符反复n次

例如 查询姓名以周开始的学生信息select * from students where name rlike"^周.*"; 查询姓名以周开始、伦结尾的学生信息select * from students where name rlike"^周.*伦$";

范围查询

<ul><li>in表示在一个非连续范围内</li><li>not in不非连续的范围之内</li><li>between...and...表示在一个连续的范围内</li><li>not between...and...表示不在一个连续的范围内</li></ul>

<ul><li>查询年龄为18、34的姓名select name,age from students where age in(18,34);</li><li>查询姓名不是shell、python的学生信息 select * from students where name not in('shell','python');</li><li>查询id是2到5的男生信息select * from students where id between 2 and 5 and gender='男';</li></ul>（超出边界不会报错，会返回当前到信息的终点） <ul><li>查询年龄不在18到34的男生信息select * from students where (age not between 18 and 34) and gender=1;</li></ul>

空判断

<ul><li>判空is null</li><li>判非空is not null</li></ul>注意： <ol><li>不能用=</li><li>null(占用空间地址的)与""(空字符串,不占空间地址的)是不同的</li></ol>

查询姓名信息为空的男生信息select * from students where name is null and gender=1；

4.聚合函数

<ol><li>count(*)总数  表示计算总行数，括号中写星与列名，结果是相同的</li><li>max(列)最大值  表示求此列的最大值</li><li>min(列)最小值  表示求此列的最小值</li><li>sum(列)求和  表示求此列的和_如果求和字段是varchar 则为0；若字段内容是数字，则相加</li><li>avg(列)平均值  表示求此列的平均值_默认保留四位小数 round（123.45,1）四舍五入，保留一位小数</li></ol>

<ul><li>查询学生总数select count(*) as 总人数 from students; 查询男生有多少人select count(*) as 男生人数 from students where gender=1;</li></ul><ul><li>查询女生最大的年龄select max(age) from students where gender=2; 查询最大年龄的女生姓名 <strike>select id from students where max(age) and gender=2</strike>; 需要用到子查询select name from students where gender=2 and age=(select max(age) from students where gender=2);</li></ul><ul><li>查询未删除学生的最小编号 select min(id) from students where is_delete=0;</li><li>查询男生的年龄和select sum(age) from students where gender=1;</li><li>查询未删除女生年龄的平均值select avg(age) from students where gender=2 and is_delete=0;</li><li>计算平均年龄，保留两位小数select round(avg(age),2) from students;或者select round(sum(age)/count(*),2) from students;</li></ul>

5.分组

group by

group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组 group by可用于单个字段分组，也可用于多个字段分组

语法

select ...字段1 from 表名 group by 字段； ...字段1一般与聚合函数共同使用，且字段1是真正能区分这个组的字段。

例如： <ul><li>按照性别分组，查询所有性别select gender from students group by gender；</li><li>计算男生和女生的人数select gender as 性别,count(*) from students group by gender;</li><li>男女同学的最大年龄select gender as 性别,max(age) from students group by gender;</li></ul>

group by + group_concat()

group_concat(字段名)可以作为一个输出字段来使用  表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

<ul><li>查询同性别学生的姓名select gender as 性别,group_concat(name) from students group by gender;</li><li>查询同性别学生的姓名,年龄select gender as 性别,group_concat('姓名',name,'_','年龄',age) from students group by gender; </li></ul>'姓名','_','年龄'目的是间隔开两个字段的信息或加以标识，用别的符号/蚊子等也可，字段前后的可以加。（相当于python中的字符串拼接）

group by + 集合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

group by + having

<ul><li>查询人数大于5的性别的人数select gender,count(*) from students group by gender having count(*)>5;</li><li>查询人数大于5的性别的人姓名select gender,count(*),group_concat(name) from students group by gender having count(*)>5;</li><li>查询平均年龄超过18岁的性别及姓名select gender,group_concat(name),avg(age) from students group by gender having avg(age)>18;</li></ul>

6.排序

目的：为了方便查看数据，可以对数据进行排序 语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]; 说明： <ol><li>将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推</li><li>默认按照列值从小到大排列（asc）</li><li>asc从小到大排列，即升序</li><li>desc从大到小排序，即降序</li></ol>

<ul><li>查询年龄在18到26岁之间的男同学，按照年龄从小到大排序</li></ul>select * from students where gender=1 and (age between 18 and 26) order by age; <ul><li>查询年龄在18到34岁之间的女同学，按照身高从高到矮排序</li></ul>select * from students where gender=2 and (age between 18 and 34) order by high desc;

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

7.分页

目的：获取部分行，当数据量过大时，在一页中查看数据非常麻烦  语法：select * from 表名 limit start,count； 说明： <ol><li>从start开始(从0开始)，获取count条数据【起始位置（从0开始），数据个数】</li><li>start,count不能写数学公式</li><li>limit 只能写到SQL语句最后，只限制显示的条数</li></ol>

查询前5个数据select * from students limit5; 查询id 6-10（包含）的数据select * from students limit 5,5;

制作分页——limit (第N页-1)*每页显示的个数，每页显示的个数 每页显示2个，第1个页面select * from students limit 0,2; 每页显示2个，第2个页面select * from students limit 2,2; 每页显示2个，第3个页面select * from students limit 4,2; 每页显示2个，第4个页面select * from students limit 6,2;

8.连接查询

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

语法：select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列；

mysql支持三种类型的连接查询。

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

例如， <ul><li>查询有能够对应班级的学生以及班级信息(含classes表中的id等)</li></ul>select * from students inner join classes on students.cls_id=classes.id; <ul><li>查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称(不含classes表中的id等) </li></ul>select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id; <ul><li>查询有能够对应班级的学生的姓名班级</li></ul>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; <ul><li>查询有能够对应班级的学生以及班级信息，并按班级进行排序</li></ul>select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;   --接上，当同一个班级时，按照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 asc;

左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充 优化：以小表驱动大表

查询每位学生对应的班级信息 select students.name,classes.name from students left join classes on students.cls_id=classes.id; 查询没有对应班级信息的学生信息，并按id排序 select * from students as s left join classes as c on s.cls_id=c.id having c.id is null order by s.id;

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

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

9.子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。

例如， <ul><li>查询最高的男生信息</li></ul>select * from students where gender=1 and high=(select max(high) from students where gender=1); <ul><li>查询出高于平均身高的信息</li></ul>select * from students where high>(select avg(high) from students);

列级子查询 查询学生的班级号能够对应的学生信息 select * from students where id in(select id from classes); select s.* from students as s left join classes as c on s.cls_id=c.id having c.id is not null;

10.自关联

自关联其实就是连结查询，需要两张表，只不过它的左表（主表）和右表（子表）都是自己。在做自连接查询的时候是自己链接自己，分别给主表和子表取别名，再付加条件执行。

在你填写收货信息的时候，会提示你选择省-市-区，当你选则江西省的后，市选项里不会出现广州市，只会显示江西省下的市，这样一个省级联动如何实现？ 如以下两种方法。 查找广州市下的区也是同样的原理，但是如果想要查找县，乡，甚至是具体到某一个街道，那就需要更多的表，这个查询就变得很长，自关联就是把方法1中的3个表融合为一个表。 方法二的优点：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。

No.1.多个表的连接查询

<ul><li>设计省信息表(province)</li></ul>id provinceid province <ul><li>设计市信息表(city)</li></ul>id cityid ctitle provinceid-与省表中对应 <ul><li>设计区信息表(areas)</li></ul>id atitle cityid-与市表中对应

例如， 查询广东省下的市      1.现在省表中拿到广东省的provinceid——select * from province where province='广东省';      2.再去city表中找到provinceid对应的市——select * from city where provinceid='440000'; 或者       使用子查询select * from city where provinceid =(select provinceid from province where province='广东省'); 或者       使用内连接selecet * from city as c inner join province as p on p.provinceid=c.provinceid having p.province='广东省';

No.2.一个表的自关联

<ul><li>设计表结构(site)</li></ul>id 从中国(1)开始 pid 中国(0)/省(1-国id)/市(省id)/区(市id) name 省/市/区的名字 --即，省的pid=国的id，市的pid=省的id，区的pid=市的id，区的id自增

查询广东省下的市 select * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name='广东省';  查询广州市下的区 selcect * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name='广州市';

6、MySQL外键

外键的特点

<ol><li>MySql外键必须使用存储引擎为innodb</li><li>因程序很难100%保证数据的完整性，而外键在数据库服务器当机或者出现其他的问题时，能够最大限度的保证数据的一致性和完整性。</li><li>设置外键约束的两个表之间会具有父子关系，即子表中外键的字段的取值范围由父表所决定</li><li>设置外键一定程度上降低数据库的速度</li><li>子表的外键字段的数据类型和父表要一致</li></ol>

添加外键约束

语法： alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

例如， 将students表中的cls_id与class表中的id相关联 alter table students add constraint fk_cls_id foreign key(cls_id) references class(id);

引入外键之后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的参照完整性。 <ul><li>--当class表中的id只有1,2,3 则students表中的cls_id插入其他数据（比如4）【 insert into students values(0,'orange',18,156,'男',4,0)；】会失败，因为字段cls_id不允许存在4，若想成功插入，需要先给class表中加入id为4的数据。【 insert into class values(4,'四班'); 】</li><li>--当class表中的id有1,2,3 students表中的cls_id有1,2 那么class表中的id1,2不允许修改，而3可以修改。</li></ul>

删除外键约束

接上↑ 若想将class表中的信息删除，若id已使用则会因为students的外键关系而不被允许。

语法： alter table 表名 drop foreign key 外键名;

<ul><li>取消students表中的cls_id与class表中的id的关联关系</li></ul>alter table students drop foreign key fk_cls_id;

总结

<ol><li>一个表可以有多个外键</li><li>对子表students（外键所在的表）的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败</li><li>对父表class的作用：对父表的主键字段进行删或改时，如果对应的主键在字表中被应用，操作会失败。</li></ol>

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

外键的定制作用：三种约束模式

语法： alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名) on delete 约束模式;

<ul><li>district：严格模式(默认), 父表不能删除或更新一个被子表引用的记录。</li><li>cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作。</li><li>set null：置空模式,前提外键字段允许为NULL, 父表操作后，子表对应的字段被置空。</li></ul>

7、MySQL和Python交互

1、数据准备

实际工作中不是用cmder，而是用python代码充当客户端

创建数据表

--创建“京东”数据库 create database jd charset=utf8; --使用“京东”数据库 use jd; --创建一个商品goods数据表 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 tinyint not null default 1, is_saleoff tinyint 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 超极本','超级本','联想','4880',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); insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

2、数据表的拆分

创建“商品分类”表

--创建“goods_cates”表 create table goods_cates( id int unsigned primary key auto_increment not null, name varchar(40) not null); --查询goods表中的商品种类 select cate_name from goods group by cate_name; --将分组结果写入“goods_cates”数据表 insert into goods_cates(name) select cate_name from goods group by cate_name;--注意，此处没有使用values() insert into 表名(字段) select 字段名 from 表名 ......;

同步表数据

--通过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; <ul><li>这种情况下与外键有所不同，后期goods_cates表中的id的变化不会同步至goods表中。 即再去改变goods_cates表中的id，goods表中的cate_name不会改变。 update 表1 inner join 表2 on 条件 set xxx=xxx；</li></ul>

建立外键

--将goods表中的cate_name与goods_cates表中的id相关联 alter table goods modify cate_name int unsigned not null; alter table goods add constraint fk_cate_name foreign key(cate_name) references goods_cates(id);

3、Python操作MySQL

安装PyMySQL

什么是PyMySQL

为了使python连接上数据库，你需要一个驱动，这个驱动是用于与数据库交互的库。 pymqsql是一个使Python连接到MySQL的库，它是一个纯Python库。 python是充当客户端的。

环境要求

PyMySQL支持的python版本： <ul><li>python2</li><li>python2.7</li><li>python3</li><li>python version >=3.4</li></ul>

PyMySQL的安装

在windows操作系统上安装

打开CMD/cmder窗口（命令提示符），运行以下其中一行语句： Python2.7 和 Python3： pip install PyMySQL Python2.7： pip2.7 install PyMySQL Python3： pip3 install PyMySQL python2： pip install MySQLdb 查看PyMySQL版本信息： pip3 show PyMySQL

Ubantu安装

https://www.jianshu.com/p/d84cdb5e6273

Python操作MySQL步骤

1、开始

<ul><li>打开PyCharm，新建文件——选中文件夹，右键 -new  -Python file  再输入名字即可</li><li>导入数据库—— from pymysql import *  或者 import pymysql</li></ul>

2、创建connection

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

conn=connect（参数列表） 参数host：连接的mysql主机，如果本机是'localhost'或'127.0.0.1' 参数port：连接的mysql主机的端口，默认是3306 参数database：使用的数据库的名称 参数user：连接的用户名 参数password：连接的密码 参数charset：通信采用的编码方式，推荐使用utf8 **使用变量conn来接收返回值，随便用abc也可以

import pymysql conn = pymysql.connect(host = 'localhost',port=3306,database='demo1',user='root',password = 'root',charset = 'utf8')

from pymysql import * conn = connect(host = 'localhost',port=3306,database='demo1',user='root',password = 'root',charset = 'utf8')

connection对象方法

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

3、获取cursor

用于执行sql语句,使用频度最高的语句为select、insert、update、delete.

获取cursor对象：调用connection对象的cursor()方法

cs=conn.cursor() 用变量cs接收返回值

cursor对象方法

close()关闭先关闭游标,在关闭链接 execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句 fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组 fetchmany(n)执行查询语句时，获取查询结果集的n行数据，一行构成一个元组，再将这些元组装入一个元组返回 fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

查询结果放在电脑内存中，所以不要一次性返回太多数据，会占用内存。

4、SQL操作

执行查询 执行命令 获取数据 处理数据

异常处理？

5、关闭cursor

cs.close()

6、关闭connection

conn.close()

7、结束

8、Python操作MySQL

1、MySQL-封装DB类