入门一

1. 数据库表

1.1 员工表

 1 create table
 2 
 3 create table `employees` (
 4   `employee_id` int(6) not null auto_increment,
 5   `first_name` varchar(20) default null,
 6   `last_name` varchar(25) default null,
 7   `email` varchar(25) default null,
 8   `phone_number` varchar(20) default null,
 9   `job_id` varchar(10) default null,
10   `salary` double(10,2) default null,
11   `commission_pct` double(4,2) default null,
12   `manager_id` int(6) default null,
13   `department_id` int(4) default null,
14   `hiredate` datetime default null,
15   primary key (`employee_id`),
16   key `dept_id_fk` (`department_id`),
17   key `job_id_fk` (`job_id`),
18   constraint `dept_id_fk` foreign key (`department_id`) references `departments` (`department_id`),
19   constraint `job_id_fk` foreign key (`job_id`) references `jobs` (`job_id`)
20 ) engine=innodb auto_increment=207 default charset=gb2312

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'steven','k_ing','sking','515.123.4567','ad_pres',24000.00,null,null,90,'1992-04-03 00:00:00'),(101,'neena','kochhar','nkochhar','515.123.4568','ad_vp',17000.00,null,100,90,'1992-04-03 00:00:00'),(102,'lex','de haan','ldehaan','515.123.4569','ad_vp',17000.00,null,100,90,'1992-04-03 00:00:00'),(103,'alexander','hunold','ahunold','590.423.4567','it_prog',9000.00,null,102,60,'1992-04-03 00:00:00'),(104,'bruce','ernst','bernst','590.423.4568','it_prog',6000.00,null,103,60,'1992-04-03 00:00:00'),(105,'david','austin','daustin','590.423.4569','it_prog',4800.00,null,103,60,'1998-03-03 00:00:00'),(106,'valli','pataballa','vpatabal','590.423.4560','it_prog',4800.00,null,103,60,'1998-03-03 00:00:00'),(107,'diana','lorentz','dlorentz','590.423.5567','it_prog',4200.00,null,103,60,'1998-03-03 00:00:00'),(108,'nancy','greenberg','ngreenbe','515.124.4569','fi_mgr',12000.00,null,101,100,'1998-03-03 00:00:00'),(109,'daniel','faviet','dfaviet','515.124.4169','fi_account',9000.00,null,108,100,'1998-03-03 00:00:00'),(110,'john','chen','jchen','515.124.4269','fi_account',8200.00,null,108,100,'2000-09-09 00:00:00'),(111,'ismael','sciarra','isciarra','515.124.4369','fi_account',7700.00,null,108,100,'2000-09-09 00:00:00'),(112,'jose manuel','urman','jmurman','515.124.4469','fi_account',7800.00,null,108,100,'2000-09-09 00:00:00'),(113,'luis','popp','lpopp','515.124.4567','fi_account',6900.00,null,108,100,'2000-09-09 00:00:00'),(114,'den','raphaely','drapheal','515.127.4561','pu_man',11000.00,null,100,30,'2000-09-09 00:00:00'),(115,'alexander','khoo','akhoo','515.127.4562','pu_clerk',3100.00,null,114,30,'2000-09-09 00:00:00'),(116,'shelli','baida','sbaida','515.127.4563','pu_clerk',2900.00,null,114,30,'2000-09-09 00:00:00'),(117,'sigal','tobias','stobias','515.127.4564','pu_clerk',2800.00,null,114,30,'2000-09-09 00:00:00'),(118,'guy','himuro','ghimuro','515.127.4565','pu_clerk',2600.00,null,114,30,'2000-09-09 00:00:00'),(119,'karen','colmenares','kcolmena','515.127.4566','pu_clerk',2500.00,null,114,30,'2000-09-09 00:00:00'),(120,'matthew','weiss','mweiss','650.123.1234','st_man',8000.00,null,100,50,'2004-02-06 00:00:00'),(121,'adam','fripp','afripp','650.123.2234','st_man',8200.00,null,100,50,'2004-02-06 00:00:00'),(122,'payam','kaufling','pkauflin','650.123.3234','st_man',7900.00,null,100,50,'2004-02-06 00:00:00'),(123,'shanta','vollman','svollman','650.123.4234','st_man',6500.00,null,100,50,'2004-02-06 00:00:00'),(124,'kevin','mourgos','kmourgos','650.123.5234','st_man',5800.00,null,100,50,'2004-02-06 00:00:00'),(125,'julia','nayer','jnayer','650.124.1214','st_clerk',3200.00,null,120,50,'2004-02-06 00:00:00'),(126,'irene','mikkilineni','imikkili','650.124.1224','st_clerk',2700.00,null,120,50,'2004-02-06 00:00:00'),(127,'james','landry','jlandry','650.124.1334','st_clerk',2400.00,null,120,50,'2004-02-06 00:00:00'),(128,'steven','markle','smarkle','650.124.1434','st_clerk',2200.00,null,120,50,'2004-02-06 00:00:00'),(129,'laura','bissot','lbissot','650.124.5234','st_clerk',3300.00,null,121,50,'2004-02-06 00:00:00'),(130,'mozhe','atkinson','matkinso','650.124.6234','st_clerk',2800.00,null,121,50,'2004-02-06 00:00:00'),(131,'james','marlow','jamrlow','650.124.7234','st_clerk',2500.00,null,121,50,'2004-02-06 00:00:00'),(132,'tj','olson','tjolson','650.124.8234','st_clerk',2100.00,null,121,50,'2004-02-06 00:00:00'),(133,'jason','mallin','jmallin','650.127.1934','st_clerk',3300.00,null,122,50,'2004-02-06 00:00:00'),(134,'michael','rogers','mrogers','650.127.1834','st_clerk',2900.00,null,122,50,'2002-12-23 00:00:00'),(135,'ki','gee','kgee','650.127.1734','st_clerk',2400.00,null,122,50,'2002-12-23 00:00:00'),(136,'hazel','philtanker','hphiltan','650.127.1634','st_clerk',2200.00,null,122,50,'2002-12-23 00:00:00'),(137,'renske','ladwig','rladwig','650.121.1234','st_clerk',3600.00,null,123,50,'2002-12-23 00:00:00'),(138,'stephen','stiles','sstiles','650.121.2034','st_clerk',3200.00,null,123,50,'2002-12-23 00:00:00'),(139,'john','seo','jseo','650.121.2019','st_clerk',2700.00,null,123,50,'2002-12-23 00:00:00'),(140,'joshua','patel','jpatel','650.121.1834','st_clerk',2500.00,null,123,50,'2002-12-23 00:00:00'),(141,'trenna','rajs','trajs','650.121.8009','st_clerk',3500.00,null,124,50,'2002-12-23 00:00:00'),(142,'curtis','davies','cdavies','650.121.2994','st_clerk',3100.00,null,124,50,'2002-12-23 00:00:00'),(143,'randall','matos','rmatos','650.121.2874','st_clerk',2600.00,null,124,50,'2002-12-23 00:00:00'),(144,'peter','vargas','pvargas','650.121.2004','st_clerk',2500.00,null,124,50,'2002-12-23 00:00:00'),(145,'john','russell','jrussel','011.44.1344.429268','sa_man',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'karen','partners','kpartner','011.44.1344.467268','sa_man',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'alberto','errazuriz','aerrazur','011.44.1344.429278','sa_man',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'gerald','cambrault','gcambrau','011.44.1344.619268','sa_man',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'eleni','zlotkey','ezlotkey','011.44.1344.429018','sa_man',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'peter','tucker','ptucker','011.44.1344.129268','sa_rep',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'david','bernstein','dbernste','011.44.1344.345268','sa_rep',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'peter','hall','phall','011.44.1344.478968','sa_rep',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'christopher','olsen','colsen','011.44.1344.498718','sa_rep',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'nanette','cambrault','ncambrau','011.44.1344.987668','sa_rep',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'oliver','tuvault','otuvault','011.44.1344.486508','sa_rep',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'janette','k_ing','jking','011.44.1345.429268','sa_rep',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'patrick','sully','psully','011.44.1345.929268','sa_rep',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'allan','mcewen','amcewen','011.44.1345.829268','sa_rep',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'lindsey','smith','lsmith','011.44.1345.729268','sa_rep',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'louise','doran','ldoran','011.44.1345.629268','sa_rep',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'sarath','sewall','ssewall','011.44.1345.529268','sa_rep',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'clara','vishney','cvishney','011.44.1346.129268','sa_rep',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'danielle','greene','dgreene','011.44.1346.229268','sa_rep',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'mattea','marvins','mmarvins','011.44.1346.329268','sa_rep',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'david','lee','dlee','011.44.1346.529268','sa_rep',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'sundar','ande','sande','011.44.1346.629268','sa_rep',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'amit','banda','abanda','011.44.1346.729268','sa_rep',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'lisa','ozer','lozer','011.44.1343.929268','sa_rep',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'harrison','bloom','hbloom','011.44.1343.829268','sa_rep',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'tayler','fox','tfox','011.44.1343.729268','sa_rep',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'william','smith','wsmith','011.44.1343.629268','sa_rep',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'elizabeth','bates','ebates','011.44.1343.529268','sa_rep',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'sundita','kumar','skumar','011.44.1343.329268','sa_rep',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'ellen','abel','eabel','011.44.1644.429267','sa_rep',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'alyssa','hutton','ahutton','011.44.1644.429266','sa_rep',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'jonathon','taylor','jtaylor','011.44.1644.429265','sa_rep',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'jack','livingston','jlivings','011.44.1644.429264','sa_rep',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'kimberely','grant','kgrant','011.44.1644.429263','sa_rep',7000.00,0.15,149,null,'2014-03-05 00:00:00'),(179,'charles','johnson','cjohnson','011.44.1644.429262','sa_rep',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'winston','taylor','wtaylor','650.507.9876','sh_clerk',3200.00,null,120,50,'2014-03-05 00:00:00'),(181,'jean','fleaur','jfleaur','650.507.9877','sh_clerk',3100.00,null,120,50,'2014-03-05 00:00:00'),(182,'martha','sullivan','msulliva','650.507.9878','sh_clerk',2500.00,null,120,50,'2014-03-05 00:00:00'),(183,'girard','geoni','ggeoni','650.507.9879','sh_clerk',2800.00,null,120,50,'2014-03-05 00:00:00'),(184,'nandita','sarchand','nsarchan','650.509.1876','sh_clerk',4200.00,null,121,50,'2014-03-05 00:00:00'),(185,'alexis','bull','abull','650.509.2876','sh_clerk',4100.00,null,121,50,'2014-03-05 00:00:00'),(186,'julia','dellinger','jdelling','650.509.3876','sh_clerk',3400.00,null,121,50,'2014-03-05 00:00:00'),(187,'anthony','cabrio','acabrio','650.509.4876','sh_clerk',3000.00,null,121,50,'2014-03-05 00:00:00'),(188,'kelly','chung','kchung','650.505.1876','sh_clerk',3800.00,null,122,50,'2014-03-05 00:00:00'),(189,'jennifer','dilly','jdilly','650.505.2876','sh_clerk',3600.00,null,122,50,'2014-03-05 00:00:00'),(190,'timothy','gates','tgates','650.505.3876','sh_clerk',2900.00,null,122,50,'2014-03-05 00:00:00'),(191,'randall','perkins','rperkins','650.505.4876','sh_clerk',2500.00,null,122,50,'2014-03-05 00:00:00'),(192,'sarah','bell','sbell','650.501.1876','sh_clerk',4000.00,null,123,50,'2014-03-05 00:00:00'),(193,'britney','everett','beverett','650.501.2876','sh_clerk',3900.00,null,123,50,'2014-03-05 00:00:00'),(194,'samuel','mccain','smccain','650.501.3876','sh_clerk',3200.00,null,123,50,'2014-03-05 00:00:00'),(195,'vance','jones','vjones','650.501.4876','sh_clerk',2800.00,null,123,50,'2014-03-05 00:00:00'),(196,'alana','walsh','awalsh','650.507.9811','sh_clerk',3100.00,null,124,50,'2014-03-05 00:00:00'),(197,'kevin','feeney','kfeeney','650.507.9822','sh_clerk',3000.00,null,124,50,'2014-03-05 00:00:00'),(198,'donald','oconnell','doconnel','650.507.9833','sh_clerk',2600.00,null,124,50,'2014-03-05 00:00:00'),(199,'douglas','grant','dgrant','650.507.9844','sh_clerk',2600.00,null,124,50,'2014-03-05 00:00:00'),(200,'jennifer','whalen','jwhalen','515.123.4444','ad_asst',4400.00,null,101,10,'2016-03-03 00:00:00'),(201,'michael','hartstein','mhartste','515.123.5555','mk_man',13000.00,null,100,20,'2016-03-03 00:00:00'),(202,'pat','fay','pfay','603.123.6666','mk_rep',6000.00,null,201,20,'2016-03-03 00:00:00'),(203,'susan','mavris','smavris','515.123.7777','hr_rep',6500.00,null,101,40,'2016-03-03 00:00:00'),(204,'hermann','baer','hbaer','515.123.8888','pr_rep',10000.00,null,101,70,'2016-03-03 00:00:00'),(205,'shelley','higgins','shiggins','515.123.8080','ac_mgr',12000.00,null,101,110,'2016-03-03 00:00:00'),(206,'william','gietz','wgietz','515.123.8181','ac_account',8300.00,null,205,110,'2016-03-03 00:00:00');

