在实际工作中,98%的工作都是查询
1.先创建一个info表
1 -- 创建一个表info,分别有名字,年龄,性别,所在城市2 CREATE TABLE IF NOT EXISTS `info`(3 `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,4 `name` VARCHAR(255) NOT NULL DEFAULT '',5 `age` TINYINT UNSIGNED NOT NULL DEFAULT 0,6 `sex` TINYINT NOT NULL DEFAULT 0,7 `city` VARCHAR(255) NOT NULL DEFAULT 08 )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 子句运算符
1 -- 小于大于等于 2 SELECT id,name,sex,age,city FROM info WHERE id=5; 3 SELECT id,name,sex,age,city FROM info WHERE id<5; 4 SELECT id,name,sex,age,city FROM info WHERE id>5; 5 SELECT id,name,sex,age,city FROM info WHERE id<=5; 6 SELECT id,name,sex,age,city FROM info WHERE id>=5; 7 SELECT id,name,sex,age,city FROM info WHERE id!=5; 8 SELECT id,name,sex,age,city FROM info WHERE id<>5; 9 -- 指定范围 BETWEEN AND和NOT BETWEEN AND10 SELECT id,name,sex,age,city FROM info WHERE id BETWEEN 5 AND 10;11 SELECT id,name,sex,age,city FROM info WHERE id NOT BETWEEN 5 AND 1012 SELECT id,name,sex,age,city FROM info WHERE age BETWEEN 50 AND 100;13 -- 指定集合14 SELECT id,name,sex,age,city FROM info WHERE id IN(11,15,3,1,10,5,7)15 SELECT id,name,sex,age,city FROM info WHERE id NOT IN(11,15,3,1,10,5,7)；16 -- AND 和 OR17 -- 查询所有带想字的或者性别为0 的数据全部显示出来18 SELECT id,name,sex,age,city FROM info WHERE name !='奇奇' OR sex=1;19 SELECT id,name,sex,age,city FROM info WHERE name !='奇奇' AND sex=0;20 -- 去除重复内容得到查询结果 DISTINCT21 SELECT DISTINCT city FROM info;
统计函数和GROUP BY分组
统计函数
COUNT() 计算行数AVG() 求平均函数SUM() 求总和MIN() 求最小MAX() 求最大1 -- 查询所有人的平均年龄 2 SELECT AVG(age) FROM info; 3 -- 查询最大年龄 4 SELECT MAX(age) FROM info; 5 -- 查询年龄最⼩的. 6 SELECT MIN(age) FROM info; 7 -- 查询一共多少行. 8 SELECT COUNT(id) FROM info; 9 -- 查询所有人的总年龄10 SELECT SUM(age) FROM info;
GROUP BY 分组和CROUP_CONCAT()函数
1 -- GROUP BY 分组,名字取得谁的?谁先添加取谁的。2 -- 你会发现报错，因为mysql无法帮你既要查询id,sex,age,city,还要帮你分组，横行都不对称。3 -- 就算不报错，你也会发现出来的数据无意义。4 SELECT id,sex,age,city FROM info GROUP BY city;5 -- GROUP BY 和 GROUP_CONCAT()归类函数可以实现上面的要求。6 SELECT city,GROUP_CONCAT(id),GROUP_CONCAT(name) FROM info GROUP BY city;
HAVING 进一步筛选
HAVEING 对结果集进一步筛选，把having前⾯的内容看成一张表，对这个表的结果进一步筛选
1 -- GROUP by 和 having2 SELECT city, COUNT(id) FROM info GROUP BY city HAVING COUNT(id)>=2;3 SELECT city,SUM(age) FROM info GROUP BY city HAVING SUM(age)>=50;4 5 6 -- 结果集可以起别名形成临时名字用于HAVING7 SELECT city, COUNT(id) as ha FROM info GROUP BY city HAVING ha>=2;
ORDER BY 排序
1 -- ORDER BY 排序 2 -- ASC：升序（默认），DESC：降序 3 -- ASC 从小到大查询年龄 4 SELECT id,name,sex,age,city FROM info ORDER BY age ASC 5 -- DESC 从大到小 6 SELECT id,name,sex,age,city FROM info ORDER BY age DESC 7 -- 如果 age从大到小,我们再使用id从小到大 8 SELECT id,name,sex,age,city FROM info ORDER BY age DESC,id DESC 9 -- 如果有where条件,先把大范围求出来再求小范围10 SELECT id,name,sex,age,city FROM info WHERE age > 20 ORDER BY age DESC;
LIMIT 限制取出条目
限制取出条目,跳过多少行，取多少行
参数是:LIMIT m,n1 -- LIMIT 不指定初始值2 SELECT id,name,sex,age,city FROM info LIMIT 5;3 -- LIMIT 指定初始值4 SELECT id,name,sex,age,city FROM info LIMIT 0,5;
CONCAT 拼接函数
1 -- CONCAT 拼接函数2 SELECT id,name,CONCAT(id,name,'hahahh ') FROM info;
为字段和表起别名
1 -- 为字段起别名2 SELECT name '姓名',sex '性别',age '年龄',city '城市' FROM info;3 SELECT name as '姓名',age as '年龄' FROM info;4 5 6 -- 为表起别名7 SELECT id,name,sex,age FROM info as i;8 -- 起了别名后的表可作为一张新表嵌套使用9 select name,sex,age,sum(age) from (SELECT id,name,sex,age FROM info) as i;
模糊查询
模糊查询多用于搜索.
LIKE和NOT LIKE1 -- 匹配字符 LIKE NOT LIKE 2 -- 字符 _ 代表一个字符 % 代表多个字符 3 -- 模糊查询 4 SELECT id,name,sex,age,city FROM info WHERE name LIKE '张_'; 5 SELECT id,name,sex,age,city FROM info WHERE name LIKE '习__'; 6 SELECT id,name,sex,age,city FROM info WHERE name LIKE '_强'; 7 8 -- 以xx开头,后面无所谓,爱咋滴咋滴 9 SELECT id,name,sex,age,city FROM info WHERE name LIKE '古%';10 11 -- 以xx结尾,前边无所谓,爱咋滴咋滴12 SELECT id,name,sex,age,city FROM info WHERE name LIKE '%强';13 14 -- 所有带xx字符的都出来,只要带 猛 的都出来15 SELECT id,name,sex,age,city FROM info WHERE name LIKE '%猛%';16 SELECT id,name,sex,age,city FROM info WHERE name NOT LIKE '%猛%'
关联查询
新建一个关联表
1 - 新建lian表 2 CREATE TABLE lian( 3 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 4 name VARCHAR(255) NOT NULL DEFAULT '', 5 info_id INT UNSIGNED NOT NULL DEFAULT 0 6 )ENGINE=MyISAM DEFAULT CHARSET=utf8; 7 -- 为表添加关联内容 8 INSERT INTO lian(name,info_id) VALUES('如花姐',9); 9 INSERT INTO lian(name,info_id) VALUES('⽯榴姐',9);10 INSERT INTO lian(name,info_id) VALUES('凤姐',9);11 INSERT INTO lian(name,info_id) VALUES('芙蓉姐姐',9);12 INSERT INTO lian(name,info_id) VALUES('宝强哥',18);13 INSERT INTO lian(name,info_id) VALUES('⽻凡哥',18);14 INSERT INTO lian(name,info_id) VALUES('霆锋哥',18);15 INSERT INTO lian(name,info_id) VALUES('之谦哥',18);16 INSERT INTO lian(name,info_id) VALUES('王思聪',6);17 INSERT INTO lian(name,info_id) VALUES('韩雪',6);18 INSERT INTO lian(name,info_id) VALUES('任达华',6);19 INSERT INTO lian(name,info_id) VALUES('陈楚河',6);20 - 查询info表中id为6,和lian表中info_id为6的的所有值.注意,相同名字需要起别名,否则冲21 突.22 -- 多表联合查询首先写select 这⾥先空着 from info i，lian l where 两个表的关联字23 段24 -- select from info as i,lian as l where i.id=s.info_id;25 -- 然后再写select和from之间要查询的字段，注意，名字重复需要起别名，否则mysql会懵逼26 SELECT i.id iid,i.name iname,i.sex,i.age,i.city,s.id sid,s.name27 sname,s.info_id28 FROM info i,lian s WHERE i.id = s.info_id;29 -- UNION合并查询,注意,查询的列名数量必须一样,会合并所有相同的.30 SELECT id,name FROM info31 UNION32 SELECT id,name FROM lian;33 -- UNION ALL 合并查询,不合并相同的.34 (SELECT id,name FROM info)35 UNION ALL36 (SELECT id,name FROM lian);