mysql 数据库单表造数 数据库单表操作

1. SQL 表中记录操作

1.1. DML 操作表中数据

# 1. insert
insert into tablename (f1, f2, f3...,vn) values (v1, v2, v3...,vn);
# 除了数字类型 其他的都要用引号 时间类型

# 2. select
select * from tableName;

# 3. delete
delete from tableName where colName = existsValue;

# 一条一条删除表中所有记录  不建议这样使用  要想全删有效率更高的方法
delete from tableName;

# 一次性删除表中所有内容
truncate table tableName;

# 4. 更新表
update tableName set colName=avalue where anotherCol = existsValue;

# 更改多列
update tableName set col1=value1, col2=value2 where anoterCol=existsValue;

# 不用 where 限制条件 会将表中所有的记录相应列都改掉
update tableName set col1 = value1;

1.2. 查询

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组后条件
order by
    排序
limit
    分页限定

1.3. python 生成测试数据表

import random
import pymysql

def create_table():
    con = pymysql.connect(host="192.168.5.131", database="ubuntudb", port=3306, user="snado", password="654321")

    cursor = con.cursor()

    sql = """
        create table person(
            sid int primary key auto_increment,
            sname varchar(32) not null,
            age int(3),
            gender varchar(3),
            address varchar(32),
            math double(3,1),
            english double(3,1)
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
    """

    try:
        cursor.execute(sql)
    except Exception as e:
        con.rollback()
        print(e)
    finally:
        cursor.close()
        con.close()

name = ["张", "王", "赵", "李", "金", "马", "胡", "狗", "鸡", "秦"]
name2 = ["三", "四","五","六","塔","阿","钦", "雨","雪","晴","初","石"]
addr = ["北京","上海","广州","天津","深圳","香港","澳门","宜昌","成都","重庆"]
genders = ["男", "女"]
def get_random():
    return random.randint(0, 9)

record = []

for i in range(1000):
    sname = name[get_random()] + name2[get_random()]
    age = random.randint(18,30)
    gender = genders[random.randint(0,1)]
    address = addr[get_random()]
    math = random.randint(60,99)
    english = random.randint(60,99)
    record.append((sname, age, gender, address, math, english))

def insert_msg():
    con = pymysql.connect(host="192.168.5.131", database="ubuntudb", port=3306, user="snado", password="654321")
    cursor = con.cursor()

    sql = "insert into person (sname, age, gender, address, math, english) values (%s,%s,%s,%s,%s,%s)"

    try:
        # cursor.execute(sql, ("张三", 23, 85.5)) 插入一条数据
        cursor.executemany(sql, record)
        con.commit()
    except Exception as e:
        con.rollback()
        print(e)
    finally:
        cursor.close()
        con.close()

1.4. 简单查询

# 1. distinct  去重
select distinct address from person;

# 2.同一行 多列数据计算

select sname, math, english, math+english score from person

select sname, math, english, math+english score from person where sid = 1005;

# 3. 判断null值替换  sql里任何数与 null计算结果都为 null
select sname, math, english, ifnull(english, 0) + ifnull(math, 0) score from person; # 如果 english 或 math 为 null 用0替换

# 4. 给字段起别名 as 或 空格
select sname, math as 数学, english as 英语, ifnull(english, 0) + ifnull(math, 0) 总分 from person;

1.5. where 条件运算符

# 1.简单的比较运算 >, <, >=, <=, =, !=

select * from person where age >= 28;

select * from person where gender != "男";

select * from person where address = "北京";

# 2. and, or

select * from person where age = 24 and gender = "女";

select * from person where (age=24 and gender="女") or (age=25 and gender="男");

# 3. between, in

select * from person where gender = "女" and age>=23 and age<=25;
# between 简化
select * from person where gender="女" and age between 23 and 25;

select * from person where gender="女" and (age=22 or age=24 or age=26);
# in 简化
select * from person where gender="女" and age in (22, 24, 26);

# 4. 对 null 判断 is, is not
select * from person where math is null;

select * from person where math is not null;

# 5. 模糊查询 like   通配符 % 任意多个字符  _ 任意一个字符

select * from person where sname like "_三";

select * from person where sname like "%张%";  # 这种比较常用 找出所有带这个字的记录

