mysql的相关操作

create database 数据库名字 charset=utf8; 创建数据库

show databases; 查看当前有多少个数据库

drop database 数据库名称 删除数据库

use 数据库名字 使用数据库

create table 表的名字(。。。。字段) 创建表

primary key 唯一的 非空的

show tables; 查看当前库有哪些表

alter table 表名 rename to 新表名

drop table 表名 删除表

insert into 表名 values(1 值 2 值 3 值…); 插入数据

select * from 表名 查询这个表数据 * 代表所有

– 使用 as 给字段起别名
– select 字段 as 名字… from 表名;
select name as ‘名字’, gender as ‘性别’ from students;

-- sql语句完全的形式
select students.* from students;
select students.name, students.gender from students;
select python_test_1.students.* from students;
省略数据库名字: 只有当前正在使用该数据库时才能够省略数据库名
省略数据表名: 只有当前正在某个数据表中查询数据时才能够省略数据表名

比较运算符 > < >= <= != =

select * from 数据库名字 where 字段 > 3;

逻辑运算符 and or not

select * from student where age>19 and id<5;

select * from student where age <30 or gender=‘女’;

select * from student where age is not null;

select * from student where not age=30;

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符

select * from student where name like ‘王%’ or name like ‘范%’;

select * from student where name like “范_”;

select * from student where name like “范%”;

范围查询

in

select * from student where id in (1,5,7,9);

between and

select * from student where id between 3 and 9;

select * from student where (id between 3 and 9) and gender=‘女’;

判断 非空 is not null

select * from student where age is not null;

优先级

优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

修改删除数据

update student set name=‘小王’,age=12 where id =4;

update student set name=‘小七’,age=10,gender=‘男’ where id=7;

删除数据表

delete from 表名 删除表中所有的信息

delete from 表名 where 条件。。。。

排序

desc 降序 asc 升序

–查询年龄在18到34岁之间的男性，按照年龄从小到大排序
select * from student where age between 18 and 34 and gender=‘女’ order by age asc;

– 查询年龄在18到34岁之间的女性，身高从高到矮排序

select * from student where age between 18 and 34 and gender=‘女’ order by height desc;

– order by 多个字段, order by age asc, height desc
– 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from student where age between 18 and 34 and gender=‘女’ order by height desc,age asc;

– 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序
select * from student where age between 18 and 34 and gender=‘女’ order by height desc,age asc,id desc;

– 按照年龄从小到大、身高从高到矮的排序
select * from student order by age asc,height desc;

聚合函数

count(*) 以行单位统计个数

select count(*) from student;

– 查询男性有多少人，女性有多少人

select count(*) from student where gender=‘男’;

–最大值
–max()

select max(字段) from 数据表名称；
select max(age) from student;
select max(height) from student;

– 查询女性的最高 身高
select max(height) from student where gender=‘女’;

– 查询最高身高的学生的名字
select name from student where height=(select max(height) from student);–子查询

–最小值
–min()

select min(字段) from 数据表名称;
select min(age) from student;

求和

需求：计算班级所有人的年龄总和

sum(字段名称)

select sum(age) from student;

–计算班级学生平均身高 /
select sum(height) / count(*) from student;

–avg() 平均值
–计算班级平均年龄
select avg(age) from student;

–四舍五入 round(1,值，2,保留几位小数)
–计算班级平均年龄 四舍五入 保留2个小数
select round(avg(age),2) from student;

分组

distinct 作用：查询结果不会出现相同的数据

select 要分组的字段 from student group by 字段名称;

–按照性别进行分组
select gender from student group by gender;

–每种性别当中最大的年龄
select gender,max(age) from student group by gender;

–计算每种性别中共有多少人
select gender,count(*) from student group by gender;

– 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高
select gender,avg(age),max(age),avg(height),max(height) from student group by gender;
select gender,round(avg(age),2),max(age),round(avg(height),2),max(height) from student group by gender;

–group_concat(…)
格式：select 要分组的字段,group_concat(字段名称) from 表名称 group by 字段名称

–按照性别，把名字进行分组
select gender,group_concat(name) from student group by gender;

–按照性别，把名字和年齡进行分组
select gender,group_concat(name,age) from student group by gender;

–按照分组，计算性别中男生的数量
select gender,count(*) from student where gender=‘男’ group by gender;

– 使用having 条件筛选
表示对于已经分组的数据做进一步的筛选
select gender,count(*) from student group by gender having gender = ‘男’;

–除了男生以为的分组人数
select gender,count(*) from student group by gender having gender !=‘男’;

–查询平均年龄超过30岁的性别和姓名
select gender,group_concat(name) from student group by gender having avg(age)>30;

