这篇文章相对入门地介绍了MySQL里的相关语法和使用，并结合Python + Flask框架初步探索里网页开发。

1. MySQL入门

1.1 启动MySQL:

1
mysql -u root -p

1.2 查看已有数据库:

1
show databases;
1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

1.3 创建数据库:

1
create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

1.4 删除数据库:

1
drop database 数据库名;

1.5 进入数据库:

1
use 数据库名;

1.6 查看数据库下所有数据表:

1
show tables;
1
2
3
4
5
6
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
+----------------+

1.7 创建表:

1
2
3
4
5
create table 表名(
  列名 类型,
  列名 类型,
  列名 类型
) default charset=utf8;
  • e.g.
1
2
3
4
5
create table tb1(
    id int,
    name varchar(16),
    age int
) default charset=utf8;
  • 不能为空/允许为空
1
2
3
4
5
create table tb1(
    id int,
    name varchar(16) not null, -- 不能为空
    age int null        			 -- 可以为空
) default charset=utf8;
  • 默认值
1
2
3
4
5
create table tb1(
    id int,
    name varchar(16) not null,
    age int default 3       			 -- 设置默认为3
) default charset=utf8;
  • 主键
1
2
3
4
5
create table tb1(
    id int primary key,       -- 不能为空，不能重复
    name varchar(16),
    age int
) default charset=utf8;
  • 一般设置，主键+自增 标准
1
2
3
4
5
create table tb1(
    id int auto_increment primary key,
    name varchar(16),
    age int
) default charset=utf8;

1.8 删除表

1
drop table 表名;

1.9 展示表信息

1
desc 表名;
1
2
3
4
5
6
7
8
9
mysql> desc tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

2 常见数据类型

2.1 int整型

  • tinyint
1
2
有符号，取值范围：-128～127（有正有负）[默认]
无符号，取值范围：0～255（只有正）
  • 有符号，取值范围：-128～127（有正有负）[默认]
1
2
3
4
5
create table tb2(
	id int auto_increment primary key,
	name varchar(16),
	age tinyint
) default charset=utf8;
  • 无符号，取值范围：0～255（只有正）
1
2
3
4
5
create table tb2(
	id int auto_increment primary key,
	name varchar(16),
	age tinyint unsigned
) default charset=utf8;
  • int
1
2
3
4
5
int
有符号，-2147483648～2147483647

int unsigned
无符号，0～4294967295
  • bigint
1
2
3
4
5
bigint
有符号，范围更大

bigint unsigned
无符号，范围更大

练习：整型

  • 创建表
1
2
3
4
5
create table tb2(
	id bigint auto_increment primary key,
  salary int,
  age tinyint
) default charset=utf8;
  • 插入数据
1
2
3
4
insert into tb2(salary, age) values(10000, 18);
insert into tb2(salary, age) values(20000, 28);

insert into tb2(salary, age) values(30000, 38), (40000,40);
  • 查看数据
1
select * from tb2;
1
2
3
4
5
6
7
8
9
10
mysql> select * from tb2;
+----+--------+------+
| id | salary | age  |
+----+--------+------+
|  1 |  10000 |   18 |
|  2 |  20000 |   28 |
|  3 |  30000 |   38 |
|  4 |  40000 |   40 |
+----+--------+------+
4 rows in set (0.00 sec)

2.2 浮点数

  • float

  • double

  • decimal

1
2
准确的小数，m是数字总个数（负号不算），d是小数后个数。
m最大为65，d最大为30

例如

1
2
3
4
5
6
7
8
9
10
create table tb3(
	id int primary key auto_increment,
	salary decimal(8, 2)
) default charset=utf8;

insert into tb3(salary) values(1.28);
insert into tb3(salary) values(5.289);
insert into tb3(salary) values(1.282); -- 整数部分不能超过m位

