MySQL数据库(三)：常用的SQL语句(下)

1、分组与分组之后的筛选

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。
语法：select count(*) from 表名 group by 字段
将男女分组显示人数：select gender as “性别”，count(*) from student group by gender;
可通过group_concat(…)查看每组的详细信息

例如显示分组之后的详细姓名
select gender as “性别”,count(*),group_concat(tname) from 表名 group by 字段名；
想要知道分组后总人数是多少除了自己算之外还有一个可选参数with rollup
，直接加在语句的最后面就可以显示例如
select gender as “性别”,count(*),group_concat(tname) from 表名 group by 字段名 with rollup

在男女分类的基础之上查询姓名和年龄，以冒号拼接，类似于python当中的字符串拼接


想要显示分组后总人数大于2的就不能直接在后面写where(会报错)要通过having去写筛选条件，比如

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

2、排序

order by 字段 默认升序;
order by 字段 asc 指定升序;
order by 字段 desc 指定降序;
1.查询年龄在18到26之间的男同学，按照年龄从小到大排序
select * from student where (age between 18 and 26) and gender = '男' order by age;
2.查询年龄在18到20岁之间的女同学，id从高到低排序
select * from student where (age between 18 and 20) and gender = '女' order by id desc;
3.查询年龄在18-23岁之间的女性，年龄从高到低降序，当年龄相同时，按照身高从低到高升序。
select gender,name,hight,age from student where (age between 18 and 20) ang gender='女' order by age desc,hight asc

3、限制

limit start,count
start 为偏移量，默认起始0，count 为条数
limit 不能写数学公式，只能写在末尾
例如
显示前两条数据：selct * from student student limit 2；
显示4-6条的数据：select * from student limit 4，3；
制作分页

select * from student limit 0，3；
select * from student limit 3，3；-- 不能用数学公式来显示这种规律，写公式会报错的

4、表连接

4.1内链接

select * from 表1 inner join 表2 on 表1.列 = 表2.列;
如果不加后面的on的条件就相当于是把所有的记录都拼接上，显示为一张表
1.显示学生的所有信息，但只显示班级名称
select s.\*,c.name from student s inner join classes c on s.cls_id=c.id;
2.查询有能够对应班级的学生以及班级信息，按照班级进行排序
select \* from student as s inner join class c on s.cls_id =c.id order by s.cls_id;
3.当同一个班级时，按照学生的id进行从小到大排序
select \* from student as s inner join class c on s.cls_id =c.id order by s.cls_id ASC,sid ASC;

4.2外链接

4.2.1左连接

查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充，左表完全显示，左右连接必须要有on筛选条件，和内链接不一样
select * from 表1 left join 表2 on 表1.列 = 表2.列;
students表左连接classes表 并查看班级为null的数据
select \* from student left s join class c on s.cls_id=c.id having c.id is null;
左连接 并且 查询 s.is_del=1 并且 s.name=“amy” 的数据
select \* from student left s join class c on s.cls_id=c.id having s.is_del=1and s.name='amy';

4.2.2右链接

查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。
select * from 表1 right join 表2 on 表1.列 = 表2.列;

5、子查询

查询最高身高的男生
1.如果数据少的话一眼看出来就可以写
select \* from student where hight = 1.78;
2.数据多的话这样的逻辑会出错，因为两者之间的数据并没有什么关联，所以要采用子查询关联起来
select name max(hight) from student where gender=1;
3.用子查询显示最高的男生身高和姓名
select name hight from student s where s.hight=(select max(hight) from student where gender=1);
4.查看高于平均身高的学生信息
select * from studet s where s.hight>(select avg(hight) from student);
5.查看最大年龄的女性id
select * from student where gender=1 and age=(select max(age) from student)（有问题，最大年龄和gender分开显示如果最大年龄是男的，而gender却要求女的，就不行）
select * from student where age=(select max(age) from student where gender=2)（找到了最大年龄，会根据最大年龄来显示数据，不论男女，所以还是不符合题意）
select * from student where gender=2 and age=(select max(age) from student where gender=2)（这样就可以查询到了）

6、自关联

①应用场景：填个人地址信息的时候，选择了相应的省份，后面就确定了可以选择的市，选择了相应市，就确定了可以选择的区。
②前言：有三张表省市区(没有表操作不了下面的语句)
查询陕西省的所有信息，找到对应的id
select * from provinces where province=‘陕西省’；
查询陕西省中有哪些市
select * from cites where provinceid=42000;
子查询语句
select * from citys where provinceid=(select provinceid from provinces where province='陕西省');
内连接语句
select * from provinces p inner join cites c on p.provinceid=c.provinceid having p.province=‘陕西省’;
自关联语句
select * from areas p inno join areas c on p.id=c.pid having p.name='陕西';

7、外键

7.1修改默认引擎

输入show engines;查看默认引擎，要把默认引擎修改为InnoDB，才可以设置外键

修改默认引擎的两种方式：
一：修改配置文件，进入到安装的MySQL中将默认的引擎直接修改，保存，关闭MySQL服务端，在从新连接修改生效，不是关客户端(命令行)！

二：通过命令行输入default-storage-engine=innodb;重启设置完毕，关闭MySQL服务端，在从新连接修改生效，不是关客户端(命令行)！
如果出现问题了可以参考下面的解决方案：关闭MySQL服务，去data目录下面删除日志文件(不关服务器删不掉)，删掉之后再重启MySQL服务端，连接客户端就会生效

7.2外键

①MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。
②主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
③外键的创建(从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致)
语法：CONSTRAINT <外键名>FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
方法一：创建表的时候直接就创建好外键

-- 创建班级表
create table classes(
    id int(4) not null primary key,
    name varchar(36)
);

-- 创建学生表
create table student(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4),
    constraint fj_cid foreign key(cid) references classes(id)
);

方法二：创建表的时候没有创建外键可以通过alter添加外键约束

-- 创建班级表
create table classes(
    id int(4) not null primary key,
    name varchar(36)
);

-- 创建学生表
create table student(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4)
);
alter table student add constraint fk_cid foreign key (cid) reference classes(id);

④操作表，
删除表的时候先删除主表会报错，有外键约束，可以直接删除从表

添加表数据，必须先添加主键所在表中的数据，先添加从表数据，会因为主表没有对应记录而报错，删除记录也是一样，删主表记录时，要保证该记录没有再从表中被引用才可以成功删除
⑤删除外键约束，回归没有外键约束的状态
alter table student drop foreign key fk_cid;

8、数据库的分表操作

①创建一个商品 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 TINYINT NOT NULL DEFAULT 1,
	is_saleoff TINYINT 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);

分表是因为当一张表的数据量比较多时，但是我们只需要查询其中的某个字段数据，就会导致查询效率降低，所以此处我们需要使用到分表。
③创建商品分类表

create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

④将商品表中的商品种类查询并插入到商品分类表里

insert into goods_cates (name) select cate_name from goods group by cate_name;

⑤将商品表中的商品种类更改为商品表分类表对应的 id

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

分表之后只需要修改分表之后的记录，连接的表中的字段就会有相应的修改，这样就节省了维护和改动的成本