MySQL高级

mysql基本使用

一、数据储存

1.传统记录数据的缺点:

​ 不易保存
​ 备份困难

​ 查找不便

2.现代化手段----文件

​ 使用简单，例如python中的open可以打开文件，用read/write对文件进行读写，close关闭文件
​ 对于数据容量较大的数据，不能够很好的满足，而且性能较差
​ 不易扩展

3.现代化手段----数据库

​ 持久化存储
​ 读写速度极高
​ 保证数据的有效性
​ 对程序支持性非常好，容易扩展

二、数据库

​ 数据库就是一种特殊的文件，其中存储着需要的数据

​ 关系型数据库核心元素：
​ 数据行(记录)
​ 数据列(字段)
​ 数据表(数据行的集合)
​ 数据库(数据表的集合)

三、mysql简介

​ MySQL是一个关系型数据库管理系统，关系型数据库是表组成的，用以用sql语句方便的在一个表以及多个表之间做非常复杂的数据查询。非关系型数据库是以键值的方式存储的。由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品

特点：

1. MySQL使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
2. 支持多种操作系统，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
3. 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
4. 支持多线程，充分利用CPU资源
5. mysql提供了优化的SQL查询算法，有效地提高查询速度
6. 提供多语言支持，常见的编码如GB2312、BIG5、UTF8
7. 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
8. 提供用于管理、检查、优化数据库操作的管理工具
9. mysql是大型的数据库。可以处理拥有上千万条记录的大型数据库并且支持多种存储引擎
10. MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择
11. MySQL使用标准的SQL数据语言形式
12. Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
13. 在线DDL更改功能

特点总结

​ 开源 免费 使用范围广,跨平台,提供了多种语言调用的 API，是学习互联网公司数据库的首选，目前市场上很多公司用的都是mysql

四、RDBMS

​ RDBMS：关系型数据库管理系统（Relational Database Management System）
​ 当前主要使用两种类型的数据库：关系型数据库、非关系型数据库
​ 所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

关系型数据库的主要产品：

1. oracle：在以前的大型项目中使用,银行,电信等项目
2. mysql：web时代使用最广泛的关系型数据库
   ms sql server：在微软的项目中使用
3. sqlite：轻量级数据库，主要应用在移动平台

RDBMS和数据库的关系

​

五、SQL

​ SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库

SQL语句主要分为：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

 对于web程序员来讲，重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它语言如TPL、DCL、CCL了解即可
  SQL 是一门特殊的语言,专门用来操作关系数据库、不区分大小写

六、数据完整性

​ 一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中

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

数据类型：

• 可以通过查看帮助文档查阅所有支持的数据类型

• 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间

• 常用数据类型如下：
  整数：int，bit
  小数：decimal
  字符串：varchar,char
  日期时间: date, time, datetime
  枚举类型(enum)

• 特别说明的类型如下：
  decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
  char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ’
  varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
  字符串text表示存储大文本，当字符大于4000时推荐使用
  对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

• 约束

  ​ 主键primary key：物理上存储的顺序
  ​ 非空not null：此字段不允许填写空值
  ​ 惟一unique：此字段的值不允许重复
  ​ 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
  ​ 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
  ​ 自增 auto_increment

  数值类型：

  类型	字节 大小	有符号范围(signed)	无符号范围(unsigned)
  TINYINT	1	-128 ~ 127	0 ~ 255
  SMALLINT	2	-32768 ~ 32767	0 ~ 65535
  MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
  INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
  BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

  字符串：

  类型	字节大小	示例
  CHAR	0-255	类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’
  VARCHAR	0-255	类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’
  TEXT	0-65535	大文本

  日期时间类型：

  类型	字节大小	示例
  DATE	4	‘2020-01-01’
  TIME	3	‘12:29:59’
  DATETIME	8	‘2020-01-01 12:29:59’
  YEAR	1	‘2017’
  TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

• 聚合函数
  总数 count
  最大值 max
  最小值 min
  平均值 avg
  求和 sum