select * from tb3;
1
2
3
4
5
6
7
8
9
mysql> select * from tb3;
+----+--------+
| id | salary |
+----+--------+
|  1 |   1.28 |
|  2 |   5.29 |
|  3 |   1.28 |
+----+--------+
3 rows in set (0.00 sec)

2.3 字符串

  • char(m)
1
2
3
4
5
6
7
8
定长字符串，速度快
m<=225

-- 无论多长，固定11位存储【超出报错】
create table tb4(
  id int auto_increment primary key,
  mobile char(11)
) default charset=utf8;
  • varchar(m)
1
2
3
4
5
6
7
8
变长字符串，节省空间
m<=65535bit

-- 真实多长，按照多长存储【超出报错】
create table tb4(
  id int auto_increment primary key,
  mobile varchar(11)
) default charset=utf8;
  • text
1
2
3
4
5
6
7
变长的大字符串，可以有65535（2**16-1）个字符，一般用于长文本

create table news(
  id int auto_increment primary key,
  title varchar(128),
  content text
) default charset=utf8;
  • mediumtext
1
2**24-1
  • longtext
1
2**32-1

2.4 时间

  • datetime
1
YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)
  • date
1
YYYY-MM-DD (1000-01-01/9999-12-31)

练习题：用户表

1
2
3
4
5
6
7
8
9
create table account(
	id int primary key auto_increment,
  name varchar(64) not null,
  password char(64) not null,
  email varchar(64) not null,
  age tinyint,
  salary decimal(10, 2),
  ctime datetime
) default charset=utf8;
1
insert into account(name, password, email, age, salary, ctime) values("Mike", "123", "xx@live.com", 19, 1000.20, "2023-12-11 11:11");
1
select * from account;
1
2
3
4
5
6
7
mysql> select * from account;
+----+------+----------+-------------+------+---------+---------------------+
| id | name | password | email       | age  | salary  | ctime               |
+----+------+----------+-------------+------+---------+---------------------+
|  1 | Mike | 123      | xx@live.com |   19 | 1000.20 | 2023-12-11 11:11:00 |
+----+------+----------+-------------+------+---------+---------------------+
1 row in set (0.01 sec)

3. 数据行操作

3.1 增加数据

1
2
3
4
insert into 表名(列1, 列2) values(值, 值);
insert into 表名(列1, 列2) values(值, 值);

insert into 表名(列1, 列2) values(值, 值), (值, 值), (值, 值), (值, 值);

3.2 删除数据

1
2
3
4
delete from 表名;
delete from 表名 where 条件;
delete from 表名 where 条件 and 条件;
delete from 表名 where 条件 or 条件;
1
2
3
4
5
-- 把account全部删除
delete from account;

-- 删除account表中id=3的行
delete from account where id=3;

3.3 修改数据

1
2
3
update 表名 set 列=值;
update 表名 set 列=值, 列=值;
update 表名 set 列=值 where 条件;
1
2
3
4
update account set password="hahahah";
update account set email="qwqwqw" where id>5;

update account set age=age+3 where id>5;

3.4 查询数据

1
2
3
select * from 表名;
select 列名, 列名 from 表名;
select 列名, 列名 from 表名 where 条件;
1
2
3
select * from account;
select name, email from account;
select name, email from account where id>3;

4. 员工管理案例

4.1 要求

  • 使用mysql内置工具

    • 创建数据库：unicom 以建立联通员工管理数据库为例

    • 数据表：admin

1
2
3
4
5
6
表名：admin
列：
    id：主键，自增
    username：字符串，不为空
    password：字符串，不为空
    mobile：字符串，不为空

​

  • python代码实现
    • 添加用户
    • 删除用户
    • 查看用户
    • 更新用户信息

4.2 mysql创建

1
create database unicom DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 
1
2
3
4
5
6
7
8
use unicom;

create table admin(
  id int not null auto_increment primary key,
  username varchar(16) not null,
  password varchar(64) not null,
  mobile char(11) not null
) default charset=utf8;

4.3 python操作