1.2 部门表

 1 create table
 2 
 3 create table `departments` (
 4   `department_id` int(4) not null auto_increment,
 5   `department_name` varchar(3) default null,
 6   `manager_id` int(6) default null,
 7   `location_id` int(4) default null,
 8   primary key (`department_id`),
 9   key `loc_id_fk` (`location_id`),
10   constraint `loc_id_fk` foreign key (`location_id`) references `locations` (`location_id`)
11 ) engine=innodb auto_increment=271 default charset=gb2312

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'adm',200,1700),(20,'mar',201,1800),(30,'pur',114,1700),(40,'hum',203,2400),(50,'shi',121,1500),(60,'it',103,1400),(70,'pub',204,2700),(80,'sal',145,2500),(90,'exe',100,1700),(100,'fin',108,1700),(110,'acc',205,1700),(120,'tre',null,1700),(130,'cor',null,1700),(140,'con',null,1700),(150,'sha',null,1700),(160,'ben',null,1700),(170,'man',null,1700),(180,'con',null,1700),(190,'con',null,1700),(200,'ope',null,1700),(210,'it ',null,1700),(220,'noc',null,1700),(230,'it ',null,1700),(240,'gov',null,1700),(250,'ret',null,1700),(260,'rec',null,1700),(270,'pay',null,1700);

