MySQL功能大全（细品）

随便练习

desc 表名
alter table old_name rename now_name 修改表的名称
alter table 表名 engine=innodb|mysiam 修改表的引擎
alter table 表名 rename to 数据库名.表名
alter table 表明 add 字段名 数据类型 属性 增加一个新的字段
alter table 表明 add 字段名 数据类型 属性 first 增加一个新的字段，并放在首位

select * from runoob_tbl where binary runoob_author=‘runoob.com’;

update runoob_tbl set field1 = new-value1, field2=new-value2;

delete from table_name [where clause]

delete from runoob_tbl where runoob_id=3;

select * from runoob_tbl where runoob_author like ‘%com’;

select country from websites
union all all 有重复数据，不带all去重
select country from apps
order by country;

select * from runoob_tbl order by submission_date asc|desc;

select name, count(*) from employee_tbl group by name;

select a.runoob_id, a.run00b_author, b.runoob_count from runoob_tbl a inner join tcount_tbl b on a.runoob_author = b.runoob_count;

select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a, tcount_tbl b where a.runoob_author = b.runoob_author;

select a.runoob_id, a.runoob_author, b.ruboob_count from runoob_tbl a right join tcount_tbl b on a.runoob_author = b.runoob_author;

创建表

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(‘移动设备’)

小结：insert into … select …

查看goods表中的商品分类名称对应的商品分类id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;

create table … select列名… 表示创建表并插入数据
cerate 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_anme brand_id int not null;

pymysql完成数据的查询操作

import pymysql

conn = pymysql.connect(host=‘localhost’, port=3306, user=‘root’, password=‘123’) #创建连接对象

cursor = conn.cursor() #获取游标对象

sql = ‘select * from student;’

row_count = cursor.execute(sql) #执行sql语句，返回值是sql语句在执行过程中影响的行数

print(‘sql语句执行影响的行数%d’%row_count)

print(cursor.fetchone()) #取出结果集中的一行数据 例如（1：张三）

for line in cursor.fetchall():
print(line) #取出结果集的所有数据

sursor.close() #关闭游标

conn.close() #关闭连接

conn.commit() #表示将修改操作提交到数据库

conn.rollback() #表示回滚数据

查看mysql数据库支持的表的存储引擎
show engines;

查看goods表的创表语句
use jing_dong
show creare table goods;

修改表的引擎
alter table students engine = ‘MYISAM’

开启事务
begin; 或者 start transaction;

事务的特性:
原子性: 强调事务中的多个操作时一iy整体
一致性: 强调数据库中不会保存不一致状态
隔离性: 强调数据库中事务之间相互不可见
持久性: 强调数据库能永久保存数据，一旦提交就不可撤销

查看表中已有索引

show index from 表名；

给name字段添加索引
alter table classes add index my_name(name);

删除索引的语法格式
alter table 表名 drop index 索引名;

向表中插入十万条数据

from pymysql import connect

def main():
#创建connection连接
conn = connect(host=‘localhost’, port=3306, database=‘python’, user=‘root’, password=‘123’)
#获得cursor对象
cursor = conn.cursor()
#插入十万次数据
for i in range(100000):
cursor.execute(‘insert into test_index values(“ha-%d”)’%i)

conn.commit()

if name == “main”:
main()

验证索引性能操作

set profiling=1 #开启运行时间检测
select * from test_index where title=‘ha-99999’;
show profiles; #查看执行的时间

alter table test_index add index(title); #添加索引
select * from test_index where title=‘ha-99999’;
show profiles;

创建索引使用: alter table 表名 add index 索引名[可选] (字段名, xxx);
删除索引使用: alter table 表名 drop index 索引名;

修改表-添加birthday字段
alter table students add birthday datetime;

修改表-修改字段类型
alter table students modify birthday date not null;

修改表-修改字段名和字段类型
alter table students change birthday birth datetime not null;

修改表-删除birth字段
alter table students drop birth;

查看建表语句
show create table students;

添加删除表示字段，0表示未删除，1表示删除
alter table students add isdelete bit default 0;

逻辑删除数据
update students set isdelete=1 where id=8;

