MySQL 数据库

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. 数值类型

2. 日期和时间类型

3. 字符串类型

三、MySQL 运算符

算术运算符：+ - * / %

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

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

逻辑运算符：and or not

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

四、MySQL 库操作

1. 查看数据库 show databases;
2. 创建数据库 create database 库名 default charset=utf8;
3. 打开数据库 use 库名;
4. 查看当前库 select database();
5. 删除数据库 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()