Python Web（八）Mysql数据库的基本使用

数据库

-数据库的介绍
    数据库就是存储和管理数据的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增加、修改、删除、查询等操作。

-数据库的分类
    关系型数据库
    非关系型数据库
-关系型数据库:
    是指采用了关系模型来组织数据的数据库，简单来说，关系模型指的就是二维表格模型，好比Excel文件中的表格，强调使用表格的方式存储数据。
-关系型数据库中核心元素
    数据行
    数据列
    数据表
    数据库(数据表的集合)
-常用的关系型数据库:
    Oracle
    Microsoft SQL Server
    MySQL
    SQLite

-非关系型数据库:
    非关系型数据库，又被称为NoSQL（Not Only SQL )，意为不仅仅是SQL，
    对NoSQL 最普遍的定义是“非关联型的”，强调 Key-Value 的方式存储数据。
-常用的非关系型数据库:
    MongoDB
    Redis
提示: 目前我们先学习一下关系型数据库，而非关系型数据库会在后面课程中进行学习，大家先知道一下即可。

-数据库的作用
    数据库的作用就是存储和管理数据的，比如: 我们在京东网站上的浏览的商品列表数据，这些数据都会存储在数据库。

-数据库的特点
    持久化存储
    读写速度极高
    保证数据的有效性

关系型数据库管理系统

-关系型数据库管理系统的介绍
    数据库管理系统（英语全拼：Relational Database Management System，简称RDBMS）
    是为管理关系型数据库而设计的软件系统，如果大家想要使用关系型数据库就需要安装数据库管理系统，其实就是一个应用软件。

-关系型数据库管理系统可以分为:
    关系型数据库服务端软件
    关系型数据库客户端软件
-关系型数据库服务端软件:
    主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，
    数据文件是用来存储数据的, 其实数据库就是一系列数据文件的集合。

-关系型数据库客户端软件:
主要负责和关系型数据库服务端软件进行通信, 向服务端传输数据或者从服务端获取数据.

-说明:
    用户操作关系型数据库客户端，实现数据库相关操作。
    关系数据库客户端借助网络使用SQL语言和关系型数据库服务端进行数据通信
    关系型数据库服务端管理着不同的数据库，每个数据库会有一系列的数据文件，
    数据都保存在数据文件里面，每个数据库可以理解成是一个文件夹。

-SQL的介绍
    SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS的数据库的语言。
    也就是说通过 SQL 可以操作 oracle,sql server,mysql,sqlite 等关系型的数据库。
    SQL的作用是实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁。

-SQL语言主要分为：
    DQL：数据查询语言，用于对数据进行查询，如select
    DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
    TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
    DCL：数据控制语言，进行授权与权限回收，如grant、revoke
    DDL：数据定义语言，进行数据库、表的管理等，如create、drop
-说明:
    对于程序员来讲，重点是数据的增、删、改、查，必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它操作如TPL、DCL了解即可.
    SQL语言不区分大小写

Mysql数据库

1. MySQL数据库的介绍
    MySQL是一个关系型数据库管理系统，在 WEB 应用方面，
    MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件，
    它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。

-MySQL的特点:
    MySQL是开源的，所以你不需要支付额外的费用。
    MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
    MySQL使用标准的SQL数据语言形式。
    MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口，
    这些编程语言包括C、C++、Python、Java、Ruby等等。
-MySQL数据库的安装
    MySQL数据库服务端软件的安装
    MySQL数据库客户端软件的安装
-MySQL数据库服务端软件的安装:
在Ubuntu中打开终端，输入下面的命令:

sudo apt-get install mysql-server

-查看Mysql服务

ps -aux | grep mysql

-ps说明
    ps 查看当前系统中的进程
    -a 表示所有用户
    -u 表示显示用户名
    -x 表示显示所有的执行程序
-查看MySQL服务状态:

sudo service mysql status

-停止MySQL服务:

sudo service mysql stop

-启动MySQL服务:

sudo service mysql start

-重启MySQL服务:

sudo service mysql restart

-MySQL配置文件的介绍:
    配置文件路径为: /etc/mysql/mysql.conf.d/mysqld.cnf

-主要配置信息说明:
    port表示端口号，默认为3306
    bind-address表示服务器绑定的ip，默认为127.0.0.1
    datadir表示数据库保存路径，默认为/var/lib/mysql
    log_error表示错误日志，默认为/var/log/mysql/error.log
-MySQL数据库客户端软件的安装:
    客户端是程序员或者dba使用的软件，通过socket方式与服务端程序通信。
    常用的MySQL数据库客户端软件有:
        图形化界面客户端Navicat
        命令行客户端mysql
