第十三章 Mysql - python学习网

1.mysql简介

　　数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

　　关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

　　Mysql是一个关系型数据库管理系统，在WEB应用方面Mysql是最好的RDBMS（Relational Database Management System,关系数据管理系统）应用软件之一。

2.mysql安装

　　Mysql是跨平台的，选择对应的平台下载安装文件安装即可。

#mysql Windows版安装
#1.下载
MySQL Community Server 5.7.16(http://dev.mysql.com/downloads/mysql/)
#2.解压
解压到指定目录
#3.添加环境变量
将MySQL的bin目录路径追加到变值值中
#4.初始化
mysqld --initialize-insecure
#5.启动mysql服务
mysqld
#6.启动mysql客户端并连接mysql服务
mysql -u root -p

#制作mysql的windows服务(以管理员身份运行cmd)
F:\mysql-5.6.39-winx64\bin\mysqld --install
#移除mysql的windows服务
F:\mysql-5.6.39-winx64\bin\mysqld --remove
#注册成服务后，可以直接执行命令启动和关闭mysql服务
net start mysql
net stop mysql

将mysql服务制作成windows服务

3.mysql软件基本管理

初始状态下，管理员root，密码为空，默认只允许从本机登录localhost
设置密码
[root@egon ~]# mysqladmin -uroot password "123"        设置初始密码 由于原密码为空，因此-p可以不用
[root@egon ~]# mysqladmin -uroot -p"123" password "456"        修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码

命令格式:
[root@egon ~]# mysql -h172.31.0.2 -uroot -p456
[root@egon ~]# mysql -uroot -p
[root@egon ~]# mysql                    以root用户登录本机，密码为空

View Code

　　忘记密码

#windows平台下，5.7版本mysql，破解密码的两种方式：
#方式一

#1 关闭mysql
#2 在cmd中执行：mysqld --skip-grant-tables
#3 在cmd中执行：mysql
#4 执行如下sql：
update mysql.user set authentication_string=password('') where user = 'root';
flush privileges;
#5 tskill mysqld #或taskkill -f /PID 7832
#6 重新启动mysql

#方式二
#1. 关闭mysql，可以用tskill mysqld将其杀死
#2. 在解压目录下，新建mysql配置文件my.ini
#3. my.ini内容,指定
[mysqld]
skip-grant-tables
#4.启动mysqld
#5.在cmd里直接输入mysql登录，然后操作
update mysql.user set authentication_string=password('') where user='root and host='localhost';
flush privileges;
#6.注释my.ini中的skip-grant-tables，然后启动myqsld，然后就可以以新密码登录了

View Code

　在windows下，为mysql服务指定配置文件

#配置文件中的注释可以有中文，但是配置项中不能出现中文
#在mysql的解压目录下，新建my.ini,然后配置
#1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8
default-storage-engine=innodb
innodb_file_per_table=1
#解压的目录
basedir=E:\mysql-5.7.19-winx64
#data目录
datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据


#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123

#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=egon
password=4573

#如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准

　　统一字符编码

#1. 修改配置文件
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#2. 重启服务
#3. 查看修改结果：
\s
show variables like '%char%'

View Code

4.sql语句

　　Sql语句主要用于存取数据，查询数据，更新数据和管理数据库系统。

#Sql语句分为3种类型
#1.DDL语句：数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
#2.DML语句：数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3.DCL语句：数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

5.库操作

　　安装并登陆mysql后，查看数据库，发现有如下数据库：

#information_schema:虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
#performance_schema:Mysql5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
#mysql:授权库，主要存储系统用户的权限信息
#test:Mysql数据库系统自动创建的测试数据库

　　创建数据库

#数据库命名规则
#可以由字母、数字、下划线、@、#、$组成
#区分大小写
#唯一性
#不能使用关键字如select、create
#不能单独使用数字
#最长128位

#数据库相关操作

#创建数据库
create database db1 charset utf8;
#查看数据库
show databases;
show create database db1;
select database();
#选择数据库
use database db1;
#修改数据库
alter database db1 charset utf8;
#删除数据库
drop database db1;

6.表操作

　　表相关操作

#先切换到库下
use db1;
#创建表
#create table 表名（
字段名1 类型[（宽度） 约束条件]，
字段名2 类型[（宽度）约束条件]
）;
#在同一张表中，字段名不能相同；宽度和约束条件可选；字段名和类型是必须的；表中的最后一个字段不要加逗号
create table t1(id int,name char);

#查看表
show tables;  #查看表
describe t1;   #查看表结构，可简写为 desc t1
show create table t1\G  #查看表详细结构

#修改表
alter table t1 modify name char(3);
alter table t1 change name name1 char(2);

1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

修改表语法

#1. 修改存储引擎
alter table service engine=innodb;

#2. 添加字段
alter table t1  add name varchar(20) not null；

alter table t1 add stu_num varchar(10) not null after name;  //添加name字段之后

alter table t1 add sex enum('male','female') default 'male' first; //添加到最前面

#3. 删除字段
 alter table t1 drop sex;

alter table service drop mac;

#4. 修改字段类型modify
alter table t1 modify age int(3);
alter table t1 modify id int(11) not null primary key auto_increment;    //修改为主键

#5. 增加约束（针对已有的主键增加auto_increment）
alter table student10 modify id int(11) not null primary key auto_increment;
#ERROR 1068 (42000): Multiple primary key defined

alter table student10 modify id int(11) not null auto_increment;
#Query OK, 0 rows affected (0.01 sec)

#6. 对已经存在的表增加复合主键
alter table service2 add primary key(host_ip,port);        

#7. 增加主键
alter table student1 modify name varchar(10) not null primary key;

#8. 增加主键和自动增长
alter table student1 modify id int not null primary key auto_increment;

#9. 删除主键
#a. 删除自增约束
alter table student10 modify id int(11) not null; 

#b. 删除主键
alter table student10 drop primary key;

示例

#复制表
#复制表结构+记录
create table new_service select * from service;
#只复制表结构
create table new1_service select * from service where 1=2;
create table t4 like employees;

#删除表
drop table t1;

　　数据类型：表内存放的数据有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

数值类型

　　整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，默认的显示宽度都是在最大值的基础上加1

　　浮点型

　　定点数类型：DEC等同于DECIMAL，浮点类型：FLOAT DOUBLE

　　位类型

　　BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

#对于位字段需要使用函数读取
bin()显示为二进制
hex()显示为十六进制

日期类型:DATE TIME DATETIME TIMESTAMP YEAR

YEAR
            YYYY（1901/2155）

DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

View Code

字符串类型：char,varchar,

1.CHAR 和 VARCHAR 是最常使用的两种字符串类型。
2.一般来说CHAR(N)用来保存固定长度的字符串，对于 CHAR 类型,N 的范围 为 0 ~ 255
VARCHAR(N)用来保存变长字符类型，对于 VARCHAR 类型,N 的范围为 0 ~ 65 535
CHAR(N)和 VARCHAR(N) 中的 N 都代表字符长度,而非字节长度。
3. char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形
4.虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值,忽略 CHAR 值存在的右填充,即使将 SQL _MODE 设置为 PAD_CHAR_TO_FULL_ LENGTH 也一样,,但这不适用于like
5.虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
6.#其他字符串系列（效率：char>varchar>text）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
BINARY系列 BINARY VARBINARY
text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

View Code

枚举类型与集合类型

　　字段值只能在给定的范围内选一个值，eum（单选）只能在给定的范围内选一个值，set（多选）在给定的范围内可以选择一个或多个值。

python学习网

分类