MySQL函数与字段约束

MySQL函数与字段约束

• 本文用python执行SQL语句。
  导入python库

import json
import pymysql
from pymysql.converters import escape_string
import pandas as pd

连接数据库

#连接数据库
conn=pymysql.connect(
    host="localhost",
    port=3306,#端口号
    user="root",#数据库用户
    password="123456",#数据库密码
    #database="world"#要连接的数据库名称
)
cur=conn.cursor()

跳转到指定数据库

sql = """USE PLJNB"""
cur.execute(sql)
cur.fetchall()

SQL函数

字符串函数

函数关键字	作用
CONCAT(S1,S2,…,Sn)	字符串拼接（将S1,S2,…,Sn拼接为一个字符串）
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充
TRIM(str)	去掉字符串头尾的空格
SUBSTRING(str,start,len)	返回字符串str从start位置起的len个长度的字符串

• 例1：

# 字符串转为小写
sql = """SELECT SUBSTRING('HELLO MYSQL',1,5);"""
cur.execute(sql)
cur.fetchall()

• 例2：

# 用0左填充id，使id长度为3
sql = """UPDATE employee SET workno = LPAD(id,3,0);"""
cur.execute(sql)
conn.commit()
cur.fetchall()

数值函数

函数关键字	作用
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机数
ROUND(x,y)	求参数x的四舍五入值，保留y位小数

例：

# 生成6位随机验证码
sql = """SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');"""
cur.execute(sql)
cur.fetchall()

日期函数

函数关键字	作用
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间加上一个时间间隔expr后的时间
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

• 例：

# 生成18随机身份码
for i in range(0,8):
    sql = """UPDATE employee SET
            idcard = RPAD('"""+ str(int(np.random.rand()*(10**18))) +"""',18,'0')
            WHERE id = """ + str(111+i) +""";"""
    cur.execute(sql)
    conn.commit()
    cur.fetchall()

流程控制函数

函数关键字	作用
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [VAL1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，…否则返回默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr的值等于val1，返回res1，…否则返回默认值

• 例：

sql = """SELECT name,
    (CASE gender WHEN '女' THEN '美女' WHEN '男' THEN '帅哥' ELSE '外星人' END) as '性别'
    FROM employee;"""
cur.execute(sql)
cur.fetchall()

SQL约束

• 约束是作用于表中字段上的规则，用于限制存储在表中的数据
• 约束保证数据库中数据的正确性、有效性和完整性
• 可以在创建表、修改表时添加约束

约束类别

约束类别	关键字	作用
非空约束	NOT NULL	该字段不能为NULL
唯一约束	UNIQUE	保证该字段的所有数据都是唯一的
主键约束	PRIMARY KEY	主键是一行数据的唯一标识
默认约束	DEFAULT	保存数据时，若未指定该字段的值，则采用默认值
检查约束	CHECK	保证字段值满足某一条件
外键约束	FOREIGN KEY	让两张表数据之间建立连接，保证数据的一致性

• 例：

sql = """
        CREATE TABLE user(
            id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
            name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
            age INT CHECK(age > 0 AND age <= 120) COMMENT '年龄',
            status char(1) DEFAULT '1' COMMENT '状态',
            gender char(1) COMMENT '性别',
            dept_id INT COMMENT '部门ID',
            update_time DATETIME COMMENT '更新数据时间',
            create_time DATETIME COMMENT '创建用户时间'
        )COMMENT '用户表';
"""
cur.execute(sql)
conn.commit()
cur.fetchall()

外键约束

添加外键

       CREATE TABLE 表名(
            字段名 数据类型，
            ...
            [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
       );

或

	ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 
	FOREIGN KEY(外键字段名) 
	REFERENCES 主表(主表列名);

• 例：

1. 建立主表

# 建立主表
sql = """
        CREATE TABLE dept(
            id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
            name VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称'
        )COMMENT '部门表';
"""
cur.execute(sql)
conn.commit()
cur.fetchall()

2. 添加外键约束

# 添加外键约束
sql = """
       ALTER TABLE user ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id)
       REFERENCES dept(id);
"""
cur.execute(sql)
cur.fetchall()

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

• 例：

sql = """
       ALTER TABLE user DROP FOREIGN KEY fk_emp_dept_id;
"""
cur.execute(sql)
cur.fetchall()

外键删除/更新行为

行为关键字	作用
NO ACTION	不允许删除、更新父表
RESTRICT	同上
CASCADE	修改/删除父表记录，同时修改/删除对应子表中的记录
SET NULL	删除父表记录，对应子表记录设为NULL（需允许取NULL）
SET DEFAULT	修改父表时，对应子表外键设为默认值（Innodb不支持）

设置外键删除/修改行为的语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) 
REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

• 例：

sql = """
       ALTER TABLE user ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id)
       REFERENCES dept(id) ON UPDATE CASCADE ON DELETE SET NULL;
"""
cur.execute(sql)
cur.fetchall()