注意：group by 和 having一起使用，有having 一定有 group by ,有group by 不一定有having

链接查询
–学生表：student
–班级表：class
需求:查询学生的名称和对应班级的名字

笛卡尔乘积查询，会产生很多无用的数据

解决方案:
select * from student,class where student.cls_id=class.id;

– 连接查询 将两个表中的数据按照设置的连接条件进行筛选,
–符合连接条件的数据才能够被筛选出来

–table1 inner join table2 on 设置内连接条件 ————内连接查询；
–使用内连接方式查询班级表和学生表
select * from student inner join class on student.cls_id = class.id;

外连接 left + join right + join

left join 左外连接查询
查询学生对应的数据，不满足条件的数据会以null填空

–使用外（left）连接方式查询班级表和学生表 student.cls_id = class.id;
select * from student left join class on student.cls_id = class.id;

注意 ：左连接 比 右连接用的多 右连接一般不用

自关联

source 路径 sql 文件名称 （自动导入sql数据）

–省市 查询 给 省 起别名 p 市 别名 c inner … join on 后面跟的是一个条件
省 市
select p.atitle,c.atitle from areas as p inner join areas as c on c.pid = p.aid where p.atitle = ‘河北省’;

子查询

子查询分类

标量子查询: 子查询返回的结果是一个数据(一行一列)

列子查询: 返回的结果是一列(一列多行)

行子查询: 返回的结果是一行(一行多列)

–标量子查询
–查询班级最高身高的学生信息
select max(height) from student;

select * from student where height=(select max(height) from student);

–查询学生的身高 大于平均身高 或者小于平均身高
select * from student where height>(select avg(height) from student);

–列级子查询，查询的结果是一列多行
in 范围格式: 主查询 where 条件 in (列子查询)
select * from student where age in (28,17,30,12,10);

select * from class where id in (select cls_id from student);

select * from student where age in (select age from student where age in(10,30,20));

数据库的备份与恢复
–备份 将某一台主机的数据完成一个副本的操作，mysqldump 是Linux下的命令
–数据迁移

mysqldump -uroot -p 数据库的名称 > ~/要备份的路径 备份文件的名称 （格式是sql格式的）

备份
msyqldump -uroot -p H_1808A > ~/Desktop/python_res.sql 密码：mysql

恢复
mysql -uroot -p 数据库名称 < ~/Desktop/备份文件的名称
mysql -uroot -p python_test_01 < ~/Desktop/python_res.sql 密码：mysql

sudo service mysql start;开启mysql服务
sudo service mysql stop; 停止mysql服务

ps aux | grep mysql;

–类似淘宝数据库
– 求所有商品的平均价格,并且保留两位小数

– 创建 “京东” 数据库
create database jing_dong charset=utf8;

– 使用 “京东” 数据库
use jing_dong;

– 创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);

