⭐表示重要。

1 数据库概述

1.1 为什么要学习数据库？

实现数据持久化。
使用完整的数据库管理系统统一管理，易于查询。

1.2 数据库的相关概念

1.2.1 DB

DB（Database）：数据库，存储数据的“仓库”，它保存了一系列有组织的数据。

1.2.2 DBMS

DBMS（Database Management System）：数据库管理系统，数据库是通过DBMS创建和操作的容器。
常见的DBMS有：Oracle、MySQL等。

1.2.3 SQL

SQL（Structure Query Language）：结构化查询语言，专门用来和数据库通信的语言。
SQL的优点：
- ① 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL。
- ② 简单易学。
- ③ 虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

1.2.4 数据库的特点

① 将数据放到表中，表再放到库中。
② 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
③ 表具有一些特性，这些特性定义了数据在表中如何存储，类似于java中“类”的设计。
④ 表是由列组成，我们也称之为字段。所有表都是由一个或多个列组成，每一列类似于java中的“属性”。
⑤ 表中的数据是按行存储的，每一行数据类似于java中的“对象”。

2 MySQL简介

2.1 概述

MySQL数据库隶属于MySQL AB公司，总部位于瑞典，后被Oracle收购。

2.2 优点

① 成本低：开放源代码，一般可以免费使用。
② 性能高：执行很快。
③ 简单：很容易安装和使用。

2.3 MySQL的版本

社区版（免费）
企业版（收费）

2.4 MySQL数据库的使用

2.4.1 登录和退出

mysql -h localhost -P 3306  -u root -p123456

-- h:host 主机地址
-- P:port 端口
-- p:password 密码
-- u:user/username 用户名

退出

exit

2.4.2 MySQL数据库的常见命令

显示当前全部的数据库：

show databases;

进入指定的数据库：

use 数据库名;

-- 示例：
-- use test;

显示当前数据库下的所有数据表：

show tables;

显示当前在那个数据库下：

select database();

显示表的结构：

desc 表名;

-- 示例：
-- desc employee;

查看MySQL的服务器版本：

select version();

2.4.3 MySQL的语法规范

① 不区分大小写。
② 每句话用;结尾。
③ 各个字句一般分行写。
④ 关键字不能缩写也不能分行。
⑤ 用缩进来提高语句的可读性。
⑥ 注释：
- 单行注释：

# 单行注释
-- 单行注释

- 多行注释：

/* 多行注释 */

3 SQL语言的分类

3.1 概述

DML（Data Manipulation Language）：数据操纵语言，用于添加、删除、修改、查询数据库记录，并检查数据完整性。
DDL（Data Definition Language）：数据定义语言，用于库和表的创建、修改、删除。
DCL（Data Control Language）：数据控制语言，用于定义用户的访问权限和安全级别。

3.2 DDL

DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
① CREATE TABLE：创建数据库表。
② ALTER TABLE：更新表结构、添加、删除、修改列长度。
③ DROP TABLE：删除表。
④ CREATE INDEX：在表上建立索引。
⑤ DROP INDEX：删除索引。

3.3 DML

DML用于查询和修改数据记录，包括如下的SQL语句：
① INSERT：添加数据到数据库中。
② UPDATE：修改数据库中的数据。
③ DELETE：删除数据库中的数据。
④ SELECT：选择（查询）数据。SELECT是SQL语言的基础，最为重要。

3.4 DCL

DCL用来控制数据库的访问，包括如下SQL语句：
① GRANT：授予访问权限。
② REVOKE：撤销访问权限。
③ COMMIT：提交事务处理。
④ ROLLBACK：事务处理回退。
⑤ SAVEPOINT：设置保存点。
⑥ LOCK：对数据库的特定部分进行锁定。

4 SELECT查询（⭐）

4.1 准备工作

USE `test`;

