having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
1.5.6 【select高级应用】数据库备份脚本拼接
SELECT CONCAT("mysqldump ","-uroot ","-p123 ",table_schema," ",table_name,">/tmp/",table_schema,"_",table_name,".sql") FROM information_schema.tables WHERE table_schema='world' INTO OUTFILE '/tmp/world_bak.sh'
使用concat进行拼接数据备份脚本。
-- 显示信息，可直接进行运算
SELECT CONCAT("132")； SELECT CONCAT("132+123")； SELECT CONCAT("132+123")；
-- 查看引擎是innodb的表
SELECT TABLE_NAME FROM TABLES WHERE ENGINE='innodb'; SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT = 'Yes';
-- 显示每个库下有多少表
SELECT TABLE_SCHEMA ,COUNT(*) FROM information_schema.`TABLES` GROUP BY TABLE_SCHEMA;
1.5.7 子查询子查询定义
在一个表表达中可以调用另一个表表达式，这个被调用的表表达式叫做子查询（subquery），我么也称作子选择（subselect）或内嵌选择（inner select）。子查询的结果传递给调用它的表表达式继续处理。
子查询（inner query）先执行，然后执行主查询（outer query）
子查询按对返回结果集的调用方法，可分为：where型子查询，from型子查询及exists型子查询。
使用子查询原则
一个子查询必须放在圆括号中。
将子查询放在比较条件的右边以增加可读性。
子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。
在子查询中可以使用两种比较条件：单行运算符(>, =, >=, <, <>, <=) 和多行运算符(IN, ANY, ALL)。
不相关子查询
子查询中没有使用到外部查询的表中的任何列。先执行子查询，然后执行外部查询
相关子查询(correlated subquery)
子查询中使用到了外部查询的表中的任何列。先执行外部查询，然后执行子查询
以上两种类型之下又可以分为：
行子查询(row subquery)：返回的结果集是 1 行 N 列 列子查询(column subquery)：返回的结果集是 N 行 1列 表子查询(table subquery)：返回的结果集是 N 行 N 列 标量子查询(scalar subquery)：返回1行1列一个值
子查询示例
创建数据表
1 CREATE TABLE PLAYERS 2 (PLAYERNO INTEGER NOT NULL, 3 NAME CHAR(15) NOT NULL, 4 INITIALS CHAR(3) NOT NULL, 5 BIRTH_DATE DATE , 6 SEX CHAR(1) NOT NULL, 7 JOINED SMALLINT NOT NULL, 8 STREET VARCHAR(30) NOT NULL, 9 HOUSENO CHAR(4) , 10 POSTCODE CHAR(6) , 11 TOWN VARCHAR(30) NOT NULL, 12 PHONENO CHAR(13) , 13 LEAGUENO CHAR(4) , 14 PRIMARY KEY (PLAYERNO)); 15 16 CREATE TABLE PENALTIES 17 (PAYMENTNO INTEGER NOT NULL, 18 PLAYERNO INTEGER NOT NULL, 19 PAYMENT_DATE DATE NOT NULL, 20 AMOUNT DECIMAL(7,2) NOT NULL, 21 PRIMARY KEY (PAYMENTNO)); 22 23 INSERT INTO PLAYERS VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411'); 24 INSERT INTO PLAYERS VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467'); 25 INSERT INTO PLAYERS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL); 26 INSERT INTO PLAYERS VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983'); 27 INSERT INTO PLAYERS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513'); 28 INSERT INTO PLAYERS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL); 29 INSERT INTO PLAYERS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL); 30 INSERT INTO PLAYERS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124'); 31 INSERT INTO PLAYERS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458', '6409'); 32 INSERT INTO PLAYERS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratford', '070-353548', '1608'); 33 INSERT INTO PLAYERS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL); 34 INSERT INTO PLAYERS VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524'); 35 INSERT INTO PLAYERS VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060'); 36 INSERT INTO PLAYERS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319'); 37 38 INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100); 39 INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75); 40 INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100); 41 INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50); 42 INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25); 43 INSERT INTO PENALTIES VALUES (6, 8, '1980-12-08', 25); 44 INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30); 45 INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);
创建数据库语句例一、获取和100号球员性别相同并且居住在同一城市的球员号码。
select playerno from players where (sex, town) = ( select sex, town from players where playerno = 100);
例二、获取和27号球员出生在同一年的球员的号码
select playerno from players where year(birth_date) = (select year(birth_date) from players where playerno = 27) and playerno <> 27;
例三、获取那些至少支付了一次罚款的球员的名字和首字母。
select name, initials from players where exists (select * from penalties where playerno = players.playerno);
例四、获取那些从来没有罚款的球员的名字和首字母。