周记录（第五周）

数据库

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库。专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方式对象数据进行管理更加的方便，快速，安全。

MySQL基础操作

SQL ( Structurequery language ) 结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

数据库语法的特点

1.SQL 语句可以换行, 要以分号结尾

2.命令不区分大小写. 关键字和函数建议用大写

3.如果提示符为 '> 那么需要输入一个'回车

4.命令打错了换行后不能修改, 可以用 \c 取消

数据库操作

查看数据库 show databases;

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

删除数据库 drop database 库名;

打开数据库 use 库名;

数据表操作

数据库管理系统中, 可以有很多库, 每个数据库中可以包括多张数据表

查看表: show tables;

创建表: create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8;

创建表: 如果表不存在,则创建, 如果存在就不执行这条命令

create table if not exists 表名(字段1 类型,字段2 类型);


create table if not exists users(

      id int not null primary key auto_increment,
      name varchar(4) not null,
      age tinyint,
      sex enum('男','女')
)engine=innodb default charset=utf8;
删除表: drop table 表名;

表结构: desc 表名;

查看建标语句：show create table users;

数据操作：增删改查

插入

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查询 

select * from 表名;

select 字段1,字段2,字段3 from 表名;

select * from 表名 where 字段=某个值;

修改

update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

删除 

delete from 表名 where 字段=某个值;

退出MySQL

exit; 或 quit;

MySQL的数据类型

1、字符串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电话号码、邮政编码等。

不管使用何种形式的串数据类型，串值都必须括在引号内。

有两种基本的串类型，分别为定长串和变长串。

定长串：char

1.   接受长度固定的字符串，其长度是在创建表时指定的。定长列不允许存储多于指定长度字符的数据。

2.    指定长度后，就会分配固定的存储空间用于存放数据。（eg. char(7) 不管实际插入多少字符，它都会占用7个字符位置。）

变长串 varchar

存储可变长度的字符串。eg.varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7个字符。

变长数据类型这样灵活，却还要使用定长数据类型是因为性能，MySQL处理定长列远比处理变长列快得多。

Text 变长文本类型存储

2、数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多。

与字符串不一样，数值不应该括在引号内。

有符号或无符号

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号

有符号数值列可以存储正或负的数值。

无符号数值列只能存储正数。

默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字。

3、日期和时间类型

MySQL使用专门的数据类型来存储日期和时间值。

4、二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

表的字段约束

unsigned 无符号。(给数值类型使用，表示为正数，不写可以表示正负数都可以)字段类型后面加括号限制宽度 。


char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度。

int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)。

int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。

not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错。

default 设置默认值。

primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。

auto_increment 定义列为自增属性，一般用于主键，数值会自动加1。

unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度。

MySQL的运算符

算术运算符： +、 -、 *、 /、 %。

比较运算符： =、 >、 <、 >=、 <=、!=。

数据库特有的比较： in、not in、is null、is not null、like、between、and。

逻辑运算符： and、or、not。

like: 支持特殊符号%和_ 。（其中%表示任意数量的任意字符，_表示任意一位字符。）

主键

1、表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值。

2、应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。

Ps:（1）记录一旦插入到表中，主键最好不要再修改。
   （2）不允许NULL
   （3)不在主键列中使用可能会更改的值。（例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。）		
    (4)自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键。
    (5)可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的。

MySQL数据库与数据表操作

数据库的操作

1.数据库的创建

链接mysql数据库后，进入mysql后可以操作数据。

1.创建库

create database if not exists chuange default charset=utf8mb4;

(1)数据库chuange如果不存在则创建数据库，存在则不创建。

(2)创建chuange 数据库，并设置字符集为utf8mb4。

(3)无特殊情况都要求字符集为utf8或者utf8mb4的字符编码。

 

2.查看所有库

show databases ;


3.打开库/进入库/选择库
use change


4.删除库

删除库，那么库中的所有数据都将在磁盘中删除。

drop database 库名

数据表的操作

1.创建表
语法格式:create table表名(字段名，类型，[字段约束] ，…);

eg.创建一个users 的表:

create table users(

#创建ID字段，为正整数，不允许为空主键，自动递增:

id int unsigned not null primary key auto_ increment ,

#创建存储名字的字段，为字符串类型，最大长度5个字符，不允许为空:

Username varchar(5) not null,

#创建存储密码的字段，固定长度32位字符，不 允许为空

password char(32) not null,

#创建年龄字段，不允许为空，默认值为20

age tinyint not null default 20

)engine=innodb default charset=ut f8mb4;

 