DROP TABLE IF EXISTS `locations`;

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`)
) ;

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');

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ;

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


DROP TABLE IF EXISTS `jobs`;

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`)
) ;

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

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `job_id` varchar(10) DEFAULT NULL,
  `salary` double(10,2) DEFAULT NULL,
  `commission_pct` double(4,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ;

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');

4.2 基础查询

语法：

SELECT 查询列表
FROM 表名;

特点：
① 查询列表可以是：表中的字段、常量值、表达式、函数。
② 查询的结果是一个虚拟的表格。
示例：查询表中的单个字段

SELECT last_name
FROM employees;

示例：查询表中的多个字段

SELECT
	last_name,
	salary,
	email 
FROM
	employees;

示例：查询表中的所有字段

SELECT 
	*
FROM employees;

示例：查询常量值

SELECT 100;

SELECT 'john';

示例：查询表达式

SELECT 100*98;

示例：查询函数

SELECT version();

示例：起别名

SELECT 100 as '常量值';

SELECT
	first_name AS '姓',
	last_name AS '名' 
FROM
	employees;

示例：去重，查询员工表中的所有部门编号

SELECT DISTINCT
	department_id as '部门编号'
FROM
	employees;

示例：查询员工名和姓连接成一个字段，并显示为姓名

SELECT
	CONCAT( last_name, first_name ) AS '姓名' 
FROM
	employees;

4.3 条件查询

语法：

SELECT 查询列表
FROM 表名
WHERE 条件表达式;

分类：
① 按条件表达式筛选：条件运算符：>、<、=、<>、!=、>=、<=。
② 按逻辑表达式筛选：逻辑运算符：&&（and）、||（or）、!（not）。
③ 模糊查询：like、between and、in、is null、is not null。
示例：查询员工工资>12000的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	salary > 12000;

示例：查询部门编号不等于90号的员工名和部门编号

SELECT
	last_name,
	department_id 
FROM
	employees 
WHERE
	department_id != 90;

示例：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT
	last_name AS '员工名',
	salary AS '工资',
	commission_pct AS '奖金' 
FROM
	employees 
WHERE
	salary >= 10000 
	AND salary <= 20000;

示例：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	( department_id < 90 OR department_id > 110 ) 
	OR ( salary > 15000 );

SELECT
	* 
FROM
	employees 
WHERE
	NOT ( department_id >= 90 AND department_id <= 110 ) 
	OR ( salary > 15000 );

示例：查询员工名中包含字符a的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	last_name LIKE '%a%';

示例：查询员工名中第二个字符为_的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	last_name like '_\_%';

示例：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT
	last_name AS '员工名',
	salary AS '工资',
	commission_pct AS '奖金' 
FROM
	employees 
WHERE
	salary BETWEEN 10000 
	AND 20000;

示例：查询员工的工种编号是IT_PROG、AD_VP的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	job_id IN ( 'IT_PROG', 'AD_VP' );

示例：查询没有奖金的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	commission_pct IS NULL;

4.4 排序查询

语法：

SELECT 查询列表
FROM 表名
WHERE 条件表达式
ORDER BY 排序列表(字段 [asc],字段 [desc],……);

示例：查询员工信息，要求工资从高到低排序

SELECT
	* 
FROM
	employees 
ORDER BY
	salary DESC;

示例：查询部门编号>=90的员工信息，按入职时间的先后进行排序

SELECT
	* 
FROM
	employees 
WHERE
	department_id >= 90 
ORDER BY
	hiredate ASC;

示例：按年薪的高低显示员工的信息和年薪

SELECT
	*,
	salary * 12 * ( IFNULL( commission_pct, 0 ) + 1 ) AS '年薪' 
FROM
	employees 
ORDER BY
	年薪 ASC;

示例：查询员工信息，要求先按工资排序，再按员工编号排序

SELECT
	* 
FROM
	employees 
ORDER BY
	salary,
	employee_id;

4.5 常见函数

4.5.1 概念

函数类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

4.5.2 好处

① 隐藏了实现细节。
② 提高代码的重用性。

4.5.3 分类

① 单行函数：将一个数据进行处理，返回一个值，如length()、concat()等。
② 分组函数：将虚拟表看做一个组，处理一组数据，返回一个值。

4.5.4 单行函数之字符函数

获取参数值的字节个数：

length(str)

示例：

SELECT LENGTH( 'john' );

示例：

SELECT
	LENGTH( '张三丰hahaha' ); -- 15

拼接字符串：

concat(str1,str2,……)

示例：

SELECT
	CONCAT( last_name, '_', first_name ) AS '姓名' 
FROM
	employees;

将字符变为大写：

upper(str)

示例：

SELECT
	UPPER(last_name)
FROM
	employees;

将字符变为小写：

lower(str)

示例：

SELECT
	LOWER( last_name ) 
FROM
	employees;

截取字符：

substr(str,position,[length])

示例：

-- 截取从指定索引处后面所有字符
SELECT
	SUBSTR( '李莫愁爱上了陆展元', 7 );

示例：

-- 截取从指定索引处指定字符长度的字符
SELECT
	SUBSTR( '李莫愁爱上了陆展元', 1,3 );

用于返回子串在大串中的第一次出现的索引，如果找不到返回0：

instr(str,substr)

示例：

SELECT
	INSTR( '杨不悔爱上了殷六侠', '殷六侠' );

去除左右空格：

trim(str)

示例：

SELECT
	trim( '           杨不悔爱上了殷六侠           ' );

替换：

replace(str,from_str,to_str)

示例：

SELECT 
REPLACE ( '杨不悔爱上了殷六侠', '爱上了', '怎么可能爱上' );

用指定的字符实现左填充指定长度：

lpad(str,len,padstr)

示例：

SELECT
	LPAD( '杨不悔爱上了殷六侠', 20, '*' );

用指定的字符实现右填充指定长度：

rpad(str,len,padstr))

示例：

SELECT
	RPAD( '杨不悔爱上了殷六侠', 20, '*' );

4.5.5 单行函数之数学函数

四舍五入：

round(x,d)

示例：

SELECT
	ROUND(1.65)

SELECT
	ROUND(1.45)

SELECT
	ROUND(1.567,2)

向上取整：

ceil(x)

示例：

SELECT
	CEIL(1.11)

向下取整：

floor(x)

示例：

SELECT
	FLOOR(1.567)

截断：

truncate(x,d)

示例：

SELECT
	TRUNCATE(1.567,2)

取余。mod(a,b)=a-a/b*b：

mod(n,m)

示例：

SELECT
	MOD(3,1)

4.5.6 单行函数之日期函数

返回当前系统日期+时间：

now()

示例：

select NOW();

返回当前系统日期：

curdate()

示例：

select CURDATE()

返回当前时间：

curtime()

示例：

select CURTIME();

获取指定的部分，年、月、日、小时、分钟、秒：

YEAR(时间);

MONTH(时间);

DAY(时间);

HOUR(时间);

MINUTE(时间);

SECOND(时间);

示例：

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

将日期格式的字符转换成指定格式的日期：

str_to_date(str,format)

示例：

SELECT STR_TO_DATE('9-13-1999','%m-%d-%y');

将日期转换为字符：

date_format(date,format)

示例：

SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日');

4.5.7 单行函数之其他函数

显示当前数据库的版本：

version()

示例：

SELECT version();

显示当前在那个数据库中：

database()

示例：

SELECT DATABASE ();

显示当前登录的用户：

user()

示例：

SELECT USER();

4.5.8 单行函数之流程控制函数

类似于java中的switch-case语句：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end;

类似于java中的if-else语句：

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end;

示例：

SELECT
	*,
	CASE
		commission_pct 
	WHEN NULL THEN '没有奖金' 
	ELSE '有奖金' 
	END 
FROM
	employees;

4.5.9 分组函数

常见的分组函数是：
- avg()：求平均值。
- count()：求总数。
- max()：求最大值。
- min()：求最小值。
- sum()：求和。
特点：
- 一般而言，sum和avg用于处理数值型。max、min、count可以处理任何类型。
- avg、count、max、min、sum都忽略null值。
- count函数一般单独使用，一般使用count(*)来统计行数。
- 和分组函数一同查询的字段要求是group by后的字段。
示例：

SELECT
	avg( salary ), -- 平均值
	count(*),  -- 总数
	MAX( salary ), -- 最大值
	MIN( salary ),　-- 最小值
	sum( salary )  -- 求和
FROM
	employees;

4.6 分组查询

语法：

SELECT 分组函数,列[要求出现在group by后面]
FROM 表名
WHERE 条件表达式
group by 分组表达式
having 分组条件表达式
ORDER BY 排序列表(字段 [asc],字段 [desc],……);

分组查询的目的是为了做统计，所以一般需要和分组函数一起配合使用。
示例：查询每个部门的平均工资

SELECT
	avg( salary ) ,department_id
FROM
	employees 