1.3 工作表

create table `jobs` (
  `job_id` varchar(10) not null,
  `job_title` varchar(35) default null,
  `min_salary` int(6) default null,
  `max_salary` int(6) default null,
  primary key (`job_id`)
) engine=innodb default charset=gb2312

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('ac_account','public accountant',4200,9000),('ac_mgr','accounting manager',8200,16000),('ad_asst','administration assistant',3000,6000),('ad_pres','president',20000,40000),('ad_vp','administration vice president',15000,30000),('fi_account','accountant',4200,9000),('fi_mgr','finance manager',8200,16000),('hr_rep','human resources representative',4000,9000),('it_prog','programmer',4000,10000),('mk_man','marketing manager',9000,15000),('mk_rep','marketing representative',4000,9000),('pr_rep','public relations representative',4500,10500),('pu_clerk','purchasing clerk',2500,5500),('pu_man','purchasing manager',8000,15000),('sa_man','sales manager',10000,20000),('sa_rep','sales representative',6000,12000),('sh_clerk','shipping clerk',2500,5500),('st_clerk','stock clerk',2000,5000),('st_man','stock manager',5500,8500);

1.4 位置表

create table

create table `locations` (
  `location_id` int(11) not null auto_increment,
  `street_address` varchar(40) default null,
  `postal_code` varchar(12) default null,
  `city` varchar(30) default null,
  `state_province` varchar(25) default null,
  `country_id` varchar(2) default null,
  primary key (`location_id`)
) engine=innodb auto_increment=3201 default charset=gb2312

insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) 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');

2. 基础查询（select）

2.1 查询单个字段

select last_name from employees

2.2 查询多个字段

select last_name,email,salary from employees

2.3 查询所有字段

select * from employees

2.4 查询常量值、表达式

select 100;
select 'john';
select 100*8;
select 100%98;

2.5 查询函数

select version();  -- 查询数据库版本

2.6 起别名（如果查询的字段有重名，可以使用起别名区分，别名中有特殊符号，用引号引起来）

select last_name as 姓,first_name as 名 from employees;

select last_name 姓,first_name 名 from employees;

select salary as 'out put' from employees;

2.7 去重（distinct）

# 查询员工表中涉及到的所有部门编号，需要去除重复
select distinct department_id from employees;

2.8 +号的作用（concat、ifnull）

/*
java中+号
1.运算符，两个都为数值
2.连接符，只要有一个操作数为字符串

mysql中
仅仅：运算符
*/
select 100+90; -- 做加法运算
select '100'+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;

-- concat中拼接null值为null
select concat(100,null);

-- ifnull(需要转换的字段，转换后的值)
select ifnull(`commission_pct`,0) 奖金率,`commission_pct` from employees;

3. 条件查询

where 

条件运算符：>，<， = ，!=， <>， >= ，<=

逻辑运算符：and（&&） ，or（||）， not（！）

模糊查询：like，between and，in，is null

3.1 简单的条件查询

# 查询工资>12000的员工信息 ( > )
select * from employees where salary > 12000;

# 查询部门编号不等于90号的员工名和部门编号 ( !=   <> )
select last_name 名字,`department_id` 部门编号 from employees where `department_id` <> 90;

# 查询工资在10000~20000之间的员工名，工资以及奖金  ( and ) 
select last_name 名字,salary 工资,`commission_pct` 奖金 from employees where salary >10000 and salary <20000;

# 查询部门编号不在90到110之间，或者工资高于15000的员工信息 ( or > < )
select * from employees where department_id >110 or department_id < 90 or salary > 15000

 3.2 模糊查询

like

-- 通配符： % 任意多个字符（包括空字符）
--         _ 任意单个字符
-- 查询员工名字包含a的员工信息
select * from employees where last_name like '%a%';

-- 查询员工第三个字符为e,第五个字符为a
select * from employees where last_name like '__n_l%'

-- 查询第二个字符为_的员工名
select * from employees where last_name like '_\_%'  -- \ 转义
select * from employees where last_name like '_$_%' escape '$'  -- escape '任意字符' 进行转义 推荐

3.3 其他查询

between and

-- 查询员工编号在100到120之间的员工信息
-- 包含临界值
-- 两个临界值不要颠倒顺序
select * from employees where employee_id between 100 and 120;
select * from employees where employee_id >=100 and employee_id<=120;

in

-- 查询员工工种编号是it_prog、ad_vp、ad_pres之间
-- in:判断某字段的值是否属于in列表中的某一项

select * from employees where job_id in ('it_prog','ad_vp','ad_pres');

is null

-- 查询没有奖金的
-- =或<>不能判断 null 值 
-- 所以需要使用 is null ,is not null
select * from employees where commission_pct is null;

