1、模糊查询
1、like子句:在where子句中 ， 可以使用该子句以及关键字结合实现模糊查找
select * from 表名 where 字段名 like '关键字';
通配符
%：表示匹配0个或者多个字符(NULL除外)
_: 表示匹配任意一个字符
可以在通配符前面或者后面指定文字
mysql> insert t1 values('张三','x');
mysql> insert t1 values('张三三','x');
mysql> insert t1 values('张三四','x');
mysql> insert t1 values('张三四五','x');
mysql> select * from t1;
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '张%';
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '张_';
+--------+------+
| name | sex |
+--------+------+
| 张三 | X |
+--------+------+
mysql> select * from t1 where name like '张__';
+-----------+------+
| name | sex |
+-----------+------+
| 张三三 | X |
| 张三四 | X |
+-----------+------+
mysql> select * from t1 where name like '张___';
+--------------+------+
| name | sex |
+--------------+------+
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '_三__';
+--------------+------+
| name | sex |
+--------------+------+
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '_三_';
+-----------+------+
| name | sex |
+-----------+------+
| 张三三 | X |
| 张三四 | X |
+-----------+------+
mysql> select * from t1 where name like '_三%';
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '%';
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select * from t1;
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select * from t1 where name like '张三';
+--------+------+
| name | sex |
+--------+------+
| 张三 | X |
+--------+------+
mysql> select * from t1 where name like '张';
Empty set (0.00 sec)
2、消除重复项
对查询的结果去重——distinct
select distinct * from 表名;
select distinct 字段名 from 表名;
mysql> select * from t1;
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select distinct * from t1; -- 第一列去重
+--------------+------+
| name | sex |
+--------------+------+
| 张三 | X |
| 张三三 | X |
| 张三四 | X |
| 张三四五 | X |
+--------------+------+
mysql> select distinct sex from t1;
+------+
| sex |
+------+
| X |
+------+
3、排序
在查询中添加排序 ， 按照自定的字段进行排序（升序 ， 降序）—— order by
select * from 表名 where 条件 order by 字段名 desc;
select * from 表名 order by 字段名 desc;
根据order by指定的排序
asc ： 按照指定字段进行升序排序（默认的）
desc: 按照指定字段进行降序排序
mysql> create table t2 (id int primary key auto_increment,name char,num int);
mysql> insert into t2(name,num) values('啊',60);
mysql> insert into t2(name,num) values('吧',70);
mysql> insert into t2(name,num) values('才',65);
mysql> insert into t2(name,num) values('的',75);
mysql> insert into t2(name,num) values('额',80);
mysql> select * from t2;
+----+------+------+
| id | name | num |
+----+------+------+
| 1 | 啊 | 60 |
| 2 | 吧 | 70 |
| 3 | 才 | 65 |
| 4 | 的 | 75 |
| 5 | 额 | 80 |
+----+------+------+
mysql> select * from t2 where num between 60 and 75 order by num asc;
+----+------+------+
| id | name | num |
+----+------+------+
| 1 | 啊 | 60 |
| 3 | 才 | 65 |
| 2 | 吧 | 70 |
| 4 | 的 | 75 |
+----+------+------+
mysql> select * from t2 where num between 60 and 75 order by num ;
+----+------+------+
| id | name | num |
+----+------+------+
| 1 | 啊 | 60 |
| 3 | 才 | 65 |
| 2 | 吧 | 70 |
| 4 | 的 | 75 |
+----+------+------+
mysql> select * from t2 where num between 60 and 75 order by num desc;
+----+------+------+
| id | name | num |
+----+------+------+
| 4 | 的 | 75 |
| 2 | 吧 | 70 |
| 3 | 才 | 65 |
| 1 | 啊 | 60 |
+----+------+------+
mysql> select * from t2 where num between 60 and 75 order by name; -- 文字好像没规律
+----+------+------+
| id | name | num |
+----+------+------+
| 2 | 吧 | 70 |
| 1 | 啊 | 60 |
| 3 | 才 | 65 |
| 4 | 的 | 75 |
+----+------+------+
mysql> select * from t2 order by num desc;
+----+------+------+
| id | name | num |
+----+------+------+
| 5 | 额 | 80 |
| 4 | 的 | 75 |
| 2 | 吧 | 70 |
| 3 | 才 | 65 |
| 1 | 啊 | 60 |
+----+------+------+
4、分组
根据指定的字段进行分组
select * from 表名 where 条件 group by 字段名(指定分组的字段)
select * from 表名 group by 字段名(指定分组的字段)
-- 获得结果就是每类分组的第一个数据
mysql> select * from t3;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
| 3 | 男 |
| 4 | 男 |
| 5 | 女 |
| 6 | 女 |
| 7 | 男 |
| 8 | 女 |
| 9 | 男 |
| 10 | 女 |
+----+------+
mysql> select * from t3 where id between 1 and 7 group by sex;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 5 | 女 |
+----+------+
mysql> select * from t3 where sex = '男' group by sex;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
+----+------+
mysql> select * from t3 group by sex;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 5 | 女 |
+----+------+
5、限制查询条数
limit子句限制查询返回的数据条数
select * from 表名 limit 返回数量;
select * from 表名 where 条件 limit 返回数量;
select * from 表名 limit 起点 ， 返回数量;
mysql> select * from t3;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
| 3 | 男 |
| 4 | 男 |
| 5 | 女 |
| 6 | 女 |
| 7 | 男 |
| 8 | 女 |
| 9 | 男 |
| 10 | 女 |
+----+------+
mysql> select * from t3 limit 1 , 4;
+----+------+
| id | sex |
+----+------+
| 2 | 男 |
| 3 | 男 |
| 4 | 男 |
| 5 | 女 |
+----+------+
mysql> select * from t3 limit 2 , 4;
+----+------+
| id | sex |
+----+------+
| 3 | 男 |
| 4 | 男 |
| 5 | 女 |
| 6 | 女 |
+----+------+
mysql> select * from t3 limit 2 ;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
+----+------+
mysql> select * from t3 where sex='男' limit 2;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
+----+------+
mysql> select * from t3 where sex='男' limit 5;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
| 3 | 男 |
| 4 | 男 |
| 7 | 男 |
+----+------+
mysql> select * from t3 where sex='男' limit 10;
+----+------+
| id | sex |
+----+------+
| 1 | 男 |
| 2 | 男 |
| 3 | 男 |
| 4 | 男 |
| 7 | 男 |
| 9 | 男 |
+----+------+
6、聚合筛选
having对 分组之后 的数据进行筛选 ，where只能操作表中的字段 ， having可以和聚合函数联合
- having必须和group by一起使用
- having后用(avg , max , min , sum , count)这些函数
- 所谓聚合筛选就是对聚合结果的筛选，也就是筛选组的，并非单行数据
select * from 表名 where 条件 group by 字段名 having 条件;
create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum("魏","蜀","吴"),
attack smallint,
defense tinyint
);
insert into sanguo
values
(1, '曹操', '男', '魏', 256, 63),
(2, '张辽', '男', '魏', 328, 69),
(3, '甄姬', '女', '魏', 168, 34),
(4, '夏侯渊', '男', '魏', 366, 83),
(5, '刘备', '男', '蜀', 220, 59),
(6, '诸葛亮', '男', '蜀', 170, 54),
(7, '赵云', '男', '蜀', 377, 66),
(8, '张飞', '男', '蜀', 370, 80),
(9, '孙尚香', '女', '蜀', 249, 62),
(10, '大乔', '女', '吴', 190, 44),
(11, '小乔', '女', '吴', 188, 39),
(12, '周瑜', '男', '吴', 303, 60),
(13, '吕蒙', '男', '吴', 330, 71);
mysql> select * from sanguo where gender='男';
+----+-----------+--------+---------+--------+---------+
| id | name | gender | country | attack | defense |
+----+-----------+--------+---------+--------+---------+
| 1 | 曹操 | 男 | 魏 | 256 | 63 |
| 2 | 张辽 | 男 | 魏 | 328 | 69 |
| 4 | 夏侯渊 | 男 | 魏 | 366 | 83 |
| 5 | 刘备 | 男 | 蜀 | 220 | 59 |
| 6 | 诸葛亮 | 男 | 蜀 | 170 | 54 |
| 7 | 赵云 | 男 | 蜀 | 377 | 66 |
| 8 | 张飞 | 男 | 蜀 | 370 | 80 |
| 12 | 周瑜 | 男 | 吴 | 303 | 60 |
| 13 | 吕蒙 | 男 | 吴 | 330 | 71 |
+----+-----------+--------+---------+--------+---------+
-- //////////////////找到所以男生角色///////////////////
mysql> select * from sanguo where gender='男' group by country;
+----+--------+--------+---------+--------+---------+
| id | name | gender | country | attack | defense |
+----+--------+--------+---------+--------+---------+
| 1 | 曹操 | 男 | 魏 | 256 | 63 |
| 5 | 刘备 | 男 | 蜀 | 220 | 59 |
| 12 | 周瑜 | 男 | 吴 | 303 | 60 |
+----+--------+--------+---------+--------+---------+
-- //////////////////按国家分组后 只会显示第一个结果///////////////////
mysql> select * from sanguo where gender='男' group by country having max(attack)>330;
+----+--------+--------+---------+--------+---------+
| id | name | gender | country | attack | defense |
+----+--------+--------+---------+--------+---------+
| 1 | 曹操 | 男 | 魏 | 256 | 63 |
| 5 | 刘备 | 男 | 蜀 | 220 | 59 |
+----+--------+--------+---------+--------+---------+
-- 选择最大攻击力大于330的数据，这是在分组的结果上取得的，所以显示的不是最大战力的人
-- 所以这样显示会很别扭，一般不会把数据全部显示，而是像下边的那样，统计组别
mysql> select country , count(id) from sanguo group by country ;
+---------+-----------+
| country | count(id) |
+---------+-----------+
| 魏 | 4 |
| 蜀 | 5 |
| 吴 | 4 |
+---------+-----------+
mysql> select country , count(id) from sanguo group by country having avg(defense)>60;
+---------+-----------+
| country | count(id) |
+---------+-----------+
| 魏 | 4 |
| 蜀 | 5 |
+---------+-----------+
-- 先分组，再选择平均防御力大于60的组
7、子查询
在一个select语句中 ， 嵌套进去另一个select语句 ， 那么被嵌套的select语句称之为子查询语句 ， 外部select语句称之为主查询。
select * from (select * from 表名 where 条件) as 表别名 where 表别名.条件;
select * from 表名 where 条件 (select * from 表名 where 条件);
注：(select * from 表名 where 条件)的结果必须精确到某个数据，也就是某个单元格
mysql> select * from (select * from sanguo where gender='女') as sg where sg.attack<260;
+----+-----------+--------+---------+--------+---------+
| id | name | gender | country | attack | defense |
+----+-----------+--------+---------+--------+---------+
| 3 | 甄姬 | 女 | 魏 | 168 | 34 |
| 9 | 孙尚香 | 女 | 蜀 | 249 | 62 |
| 10 | 大乔 | 女 | 吴 | 190 | 44 |
| 11 | 小乔 | 女 | 吴 | 188 | 39 |
+----+-----------+--------+---------+--------+---------+
mysql> select * from sanguo where defense<(select defense from sanguo where name='诸葛亮');
+----+--------+--------+---------+--------+---------+
| id | name | gender | country | attack | defense |
+----+--------+--------+---------+--------+---------+
| 3 | 甄姬 | 女 | 魏 | 168 | 34 |
| 10 | 大乔 | 女 | 吴 | 190 | 44 |
| 11 | 小乔 | 女 | 吴 | 188 | 39 |
+----+--------+--------+---------+--------+---------+
-- 查询的是，三国里比诸葛亮防御力还要低的人的数据
8、运算符
在查询得到的结果中做运算，不影响源数据
mysql> select name , defense-5 as defense from sanguo;
+-----------+---------+
| name | defense |
+-----------+---------+
| 曹操 | 58 |
| 张辽 | 64 |
| 甄姬 | 29 |
| 夏侯渊 | 78 |
| 刘备 | 54 |
| 诸葛亮 | 49 |
| 赵云 | 61 |
| 张飞 | 75 |
| 孙尚香 | 57 |
| 大乔 | 39 |
| 小乔 | 34 |
| 周瑜 | 55 |
| 吕蒙 | 66 |
+-----------+---------+
9、外键约束
foreign key：建立表与表之间的某种约束关系 ， 这个关系的存在 ， 可以让表与表之间的数据关联性更强 ， 数据较完整。
主表：被外键连接的
从表：设置外键进行连接
注意：主表必须先创建 ， 才能创建从表进行外键约束；
foreign key (外键字段名) references 主表名(字段名)
-- DROP TABLE t4,t5;
--
-- create table t4(
-- id int(4) primary key auto_increment,
-- name varchar(5)
-- );
--
-- create table t5(
-- id int(4) primary key auto_increment,
-- age int(3),
-- -- 创建一个字段作为外键约束 让表之间成为一对一的关系
-- t5_id int unique,
-- foreign key (t5_id) references t4(id)
-- on delete restrict
-- on update restrict
-- );
--
-- insert into t4(name) values
-- ('韩非'),
-- ('张良'),
-- ('卫庄'),
-- ('紫女'),
-- ('墨鸦 '),
-- ('红莲公主');
--
-- insert into t5(age , t5_id) values (25 , 5); -- 墨鸦 5
-- insert into t5(age , t5_id) values (20 , 4); -- 紫女 4
-- insert into t5(age , t5_id) values (21 , 3); -- 卫庄 3
-- insert into t5(age , t5_id) values (22 , 2); -- 张良 2
-- insert into t5(age , t5_id) values (23 , 1); -- 韩非 1
-- insert into t5(age , t5_id) values (24 , 6); -- 红莲公主 6
外键约束特性1
restrict(默认)
on delete restrict
on update restrict
当主表要删除数据的时候 ， 从表有数据相关联的时 ， 则不允许主表数据删除（修改也一样）
-- delete from t4 where id =1; -- 删除id为1的韩非，错误，外键约束
-- delete from t4 where name='墨鸦'; -- 删除墨鸦，错误，外键约束
-- update t4 set id = '7' where id = 1; -- id=1改为id=7，错误，外键约束
update t4 set name = '盖聂' where id = 3;
-- 但是可以修改id=3的卫庄，把它改为盖聂，id被约束，但是，名字没有
外键约束特性2
delete from t5 where id=5;-- 删除从表id=5的数据，联结的是主表id=1的韩非
-- delete from t4 where id=5; -- 删除主表id=5墨鸦，但是报错，删除不了，因为外键约束
delete from t4 where id=1; -- 可以删除主表的id=1的韩非，因为从表的绑定已经删除