-图形化界面客户端Navicat的使用
    可以到Navicat官网下载(https://www.navicat.com.cn/download/navicat-for-mysql)
    将压缩文件拷贝到Ubuntu虚拟机中，放到桌面上，解压
-Navicat的使用说明:

tar zxvf navicat112_mysql_cs_x64.tar.gz
./start_navicat

-命令行客户端mysql的安装
    在Ubuntu中打开终端，输入下面的命令:

sudo apt-get install mysql-client

-mysql命令的使用帮助:

mysql --help

-MySQL客户端的使用:
    MySQL客户端连接MySQL服务端命令:

mysql -uroot -p

-说明:
    -u: 表示MySQL服务端的用户名
    -p: 表示MySQL服务端的密码
    quit 或者 exit 或者 ctr + d 表示退出

数据类型和约束

-数据类型
    数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，
    使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

-常用数据类型如下:
    整数：int，bit
    小数：decimal
    字符串：varchar,char
    日期时间: date, time, datetime
    枚举类型(enum)
-数据类型说明:
    decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
    char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数
    varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数
    对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
    字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.
-数据约束
    约束是指数据在数据类型限定的基础上额外增加的要求.
-常见的约束如下:
    主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
    非空 not null: 此字段不允许填写空值.
    惟一 unique: 此字段的值不允许重复.
    默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
    外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 
        会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

数据类型附录表

整数类型

类型	字节大小	有符号范围(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	固定长度，小型数据	身份证号、手机号、电话、密码
VARCHAR	可变长度，小型数据	姓名、地址、品牌、型号
TEXT	可变长度，字符个数大于 4000	存储小型文章或者新闻
LONGTEXT	可变长度， 极大型文本数据	存储极大型文本数据

时间类型

类型	字节大小	示例
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

小结

常用的数据类型:

• 整数：int，bit

• 小数：decimal

• 字符串：varchar,char

• 日期时间: date, time, datetime

• 枚举类型(enum)

常见的约束:

• 主键约束 primary key

• 非空约束 not null

• 惟一约束 unique

• 默认约束 default

• 外键约束 foreign key

数据类型和约束保证了表中数据的准确性和完整性

命令行客户端Mysql的使用

-登录数据库
输入下面命令:

mysql -uroot -p

说明:
-u 后面是登录的用户名
-p 后面是登录密码, 如果不填写, 回车之后会提示输入密码
-登录成功后, 输入如下命令查看效果：
-显示当前时间

select now();

-登出(退出)数据库:
    quit 或 exit 或 ctrl + d
-数据库操作的SQL语句
查看所有数据库

show databases;

创建数据库
    create database 数据库名 charset=utf8;
例：

create database python charset=utf8;

使用数据库
    use 数据库名;
例：

use python;

查看当前使用的数据库

select database();

删除数据库-慎重
drop database 数据库名;
例：

drop database polls;

-表结构操作的SQL语句
查看当前数据库中所有表

show tables;

创建表

create table students(
 id int unsigned primary key auto_increment not null,
 name varchar(20) not null,
 age tinyint unsigned default 0,
 height decimal(5,2),
 gender enum('男','女','人妖','保密')
);

说明:
 create table 表名(
 字段名称 数据类型  可选的约束条件,
 column1 datatype contrai,
 ...
 );
修改表-添加字段
    alter table 表名 add 列名 类型 约束;
例：

alter table students add birthday datetime;

修改表-修改字段类型
    alter table 表名 modify 列名 类型 约束;
例：

alter table students modify birthday date not null;

说明:
    modify: 只能修改字段类型或者约束，不能修改字段名
修改表-修改字段名和字段类型
    alter table 表名 change 原名 新名 类型及约束;
例：

alter table students change birthday birth datetime not null;

说明:
    change: 既能对字段重命名又能修改字段类型还能修改约束
修改表-删除字段
    alter table 表名 drop 列名;
例：

alter table students drop birthday;

查看创表SQL语句
     show create table 表名;
例：

show create table students;

查看创库SQL语句
    show create database 数据库名;
例：

create database mytest charset=utf8;
show create database mytest;

删除表
    drop table 表名;
例：

drop table students;

-表数据操作的SQL语句
查询数据
查询所有列
    select * from 表名;
例：

select * from students;

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

select id,name from students;

添加数据
全列插入：值的顺序与表结构字段的顺序完全一一对应
    insert into 表名 values (...)
例:

insert into students values(0, 'xx', default, default, '男');

部分列插入：值的顺序与给出的列顺序对应
    insert into 表名 (列1,...) values(值1,...)
例:

insert into students(name, age) values('王二小', 15);

全列多行插入
    insert into 表名 values(...),(...)...;
例:

insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');

部分列多行插入
    insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：

insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);

说明:
    主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
    在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值
修改数据
   update 表名 set 列1=值1,列2=值2... where 条件
例：

update students set age = 18, gender = '女' where id = 6;

删除数据
   delete from 表名 where 条件
例：

delete from students where id=5;

问题:
    上面的操作称之为物理删除，一旦删除就不容易恢复，我们可以使用逻辑删除的方式来解决这个问题。

添加删除表示字段，0表示未删除 1表示删除

alter table students add isdelete bit default 0;

逻辑删除数据

update students set isdelete = 1 where id = 8;

说明:
逻辑删除，本质就是修改操作

as和distinct关键字

-as关键字
    在使用SQL语句显示结果的时候，往往在屏幕显示的字段名并不具备良好的可读性，
    此时可以使用 as 给字段起一个别名。
-使用 as 给字段起别名

select id as 序号, name as 名字, gender as 性别 from students;

-可以通过 as 给表起别名
如果是单表查询 可以省略表名

select id, name, gender from students;

表名.字段名

select students.id,students.name,students.gender from students;

可以通过 as 给表起别名

select s.id,s.name,s.gender from students as s;

-distinct关键字
-distinct可以去除重复数据行。
    select distinct 列1,... from 表名;
例： 查询班级中学生的性别

select name, gender from students;

看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct

select distinct name, gender from students;

where条件查询

-where条件查询的介绍
    使用where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。
-where语句支持的运算符:
    比较运算符
    逻辑运算符
    模糊查询
    范围查询
    空判断
-where条件查询语法格式如下:
    select * from 表名 where 条件;
例：

select * from students where id = 1;

-比较运算符查询
    等于: =
    大于: >
    大于等于: >=
    小于: <
    小于等于: <=
    不等于: != 或 <>
例1：查询编号大于3的学生:

select * from students where id > 3;

例2：查询编号不大于4的学生:

select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生:

select * from students where name != '黄蓉';

例4：查询没被删除的学生:

select * from students where is_delete=0;

-逻辑运算符查询
    and
    or
    not
例1：查询编号大于3的女同学:

select * from students where id > 3 and gender=0;

例2：查询编号小于4或没被删除的学生:

select * from students where id < 4 or is_delete=0;

例3：查询年龄不在10岁到15岁之间的学生:

select * from students where not (age >= 10 and age <= 15);

说明: 多个条件判断想要作为一个整体，可以结合‘()’。

-模糊查询
    like是模糊查询关键字
    %表示任意多个任意字符
    _表示一个任意字符
例1：查询姓黄的学生:

select * from students where name like '黄%';

例2：查询姓黄并且“名”是一个字的学生:

select * from students where name like '黄_';

例3：查询姓黄或叫靖的学生:

select * from students where name like '黄%' or name like '%靖';

-范围查询
    between .. and .. 表示在一个连续的范围内查询
    in 表示在一个非连续的范围内查询
例1：查询编号为3至8的学生:

select * from students where id between 3 and 8;

例2：查询编号不是3至8的男生:

select * from students where (not id between 3 and 8) and gender='男';

-空判断查询
    判断为空使用: is null
    判断非空使用: is not null
例1：查询没有填写身高的学生:

select * from students where height is null;

注意:
    不能使用 where height = null 判断为空
    不能使用 where height != null 判断非空
    null 不等于 '' 空字符串

排序

-排序查询语法：
    select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
-语法说明:
    先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
    asc从小到大排列，即升序
    desc从大到小排序，即降序
    默认按照列值从小到大排列（即asc关键字）
例1：查询未删除男生信息，按学号降序:

select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序:

select * from students  order by age desc,height desc;

分页查询

-分页查询的介绍
    当我们在京东购物，浏览商品列表的时候，由于数据特别多，一页显示不完，
    一页一页的进行显示，这就是分页查询

-分页查询的语法
    select * from 表名 limit start,count
说明:
    limit是分页查询关键字
    start表示开始行索引，默认是0
    count表示查询条数
例1：查询前3行男生信息:

select * from students where gender=1 limit 0,3;

简写

select * from students where gender=1 limit 3;

-分页查询案例
    已知每页显示m条数据，求第n页显示的数据
    提示: 关键是求每页的开始行索引
    查询学生表，获取第n页数据的SQL语句:

select * from students limit (n-1)*m,m