⭐表示重要。

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 登录和退出

  • 登录
  1. mysql -h localhost -P 3306  -u root -p123456
  3. -- h:host 主机地址
  4. -- P:port 端口
  5. -- p:password 密码
  6. -- 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日');

date_formate.png

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 连接查询的分类

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

4.7.5 SQL 92 标准之等值连接

  • 示例：查询女神名和其对应的男神名
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 SQL 92 标准之非等值连接

  • 工资等级的 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 SQL 92 标准之自连接

  • 示例：
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 SQL 99 标准之语法

  • 语法：
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 SQL 99 标准之内连接

  • 示例：查询女神名和其对应的男神名
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 SQL 99 标准之外连接

  • 示例：查询没有男朋友的女神名
SELECT
    beauty.`name`,
    boys.boyName 
FROM
    beauty
    LEFT JOIN boys ON beauty.boyfriend_id = boys.id 
WHERE
    boys.boyName IS NULL;

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

  • 示例：
SELECT
    beauty.`name`,
    boys.boyName 
FROM
    beauty
    CROSS JOIN boys;

4.8 子查询

4.8.1 概述

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

什么是子查询.png

  • 按照子查询出现的位置：
    • 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 整型

数据类型之整型.png

  • 特点：
  • ① 如果不设置无符号还是有符号，默认是有符号。如果需要设置无符号，需要添加 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 小数

数据类型之小数.png

  • 语法：
CREATE TABLE `表名` ( 
    列名 FLOAT ( M, D ), 
    列名 DOUBLE ( M, D ), 
    列名 DECIMAL ( M, D ) 
);
  • 特点：
  • ① M 表示整数位 + 小数位。D 表示小数位。
  • ② M 和 D 都可以省略不写。如果是 DECIMAL ，那么 M 默认是 10 ，D 默认为 0。
  • ③ 定点型的精确度较高。如货币运算。

6.4.4 字符型

数值类型之字符型.png

  • 特点：
  • ① 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 日期类型

数据类型之日期类型.png

  • 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没有开启事务的情况.jpg

  • MySQL 手动开启事务的情况：

MySQL开启事务的情况.jpg

7.3 事务的使用场景

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

7.4 存储引擎

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

查看MySQL的存储引擎.png

  • 在 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 隐式事务

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

查看MySQL的隐式事务.png

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 授权用户

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

用户创建之后，基本没什么权限.png

  • 语法：
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';

给user1用户分配对test这个数据库操作的权限.png

  • 示例：给 user2 用户分配对所有数据库操作的权限
GRANT ALL ON *.* TO 'user2'@'%';

给user2用户分配对所有数据库操作的权限.png

8.4 撤销授权

  • 语法：
REVOKE  权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';
  • 示例：撤销 user1 用户对 test 操作的权限
REVOKE ALL ON test.* FROM 'user1'@'localhost';

撤销user1用户对test操作的权限.png

8.5 查看权限

  • 语法：
SHOW GRANTS FOR '用户名'@'主机名';
  • 示例：查看 user2 用户的权限
SHOW GRANTS FOR 'user2'@'%';

查看user2用户的权限.png

8.6 删除用户

  • 语法：
DROP USER '用户名'@'主机名'
  • 示例：删除 user2
 DROP USER 'user2'@'%';

删除user2.png

8.7 修改用户密码

8.7.1 修改管理员密码

  • 语法：
mysqladmin -uroot -p password 新密码

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

  • 示例：修改管理员的密码
mysqladmin -uroot -p password root
输入旧密码

修改管理员的密码.png

8.7.2 修改普通用户的密码

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

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

  • 示例：
set password for 'user1'@'localhost' = password('666666');

修改普通用户的密码.png

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

9.1 概述

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

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

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

数据库第一范式.png

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

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

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

数据库第二范式1.png

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

数据库第二范式2.jpg

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

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

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

数据库第三范式.jpg

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

10 备份和还原

10.1 前提条件

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

配置MySQL的Path环境变量1.png

配置MySQL的Path环境变量2.png

配置MySQL的Path环境变量3.png

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

备份数据库.png

10.3 还原

  • 登录 MySQL ，然后指定还原的命令：
use 数据库名;
source sql脚本路径.sql
  • 示例：

还原数据库.png

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

变量的分类.png

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 ，如果是会话级别需要加S ESSION 。如果什么都不写，默认是 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 [标签];
  • repea t语法：类似于 do…while
[标签:] REPEAT
   循环体
UNTIL 结束循环条件
END repeat [标签];

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

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

  • 关闭 MySQL57 服务。
net stop MySQL57

关闭MySQL57服务.png

  • 修改 my.ini 文件，使得 MySQL 启动的时候跳过权限检查：
# 在[mysqld]下方添加
skip-grant-tables = true

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

  • 开启 MySQL57 服务：
net start MYSQL57

开启MySQL57服务.png

  • 无密码登录 MySQL ：
mysql -u root

无密码登录MySQL.png

  • 修改 root 账户的密码为 123456 ：
update mysql.user set authentication_string=password('123456') where user='root';

修改root账户的密码为123456.png

  • 刷新权限：
flush privileges;

刷新权限.png

  • 退出登录的 MySQL ：
exit;

退出登录的MySQL.png

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

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

  • 验证无密码登录是否会失败：
mysql -u root

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

  • 使用新密码登录 MySQL ：
mysql -u root -p123456

使用新密码登录MySQL.png