MySQL之基础查询（DQL）
SELECT语句
语法：
SELECT 要查询的东西 【FROM 表名】;
SELECT：标识选择哪些列
FROM：标识从哪个表中选择
*：选择全部列
特点：
通过select查询完的结果，是一个虚拟的表格，不是真实存在。
要查询的东西可以是常量值、表达式、字段、函数。
列的别名
介绍：
重命名一个列，便于计算。紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
示例：
SELECT last_name AS name,commission_pct AS comm FROM employees; SELECT last_name "Name",salary*12 "Annual Salary" FROM employees;
案例讲解
#1.查询表中的单个字段 SELECT last_name FROM employees; #2.查询表中的多个字段 SELECT last_name,salary,email FROM employees; #3.查询表中的所有字段 #方式一： SELECT employee_id,first_name,last_name,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredate FROM employees; #方式二： SELECT * FROM employees; #4.查询常量值 SELECT 100; SELECT 'john'; #5.查询表达式 SELECT 100%98; #6.查询函数 SELECT VERSION(); #7.起别名 /* * ①便于理解 * ②如果要查询的字段有重名的情况，使用别名可以区分开来 */ #方式一：使用as SELECT 100%98 AS 结果; SELECT last_name AS 姓,first_name AS 名 FROM employees; #方式二：使用空格 SELECT last_name 姓,first_name 名 FROM employees; #案例：查询salary，显示结果为out put SELECT salary AS "out put" FROM employees; #8.去重 #案例：查询员工表中涉及到的所有的部门编号 SELECT DISTINCT department_id FROM employees; #9.+号的作用 /* * java中的+号： * ①运算符，两个操作数都为数值型 * ②连接符，只要有一个操作数为字符串 * * mysql中的+号： * 仅仅只有一个功能：运算符 * * select 100+90;两个操作数都为数值型，则做加法运算 * select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算 * select 'john'+90;如果转换失败，则将字符型数值转换成0 * select null+10;只要其中一方为null，则结果肯定为null */ #案例：查询员工名和姓连接成一个字段，并显示为姓名 SELECT CONCAT('a','b','c') AS 结果; SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
测试数据
#员工表 CREATE TABLE `employees` ( `employee_id` int(6) NOT NULL AUTO_INCREMENT, `first_name` varchar(20) DEFAULT NULL, `last_name` varchar(25) DEFAULT NULL, `email` varchar(25) DEFAULT NULL, `phone_number` varchar(20) DEFAULT NULL, `job_id` varchar(10) DEFAULT NULL, `salary` double(10,2) DEFAULT NULL, `commission_pct` double(4,2) DEFAULT NULL, `manager_id` int(6) DEFAULT NULL, `department_id` int(4) DEFAULT NULL, `hiredate` datetime DEFAULT NULL, PRIMARY KEY (`employee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; #员工数据 INSERT INTO employees VALUES ('100', 'Steven', 'K_ing', 'SKING', '515.123.4567', 'AD_PRES', '24000.00', null, null, '90', '1992-04-03 00:00:00'); INSERT INTO employees VALUES ('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 'AD_VP', '17000.00', null, '100', '90', '1992-04-03 00:00:00'); INSERT INTO employees VALUES ('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', 'AD_VP', '17000.00', null, '100', '90', '1992-04-03 00:00:00'); INSERT INTO employees VALUES ('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', 'IT_PROG', '9000.00', null, '102', '60', '1992-04-03 00:00:00'); INSERT INTO employees VALUES ('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', 'IT_PROG', '6000.00', null, '103', '60', '1992-04-03 00:00:00'); INSERT INTO employees VALUES ('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', 'IT_PROG', '4800.00', null, '103', '60', '1998-03-03 00:00:00'); INSERT INTO employees VALUES ('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', 'IT_PROG', '4800.00', null, '103', '60', '1998-03-03 00:00:00'); INSERT INTO employees VALUES ('107', 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', 'IT_PROG', '4200.00', null, '103', '60', '1998-03-03 00:00:00'); INSERT INTO employees VALUES ('108', 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', 'FI_MGR', '12000.00', null, '101', '100', '1998-03-03 00:00:00'); INSERT INTO employees VALUES ('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', 'FI_ACCOUNT', '9000.00', null, '108', '100', '1998-03-03 00:00:00'); INSERT INTO employees VALUES ('110', 'John', 'Chen', 'JCHEN', '515.124.4269', 'FI_ACCOUNT', '8200.00', null, '108', '100', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('111', 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', 'FI_ACCOUNT', '7700.00', null, '108', '100', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('112', 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', 'FI_ACCOUNT', '7800.00', null, '108', '100', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('113', 'Luis', 'Popp', 'LPOPP', '515.124.4567', 'FI_ACCOUNT', '6900.00', null, '108', '100', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('114', 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', 'PU_MAN', '11000.00', null, '100', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('115', 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', 'PU_CLERK', '3100.00', null, '114', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('116', 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', 'PU_CLERK', '2900.00', null, '114', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('117', 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', 'PU_CLERK', '2800.00', null, '114', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('118', 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', 'PU_CLERK', '2600.00', null, '114', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('119', 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', 'PU_CLERK', '2500.00', null, '114', '30', '2000-09-09 00:00:00'); INSERT INTO employees VALUES ('120', 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', 'ST_MAN', '8000.00', null, '100', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('121', 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', 'ST_MAN', '8200.00', null, '100', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('122', 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', 'ST_MAN', '7900.00', null, '100', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('123', 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', 'ST_MAN', '6500.00', null, '100', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('124', 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', 'ST_MAN', '5800.00', null, '100', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('125', 'Julia', 'Nayer', 'JNAYER', '650.124.1214', 'ST_CLERK', '3200.00', null, '120', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('126', 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', 'ST_CLERK', '2700.00', null, '120', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('127', 'James', 'Landry', 'JLANDRY', '650.124.1334', 'ST_CLERK', '2400.00', null, '120', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('128', 'Steven', 'Markle', 'SMARKLE', '650.124.1434', 'ST_CLERK', '2200.00', null, '120', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('129', 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', 'ST_CLERK', '3300.00', null, '121', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('130', 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', 'ST_CLERK', '2800.00', null, '121', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('131', 'James', 'Marlow', 'JAMRLOW', '650.124.7234', 'ST_CLERK', '2500.00', null, '121', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('132', 'TJ', 'Olson', 'TJOLSON', '650.124.8234', 'ST_CLERK', '2100.00', null, '121', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('133', 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', 'ST_CLERK', '3300.00', null, '122', '50', '2004-02-06 00:00:00'); INSERT INTO employees VALUES ('134', 'Michael', 'Rogers', 'MROGERS', '650.127.1834', 'ST_CLERK', '2900.00', null, '122', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('135', 'Ki', 'Gee', 'KGEE', '650.127.1734', 'ST_CLERK', '2400.00', null, '122', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('136', 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', 'ST_CLERK', '2200.00', null, '122', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('137', 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', 'ST_CLERK', '3600.00', null, '123', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('138', 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', 'ST_CLERK', '3200.00', null, '123', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('139', 'John', 'Seo', 'JSEO', '650.121.2019', 'ST_CLERK', '2700.00', null, '123', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('140', 'Joshua', 'Patel', 'JPATEL', '650.121.1834', 'ST_CLERK', '2500.00', null, '123', '50', '2002-12-23 00:00:00'); INSERT INTO employees VALUES ('171', 'William', 'Smith', 'WSMITH', '011.44.1343.629268', 'SA_REP', '7400.00', '0.15', '148', '80', '2014-03-05 00:00:00'); INSERT INTO employees VALUES ('172', 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', 'SA_REP', '7300.00', '0.15', '148', '80', '2014-03-05 00:00:00'); INSERT INTO employees VALUES ('173', 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', 'SA_REP', '6100.00', '0.10', '148', '80', '2014-03-05 00:00:00'); INSERT INTO employees VALUES ('201', 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 'MK_MAN', '13000.00', null, '100', '20', '2016-03-03 00:00:00'); INSERT INTO employees VALUES ('202', 'Pat', 'Fay', 'PFAY', '603.123.6666', 'MK_REP', '6000.00', null, '201', '20', '2016-03-03 00:00:00'); INSERT INTO employees VALUES ('203', 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 'HR_REP', '6500.00', null, '101', '40', '2016-03-03 00:00:00'); INSERT INTO employees VALUES ('204', 'Hermann', 'Baer', 'HBAER', '515.123.8888', 'PR_REP', '10000.00', null, '101', '70', '2016-03-03 00:00:00'); INSERT INTO employees VALUES ('205', 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 'AC_MGR', '12000.00', null, '101', '110', '2016-03-03 00:00:00'); INSERT INTO employees VALUES ('206', 'William', 'Gietz', 'WGIETZ', '515.123.8181', 'AC_ACCOUNT', '8300.00', null, '205', '110', '2016-03-03 00:00:00');
