二、MySQL查询

2018-03-19 本文已影响0人 AKyS佐毅

1、SQL的分类

Select查询语句
DML语句（数据操作语言） Insert / Update / Delete
DDL语句（数据定义语言） Create / Alter / Drop / Truncate(删除数据立即生效)
DCL语句（数据控制语言） Grant（赋于权限） / Revoke（回收权限）
事务控制语句TCL Commit / Rollback

SELECT [DISTINCT] {*, column [alias], ...}  FROM table [WHERE   condition(s)];

1、全列/投影查询

SELECT  employees.department_id, employees.first_name , employees.last_name ,employees.manager_id ,employees.phone_int,  employees.hire_date  FROM employees;

SELECT * FROM departments;

2、算术操作符
- 对NUMBER型数据可以使用算数操作符创建表达式（+ - * /）

SELECT first_name ,last_name , salary*12  salarys FROM employees;

3、空值
1、空值是指不可用、未分配的值,也就是没有值。
2、空值不等于零或空格
3、任意类型都可以支持空值，也就是说任何类型的字段都可以允许空值作为值的存在

注意:包括空值的任何算术表达式都等于空

SELECT `employees`.`commission_pct` FROM employees WHERE employee_id = 206;

4、去重
distinct关键字可以用于一列，也可以用于多列

SELECT  DISTINCT `employees`.`department_id` FROM employees  WHERE department_id is not NULL;

SELECT  DISTINCT `employees`.`department_id` ,`employees`.`jod_id` FROM employees;

5、where条件中的字符串和日期

注意：
1、字符串和日期要用单引号扩起来
2、数字类型直接书写
3、字符串是大小写不敏感的,日期值是格式敏感的
4、字符串敏感，需要添加binary关键字

SELECT * FROM employees WHERE hire_date = '1989-09-21';

SELECT  `employees`.`jod_id` FROM employees WHERE first_name = 'Neena';

SELECT  `employees`.`jod_id` FROM employees WHERE BINARY first_name = 'neena';

6、比较运算符
- 1、常用算术比较运算符；
- 2、BETWEEN AND：在两值之间 (包含开始和结尾)；
- 3、IN（list）：匹配列出的值；
- 4、LIKE ：匹配字符串模式；
  - 1、在匹配的模式中,随便写一个符号;一般使用/或者,写在要转义的字符之前;
  - 2、在LIKE之后加上一个ESCAPE '转义符号';代表,在前面那个LIKE的匹配模式中,使用转义符号进行转义;
- 5、IS NULL：是否为空；

SELECT * FROM employees WHERE hire_date  > '1989-09-21';

SELECT first_name ,last_name , salary  FROM employees WHERE salary between 8000 and 10000;

SELECT first_name ,last_name , salary  FROM employees WHERE salary IN (10000,11000,12000,13000);

SELECT last_name  FROM employees WHERE last_name like '_o%';

SELECT * FROM departments WHERE department_name LIKE 'CHINA/_%' ESCAPE '/';

SELECT first_name,manager_id FROM employees WHERE manager_id is null;

7、逻辑运算符
- AND：如果组合的条件都是TRUE,返回TRUE
- OR：如果组合的条件之一是TRUE,返回TRUE
- NOT：如果下面的条件是FALSE,返回TRUE

优先级规则：比较运算符 > NOT > AND > OR

8、排序
使用ORDER BY 子句将记录排序
ASC: 升序,缺省
DESC: 降序

SELECT first_name,last_name ,salary*12 salarys, department_id FROM employees ORDER BY salarys DESC;
SELECT first_name,last_name ,salary*12 salarys, department_id FROM employees ORDER BY  department_id DESC, salarys DESC;

2、MySQL的查询函数

1、字符函数:
- LOWER(str)：返回字符串str变为小写字母的字符
- UPPER(str)：返回字符串str变为大写字母的字符
- CONCAT(a,b,....):把字符串连接起来;连成一个字符串;参数可以是多个值;
- CHAR_LENGTH/LENGHT:都是在判断字符串长度的;char_length:字符个数;length:字节个数;
- LPAD/RPAD:左填补/右填补:LPAD(str,length,padstr),在str字符串的左边填补padstr,让整个字符串的长度达到length;
- LTRIM（str）：左边空格被trim掉；
- RTRIM（str）：右边空格被trim掉；
- TRIM(str)=LTRIM+RTRIM
- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)：高级用法，从str中按照指定方式截取remstr；
- REPLACE(str,from_str,to_str)：
格式：INSERT(str,pos,len,newstr)
- 1，返回字符串str, 其子字符串起始于pos 位置和长度被字符串 newstr取代的len 字符。
- 2，如果pos 超过字符串长度，则返回值为原始字符串。
- 3，假如len的长度大于其它字符串的长度，则从位置pos开始替换。
- 4，若任何一个参数为null，则返回值为NULL
- 示例：
  给用户名加密；保留用户名前3位，中间4位使用*代替，如果姓名还有多余的字符，保留；

 SELECT INSERT('aaaaa',2,3,'bbb');   //结果： abbba

