MySQL数据库的高级使用

create table good_cates(
    id int not null primary key auto_increment;
    name varchar(50) not null;
);

-- 查询goods表中商品的分类信息
select cate_name from goods group by cate_name;
-- 将查询结果插入到good_cates表中
insert into good_cates(name) select cate_name from goods group by cate_name;
-- 添加移动设备分类信息
insert into good_cates(name) values('移动设备');

-- 查看goods表中的商品分类名称对应的商品分类id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;
-- 把该语句中from 后的语句理解为一张虚表  
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

-- 查询品牌信息 
select brand_name from goods group by brand_name;
-- 通过create table ...select来创建数据表并且同时插入数据
-- 创建商品分类表，注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table good_brands (     
id int unsigned primary key auto_increment,     
name varchar(40) not null) select brand_name as name from goods group by brand_name;

-- 将goods表中的品牌名称更改成品牌表中对应的品牌id
update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

-- 查看表结构
desc goods;
-- 通过alter table语句修改表结构
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

import pymysql
# 创建连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python', charset='utf8')
# 获取游标对象
cursor = conn.cursor()
# 查询 SQL 语句
sql = "select * from students;"
# 执行 SQL 语句 返回值就是 SQL 语句在执行过程中影响的行数
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)
# 取出结果集中一行数据,　例如:(1, '张三')
# print(cursor.fetchone())
# 取出结果集中的所有数据, 例如:((1, '张三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
    print(line)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

# -*- codeing = utf-8 -*-
# @Time : 2021/12/16 8:34 上午
# @Author : 李明辉
# @File : ithui_对数据库的增删改.py
# @Software : PyCharm
# 1、导包
import pymysql
if __name__ == '__main__':
    # 2、创建连接对象
    # connect = Connection = Connect 本质上是一个函数，使用这三个里面的任意一个函数都以创建一个连接对象
    # 1、host：服务器主机地址
    # 2、port：mysql数据库的端口号
    # 3、user：用户名
    # 4、password：密码
    # 5、database：操作的数据库
    # 6、charset：操作数据库的使用编码格式
    conn = pymysql.connect(host="localhost",
                           port=3306,
                           user="root",
                           password="LI1944575687",
                           database="python1",
                           charset="utf8")
    # 3、获取游标，目的就是要执行sql语句
    cursor = conn.cursor()
    # 准备sql
    # 对数据表完成添加、删除、修改操作，需要把修改的数据提交到数据库
    # sql = "insert into classes(name) values('python50');"
    # sql = "update classes set name = 'python45' where id=5;"
    sql = "delete from classes where id=5;"
    try:
        # 4、执行sql语句
        cursor.execute(sql)
        # 提交修改的数据到数据库
        conn.commit()
    except Exception as e:
        # 对修改的数据进行撤销,表示数据回滚
        conn.rollback()
    finally:
        # 5、关闭游标
        cursor.close()
        # 6、关闭连接
        conn.close()

# -*- codeing = utf-8 -*-
# @Time : 2021/12/16 9:02 上午
# @Author : 李明辉
# @File : ithui_防止sql注入多个参数的使用.py
# @Software : PyCharm
import pymysql
if __name__ == '__main__':
    # 2、创建连接对象
    # connect = Connection = Connect 本质上是一个函数，使用这三个里面的任意一个函数都以创建一个连接对象
    # 1、host：服务器主机地址
    # 2、port：mysql数据库的端口号
    # 3、user：用户名
    # 4、password：密码
    # 5、database：操作的数据库
    # 6、charset：操作数据库的使用编码格式
    conn = pymysql.connect(host="localhost",
                           port=3306,
                           user="root",
                           password="LI1944575687",
                           database="python1",
                           charset="utf8")
    # 3、获取游标，目的就是要执行sql语句
    cursor = conn.cursor()
    # 准备sql
    sql = "insert into students(name, age, gender, c_id) valuse(%s, %s, %s, %s);"
    try:
        # 4、执行sql语句
        # 1.sql语句
        # 2.执行sql语句的传入参数，参数类型可以树元组，列表，字典
        name = input("请输入姓名")
        age = input("请输入年龄")
        gender = input("请输入性别")
        c_id = input("请输入班级号")
        params = [name, age, gender, c_id]
        cursor.execute(sql, params)
        conn.commit()
    except Exception as e:
        conn.rollback()
    finally:
        # 5、关闭游标
        cursor.close()
        # 6、关闭连接
        conn.close()

-- 选择数据库
use jing_dong;
-- 查看goods表
show create table goods;
mysql root@(none):jing_dong> show create table goods;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| goods | CREATE TABLE `goods` (                                 |
|       |   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       |
|       |   `name` varchar(150) NOT NULL,                        |
|       |   `cate_id` int(10) unsigned NOT NULL,                 |
|       |   `brand_id` int(10) unsigned NOT NULL,                |
|       |   `price` decimal(10,3) NOT NULL DEFAULT '0.000',      |
|       |   `is_show` bit(1) NOT NULL DEFAULT b'1',              |
|       |   `is_saleoff` bit(1) NOT NULL DEFAULT b'0',           |
|       |   PRIMARY KEY (`id`)                                   |
|       | ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------+

-- 创建索引的语法格式
-- alter table 表名 add index 索引名[可选](列名, ..)
-- 给name字段添加索引
alter table classes add index my_name (name);

-- 删除索引的语法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以查看创表sql语句
show create table classes;
alter table classes drop index my_name;

create table test_index(title varchar(10));

from pymysql import connect
def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()
if __name__ == "__main__":
    main()
验证索引性能操作：
-- 开启运行时间监测：
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
-- 查看执行的时间：
show profiles;
-- 给title字段创建索引：
alter table test_index add index (title);
-- 再次执行查询语句
select * from test_index where title='ha-99999';
-- 再次查看执行的时间
show profiles;

-- 创建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);
-- 创建联合索引
alter table teacher add index (name,age);

-- 下面的查询使用到了联合索引
select * from stu where name='张三' -- 这里使用了联合索引的name部分
select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分 
-- 下面的查询没有使用到联合索引
select * from stu where age=10 -- 因为联合索引里面没有这个组合，只有 name | name age 这两种组合