MySQL基本使用

1.MySQL入门

1.1.数据库的介绍

1.数据库就是存储和管理数据库的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增加、修改、查询等操作。
2.数据库的分类
(1)关系型数据库
行、列、表、库
(2)非关系型数据库：key和value 
常见的有MongoDB文档型数据库,Redis：key-value的内存缓存,HBASE是列 式数库，目标是高效存储大量的数据。
3.mysql数据库
MySQL是一个关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件，它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。

1.2.MySQL数据库的登录和登出

1.登录
方法一：mysql -u用户名 -p密码
方法二：mysql --host=192.168.88.100 --user=root --password=123456
2.登录效果
select now(); 显示当前时间
3.登出数据库
quit 或 exit 或 ctrl + d
4.数据库的定义语言：
简称DDL(Data Definition Language)
用来定义数据库对象：数据库，表，列等。
关键字：create，alter，drop等
5.数据库的操作语言：
简称DML(Data Manipulation Language)
用来对数据库中表的记录进行更新。
关键字：insert，delete，update等
6.数据库的查询语言：
简称DQL(Data Query Language)
用来查询数据库中表的记录。
关键字：select，from，where等
7.数据库的控制语言：
简称DCL(Data Control Language)
用来定义数据库的访问权限和安全级别，及创建用户。

1.3.DDL数据库操作

1.3.1DDL之库操作

1.创建数据库：
CREATE DATABASE IF NOT EXISTS 数据库名称;
指定字符集的方法有两种(效果完全相同)
CHARACTER SET utf8
CHARSET = utf8
2.查看数据库列表：
SHOW DATABASES;
3.使用数据库：
USE 数据库名称；
SELECT database(); 查看当前使用的数据库名字
SHOW CREATE DATABASE day02; # 查看指定数据库的详情
4.删除数据库：
DROP DATABASE 数据库名称;
5.修改数据库：
ALTER DATABASE day02 CHARSET 'utf8';

1.3.2DDL之表操作

1.创建数据表：
create table if not exists 数据表名(
        字段名 数据类型(长度) 约束,
        字段名 数据类型(长度) 约束,
        字段名 数据类型(长度) 约束
    );
Sql中常见的数据类型：
	整数: int
    小数: decimal, double
    字符串: varchar, char
    日期类型: date(年月日), time(时分秒), datetime(年月日, 时分秒), timestamp(时间戳, 毫秒值)
    枚举类型:  enum;ENUM ('男', '女', '保密')
2.查看数据表：
SHOW TABLES;
DESC 表名; -- 查看该表的详情的, 有哪些字段, 字段类型, 约束等.
3.修改表名：
RENAME TABLE 原字段名 TO 新字段名; 
4.删除数据表：
DROP TABLE category;

1.3.3DDL之字段操作

1.查看表结构：
DESC category;
2.添加字段(列), addr int 非空约束
alter table 数据表名 add 列名 数据类型 约束;
ALTER TABLE category ADD address INT NOT NULL;
3.修改字段(列), 修改 类型, 约束：
alter table 数据表名 modify 列名 数据类型 约束;
ALTER TABLE category MODIFY address VARCHAR(50);
alter table 数据表名 change 旧列名 新列名 数据类型 约束;
ALTER TABLE category CHANGE addr address VARCHAR(20) UNIQUE;
4.删除字段(列)
alter table 数据表名 drop 列名;
ALTER TABLE category DROP address;

1.4.DML表数据的操作

1.4.1.DML之表数据的操作

1.添加表数据：
添加多条数据, 格式: insert into 数据表名(列名1, 列名2) values(值1, 值2),(值1, 值2),(值1, 值2);
语法糖的简写方法：
insert into 数据表名 values(值1, 值2, 值3); 必须是全列值
2.更新表数据：
update 数据表名 set 列名=值, 列名=值 where 条件;
3.删除表数据：
delete from 数据表名 where 条件; 只清空表数据，不会重置主键id
truncate table 数据表名; 清空表数据的同事，清空主键id
4.查询数据：
sele *from 表名

