MySQL习题

基本操作
–创建students表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default ‘’,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum(“男”,“女”,“中性”,“保密”) default ‘保密’,
cls_id int unsigned default 0,
is_delete bit default 0
);
–创建classes表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);

–查看表的创建结构
–show create table 表名；
mysql> show create table students;

–向students表中插入数据
insert into students values
(0,‘小明1’,10,179,4,2,0),
(0,‘小明2’,10,179,4,2,0),
(0,‘小明3’,10,179,4,2,0),
(0,‘小明4’,10,179,4,2,0),
(0,‘小明5’,10,179,4,2,0),
(0,‘小明6’,10,179,4,2,0),
(0,‘小明7’,10,179,4,2,0),
(0,‘小明8’,10,179,4,2,0),
(0,‘小明9’,10,179,4,2,0),
(0,‘小明10’,10,179,4,2,0);

–向students表中插入数据
insert into classes values(0,“python_01”),(0,“python_02”);

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

–查询指定字段
– select 列1，列2，列3… from 表名
select name,age from students

–使用as 给字段取别名
– select name as “别名1”,age as “别名2” from students;
select name as “姓名”,age as “年龄” from students;

–使用as 给表取别名
select s.name ,s.age from students as s;
select s.name as “姓名”,s.age as “年龄” from students as s;

–消除重复行
–distinct 字段
–select distinct 字段 from 表名;
select distinct gender from students;

进阶：
连接数据库:
1.mysql -uroot -proot
2.Mysql -uroot -p
退出数据库
1.exit/quite/ctrl+d
显示时间
1.select now();
查看所有数据库
1.show databases;

创建数据库
1.create database 数据库名 charset=utf8;
查看你就数据库的语句
1.show create database 数据库名
show create database python-04
show create database python04
删除数据库
drop database 数据库名
drop database python04
查看当前所在的数据库
select database();
选择数据库
mysql> use 数据库名
mysql> use python04new
Database changed