• 命令行脚本

  命令行连接
  	打开终端，运行命令
  	mysql -uroot -p
  	回车后输入密码
  set password for root@localhost = password('123');	  修改MySQL密码
  show  databases;查看当前实例包含的多少数据库
  create database 1904A charset=utf8; 创建新的数据库
  drop database 1904A;  删除指定数据库
  use 1904A;    进入指定数据库
  show  tables；查找当前实例包含的表单
  create table +表名(name  varchar(255),age  tinyint,gender enum('男','女','保密') default '保密'); 创建表单（字段  类型  约束）
  desc  +表名；查询表单结构
  alter table 表名 modify 列名 想改的类型；修改字段类型
  drop table +表名；删除表
  rename table 旧名 to 新名；
  mysql退出三种方法：
  	mysql > quit;
  	mysql > exit;
  	mysql > \q;
  select version(); 查看mysql版本
  select now(); 查看当前时间
  insert into 表名 values('韩晨曦',16); 插入数据
  select * from 表名; 查看数据
  select gender from 表名 group by gender; 分组
  select * from 表名 limit start,count  分行查看数据
  select * from 表名 where 条件 limit (n-1)*m,m  分页
  

mysql-查询

一、MySQL查询

创建数据库
	create database 表名 charset=utf8;
使用数据库
	use 库名;
创建表
	create table 表名(字段   类型   约束);
向表中插入数据
	insert into 表名 values (字段对应的值);
	insert into 表名 values (字段对应的值),(字段对应的值);
查询所有字段
	select * from 表名;
查询指定字段
	select 列1,列2,... from 表名;
使用 as 给字段起别名
	select id as 序号, name as 名字, gender as 性别 from students;
可以通过 as 给表起别名
	select 新表名.列名 from 旧表名 as 新表名;
消除重复行
	select distinct 列1,... from 表名;
	在select后面列前使用distinct可以消除重复的行

二、条件

​ 使用where子句对表中的数据筛选
语法如下：

select * from 表名 where 条件;
例：
select * from students where id=1;

• where后面支持多种运算符，进行条件的处理

比较运算符
		等于: =
	查询没被删除的学生；
	select * from students where is_delete=0;
		大于: >
	查询编号大于3的学生；
	select * from students where id > 3;
		大于等于: >=
		小于: <
		小于等于: <=
	查询编号不大于4的学生；
	select * from students where id <= 4;
		不等于: != 或 <>
	查询姓名不是“黄蓉”的学生；
	select * from students where name != '黄蓉';
逻辑运算符
		and
	查询编号大于3的女同学；
	select * from students where id > 3 and gender=0;
		or
	查询编号小于4或没被删除的学生；
	select * from students where id < 4 or is_delete=0;
		not
模糊查询
		like
	查询姓黄的学生；
	select * from students where name like '黄%';
		%表示任意多个任意字符
	查询姓黄并且“名”是一个字的学生；
	select * from students where name like '黄_';
		_表示一个任意字符
	查询姓黄或叫靖的学生；
	select * from students where name like '黄%' or name like '%靖';
范围查询
		in
		表示在一个非连续的范围内
	查询编号是1或3或8的学生；
	select * from students where id in(1,3,8);
		between····and····
		表示在一个连续的范围内
	查询编号为3至8的学生;
	select * from students where id between 3 and 8;
	查询编号是3至8的男生;
	select * from students where (id between 3 and 8) and gender=1;
空判断
		判空is null
	查询没有填写身高的学生;
	select * from students where height is null;
		判非空is not null
	查询填写了身高的学生;
	select * from students where height is not null;
	查询填写了身高的男生;
	select * from students where height is not null and gender=1;
	注意：null与''是不同的

• 优先级
  优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
  and比or先运算，如果同时出现并希望先算or，需要结合()使用

三、排序

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

语法：
	select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
		例1：查询未删除男生信息，按学号降序;
	select * from students where gender=1 and is_delete=0 order by id desc;
		例2：查询未删除学生信息，按名称升序;
	select * from students where is_delete=0 order by name;
		例3：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序;
	select * from students  order by age desc,height desc;

说明：
将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列（asc）
asc从小到大排列，即升序
desc从大到小排序，即降序

四、聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

