<MySQL>入門一

来源:https://www.cnblogs.com/mapleins/archive/2018/12/07/10082181.html
-Advertisement-
Play Games

1. 資料庫表 1.1 員工表 1.2 部門表 1.3 工作表 1.4 位置表 2. 基礎查詢（select） 2.1 查詢單個欄位 2.2 查詢多個欄位 2.3 查詢所有欄位 2.4 查詢常量值、表達式 2.5 查詢函數 2.6 起別名（如果查詢的欄位有重名，可以使用起別名區分，別名中有特殊符號， ...


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;

 小練習

-- 查詢員工姓名，部門編號，年薪，按年薪降序，姓名升序
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;

 

5.常見函數

將一組邏輯封裝在方法中，對外暴露方法名

　　1.隱藏了實現細節

　　2.提高代碼的重用性

　　函數名（實參列表）

分類：

　　1.單行函數：concat 、length 、ifnull等

　　2.分組函數：做統計使用，聚合函數。

字元函數

-- 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('源氏開大殺殺殺殺殺','殺','送'); -- 源氏開大送送送送送

數學函數

-- 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

日期函數

-- 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

 其他函數

-- 查看mysql版本
SELECT VERSION();

-- 查看當前選擇的資料庫
SELECT DATABASE();

-- 查看當前用戶
SELECT USER();

 流程式控制制函數

 -- 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
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux總結之find搜索文件（times 3）
    個人原創，轉自請在文章頭部顯眼位置註明出處：https://www.cnblogs.com/sunshine5683/p/10091341.html find命令的各種搜索 一、根據文件名進行查找 命令：find [路徑，其中/表示根目錄搜索，即全盤搜索] -name [文件名] 實例： 二、根據文 ...
  • You have new mail in /var/spool/mail/root 解決煩瑣提示的方法
    今天寫定時任務時，出現奇怪的提示，有的時候每敲一下回車，也出現奇怪的提示 You have new mail in /var/spool/mail/root 阿西吧........表示很煩...究竟是為什麼呢？在網上一查，原來是 Linux 系統經常會自動發出一些郵件來提醒用戶系統中出了哪些問題（收 ...
  • LRU演算法
    記憶體管理的一種頁面置換演算法，對於在記憶體中但又不用的數據塊（記憶體塊）叫做LRU，操作系統會根據哪些數據屬於LRU而將其移出記憶體而騰出空間來載入另外的數據。 什麼是LRU演算法？ LRU是Least Recently Used的縮寫，即最近最少使用，常用於頁面置換演算法，是為虛擬頁式存儲管理服務的。 關於操 ...
  • liunx命令
    文件和目錄 cd /home 進入 '/ home' 目錄' cd .. 返回上一級目錄 cd ../.. 返回上兩級目錄 cd 進入個人的主目錄 cd ~user1 進入個人的主目錄 cd - 返回上次所在的目錄 pwd 顯示工作路徑 ls 查看目錄中的文件 ls -F 查看目錄中的文件 ls - ...
  • MySql的基操勿六
    2018/12/6 星期四 19:34:07 authot by dabaine 資料庫註釋; 這就是註釋 / ..... / 這也是註釋 創建庫; create databse [if not exists] dabaine [character set "utf8"]; 查看所有資料庫; sho ...
  • 阿裡雲優惠券領取
    阿裡雲限時紅包領取,雲產品通用紅包，可疊加官網常規優惠,最高1888 內部鏈接 https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=up4l93vp ...
  • MySQL之庫、表操作
    一、庫操作 二、表操作 1，存儲引擎 存儲引擎就是表的類型，MySQL中根據不同的存儲引擎會有不同的處理機制，存儲引擎的概念是MySQL裡面才有的。 1.1，MySQL的一個整個工作流程 1.2，存儲引擎的分類 在cmd中輸入show engines可以查看所有的引擎，輸入show variable ...
  • mysql設置大小寫敏感
    問題:新安裝的資料庫,訪問的時候後臺報錯找不表,發現是數據查詢語句對大小寫敏感. 1.關閉MySQL服務 2.在服務運行目錄找到my.ini或者my.cnf文件 打開文件， 3.找到[mysqld]在下麵增加一行 4.lower_case_table_names=1 （0:大小寫敏感;1:大小寫不敏 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類