SELECT UPPER(first_name) ,LOWER(last_name) FROM employees ;
SELECT concat(first_name,'-',last_name) FROM employees;

CHAR_LENGTH：字符串长度；
LENGTH：字符串长度（单位为字节）；

对比试验：

 SELECT CHAR_LENGTH('AKySBLANK');
 SELECT CHAR_LENGTH('姚亚杰');

LPAD(str,len,padstr)
- 1、返回字符串str, 其左边由字符串padstr 填补到len 字符长度。
- 2、假如str 的长度大于len, 则返回值被缩短至len 字符。
RPAD(str,len,padstr)
- 1、返回字符串str, 其右边被字符串padstr填补至len 字符长度。
- 2、假如字符串str 的长度大于 len,则返回值被缩短到与len 字符相同长度。

SELECT LPAD(first_name,20,last_name) FROM employees;

LTRIM/RTRIM/TRIM

SELECT TRIM('    aaaaa   ');
SELECT CHAR_LENGTH(TRIM('     a   '));

REPLACE(str,from_str,to_str)：
- 1、在str中把from_str全部替换为to_str；
- 2、大小写敏感；

SELECT REPLACE(first_name,'av','****') FROM employees;

SUBSTRING(str,pos)：
- 从字符串str返回一个子字符串，起始于位置pos 。SUBSTRING(str,pos,len)：
- 从字符串str返回一个长度同len字符相同的子字符串，起始于位置pos
- 如果pos是负数，从字符串尾部开始计算；

SELECT SUBSTRING('BLANKAKyS',2,3);   //LAN
SELECT SUBSTRING('BLANKAKyS',-2,3);   //yS

2、数字函数
ABS（x）：返回一个数字的绝对值；
MOD(N,M)：返回N 被 M除后的余数；
CEIL(x)：返回不小于X 的最小整数值；
FLOOR(x)：返回不大于X的最大整数值；
ROUND(X) ROUND(X,D)：
- 1、返回参数X, 其值接近于最近似的整数。
- 2、在有两个参数的情况下，返回X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。
- 3、若要接保留X值小数点左边的D 位，可将 D 设为负值。
TRUNCATE(X,D)
- 1、返回被舍去至小数点后D位的数字X。
- 2、若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.

SELECT ABS(-2);    //2
SELECT MOD(10,3);    //1
SELECT FLOOR(23.9);    //23
SELECT ROUND(2.4);    // 2
SELECT ROUND(2.4,2);  //2.40
SELECT TRUNCATE(399.149,2); //399.14

3、日期函数
- DATE_ADD(date,INTERVAL expr type)/DATE_SUB(date,INTERVAL expr type)
  - 1、执行日期运算；
  - 2、date 是一个 DATETIME 或DATE值，用来指定起始时间；
  - 3、expr 是一个字符串表达式，用来指定从起始日期添加或减去的时间间隔值；
  - 4、type 为关键词，它指示了表达式被解释的方式
```
 SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);    //2019-03-19 16:14:37
 SELECT DATE_ADD('2018-01-01 00:00:00',INTERVAL '1:1' MINUTE_SECOND); //2018-01-01 00:01:01
```
- CURRENT_DATE/CURRENT_TIME
- DATEDIFF(expr,expr2)：返回起始时间expr和结束时间expr2之间的天数
```
SELECT DATEDIFF('2018-01-01 00:00:00','2019-01-01 00:00:00');
```
- 获取日期中的某个时间
```
 SELECT  month(NOW());
 SELECT  year(NOW());
 SELECT  day(NOW());
 SELECT  hour(NOW());
 SELECT  MINUTE(NOW());
 SELECT LAST_DAY(NOW());   //2018-03-31
```
- UNIX_TIMESTAMP(date)：它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回
  FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)：返回'YYYY-MM-DD HH:MM:SS'或指定format的日期
  - 性能要求比较高的应用当中,时间都不是保存为date或者datetime或者timestamp类型的,时间都是保存为bigint类型的;因为MYSQL处理时间的效率是比较慢的;