count 总数
​	count(*)表示计算总行数，括号中写星与列名，结果是相同的
​	查询学生总数；
​	select count(*) from students;
max 最大值
​	max(列)表示求此列的最大值
​	查询女生的编号最大值；
​	select max(id) from students where gender=2;
min 最小值
​	min(列)表示求此列的最小值
​	查询未删除的学生最小编号；
​	select min(id) from students where is_delete=0;
avg 平均值
​	avg(列)表示求此列的平均值
​	查询未删除女生的编号平均值；
​	select avg(id) from students where is_delete=0 and gender=2;
sum 求和
​	sum(列)表示求此列的和
​	查询男生的总年龄；
​	select sum(age) from students where gender=1;

-- 平均年龄；
	select sum(age)/count(*) from students where gender=1;

五、分组

1.group by
	group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
	group by可用于单个字段分组，也可用于多个字段分组
	语法：
		select 列名 from 表名 group by 列名;
	根据gender字段来分组，gender字段的全部值有4个'男','女','中性','保密'，所以分为了4组 当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大
2.group by + group_concat()
	group_concat(字段名)可以作为一个输出字段来使用，
	表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
	select gender,group_concat(列名) from 表名 group by gender;
3.group by + 集合函数
	通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
	select gender,avg(age) from students group by gender;
4.group by + having
	having 条件表达式：用来分组查询后指定一些条件来输出查询结果
	having作用和where一样，但having只能用于group by
	select gender,count(*) from students group by gender having count(*)>2;
5.group by + with rollup
	with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
	select gender,group_concat(age) from students group by gender with rollup;

六、分页

分行
	当数据量过大时，在一页中查看数据是一件非常麻烦的事情
	语法；
	select * from 表名 limit start,count
		查询前3行男生信息；
	select * from 表名 where gender=1 limit 0,3;
	说明；
	从start开始，获取count条数据
分页
	已知：每页显示m条数据，当前显示第n页
	求总页数：此段逻辑后面会在python中实现
	①查询总条数p1
	②使用p1除以m得到p2
	③如果整除则p2为总数页
	④如果不整除则p2+1为总页数
	求第n页的数据；
	select * from 表名 where is_delete=0 limit (n-1)*m,m

​ group by
​ group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
​ group by可用于单个字段分组，也可用于多个字段分组
​ 语法；
​ select 列名 from 表名 group by 列名;
​ 根据gender字段来分组，gender字段的全部值有4个’男’,‘女’,‘中性’,‘保密’，所以分为了4组 当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大
​ group by + group_concat()
​ group_concat(字段名)可以作为一个输出字段来使用，
​ 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
​ select gender,group_concat(列名) from 表名 group by gender;
​ group by + 集合函数
​ 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
​ select gender,avg(age) from students group by gender;
​ group by + having
​ having 条件表达式：用来分组查询后指定一些条件来输出查询结果
​ having作用和where一样，但having只能用于group by
​ select gender,count() from students group by gender having count()>2;
​ group by + with rollup
​ with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
​ select gender,group_concat(age) from students group by gender with rollup;

六、分页

​ 分行
​ 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
​ 语法；
​ select * from 表名 limit start,count
​ 查询前3行男生信息；
​ select * from 表名 where gender=1 limit 0,3;
​ 说明；
​ 从start开始，获取count条数据
​ 分页
​ 已知：每页显示m条数据，当前显示第n页
​ 求总页数：此段逻辑后面会在python中实现
​ ①查询总条数p1
​ ②使用p1除以m得到p2
​ ③如果整除则p2为总数页
​ ④如果不整除则p2+1为总页数
​ 求第n页的数据；
​ select * from 表名 where is_delete=0 limit (n-1)*m,m
​ mysql与python交互
​ mysql高级

er with rollup;

六、分页

​ 分行
​ 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
​ 语法；
​ select * from 表名 limit start,count
​ 查询前3行男生信息；
​ select * from 表名 where gender=1 limit 0,3;
​ 说明；
​ 从start开始，获取count条数据
​ 分页
​ 已知：每页显示m条数据，当前显示第n页
​ 求总页数：此段逻辑后面会在python中实现
​ ①查询总条数p1
​ ②使用p1除以m得到p2
​ ③如果整除则p2为总数页
​ ④如果不整除则p2+1为总页数
​ 求第n页的数据；
​ select * from 表名 where is_delete=0 limit (n-1)*m,m
​ mysql与python交互
​ mysql高级