MySQL 数据库 2019-04-13

作者: 拔萝卜_c4c8 | 来源:发表于2019-04-13 01:09 被阅读1次

MySQL 数据库

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database
数据库作用
1. 持久保存
2. 方便查询并提取满足条件的数据, 数据访问速度快
3. 处理并发访问
4. 权限管理
常见数据库:

关系型数据库 MySQL ->库->表->数据 MySQL Oracle PostgreSQL SQL Server ....

非关系型数据库 Redis ->数据 MongoDB ... 概念：

数据： data

数据库： DB

数据库管理系统：DBMS

数据库系统：DBS

一、MySQL基础操作

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

操作数据库的步骤

连接, 打开库, 操作, 关闭退出

SQL ( Structure query language ) 结构化查询语言

SQL语言分为4个部分：
    DDL（Data Definition Languages） :数据的定义
    DML（Data Manipulation Language）:数据的操作        
    DQL（Data Query Language）       :数据的查询操作
    DCL（Date Control Language）     :数据控制语句

SQL语句中的快捷键

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

\s 查看服务器端信息

\c 结束命令输入操作

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

\h 查看帮助
MySQL 语法特点
1. SQL 语句可以换行, 要以分号结尾
2. 命令不区分大小写. 关键字和函数建议用大写
3. 如果提示符为 '> 那么需要输入一个'回车
4. 命令打错了换行后不能修改, 可以用 \c 取消

二、MySQL 数据类型

1. 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2. 日期和时间类型

类型	大小(字节)	范围	格式	用途
DATA	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/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3. 字符串类型

类型	大小	用途
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字节	极大文本数据

三、MySQL 运算符

算术运算符：+ - * / %

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

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

逻辑运算符：and or not

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

四、MySQL 库操作

查看数据库 show databases;
创建数据库 create database 库名 default charset=utf8;
打开数据库 use 库名;
查看当前库 select database();
删除数据库 drop database 库名;

五、MySQL 表操作

1. 字段约束

unsigned : 无符号

int(4) : 显示宽度

zerofill : 零填充, 位数不够的时候用前导零填充

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

default : 设置默认值

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

primary key : 主键不能为空唯一性

unique : 唯一索引可以为空唯一性

index : 普通索引可以为空可以重复

2. 基本操作

查看当前库中所有表：show tables;

创建表：create table 表名(字段1 类型 [字段约束],字段2 类型 [字段约束]...)default charset=utf8;

查看表结构：desc 表名;

查看建表语句：show create table 表名\G

删除表：drop table 表名;

查看当前表中所有数据: select * from 表名;

3. 表数据操作

3.1 增删改

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

插入：格式 insert into 表名[(字段列表)] values(值列表...);

1. 标准添加: insert into 表名(所有字段) values(对应的值);

2. 指定部分字段添加值: insert into 表名 (部分字段) value (对应的值);

3. 不指定字段添加值: insert into 表名 value(值1,值2,...);

4. 批量添加值: insert into 表名 values(a值1,a值2,...),(b值1,b值2,...);

删除：delete from 表名 where 字段=某个值;
修改：update 表名 set 字段=某个值 where 条件;

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

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

3.2 查询

书写流程：where group by -->having order by limit