-- 查询有奖金的
select * from employees where commission_pct is not null;

安全等于

-- 查询没有奖金的
-- 安全等于：<=>
-- 可以当 is 或者 = 使用
select * from employees where commission_pct <=> null;

select * from employees where salary <=> 12000;

 4. 排序查询

order by

-- select 查询字段 from 表名 where 筛选条件 order by [asc,desc]
-- asc 升序， desc 降序 ，不写默认是asc
-- order by 子句 支持单个字段，多个字段（，隔开），表达式，函数，别名
-- order by 一般放最后面，除了 limit

-- 查询员工信息 工资由高到低
select * from employees order by salary desc;
-- 查询员工信息 工资由低到高 默认asc
select * from employees order by salary;

-- 查询部门编号>=90的员工信息，按入职时间排序
select * from employees where department_id >= 90 order by hiredate;

-- 按年薪由高到低 显示员工信息及年薪
select *,salary*12*(1+ifnull('commssion_pct',0)) 年薪 from employees order by salary*12*(1+ifnull('commssion_pct',0)) ;
-- order by 后面支持别名
select *,salary*12*(1+ifnull('commssion_pct',0)) 年薪 from employees order by 年薪 ;

-- 按照名字字母的长度 排序
-- length() 函数
select * from employees order by length(last_name);

-- 查询员工信息，先按工资升序排序，再按员工编号降序排序
select * from employees order by salary asc,  employee_id desc;

 

5.小练习

-- 查询员工姓名，部门编号，年薪，按年薪降序，姓名升序
select last_name 姓名,department_id 部门编号,salary*12*ifnull(commission_pct,0) 年薪 
from employees order by 年薪 desc,姓名 asc

-- 选择工资不在8k到17k的员工姓名和工资，按工资降序
select last_name 姓名,salary 工资 from employees where salary not between 8000 and 17000 order by 工资 desc

-- 查询邮件中包含e的员工，并且按邮箱字节降序， 再按部门号升序
select * from employees where `email` like '%e%' order by length(email) desc,department_id;

 

6.常见函数

将一组逻辑封装在方法中，对外暴露方法名

　　1.隐藏了实现细节

　　2.提高代码的重用性

　　函数名（实参列表）

分类：

　　1.单行函数：concat 、length 、ifnull等

　　2.分组函数：做统计使用，聚合函数。

6.1 字符函数

-- length 获取参数值的字节个数
select length('maple');
-- utf-8中文三个字节，gbk中文字符2个字节
select length('麦克雷'); 

-- concat 拼接字符串
select concat (last_name,'_',first_name) 姓名 from employees;

-- upper、lower
select upper('maple');
select lower('maple');
-- 将姓变大写，名变小写
select concat(upper(last_name),'_',lower(first_name)) from employees;

-- substr、substring
-- mysql 索引从1开始,包括索引位置，例如4包括'在'
select substr('麦克雷在高台读午时已到',4);  -- 在高台读午时已到
-- (str,index,len) 指定索引处，截取字符长度
select substr('麦克雷在高台读午时已到',1,3); -- 麦克雷
-- 案例：姓名中首字母字符大写，其他小写，然后用_拼接
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) from employees;

-- instr 子串在字符串中的第一次出现索引，如果找不到返回0
select instr('双眼失明丝毫不影响我追捕敌人','追捕敌人')；  -- 11

-- trim 
select length(trim('    源氏   ')) as out_put ; -- 6
select trim('a' from 'aaaaaaaaa天aaa使aaaaaaaa');  -- 天aaa使

-- lpad 用指定字符实现左填充指定长度
select lpad('全场最佳',10,'6'); -- 666666全场最佳
-- rpad 用指定字符实现左填充指定长度
select rpad('全场最佳',10,'6'); -- 全场最佳666666

-- replace 替换字符
select replace('源氏开大杀杀杀杀杀','杀','送'); -- 源氏开大送送送送送

 

6.2 数学函数

-- round 四舍五入
select round(1.65); -- 2
select round(1.4);  -- 1
select round(-1.4); -- -1
-- 保留位数
select round(1.567,2);  -- 1.57

-- ceil 向上取整 返回大于等于该参数的最小整数
select ceil(1.00001); -- 2
select ceil(-1.00001); -- -1

-- floor 向下取整
select floor(9.99);  -- 9
select floor(-0.15); -- -1

-- truncat 小数点后截断几位
select truncate(1.6999,1); -- 1.6

-- mod取余
select mod(10,3); -- 1

 

6.3 日期函数

-- now 返回当前系统日期+时间
select now(); -- 2018-12-07 17:18:57

-- curdate 返回当前系统日期，不包含时间
select curdate(); -- 2018-12-07

-- curtime 返回当前时间，不包含日期
select curtime(); -- 17:20:10

-- 可以获取指定的部分，年、月、日、时、分、秒
select year(now()) 年；        -- 2018
select year('1998-10-12') 年;  -- 1998
select year(hiredate) from employees;