GROUP BY
	department_id;

示例：查询每个工种的最高工资

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

SELECT
	department_id,
	count(*) AS `count` 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	`count` > 2

示例：查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资

SELECT
	job_id,
	max( salary ) AS `max` 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	`max` > 12000;

示例：按员工姓名的长度分组，查询每一组的员工个数，筛选出员工个数>5的

SELECT
	count(*) ,LENGTH( last_name ) 
FROM
	employees 
GROUP BY
	LENGTH( last_name ) 
HAVING
	count(*) > 5;

示例：查询每个部门每个工种的员工的平均工资

SELECT
	AVG( salary ),
	department_id,
	job_id 
FROM
	employees 
GROUP BY
	department_id,
	job_id;

4.7 连接查询

4.7.1 概念

连接查询，又称为多表查询，当查询的子弹来自于多个表时，就会用到连接查询。

4.7.2 准备工作

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');

DROP TABLE IF EXISTS `beauty`;

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;

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

DROP TABLE IF EXISTS `boys`;

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;

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

4.7.3 笛卡尔积

笛卡尔积产生的条件：
- ①省略连接条件。
- ②连接条件无效。
- ③所有表中的所有行互相连接。
为了避免笛卡尔积，可以在WHERE加入有效的连接条件。
示例：

SELECT
	`name`,
	boyName 
FROM
	beauty,
	boys;

4.7.4 连接查询的分类

按年代分类：
- SQL92标准。
- SQL99标准（推荐）。
按功能分类：
- 内连接：
- - 等值连接。
- - 非等值连接。
- - 自连接。
- 外连接。
- - 左外连接。
- - 左右连接。
- - 全外连接（MySQL不支持）。
- 交叉连接。

4.7.5 SQL92标准之等值连接

示例：查询女神名和其对应的男神名

SELECT
	`name`,
	boyName 
FROM
	beauty,
	boys 
WHERE
	beauty.boyfriend_id = boys.id;

示例：查询员工名和其对应的部门名

SELECT
	last_name,
	department_name 
FROM
	employees,
	departments 
WHERE
	employees.department_id = departments.department_id;

示例：查询有奖金的员工名、部门名

SELECT
	e.last_name,
	d.department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
	AND e.commission_pct IS NOT NULL;

4.7.6 SQL92标准之非等值连接

工资等级的sql：

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;

4.7.7 SQL92标准之自连接

示例：

SELECT
	e.last_name as last_name ,
	m.last_name as  manager_name
FROM
	employees as e,
	employees as m 
WHERE
	e.manager_id = m.employee_id;

4.7.8 SQL99标准之语法

语法：

SELECT 查询列表
FROM 表1 别名 [连接类型 inner|left|right] join 表2 别名 
ON 连接条件
WHERE 筛选条件
group by 分组字段
having 分组筛选条件
order by 排序列表;

说明：
- 内连接：inner join
- 外连接：
- - 左外连接：left [outer] join
- - 右外连接：right [outer] join
- - 全外连接：full [outer] join
- 交叉连接：cross join

4.7.9 SQL99标准之内连接

示例：查询女神名和其对应的男神名

SELECT
	`name`,
	boyName 
FROM
	beauty
	INNER JOIN boys 
	ON beauty.boyfriend_id = boys.id;

示例：查询员工的工资等级

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;

示例：查询员工的名称和其上级的名称

SELECT
	e.last_name AS last_name,
	m.last_name AS manager_name 
FROM
	employees AS e
	INNER JOIN employees AS m ON e.manager_id = m.employee_id;

4.7.10 SQL99标准之外连接

示例：查询没有男朋友的女神名

SELECT
	beauty.`name`,
	boys.boyName 
FROM
	beauty
	LEFT JOIN boys ON beauty.boyfriend_id = boys.id 
WHERE
	boys.boyName IS NULL;

4.7.11 SQL99标准之交叉连接(笛卡尔积)

示例：

SELECT
	beauty.`name`,
	boys.boyName 
FROM
	beauty
	CROSS JOIN boys;

4.8 子查询

4.8.1 概述

出现在其他语句内部的select语句，称为子查询或内查询。而内部嵌套其他select语句的查询，称为主查询或外查询。

按照子查询出现的位置：
- select后面：仅仅支持标量子查询。
- from后面：支持表子查询。
- where或having后面：支持标量子查询或列子查询，行子查询。
- exists后面（又称为相关子查询）：支持表子查询。
按结果集的行列数不同：
- 标量子查询（结果集只有一行一列）。
- 列子查询（结果集只有一列多行）。
- 行子查询（结果集有一行多列）。
- 表子查询（结果集，一般为多行多列）。

4.8.2 where或having后面

4.8.2.1 特点

① 子查询放在小括号内。
② 子查询一般放在条件的右侧。
③ 标量子查询，一般搭配单行操作符使用（>、<、>=、<=、<>）。
④ 列子查询，一般搭配多行操作符使用（in、any/some、all）。

4.8.2.2 标量子查询

示例：查询谁的工资比Abel高

SELECT
	last_name 
FROM
	employees 
WHERE
	salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );

示例：返回job_id和141号员工相同，salary比143号员工多的员工姓名、job_id和工资

SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) 
	AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 );

示例：返回公司工资最少的员工的last_name、job_id和salary

SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT min( salary ) FROM employees );

示例：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT
	department_id,
	MIN( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	min( salary ) > ( SELECT min( salary ) FROM employees WHERE department_id = 50 );

4.8.2.3 列子查询

示例：返回location_id是1400或1700的部门中的所有员工姓名

SELECT
	last_name 
FROM
	employees 
WHERE
	department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );

示例：返回其它工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id以及salary

SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) and job_id !=  'IT_PROG';

示例：返回其它工种中比job_id为'IT_PROG'工种所有工资低的员工的员工号、姓名、job_id以及salary

SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) and job_id !=  'IT_PROG';

4.8.2.4 行子查询（用的较少）

示例：查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees 
WHERE ( salary, employee_id ) = (( SELECT max( salary ) FROM employees ),( SELECT min( employee_id ) FROM employees ) 
);

4.8.3 select后面

示例：查询每个部门的员工个数

SELECT d.*,( SELECT count(*) FROM employees e WHERE e.department_id = d.department_id ) as '员工个数'
FROM
	departments d;

4.8.4 from后面

示例：查询每个部门的平均工资的工资等级