1.4.2.DML之表数据约束详解

1.主键约束：
primary key,：特点: 非空, 唯一, 且一般结合自增一起使用 AUTO_INCREMENT
2.非空约束：NOT NULL
3.唯一约束：UNIQUE
4.默认值约束：DEFAULT '北京'

1.5.DQL数据查询

1.5.1.简单查询

1.SELECT 列名(S) FROM 表名；
2.条件语句：
SELECT * FROM 表名 WHERE 条件;
3.比较运算符：不等于: != 或 <>，大于等于: >=
4.逻辑运算符：and 逻辑与，or 逻辑或，not 逻辑非
5.模糊查询：like是模糊查询关键字，%表示任意多个任意字符，_表示一个任意字符
SELECT * FROM product WHERE pname LIKE '%斯';
6.范围查询：
第一种and：SELECT * FROM product WHERE price BETWEEN 800 AND 2000;
第二种in：SELECT * FROM product WHERE price in (600,800,2000);
7.空判断查询：判断为空使用: is null，判断非空使用: is not null

1.5.2.排序查询

1.ASC 升序;DESC 降序
SELECT * FROM product ORDER BY price DESC;

1.5.3.聚合函数

1.聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。
2.常用的聚合函数:
count(col): 表示求指定列的总记录数
count(*): 表示把整行当做一个整体，都为null，该行才会被忽略
count(1): 有所少条，就算多少行
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
注意：==聚合函数的计算会忽略null值

1.5.3.分组查询

1.分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;
说明：
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

1.5.3.分页查询

1.当我们在京东购物，浏览商品列表的时候，由于数据特别多，一页显示不完，一页一页的进行显示，这就是分页查询
2.SELECT 字段列表 FROM 表名 LIMIT M, N
说明：
limit是分页查询关键字
M表示开始行索引，默认是0
N表示查询条数
#起始索引，数据条数;每页的起始索引从:(当前页数-1)*每页数据条数
#总页数:(总条数+每页的数据条数-1)/每页的数据条数
#获取当前产品中，类别为c002的产品里，价格最低的2件商品
SELECT *FROM product WHERE category_id='c002' ORDER BY price ASC LIMIT 0,2;
3.#去重查询 distinct：
SELECT DISTINCT *from 表名;

1.5.4.多表查询

1.5.4.1.外键约束

1.外键约束相关：就是用来保证数据的完整性和安全性
2.添加外键约束:
ALTER TABLE employee add CONSTRAINT fk01 FOREIGN key(did) references dept(did);
2.删除外键约束:
ALTER TABLE employee DROP FOREIGN KEY fk01;

1.5.4.2.连接查询

1.内连接查询：求交集
SELECT 字段 FROM 左表 INNER JOIN 右表 ON 左表和右表的连接规则
2.左连接查询：保留左侧表信息，求交集
SELECT 字段列表 FROM 左表 LEFT OUTER JOIN 右表 ON 左表和右表的连接规则
3.右连接查询：保留右侧表信息，求交集
SELECT 字段列表 FROM 左表 RIGHT OUTER JOIN 右表 ON 左表和右表的连接规则
4.自关联查询：自连接查询必须对表起别名
SELECT *FROM areas as city INNER JOIN areas as prov on prov.id=city.pid WHERE prov.title='新乡市';
5.子查询：
SELECT *FROM areas WHERE  pid=(SELECT id FROM areas WHERE title='河南省');
6.拓展之case when 
case when  字段逻辑判断 then 替换值   
	 when 字段逻辑判断  then 替换值
	 esle  替换值
	 end   新的字段名称
7.case 字段 when  值   then 替换值
 		   when  值   then 替换值
 		   else  替换值
 		   end 	新字段名称

1.6.窗口函数的基本用法