去重

select distinct name, gender from students;

is null is not null
between…and…
in
like %表示任意多个字符 表示一个任意字符
select * from students where name like ‘黄%’;
select * from students where name like '黄’;

查询未删除的男生的信息，按年龄降序
select * from students where isdelete=0 and gender=‘男’ order by age desc;

显示所有的学生信息，先按年龄从大到小排序，当年龄相同时按照身高从高到矮排序
select * from students otder by age desc, height desc;

select * from 表名 limit start,count
limit:是分页查询关键字
start:表示开始行索引，默认是0
count:表示查询条数

查询前3行男生的信息
select * from students where gender=1 limit 0,3;
select * from students where gender=1 limit 3;

查询学生表，获取第n页数据的sql语句
select * from students limit (n-1)*m,m;

已知每页显示3条数据，求第二页显示的数据
select * from students limit 3,3;

group by 使用

select gender from students group by gender;

group by + group_concat()
group_concat():统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
#根据gender字段进行分组，查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

group by + 聚合函数的使用
select gender, avg(age) from students group by gender;

统计不同性别的人的个数
select gender, count(*) from students group by gender;

group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
#根据gender字段进行分组，统计分组条数大于2的
select gender, count() from students group by gender having count()>2;

#不同性别人的平均年龄，平均年龄>30的
select gender,avg(age) from students group by gender having avg(age)>30;

group by + with rollup的使用
withrollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

#根据gender字段进行分组，汇总总人数
select gender, count(*) from students group by gender with rollup;

#根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

group by 根据指定的一个或者多个字段对数据进行分组
group_concat(字段名)函数是统计每个分组指定字段的信息集合
聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
having 是对分组数据进行条件过滤
with rollup在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

内连接 inner join

select * from students inner join class on students.cid=class.name;

内连接使用inner join … on…, on表示两个表的连接查询条件
左连接使用left join … on …, on 表示两个表的连接查询条件
左连接以左表为主根据条件查询右表数据，右表数据不存在使用null值填充。

select * from dtudents as s left join class as c on s.cid=c.name;

自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
自连接就是一种特殊的连接方式，连接的表还是本身这张表

select * from areas as c inner join areas as p;
select * from areas as c inner join areas as p on c.pid=p.id;

子查询的使用
#查询大于平均数学成绩的学生
select * from students where math>(select avg(math) from students);

#查询学生在班的所有班级的名字
select name from class where id in (select cid from students where cid id not null);

#查找年龄最大且成绩最低的学生
select * from students where (math,age)=(select min(math),max(age) from students);

外键

#为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);

创建表时加外键
create table teacher(
id not null primary key cuto_increment,
name varchar(10),
sid int not null,
foreign key(sid) references school(id)
)engine=InnoDB default charset=utf8;

删除外键名称
#获取外键约束名称
show create table teacher;
#删除外键约束
alter table teacher drop foreign key 外键名;

create table … select… 表示创建并插入数据
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;

快速修改表结构

#我们要把类名字、品牌名字改成类id和品牌id
alter table goods change cate_name cate_id int not null,change brand_name brand_id int not null;

sql中pymysql的使用步骤

import pymysql
conn=pymysql.connect(参数列表)
cursor=conn.cursor()
row_count=cursor.execute(sql)
result=cursor.fetchall()
conn.commit()
cursor.clase()
conn.close()

执行顺序：from where froup by having select distinct union order by

视观表

create view “view_name” as “sql语句”

row_number() over (partition by column1,column2 order by column3 desc) as new_name;
该函数的作用是，按照column1和column2对数据进行分组，在每一个分组内，按照column3进行排序，排序之后，对每一个分组内的多行数据，标记上序号，序号从1开始，依次递增。当然，可以给序号取一个新的名字new_name

modify能修改字段类型和约束,而change不能。 change用来字段重命名,不能修改字段类型和约束

alter table sony_menbers change age age2 int;

将日期时间进行拆分

select member_id,
left(registration_date,4) as year,
right(registration_date,5) as date from sony_members;

select id1, id2, case when id1 is null then id2 else id1 end as id3 from name;