MySQL

MySQL的基础使用

Mysql的链接串

# -h mysql主机ip
# -P mysql的服务端口
# -u mysql用户名
# -p mysql用户密码
mysql -h101.43.211.36 -P3306 -ueuansu -p

基础的mysql语句

-- 查看有哪些数据库
show databases;
-- 使用某个数据库
use <database>;
-- 退出mysql的命令行环境
exit;

SQL的注释：

单行注释：-- 注释内容
单行注释：# 注释内容
多行注释：/* 注释内容 */

SQL基础和DDL

SQL的概述

SQL：Structured Query Lanuage，结构化查询语言，用于访问和处理数据库的标准计算机语言。

SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。

经过多年发展，SQL已经成为数据库领域统一的数据操作标准语言。

SQL语言的分类

由于数据库管理系统（数据库软件）功能非常多，不仅仅是存储数据库，换药包含：数据的管理、表的管理、库的管理、账户管理、权限管理等等。

操作数据库的SQL语言可以基于功能，划分为4类：

数据定义：DDL（Data Definition Language），数据库的创建、删除，表的创建删除等。
数据操纵：DML（Data Manipulation Language），新增数据、修改数据、删除数据等。
数据控制：DCL（Data Control Language），新增用户、删除用户、密码修改、权限管理等。
数据查询：DQL（Data Query Language），基于需求查询和计算数据。

SQL-DDL

-- 创建数据库
create database 数据库名称[charset utf8];
-- 删除数据库
drop database 数据库名称;
-- 查看当前使用的数据库
select database();
-- 查看数据库下有哪些表
show tables;
-- 创建表
create table 表名称(
    列名称 列类型,
    ...
);
-- 删除表
drop table 表名称;
drop table if exists 表名称;

SQL-DML

数据插入 INSERT

-- 基础语法
INSERT INTO 表[(列1, 列2, ..., 列N)] VALUES (值1, 值2, ..., 值N)[, (值1, 值2, ..., 值N), ..., (值1, 值2, ..., 值N)]
-- 示例
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    age INT
);
-- 仅插入id列数据
INSERT INTO student(id) VALUES(10001), (10002), (10003);
-- 插入全部列数据
INSERT INTO student(id, name, age) VALUES(10004, '南歌', 18), (10005, 'EuanSu', 18), (10006, '青山', 18);
-- 插入全部列数据，快捷写法
INSERT INTO student VALUES(10007, '南歌', 18), (10008, 'EuanSu', 18), (10009, '青山', 18);

数据删除 DELETE

-- 基础语法
DELETE FROM 表名称 WHERE 条件判断
-- 示例
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    age INT
);
-- 向表中快捷插入数据
INSERT INTO student VALUES(10001, '南歌', 18), (10002, 'EuanSu', 19), (10003, '青山', 20);
-- 删除name为南歌的数据
DELETE FROM student WHERE name='南歌';
-- 删除age大于19的数据
DELETE FROM student WHERE age>19;

数据更新 UPDATE

-- 基础语法
UPDATE 表名 SET 列=值 [WHERE 条件判断];
-- 示例
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    score INT
);
-- 向表中快捷插入数据
INSERT INTO student VALUES(10001, '南歌', 100), (10002, 'EuanSu', 98), (10003, '青山', 99);
-- 更新所有数据
UPDATE student SET score=60;
-- 更新指定条件的数据
UPDATE student SET score=100 WHERE name='南歌';

SQL-DQL

基础查询

-- 基础语法
SELECT 字段列表|* FROM 表
-- 演示
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    score INT
);
-- 向表中快捷插入数据
INSERT INTO student VALUES(10001, '南歌', 100), (10002, 'EuanSu', 98), (10003, '青山', 99);
-- 查询id和name两个列
SELECT id, name FROM student;
-- 查询全部列
SELECT id, name, score FROM student;
-- 查询全部列，快捷写法
SELECT * FROM student;

基础数据查询 - 过滤