1.窗口函数的用法有:聚合函数+窗口函数
SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table
SELECT first_name,last_name, salary,sum(salary) OVER () as totalSalary FROM employee;
SELECT *,dense_rank() OVER (PARTITION BY country ORDER BY salary) FROM hero ;其中partition一般用来分组,将每一组的结果进行排序
排序函数+窗口函数
#排序函数(row_number,rank,dese_rank)
# row_number:1,2,3,4
# rank:1,1,3,4
# des_rank:1,1,2,3
SELECT
name,genre,updated,
   row_number() OVER (ORDER BY updated desc) rn,
   rank() OVER (ORDER BY updated desc) rk,
   dense_rank() OVER (ORDER BY updated desc) dk FROM game;

1.7.可视化ETL平台kettle

1.7.1.kettle的介绍

1.ETL（Extract-Transform-Load的缩写，即数据==抽取、转换、装载==的过程）
2.kettle 是纯 java 开发，开源的 ETL工具，用于数据库间的数据迁移 。可以在 Linux、windows、unix 中运行。有图形界面，也有命令脚本还可以二次开发。
kettle 的官网是 https://community.hitachivantara.com/docs/DOC-1009855
github 地址是 https://github.com/pentaho/pentaho-kettle
3.kettle环境搭建
jdk版本：jdk1.8.0版本
kettle版本：pdi-ce-9.0版本
MySQL版本：MySQL-8.0
4.进入解压后的文件夹data-integration,双击Spoon.bat，启动kettle。

1.7.1.txt数据转换为Excel数据

1.新建---转换---保存给文件起一个名字
2.点击左上角的核心对象,将输入组件和输出组件双击,并且添加到平台上
3.双击文本输入进入组件配置--点击浏览按钮--选中txt文件---增加---内容---分割符号---编码格式---字段----获取字段----修改响应的格式----预览查看数据----确定
双击输出组件---内容---浏览---给文件起名字---字段---获取字段---修改响应的格式--确定----执行

1.7.2.Excel数据转换MySQL数据

1.在dataGrip中右键点击mysql连接选择query console
2.建立一个数据库名称为kettle_demo
3.在kettle中添加mysql驱动mysql-connector-java-8.0.13.jar导入到 data-integration/lib中。
4.data-integration\simple-jndi\jdbc.properties 文件编辑，在末尾加上连接信息
MYSQL_DB/type=javax.sql.DataSource  
MYSQL_DB/driver=com.mysql.cj.jdbc.Driver
MYSQL_DB/url=jdbc:mysql://localhost:3306/kettle_demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT
MYSQL_DB/user=root
MYSQL_DB/password=123456
5.记录下MYSQL_DB的名字后面需要用到
6.新建转换,点击核心对象---excel输入---表输出--双击excel输入组件---表格类型选择Excel 2007 XLSX(Apache POI)----浏览---excel'文件---增加----工作表---选择sheet1 移到右边---点击字段----获取头部字段数据-----修改相应的格式---预览----确定--双击表输入---新建---一般----连接名称自己写个----连接类型选择mysql---连接方式选择JNKI---JNDI名称设置为之前记录的名称MYSQL_DB---点击确定---目标表自己写个---点击SQL---修改相应的字段---执行完退出---确定---执行.

1.7.3.MySQL表之间转换

1.在转换中,点击主对象树---DB连接---右键共享---新建---转换---将组件添加到平台中--双击表输入---获取SQL查询语句---选择表---否---预览---输出双击组件---目标表--SQL--执行---确定--运行
2插入更新---新建--转换--找到两个组件输入表和插入更新---双击输入表---选择表---双击插入更新表---目标表预览---添加关键字,设置id,比价符号为=,流里的字段为id--获取更新字段--确定---运行
3.switch-case:新建-转换-选择组件,表输入,Switchcase--再拖出两个excel-按住shift键盘将线连接并且选择create  a new for this step-双击表输入--获取sql查询-确定-双击switchcase-switch字段选择gender-值写男,女确定-字段-获取字段-双击excel-浏览写下名字-执行.