1
pip install pymysql
4.3.1 简单创建和操作
1
2
3
4
5
6
7
8
9
10
11
12
13
import pymysql

# 1. 链接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="<PASSWORD>", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2. 发送指令
cursor.execute('insert into admin(username,password,mobile) values("mike","abc123","12345678901")')
conn.commit()

# 3. 关闭链接
cursor.close()
conn.close()
1
2
3
4
5
6
mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |
+----+----------+----------+-------------+
  • 格式化发送指令（列表）
1
2
3
4
# 2. 格式化发送指令[]
sql = 'insert into admin(username,password,mobile) values(%s,%s,%s)'
cursor.execute(sql, ["Ann", "qwe456", "09876543211"])
conn.commit()
1
2
3
4
5
6
7
mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |
|  3 | Ann      | qwe456   | 09876543211 |
+----+----------+----------+-------------+
  • 格式化发送指令（字典）
1
2
3
4
# 2. 格式化发送指令{}
sql = 'insert into admin(username,password,mobile) values(%(n1)s,%(n2)s,%(n3)s)'
cursor.execute(sql, {"n1":"Cindy", "n2":"zxc789", "n3":"56473829101"})
conn.commit()
1
2
3
4
5
6
7
8
mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |
|  3 | Ann      | qwe456   | 09876543211 |
|  5 | Cindy    | zxc789   | 56473829101 |
+----+----------+----------+-------------+
4.3.2 在python终端动态创建
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymysql

while True:
    print("请输入信息，输入q退出")
    user = input("用户名：")
    if user == "q":
        break
    pwd = input("密码：")
    mobile = input("电话号码：")

    # 1. 链接mysql
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="Silence2004", charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2. 发送指令
    sql = 'insert into admin(username,password,mobile) values(%s,%s,%s)'
    cursor.execute(sql, [user, pwd, mobile])
    conn.commit()

    # 3. 关闭链接
    cursor.close()
    conn.close()
4.3.3 查询数据
1
2
3
4
5
6
# 第1步和第3步不变

# 第2步，发送指令
cursor.execute("select * from admin")  # 指令就是mysql命令，格式和使用方式完全相同
data_list = cursor.fetchall()
print(data_list)
1
2
3
4
5
6
7
# data_list
[
 {'id': 1, 'username': 'mike', 'password': 'abc123', 'mobile': '12345678901'}, 
 {'id': 3, 'username': 'Ann', 'password': 'qwe456', 'mobile': '09876543211'}, 
 {'id': 5, 'username': 'Cindy', 'password': 'zxc789', 'mobile': '56473829101'}, 
 {'id': 6, 'username': 'Kage', 'password': 'slefln', 'mobile': '1412344'}
]
  • 格式化查询
1
2
3
4
# 第2步，发送指令
cursor.execute("select * from admin where id > %s", [2, ])
data_list = cursor.fetchall()
print(data_list)
1
2
3
4
5
[
 {'id': 3, 'username': 'Ann', 'password': 'qwe456', 'mobile': '09876543211'}, 
 {'id': 5, 'username': 'Cindy', 'password': 'zxc789', 'mobile': '56473829101'}, 
 {'id': 6, 'username': 'Kage', 'password': 'slefln', 'mobile': '1412344'}
]
  • fetchone()
1
2
3
4
# 第2步，发送指令
cursor.execute("select * from admin where id > %s", [2, ])
res = cursor.fetchone()  # 第一条数据
print(res)
1
{'id': 3, 'username': 'Ann', 'password': 'qwe456', 'mobile': '09876543211'}
4.3.4 删除数据
1
2
3
4
5
# 第1步和第3步不变

# 第2步，发送指令
cursor.execute("delete from admin where id = %s", [5, ])
conn.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |
|  3 | Ann      | qwe456   | 09876543211 |
|  5 | Cindy    | zxc789   | 56473829101 |  -- 删除
|  6 | Kage     | slefln   | 1412344     |
|  7 | John     | adkadkaw | 134231      |
+----+----------+----------+-------------+

mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |
|  3 | Ann      | qwe456   | 09876543211 |
|  6 | Kage     | slefln   | 1412344     |
|  7 | John     | adkadkaw | 134231      |
+----+----------+----------+-------------+
4.3.5 修改数据
1
2
3
4
5
# 第1步和第3步不变

# 第2步，发送指令
cursor.execute("update admin set mobile=%s where id=%s", [123, 1, ])
conn.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 12345678901 |  -- 修改前
|  3 | Ann      | qwe456   | 09876543211 |
|  6 | Kage     | slefln   | 1412344     |
|  7 | John     | adkadkaw | 134231      |
+----+----------+----------+-------------+

mysql> select * from admin;
+----+----------+----------+-------------+
| id | username | password | mobile      |
+----+----------+----------+-------------+
|  1 | mike     | abc123   | 123         |  -- 修改后
|  3 | Ann      | qwe456   | 09876543211 |
|  6 | Kage     | slefln   | 1412344     |
|  7 | John     | adkadkaw | 134231      |
+----+----------+----------+-------------+

4.4 总结

  • 在新增 删除 修改一定要commit
1
2
cursor.execute("..")
conn.commit()
  • 在查询时，不需要commit，但要fetchall/fetchone
1
2
3
4
5
6
cursor.execute("..")
# 第一条数据，字典，无数据时是空列表
ans1 = cursor.fetchone()

# 所有数据，元素为字典的列表，无数据时是None
ans2 = cursor.fetchall()
  • 不要用python的字符串格式化，一定要execute
1
2
cursor.execute("...%s   ...%s   ...%s", ["..", "..", "..", ])
conn.commit()

5. 案例：Flask + MySQL

5.1 新增用户

  • 网页/add/user
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h1>添加用户</h1>
<form method="POST" action="/add/user">
    <input type="text" name="user" placeholder="用户名">
    <input type="password" name="pwd" placeholder="密码">
    <input type="text" name="mobile" placeholder="手机号">
    <input type="submit" value="提 交">
</form>
</body>
</html>
  • python操作，链接mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)

@app.route('/add/user', methods=['GET', 'POST'])
def add_user():
    if request.method == 'GET':
        return render_template("add_user.html")

    user = request.form.get("user")
    password = request.form.get("pwd")
    mobile = request.form.get("mobile")

    # 1. 链接mysql
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="<PASSWORD>", charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2. 发送指令
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user,password,mobile])
    conn.commit()

    # 3. 关闭链接
    cursor.close()
    conn.close()

    return "添加成功"

if __name__ == '__main__':
    app.run(debug=True)
  • 结果
1
2
3
4
5
6
mysql> select * from admin;
+----+----------+----------+--------+
| id | username | password | mobile |
+----+----------+----------+--------+
|  8 | somebody | 123      | 12345  |
+----+----------+----------+--------+

5.2 查询并输出到页面

  • 网页show/user
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<h1>用户列表</h1>
<table border="1">
    <thead>
    <tr>
        <th>ID</th>
        <th>姓名</th>
        <th>密码</th>
        <th>手机号</th>
    </tr>
    </thead>

    <tbody>
    {% for item in data_list %}
    <tr>
        <td>{{ item.id }}</td>
        <td>{{ item.username }}</td>
        <td>{{ item.password }}</td>
        <td>{{ item.mobile }}</td>
    </tr>
    {% endfor %}
    </tbody>
</table>
</body>
</html>
  • python操作，赠加函数show_user
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@app.route('/show/user', methods=['GET', 'POST'])
def show_user():
    # 1. 链接mysql
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="<PASSWORD>", charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2. 发送指令
    sql = "select * from admin"
    cursor.execute(sql)
    data_list = cursor.fetchall()

    # 3. 关闭链接
    cursor.close()
    conn.close()

    return render_template("show_user.html", data_list=data_list)
  • 结果