SELECT
	temp.department_id,
	jg.grade_level 
FROM
	( SELECT department_id AS department_id, avg( salary ) AS `avg` FROM employees GROUP BY department_id ) temp
	INNER JOIN ( SELECT grade_level, highest_sal, lowest_sal FROM job_grades ) jg ON temp.avg BETWEEN jg.lowest_sal 
	AND jg.highest_sal;

4.8.5 exists后面

语法：

exists(完成的查询语句)

结果：0或1

示例：查询有员工的部门名

SELECT
	department_name 
FROM
	departments d 
WHERE
	EXISTS ( SELECT * FROM employees e WHERE d.department_id = e.department_id );

4.9 分页查询

语法：

SELECT 查询列表
FROM 表 [join type] JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组筛选条件
ORDER BY 排序字段
LIMIT 起始索引(从0开始)，每页显示条数。

示例：查询前5条员工信息

SELECT
	* 
FROM
	employees 
	LIMIT 0,5;

4.10 union联合查询

union联合查询：将多条查询语句的结果合并成一个结果。
语法：

查询语句1
UNION
查询语句2
……;

特点：
① 要求多条查询语句的查询列数是一致的。
② 要求多条查询语句的每一列的类型和顺序最好是一致的。
③ union关键字默认是去重，如果使用union all，可以包含重复项。
示例：查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE last_name LIKE '%a%'  
UNION
SELECT * FROM employees WHERE department_id > 90 ;

5 DML（⭐）

5.1 概述

DML（Data Manipulation Language，数据操纵语言），可以在下列条件下执行：
- 向表中插入数据。
- 修改现存数据。
- 删除现存数据。
事务是由完成若干项工作的DML语句组成的，要么全部成功，要么全部失败。

5.2 INSERT

语法：

INSERT INTO 表名(列名1,列名2,……,列名n) values(值1,值2,……,值n);

INSERT INTO 表名(列名1,列名2,……,列名n) values(值1,值2,……,值n),(值1,值2,……,值n),……;

示例：新增雇员信息

insert into employees (first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredate)
values ('xx','xxx','xxx@qq.com','18888888',null,3000,null,null,null,'2019-11-11')

5.3 UPDATE

语法：

UPDATE 表名
SET 列1=值1,列2=值2,……,列n=值n
[WHERE 条件];

示例：修改表中姓名为K_ing的手机号码为12345678901

UPDATE employees 
SET phone_number = '12345678901' 
WHERE
	last_name = 'K_ing';

5.4 DELETE

语法：

DELETE FROM 表名
[WHERE 筛选条件];

示例：删除last_name为xxx的员工

DELETE 
FROM
	employees 
WHERE
	last_name = 'xxx';

6 DDL（⭐）

6.1 概述

库的管理（创建、修改、删除）和表的管理（创建、修改、删除）。

6.2 库的管理

6.2.1 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] 数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

示例：创建user数据库