1.6. 排序 order by field

select * from person order by math; # 默认升序排列 （asc）

select * from person order by math desc; # 按 math 字段 降序排列

# 多字段排序
select * from person order by math desc, english desc; # 当math一样时 再判断 english 按降序排列

select *, ifnull(math, 0) + ifnull(english, 0) score from person order by score desc;

1.7. 聚合函数

• count, max, min, sum, avg, 聚合函数会忽略 null 值

# 1. count
select count(math) from person; # 聚合函数会忽略 null 值

select count(ifnull(math, 0)) from person;

select count(*) from person;
# 求某一列 最大 最小 平均值
select max(math) from person;

select min(math) from person; # 忽略 null

select avg(math) from person;

select avg(english), sum(english) from person;

1.8. 分组 group

select gender, count(sname) from person group by gender; # gender 为分组依据 count为要查询内容

select gender, avg(ifnull(math, 0) + ifnull(english,0)) avg_score from person group by gender;

# 分组之前条件筛选
select gender , count(english) from person where english > 80 group by gender; # 查看分数大于80分的 男女同学各有多少人

# 筛选分组之后的结果
select gender, count(english) eng_count from person where english > 90 group by gender having eng_count > 100;

1.9. 分页 limit 数据库不一样 不一定是 limit limit仅针对 mysql

select * from person limit 0, 10;  # 从第 0 条开始 查 10条记录

select * from person limit 10, 10;  # 从第 10 条开始 查 10条记录

...

# 分页会配合前后台代码共同完成

1.10. 约束

1. primary key; # 主键约束
2. not null; # 非空约束
3. unique; # 唯一约束
4. foreign key; # 外键约束

###################### 1 主键约束
# 创建表 主键约束  一个表只有一个主键 非空且唯一
CREATE TABLE stu(
	id int PRIMARY KEY,
	sname VARCHAR(32)
);
# 删除主键约束
alter table stu drop primary key;
# 添加主键约束
alter table stu modify id int primary key;

####################### 2 非空约束
# 创建表时 非空约束
create table stu(
    id int,
    sname varchar(32) not null
);
# 取消非空约束
alter table stu modify sname varchar(32);
# 创建表后添加非空约束 表里相应列上必须没有 null 值才能加上
alter table stu modify sname varchar(32) not null;

####################### 3 唯一约束
# 创建表 唯一约束
create table stu(
	id int,
	mobile varchar(11) unique
);
# 删除唯一约束
alter table stu drop index mobile;
# 添加唯一约束
alter table stu modify mobile varchar(11) unique;

####################### 4 自动增长
CREATE TABLE stu(
	id int PRIMARY KEY auto_increment,
	sname VARCHAR(32)
);

alter table stu modify id int;  # 取消自动增长

alter table stu modify id int auto_increment; # 添加自动增长

• 外键约束 当两个表中的两列存在某种关系时 要用这种约束

CREATE TABLE class(
	class_id INT PRIMARY KEY,
	capacity INT
);

CREATE TABLE stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(32) NOT NULL,
	age INT,
	class INT,
	# 添加外键约束  学生表的 学生班级 与 编辑表的 班级编号 有对应关系 班级表称为主表 学生表称为从表
	CONSTRAINT stu_class_fk FOREIGN KEY (class) REFERENCES class(class_id)
);

# 删除外键
alter table stu drop foreign key stu_class_fk;

# 添加外键  添加时必须没有外键冲突才能添加成功
alter table stu add CONSTRAINT stu_class_fk FOREIGN KEY (class) REFERENCES class(class_id);

• 添加外键约束后 主表的行必须在子表没有对应数据时才能删除 子表添加外键列的值 必须在主表有行与之对应
• 比如 班级表删除 id=3的行 学生表必须 class没等于 3的数据 学生表添加数据 class的值必须在 班级表中有对应的 class_id
• 级联更新 删除

# 设置 级联更新 级联删除
alter table stu add CONSTRAINT stu_class_fk FOREIGN KEY (class) REFERENCES class(class_id) ON UPDATE CASCADE ON DELETE CASCADE;

# 当主表相对子表的外键发生变化时  子表会全部自动更新

# 当主表相对子表的外键删除时 子表所有相关的数据会被自动删除