select month(now()) 月;      -- 12 
select monthname(now()) 月;  -- december

-- str_to_date 将字符通过指定的格式转换成日期
select str_to_date('1992-2-3','%y-%c-%d');
select * from employees where hiredate = '1992-4-3';
-- 但是前台可能传过来的值为 3-4-1992 ,此时需要格式化字符串
select * from employees where hiredate = str_to_date('4-3-1992','%m-%d-%y');

-- date_format 将日期转换为字符
select date_format(now(),'%y年%m月%d日');
-- 查询有奖金的员工名和入职日期（xx月/xx日 xx年）
select last_name,date_format(hiredate,'%m月/%d %y') 入职日期 from employees where commission_pct is not null;

　-- datediff 日期相减
  select datediff(now(),'1992-3-6'); --9973

 

6.4 其他函数

-- 查看mysql版本
select version();

-- 查看当前选择的数据库
select database();

-- 查看当前用户
select user();

 

6.5 流程控制函数

 -- if 函数
 select if(10>5,'大','小');
 
 -- case 函数 使用1
 /*
    case 需要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1;
    when 常量2 then 要显示的值1或语句2;
    ...
    else 要显示的值n或语句n;
    end
 */

 /*
    部门号=30，显示工资为1.1倍
    部门号=40，显示工资为1.2倍
    部门号=50，显示工资为1.3倍
    其他部门，显示为原工资
 */
 select salary 原始工资,department_id,
 case department_id
 when 30 then salary*1.1
 when 40 then salary*1.2
 when 50 then salary*1.3
 else salary
 end 新工资
 from employees; 
 
 -- case 函数 使用2
/*
    case
    when 条件1 then 要显示的值1或 语句1；
    ...
    else 要显示的值n 或语句 n
    end
    区别在case 后面是否加语句
*/
/*
    查询员工的工资情况
    如果工资>20000,显示a级别
    如果工资>15000,显示b级别
    如果工资>10000,显示c级别
    否则,显示d级别
*/ 
 
 select last_name , salary,
 case 
 when salary>20000 then 'a'
 when salary>15000 then 'b'
 when salary>10000 then 'c'
 else 'd'
 end 工资级别
 from employees;
 

 6.6 分组函数

sum  avg  max  min  count

 

-- 简单使用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;

select sum(salary) 和, round(avg(salary),2) 平均,min(salary) 最小, max(salary) 最大 ,count(salary) 总数 from employees;

-- 支持哪些参数类型
/*
    1.sum,avg一般处理数值型
      max,min,count可以处理任何类型
    2.以上分组函数都忽略null值
    3.可以和distinct搭配 去重
    4.一般用count(*)做统计行数
    5.和分组函数一同查询的字段要求是group by后的字段
*/
-- 去除了重复的工资再求和
select sum(distinct salary) ,sum(salary) from employees;
-- 计算有多少种工资
select count(distinct salary),count(salary) from employees;