insert into goods values(0,‘r510vc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘3399’,default,default);
insert into goods values(0,‘y400n 14.0英寸笔记本电脑’,‘笔记本’,‘联想’,‘4999’,default,default);
insert into goods values(0,‘g150th 15.6英寸游戏本’,‘游戏本’,‘雷神’,‘8499’,default,default);
insert into goods values(0,‘x550cc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘2799’,default,default);
insert into goods values(0,‘x240 超极本’,‘超级本’,‘联想’,‘4880’,default,default);
insert into goods values(0,‘u330p 13.3英寸超极本’,‘超级本’,‘联想’,‘4299’,default,default);
insert into goods values(0,‘svp13226scb 触控超极本’,‘超级本’,‘索尼’,‘7999’,default,default);
insert into goods values(0,‘ipad mini 7.9英寸平板电脑’,‘平板电脑’,‘苹果’,‘1998’,default,default);
insert into goods values(0,‘ipad air 9.7英寸平板电脑’,‘平板电脑’,‘苹果’,‘3388’,default,default);
insert into goods values(0,‘ipad mini 配备 retina 显示屏’,‘平板电脑’,‘苹果’,‘2788’,default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ',‘台式机’,‘联想’,‘3499’,default,default);
insert into goods values(0,‘vostro 3800-r1206 台式电脑’,‘台式机’,‘戴尔’,‘2899’,default,default);
insert into goods values(0,‘imac me086ch/a 21.5英寸一体电脑’,‘台式机’,‘苹果’,‘9188’,default,default);
insert into goods values(0,‘at7-7414lp 台式电脑 linux ）’,‘台式机’,‘宏碁’,‘3699’,default,default);
insert into goods values(0,‘z220sff f4f06pa工作站’,‘服务器/工作站’,‘惠普’,‘4288’,default,default);
insert into goods values(0,‘poweredge ii服务器’,‘服务器/工作站’,‘戴尔’,‘5388’,default,default);
insert into goods values(0,‘mac pro专业级台式电脑’,‘服务器/工作站’,‘苹果’,‘28888’,default,default);
insert into goods values(0,‘hmz-t3w 头戴显示设备’,‘笔记本配件’,‘索尼’,‘6999’,default,default);
insert into goods values(0,‘商务双肩背包’,‘笔记本配件’,‘索尼’,‘99’,default,default);
insert into goods values(0,‘x3250 m4机架式服务器’,‘服务器/工作站’,‘ibm’,‘6888’,default,default);
insert into goods values(0,‘商务双肩背包’,‘笔记本配件’,‘索尼’,‘99’,default,default);

select round(avg(price),2) from goods;

– 查询所有价格大于平均价格的商品，并且按价格降序排序
select * from goods where price > (select round(avg(price),2) from goods) order by price desc;

– 查询类型cate_name为 ‘超级本’ 的商品名称、价格
select name,price from goods where cate_name=‘超级本’;

– 显示商品的种类
select distinct cate_name from goods; distinct 去除重复的字段数据
分组方式
select cate_name from goods group by cate_name;

–显示每种类型的商品的平均价格
select cate_name,avg(price) from goods group by cate_name;

– 查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

– 查询每种类型中最贵的商品信息
–子查询方式
select * from goods where (cate_name,price) in (select cate_name,max(price) from goods group by cate_name);

创建 goods_cates 表
create table if not exists goods_cates(id int unsigned primary key auto_increment,name varchar(40) not null);

插入数据到 goods_cates 表中
insert into goods_cates (name) select cate_name from goods group by cate_name;

–连接更新
–根据goods_cates 表 更新 goods 表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name = c.id;

修改字段名称 旧名称 新名称
alter table goods change cate_name cate_id int unsigned not null;

创建品牌表
create table if not exists goods_brands(id int unsigned primary key auto_increment,name varchar(40) not null)select brand_name as name from goods group by brand_name;

视图

–视图 可以把它比喻成一个封装好的查询语句
create view 视图名称 as 查询语句
注意：在视图里面不存在更改，只有删除，重新创建
drop view 视图名称 删除视图；
视图只能封装 select 查询语句 其他的一概不可以

三张表链接查询
select * from goods left join goods_cates on goods.cate_id = goods_cates.id left join goods_brands on goods.brand_id=goods_brands.id;

外键

foreign key

解决方案 让cate_id 作为一个外键 关联 goods_cates 中的id

references 关联 链接

foreign key(cate_id) references goods_cates(id),

foreign key(brand_id) references goods_brands(id)

修改字段 作为外键
alter table goods add foreign key(brand_id) references goods_brands(id);
alter table goods add foreign key(cate_id) references goods_cates(id);

安装mysql服务端命令
sudo apt-get install mysql-server

启动服务端命令
ps ajx|grep mysql

重启服务端命令
sudo service mysql restart

事务

开启事务 begin;
或者
start transaction;

提交事务
commit 注意 commit 提交后本次事务终止，想使用事务得需要重新开启 begin

回滚事务
rollback;

索引

在表中查看索引
show index from 表名；

创建索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

删除索引 格式
drop index 索引名称 on 表名;

开启运行时间监测：
set profiling=1;

查找第1万条数据ha-99999
select * from test_index where title=‘ha-99999’;

查看执行的时间：
show profiles;

为表title_index的title列创建索引：
create index title_index on test_index(title(10));

执行查询语句：
select * from test_index where title=‘ha-99999’;

再次查看执行的时间
show profiles;

sudo pip3 install pymysql;
pip 默认会把包安装到Python2的版本中 pip3 会默认安装到Python3

联合索引（复合索引）组合索引

修改表结构，添加索引
alter table 表名 add index 索引名称（字段1，字段2，字段3）

create index 索引名称 on 表名(字段1，字段2，字段3)

什么叫复合索引？
对多个字段同时建立的索引，使用复合索引必须遵循最左侧匹配原则

表student 有 name ,age ,address 三个字段

create index stu_index on student(name,age,address); #创建联合索引

三种情况：1 name
2 name|age
3 name | age | address

假如student表中有10个字段 我们只是在name,age,address三个字段创建索引
	  
selec * from student where name = '李四';# 这种情况使用到了索引
	  
selec * from student where name = '李四' and age=12; # 这种情况使用到了索引
	  
selec * from student where name = '李四' and age=12 and address="河南";# 这种情况使用到了索引
	  
selec * from student where name = '李四' and address="河南"; # 这种情况没有使用到索引