查看当前选择的数据库
mysql> select database();
创建表
–auto_increament 表示自动增长
–not null 表示不能为空
–primary key 表示为主键
–default 默认值
–create table 数据表名字（字段，类型，约束，）
mysql> create table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum(“男”,“女”,“中性”,“保密”) default “保密”, # 枚举
cls_id int unsigned
);
Query OK, 0 rows affected (0.01 sec)
向表中插入数据
mysql> insert into students values(0,“王宝强”,22,182.12,“男”,0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;

–查看表常见的语句
–修改表–添加字段
alter table 表名 add 列名 类型；
alter table students add happy varchar(20);

–修改表–修改字段；不重名版
alter table 表名 modify 列名 类型及约束；
alter table students modify brithday data;

–修改表–修改字段；重命名版
–alter table 表明 change 原名 类型及约束；
alter table students change birthbay birth date default ‘2000-02-01’;

–修改表–删除字段
–alter table 表名 drop 列名；

-----------对表的增删改查-----------------
1.增加
–前列插入
inset into 表名 values(…)
create table students(classno int,name varchar(10));

–向students表插入一个学生信
–部分插入
insert into 列名（列，…）values(值1，值2…)
insert into students(name,age) values (‘nn’,22);

–枚举插入中下标 从1 开始1–‘男’2–‘女’
insert into students(name,gender) values(‘cc’,2);

–多行插入
insert into students(name,age) values(‘c1’,11),(‘c2’,22),(‘c3’,21);

–更新 update 表名 set 列1=值，列2=值，…where 条件
update students set gender=1;----全部修改
update students set gender=1 where name=‘sss’ —只要是name='sss’全部修改

—删除
—物理删除 delete from 表名 where 条件
—逻辑删除
–用一个字段说明这条信息不能用了
–alter table students add is_delete bit default 0;
–update students set is_delete=1 where id=6;

—查询的基本使用
-查询所有列 select * from 表名
select * from students;
-定条件查询
select name,gender from students;

–可以使用as 为列或表指定别名
-select 字段 as 别名 ，字段 as 别名 from 数据表 where …
select name as 姓名，gender as 性别 from students ;

提升：
–比较运算运算符
–select … from 表名 where …
–查询大于18岁的信息
select * from students where age > 18;
select age,id,name from students where age >18;

–查询小于18岁的信息
select * from students where age < 18;
select age,id,name from students where age <18;
–查询年龄为18岁的信息
select * from students where age=18;

–!= 或者 <> 不等于

–逻辑运算符
–and
– 18到28之间的学生
select * from students where age >18 and age<28;
–18岁以上的女性
select * from students where age>18 and gender=“女”；
select * from students where age>18 and gender=“2”；

--or 
--18岁以上或者身高超高180（包含）以上
select * from students where age >18 or height>=180;

--not
--不在 18岁以上的女性，这个范围信息
select * from students  where not (age >18 and gender="女")
--年龄不是小于或者等于18 ，并且是女性。
select * from students where not(age<=18 and gender="女"）

–模糊查询
–like
–% 替换一个或多个
–_替换一个
– 查询姓名中以”小“开始的姓名
select * from students where name like"小%";

--查询有2个字的名字
select * from students where name like"__";

--查询有2个字的名字
select * from students where name like"___";


--rlike 正则
--查询 以周开始的姓名
select * from students where name rlike"^周.*"
--查询 以周开始的姓名,以伦结尾
select * from students where name rlike"^周.*伦$"



--范围查询
--in(1,3,5)表示在一个非连续的范围内
--查询年龄为18 34的姓名
select * from students where age in (18,34);
select * from students where age=18 or age=34;

--not in 不非连续的范围之内
--年龄不是18 ，34，之间的信息
select name,age from students where age not in(18,34);

--between...and.. 表示在这个连续范围内
--查询 年龄在18到34之间的信息
select name,age from students where age between 18 and 34;

--not between...and... 表示不在这个连续范围内
select name,age from students where age not between 18 and 34;

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

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

–排序
–order by 字段
–asc从小到大排序，即升序
–desc从大到小，即降序

--查询年龄在18到34岁之间的男性，按照年龄从小到大排序
select * from  students where (age between in 18 and 34）and gender="男" order by  age；--（默认asc）
select * from  students where (age between in 18 and 34）and gender="男" order by  age asc;

--查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34 )order by height desc;

--order by 多个字段
--查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的话，按照年龄从小到大排序
select * from students where(age between 18 and 34) order by height desc,age asc;

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

--最大值
--max
--查询最大年龄：
select age from students;
select max(age) from students;

--查询女性最高的身高
select height from students where gender=2;
select max(height) from students where gender=2;

--最小值
--min
	--查询最小年龄：
select age from students;
select min(age) from students;

--查询女性最高的身高
select height from students where gender=2;
select min(height) from students where gender=2;

--求和
--sum
--计算所有人的年龄总
select sum(age) from students;

--求平均值
--avg
--计算平均年龄
select avg(age) from students;
select sum(age)/count(*) from students;

--四舍五入 round(12.23,1) 保留1位小数
--计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*),2) from students;
select round(avg(age),2) from students; 

--计算男性的平均身高，保留2位小数。
select round(avg(height),2) from students where gender='1';

–分组
–group by
–按照性别分组，查询所有的性别
select gender from students group by gender;

--计算男性的人数
select gender,count(*) from students where gender=1 group by gender;

--group_concat(..)
--查询同种性》25别中的姓名
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," ","_") from students where gender=1 group by gender;

--having
--查询平均年龄超过30岁的性别，以及姓名 having avg(avg) >30
select group_concat(name,avg(age)) from students group by gender having avg(age)>30;

--查询每种性别中的人数多余2个的信息

select gender,group_concat(name) from students group by gender having count(*)>2;

–分页
–limite start,count
–限制查询出来的数据个数
select * from students where gender=1 limit 2;

--查询前五个数据
select * form students limit 0,5;   --12345
--查询id6-10（包含）的书序。
select * from students limit 5,5;   --678910

--每页显示2个，第1个页面
select * from students limit 0,2;

--每页显示2个，第2个页面
select * from students limit 2，2；
--每页显示2个，第3个页面
select * from students limit 4,2;
--每页显示2个，第4个页面
select * from students limit 6,2;
--每页显示2个，显示第六行信息，按照年龄从小到大排序
select  * from  students order by gender asc limit 10,2;  --