查看表结构:

desc users;


查看建表语句:

show create table users;



创建表的基本原则:

(1)表名和字段名尽可能的符合命名规范，并且最好能够见名之意。

(2)表中数据必须有唯一标示，即主键定义。无特殊情况，主键都为数字并自增即可。

(3)表中字段所对应的类型设置合理，并限制合理长度。

(4)表引擎推荐使用innodb， 并无特殊情况都要求为utf8或者utf8mb4的字符编码。

---

2.修改表结构

语法格式: alter table表名action (更改的选项)

添加字段

#在users表中追加一个num字段：
alter table users add num int not null;
#在指定字段后面追加字段在users 表中age字段后面添加一个email字段：
alter table users add email varchar(50) after age;
#在指定字段后面追加字段，在users 表中age字段后面添加一个phone：
alter table users add phone char(11) not nu1l after age;
#在表的最前面添加一个字段：
alter table users add aa int first;


删除字段

#删除字段alter table 表名drop 被删除的字段名
alter table users drop aa;


修改字段
语法格式: alter table 表名change | modify被修改的字段信息
change:可以修改字段名，
modify:不能修改字段名。

#修改表中的num字段类型，使用modify不修改字段名
alter table users modify num tinyint not null default 12;
#修改表中的num字段为int并且字段名为nn 
alter table users change num mm int ;

ps:注意:一般情况下，无特殊要求，不要轻易修改表结构

---

3.修改表名

语法结构: alter table原表名rename as新表名

---

4.更改表中的自增的值

在常规情况下， auto. _increment 默认从1开始继续递增:
alter table users auto_ increment = 1 000 ;

---

5.修改表引擎

#推荐在定义表时，表引擎为innodb。
#通过查看建表语句获取当前的表引擎
mysql> show create table users\G;
*************************** 1.row ***************************
Table: users
Create Table: CREATE TABLE'users’ (
  PRIMARY KEY ( id')
) ENGINE=InnoDB AUTO_ INCREMENT=1001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
#直接查看当前表状态信息
mysql> show table status f rom chuange where name =‘users'\G;
*************************** 1. row **** *** ******* **** *** ******
Name: users
Engine: InnoDB

#修改表引擎语句 
alter table users engine =' myisam';

---

6.删除表
语法结构:drop table 表名

Mysql数据库表引擎与字符集

1.字符集
如何存储字符串：建立字符与二进制数据的映射关系。

（1）也就是界定清楚字符范围。

（2）将一个字符映射成一个二进制数据的过程也叫编码，将一个二进制数据映射到一个字符的过程叫解码。

人们抽象出字符集的概念来描述某个字符范围的编码规则。

一些常用字符集的情况：

ASCII字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式：

eg.'L' ->'M'->01001100 (十六进制:0x4C，十进制:76)

ISO 8859-1字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。

GB2312字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：如果该字符在ASCII字符集中，则采用1字节编码;否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。比方说字符串'爱u'，其中'爱'需要用2个字节进行编码，编码后的十六进制表示为0xCED2，'u'需要用1个字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是0xCED275。


如何区分某个字节代表一个单独的字符还是代表某个字符的一部分：如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

GBK字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

utf8字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：

'L'->'M'->01001100 (十六进制:0x4C)

'啊'->111001011001010110001010 (十六进制: 0xE5958A)


对于同一个字符，不同字符集也可能有不同的编码方式。比如对于汉字'我'来说，ASCII字符集中根本没有收录这个字符，utf8和gb2312字符集对汉字我的编码方式如下：

utf8编码:111001101000100010010001 (3个字节，十六进制表示是: 0xE68891)

gb2312编码: 1100111011010010 (2个字节，十六进制表示是: 0xCED2)

2.MySQL中的utf8和utf8mb4

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL的大叔偷偷的定义了两个概念：

utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。

utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

ps:在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

MySQL支持好多好多种字符集，查看当前MySQL中支持的字符集可以用:show charset;

MySQL 数据操作 DML

数据的DML操作：添加数据，修改数据，删除数据。

添加数据
格式： insert into 表名[(字段列表)] values(值列表…);

标准添加（指定所有字段，给定所有的值）
mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');
Query OK, 1 row affected (0.13 sec)
​