-- 基础语法
SELECT 字段列表|* FROM 表 WHERE 条件判断
-- 演示
-- 查询id和name两个列，id<=10002
SELECT id, name FROM student WHERE id<=10002;
-- 查询全部列，name=南歌
SELECT * FROM student WHERE name="南歌";

分组聚合

分组聚合应用场景非常多，如：统计班级中，男生和女生的人数。

这种需求就需要：

按性别分组
统计每个分组的人数

这就称之为：分组聚合。

-- 基础语法
SELECT 字段|聚合函数 FROM 表 [WHERE条件] GROUP BY 列;
-- 聚合函数有：
-- SUM(列)，求和
-- AVG(列)，求平均值
-- MIN(列)，求最小值
-- MAX(列)，求最大值
-- COUNT(列|*)，求数量e
-- 演示
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    score INT,
    gender VARCHAR(2)
);
INSERT INTO student VALUES(10001, '南歌', 100, '男'), (10002, 'EuanSu', 98, '男'), (10003, '青山', 99, '男'), (10004, '西野七濑', 100, '女'), (10005, '正源司阳子', 100, '女'), (10006, '与田佑希', 100, '女');
-- 按照性别分组查询平均分数
SELECT gender, avg(score) FROM student group by gender;
-- 分组聚合函数查询
SELECT gender, avg(score), sum(score), min(score), max(score), count(*) FROM student group by gender;

排序分页

-- 基础语法
SELECT 列|聚合函数|* FROM 表
WHERE ...
GROUP BY ...
ORDER BY ... [ASC|DESC]
LIMIT n[, m]
-- 执行顺序： FROM->WHERE->GROUP BY和聚合函数->SELECT->ORDER BY->LIMIT
-- 示例
-- 升序排列
SELECT * FROM student where score > 98 order by score asc;
-- 降序排列
SELECT * FROM student where score > 98 order by score desc;

-- 查询2条数据
SELECT * FROM student limit 2;
-- 从第3条开始，查询2条
SELECT * FROM student limit 3, 2;

Python操作Mysql

创建到Mysql的数据库链接

import pymysql
# 连接到数据库
connect = pymysql.connect(host='localhost', port=3306, user='root', passwd='password',charset='utf8')
# 打印MySQL数据库软件信息
print(connect.get_server_info())
# 关闭到数据库的连接
connect.close()

执行SQL语句

import pymysql

# 连接到数据库
connect = pymysql.connect(host='localhost', port=3306, user='root', passwd='password', charset='utf8')
# 获取游标对象
cursor = connect.cursor()
# 选择数据库
connect.select_db('website')
# 使用游标对象，执行SQL语句
cursor.execute("SELECT * FROM student;")
# 获取查询结果
result: tuple = cursor.fetchall()
for row in result:
    print(row)
# 关闭到数据库的连接
connect.close()

commit提交

import pymysql

# 连接到数据库
connect = pymysql.connect(host='101.43.211.36', port=3306, user='euansu', passwd='suwenhui123@', charset='utf8')
# 获取游标对象
cursor = connect.cursor()
# 选择数据库
connect.select_db('website')
# 使用游标对象，执行SQL语句
cursor.execute("INSERT INTO student VALUES(10007, '斋藤飞鸟', 100, '女')")
# 通过commit确认
connect.commit()
# 关闭连接
connect.close()

自动commit

import pymysql

# 连接到数据库
connect = pymysql.connect(host='localhost', port=3306, user='root', passwd='password', charset='utf8', autocommit=True)
# autocommit，自动提交
# 获取游标对象
cursor = connect.cursor()
# 选择数据库
connect.select_db('website')
# 使用游标对象，执行SQL语句
cursor.execute("INSERT INTO student VALUES(10008, '有村架纯', 100, '女')")
# 关闭连接
connect.close()

MySQL性能测试工具

mysqlslap 
# --concurrency 	并发数据量
# --iterations  	要运行测试多少次
# --query			要执行的SQL
# --create-schema	测试的database

mysqlslap -hlocalhost -P3306 -uroot -p'password' --concurrency=2 --iterations=3 --create-schema=test --query=test.sql

MySQL基础使用