MYSQL详细知识点

'''mysql -u root -p
#列----->字段
#行----->记录
#如果能够唯一标记某个字段：那么这就是主键
#####################数据库操作##########
#链接数据库
mysql -uroot -p
mysql -uroot -pmysql

#退出数据库
exit/quit/ctrl+d

#显示时间
select now();

#显示数据库
show databases;
#显示数据库版本
select version();
#创建数据库 create database 数据库名 charset=utf8;
create database python04;
create database python04new charset=utf8;
#查看创建数据库时的语句
show create database python04;
#删除数据库
drop database 数据库名;
drop database python04;
#查看当前使用的数据库
select database();
#使用数据库
use python04;


####################表操作##########

#查看当前数据库中所有的表
show tables;

#创建表
#auto_increment表示自动增长
#not null 表示不能为空
#primary key 表示主键
#default 默认值
create table 数据表名字(字段 类型 约束[字段 类型 约束]);
create table xxxx(id int primary key not null auto_increment, name varchar(30));

#desc 表名；  查看表信息

创建students表(id,name,age,high,gender,cls_id)
create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(30),
    age tinyin unsigned,
    high decimal(5,2),
    gender enum("男","女","中性","保密") default "保密",
    cls_id int unsigned
);

#插入数据
insert into students values(0,"老王",18,188.88,"男",0);
#查看表
select * from students;

#外键插入
alter table goods add foreign key (cate_id) references goods_cates(id);
#添加字段
alter table students add birthday datetime;

alter table 表名 change 原名 新名 类型及约束;     change用来字段重命名，不能修改字段类型和约束；

         modify不用来字段重命名，只能修改字段类型和约束；
alter table 表名 modify 列名 类型及约束;
#修改字段类型
alter table students modify birthday date;
#修改字段名字
alter table students change birthday birth date default "1997-01-01";
#删除字段
alter table students drop high;
#删除表
drop table 数据表;

show create table students;
#全列插入
insert into students values()
主键字段可以用0 null default来占位
#枚举中的下标从1开始 1---“男”  2---“女”。。
#部分插入
insert into students (name,gender) values ("小乔",2);
#多行插入
insert into students (name,gender) values ("大桥",2),("小乔",2);
#插入一个字段
insert into students(name) select cate_name from goods group by cate_name;
#数据修改
update students set gender=1;字段全部修改
update students set gender=1 where id=3;
update students set gender=1,age=22 where id=2;
#查询
select * from students;
select * from students where name="小李飞刀";
select * from students where age>20;

select name,age from students;#按列查看
select name as 姓名,gender as 性别 from students;#列名修改并显示

#删除数据
#物理删除
delete from students; 整个数据表中的所有数据都删除
delete from students where name="小李飞刀";
#逻辑删除
alter table students add is_delete bit default 0;#1表示已删除，0表示没删除
update students set is_delete=1 where id=6;

################################################查询##########################

#查询
#select * from 表名;
select * from students;#查询所有字段
select * from classes;

#select 列1，列2,...from 表名;
select name,age from students;#查询指定字段
#使用as给字段起别名
#select 字段 as 名字...from 表名;
select name as 姓名,age as 年龄 from students;
#select 表名.字段...from 表名;
select students.name,students.age from students;
#可以通多as给表起别名
#select 别名.字段 ...from 表名 as 别名;
select s.name,s.age from students as s;

#消除重复行
select distinct gender from students;

#条件查询
#比较运算符
#select ...from 表名 where ....
# > 查询大于18岁的信息
select * from students where age>18;
# < 查询小于18岁的信息
select * from students where age<18;
# = 查询年龄为18岁的所有学生的信息
# >=   <=

#逻辑运算符
#and  18到28之间的学生信息
select * from students where age>18 and age<28;
18岁以上的女性
select * from students where age>18 and gender="女";

#or 18以上或者身高过180以上
select * from students where age>18 or high>180
#not 不在18岁以上的女性
select * from students where not(age>18 and gender=2);

#模糊查询
#like  % 替换1个或者多个  _替换1个
#查询姓名中以“小”开始的名字
select name from students where name like "小%";
#查询姓名中有“小”所有的名字
select name from students where name like "%小%";
#查询有2个字的名字
select name from students where name like "__";
#查询至少有两个字的名字
select name from students where name like "__%";
#rlike 正则
#查询以 周开始的姓名
select name from students where name rlike "^周.*";
#查询以周开始以伦结尾
select name from students where name rlike "^周.*伦$";

#范围查询
#in (1, 3, 8)表示在一个非连续的范围内
#查询年龄为18,34的姓名
# select name,age from students where age=18 or age=34;
# select name,age from students where age in (12,18,34);
# #not in 不在非连续的范围之内
#年龄不是18,34岁之间的信息
select name,age from students where age not in (12,18,34);
#between ....and ....表示在一个连续的范围内
#查询年龄不在在18到34之间的信息
select * from students where age not between 18 and 34;
#select * from students where not age between 18 and 34;

#空判断   判空 is null   判非空is not null
#查询身高为空的信息
select * from students where height is null;
select * from students where height is not null;

#排序
#order by 字段
#asc 从小到大排列，即升序(默认）
#desc从大到小排序，即降序
#查询年龄在18到34之间的男性，按照从小到大排序
select * from students where (age between 18 and 34) order by age asc;f

select * from students where (age between 18 and 34) and gender=2 order by height desc;

order by 多个字段

select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;
#先从年龄从小到大，再从身高从高到矮排
select * from students order by age asc,height desc;

#聚合函数
#总数 count
#查询男性有多少人，女性有多少人
select * from students where gender=1;
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;

#最大值 max
#查询最大的年龄
select age from students;
select max(age) from students;
select max(height) from where gender=2;
#最小值 min
select min(age) from students;
#求和 sum
select sum(age) from students;
#平均值
select avg(age) from students;
select sum(age)/count(*) from students;
#四舍五入round(123.12,1)保留1位小数
select round(sum(age)/count(*),2) from students;
#分组 group by
select gender,count(*) from students group by gender;
###查询组内有哪些人
select gender,group_concat(name) from students group by gender;

#计算男性中的人数
select gender,count(*) from students where gender=1 group by gender;
#查询同种性别中的姓名
select gender,group_concat(name) from students where gender=1 group by gender;
select gender,group_concat(name,age,id) from students where gender=1 group by gender;
select gender,group_concat(name,"_",age,"_",id) from students where gender=1 group by gender;

#having 对查询结果做出条件
select gender,group_concat(name) from students group by gender having avg(age)>30;

#分页 limit start count;
#限制查询出来的数据个数
select * from student where gender=1 limit 2;
#查询前5个数据
select * from students limit 0,5;#0表示从第一个开始，5表示取出5个
select * from students limit （第N页-1）*每页的个数，每页的个数;

#连接查询
#inner join ....on(内连接，交集）
select ...from 表A inner join 表B on 条件;
select * from studnets inner join classes on students.cls_id=classes.id;
select students.*,classes.name from studnets inner join classes on students.cls_id=classes.id;
#给表起名
select s.name,c.name from studnets as s inner join classes as c on s.cls_id=c.id;
#外连接:左连接、右连接
#左连接 left join ....on
#右连接 right join ....on
#查询没有对应班级信息的学生
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

#自关联
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="河北省";

#子查询
#标量子查询
#查询最高的男生信息
select * from students where height=(select max(height) from students)

#列级子查询
#查询学生的班级号能够对应的学生信息
select * from students where cls_id in(select id from classes);


视图：就是一条select语句执行后返回的结果集，是对若干张基本表的引用，一张虚表，查询语句执行的结果
create view v_goods_info as select g.*,c.name as cate_name,b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands
as b on g.brand_id = b.id;

定义视图：create view 视图名称 as select 语句;

查看视图：show tables;
使用视图：select * from v_stu_scroe;
删除视图：drop view v_stu_scroe
作用：
1、提高了重用性，就像一个函数
2、对数据库重构，却不影响程序的运行
3、提高了安全性能，可以对不同的用户
4、让数据更清晰

事务：是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
四大特性：（ACID）
1、原子性
2、一致性
3、隔离性
4、持久性
开启事务：begin;或者 start transaction;
提交事务：commit;
回滚事务:rollback;

索引：

set profiling = 1;  开启时间监视
show profiles;
create index title_index on test_index(title(10));

查看索引：show index from 表名；
创建索引：create index 索引名称 on 表名(字段名称(长度));
删除索引：drop index 索引名称 on 表名;

账户管理：
1、服务实例级账号
2、数据库级别账号
3、数据表级别账号
4、字段级别权限
5、存储程序级别的账号
创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

主从数据库：读写分离、数据备份、负载均衡
配置主从同步的基本步骤：
1、在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2、在每一个从服务器上配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
3、在开始复制进程前，在主服务器上记录二进制文件的位置信息
4、如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照
   （可以使用mysqldump导出数据库，或者直接复制数据文件）
5、配置从服务器要连接的主服务器的IP地址和登录权限，二进制日志文件名和位置
数据备份：mysqldump -uroot -p密码 数据库名 > python.sql
恢复：mysql -uroot -p 数据库名 < python.sql




'''
from pymysql import connect

def main():
    # 创建connection对象
    conn = connect(host="localhost", port=3306, user="root", password="123456", database="python", charset="gbk")
    # AttributeError: 'NoneType' object has no attribute 'encoding'

    # 获取cursor对象
    cur = conn.cursor()

    for i in range(100000):
        cur.execute("insert into test_index values('ha-%d')"%i)
    conn.commit()

if __name__ == "__main__":
    main()