MySQL数据库
非关系型数据库的优势：
-
性能
NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
-
可扩展性
同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
关系型数据库的优势：
-
复杂查询
可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
-
事务支持
使得对于安全性能很高的数据访问要求得以实现。
MySQL数据库的介绍
发展史
1996年，MySQL 1.0
2008年1月16号 Sun公司收购MySQL。
2009年4月20，Oracle收购Sun公司。
MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。
MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。
MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。
一 进入到MySQL数据库
#正常的标准形式
mysql -h127.0.0.1 -uroot -p#-h HOST -u USER -p PASSWORD
#每个数据库都有一个默认的root超级管理员
#默认没有数据库的密码
#建议的使用方式
mysql -uroot -p
#如果访问的是本机的数据库 -h可以为 127.0.0.1/localhost/本机的ip地址
二 对于MySQL数据库的操作
命令为：
CREATE创建 DROP删除 SHOW查看 ALTER修改库或者表的结构
- 查看所有的数据库
mysql> show databases;
-
选择数据库
use 库名
-
查看当前所在的数据库
select database()
-
查看当前库下 所有的表
show tables()
-
创建数据库
create database 库名
-
查看创建库的信息
show create database 库名
-
创建一个不存在的数据库 python 如果存在在不创建 不存在则创建 （不管在不在 都执行成功 防止报错）
create database if not exists 库名
-
删除数据库
drop database 库名
-
删除数据库 防止报错
drop database if exists 库名
设置数据库的字符集
alter database 库名 character set utf8/latin1(之前数据库默认latin1)
-
创建库 并设置字符集 在该库下创建的表和字段 默认都为 utf8字符编码
create database 库名 character set utf8
三 对数据库的数据表进行操作
-
查看所有的表
show tables
-
删除数据表
drop table if exists 表名
-
创建表
CREATE TABLE `user` ( #字段名 数据类型 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL DEFAULT '帅哥', `sex` enum('w','m') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 -
对于表字段的删除
alter table 表名 drop 字段名
-
给表 添加字段
alter table 表名 add 字段名..... after 字段名
MySQL必须知道的
- MySQL数据库的命令 不区分大小写
- 每创建一个数据库 都会对应在当前MySQL数据库下 创建 一个为你数据库名字的文件夹 里面包含 该库下面的所有的表
- 在Windows下 库名也不区分大小写 但是如果是在Linux 下 严格区分大小写
- \G 以竖着形式进行查看
- \c 撤销当前的命令
- 如果 出现引号没有闭合的情况下 必须将另外的引号 补全 再次 \c 撤销命令
- \q/quit/exit 对数据库进行退出
MySQL开启不严谨报错
修改my.ini配置文件
路径:C:\ProgramData\MySQL\MySQL Server 5.7
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
latin1有的版本默认字符编码
MySQL服务无法正常启动的解决方法(1053错误)
是mysql权限问题，开始->输入services.msc，找到MySQL服务，右键属性，登录，选择此帐户，然后选择Administrator和相应密码，确定。就能启动了。
授权root可以通过外网IP进行访问
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MySQL表的创建
一 字段类型
-
数值类型
类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 （-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 FLOAT 4 字节 单精度浮点数值 DOUBLE 8 字节 双精度浮点数值 DECIMAL[ˈdɛsəməl] Gld对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值(更加准确) -
–INT(3)、SMALLINT（3）等整型后面的数字不会影响数值的存储范围，只会影响显示
–整型后面的数字只有配合零填充的时候才有实际意义。 zerofill
–整型后面的数字可以省略
Decimal 小数类型不仅能够保证数据计算更为精确，还可以节省储存空间
Float/double/decimal 在存储的时候 小数点超出的时候 会4舍五入
-
-
日期和时间类型
类型 大小(字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 日期类型注意事项：
–存储日期时，我们可以使用整型来进行存储时间戳，这样做便于我们进行日期的计算
-
字符串类型
类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据 enum(“w”,"m") 65535个成员 枚举：可赋予某个枚举成员mc set(“w”,"m") 64个成员 集合：可赋予多个集合成员 多个值用逗号隔开 字符串类型注意事项：
-
Char varchar区别：char执行效率 高于 varchar varchar 相对于char节省空间
- –CHAR和VARCHAR类型的长度范围都在0~255之间
-
Char varchar区别：char执行效率 高于 varchar varchar 相对于char节省空间
+ –在使用CHAR和VARCHAR类型时，当我们传入的实际的值的长度大于指定的长度，字符串会被截取至指定长度
+ –在使用CHAR类型时，如果我们传入的值的长度小于指定长度，实际长度会使用空格补至指定长度
+ –在使用VARCHAR类型时，如果我们传入的值的长度小于指定长度，实际长度即为传入字符串的长度，不会使用空格填补
+ –CHAR要比VARCHAR效率更高，但占用空间较大
-
–BLOB和TEXT类型是可以存放任意大数据的数据类型
–BLOB区分大小写，TEXT不区分大小写
–ENUM和SET类型是特殊的的串类型，其列值必须从固定的串集中选择
–ENUM只能选择其中一个值，SET可以选择多个值
三 字段约束
-
unsigned 无符号 正数
只能用于设置数值类型，不允许出现负数
最大存储长度会增加一倍
-
zerofill 零填充
只能用于设置数值类型，在数值之前会自动用0补齐不足的位数
-
auto_increment 自增
用于设置字段的自动增长属性，每增加一条记录，该字段的值会自动加1
-
default 默认值
可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值
在修改表的某字段的时候 再次添加默认值的时候 只有在添加新的值的时候才会出现默认值 否则只有添加新的字段的时候 默认值才会将没有值的填充上 null也是值 空值
-
Null和not null
默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段填入值
-
comment 设置当前字段的说明
实例
create table b( -> age tinyint unsigned comment '年龄' -> ) engine=innodb default charset=utf8 comment='b表' -> ;n
给字段加注释
alter table 表名 modify 字段名 类型 约束条件 comment='注释内容'
给表添加注释
alter table 表名 comment='注释内容'
NULL值注意事项：
-NULL意味着“没有值”或“未知值”
–可以测试某个值是否为NULL
–不能对NULL值进行算术计算
–对NULL值进行算术运算，其结果还是NULL
–0或NULL都意味着假，其余值都意味着真
四 在MySQL中，主要有四类索引：
主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）
(1) 主键索引
主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。
注意：
1.最好为每张表指定一个主键，但不是必须指定。
2.一个表只能指定一个主键，而且主键的值不能为空
3.主键可以有多个候选索引（例如NOT NULL，AUTO_INCREMENT）
自增的步长：
mysql是的默认步长是基于会话session的 查看全局变量，其中默认是1
mysql> show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
设置步长基于会话步长，只能自该自己的会话
set session auto_increment_increment=2; 设置会话步长
set session auto_increment_offset=10; 设置开始的位置
基于全局的级别的，可以修改全部的会话
show global variables like 'auto_inc%'; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
set global auto_increment_offset=10;
修改表的自增的值
alter table 表名 auto_increment = 1 #再次从1 开始递增
(2) 唯一索引
唯一索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。我们使用关键字UNIQUE对字段定义为唯一索引
(3) 常规索引
常规索引技术是关系数据查询中最重要的技术，如果要升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。常规索引也存在缺点：
1.多占用磁盘空间
2.会减慢插入，删除和修改操作
3.需要按照索引列上排序格式执行
创建索引我们可以使用INDEX和KEY关键字随表一同创建。
(4) 全文索引
alter table user add fulltext a(article)
全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。
但是FULLTEXT是不支持中文全文索引的，所以我们将来会使用效率更高的全文索引引擎Sphinx。
五 数据表的类型及存储位置
MyISAM和InnoDB两种表类型最为重要：
1.MyISAM数据表类型的特点是成熟、稳定和易于管理。
2.MyISAM表类型会产生碎片空间，要经常使用OPTIMIZE TABLE命令去清理表空间
3.MyISAM不支持事务处理，InnoDB支持
4.MyISAM不支持外键，InnoDB支持
5.MyISAM表类型的数据表效率更
6.MyISAM表类型的数据表会产生三个文件，InnoDB表类型表默认只会产生一个文件。
六 不同表引擎存储的区别
.frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，
1、MyISAM数据库表文件：
.MYD文件：即MY Data，表数据文件
.MYI文件：即MY Index，索引文件
.log文件：日志文件
3、InnoDB采用表空间（tablespace）来管理数据，存储表数据和索引
InnoDB数据库文件（即InnoDB文件集，ib-file set）：
sibdata1、ibdata2等：系统表空间文件，存储InnoDmB系统信息和用户数据库表数据和索引，所有表共用
.ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引
日志文件： ib_logfile1、ib_logfile2
六 innodb的事务处理
数据库默认类型为mysiam类型
engine = INNODB
命令修改表类型
alter table 表名 engine=innodb;
(1) 查询当前是否为 自动提交
Select @@autocommit 值为1 为自动提交
(2) 开启事务处理
set autocommit = 0;
(3) 事务开始
begin;
(4) 执行sql语句
insert into 。。。。
(5) 提交或者回滚
commit work 提交
rollback work 回滚
注意：
- 如果开启了事务处理 在对数据进行了操作 但是没有执行 commit的时候 退出数据库 那么对数据进行操作的都不会保存
- 只有innodb才支持事务处理
七 建表的注意事项
1.表的字段之间要使用逗号隔开。
2.建表的最后一句一定不能有逗号。
3.表名称和字段名称尽量不要使用MySQL系统的关键字
4.如果一定要使用关键字，我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。
5.使用反引号会使建表效率增高。
6.数据表名称和字段名称不能重名
7.AUTO_INCREMENT属性必须依附于主键索引
二 对表结构的操作
-
给表添加一个新的字段
alter table 表名 add 字段名 类型 约束条件
-
给表 删除一个字段
alter table 表名 drop 字段名
-
更改字段名
alter table 表名 change 原字段名 新字段名 类型 约束条件
-
修改字段信息
alter table 表名 modify 字段名 字段类型 约束条件
-
表添加索引
alter table 表名 add 索引类型 索引名(字段名) #添加索引名的索引
alter table 表名 add 索引类型(字段名) #不添加索引名的索引 默认索引名为字段名
-
删除索引
alter table 表名 drop key 索引名
-
创建一个 和 a表一样的表 b
create table b like a
-
查看表的所有索引
show index from 表名
-
修改表的字符编码
alter table aa character set utf8;
-
修改表的某个字段的字符编码
alter table aa modify name varchar(20) character set utf8;
八 INSERT 数据的添加
-
指定字段添加值
insert into 表名(字段名,,) values(值,)
-
不指定字段 添加值
insert into 表名 values(值)
注意:
所有字段都需要一一对应的添加上值
-
指定字段添加多个值
insert into user(字段名) values(值一),(值二)
-
不指定字段添加多个值
insert into user values(值一),(值二)
九 SELECT 数据的查询
-
不指定字段的查询 （不建议）
select * from 表名
-
指定字段的数据查询（建议）
select 字段名,,,, from 表名
-
对查询的字段 起别名
select 字段名 as 别名 from 表名
十 WHERE 条件
(1) 比较运算符
-
>select * from 表名 where age>181
-
<
select * from 表名 where age<18
-
>=select * from 表名 where age>=18
-
<=
select * from 表名 where age<=18
-
!=/<>
select * from 表名 where age!=18
select * from 表名 where age<>18
=
(2) 逻辑运算符
-
and 并且
select age from 表名 where age>10 and age<20
-
or 或运算
select age from 表名 where age>10 or age<20
-
between and 在什么范围之内
select * from user where age between 10 and 20
select * from user where age>=10 and age<=22
-
not between and 不在什么范围之内
select * from user where age not between 10 and 22
select * from user where age<10 or age>22
-
in 在，，，里面/not in 在，，，里面
select * from user where age in(16,27,38,22) #查询所有信息 条件是 年龄在 16,27,38,22的年龄里
(3) 子查询 sql语句的条件 还是sql语句
select * from user where age in(select age from user where age between 10 and 20)
(4) 排序查询 order by
-
默认为升序
order by age
order by age asc
-
降序排列
order by 字段名 desc
(5) is is not
-
查询 age为null的数据
select * from user where age is null
-
查询age不为null的数据
select * from user where age is not null
(6) limit x,y 从x开始 取y条数据 如果从0取y条数据 那么x可以省略
select * from user limit 0,5
组合体:
select * from user where age>10 and age <22 and phone is not null order by age desc limit 2
查询所有的信息 条件是 年龄在10到22之间 并且 电话不为 空 取两条年龄最大的数据
删除表所有的数据
- delete from 表名
- truncate 表名
区别:
delete 删除表所有的数据 再次添加数据的时候 主键自增 会在原有的基础上 继续自增
truncate 会将cpszs 主键自增归位 也就是 再次添加数据 从1开始递增
修改表的自增的值
alter table 表名 auto_increment = 1 #再次从1 开始递增
group by 分组
select sex,count(*) from user group by sex #按照性别分组 并统计有多少人
select class,count(*) as person from user group by class; #统计 每个班级 有多少人
select class,sex,count(*) as person from user group by class,sex; #统计每个班的男生和女生分别有多少人
having 相当于 where
select class,sex,count(*) as person from user group by class,sex having person>10; #查询 人数 在10个以上的男生或女生
select class,count(*) as person from user group by class having person>50; #查询50人 以上的班级
select classid,sex,count(*) p from a group by classid,sex having classid in('python1708','python1707') and p>2;
select classid,count(*) as t from a group by classid having classid in('python1706','python1707') and t>1; #统计classid为 1706或者1707 的 人数大于1人的p
like 模糊查询
'%字符%' 包含某个字符
‘字符%’ 以什么字符作为开头
'%字符' 以什么字符作为 结尾
select * from 表名 where 字段 like ‘%字符%’ #查询 某个字段中 包含 某个字符的所有数据
select name,age from user where name like '%王%' order by age desc limit 0,5; #查询 姓名包含王字的年龄最大的5条数据
select * from user where name like '%王%'and age>18 order by id desc limit 5; #查询 姓名包含王字的姓名 并且年龄大于18 降序排列 取5条数据
DELETE 删除 (如果没有where 默认删除所有数据)
delete from 表名 where 条件
UPDATE 修改(如果没有条件 默认修改所有的数据)
update 表名 set 字段名=字段值,,, where 条件
DISTINCT 去除重复数据
select distinct 字段名 from 表名
select age from user group by age #查询去除重复数据的年龄
多表联查
(1) 隐式内链接
select * from 表1,表2 where 表1.字段名 = 表2.字段名
select u.id,u.name,info.email,info.address from user u,userinfo info where u.id=info.uid 查询俩个表里的某些字段
(2) 显式内链接
select * from 表1 inner join 表2 on 表1.字段名 = 表2.字段名
(3) left join 左链接
select * from userinfo u LEFT JOIN user info on u.uid=info.id
注意:
以左表位主表 右表为辅表 会把主表的数据全部查出来 辅表的数据有对应的就查出来 没有 就以null来补全
(4) right join 右链接
select * from userinfo u right JOIN user info on u.uid=info.id
注意:
以右表位主表 左表为辅表 会把主表的数据全部查出来 辅表的数据有对应的就查出来 没有 就以null来补全
MySQL的聚合函数
- count() 统计个数
- max() 最大的
- min() 最小的
- sum() 求和
- avg() 求平均数
数据库的导入导出
(1)导入
mysql -uroot -p 库名<demo.sql
(2)导出
mysqldump -uroot -p 库名>demo.sql
以下作为了解
修改密码
方法1 用SET PASSWORD命令
set password for 用户名@loclhost = passworsd('新密码');
方法2 用UPDATE直接编辑user表
- 首先登录MySQL。
- 连接权限数据库： use mysql; 。
- 改密码：update user set password=password("shapolang") where user="root";（别忘了最后加分号）
- 刷新权限（必须步骤）：flush privileges;
重新登录，输入新密码shapolang就ok了；
(1) 使用 mysql库
use mysql
(2) 查看当前数据库下有 哪些用户
select user from user
(3)创建用户
create user lisi identified by '123456';
(4) 修改用户名
rename user lisi to wangwu;
(5) 赋予权限
grant all on demo.* to wangwu;
all 所有的权限
select,update 赋予 查 和改的权限
(6) 回收权限
revoke all on demo.* from wangwu;
(7) 删除用户
drop user wangwu
(8) 刷新
flush privileges
无限极分类
select * from type order by concat(path,id); #concat 将字段 链接在一起
| id | pid | typename | path |
|---|---|---|---|
| 1 | 0 | movie | 0, |
| 2 | 1 | japanmovie | 0,1, |
| 3 | 1 | 美国movie | 0,1, |
| 7 | 1 | Chinesemovie | 0,1 |
| 4 | 0 | 衣服 | 0, |
| 5 | 4 | 内衣 | 0,4, |
| 6 | 5 | 红豆 | 0,4,5, |
一 Python 操作 MySQL
import pymysql
pip install pymysql
(1) 连接MySQL数据库
db = pymysql.connect(主机名,用户名,密码,数据库名)
(2) 设置字符集
db.set_charset(‘utf8’)
cursor = db.cursor()
(4) 执行sql语句
cursor.execute(sql语句)
(5) 获取所有的结果集
cursor.fetchall()
(6) 获取一条数据
cursor.fetchone()
(7) 获取当前sql语句 所受影响的行数
cursor.rowcount
(8) 关闭数据库链接
db.close()
拼凑正常完整的sql语句
print("select name,password from user where name=\""+username+"\"")
print("select name,password from user where name='"+username+"'")
print("select name,password from user where name='%s'"%(username))
print("select name,password from user where name='{}'".format(username))
二 执行事务
事务机制可以确保数据一致性。
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
- 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
- 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。
实例
实例(Python 3.0+)
# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# 执行SQL语句
cursor.execute(sql)
# 向数据库提交
db.commit()
except:
# 发生错误时回滚
db.rollback()
对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。
commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。
三 Python的hashlib提供了常见的摘要算法，如MD5，SHA1等等。
md5讲数据加密成32位长度的字符串
import hashlib
# 计算出一个字符串的MD5值：
md5 = hashlib.md5() #MD5加密
md5.update('how to use md5 in python hashlib?'.encode('utf-8'))
print(md5.hexdigest())