-- count详细使用
-- 计算总行数
select count(*) from employees;
-- 相当于在表加了一个全是1的字段，然后计算总数。
-- count()里可以随便写。
-- 在myisam引擎下，count(*)效率最高，默认带有计数器
-- 在innodb下，count(*)和count(1)差不多，比count(字段）高一些，因为要判断非空
select count(1) from employees;

 

6.7 分组查询（group by）

-- 分组函数 
/*
    select 分组函数，列（出现在gourp by后面）
    from 表
    where 筛选条件
    group by 分组的列表
    order by 子句
    
    查询的列表特殊，必须是分组函数和group by后的字段
    
    分组前筛选：from 哪个表                           group by 前面    where
    分组后筛选：having,对分组后的结果集进行筛选       group by 后面    having
    
    分组函数做条件肯定放在having中
    能用分组前筛选，就优先考虑分组前筛选
    
    group by 支持单个字段分组，也支持多个字段分组（多个字段用逗号隔开，没有顺序要求）
         也支持表达式，函数（用的较少）
             也可以添加排序
*/

-- 查询每个工种的最高工资
select max(salary),job_id from employees group by job_id;

-- 查询每个位置有多少部门
select count(*),location_id from departments group by location_id;

-- 查询邮箱中包含a字符，每个部门的平均工资
select avg(salary),department_id from employees where email like '%a%' group by department_id;

-- 查询有奖金的每个领导下的员工最高工资
select max(salary) 最高工资,manager_id 领导 from employees where commission_pct is not null group by manager_id;

-- 查询哪个部门的员工个数大于2 
-- 使用having,对分组后的数据再进行筛选
select count(*),department_id from employees group by department_id having count(*) >2 ;

-- 查询每个工种有奖金的员工，最高工资>12000的工种编号和最高工资
select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>12000;

-- 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
select min(salary),manager_id from employees where manager_id > 102 group by manager_id having min(salary) > 5000;

-- 按表达式或函数筛选
-- group by ,having 支持别名
-- 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
select count(*) 员工个数,length(last_name) 名字长度 from employees group by 名字长度 having 员工个数>5;

-- 查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id from employees group by department_id,job_id;

-- 查询每个部门每个工种的员工的平均工资 并且按平均工资排序
select avg(salary),department_id,job_id from employees group by department_id,job_id order by salary;

7. 多表查询

create table `beauty` (
  `id` int(11) not null auto_increment,
  `name` varchar(50) not null,
  `sex` char(1) default '女',
  `borndate` datetime default '1987-01-01 00:00:00',
  `phone` varchar(11) not null,
  `photo` blob,
  `boyfriend_id` int(11) default null,
  primary key (`id`)
) engine=innodb auto_increment=13 default charset=utf8;

/*data for the table `beauty` */

insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',null,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',null,9),(3,'angelababy','女','1989-02-03 00:00:00','18209876567',null,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',null,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',null,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',null,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',null,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',null,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',null,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',null,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',null,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',null,1);

create table `boys` (
  `id` int(11) not null auto_increment,
  `boyname` varchar(20) default null,
  `usercp` int(11) default null,
  primary key (`id`)
) engine=innodb auto_increment=5 default charset=utf8;

/*data for the table `boys` */

insert  into `boys`(`id`,`boyname`,`usercp`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

连接分类：

-- 查出每个女神的男朋友
select name,boyname from boys,beauty;

/*
    出现笛卡尔积，48号
    即：select count(*) from boys   -> 4
        select count(*) from beauty -> 12
    表1 m行，表2 n行，笛卡尔积：m*n行
    
    发生原因：没有有效的连接条件
    如何避免：添加有效的连接条件
    
    分类：
    按年代分类：
        sql92标准：仅仅支持内连接，外连接支持一部分，但是支持的不好
        sql99标准（推荐）：支持下面三张连接，除了全外连接
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        交叉连接：
*/

select beauty.name,boys.boyname from boys,beauty where boys.id = beauty.boyfriend_id;

7.1 sql92语法

7.1 .1 等值连接

-- 等值连接
/*

1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面的所有子句，比如排序，分组，筛选

*/

-- 1.查询员工名和对应的部门名
select last_name,department_name 
from employees e,departments d 
where e.`department_id` = d.`department_id`;

-- 2.查询员工号，工种号，工种名
select e.`employee_id`,e.`job_id`,j.`job_title` 
from employees e,jobs j 
where e.`job_id` = j.`job_id`;

-- 3.查询有奖金的员工名，部门名
select e.`last_name`,d.`department_name`,e.`commission_pct`
from employees e,departments d
where e.`department_id` = d.`department_id`
and e.`commission_pct` is not null;

-- 4.查询城市名中第二个字符为o的部门名和城市名
select d.`department_name`,l.`city`
from departments d,locations l
where l.`city` like '_o%'
and d.`location_id`=l.`location_id`

-- 5.查询每个城市的部门个数
select count(*) 部门个数,l.`city`
from departments d,locations l
where d.`location_id` = l.`location_id`
group by l.`city`;

-- 6.查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
select d.`department_name`,d.`manager_id`,min(e.salary)
from employees e,departments d
where e.`commission_pct` is not null
and e.`department_id` = d.`department_id`
group by d.`department_id`,e.`department_id`;

-- 7.查询每个工种的工种名和员工个数，并且按员工个数降序
select j.`job_title`,count(*) 员工个数
from employees e,jobs j
where e.`job_id`=j.`job_id` 
group by e.`job_id`
order by 员工个数;

-- 三表连接
-- 查询员工名，部门名和所在的城市
select e.`last_name`,d.`department_name`,l.`city`
from employees e,departments d,locations l
where e.`department_id` = d.`department_id` and d.`location_id` = l.`location_id`;

7.1.2 非等值连接

-- 工资级别表
create table job_grades
(grade_level varchar(3),
 lowest_sal  int,
 highest_sal int);

insert into job_grades
values ('a', 1000, 2999);

insert into job_grades
values ('b', 3000, 5999);

insert into job_grades
values('c', 6000, 9999);

insert into job_grades
values('d', 10000, 14999);

insert into job_grades
values('e', 15000, 24999);

insert into job_grades
values('f', 25000, 40000);

-- 非等值连接

-- 查询员工的工资和工资级别
select e.`salary`,jg.`grade_level`
from employees e,job_grades jg
where e.`salary` between jg.`lowest_sal` and jg.`highest_sal`;

7.1.3 自连接

-- 自连接
-- 把一张表当成两张或多张表使用，即自连接

-- 查询员工名和上级的名字
select e1.`last_name` 员工名,e2.`last_name` 上级,e1.`employee_id`
from employees e1,employees e2
where e1.`manager_id`=e2.`employee_id`;

7.2 sql99语法

-- sql99语法
/*
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on 连接条件
    where 筛选条件
    group by 分组
    having 筛选条件
    order by 排序

分类：
    内连接：inner
    外连接：
        左外：left [outer] 
        右外：right [outer]
        全外：full [outer]
    交叉连接： cross
*/

7.2.1 内连接

1.等值连接

-- 内连接 inner join on
/*
    1.添加排序、分组、筛选
    2.inner 可以省略
    3.筛选条件放在where后面，连接条件放在onq前面，提高了分离性
    4.inner join 和sql92的等值连接效果是一样的，都是查询多表的交集
*/

-- 等值连接

-- 1.查询员工名部门名
select e.`last_name`,d.`department_name`
from employees e
inner join departments d
on e.`department_id` = d.`department_id`;

-- 2.查询名字中包含e的员工名和工种名
select e.`last_name`,j.`job_title`
from employees e
inner join jobs j
on e.`job_id` = j.`job_id`
where e.`last_name` like '%e%';

-- 3.查询每个城市 部门个数>3的城市名和部门个数
select count(*),l.`city`
from departments d
inner join locations l
on d.`location_id`=l.`location_id`
group by l.`city`
having count(*)>3;

-- 4.查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序
select d.`department_name`,count(*)
from employees e
inner join departments d
on e.`department_id` = d.`department_id`
group by d.`department_name`
having count(*)>3
order by count(*) desc;

-- 5.查询员工名、部门名、工种名、并按部门名降序 三表连接
select e.`last_name`,d.`department_name`,j.`job_title`
from employees e
inner join departments d on e.`department_id` = d.`department_id`
inner join jobs j on e.`job_id` = j.`job_id`
order by d.`department_name` desc;

2.非等值连接

-- 非等值连接

-- 查询员工的工资级别
select e.`salary`,jg.`grade_level`
from employees e
inner join job_grades jg
on e.`salary` between jg.`lowest_sal` and jg.`highest_sal`;

-- 查询每个工资级别的个数>20的个数，并且排序级别降序
select count(*),jg.`grade_level`
from employees e
inner join job_grades jg
on e.`salary` between jg.`lowest_sal` and jg.`highest_sal`
group by jg.`grade_level`
having count(*)>20
order by jg.`grade_level` desc;

3.自连接

-- 自连接

-- 查询员工的名字和上级的名字
select e.`last_name` 员工名字,m.`last_name` 上级名字
from employees e
inner join employees m
on e.`manager_id` = m.`employee_id`;

-- 查询员工名字包含k的员工名字和上级的名字
select e.`last_name` 员工名字,m.`last_name` 上级名字
from employees e
inner join employees m
on e.`manager_id` = m.`employee_id`
where e.`last_name` like '%k%';

7.2.2  外连接

-- 左外右外

/*
  应用场景：查询一个表里有，另一个表没有的
  
    特点：
         1.外连接的查询结果为主表中的所有记录
              如果从表中有和它匹配的，则显示匹配的值
              如果从表中没有和它匹配的，则显示null
              外连接查询结果=内连接结果+主表中有而从表没有的记录
              
         2.左外连接：left左边的是主表
           右外连接：right右边的是主表
           
         3.左外和右外交换两个表的顺序，可以实现同样的效果
         4.全外连接=内连接的结果+表1有表2没有的+表2有表1没有的
*/

-- 查询没有男朋友的女神名
select be.name
from beauty be
left outer join boys bo
on bo.id = be.boyfriend_id
where bo.id is null;

-- 1.查询哪个部门没有员工
select d.`department_name`
from departments d
left outer join employees e
on d.`department_id` = e.`department_id`
where e.`employee_id` is null;

7.2.3 交叉连接

-- 交叉连接
-- 形成笛卡尔积

select be.*,bo.*
from beauty be
cross join boys bo;

7.3 练习

-- 练习

-- 1.查询编号>3的女神的男朋友信息，如果有则列出详细信息，如果没有用null填充
select be.id,be.name,bo.*
from beauty be
left join boys bo
on be.boyfriend_id = bo.id
where be.id >3;

-- 2.查询哪个城市没有部门
select l.`city`
from locations l
left join departments d
on d.`location_id` = l.`location_id`
where d.`department_id` is null;

-- 3.查询部门名称为sal或it的员工信息
select d.`department_name`,e.`last_name`
from departments d
left join employees e
on d.`department_id` = e.`department_id`
where d.`department_name` in ('sal','it');

 8.子查询

/*
    子查询：
    出现在其他语句的select语句，称为子查询或内查询
    主查询：
    外部的查询语句，称为主查询或外查询

    分类：
    按照查询出现的位置：
        select后
            仅仅支持标量子查询
        from后
            支持表子查询
        where或having后 ☆
            标量子查询 ☆
            列子查询 ☆
            行子查询
        exists后（相关子查询）
            表子查询
    按照结果集的行列数不同：
        标量子查询：结果集一行一列
        列子查询：结果集一列多行
        行子查询：结果集一行多列
        表子查询：结果集一般为多行多列
*/

 where having 后面：