指定部分字段添加值
mysql> insert into stu(name,classid) value('lisi','lamp138');
Query OK, 1 row affected (0.11 sec)
​
不指定字段添加值
mysql> insert into stu value(null,'wangwu',21,'w','lamp138');

​
批量添加值
mysql> insert into stu values
    -> (null,'zhaoliu',25,'w','lamp94'),
    -> (null,'uu01',26,'m','lamp94'),
    -> (null,'uu02',28,'w','lamp92'),
    -> (null,'qq02',24,'m','lamp92'),
    -> (null,'uu03',32,'m','lamp138'),
    -> (null,'qq03',23,'w','lamp94'),
    -> (null,'aa',19,'m','lamp138');

修改数据
格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n… where 条件

将id为11的age改为35，sex改为m值
mysql> update stu set age=35,sex='m' where id=11;
​
将id值为12和14的数据值sex改为m，classid改为lamp92
mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面
mysql> update stu set sex='m',classid='lamp92' where id in(12,14);

删除数据
格式：delete from 表名 [where 条件]

删除stu表中id值为100的数据

mysql> delete from stu where id=100;

Query OK, 0 rows affected (0.00 sec)

 

删除stu表中id值为20到30的数据

mysql> delete from stu where id>=20 and id<=30;

Query OK, 0 rows affected (0.00 sec)

 

删除stu表中id值为20到30的数据（等级于上面写法）

mysql> delete from stu where id between 20 and 30;

Query OK, 0 rows affected (0.00 sec)

 

删除stu表中id值大于200的数据

mysql> delete from stu where id>200;

Query OK, 0 rows affected (0.00 sec)

DQL-MySQL数据查询SQL

select 字段列表|*  from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]

基础查询

# 查询表中所有列 所有数据
select * from users;

# 指定字段列表进行查询
select id,name,phone from users;

Where 条件查询

可以在where子句中指定任何条件。

可以使用 and 或者 or 指定一个或多个条件。

where条件也可以运用在update和delete语句的后面。

where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤。


eg.查询users表中 age > 22的数据

select * from users where age > 22;


查询 users 表中 name=某个条件值 的数据

select * from users where name = '王五';


查询 users 表中 年龄在22到25之间的数据

select * from users where age >= 22 and age <= 25;

select * from users where age between 22 and 25;

 
查询 users 表中 年龄不在22到25之间的数据

select * from users where age < 22 or age > 25;

select * from users where age not between 22 and 25;


查询 users 表中 年龄在22到25之间的女生信息

select * from users where age >= 22 and age <= 25 and sex = '女';

Like 子句
当想查询某个字段是否包含时，可以使用like语句进行某个字段的模糊搜索，例如： 查询 name字段中包含五的数据：
select * from users where name like ‘王五’;

使用 % 模糊搜索。
%代表任意个任意字符  查询name字段中包含五的。

 select * from users where name like '%五%'：
 查询name字段中最后一个字符为五的  
 select * from users where name like '%五'：   
 查询name字段中第一个字符为王的 ：
 select * from users where name like '王%'：  

使用 _ 单个的下划线。表示一个任意字符，使用和%类似。

查询表中 name 字段为两个字符的数据： 
 select * from users where name like '__';   
 查询 name 字段最后为五，的两个字符的数据 ： 
 select * from users where name like '_五';
			
ps.where子句中的like在使用%或者_进行模糊搜索时，效率不高，因此：
（1）尽可能的不去使用%或者_如果需要使用，
（2）尽可能不要把通配符放在开头处			 

聚合函数(Mysql中的统计函数)

max(),min(),count(),sum(),avg() 

eg. 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄:

select max(age),min(age),sum(age),avg(age) from users;

统计 users 表中的数据量

select count(*) from users;

select count(id) from users;

其中，count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算；count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计。但是，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计。

Group BY 分组

group by 语句根据一个或多个列对结果集进行分组。一般情况下，是用与数据的统计或计算，配合聚合函数使用。

统计 users 表中 男女生人数：

很明显按照上面的需要，可以写出两个语句进行分别统计：

select count(*) from users where sex = '女';

select count(*) from users where sex = '男';


可以使用分组进行统计，更方便

select sex,count(*) from users group by sex;

 
统计1班和2班的人数

select classid,count(*) from users group by classid;
	 

分别统计每个班级的男女生人数