数据的DQL操作：数据查询

 select [字段列表]  /*  from 表名

    [where 搜索条件]

    [group by 分组字段 [having 子条件]]

    [order by 排序 asc|desc]

    [limit 分页参数];

基础查询 : select * from 表名;
where条件查询
- 你可以在 WHERE 子句中指定任何条件。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
```
查询lamp138和lamp94期的女生信息
mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
```
like子句
- LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。
- 可以使用regexp正则来代替 like
```
查询name字段值是以zh开头的所有信息
mysql> select * from stu where name like "zh%";
```
MySQL的统计函数(聚合函数): select max(),min(),sum()avg(),count(*) from 表名;
GROUP BY 语句分组和 having
- GROUP BY 语句根据一个或多个列对结果集进行分组。
- 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- having 对分组后的数据进行删选
```
MySQL> select classid,sex,count(*) from stu  group by classid,sex;
```
ORDER BY 排序 ------ asc 升序 / desc 降序
- 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
- 可以设定多个字段来排序。
- 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认为asc 按升序排列
- 可以添加 WHERE...LIKE 子句来设置条件。
```
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
```
limit 关键字查询部分数据
- limit m; 查询数据只显示前m条
- limit m,n; 排除前m条，然后再查询出前n条

4. 表结构操作

格式: alter table 表名 action(更改选项);

添加字段：alter table 表名 add 字段名 类型 [字段约束];

删除字段：alter table 表名 drop 字段名;

修改字段：change 可改字段名和类型; modify 可改字段类型, 不可改字段名
        alter table 表名 change 原字段名 修改后的字段名 类型 [字段约束];
        alter table 表名 modify 字段名 修改后的类型 [字段约束];

添加唯一性索引：alter table user add unique uni_name(name);

添加普通索引：alter table user add index index_email(email);

删除索引：alter table user drop index index_email;

修改表名：alter table 表名 rename as 新表名；

更改auto_increment初始值：alter table 表名称 auto_increment=数值；

更改表类型：alter table 表名 engine=‘innodb’;

          alter table 表名 engine='myisam';

六、数据的导入和导出

1. 数据导出

-- 导出整个数据库
mysqldump -u 用户名 -p 数据库名 >./导出的文件名.sql
Enter password:
-- 导出一个表
mysqldump -u 用户名 -p 数据库名 表名 >./导出的文件名.sql
Enter password:

2. 数据导入

-- 将数据库导入
-- 需在本地数据库中创建相对应同名的数据库
mysql -u 用户名 -p 数据库名<./文件名.sql
Enter password:

七、授权、改密

1. 授权

格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';

实例：创建zhangsan账号，密码123，授权lamp61库下所有表的增/删/改/查数据,来源地不限
 mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified   by '123';
 mysql> grant all on *.* to zhangsan@'%' identified by '123';
 Query OK, 0 rows affected (0.00 sec)

 添加用户, 并授予在lamp189的数据库中可以对user表进行 查询和添加数据操作
 GRANT select,insert ON lamp189.user TO 'xxoo'@'%' IDENTIFIED BY 'abcd'

 删除用户
 drop user 'xxoo'@'%'

2. 改密

1.找到mysql配置文件 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
2.在[mysqld] 下面添加 skip-grant-tables 保存
3.重启mysql服务
4.打开命令行 mysql -uroot -p  不用输入密码
5.use mysql 切换到权限库
6.执行修改密码 Update user set authentication_string=password('1234567') where user='root';
7:执行刷新权限 flush privileges;
修改成功后：要把配置文件改回来 然后在重启服务
使用新密码链接数据库

八、Python 连接数据库

Python3 MySQL 数据库连接

1. PyMySQL安装

pip install pymysql

2. 连接数据库

通过如下代码测试数据库连接

 import pymysql
 
 # 打开数据库连接
 db = pymysql.connect("localhost","root","123456","mydb" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # 使用 execute()  方法执行 SQL 查询 
 cursor.execute("SELECT VERSION()")

 # 使用 fetchone() 方法获取单条数据.
 data = cursor.fetchone()

 print ("Database version : %s " % data)

 # 关闭数据库连接
 db.close()

3. 执行数据添加

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","root","123456","mydemo" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 

try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

4. 执行删除操作

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 删除语句
 sql = "delete from stu where id = '%d'" % (13)
 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交修改
    db.commit()
 except:
    # 发生错误时回滚
    db.rollback()

 # 关闭数据库连接
 db.close()

5. 执行数据修改/更新

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

6. 执行数据查询

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

fetchall(): 接收全部的返回结果行.

rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 查询语句
 sql = "select * from stu limit %d" % (3)
 #sql = "select * from stu"

 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
       id = row[0]
       name = row[1]
       sex = row[2]
       age = row[3]
       classid = row[4]
        # 打印结果
       print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
 except:
    print ("Error: unable to fetch data")

 # 关闭数据库连接
 db.close()