【038】Python全栈日记-MySQL（一）

本次开始数据库的内容，主要以MySQL为主。

 

一、MySQL简介

1、MySQL历史：

合伙人David Axmark, Allan Larsson 和 Michael Widenius

“My”是 合伙奠基人Michael Widenius的女儿的名字

“SQL”是结构化查询语言的缩写

最流行的开源关系型数据库系统

由瑞典公司MySQL AB公司开发

1995年5月23日发行了第一个版本

美国SUN公司于2008年收购MYSQL

美国ORACLE公司于2010年收购SUN公司

当前MYSQL属于ORACLE 公司

 

所以不得不说oracle是真的牛逼，java、MySQL还有他自己的Oracle都是oracle公司的.，基本垄断。

 

2、MySQL-最流行的开源数据库

全球排名前 10 的 Web 站点中有 9 个使用该数据库

LAMP(linux,apache,mysql,perl/php/python)中的 “M”

社交网站、开源论坛、开源内容管理系统（wordpress,joomla,drupal)广泛使用

超过400家的著名企业使用MYSQL

 

3、多种编程语言提供MySQL支持

C、C++、JAVA (JDBC)、.NET (ODBC)、PHP、PYTHON、PERL

 

4、MySQL支持的操作系统、

WINDOWS (32位，64位）

LINUX (32位，64位）

MacOS (32位，64位）

 

 

5、MySQL发行版本

社区版 （无需注册免费下载）

MySQL Community Edition (GPL)

MySQL Cluster (GPL)

 

企业版 （可以注册后免费下载，商业收费）

MySQL Enterprise Edition

MySQL Cluster CGE

虽然现在已经推出8.0，但是还是推荐使用5.7

 

 

6、MySQL常用Web连接

官网主页（http://www.mysql.com）

下载（https://www.mysql.com/downloads/）

文档（https://dev.mysql.com/doc/）

论坛（https://forums.mysql.com/）

示例数据库（https://dev.mysql.com/doc/index-other.html）

 

 

 

二、安装MySQL

正常情况下是在linux下使用MySQL，但是由于先学习MySQL后学习linux所以先学习在windows下的安装与使用。

Linux与winsdow安装MySQL的区别在于，图形化点击的一些事情被替代为敲代码，当然MySQL也为我们提供了傻瓜安装包，但是为了达到装逼的目的，我们使用官网的zip包。

 

1、下载

官网选择最后的MySQL Community Edition (GPL)，然后选择第一个MySQL Community Server (GPL)，然后点击Looking for previous GA versions?选择windows的5.7版本。

然后有以下几个版本：

 

 

 

2、配置环境变量

下载完后解压，把文件夹放在c盘根目录（其他盘也行，我喜欢把环境配在c），然后配置环境变量，

将c:\mysql\bin目录添加到PATH变量中,为了方便

我的电脑->属性->高级系统设置->环境变量->新建系统环境变量->c:\mysql\bin;

然后通过cmd（win+R输入cmd）查看变量是否配置成功。

 

 

3、建立MySQL服务、并启动服务

添加MySQL服务

如果win10以管理员身份运行cmd（或者powershell）（win键 + x）

如果win7，win键 + r ，cmd（win10也行的）

输入：mysqld install MySQL57（因为我们已经配置好了环境变量，所以可以直接使用mysqld 后面的MySQL57是你起的服务名，随便起）

 

安装成功，显示如下

我们也可以删除：（别忘了安回来…………）

既然我们建立了服务，我们还需要启动这个服务：

1、win+R：输入services.msc

2、cmd输入services.msc

找到我们创建的名为mysql57的服务，启动它。

当然也可以通过cmd命令来启动服务net start mysql57

启动后，你的电脑就会有一部分内存被划为数据库内存，里面存储了你将要创建的数据。

你打开我们c盘存mysql环境的文件，会发现启动服务后多了一个文件夹叫data，data就是启动的数据库

 

4、进入数据库

既然我们已经在内存中开辟了一块，那我们就要进去瞅瞅。

（1）使用mysql客户端连接MySQL服务并修改密码

mysql –uroot –p （-p是密码的意思，你可以直接把密码加在p后面，但是不安全）

我们没有设置密码，所以直接回车就ok，如果有密码，你输入的时候也不会显示，输完回车就行了。

进去后就从cmd的c:\windows\system32>变为mysql>

 

（2）修改密码

mysql>alter user root@localhost identified by ‘123456’; （123456是密码，随便设）

 

记住命令结尾要加；，要不谁知道你写没写完，如果不加；会出现继续输入的箭头，这时候按ctrl＋c或者输入\c就行了

 

 

（3）退出重新连接

退出：mysql>quit; 或者 mysql>exit

重连：mysql –uroot –p

 

 

三、什么是数据库

简单的说，数据库（英文Dtabase）就是一个存放数据的仓库，这个仓库是按照一定的数据结果（数据结构是指数据的组织形式或数据之间的联系）来组织、存储的、我们可以通过数据库提供的多种方法来管理数据库里的数据更简单的形象理解，数据库和我们生活中存放杂物的仓库性质一样，区别只是存放的东西不同。

 

我们在vue中学习的文章列表的例子，书名作者时间这些信息就是存储在数据库中的，那是如何存进去的呢？是作为一个一个二维表单存在里面的。

例如淘宝：购买人就是一个表单，下单时间又是一个表单，进货商、商品信息等等都是一个一个的表单存在其中，

这时候就可以更好的写明白web前端、python\php\java、数据库中间的关系了，下图是老师上课给我们画的。

我们所学的java、python、php等等只是用来把数据库内容提供给前端的一个工具，而我们使用语言对数据库进行操作时，需要通过SQL来操作数据库，而不是直接用python对数据库进行修改。

 

 

 

 

四、MySQL常用管理命令

大部分shell操作中箭头上都是查询之前的代码，tab都是补全。

 

1、客户端连接MySQL服务

mysql –uroot –p 回车，录入mysql的root用户密码

 

-u代表mysql的用户名，可以不加空格之间填写连接用户-uroot

-p代表用户名对应的密码,可以直接在-p后面写密码，但是旁边有人能看到

(mysql -uroot -p1234)

-p后面可以不写密码，直接回车后会提醒录入密码，这样比较安全

 

退出连接

mysql>quit;  或者 exit;

我们同样可以访问他人建立的数据库：

Mysql -uroot -p -h 10.10.16.207 : 

（-h：--host）

后面的ip为对方的ip，当然除了你自己也不会有人让你直接访问他的root用户的，防止你删库跑路。

 

 

当我们使用的方法带有括号，就说明这是人家内部写好的函数，你调用就能实现相应功能。

2、 查看MySQL服务器版本

select version();

 

3、查看当前连接用户

Select user();

 

 

4、查看当前服务器日期和时间

select now();

这个很重要，例如有些游戏能够通过修改电脑或者手机的时间达到快速完成任务的效果，这就是因为使用的是本地时间、而不是数据库时间。

例如你在淘宝整点买东西，不可能你把手机时间调提前了了就可以提前买吧。

 

5、当前用户可以访问的数据库

show databases;

其中mysql是整个库的核心文件

Informati_schema:是存储信息用的库。

Performa_schema：是正在运行的库。

Sys：是在5.7中新加的库，有了这个库我们就可以对root用户的用户名进行修改，而不局限于只使用root，大大的增加了安全性。

 

6、查看“mysql”数据库的表

show tables from mysql;

我们也可以查看sys、da1中的表

show tables from da1;

 

之前说了，数据库中存放的是一对二维表，这个指令就是用来查看库中的表的。

Da1是我自己创建的，所以里面就一个表。

 

 

同样我们可以不在命令中使用from。我们直接进入到da1或者mysql库中：

use mysql;  移动到名为mysql的库中，相当于cd xxx，转到xxx文件下。

然后使用：

show tables;

来展示当前库中的表：

 

 

7、查看当前使用的数据库

select database();

有时候东西一多都不知道自己在哪个库中了，就要用这个来查了。

 

 

8、查看mysql库的user表的列（表头）

desc mysql.user;

这个意思就是查看mysql库中名字为user的表头信息。

当我要查看da1库中t表的表头也就是列：

desc da1.t

如果我们本来就在da1中，就可直接使用：

desc t;

 

 

9、查看mysql库的user表的行（详细内容）

select * from mysql.user;

这个效果不明显。我们用da1库看看，查看表的列和行有啥区别：

查看表的列：desc da1.t （显示表头信息）

查看表的行select * from da1.t;（详细信息）

 

当东西特别多的时候，例如查看mysql库的user表的行：

user表的列太多，导致折行，把列转成行来看，这样清晰一些

select * from mysql.user\G

 

 

 

10、查看数据库、表、列存放位置

show variables like 'datadir';查看数据库位置

 

select user,host,authentication_string from mysql.user;  查看用户，主机、密码

后边那一串就是密码，已经转为了正常解析不出来的格式。

 

 

11、创建数据库

create database test; 

test是数据库名字

show databases;

 

 

12、删除数据库

drop database test;

目录会被删除

如果里面有表文件也会被删除

千万不要删除mysql,sys,performance_schema等这些系统数据库

如果删除了，mysql服务器程序崩溃，切记危险！

 

 

 

13、创建中文数据库

MySQL默认编码拉丁文，不支持中文，所以我们如果数据库中要存储中文，就需要在创建数据库时进行转码。

create database test character set 'utf8' collate 'utf8_general_ci';

创建名为 “test”的数据库，使用utf8编码可以存储中文；

 

如果我们已经创建了数据库，当时忘记修改编码格式，应该如何修改呢？

alter database xyz character set utf8 collate utf8_general_ci;

 

还有一种方法就是在mysql根目录创建my.ini或者my.cnf文件

my.cnf中粘贴默认选项

[mysql]

default-character-set=gbk

这个就是设置默认的语言，需要重启一下服务

 

 

 

 

 

 

五、SQL语言基础

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

 

SQL 和 MySql 同属于关系型数据库管理系统,是力求功能上的统一,所以它们有相同的基本功能.

 

特别适用于处理存放在数据库的数据表中存在一定关联关系的结构化数据

 

 

1、语句分类

DDL (Data Definition Language)

数据定义语句

创建数据库、创建二维表、修改二维表等。例如：create, alter, drop, truncate, rename。一般要先用DDL（create）创建二维表的列，才能使用后续其它类别的语句

 

DML (DATA MANIPULATION LANGUAGE)

数据操纵语句

例如：insert，向已建二维表中插入行记录，update，修改行记录，delete删除行记录，应用非常广泛，银行刷卡，打电话等交易型数据均以DML语句的形式存在

 

DQL (Data Query Language)

通常当成DML

数据查询语句

SELECT语句，从数据库的二维表中查找数据。应用非常广泛，但前提是先用DML（insert）语句，向已建二维表中插入行记录，才能用select查找

 

DCL (DATA CONTROL LANGUAGE)

数据控制语句

例如：grant, revoke，一般数据库为多用户系统，不同用户的权限不一样，可以访问的数据不一样，可以用DCL语句为给用户赋予或收回数据库的各类权限

 

TCL (TRANSACTION CONTROL LANGUAGE)

事务控制语句

例如：commit; rollback; savepoint，只有支持事务的数据库管理系统或存储引擎TCL语句才能有效。

 

 

2、MySQL 数据类型

四种主要数据类型

（1）数值

例如：身高、体重、工资、销量、房屋面积等数据，适用于数值类型存储

【1】整数

int(N),

如：no int(4)，括号中的数字4为显示宽度和取值范围无关，如果学号为1，显示该学号时前面会补充3个空格，如果学号为99999，不影响存储，同样可以显示出来。

【2】小数

decimal(M,N)，

M代表总共的位数，N代表的是小数的位数，如：score decimal(4,1)，表示score这列存储分数，一共有4位，其中小数部分占1位。

 

 

（2）字符

例如：姓名、公司名称、地址、商品描述、简历等数据，适用于字符类型存储

【1】定长字符

char(N)，

如: sex char(6)，存储性别占6位字符，如果只存储了一个字符‘男’,右边会填充5个空格，保证一共6个字符，但是最多只能存储6个字符

处理查询速度快，但是浪费存储空间

对于长度固定确经常作为查询条件的列，可以考虑使用char类型，例如： 18位身份证号码

 

【2】变长字符

varchar(N)，

如：name varchar(20)，存储学生姓名最多可以20个字符，不足20个字符不会填充空格，相对char类型节约存储空间。

 

 

（3）时间

例如：生日、订单日期、登录时间等数据，适用于时间类型存储

【1】date

用于仅仅需要存储日期数值时，不包括时间（时，分，秒）信息

默认格式：‘YYYY-MM-DD’, YYYY表示年，MM表示月，DD表示日

例如：birth date，存储学生的生日信息

 

【2】datetime

用于即需要存储日期也需要存储时间信息时

默认格式：‘YYYY-MM-DD HH:MM:SS’, YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒

例如：login_time datetime，存储登录业务系统的日期和时间

 

 

（4）二进制

例如：照片、文件扫描件、音频、视频等数据，适用于二进制类型存储。

 

 

 

六、MySQL 数据类型选择要素

1、适当 (Appropriate)

需要以最适合数据类型来表示数据

例如：存储日期应当选择日期类型，而不要选择字符串类型。如果选择字符串类型存储，当需要日期计算的时候，需要用相关函数进行转换，记录数多的时候会浪费额外的系统资源

 

2、简洁 (Brief)

选择所用存储空间最少的数据类型。这可节省资源并提高性能

例如：存储学生姓名，应该选择varchar类型，而不要选择char类型，以节约存储空间

 

3、完整 (Complete)

选择的数据类型应分配有可存储特定项的最大可能值的充足空间

例如：存储地址信息时，使用varchar(N)来存储，N值给太小了，当地址字符串过长时会出现不能存储的情况，应当考虑最大的地址长度，给一个合适的值。

 

 

 

七、DDL建表语句CREATE TABLE

第四节已经学了，大概预览一下：

创建学生表，表名student，根据需求，需要存储学号，姓名，性别、生日4列信息：

mysql -uroot -p ；

--以root用户本地连接MYSQL数据库

 

show databases;

--查看当前可以使用哪些数据库

 

use test;

--使用test数据库

 

show tables;

--查看数据库下有哪些表

 

create table student (no int , name varchar(20) , sex char(1) , birth date );

--创建学生表，包括4列信息

 

show tables;     

desc student;      

--查看student表列的信息

 

八、DML-insert语句示例：

1、向student表中添加学生信息，学号1，张三，男，1995-10-23

insert into student (sno,sname,sex,birth) values(1,'张三','男','1995-10-23');：给表添加信息

select * from student;：查看student表

2、再添加一个但是不填写生日

 

3、空值 null

null是SQL语言中的特殊值

null值代表不确定，数据类型未知

属于不明确、状态未知的数据

上例中向表中插入一行数据，没有指定‘李四’ 的生日，而且‘birth’这一列没有指定默认值，会用 null 来填充

 

4、insert添加多个

insert into student (no,name,sex,birth) values (3,'王五','男','1994-06-14'),(4,'小明','女','1993-09-14');

 

 

4、insert语法总结

Insert into 表名 (列名1,列名2,....) values(列1对应值,列2对应值,....);

 

Insert into 表名 values(列1对应值,列2对应值,....,列N对应值); 

不指定列名需要在values中指定所有列对应的值，顺序不能错

 

Insert into 表名 values(列1对应值,列2对应值,....,列N对应值),(列1对应值,列2对应值,....,列N对应值),(列1对应值,列2对应值,....,列N对应值).....;

一次插入多行，每行用逗号隔开，写法仅限于MYSQL数据库

 

 

 

九、DML-update语句

接着上面的表，我们要改变表中信息怎么办？

例如改变李四的生日

update student set birth = '1996-02-24'where no=2;

 

总结：

update 表名 set 列1=列1值, 列2=列2值 where 列N=列N值;

一般更新语句需要加上where子句已定位要修改的行，如果不加将会修改所有行相应的列；如果修改多个列对应的值，用“逗号”隔开各个列。

 

 

 

十、DML-delete语句

删除“李四”信息

delete from student where no=2;

    

总结：

delete from 表名 where 列N=列N值;

有些数据库如oracle可以省略delete后面的from，mysql不可以；一般删除语句需要加上where子句已定位要删除的行，如果不加会删除整个表的所有行

 

 

十一、DQL-SELECT简单查询语句

select * from student;      

-- * 代表显示所有列对应的行数据

select name,sex from student;

--仅挑选student表的2列对应的行数据

 

 

十二、一些扩充

工作中其实很少会让你删除创建，最主要的十学会用select；

而且，一部分的工作是可以借助图形工具来完成的，推荐软件：Navicat Premium 12

这是我们刚才建的表的图形显示，这里有些属性要注意一下

我们对student表右键有个设计表，然后就可以再图形界面对表进行更改。

自动递增：

create table student(no int auto_increment primary key),

勾选后会在SQL预览中有相应代码:

自动递增的意思就是，当不给序号no值时，自动从1开始递增。

剩下的可以自己试试，后面还会有的，这里就是做个引入。

 

 

十三、作业

1：查看原始数据库information_schema中的表，并显示出VIEWS表的字段结构属性信息

 

2：创建数据库offcn，如果offcn存在了不要提示错误信息，不存在创建offcn。

我们有时候会借助到help命令：

If not exists就是如果存在不报错，警告，如果不存在报错。

 

 

3：查看mysql数据库中的user表的密码（authentication_string）那一列数据即可

 

4、要求在offcn库中创建一个学员专业表，名称为offcntab，

表字段分别为sid,name,major(专业),phone,salary,birthday  

其中：

sid为数值型并且是整型，其中要求sid为普通整型，sid字段只能为正整数

name为定长字符型，长度为16个字符，其中name默认值是’ZHANGSAN’

major为定长字符型，长度为16个字符

phone为变长字符型，最大为20个字符，该列不能不填

salary为十进制数值型，总的位数为10，精确到小数点后两位

birthday定义为日期型