CREATE DATABASE IF NOT EXISTS `user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

6.2.2 修改数据库

修改数据库名：先删除数据库，再创建新的数据库。
更改数据库的字符集和排序规则：

ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

6.2.3 删除数据库

语法：

DROP DATABASE [IF EXISTS] 数据库名;

示例：删除user数据库

DROP DATABASE IF EXISTS user;

6.3 表的管理

6.3.1 表的创建

语法：

CREATE TABLE 表名(
	列名1 列的类型[长度] [列的约束],
    列名2 列的类型[长度] [列的约束],
    ……
    列名n 列的类型[长度] [列的约束]
);

示例：创建一个学生表

CREATE TABLE student ( 
	id BIGINT primary KEY auto_increment, 
	`name` VARCHAR ( 255 ) 
);

6.3.2 表的修改

语法：

ALTER TABLE 表名 add|drop|modify|change column 列名 [列类型 约束];

示例：修改列名

ALTER TABLE student CHANGE COLUMN `name` stu_name VARCHAR ( 255 );

示例：修改列的类型或约束

ALTER TABLE student MODIFY COLUMN `stu_name` VARCHAR ( 32 );

示例：添加新列

ALTER TABLE student ADD COLUMN `salary` DOUBLE;

示例：删除列

ALTER TABLE student DROP COLUMN `salary`;

示例：修改表名

ALTER TABLE student RENAME TO `new_student`;

6.3.3 表的删除

语法：

DROP TABLE IF EXISTS 表名;

示例：

DROP TABLE IF EXISTS `student`;

6.3.4 复制表

语法：仅仅复制表的结构

CREATE TABLE 要复制的表名 LIKE 原表名;

语法：复制表的结构和数据：

CREATE TABLE 要复制的表名 SELECT * FROM 原表名;

示例：仅仅复制表的结构

CREATE TABLE admin_back LIKE admin;

示例：复制表的结构和数据

CREATE TABLE admin_back 
SELECT * FROM admin;

6.4 常见的数据类型

6.4.1 概述

数值型：整型、小数（定点数、浮点数）。
字符型：较短的文本（char、varchar）、较长的文本（text、longtext、blob）。
日期型：date、datetime、timestamp、time、year。

6.4.2 整型

特点：
① 如果不设置无符号还是有符号，默认是有符号。如果需要设置无符号，需要添加unsigned关键字。

CREATE TABLE `test_data_struct` ( 
	id INT, 
	id1 INT UNSIGNED
);

② 如果插入的数值超过了整型的范围，MySQL会报（Out of range for column ...）异常。
③ 如果不设置长度，会有默认的长度。比如int类型有符号默认是11位，无符号默认是10位。在MySQL中，对于整型来说，整型类型确定字段的范围大小，而长度确定字段显示的长度，如果不够，将用0来填充，当然此时需要配置zerofill关键字来配合使用，并且此时的数据类型必须是unsigned。

CREATE TABLE `test_data_struct` ( 
	id INT, 
	id1 INT(7) UNSIGNED ZEROFILL
);

6.4.3 小数

语法：

CREATE TABLE `表名` ( 
	列名 FLOAT ( M, D ), 
	列名 DOUBLE ( M, D ), 
	列名 DECIMAL ( M, D ) 
);

特点：
① M表示整数位+小数位。D表示小数位。
② M和D都可以省略不写。如果是DECIMAL，那么M默认是10，D默认为0。
③ 定点型的精确度较高。如货币运算。

6.4.4 字符型

特点：
① char和varchar表示较短的文本。
② text和longtext表示较长的文本。
③ blob表示二进制数据。
④ enum表示枚举。

create table test_enum (
	c enum('a','b','c') 
);

⑤ set和enum类似，但是set一次可以插入多个值。

CREATE TABLE test_set (
	s SET ( 'a', 'b', 'c' )
);

INSERT INTO test_set (s) VALUES ('a');
INSERT INTO test_set (s) VALUES ('a,b');

⑥ char表示固定长度的字符，varchar表示可变长度的字符。

6.4.6 日期类型

timestamp和datetime的区别：
① timestamp范围比较小。
② timestamp和时区有关。
③ timestamp受MySQL版本和服务器的SQLMode影响很大。
④ 表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间。

6.5 约束

6.5.1 概述

为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
约束是表级的强制规定。
可以在创建表的时候添加约束，或者在表创建之后数据添加之前添加约束。

6.5.2 约束类型

有如下的六大约束：
① NOT NULL，非空约束，规定某个字段不能为空。
② UNIQUE，唯一约束，规定某个字段在整个表中是唯一的。
③ PRIMARY KEY，主键，非空且唯一。
④ CHECK，检查约束。
⑤ DEFAULT，默认值。
⑥ FOREIGN KEY，外键。

需要注意的是，MySQL不支持check约束，但是可以使用check约束，没有任何效果。

6.5.3 分类

根据约束数据列的限制，约束可以分为：
- 单列约束：每个约束只能约束一列。
- 多列约束：每个约束可以约束多列。

CREATE TABLE 表名(
	列名 字段类型 单列约束,
    列名 字段类型 单列约束,
    多列约束(列名1,列名2)
);

根据约束的作用范围，约束可以分为：
- 列级约束：只能作用在一个列上，并且是根据列的定义后面。
- 表级约束：可以作用在多个列上，不和列在一起，而是单独定义。

CREATE TABLE 表名(
	列名 字段类型 列级约束,
    列名 字段类型 列级约束,
    表级约束(列名1,列名2)
);

列级约束，六大约束都支持，但是外键约束没有效果。

表级约束，除了非空、默认约束，其他约束都支持。

示例：创建表的时候添加列级约束

CREATE TABLE stu_info (
	id INT PRIMARY KEY,# 主键
	stu_name VARCHAR ( 255 ) NOT NULL,# 非空
	gender CHAR ( 1 ) CHECK (
	gender IN ( '男', '女' )),# 检查
	seat INT UNIQUE,# 唯一
	age INT DEFAULT 18 # 默认约束
);

示例：创建表的时候添加表级约束

CREATE TABLE major ( 
	id INT PRIMARY KEY, 
	`name` VARCHAR ( 255 ) 
);

CREATE TABLE stu_info (
	id INT ,
	stu_name VARCHAR ( 255 ) not null ,
	gender CHAR ( 1 ) ,
	seat INT ,
	age INT DEFAULT 18,
	major_id int,
	CONSTRAINT pk_id PRIMARY KEY (id),
	CONSTRAINT uk_seat UNIQUE (seat) ,
	CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);

6.5.4 主键和唯一的区别

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	最多有1个	√，但是不推荐
唯一	√	√	可以有多个	√，但是不推荐

6.5.5 外键的特点

要求在从表设置外键。
要求从表的外键列的类型要和主表的关联列的类型要求一致或兼容，但是名称不要求一致。
主表的关联列必须是一个key（一般是主键或唯一键）。

6.5.6 修改表时添加约束

语法：

# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (字段名) [外键的引用];

示例：

CREATE TABLE major ( 
	id INT PRIMARY KEY, 
	`name` VARCHAR ( 255 ) 
);
CREATE TABLE stu_info (
	id INT ,
	stu_name VARCHAR ( 255 ) NOT NULL,# 非空
	gender CHAR ( 1 ) ,
	seat INT UNIQUE,# 唯一
	age INT ,
	major_id int
);
# 修改表时添加约束
ALTER TABLE stu_info MODIFY COLUMN id INT PRIMARY KEY; 
ALTER TABLE stu_info MODIFY COLUMN age INT DEFAULT 18; 
ALTER TABLE stu_info ADD CONSTRAINT fk_major_id FOREIGN KEY (major_id) REFERENCES major(id);

6.5.7 修改表时删除约束

示例：

CREATE TABLE major ( 
	id INT PRIMARY KEY, 
	`name` VARCHAR ( 255 ) 
);
CREATE TABLE stu_info (
	id INT PRIMARY KEY,
	stu_name VARCHAR ( 255 ) NOT NULL,# 非空
	gender CHAR ( 1 ) ,
	seat INT UNIQUE,# 唯一
	age INT DEFAULT 18,
	major_id int,
	CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);
# 修改表时删除非空约束
ALTER TABLE stu_info MODIFY COLUMN stu_name  VARCHAR(255) ;
# 修改表时删除默认约束
ALTER TABLE stu_info MODIFY COLUMN age INT;
# 修改表时删除主键
ALTER TABLE stu_info DROP PRIMARY KEY;
# 修改表时删除主键
# 查询唯一键 show index from stu_info
ALTER TABLE stu_info DROP INDEX seat;
# 修改表时删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_marjor_id;

6.6 标识列

6.6.1 概述

又称为自增长列，可以不用手动插入值，系统提供默认的序列值。

6.6.2 特点

① 标识列必须不一定和主键搭配，但是要求是一个key。
② 一个表中至多有一个标识列。
③ 标识列的类型只能是数值型。
④ 标识列可以通过set auto_increment_increment = 3;设置步长，也可以通过手动插入值的方式设置标识列的起始值INSERT INTO student(id,name) VALUES (5,'xxx')。

6.6.3 应用示例

示例：创建表的时候设置标识列

# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student ( 
	id INT PRIMARY KEY auto_increment, 
	`name` VARCHAR ( 255 ) 
);
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx');

示例：修改表时设置标识列

# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student ( 
	id INT PRIMARY KEY , 
	`name` VARCHAR ( 255 ) 
);
# 修改表的时候设置标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY auto_increment;
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx');

示例：修改表时删除标识列

# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student ( 
	id INT PRIMARY KEY auto_increment, 
	`name` VARCHAR ( 255 ) 
);
# 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY;

7 事务（⭐）

7.1 概述

事务是由单独单元的一个或多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中的某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始之前的状态；如果单元中的所有SQL语句都执行成功，则事务执行成功。
总结：事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

7.2 事务的原理

事务开启之后，所有的操作都会临时保存在事务日志中，事务日志只有在得到commit命令才会同步到数据库中，其他的任何情况都会清空事务日志（rollback，断开连接）。
MySQL没有开启事务的情况（默认情况下，MySQL中的事务是默认自动提交的）：

MySQL手动开启事务的情况：

7.3 事务的使用场景

如果在某个业务中需要执行多条SQL语句（写），那么此时一般是需要使用事务，从而保证这多条SQL语句执行同时成功或失败。
例如：转账、批量删除、从购物车中提交订单等等。

7.4 存储引擎

在MySQL中的数据使用各种不同的存储引擎存储在文件或内存中。
可以通过SHOW ENGINES;来查看MySQL支持的存储引擎。

SHOW ENGINES;

在MySQL中使用最多的存储引擎是innodb和myisam等，其中innodb支持事务，而myisam不支持事务。
MySQL从5.5.5版本以上开始默认的存储引擎是innodb。

7.5 事务的特性（ACID）

原子性（Atomicity）：原子性是指事务是一个不可分割的整体，事务中的操作要么全部成功，要么全部失败。
一致性（Consistency）：事务必须使得数据库从一个一致性的状态转移到另一个一致性的状态。比如：张三有余额3000，李四有余额2000，张三向李四转账500，此时张三的余额是2500，李四的余额是2500，在这个过程中余额的总数5000是不变的。
隔离型（Isolation）：事务的隔离性是指一个事务的执行不能被其它事务所干扰，即一个事务内存的操作及使用的数据对并发中的其它事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其它操作和数据库故障不应该对其有任何影响。

7.6 事务的创建

7.6.1 隐式事务

事务没有明显的开启和结束标记。
在MySQL中，DML（INSERT、UPDATE、DELETE）等语句是自动开启事务的。
可以通过SHOW VARIABLES LIKE '%autocommit%';命令查看MySQL是否是隐式事务。

SHOW VARIABLES LIKE '%autocommit%';

7.6.2 显示事务

事务有明显的开启和结束标记。
步骤：
① 关闭隐式事务。

SET autocommit = 0;

② 开启事务。

START TRANSACTION;

③ 业务操作：一系列的SQL语句。
④ 结束事务：提交事务(COMMIT)或回滚事务(ROLLBACK)

COMMIT[ROLLBACK];

示例：张三向李四转账500
准备工作：

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `balance` double NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `account` VALUES (1, '张三', 3000);
INSERT INTO `account` VALUES (2, '李四', 2000);

转账时候开启显示事务：

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE username = '张三';
UPDATE account SET balance = balance + 500 WHERE username = '李四';
COMMIT;

7.7 隔离级别

7.7.1 数据库并发访问问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
脏读：对于两个事务T1、T2，T1读取了已经被T2更新但是还没提交的字段。之后，如果T2回滚，T1读取的内容就是临时且无效的。
不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段值，值就不同了。
幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的数据。之后，如果T1再次读取同一个表，就会发现多出几行数据。

7.7.2 数据库事务的隔离性

数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题。
一个事务和其他事务隔离的程序称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程序，隔离级别越高，数据一致性就越好，但是并发性就越低。

7.7.3 数据库提供了4种事务隔离级别

隔离级别	描述
READ UNCOMMITTED（读未提交）	会出现脏读、不可重复读、幻读
READ COMMITTED（读已提交）	解决了脏读，会出现不可重复读、幻读
REPEATABLE READ（可重复读）	解决了不可重复读、幻读，会出现幻读
SERIALIZABLE（串行化）	解决了脏读、不可重复读、幻读，但性能十分低下

注意：

Oracle默认支持2种事务隔离级别：READ COMMITTED（读已提交）和SERIALIZABLE（串行化）。Oracle默认的事务隔离级别是READ COMMITTED（读已提交）。
MySQL支持4种事务隔离级别。MySQL默认的事务隔离级别是REPEATABLE READ（可重复读）。

7.7.4 在MySQL中设置隔离级别

每启动一个MySQL客户端程序，就会获取一个单独的数据库连接。每个数据库连接都会有一个全局变量@@tx_isolation，表示当前的事务隔离级别。
查看当前事务的隔离级别：

-- 查看当前事务的隔离级别
SELECT @@tx_isolation;

设置当前MySQL连接的隔离级别：

SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

设置数据库系统的全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

8 DCL

8.1 概述

目前为止，默认使用的都是root用户，超级管理员，拥有全部权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多项目的数据库，所以，我们应该根据不同的项目建立不同的用户、分配不同的权限来管理和维护数据库。

8.2 创建用户

语法：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

说明：
用户名：将创建的用户。
主机名：指定该用户在哪个主机上可以登录，如果是本地用户可以使用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符%。
密码：该用户的登录密码，密码可以为空，如果为空则表示该用户可以不需要密码登录服务器。
示例：user1用户只能在localhost这个IP登录mysql服务器

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';

示例：user2用户可以在任何电脑上登录mysql服务器

CREATE USER 'user2'@'%' IDENTIFIED BY '123';

8.3 授权用户

用户创建之后，基本上没有什么权限，所以我们需要给用户授权。

语法：

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

说明：
GRANT：授权的关键字。
授予用户的权限：如SELECT，INSERT，UPDATE等。如果要授予所的权限则使用ALL。
数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*。
'用户名'@'主机名': 给哪个用户授权。
示例：给user1用户分配对test这个数据库操作的权限

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';

示例：给user2用户分配对所有数据库操作的权限

GRANT ALL ON *.* TO 'user2'@'%';

8.4 撤销授权

语法：

REVOKE  权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

示例：撤销user1用户对test操作的权限

REVOKE ALL ON test.* FROM 'user1'@'localhost';

8.5 查看权限

语法：

SHOW GRANTS FOR '用户名'@'主机名';

示例：查看user2用户的权限

SHOW GRANTS FOR 'user2'@'%';

8.6 删除用户

语法：

DROP USER '用户名'@'主机名'

示例：删除user2

 DROP USER 'user2'@'%';

8.7 修改用户密码

8.7.1 修改管理员密码

语法：

mysqladmin -uroot -p password 新密码

注意：需要在没有登录MySQL的情况下操作。

示例：修改管理员的密码

mysqladmin -uroot -p password root
输入旧密码

8.7.2 修改普通用户的密码

语法：

set password for '用户名'@'主机名' = password('新密码');

注意：需要在root账号登录的情况下给普通用户的密码进行修改。

示例：

set password for 'user1'@'localhost' = password('666666');

9 数据库设计的三大范式（⭐）

9.1 概述

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。
建立科学的、规范的数据库就需要满足一些规则来优化数据库的设计和存储，这些规则称为范式。

9.2 第一范式：确保每列保存原子性

第一范式是最基本的范式，如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理是遵循系统的实际需求来定的。比如：某些数据库系统中需要用到地址这个字段，本来直接将地址属性设计成一个数据库表的字段就行；但是，如果系统中经常会访问到地址属性中城市部分，那么就需要将地址这个属性进行重新拆分，拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中的某一部分操作的时候将会非常方便，这样的设计才满足了数据库的第一范式，如下图所示：

如果不遵循第一范式，查询出来的数据还需要进一步进行处理（查询不方便）。遵循第一范式，需要什么字段就查询什么字段（方便查询）。

9.3 第二范式：确保表中的每列都和主键相关

第二范式是建立在第一范式的基础之上。第二范式需要确保数据库表中的每一列都和主键相关，而不是只和主键的某一部分相关（主要针对联合主键而言）；换言之，在一个数据库表中，一个表中只能保存一种数据，不可以将多种数据保存在同一张数据库表中。
比如：要设计一个订单信息表，因为订单中可能有多种商品，所以将订单编号和商品编码作为数据库表中的联合主键，如下表所示：

这样就产生了一个问题：这个表示以订单编号和商品编号作为联合主键，但是该表中的商品名称、数量、商品价格和订单编号没有关系，仅仅和商品编码有关系，这就违背了数据库第二范式。
如果将这个订单信息表进行拆分，将商品信息拆分到另一个表中，将订单项目表也拆分到另一个表中，就非常完美了。

这样设计，在很大程度上减少了数据的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

9.4 第三范式：确保每列都和主键直接相关，而不是间接相关

第三范式需要确保数据库表中的每列数据都和主键直接相关，而不是间接相关。
比如：在设计一个订单信息表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，这样就可以不用在订单表中添加客户端的其他信息（比如：客户姓名、所属公司）等字段。

这样在查询订单信息的时候，就可以使用客户编号去客户信息表中查询客户的信息，而不必在订单信息表中多次存储客户信息的内容，减少了数据冗余。

10 备份和还原

10.1 前提条件

需要将MySQL的bin目录配置到Path环境变量中。

10.2 备份

命令：

mysqldump -h主机地址 -P端口号 -u用户名 -p密码 数据库名 [--default-character-set=utf8] > 文件路径/文件名.sql

--default-character-set=utf8：设置导出文件的编码。

示例：

mysqldump -hlocalhost -P3306 -uroot -p123456 test --default-character-set=utf8 > d:/test.sql

10.3 还原

登录MySQL，然后指定还原的命令：

use 数据库名;
source sql脚本路径.sql

示例：

11 视图（了解）

11.1 概述

MySQL从5.0.1版本开始提供了视图功能。一种虚拟存在的表，行和列的数据来自自定义视图的查询中使用的表，并且是在使用视图的时候动态生成的，只保存了SQL的逻辑，不保存查询结果。

11.2 应用场景

多个地方用到同样的查询结果。
该查询结果使用的SQL语句较为复杂。

11.3 好处

重用SQL语句。
简化复杂的SQL操作，不必知道它的查询细节。
保护数据，提高安全性。

11.4 视图的语法

11.4.1 创建视图

语法：

CREATE VIEW 视图名
AS
查询语句;

示例：查询姓名中包含a字符的员工名、部门名和工种信息

CREATE VIEW v1
AS
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;
SELECT * FROM v1 WHERE last_name like '%a%';

11.4.2 修改视图

语法：

CREATE OR REPLATE VIEW 视图名
AS 
查询语句;

语法：

ALTER VIEW 视图名
AS
查询语句;

11.4.3 删除视图

语法：

DROP VIEW 视图名,视图名,……;

11.4.4 查看视图结构

语法：

DESC 视图名;

SHOW CREATE VIEW 视图名;

12 MySQL的流程控制（了解）

12.1 变量

12.1.1 分类

12.1.2 系统变量

概念：系统变量是由系统提供的，不是用户自定义的，是属于服务器层面的。
语法：
- 查看所有的系统变量：

-- 显示所有全局变量
SHOW GLOBAL VARIABLES;
-- 显示所有会话变量
SHOW SESSION VARIABLES;

- 查看满足条件的部分系统变量：

-- 查看满足条件的部分全局变量
SHOW GLOBAL VARIABLES LIKE '%character%';
-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%character%';

- 查看指定的某个系统变量的值：

-- 查看某个指定的全局变量
SELECT @@GLOBAL.系统变量名;
-- 查看某个指定的会话变量
SELECT @@SESSION.系统变量名;

- 为某个系统变量赋值：

-- 为全局变量设置值
SET GLOBAL 系统变量名 = 值;
-- 为会话变量设置值
SET SESSION 系统变量名 = 值;
-- 为全局变量设置值
SET @@GLOBAL.系统变量名 = 值;
-- 为会话变量设置值
SET @@SESSION.系统变量名 = 值;

如果是全局级别需要加GLOBAL，如果是会话级别需要加SESSION。如果什么都不写，默认是SESSION。

全局变量的作用域：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话有效，但是不能跨重启。如果需要跨重启，需要修改服务器的配置文件。
会话变量的作用域：仅仅针对当前的会话（连接）有效。
示例：显示所有的全局变量

SHOW GLOBAL VARIABLES;

示例：查看部分的全局变量

SHOW GLOBAL VARIABLES LIKE '%character%';

示例：查看指定的全局变量的值

SELECT @@GLOBAL.autocommit;

SELECT @@GLOBAL.tx_isolation;

示例：为某个指定的全局变量赋值

SET @@GLOBAL.autocommit = 1;

SET GLOBAL autocommit = 1;

示例：显示所有的会话变量

SHOW SESSION VARIABLES;

示例：查看部分的会话变量

SHOW SESSION VARIABLES LIKE '%character%';

示例：查看指定的会话变量的值

SELECT @@SESSION.autocommit;

SELECT @@SESSION.tx_isolation;

示例：为某个指定的会话变量赋值

SET @@SESSION.autocommit = 1;

SET SESSION autocommit = 1;

12.1.3 自定义变量

概念：变量是用户自定义的，不是由系统提供的。
用户变量：
- 作用域：
- - 针对于当前会话（连接）有效，同于会话变量的作用域。
- - 用户变量可以放在任何地方，可以放在begin...end里面或begin...end外面。
- 语法：
- - 声明并初始化值：

SET @用户变量名 = 值;
-- 推荐方式
SET @用户变量名 := 值;

- - 赋值(更新用户变量的值)：

SET @用户变量名 = 值;
-- 推荐方式
SET @用户变量名 := 值;
SELECT 字段 into 用户变量名 FROM 表名;

- - 使用用户变量（查看用户变量）：

SELECT @用户变量名;

局部变量：
- 作用域：
- - 仅仅在定义它的begin...end中有效。
- - 应用在begin..end中，而且必须是第一句。
- 语法：
- - 声明：

DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;

- - 赋值：

SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT 字段 into局部变量名 FROM 表名;

- - 使用局部变量（查看局部变量）：

SELECT 局部变量名;

12.2 存储过程和函数

概述：类似于Java中的方法。
好处：
- 简化了应用开发人员的很多工作。
- 减少了数据在数据库和应用服务器之间的传输。
- 提供了数据处理的效率。
存储过程：
- 概念：一组预先编译好的SQL语句的集合，可以理解成批处理语句。
- 创建存储过程语法：

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

注意：

①参数列表包含三部分：参数模式参数名参数类型
- 例如： IN sut_name varchar(255)
- 参数模式： IN(该参数可以作为输入，即该参数需要调用方传入值)、OUT(该参数可以作为输出，即该参数可以作为返回值)、INOUT(该参数可以既作为输入又可以作为输出)
②如果存储过程体仅仅只有一句话，那么BEGIN...END可以省略
- 存储过程体的每条SQL语句结尾要求必须加上分号;
- 存储过程的结尾可以使用DELIMITER重新设置。
- 语法：DELIMITER 结束标记
- 例如：DELIMITER $
- 调用存储过程语法：

CALL 存储过程名(实参列表);

- 删除存储过程语法：

DROP PROCEDURE 存储过程名;

- 查看创建存储过程信息语法：

SHOW CREATE PROCEDURE 存储过程名;

函数：
- 概念：
- - 一组预先编译好的SQL语句的集合，可以理解成批处理语句。
- - 存储过程可以有0个返回，也可以有多个返回，而函数有且仅有一个返回。
- 创建函数的语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：

① 参数列表包含两个部分：参数名参数类型。
② 函数体：必须有return语句。
③ 函数体重仅仅有一句的时候，可以省略BEGIN...END。
④ 需要使用DELIMITER语句设置结束标记。
- 调用函数的语法：

SELECT 函数名(实参列表);

- 删除函数的语法：

DROP FUNCTION 函数名;

- 查看创建函数的语法：

SHOW CREATE FUNCTION 函数名;

示例：查询员工名为K_ing的所有记录

-- 创建存储过程
DELIMITER $;
create PROCEDURE test()
BEGIN
	SELECT * FROM employees WHERE employees.last_name = 'K_ing';
END $;
DELIMITER ;

-- 调用存储过程
CALL test();

示例：查询员工名为K_ing的所有记录

-- 创建存储过程
DELIMITER $;
create PROCEDURE test(IN last_name VARCHAR(255))
BEGIN
	SELECT * FROM employees WHERE employees.last_name = last_name;
END $;
DELIMITER ;

-- 调用存储过程
CALL test('K_ing');

示例：查询公司的员工个数

-- 创建函数
DELIMITER $;
create FUNCTION test() RETURNS int
BEGIN
  DECLARE count int DEFAULT 0; 
  SELECT count(*) into count from employees;
	return count;
	
END $;
DELIMITER ;

-- 调用函数
SELECT test();

12.3 流程控制语句

12.3.1 分支结构

case结构情况1：类似于Java中的switch语句，一般用于实现等值判断。
语法：

CASE 变量|表达式|字段
WHEN 要判断的值1 THEN 返回的值1或语句1;
WHEN 要判断的值2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END [CASE;] -- 如果是语句的话，需要加CASE;

case结构情况2：类似于Java中的多重if语句，一般用于实现区间判断。
语法：

CASE 
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END [CASE;] -- 如果是语句的话，需要加CASE;

注意：

case结构可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN...END中或BEGIN...END外面。
case结构也可以作为独立的语句去使用，只能放在BEGIN...END中。
示例：创建一个存储过程，根据传入的成绩，来显示等级。比如传入的成绩：90-100，显示A；80-90，显示B;60-80,显示C;否则，显示D。

DELIMITER $
CREATE PROCEDURE test(IN score INT)
BEGIN
	CASE 
	WHEN score >= 90 and score <=100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;

END $
DELIMITER ;

CALL test(55);

12.3.2 循环结构

在MySQL中有while、loop和repeat三种循环结构。
在MySQL中iterate类似于continue，结束本次循环。leave类似于break，结束当前循环。
while语法：

[标签:] WHILE 循环条件
DO
	循环体
END WHILE [标签];

loop语法：可以用来模拟简单的死循环

[标签:] LOOP
    循环体
END LOOP [标签];

repeat语法：类似于do...while

[标签:] REPEAT
   循环体
UNTIL 结束循环条件
END repeat [标签];

13 解决忘记root用户密码问题

下面的操作需要以管理员权限运行cmd。

关闭MySQL57服务。

net stop MySQL57

修改my.ini文件，使得MySQL启动的时候跳过权限检查：

# 在[mysqld]下方添加
skip-grant-tables = true

开启MySQL57服务：

net start MYSQL57

无密码登录MySQL：

mysql -u root

修改root账户的密码为123456：

update mysql.user set authentication_string=password('123456') where user='root';

刷新权限：

flush privileges;

退出登录的MySQL：

exit;

修改my.ini文件，删除之前添加在[mysqld]下方的那一行。

验证无密码登录是否会失败：

mysql -u root

使用新密码登录MySQL：

mysql -u root -p123456

Mysql入门——mysql基础练练手（2）