1.8.Pytheon与mysql交互-pymysql模块

1.8.1.分表操作

1.分表操作步骤:
第一步:创建商品种类表
第二步:同步数据到商品种类表中
第三步:更新商品信息表数据
第四步:修改商品信息表表结构
2.创建分类表:
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null);
(1)将分组结果写入到goods_cates商品种类数据表中
insert into goods_cates (name) select cat_name from goods group by cate_name;
(2)将商品表中的分类名称更改为分类名称对应的分类id,注意:通过goods_cates数据表来更新goods表
update goods g inner join goods_cates gc on g.cate_name=gc.name set g.cate_name=gc.id
(3)修改goods表中的cate_name 名称和类型
alter table goods change cate_name cat_id int unsigned not null;

1.8.2.pymysql查操作

1.操作步骤:
(1)导入模块 :pymysql
(2)创建连接:conn=connect(参数列表)
(3)创建游标:cur=conn.cursor()
(4)执行sql:cur.execute(sql)
(5)关闭游标:cur.close()
(6)关闭连接:conn.close()
代码实例:

import pymysql
# 创建和数据库服务器的连接 
conn = pymysql.connect(
	host='localhost', 
	port=3306, 
	user='root', 
	password='mysql',
	database='python_test_1', 
	charset='utf8')
# 获取游标
cursor = conn.cursor()
# 创建sql语句
sql = """select * from hero;"""
# 数据库中受影响的的数据行数
row_count = cursor.execute(sql)
print("ＳＱＬ语句执行影响的行数%d" % row_count)
# 取出结果集中一行  返回的结果是一行数据　如: (1, '妲己', 2)
data1 = cursor.fetchone()
print(data1)
# 取出结果集中的所有数据　　返回的结果是所有数据　
# 如: ((第一行数据),(第二行数据),(第三行数据), ...)
# ((1, '妲己', 2), (2, '李白', 1), (3, '程咬金', 3), (4, '亚瑟', 5), (5, '荆轲', 99))
data2 = cursor.fetchall()
print(data2)
data=cursir.fetchmany(3)#获取多条数据
# 也可以使用for循环遍历数据
for line in cursor.fetchall():
    print(line)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

1.8.3.pymysql增删改操作

1.代码实例

import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='mysql',db='python_test_1', charset='utf8')
# 通过连接获取游标
cursor = conn.cursor()
# 删除数据
sql_1 = "delete from hero where id = 5;"
# 增加数据
sql_2 = "insert into hero (name) values ('西部大坏蛋孙悟空');"
# 修改数据
sql_3 = "update hero set kongfuid=444 where id = 4;"
# 执行相应的sql语句即可
row_count = cursor.execute(sql_1)
print("ＳＱＬ语句执行影响的行数%d" % row_count)
# 回滚数据到什么都不做的状态 即撤销刚刚的修改
# conn.rollback()
# 提交数据到数据库
conn.commit()
# 关闭游标和连接
cursor.close()
conn.close()

1.8.4.Sql注入问题

	1.后台提交恶意的数据和SQL进行字符串的方式拼接,从而影响了SQL语句的含义,最终产生数据泄露现象,防止sql注入

代码实例:
from pymysql import connect
# 创建Connection连接
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# 获取用户想要查询的物品名称
find_name = input("请输入物品名称：")
# sql语句
# 注意: 
# 此处不同于python的字符串格式化，必须全部使用%s占位
# 所有参数所需占位符外不需要加引号
sql = 'select * from goods where name=%s'
# 安全的方式
# 构造参数列表 
params = [find_name]
# 执行select语句
# 注意:
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 
count = cur.execute(sql, params)
# 获取查询的结果
result = cur.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cur.close()
# 关闭Connection对象
conn.close()