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="河南"; # 这种情况没有使用到索引