```
SELECT UNIX_TIMESTAMP(NOW());
SELECT FROM_UNIXTIME(1521448019);
SELECT  CEIL(DATEDIFF(NOW(),hire_date)/30)  FROM employees;
```
4、转换函数
- 数字和字符串
  - FORMAT(X,D)
    - 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后D 位，并将结果以字符串的形式返回。若D 为 0, 则返回结果不带有小数点，或不含小数部分。
- 日期和字符串

select date_format(hire_date,'%Y年%m月%d日 %H:%i:%s') from employees;
select * from employees where YEAR(hire_date)='1999';
select * from employees where hire_date between str_to_date('1995年3月','%Y年%m') and str_to_date(now(),'%Y年%m');
SELECT STR_TO_DATE(hire_date+1,'%Y%m%d') FROM employees WHERE employee_id = 100;

5、其他函数

SELECT UUID(); //随机数
COALESCE：返回值为列表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL

 SELECT COALESCE(null,null,'a','b');     //a

IF/IFNULL语句

SELECT first_name ,last_name ,salary*12*(1+ IFNULL(commission_pct,0)) from employees;

CASE

 select first_name,last_name,salary,
 case when salary < 5000 then 'D'
       when salary < 10000 and salary >=5000 then 'C'
       when salary < 15000 and salary >=10000 then 'B'
else 'A'
END
from employees;

Group By
- Having字句的执行是在分组之后,select之前执行的,所以having字句中的条件不能使用select中的别名;

select department_id,avg(salary) from employees where department_id is not null group by department_id;
select department_id,avg(salary) from employees where department_id is not null group by department_id   HAVING AVG(salary)>8000;

3、练习使用的数据库文件和查询语句

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `countries`
-- ----------------------------
DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
  `country_id` char(2) NOT NULL COMMENT '国家id',
  `country_name` varchar(40) DEFAULT NULL COMMENT '国家名字',
  `region_id` int(11) DEFAULT NULL COMMENT '所属地域ID',
  PRIMARY KEY (`country_id`),
  KEY `countr_reg_fk` (`region_id`),
  CONSTRAINT `countr_reg_fk` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `countries`
-- ----------------------------
BEGIN;
INSERT INTO `countries` VALUES ('AR', 'Argentina', '2'), ('AU', 'Australia', '3'), ('BE', 'Belgium', '1'), ('BR', 'Brazil', '2'), ('CA', 'Canada', '2'), ('CH', 'Switzerland', '1'), ('CN', 'China', '3'), ('DE', 'Germany', '1'), ('DK', 'Denmark', '1'), ('EG', 'Egypt', '4'), ('FR', 'France', '1'), ('HK', 'HongKong', '3'), ('IL', 'Israel', '4'), ('IN', 'India', '3'), ('IT', 'Italy', '1'), ('JP', 'Japan', '3'), ('KW', 'Kuwait', '4'), ('MX', 'Mexico', '2'), ('NG', 'Nigeria', '4'), ('NL', 'Netherlands', '1'), ('SG', 'Singapore', '3'), ('UK', 'United Kingdom', '1'), ('US', 'United States of America', '2'), ('ZM', 'Zambia', '4'), ('ZW', 'Zimbabwe', '4');
COMMIT;