–连接查询
– A inner join B 取交集。

– A left join B 取 A 全部，B 没有对应的值为 null。

– A right join B 取 B 全部 A 没有对应的值为 null。

– A full outer join B 取并集，彼此没有对应的值为 null

– 对应条件在 on 后面填写。
–内连接（两个表共有的）
select * from students inner join classes;

--查询 又能够对应班级的学生以及班级信息
select * form students inner join classes on students.cls_id = classes.id;
--按照要求显示姓名，班级
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
--给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
--查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select c.name from students as s inner join classes as c on s.cls_id=c.id;
--在以上的查询中，将班级姓名显示在第一列
select c.name ,s.* from students as s inner join classes as c on s.cls_id=c.id;
--

--外连接
--left join
--查询每一位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
--查询没有对应班级信息的学生
select * from students as s left join classes as c on s.cls_id=c.id having c.cls_id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.cls_id is null;

–创建表
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, ‘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);

练习
– ●查询类型cate_name为‘'超极本"的商品名称·价格
select name ,price from goods where cate_name=‘超级本’;
– ●显示商品的种类
select cate_name from goods group by cate_name;
·求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price from goods;
– ●显示每种商品的平均价格
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 id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)order by price desc;
– ●查询每种类型中最贵的电脑信息
select * from goods
inner join
select
cate_name,
max(price) as max_price,min(price) as min_price,avg(price) as avg_price.
count(*) from goods group by cate_name) as goods_new_info
on goods.cate_name=goods_new_info.cate_name and goods,price=g oods_new_info.max_price;

– 2.创建"商品分类""表
–创建商品分类表
create table if not exists goods_cates (
id int unsigned primary key auto_increment,
name varchar(40) not null
);
●查询goods表中商品的种类
select cate_name from goods group by cate_name;
.将分组结果写入到goods_ cates数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;
3.同步表数据
●通过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;

– 4.创建"商品品牌表"表
– ●通过create…select来创建数据表并且同时写入记录,一 步到位
select brand name from goods group by brand_name;
–在创建数据表的时候一起插入数据
–注意:需要对brand_name 用as起别名，否则name字段就没有值
create table 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;
5.同步数据
●通过goods_brands数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

– 6.修改表结构
– ●查看goods的数据表结构,会发现cate_name和brand_name对应的类型为varchar 但是存储的都是数字
desc goods;
– ●通过alter table语句修改表结构
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

– ●分别在goods_ cates 和goods. brands表中插入记录
insert into goods_cates(name) values (‘路由器’),(‘交换机’),(‘网卡’);
insert into goods_brands(name) values (‘海尔’),(‘清华同方’),(‘神舟’);
– ●在goods数据表中写入任意记录
insert into goods (name ,cate_id, brand_id, price)
values(‘LaserJet Pro P1606dn黑白激光打印机’,12, 4, ‘1849’);
– ●查询所有商品的详细信息(通过内连接)
select g.id,g. name,c. name , b.name,g.price from goods as g
inner join goods_ cates as C on g.cate_ id=c.id
inner join goods_ brands as b on g.brand id=b.id;
– ●查询所有商品的详细信息(通过左连接)
select g. id,g. name,c.name , b.name,g.price from goods as 9
left join goods_ cates as C on g.cate _1d=c.id
left join goods. _brands as b on g.brand id=b. id;
– ●如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢?可以使用之前讲过的外键来解
– ●外键约束:对数据的有效性进行验证
– ●关键字: foreign key,只有innodb数据库引擎支持外键约束
– ●对于已经存在的数据表如何更新外键约束

– ●如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢?可以使用之前讲过的外键来解
– 决
– ●外键约束:对数据的有效性进行验证
– ●关键字: foreign key,只有innodb数据库引擎支持外键约柬
– ●对于已经存在的数据表如何更新外键约束
–给brand_ id添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id); --给goods表中的（brand_id）添加一个外键goods_brands(id)
–给cate_ id添加外键失败
会出现1452错误
–错误原因:已经添加了-个不存在的cate_ i1d值12, 因此需要先删除
alter table goods add foreign key (cate_id) references goods.cates(id);