select classid,sex,count(*) as num from users group by classid,sex;


ps: 注意，在使用。group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后面。

Having 子句

	having时在分组聚合计算后，对结果再一次进行过滤，类似于where，where过滤的是行数据，having过滤的是分组数据。


要统计班级人数

select classid,count(*) from users group by classid;


统计班级人数，并且要人数达到5人及以上

select classid,count(*) as num from users group by classid having num >=5;

Order by 排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序。

Asc 升序，默认

desc降序


按照年龄对结果进行排序，从大到小：

select * from users order by age desc;
	 

从小到大排序 asc 默认就是。可以不写：

select * from users order by age;


也可以按照多个字段进行排序：

select * from users order by age,id; # 先按照age进行排序，age相同情况下，按照id进行排序

select * from users order by age,id desc;

Limit 数据分页

limit n 提取n条数据，

limit m,n 跳过m跳数据，提取n条数据。


查询users表中的数据，只要3条:

select * from users limit 3;


跳过前4条数据，再取3条数据：

select * from users limit 4,3;


limit一般应用在数据分页上面

例如每页显示10条数据，第三页的limit：

第一页  limit 0,10

第二页  limit 10,10

第三页 limit 20,10

第四页 limit 30,10


user表中 年龄最大的三个用户数据：

select * from users order by age desc limit 3;

Mysql数据库导入导出和授权

数据导出

数据库数据导出

不进入mysql，然后输入以下命令 导出某个库中的数据：
    ysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql

导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件，之后可以用这个sql文件到别的库，或着本机中创建或回复这些数据。

将数据库中的表导出

不要进入mysql，然后输入以下命令 导出某个库中指定的表的数据:
    mysqldump -u root -p tlxy tts >  ~/Desktop/code/tlxy-tts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

在新的数据库中 导入备份的数据，导入导出的sql文件：

mysql-u  root -p ops < ./tlxy.sql

 
把导出的表sql 导入数据库：

mysql-u  root -p ops < ./tlxy-tts.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，不要用在项目中！

可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作

这样就一定程度上保证了数据库的安全。

创建用户的语法格式：

grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by  ‘密码’


eg. 在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表进行添加和查询的权限:

grant select,insert on tlxy.* to zhangsan@'%' identified by '123456';


用户lisi。密码 123456 可以对tlxy库中的所有表有所有操作权限:

grant all on tlxy.* to lisi@'%' identified by '123456';


删除用户:

drop user 'lisi'@'%';

python操作mysql

python操作mysql的基本步骤

1.   链接mysql数据库

2.   创建游标对象

3.   准备sql

4.   用游标对象执行sql

5.   用游标对象提取结果

6.   关闭数据库连接

eg:

import pymysql



# 1. 链接mysql数据库

db =pymysql.connect('localhost','root','','tlxy',charset='utf8mb4',

                   cursorclass=pymysql.cursors.DictCursor

                   )


# 2.创建游标对象

cursor = db.cursor()
 

# 3.准备sql语句

sql = 'select version()'
 

# 4.执行sql语句

cursor.execute(sql)


# 5. 提取结果.
fetchall() 提取所有的结果。fetchone() 提取一条结果

data = cursor.fetchone()

 
# 6. 关闭数据库连接

db.close()

print(data)

web版的在线留言板

1.先实现web的基本搭建 flask框架实现

2.创建留言板数据库

3.完成留言板的逻辑代码

flask框架实现web的基本搭建

(1)安装flask框架: pip install flask

(2)使用flask框架搭建web:

from flask import Flask

app = Flask(__name__)

@app.route("/")

def hello():

    return "Hello, World!"

@app.route('/love')

def love():

    return 'iloveyoutosimida'


if __name__ == '__main__':

    app.run(debug=True,host='127.0.0.1',port='8080')

留言板数据库的设计与创建

id nikename 昵称     info 留言信息       datetime 留言时间

1      张三       我想要大家帮我抢个票   2020-1-1 12：12：12


创建库：
create database tlxy charset=utf8mb4;


创建表：
create table lyb(
    id int unsigned not null auto_increment primary key,
    nikename varchar(6) not null,
    info     text     not null,
    date   datetime not null	
)engine=innodb default charset=utf8mb4;

 

添加测试数据：
insert into lyb values(null,'张','我想要大家帮我抢个票','2020-01-01 11:11:11');