-- ----------------------------
--  Table structure for `departments`
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `department_name` varchar(30) NOT NULL COMMENT '部门名称',
  `manager_id` int(6) DEFAULT NULL COMMENT '部门管理员ID',
  `location_id` int(4) DEFAULT NULL COMMENT '部门地址ID',
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `dept_id_pk` (`department_id`),
  KEY `dept_mgr_fk` (`manager_id`),
  CONSTRAINT `dept_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `departments`
-- ----------------------------
BEGIN;
INSERT INTO `departments` VALUES ('120', 'Treasury', null, '1700'), ('130', 'Corporate Tax', null, '1700'), ('140', 'Control And Credit', null, '1700'), ('150', 'Shareholder Services', null, '1700'), ('160', 'Benefits', null, '1700'), ('170', 'Manufacturing', null, '1700'), ('180', 'Construction', null, '1700'), ('190', 'Contracting', null, '1700'), ('200', 'Operations', null, '1700'), ('210', 'IT Support', null, '1700'), ('220', 'NOC', null, '1700'), ('230', 'IT Helpdesk', null, '1700'), ('240', 'Government Sales', null, '1700'), ('250', 'Retail Sales', null, '1700'), ('260', 'Recruiting', null, '1700'), ('270', 'Payroll', null, '1700'), ('280', 'CHINA_It', null, '2000'), ('290', 'CHINA_Sales', null, '2000'), ('300', 'CHINASales', null, '1700');
COMMIT;

-- ----------------------------
--  Table structure for `employees`
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `first_name` varchar(20) DEFAULT NULL COMMENT '姓',
  `last_name` varchar(25) NOT NULL COMMENT '名',
  `email` varchar(25) NOT NULL COMMENT '邮箱',
  `phone_int` varchar(20) DEFAULT NULL COMMENT '电话',
  `hire_date` date NOT NULL COMMENT '雇佣时间',
  `jod_id` varchar(10) NOT NULL COMMENT '工作id',
  `salary` decimal(8,2) DEFAULT NULL COMMENT '月薪',
  `commission_pct` decimal(2,2) DEFAULT NULL COMMENT '奖金点',
  `manager_id` int(6) DEFAULT NULL COMMENT '该员工经理ID',
  `department_id` int(4) DEFAULT NULL COMMENT '该员工所属部门id',
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8 COMMENT='员工表\n';

-- ----------------------------
--  Records of `employees`
-- ----------------------------
BEGIN;
INSERT INTO `employees` VALUES ('100', 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', '24000.00', null, null, '90'), ('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', '17000.00', null, '100', '90'), ('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1993-01-13', 'AD_VP', '17000.00', null, '100', '90'), ('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1990-01-03', 'IT_PROG', '9000.00', null, '102', '60'), ('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1991-05-21', 'IT_PROG', '6000.00', null, '103', '60'), ('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1997-06-25', 'IT_PROG', '4800.00', null, '103', '60'), ('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '1998-02-05', 'IT_PROG', '4800.00', null, '103', '60'), ('107', 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1999-02-07', 'IT_PROG', '4200.00', null, '103', '60'), ('108', 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '1994-08-17', 'FI_MGR', '12000.00', null, '101', '100'), ('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1994-08-16', 'FI_ACCOUNT', '9000.00', null, '108', '100'), ('110', 'John', 'Chen', 'JCHEN', '515.124.4269', '1997-09-28', 'FI_ACCOUNT', '8200.00', null, '108', '100'), ('111', 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '1997-09-30', 'FI_ACCOUNT', '7700.00', null, '108', '100'), ('112', 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '1998-03-07', 'FI_ACCOUNT', '7800.00', null, '108', '100'), ('113', 'Luis', 'Popp', 'LPOPP', '515.124.4567', '1999-12-07', 'FI_ACCOUNT', '6900.00', null, '108', '100'), ('114', 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '1994-12-07', 'PU_MAN', '11000.00', null, '100', '30'), ('115', 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '1995-05-18', 'PU_CLERK', '3100.00', null, '114', '30'), ('116', 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '1997-12-24', 'PU_CLERK', '2900.00', null, '114', '30'), ('117', 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '1997-07-24', 'PU_CLERK', '2800.00', null, '114', '30'), ('118', 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '1998-11-15', 'PU_CLERK', '2600.00', null, '114', '30'), ('119', 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '1999-08-10', 'PU_CLERK', '2500.00', null, '114', '30'), ('120', 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '1996-07-18', 'ST_MAN', '8000.00', null, '100', '50'), ('121', 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '1997-04-10', 'ST_MAN', '8200.00', null, '100', '50'), ('122', 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '1995-05-01', 'ST_MAN', '7900.00', null, '100', '50'), ('123', 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '1997-10-10', 'ST_MAN', '6500.00', null, '100', '50'), ('124', 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1999-11-16', 'ST_MAN', '5800.00', null, '100', '50'), ('125', 'Julia', 'Nayer', 'JNAYER', '650.124.1214', '1997-07-16', 'ST_CLERK', '3200.00', null, '120', '50'), ('126', 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', '1998-09-28', 'ST_CLERK', '2700.00', null, '120', '50'), ('127', 'James', 'Landry', 'JLANDRY', '650.124.1334', '1999-01-14', 'ST_CLERK', '2400.00', null, '120', '50'), ('128', 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '2000-03-08', 'ST_CLERK', '2200.00', null, '120', '50'), ('129', 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '1997-08-20', 'ST_CLERK', '3300.00', null, '121', '50'), ('130', 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '1997-10-30', 'ST_CLERK', '2800.00', null, '121', '50'), ('131', 'James', 'Marlow', 'JAMRLOW', '650.124.7234', '1997-02-16', 'ST_CLERK', '2500.00', null, '121', '50'), ('132', 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '1999-04-10', 'ST_CLERK', '2100.00', null, '121', '50'), ('133', 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '1996-06-14', 'ST_CLERK', '3300.00', null, '122', '50'), ('134', 'Michael', 'Rogers', 'MROGERS', '650.127.1834', '1998-08-26', 'ST_CLERK', '2900.00', null, '122', '50'), ('135', 'Ki', 'Gee', 'KGEE', '650.127.1734', '1999-12-12', 'ST_CLERK', '2400.00', null, '122', '50'), ('136', 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', '2000-02-06', 'ST_CLERK', '2200.00', null, '122', '50'), ('137', 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '1995-07-14', 'ST_CLERK', '3600.00', null, '123', '50'), ('138', 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '1997-10-26', 'ST_CLERK', '3200.00', null, '123', '50'), ('139', 'John', 'Seo', 'JSEO', '650.121.2019', '1998-02-12', 'ST_CLERK', '2700.00', null, '123', '50'), ('140', 'Joshua', 'Patel', 'JPATEL', '650.121.1834', '1998-04-06', 'ST_CLERK', '2500.00', null, '123', '50'), ('141', 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '1995-10-17', 'ST_CLERK', '3500.00', null, '124', '50'), ('142', 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '1997-01-29', 'ST_CLERK', '3100.00', null, '124', '50'), ('143', 'Randall', 'Matos', 'RMATOS', '650.121.2874', '1998-03-15', 'ST_CLERK', '2600.00', null, '124', '50'), ('144', 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '1998-07-09', 'ST_CLERK', '2500.00', null, '124', '50'), ('145', 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '1996-10-01', 'SA_MAN', '14000.00', '0.40', '100', '80'), ('146', 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '1997-01-05', 'SA_MAN', '13500.00', '0.30', '100', '80'), ('147', 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '1997-03-10', 'SA_MAN', '12000.00', '0.30', '100', '80'), ('148', 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '1999-10-15', 'SA_MAN', '11000.00', '0.30', '100', '80'), ('149', 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2000-01-29', 'SA_MAN', '10500.00', '0.20', '100', '80'), ('150', 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', '1997-01-30', 'SA_REP', '10000.00', '0.30', '145', '80'), ('151', 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', '1997-03-24', 'SA_REP', '9500.00', '0.25', '145', '80'), ('152', 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', '1997-08-20', 'SA_REP', '9000.00', '0.25', '145', '80'), ('153', 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', '1998-03-30', 'SA_REP', '8000.00', '0.20', '145', '80'), ('154', 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', '1998-12-09', 'SA_REP', '7500.00', '0.20', '145', '80'), ('155', 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', '1999-11-23', 'SA_REP', '7000.00', '0.15', '145', '80'), ('156', 'Janette', 'King', 'JKING', '011.44.1345.429268', '1996-01-30', 'SA_REP', '10000.00', '0.35', '146', '80'), ('157', 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', '1996-03-04', 'SA_REP', '9500.00', '0.35', '146', '80'), ('158', 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', '1996-08-01', 'SA_REP', '9000.00', '0.35', '146', '80'), ('159', 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', '1997-03-10', 'SA_REP', '8000.00', '0.30', '146', '80'), ('160', 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', '1997-12-15', 'SA_REP', '7500.00', '0.30', '146', '80'), ('161', 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', '1998-11-03', 'SA_REP', '7000.00', '0.25', '146', '80'), ('162', 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', '1997-11-11', 'SA_REP', '10500.00', '0.25', '147', '80'), ('163', 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', '1999-03-19', 'SA_REP', '9500.00', '0.15', '147', '80'), ('164', 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', '2000-01-24', 'SA_REP', '7200.00', '0.10', '147', '80'), ('165', 'David', 'Lee', 'DLEE', '011.44.1346.529268', '2000-02-23', 'SA_REP', '6800.00', '0.10', '147', '80'), ('166', 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', '2000-03-24', 'SA_REP', '6400.00', '0.10', '147', '80'), ('167', 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', '2000-04-21', 'SA_REP', '6200.00', '0.10', '147', '80'), ('168', 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', '1997-03-11', 'SA_REP', '11500.00', '0.25', '148', '80'), ('169', 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', '1998-03-23', 'SA_REP', '10000.00', '0.20', '148', '80'), ('170', 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', '1998-01-24', 'SA_REP', '9600.00', '0.20', '148', '80'), ('171', 'William', 'Smith', 'WSMITH', '011.44.1343.629268', '1999-02-23', 'SA_REP', '7400.00', '0.15', '148', '80'), ('172', 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', '1999-03-24', 'SA_REP', '7300.00', '0.15', '148', '80'), ('173', 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', '2000-04-21', 'SA_REP', '6100.00', '0.10', '148', '80'), ('174', 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '1996-05-11', 'SA_REP', '11000.00', '0.30', '149', '80'), ('175', 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', '1997-03-19', 'SA_REP', '8800.00', '0.25', '149', '80'), ('176', 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '1998-03-24', 'SA_REP', '8600.00', '0.20', '149', '80'), ('177', 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', '1998-04-23', 'SA_REP', '8400.00', '0.20', '149', '80'), ('178', 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '1999-05-24', 'SA_REP', '7000.00', '0.15', '149', null), ('179', 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', '2000-01-04', 'SA_REP', '6200.00', '0.10', '149', '80'), ('180', 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', '1998-01-24', 'SH_CLERK', '3200.00', null, '120', '50'), ('181', 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '1998-02-23', 'SH_CLERK', '3100.00', null, '120', '50'), ('182', 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '1999-06-21', 'SH_CLERK', '2500.00', null, '120', '50'), ('183', 'Girard', 'Geoni', 'GGEONI', '650.507.9879', '2000-02-03', 'SH_CLERK', '2800.00', null, '120', '50'), ('184', 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', '1996-01-27', 'SH_CLERK', '4200.00', null, '121', '50'), ('185', 'Alexis', 'Bull', 'ABULL', '650.509.2876', '1997-02-20', 'SH_CLERK', '4100.00', null, '121', '50'), ('186', 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '1998-06-24', 'SH_CLERK', '3400.00', null, '121', '50'), ('187', 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '1999-02-07', 'SH_CLERK', '3000.00', null, '121', '50'), ('188', 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', '1997-06-14', 'SH_CLERK', '3800.00', null, '122', '50'), ('189', 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', '1997-08-13', 'SH_CLERK', '3600.00', null, '122', '50'), ('190', 'Timothy', 'Gates', 'TGATES', '650.505.3876', '1998-07-11', 'SH_CLERK', '2900.00', null, '122', '50'), ('191', 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '1999-12-19', 'SH_CLERK', '2500.00', null, '122', '50'), ('192', 'Sarah', 'Bell', 'SBELL', '650.501.1876', '1996-02-04', 'SH_CLERK', '4000.00', null, '123', '50'), ('193', 'Britney', 'Everett', 'BEVERETT', '650.501.2876', '1997-03-03', 'SH_CLERK', '3900.00', null, '123', '50'), ('194', 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200.00', null, '123', '50'), ('195', 'Vance', 'Jones', 'VJONES', '650.501.4876', '1999-03-17', 'SH_CLERK', '2800.00', null, '123', '50'), ('196', 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '1998-04-24', 'SH_CLERK', '3100.00', null, '124', '50'), ('197', 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', '1998-05-23', 'SH_CLERK', '3000.00', null, '124', '50'), ('198', 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', '1999-06-21', 'SH_CLERK', '2600.00', null, '124', '50'), ('199', 'Douglas', 'Grant', 'DGRANT', '650.507.9844', '2000-01-13', 'SH_CLERK', '2600.00', null, '124', '50'), ('200', 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '1987-09-17', 'AD_ASST', '4400.00', null, '101', '10'), ('201', 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '1996-02-17', 'MK_MAN', '13000.00', null, '100', '20'), ('202', 'Pat', 'Fay', 'PFAY', '603.123.6666', '1997-08-17', 'MK_REP', '6000.00', null, '201', '20'), ('203', 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', '1994-06-07', 'HR_REP', '6500.00', null, '101', '40'), ('204', 'Hermann', 'Baer', 'HBAER', '515.123.8888', '1994-06-07', 'PR_REP', '10000.00', null, '101', '70'), ('205', 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '1994-06-07', 'AC_MGR', '12000.00', null, '101', '110'), ('206', 'William', 'Gietz', 'WGIETZ', '515.123.8181', '1994-06-07', 'AC_ACCOUNT', '8300.00', null, '205', '110');
COMMIT;

-- ----------------------------
--  Table structure for `job_grades`
-- ----------------------------
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades` (
  `grade_level` varchar(3) DEFAULT NULL COMMENT '等级',
  `lowest_sal` decimal(10,0) DEFAULT NULL COMMENT '该等级下限',
  `higest_sal` decimal(10,0) DEFAULT NULL COMMENT '该等级上限'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `job_grades`
-- ----------------------------
BEGIN;
INSERT INTO `job_grades` VALUES ('A', '1000', '2999'), ('B', '3000', '5999'), ('C', '6000', '9999'), ('D', '10000', '14999'), ('E', '15000', '24999'), ('F', '25000', '40000');
COMMIT;

-- ----------------------------
--  Table structure for `job_history`
-- ----------------------------
DROP TABLE IF EXISTS `job_history`;
CREATE TABLE `job_history` (
  `employee_id` int(6) NOT NULL COMMENT '员工ID',
  `start_date` date NOT NULL COMMENT '开始工作时间',
  `end_date` date NOT NULL COMMENT '工作结束时间',
  `job_id` varchar(10) NOT NULL COMMENT '工作ID',
  `department_id` int(4) DEFAULT NULL COMMENT '所属部门ID',
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `jhist_emp_id_st_date_pk` (`employee_id`,`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工工作历史表';

-- ----------------------------
--  Records of `job_history`
-- ----------------------------
BEGIN;
INSERT INTO `job_history` VALUES ('101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'), ('102', '1993-01-13', '1998-07-24', 'IT_PROG', '60'), ('114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'), ('122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'), ('176', '1998-03-24', '1998-12-31', 'SA_REP', '80'), ('200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'), ('201', '1996-02-17', '1999-12-19', 'MK_REP', '20');
COMMIT;

-- ----------------------------
--  Table structure for `jobs`
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `job_id` varchar(10) DEFAULT NULL,
  `job_title` varchar(35) DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `jobs`
-- ----------------------------
BEGIN;
INSERT INTO `jobs` VALUES ('AD_PRES', 'President', '20000', '40000'), ('AD_VP', 'Administration Vice President', '15000', '30000'), ('AD_ASST', 'Administration Assistant', '3000', '6000'), ('FI_MGR', 'Finance Manager', '8200', '16000'), ('FI_ACCOUNT', 'Accountant', '4200', '9000'), ('AC_MGR', 'Accounting Manager', '8200', '16000'), ('AC_ACCOUNT', 'Public Accountant', '4200', '9000'), ('SA_MAN', 'Sales Manager', '10000', '20000'), ('SA_REP', 'Sales Representative', '6000', '12000'), ('PU_MAN', 'Purchasing Manager', '8000', '15000'), ('PU_CLERK', 'Purchasing Clerk', '2500', '5500'), ('ST_MAN', 'Stock Manager', '5500', '8500'), ('ST_CLERK', 'Stock Clerk', '2000', '5000'), ('SH_CLERK', 'Shipping Clerk', '2500', '5500'), ('IT_PROG', 'Programmer', '4000', '10000'), ('MK_MAN', 'Marketing Manager', '9000', '15000'), ('MK_REP', 'Marketing Representative', '4000', '9000'), ('HR_REP', 'Human Resources Representative', '4000', '9000'), ('PR_REP', 'Public Relations Representative', '4500', '10500');
COMMIT;

-- ----------------------------
--  Table structure for `locations`
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `location_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '地址ID',
  `street_address` varchar(40) DEFAULT NULL COMMENT '地址信息',
  `postal_code` varchar(12) DEFAULT NULL COMMENT '邮政编码',
  `city` varchar(30) NOT NULL COMMENT '城市',
  `state_province` varchar(25) DEFAULT NULL COMMENT '省份',
  `country_id` char(2) DEFAULT NULL COMMENT '所属国家ID',
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `loc_id_pk` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `locations`
-- ----------------------------
BEGIN;
INSERT INTO `locations` VALUES ('1000', '1297 Via Cola di Rie', '00989', 'Roma', null, 'IT'), ('1100', '93091 Calle della Testa', '10934', 'Venice', null, 'IT'), ('1200', '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'), ('1300', '9450 Kamiya-cho', '6823', 'Hiroshima', null, 'JP'), ('1400', '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'), ('1500', '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'), ('1600', '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'), ('1700', '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'), ('1800', '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'), ('1900', '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'), ('2000', '40-5-12 Laogianggen', '190518', 'Beijing', null, 'CN'), ('2100', '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'), ('2200', '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'), ('2300', '198 Clementi North', '540198', 'Singapore', null, 'SG'), ('2400', '8204 Arthur St', null, 'London', null, 'UK'), ('2500', 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK'), ('2600', '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK'), ('2700', 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'), ('2800', 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'), ('2900', '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'), ('3000', 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'), ('3100', 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'), ('3200', 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
COMMIT;

-- ----------------------------
--  Table structure for `regions`
-- ----------------------------
DROP TABLE IF EXISTS `regions`;
CREATE TABLE `regions` (
  `region_id` int(11) NOT NULL COMMENT '地域ID',
  `region_name` varchar(25) DEFAULT NULL COMMENT '地域名字',
  PRIMARY KEY (`region_id`),
  UNIQUE KEY `reg_id_pk` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `regions`
-- ----------------------------
BEGIN;
INSERT INTO `regions` VALUES ('1', 'Europe'), ('2', 'Americas'), ('3', 'Asia'), ('4', 'Middle East and Africa');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询语句

SELECT  employees.department_id, employees.first_name , employees.last_name ,employees.manager_id ,employees.phone_int,  employees.hire_date  FROM employees;

SELECT * FROM departments;

SELECT first_name ,last_name , salary*12  salarys FROM employees;

SELECT  hire_date+1 FROM employees WHERE employee_id = 100;

SELECT `employees`.`commission_pct` FROM employees WHERE employee_id = 206;

SELECT  DISTINCT `employees`.`department_id` FROM employees  WHERE department_id is not NULL;

SELECT  DISTINCT `employees`.`department_id` ,`employees`.`jod_id` FROM employees;

SELECT * FROM employees WHERE hire_date  > '1989-09-21';

SELECT  `employees`.`jod_id` FROM employees WHERE first_name = 'Neena';

SELECT  `employees`.`jod_id` FROM employees WHERE BINARY first_name = 'neena';  

SELECT NOW()；

SELECT NOW() from dual;

SELECT 1;

SELECT CURRENT_DATE;

SELECT CURRENT_TIME;

SELECT first_name ,last_name , salary  FROM employees WHERE salary between 8000 and 10000;

SELECT first_name ,last_name , salary  FROM employees WHERE salary IN (10000,11000,12000,13000);

SELECT last_name  FROM employees WHERE last_name like '_o%';

SELECT * FROM departments WHERE department_name LIKE 'CHINA_%';

SELECT * FROM departments WHERE department_name LIKE 'CHINA/_%' ESCAPE '/';

SELECT first_name,manager_id FROM employees WHERE manager_id is null;

SELECT first_name from employees WHERE first_name like '%a%' or first_name like '%e%';

SELECT first_name,last_name ,salary*12 salarys, department_id FROM employees ORDER BY  department_id DESC, salarys DESC;

SELECT UPPER(first_name) ,LOWER(last_name) FROM employees ;

SELECT concat(first_name,'-',last_name) FROM employees;

SELECT INSERT('aaaaa',2,3,'bbb');

SELECT INSERT(first_name,4,4,'****') FROM employees;

SELECT CHAR_LENGTH('AKySBLANK');

SELECT CHAR_LENGTH('姚亚杰');

SELECT LPAD(first_name,20,last_name) FROM employees;

SELECT TRIM('    aaaaa   ');

SELECT CHAR_LENGTH(TRIM('     a   '));

SELECT REPLACE(first_name,'av','****') FROM employees;

SELECT SUBSTRING('BLANKAKyS',-2,3);

SELECT ABS(-2);

SELECT MOD(10,3);

SELECT CEIL(0.1);

SELECT FLOOR(23.9);

SELECT ROUND(2.4);

SELECT ROUND(2.4,2);

SELECT TRUNCATE(399.149,2);

SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(),INTERVAL 3 MONTH);

SELECT DATE_ADD('2018-01-01 00:00:00',INTERVAL '1:1' MINUTE_SECOND);

SELECT DATEDIFF('2018-01-01 00:00:00','2019-01-01 00:00:00');

SELECT  month(NOW());

SELECT  year(NOW());

SELECT  day(NOW());

SELECT  hour(NOW());

SELECT  MINUTE(NOW());

SELECT LAST_DAY(NOW());

SELECT UNIX_TIMESTAMP(NOW());

SELECT FROM_UNIXTIME(1521448019);

SELECT  CEIL(DATEDIFF(NOW(),hire_date)/30)  FROM employees;

SELECT FORMAT(salary,2) FROM employees;

select date_format(hire_date,'%Y年%m月%d日 %H:%i:%s') from employees;

select * from employees where YEAR(hire_date)='1999';

select * from employees where hire_date between str_to_date('1995年3月','%Y年%m') and str_to_date(now(),'%Y年%m');

SELECT STR_TO_DATE(hire_date+1,'%Y%m%d') FROM employees WHERE employee_id = 100;

SELECT UUID();

SELECT COALESCE(null,null,'a','b');

SELECT first_name ,last_name ,salary*12*(1+ IFNULL(commission_pct,0)) from employees;

select ifnull(department_id,"未分配部门") from employees;

select first_name,last_name,salary,
case when salary < 5000 then 'D'
     when salary < 10000 and salary >=5000 then 'C'
     when salary < 15000 and salary >=10000 then 'B'
else 'A'
END
from employees;

select department_id,avg(salary) from employees where department_id is not null group by department_id;

select department_id,avg(salary) from employees where department_id is not null group by department_id HAVING AVG(salary)>8000;

微信扫码关注java技术栈，每日更新面试题目和答案，并获取Java面试题和架构师相关题目和视频。

二、MySQL查询

1、SQL的分类

2、MySQL的查询函数

3、练习使用的数据库文件和查询语句

猜你喜欢

热点阅读