MySQL查询

在实际工作中,98%的工作都是查询

1.先创建一个info表

-- 创建一个表info,分别有名字,年龄,性别,所在城市
CREATE TABLE IF NOT EXISTS `info`(
 `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 `name` VARCHAR(255) NOT NULL DEFAULT '',
 `age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
 `sex` TINYINT NOT NULL DEFAULT 0,
 `city` VARCHAR(255) NOT NULL DEFAULT 0
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

2.插入数据

INSERT INTO info (id,name,sex,age,city) VALUES(NULL,'李旺儒','0','77','广州'),(NULL,'孔令峰','1','30','广州'),(NULL,'黄景坤','1','30','广州'),(NULL,'奇奇','0','90','深圳'),(NULL,'吴志勇','1','18','深圳'),(NULL,'狗哥','1','10','深圳'),(NULL,'嚣张','0','37','广州'),(NULL,'张三金','1','85','郑州'),(NULL,'张书领','1','43','郑州'),(NULL,'申坤奇','1','33','郑州'),(NULL,'王有卓','1','23','南宁'),(NULL,'唐金铭','1','24','南宁'),(NULL,'杨胜涛','1',27,'南宁'),(NULL,'叶泰峰','1','30','南宁'),(NULL,'李帅想','1','18','北京'),(NULL,'想帅帅',0,16,'北京'),(NULL,'李想帅',1,15,'北京'),(NULL,'李想',1,19,'北京')

 WHERE 子句运算符

-- 小于大于等于
SELECT id,name,sex,age,city FROM info WHERE id=5;
SELECT id,name,sex,age,city FROM info WHERE id<5;
SELECT id,name,sex,age,city FROM info WHERE id>5;
SELECT id,name,sex,age,city FROM info WHERE id<=5;
SELECT id,name,sex,age,city FROM info WHERE id>=5;
SELECT id,name,sex,age,city FROM info WHERE id!=5;
SELECT id,name,sex,age,city FROM info WHERE id<>5;
-- 指定范围 BETWEEN AND和NOT BETWEEN AND
SELECT id,name,sex,age,city FROM info WHERE id BETWEEN 5 AND 10;
SELECT id,name,sex,age,city FROM info WHERE id NOT BETWEEN 5 AND 10
SELECT id,name,sex,age,city FROM info WHERE age BETWEEN 50 AND 100;
-- 指定集合
SELECT id,name,sex,age,city FROM info WHERE id IN(11,15,3,1,10,5,7)
SELECT id,name,sex,age,city FROM info WHERE id NOT IN(11,15,3,1,10,5,7)；
-- AND 和 OR
-- 查询所有带想字的或者性别为0 的数据全部显示出来
SELECT id,name,sex,age,city FROM info WHERE name !='奇奇' OR sex=1;
  SELECT id,name,sex,age,city FROM info WHERE name !='奇奇' AND sex=0;
-- 去除重复内容得到查询结果 DISTINCT
SELECT DISTINCT city FROM info;

 统计函数和GROUP BY分组

统计函数
COUNT() 计算行数
AVG() 求平均函数
SUM() 求总和
MIN() 求最小
MAX() 求最大

-- 查询所有人的平均年龄
SELECT AVG(age) FROM info;
-- 查询最大年龄
SELECT MAX(age) FROM info;
-- 查询年龄最⼩的.
SELECT MIN(age) FROM info;
-- 查询一共多少行.
SELECT COUNT(id) FROM info;
-- 查询所有人的总年龄
SELECT SUM(age) FROM info;

GROUP BY 分组和CROUP_CONCAT()函数

-- GROUP BY 分组,名字取得谁的?谁先添加取谁的。
-- 你会发现报错，因为mysql无法帮你既要查询id,sex,age,city,还要帮你分组，横行都不对称。
-- 就算不报错，你也会发现出来的数据无意义。
SELECT id,sex,age,city FROM info GROUP BY city;
-- GROUP BY 和 GROUP_CONCAT()归类函数可以实现上面的要求。
SELECT city,GROUP_CONCAT(id),GROUP_CONCAT(name) FROM info GROUP BY city;

HAVING 进一步筛选

HAVEING 对结果集进一步筛选，把having前⾯的内容看成一张表，对这个表的结果进一步筛选

-- GROUP by 和 having
SELECT city, COUNT(id) FROM info GROUP BY city HAVING COUNT(id)>=2;
SELECT city,SUM(age) FROM info GROUP BY city HAVING SUM(age)>=50;


-- 结果集可以起别名形成临时名字用于HAVING
SELECT city, COUNT(id) as ha FROM info GROUP BY city HAVING ha>=2;

ORDER BY 排序

-- ORDER BY 排序
-- ASC：升序（默认），DESC：降序
-- ASC 从小到大查询年龄
SELECT id,name,sex,age,city FROM info ORDER BY age ASC
-- DESC 从大到小
SELECT id,name,sex,age,city FROM info ORDER BY age DESC
-- 如果 age从大到小,我们再使用id从小到大
SELECT id,name,sex,age,city FROM info ORDER BY age DESC,id DESC
-- 如果有where条件,先把大范围求出来再求小范围
SELECT id,name,sex,age,city FROM info WHERE age > 20 ORDER BY age DESC;

 LIMIT 限制取出条目

限制取出条目,跳过多少行，取多少行
参数是:LIMIT m,n

-- LIMIT 不指定初始值
SELECT id,name,sex,age,city FROM info LIMIT 5;
-- LIMIT 指定初始值
SELECT id,name,sex,age,city FROM info LIMIT 0,5;

 CONCAT 拼接函数

-- CONCAT 拼接函数
SELECT id,name,CONCAT(id,name,'hahahh ') FROM info;

为字段和表起别名

-- 为字段起别名
SELECT name '姓名',sex '性别',age '年龄',city '城市' FROM info;
SELECT name as '姓名',age as '年龄' FROM info;


-- 为表起别名
SELECT id,name,sex,age FROM info as i;
-- 起了别名后的表可作为一张新表嵌套使用
select name,sex,age,sum(age) from (SELECT id,name,sex,age FROM info) as i;

模糊查询

模糊查询多用于搜索.
LIKE和NOT LIKE

-- 匹配字符 LIKE NOT LIKE
-- 字符 _ 代表一个字符 % 代表多个字符
-- 模糊查询
SELECT id,name,sex,age,city FROM info WHERE name LIKE '张_';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '习__';
SELECT id,name,sex,age,city FROM info WHERE name LIKE '_强';

-- 以xx开头,后面无所谓,爱咋滴咋滴
SELECT id,name,sex,age,city FROM info WHERE name LIKE '古%';

-- 以xx结尾,前边无所谓,爱咋滴咋滴
SELECT id,name,sex,age,city FROM info WHERE name LIKE '%强';

-- 所有带xx字符的都出来,只要带 猛 的都出来
SELECT id,name,sex,age,city FROM info WHERE name LIKE '%猛%';
SELECT id,name,sex,age,city FROM info WHERE name NOT LIKE '%猛%'

  关联查询

新建一个关联表

- 新建lian表
CREATE TABLE lian(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL DEFAULT '',
 info_id INT UNSIGNED NOT NULL DEFAULT 0
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 为表添加关联内容
INSERT INTO lian(name,info_id) VALUES('如花姐',9);
INSERT INTO lian(name,info_id) VALUES('⽯榴姐',9);
INSERT INTO lian(name,info_id) VALUES('凤姐',9);
INSERT INTO lian(name,info_id) VALUES('芙蓉姐姐',9);
INSERT INTO lian(name,info_id) VALUES('宝强哥',18);
INSERT INTO lian(name,info_id) VALUES('⽻凡哥',18);
INSERT INTO lian(name,info_id) VALUES('霆锋哥',18);
INSERT INTO lian(name,info_id) VALUES('之谦哥',18);
INSERT INTO lian(name,info_id) VALUES('王思聪',6);
INSERT INTO lian(name,info_id) VALUES('韩雪',6);
INSERT INTO lian(name,info_id) VALUES('任达华',6);
INSERT INTO lian(name,info_id) VALUES('陈楚河',6);
- 查询info表中id为6,和lian表中info_id为6的的所有值.注意,相同名字需要起别名,否则冲
突.
-- 多表联合查询首先写select 这⾥先空着 from info i，lian l where 两个表的关联字
段
-- select from info as i,lian as l where i.id=s.info_id;
-- 然后再写select和from之间要查询的字段，注意，名字重复需要起别名，否则mysql会懵逼
SELECT i.id iid,i.name iname,i.sex,i.age,i.city,s.id sid,s.name
sname,s.info_id
FROM info i,lian s WHERE i.id = s.info_id;
-- UNION合并查询,注意,查询的列名数量必须一样,会合并所有相同的.
SELECT id,name FROM info
UNION
SELECT id,name FROM lian;
-- UNION ALL 合并查询,不合并相同的.
(SELECT id,name FROM info)
UNION ALL
(SELECT id,name FROM lian);