MySQL学习笔记

最新推荐文章于 2025-02-05 20:52:48 发布

二木成林

最新推荐文章于 2025-02-05 20:52:48 发布

阅读量3.5k

点赞数 11

分类专栏：学习笔记文章标签： MySQL

本文链接：https://blog.csdn.net/cnds123321/article/details/116807242

版权

学习笔记专栏收录该内容

29 篇文章

订阅专栏

1.MySQL的安装配置

1.0 特别说明

本笔记整理自尚硅谷官方李玉婷老师的MySQL基础视频

本章所需要用到的sql文件

girls.sql

- ----------------------------
-- Table structure for admin
-- ----------------------------
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;

-- ----------------------------
-- Records of admin
-- ----------------------------
INSERT INTO `admin` VALUES ('1', 'john', '8888');
INSERT INTO `admin` VALUES ('2', 'lyt', '6666');

-- ----------------------------
-- Table structure for beauty
-- ----------------------------
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;

-- ----------------------------
-- Records of beauty
-- ----------------------------
INSERT INTO `beauty` VALUES ('1', '柳岩', '女', '1988-02-03 00:00:00', '18209876577', null, '8');
INSERT INTO `beauty` VALUES ('2', '苍老师', '女', '1987-12-30 00:00:00', '18219876577', null, '9');
INSERT INTO `beauty` VALUES ('3', 'Angelababy', '女', '1989-02-03 00:00:00', '18209876567', null, '3');
INSERT INTO `beauty` VALUES ('4', '热巴', '女', '1993-02-03 00:00:00', '18209876579', null, '2');
INSERT INTO `beauty` VALUES ('5', '周冬雨', '女', '1992-02-03 00:00:00', '18209179577', null, '9');
INSERT INTO `beauty` VALUES ('6', '周芷若', '女', '1988-02-03 00:00:00', '18209876577', null, '1');
INSERT INTO `beauty` VALUES ('7', '岳灵珊', '女', '1987-12-30 00:00:00', '18219876577', null, '9');
INSERT INTO `beauty` VALUES ('8', '小昭', '女', '1989-02-03 00:00:00', '18209876567', null, '1');
INSERT INTO `beauty` VALUES ('9', '双儿', '女', '1993-02-03 00:00:00', '18209876579', null, '9');
INSERT INTO `beauty` VALUES ('10', '王语嫣', '女', '1992-02-03 00:00:00', '18209179577', null, '4');
INSERT INTO `beauty` VALUES ('11', '夏雪', '女', '1993-02-03 00:00:00', '18209876579', null, '9');
INSERT INTO `beauty` VALUES ('12', '赵敏', '女', '1992-02-03 00:00:00', '18209179577', null, '1');

-- ----------------------------
-- Table structure for boys
-- ----------------------------
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;

-- ----------------------------
-- Records of boys
-- ----------------------------
INSERT INTO `boys` VALUES ('1', '张无忌', '100');
INSERT INTO `boys` VALUES ('2', '鹿晗', '800');
INSERT INTO `boys` VALUES ('3', '黄晓明', '50');
INSERT INTO `boys` VALUES ('4', '段誉', '300');

myemployees.sql

-- ----------------------------
-- Table structure for departments
-- ----------------------------
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`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('10', 'Adm', '200', '1700');
INSERT INTO `departments` VALUES ('20', 'Mar', '201', '1800');
INSERT INTO `departments` VALUES ('30', 'Pur', '114', '1700');
INSERT INTO `departments` VALUES ('40', 'Hum', '203', '2400');
INSERT INTO `departments` VALUES ('50', 'Shi', '121', '1500');
INSERT INTO `departments` VALUES ('60', 'IT', '103', '1400');
INSERT INTO `departments` VALUES ('70', 'Pub', '204', '2700');
INSERT INTO `departments` VALUES ('80', 'Sal', '145', '2500');
INSERT INTO `departments` VALUES ('90', 'Exe', '100', '1700');
INSERT INTO `departments` VALUES ('100', 'Fin', '108', '1700');
INSERT INTO `departments` VALUES ('110', 'Acc', '205', '1700');
INSERT INTO `departments` VALUES ('120', 'Tre', null, '1700');
INSERT INTO `departments` VALUES ('130', 'Cor', null, '1700');
INSERT INTO `departments` VALUES ('140', 'Con', null, '1700');
INSERT INTO `departments` VALUES ('150', 'Sha', null, '1700');
INSERT INTO `departments` VALUES ('160', 'Ben', null, '1700');
INSERT INTO `departments` VALUES ('170', 'Man', null, '1700');
INSERT INTO `departments` VALUES ('180', 'Con', null, '1700');
INSERT INTO `departments` VALUES ('190', 'Con', null, '1700');
INSERT INTO `departments` VALUES ('200', 'Ope', null, '1700');
INSERT INTO `departments` VALUES ('210', 'IT ', null, '1700');
INSERT INTO `departments` VALUES ('220', 'NOC', null, '1700');
INSERT INTO `departments` VALUES ('230', 'IT ', null, '1700');
INSERT INTO `departments` VALUES ('240', 'Gov', null, '1700');
INSERT INTO `departments` VALUES ('250', 'Ret', null, '1700');
INSERT INTO `departments` VALUES ('260', 'Rec', null, '1700');
INSERT INTO `departments` VALUES ('270', 'Pay', null, '1700');

-- ----------------------------
-- Table structure for employees
-- ----------------------------
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`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;

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

-- ----------------------------
-- Table structure for jobs
-- ----------------------------
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`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

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

-- ----------------------------
-- Table structure for job_grades
-- ----------------------------
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades` (
  `grade_level` varchar(3) DEFAULT NULL,
  `lowest_sal` int(11) DEFAULT NULL,
  `highest_sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of job_grades
-- ----------------------------
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');

-- ----------------------------
-- Table structure for locations
-- ----------------------------
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`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;

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

1.1 数据库概述

1.1.1 什么是数据库

数据库是保存有组织的数据的容器，也就是保存数据的仓库。

1.1.2 为什么需要数据库

为什么需要数据库：保存数据的容器如数组、集合都是保存在内存中，当断电后这些数据就会失效，那么可以考虑存储到文件中，但又不适合按条件查询，所以出现了数据库管理系统，它的特点是：

实现数据持久化（保存为文件的优点）
使用完整的管理系统统一管理，易于查询

1.1.3 数据库的常见概念

DB：数据库，是database的简称，
DBMS：数据库管理系统（Database Management System），常见的数据库管理系统有MySQL、Oracle、DB2、SQL server等（注：我们平时说的MySQL数据库等是在说数据库管理系统）。数据库是通过数据库管理系统创建和操作的容器。
SQL：结构化查询语言（Structure Query Language），专门用来与数据库通信的语言（注：比如说查询就要用"select"，插入就要用"insert"，本质上和编程一样）。SQL的优点如下：
- 不是某个特定数据库供应商专有的语言，几乎所有的数据库管理系统都支持SQL。（注：也就是说SQL是数据库语言的一种行业标准，其他数据库系统如MySQL、SQL server在大体上都遵循SQL，只是在一些细微的操作有所不同，所以说学会了MySQL其他数据库也会触类旁通。）
- 简单易学，但可以进行非常复杂和高级的数据库操作。

1.1.4 数据库的特点

1、将数据放到表中，表再放到库中。

2、一个数据库中可以有多张表，每个表有一个名字，用来唯一标识自己。

3、表具有一些特性，这些特性定义了数据在表中如何存储，类似于Java中“类”的设计。

4、表由列组成，也称为字符（注：所以我们如果说“字段”，那么一定是在说数据库表，而不是Java类）。所有表由一个或多个列组成，每一列类似于Java中的属性。如下图表有4列，分别是id、username、password和register_date。

5、表中的数据是按行存储的，每一行类似于Java中的“对象”。

1.1.5 MySQL概述

MySQL是当前最流行的关系型数据库之一。

MySQL的特点

成本低：开源，可以免费使用。
性能高：SQL语句执行很快。
简单：安装和使用简单。

数据库管理系统分为两类：

基于共享文件系统的DBMS，如Access。
基于客户机/服务器的DBMS，如MySQL、Oracle、SQL server等。（注：所以我们一般安装MySQL说的是安装服务端软件）

1.3 MySQL的安装和使用

1.3.1 MySQL的下载

请上官网下载，或者使用提供的MySQL安装文件。

1.3.2 MySQL的安装/卸载

请上网查找安装教程，或者使用提供的MySQL安装教程。

安装成功后目录如下：

其中my.ini文件很重要是MySQL数据库的配置文件：

MySQL的卸载可以参照下图给出的提示：

1.3.3 MySQL服务的启动/停止

MySQL安装成功后，还需要启动服务才能使用。

启动MySQL服务有如下两种方式：

第一种方式：

找到计算机中的“计算机管理”，双击它

进入后，找到“计算机管理”左侧菜单栏中的“服务”，双击，然后在右边的面板中找到为“MySQL”的服务名称（这个服务名称跟安装时指定的名称有关，如果没有修改则默认为MySQL），右键单击，出现面板，可以启动和停止该服务。

第二种方式：

以管理员身份运行DOS窗口，打开命令行（注意：使用命令行方式的前提是正确配置了MySQL的环境变量。）

输入"net stop mysql"即可停止MySQL服务

输入"net start mysql"即可开启MySQL服务

1.3.4 MySQL服务端的登录/退出

启动MySQL服务后，还需要登录账户才能够授权使用，所以需要输入用户名和密码进行登录，也有两种方式实现：

第一种方式：使用MySQL自带的客户端（不推荐使用）

在Windows电脑的菜单栏中找到MySQL 5.5 Command Line Client命令行客户端，双击进入

进入后输入登录MySQL的账户密码

出现下图的情况表示登录成功，可以开始使用数据库了

输入"exit"或者按Ctrl+C快捷键来退出MySQL的登录。

第二种方式：使用命令行

使用管理员身份打开DOS窗口

然后输入"mysql -h localhost -P 3306 -u root -p"，然后回车，输入登录密码，也可以成功登录

mysql -h localhost -P 3306 -u root -p
# 这是一个例子
# 也是固定的语句，几乎不会改变，输入后回车再输入密码
# -P 3306是大写的P字母，表示端口号，后面的小写p是指登录密码
mysql [-h主机名 -P端口号] -u用户名 -p密码
# [-h主机名 -P端口号]表示可选项，可写可不写
# -h这些参数后面可以写空格也可以不写，但小写-p后面如果是明文密码不加空格，如果是密文（即回车）就不需要
# 退出可以输入"exit"或Ctrl+C

1.3.5 MySQL的环境变量配置

一般来说，只要按照上面提供的教程正确安装MySQL成功，就会默认配置好环境变量，如果没有配置成功，那么请上网查找相关教程，暂不提供。

1.4 MySQL的常用命令

这里所谓的常用命令就是几个才进入数据库可以使用到的命令。

1.4.1 查看数据库

语法如下：

show databases;
# "show"表示显示，"databases"是"database"的复数形式，所以是显示多个数据库，最后以分号结尾是为了区分数据库执行语句
# 查看当前数据库管理系统中所有数据库
# 注意，查询出来的information_schema、mysql、performance_schema和test是系统默认有的，不能修改（test可以用，其他不能）

1.4.2 操作某个数据库

语法如下：

use 数据库名
# 查看具体某个数据库
# 例如：use test;

1.4.3 查看某个数据库下的所有表

语法如下：

show tables;
# 固定语句，查看某个数据库下的所有表名称
# 前提是，已经进入到该数据库下（即执行了use语句）
show tables from 数据库名;
# 固定语句，查看其他数据库的表

1.4.4 查看当前处于哪个数据库中

语法如下：

select database();
# 固定语句，查看当前处于哪个数据库中

1.4.5 查看表的结构

语法如下：

desc 表名;
# 查看指定表下的数据结构

1.4.6 查看系统版本

语法如下：

select version();
# 查看系统服务端的版本，注意，这是在数据库系统内
mysql --version
# 或
mysql -V
## 这两条命令是在数据库系统外，即命令行中

1.5.7 MySQL的语法规范

不区分大小写，但建议关键字大写，表名、列名小写。
每条命令最好用分号结尾。
每条命令根据需要，开源进行缩进或换行。
有如下注释方式：

# 单行注释
-- 单行注释
/* 多行注释 */

1.5.8 常用命令总结

命令	功能
SHOW DATABASES;	查看当前系统中有哪些数据库
USE 数据库名;	将使用某数据库
SHOW TABLES;	查看某数据库下的所有表名称
SELECT DATABASE();	查看当前处于哪个数据库中
DESC 表名	查看数据库下指定表的结构
SELECT VERSION();	查看数据库系统的版本

1.5 MySQL的图形化界面客户端

较为推荐的有Navicat For Mysql、SQLyog等。

它们的安装和使用都比较简单，不再详细说明。

2.DQL语言的学习

DQL的全称是Data Query Language，即数据查询语言。

2.1 基础查询

基础查询即只有select和from的查询。

语法：

select 查询列表 from 表名;
-- 查询的基本语法如上所示
-- 查询列表可以是：表中的字段、常量值、表达式、函数
-- 例如：select id,name from user;
-- 例如：select count(*) from user;

基本查询的几种情况：

2.1.1 查询表中的单个字段。

-- 语法
select 字段名 from 表名;
-- 示例
select id from user;

2.1.2 查询表中的多个字段

-- 语法：不同字段之间用英文逗号隔开
select 字段名,字段名,字段名,... from 表名;
-- 示例
select id,name,sex from user;

2.1.3 查询表中所有字段

-- 语法：有两种方式，一种是按照查询多个字段的方式写出所有字段名，另外一种是使用通配符"*"号
select 字段名,字段名,字段名,... from 表名;
# 或
select * from 表名;
-- 示例
select * from user;

2.1.4 查询常量值

-- 语法：直接在select后面跟常量，没有实际意义
select 常量值;
-- 示例
select 100;
select '张三';

2.1.5 查询函数

-- 语法：直接在select后面跟函数名
select 函数名;
-- 示例
select VERSION();

2.1.6 查询表达式

-- 语法：直接在select后面跟运算表达式
select 表达式;
-- 示例
select 100%12;

2.1.7起别名

别名的用处：

①便于理解，有些字段可能过长
②如果在多张表的查询中有重复的字段的情况，可以使用别名区分开来。

注意：

①可以对表名起别名。
②也可以对字段名起别名。

起别名的方式：

使用AS关键字
使用空格

-- 语法：其中[AS]是一个可选项，即可以使用AS表明，也可以省略不写，都表示是别名
select 字段名 [AS] 别名 from 表名;
-- 示例
select last_name AS 姓,first_name AS 名 from user;
select last_name 姓,first_name 名 from user;
-- 特别注意：如果别名中含有如空格这样的特殊字符，要用双引号或单引号引起来
select salary AS "工 资" from employee;

2.1.8 去重

去重的含义是去掉重复的记录，只需要在select后面跟DISTINCT关键字即可。

-- 语法
select distinct 字段名 from 表名;
-- 示例
select distinct name from user;

2.1.9 "+"号运算

在MySQL中"+"号仅作为运算符使用，计算两个数的和。例如：select 100+90，最后的结果是190。

但如果是select '123'+90，其中一方是字符型，试图将字符型数值转换成数值型，如果转换成功则作加法运算，如果转换失败，则将字符型数值转换成0，所以最后的结果为90。

但如果是select null+100，其中一方是null，那么最后的结果一定是null。

所以总结如下：

加号两端的数值类型	处理方式	例如	结果
都是数值类型	作加法运算	select 100+90	190
一方是非数值类型	将非数值类型数据转换成数值类型数据，如果转换成功则作加法运算，如果转换失败则转换成0再做加法运算	select 'abc'+100	100
一方是null类型	那么结果一定为null	select null+100	null

本节的练习题请参考0.综合实例部分。

2.2 条件查询

条件查询顾名思义就是带有条件的查询，比如要求性别为男，工资大于5000等等。

基本语法格式如下：

-- 语法
selecct 查询列表 from 表名 where 筛选条件;
-- 注意：where后面跟着筛选条件
-- 例如：select * from user where sex='男';

条件查询按情况分为三类：

按条件表达式筛选
按逻辑表达式筛选
模糊查询

2.2.1 按条件表达式筛选

常用的条件运算符有：>、<、<、=、!=、<>、>=、<=等。

-- 语法
select 查询列表 from 表名 where 字段名 条件运算符 常量值
-- 示例
select * from user where sex='男';
select * from user where age>=18;

2.2.2 按逻辑表达式筛选

在MySQL中的逻辑运算符主要有：and、or、not。分别对应与、或、非。

逻辑运算符	效果
and	等价于Java中的"&&"，两个条件都为true，结果为true，反之为false
or	等价于Java中的"\|\|"，只要有一个条件为true，结果为true，反之为false
not	等价于Java中的"!"，如果连接的条件本身为false，结果为true，反之为false

语法如下：

-- 语法
select 查询列表 from 表名 where 条件表达式1 [AND|OR] 条件表达式2;# AND和OR运算符的语法
select 查询列表 from 表名 where NOT(条件表达式);# NOT运算符的语法
-- 示例
select * from user where age>=18 AND age<=24;# 查询年龄在18到24的用户信息
select * from user where NOT(age>=18 AND age<=24);# 查询年龄不在18到24的用户信息

2.2.3 模糊查询

模糊查询有以下几个关键字并按此分类：

like
between and
in
is null/is not null

2.2.3.1 like

like用来进行模糊匹配，通常和通配符搭配使用，可以使用的通配符有两个：

%：匹配任意多个字符，也包含0个字符。如："%abc%"可以匹配"12abc"、"abc123"、"12abc56"、"abc"等等。
_：匹配任意单个字符。如："ab_c"可以匹配"ab1c"等，但不能匹配"abccd"。

-- 语法
select 查询列表 from 表名 where 字段名 like 匹配的字符串;
-- 示例
select * from user where name like '%王%';# 查询用户名中带有"王"的记录
select * from user where name like '_玲';# 查询用户名中第二个字符为"玲"的记录

我们知道%和_是通配符，但如果要去匹配"%"和" _ "字符，那么就需要进行转义，转义的语法如下：

转义前：select * from user where name like '_三';# 匹配名字为"_三"的记录，这里"_"作为一个普通字符而不是通配符使用
转义后：select * from user where name like '/_三' ESCAPE '/';# 表示"/"之后的"_"不作为通配符

2.2.3.2 between and

between and用来匹配在一个范围内的记录，如年龄在18到24的记录。

between and的特点如下：

①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序

语法如下：

-- 语法
select 查询列表 from 表名 where 字段名 BETWEEN 起始临界值 AND 结束临界值;
-- 示例
select * from user where age>=18 and age<=24;# 使用条件运算符查询
select * from user where age BETWEEN 18 AND 24;# 上面的语句等价于该SQL句，使用between and限定范围

2.2.3.3 in

in用来判断某字段的值是否属于in列表中的某一项。

特点如下：

①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符

-- 语法
select 查询列表 from 表名 where 字段名 IN 匹配的列表;# 匹配的列表用小括号包含
-- 示例
select * from user where sex in ('男','女');# 匹配性别为"男"和"女"的记录

2.2.3.4 is null

=、!=、<>不能用来判断null值。

因此MySQL提供了is null和is not null来判断是否是null值。

语法：

-- 语法
select 查询列表 from 表名 where 字段名 IS NULL;# 判断某字段为null值的记录
select 查询列表 from 表名 where 字段名 IS NOT NULL;# 判断某字段不为null值的记录
-- 示例
select * form user where id IS NULL;# 查询id为null值的记录
select * from user where is IS NOT NULL;# 查询id不为null值的记录

注意：MySQL提供了一个"<=>"安全等于用于判断null值和普通数值。

is null VS <=>：

IS NULL：仅仅可以判断NULL值，可读性较高，建议使用
<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低

2.3 排序查询

排序指按照某种顺序进行排列。

在MySQL中排序所需要用到的关键字有ASC和DESC。

ASC是单词"ascend"的缩写，意为"上升"，表示升序；

DESC是单词"descend"的缩写，意为"下降"，表示降序。

基本语法如下：

select 查询列表 from 表名 [where 筛选条件] ORDER BY 排序的字段或表达式;

特点如下：

1、asc代表的是升序，可以省略；desc代表的是降序
2、order by子句可以支持单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句

2.3.1 按单个字段排序

-- 语法：直接在ORDER BY后面添加单个字段排序
select 排序列表 from 表名 ORDER BY 字段名 [DESC|ASC];# 按单个字段进行升序或降序排列
-- 示例
select * from employees ORDER BY salary DESC;# 按工资进行降序排列

2.3.2 添加筛选条件再排序

-- 语法：在ORDER BY之前添加where进行条件筛选
select 排序列表 from 表名 WHERE 筛选条件 ORDER BY 字段名 [DESC|ASC];# 按筛选进行升序或降序排列
-- 示例
select * from employees where department_id>=90 ORDER BY employee_id DESC;# 查询部门编号>=90的员工信息，并按员工编号降序

2.3.3 按表达式排序

-- 语法：在ORDER BY后面跟着计算的表达式进行排序
select 排序列表 from 表名 ORDER BY 表达式 [DESC|ASC];# 按表达式进行升序或降序排列
-- 示例
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;# 查询员工信息，按年薪降序

2.3.4 按别名排序

-- 语法：为字段添加别名，然后在ORDER BY后面用别名排序
select 字段1,字段2 别名,字段3,... from 表名 ORDER BY 别名 [DESC|ASC];# 按别名进行排序
-- 示例
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 ASC;# 查询员工信息，按年薪升序

2.3.5 按函数排序

-- 语法：使用函数对字段进行处理排序
SELECT 函数(字段) from 表名 ORDER BY 函数(字段) [DESC|ASC];# 按函数排序
-- 示例
SELECT LENGTH(last_name),last_name FROM employees ORDER BY LENGTH(last_name) DESC;# 查询员工名，并且按名字的长度降序

2.3.6 按多个字段排序

-- 语法：可以在ORDER BY后面添加多个待排序的字段
select 排序列表 from 表名 ORDER BY 字段名1 [DESC|ASC],字段名2 [DESC|ASC],...;# 按多个字段排序
-- 示例
select * from employees ORDER BY salary DESC,employee_id ASC;# 查询员工信息，要求先按工资降序，再按employee_id升序

2.4 常见函数

2.4.1 字符串函数

注意：字符串函数中索引从1开始。

函数	描述	实例	结果
CHAR_LENGTH(s)	返回字符串 s 的字符数	select char_length('字符串');	3
CHARACTER_LENGTH(s)	返回字符串 s 的字符数	select character_length('字符串') as LengthOfString;	3
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	select concat('我','在','学习','SQL') as ConcatenatedString;	我在学习SQLUPPER
UPPER(s1)	将字符串中的字母全部变成大写字母。	select UPPER('join');	JOIN
LOWER(s1)	将字符串中的字母全部变成小写字母。	select LOWER('JOin');	join
CONCAT_WS(x, s1,s2...sn)	同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符	select concat_ws('--','我','在','学习','SQL');	我--在--学习--SQL
FIELD(s,s1,s2...)	返回第一个字符串 s 在字符串列表(s1,s2...)中的位置	select field('我','我','在','学习','SQL');	1
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置	select find_in_set('c','a,b,c,d,e,f');	3
FORMAT(x,n)	函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。	select format(3.14157,2);	3.14
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	select insert('abcdefg',1,5,'12345');	12345fg
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置	select locate('b','abcd');	2
LCASE(s)	将字符串 s 的所有字母变成小写字母	select lower('ABCDEFG');	abcdefg
LEFT(s,n)	返回字符串 s 的前 n 个字符	select left('abcdefg',3);	abc
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	select lpad('abc',10,'1234');	1234123abc
LTRIM(s)	去掉字符串 s 开始处的空格	select ltrim(' abcdefgh') as lefttrimmedstring;	abcdefgh
MID(s,n,len)	从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)	select mid('ABCDEFG',2,3);	BCD
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	select position('b' in 'abcd');	2
REPEAT(s,n)	将字符串 s 重复 n 次	select repeat('abc',5);	abcabcabcabcabc
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	select replace('abc','b','2');	a2c
REVERSE(s)	将字符串s的顺序反过来	select reverse('123456789');	987654321
RIGHT(s,n)	返回字符串 s 的后 n 个字符	select right('abc',2);	bc
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	select rpad('abc',6,'*');	abc***
RTRIM(s)	去掉字符串 s 结尾处的空格	select rtrim('abcd ') as RightTrimmedString;	abcd
SPACE(n)	返回 n 个空格	select space(10);
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	select strcmp('loveYou','loveMe');	1
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	select substr('loveYou',1,4) as ExtractString;	love
SUBSTRING_INDEX(s, delimiter, number)	返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。	select substring_index('ab','',1);	a
TRIM(s)	去掉字符串 s 开始和结尾处的空格	select trim(' abc ') as trimmedString;	abc
UCASE(s)	将字符串转换为大写	select ucase('abcdefg');	ABCDEFG
UPPER(s)	将字符串转换为大写	select upper('abcdefg');	ABCDEFG

上述函数的示例如下：

-- 返回字符串 s 的字符数
select char_length('字符串');

-- 返回字符串 s 的字符数
select character_length('字符串') as LengthOfString;

-- 字符串 s1,s2 等多个字符串合并为一个字符串
select concat('我','在','学习','SQL') as ConcatenatedString;

-- 同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符
select concat_ws('--','我','在','学习','SQL');

-- 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置
select field('我','我','在','学习','SQL');

-- 返回在字符串s2中与s1匹配的字符串的位置
select find_in_set('c','a,b,c,d,e,f');

-- 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。
select format(3.14157,2);

-- 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
select  insert('abcdefg',1,5,'12345');

-- 从字符串 s 中获取 s1 的开始位置
select locate('b','abcd');

-- 将字符串 s 的所有字母变成小写字母
select lower('ABCDEFG');

-- 返回字符串 s 的前 n 个字符
select left('abcdefg',3);

-- 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
select lpad('abc',10,'1234');

-- 去掉字符串 s 开始处的空格
select ltrim('   abcdefgh') as lefttrimmedstring;

-- 从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)
select mid('ABCDEFG',2,3);

-- 从字符串 s 中获取 s1 的开始位置
select position('b' in 'abcd');

-- 将字符串 s 重复 n 次
select repeat('abc',5);

-- 将字符串 s2 替代字符串 s 中的字符串 s1
select replace('abc','b','2');

-- 将字符串s的顺序反过来
select reverse('123456789');

-- 返回字符串 s 的后 n 个字符
select right('abc',2);

-- 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
select rpad('abc',6,'*');

-- 去掉字符串 s 结尾处的空格
select rtrim('abcd   ') as RightTrimmedString;

-- 返回 n 个空格
select space(10);

-- 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
select strcmp('loveYou','loveMe');

-- 从字符串 s 的 start 位置截取长度为 length 的子字符串
select substr('loveYou',1,4) as ExtractString;

-- 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
select substring_index('a*b','*',1);

-- 去掉字符串 s 开始和结尾处的空格
select trim('   abc   ') as trimmedString;

-- 将字符串转换为大写
select ucase('abcdefg');

-- 将字符串转换为大写
select upper('abcdefg');

2.4.2 数学函数

注意：一些数学函数里面不推荐放入字符串，虽然不会报错，但毫无意义。COUNT(*)和COUNT(1)的效率比较：

MYISAM存储引擎下，COUNT(*)效率更高。
INNODB存储引擎下，COUNT(* )和COUNT(1)的效率差不多，比COUNT(字段)要高一些，推荐使用COUNT(*)可读性更好。

和SUM、AVG、MIN、MAX、COUNT等函数一同查询的字段要求是group by后的字段。

函数名	描述	实例
ABS(x)	返回 x 的绝对值	select abs(-8);
ROUND(x)	返回x的四舍五入值	SELECT ROUND(1.55);
CEIL(x)	向上取整，返回>=该数x的最小整数	SELECT CEIL(-1.02);
FLOOR(x)	向下取整，返回<=该数x的最大整数	SELECT FLOOR(-9.99);

示例如下：

-- 返回 x 的绝对值　
select abs(-8);

-- 返回一个表达式的平均值，expression 是一个字段
slect avg(score) from students;

-- 返回查询的记录总数，expression 参数是一个字段或者 * 号
select count(*) from student;

-- 返回列表中的最大值
select greatest(30,25,36,18,25,65);
select greatest('baidu','jingdong','guge');

-- 返回列表中的最小值
select least(30,25,36,18,25,65);
select least('baidu','jingdong','guge');

2.4.3 加密函数

函数名	描述	实例
PASSWORD(str)	一般对用户的密码加密，不可逆	insert into userinfo(uid,uname,passw) values(12,'麦当',password('123'));
MD5(str)	普通加密，不可逆	insert into userinfo(uid,uname,passw) values(13,'叮当',MD5('123'));
ENCODE(str,pswd_str)	加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它	insert into userinfo(uid,uname,passw) values(14,'叮咚',ENCODE('123','11'));
DECODE(crypt_str,pswd_str)	解密函数	select DECODE(passw,'11') from userinfo where uid=14;

示例如下：

-- password(str) 对用户密码进行加密，不可逆
insert into userinfo(uid,uname,passw) values(12,'麦当',password('123'));

-- MD5(str) 普通加密 ，不可逆
insert into userinfo(uid,uname,passw) values(13,'叮当',MD5('123'));

-- ENCODE(str,pswd_str) 加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它
insert into userinfo(uid,uname,passw) values(14,'叮咚',ENCODE('123','11'));

-- DECODE(crypt_str,pswd_str) 解密函数
select DECODE(passw,'11') from userinfo where uid=14;

-- 查看数据
select * from userinfo;

2.4.4 其他函数

函数名	描述	实例
VERSION()	查看当前系统版本号	SELECT VERSION();
DATABASE()	查看当前所处数据库	SELECT DATABASE();
USER();	查看当前系统登录用户	SELECT USER();

2.4.5 流程控制函数

不太推荐使用，因为有其他替代方式。

2.4.5.1 IF函数

-- 语法
IF(条件表达式,str1,str2)# 如果条件表达式为真则执行str1，否则执行str2
-- 示例
SELECT IF(10>5,'大','小');# 简单使用
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM employees;# 复杂使用

2.4.5.2 CASE函数使用一：SWITCH CASE的效果

在Java中switch...case的使用如下：

switch(变量或表达式){
	case 常量1：语句1;break;
	...
	default:语句n;break;
}

而MySQL使用的格式如下：

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

例如，有一个需求为：

案例：查询员工的工资，要求
    部门号=30，显示的工资为1.1倍
    部门号=40，显示的工资为1.2倍
    部门号=50，显示的工资为1.3倍
    其他部门，显示的工资为原工资

所以SQL语句为：

SELECT
	salary 原始工资,
	department_id,
	CASE department_id
		WHEN 30 THEN
			salary * 1.1
		WHEN 40 THEN
			salary * 1.2
		WHEN 50 THEN
			salary * 1.3
		END AS 新工资
FROM
	employees;

2.4.5.3 CASE函数使用二：多重IF

在Java中的多重IF判断如下：

if(条件1){
	语句1；
}else if(条件2){
	语句2；
}
...
else{
	语句n;
}

在MySQL中写的格式：

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

下面有如下需求：

案例：查询员工的工资的情况
    如果工资>20000,显示A级别
    如果工资>15000,显示B级别
    如果工资>10000，显示C级别
    否则，显示D级别

所以写的SQL语句如下：

SELECT
	salary,
	CASE
		WHEN salary > 20000 THEN
			'A'
		WHEN salary > 15000 THEN
			'B'
		WHEN salary > 10000 THEN
			'C'
		ELSE
			'D'
		END AS 工资级别
FROM
	employees;

2.5 分组函数

分组函数用来统计使用，又称为聚合函数或统计函数或组函数。

分组函数有如下几种：

SUM：求和
AVG：平均值
MAX：最大值
MIN：最小值
COUNT：计算个数

分组函数的特点：

1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型。
2、以上分组函数都忽略null值，即只有一行中有任意一列不为NULL，都会被分组函数统计在内。
3、可以和distinct搭配实现去重的运算。
4、count函数的单独介绍，一般使用count(*)用作统计行数。
5、和分组函数一同查询的字段要求是group by后的字段。

2.5.1 简单使用

-- 语法
select 分组函数(字段名) from 表名;
-- 示例
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

2.5.2 和DISTINCT搭配使用

-- 语法，DISTINCT放在字段名之前，包含在分组函数之内
select 分组函数(DISTINCT 字段名) from 表名;
-- 示例
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

2.5.3 COUNT(*)和COUNT(1)

可以使用COUNT(*)和COUNT(1)来统计表的行数。例如：

SELECT COUNT(salary) FROM employees;# 统计salary的总行数，如果salary字段为null值则不统计在内
SELECT COUNT(*) FROM employees;# 统计employees表的总行数
SELECT COUNT(1) FROM employees;# 统计employees表的总行数

效率：

MYISAM存储引擎下，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些。

2.6 分组查询

分组查询主要是使用GROUP BY关键字进行查询，它的语法如下：

select 查询列表 from 表名 [where 筛选条件] GROUP BY 分组的字段 [order by 排序的字段];

它的特点如下：

1、和分组函数一同查询的字段必须是group by后出现的字段（注：查看简单分组的示例）
2、分组查询中的筛选条件分为两类：分组前筛选和分组后筛选

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by 子句的后面	having

注：一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率。

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

2.6.1 简单的分组

-- 语法
select 查询列表 from 表名 GROUP BY 字段名;
-- 示例
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;# 查询每个工种的员工平均工资
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;# 查询每个位置的部门个数

2.6.2 实现分组前的筛选

分组前的筛选只需要在where后面添加筛选条件即可。

-- 语法
select 查询列表 from 表名 WHERE 筛选条件 GROUP BY 字段名;
-- 示例
SELECT MAX(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;# 查询邮箱中包含a字符的每个部门的最高工资
SELECT AVG(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;# 查询有奖金的每个领导手下员工的平均工资

2.6.3 实现分组后的筛选

分组后的可能会形成一张新的表，也可以进行条件筛选，不过需要用到关键字HAVING。

-- 语法
select 查询列表 from 表名 [WHERE 分组前的筛选条件] GROUP BY 字段名 HAVING 分组后的筛选条件;
-- 示例1：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
--  示例2：查询哪个部门的员工个数>5
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
-- 示例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

2.6.4 添加排序

添加排序只需要在最后面添加一个ORDER BY子句即可。

-- 语法
select 查询列表 from 表名 [WHERE 分组前的筛选条件] GROUP BY 字段名 HAVING 分组后的筛选条件 ORDER BY 字段名;
-- 示例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

2.6.5 按多个字段分组

按多个字段分组只需要在GROUP BY后面添加多个待分组的字段即可。

-- 语法：
select 查询列表 from 表名 [WHERE 分组前的筛选条件] GROUP BY 字段名1,字段名2,字段名3,.. [HAVING 分组后的筛选条件] [ORDER BY 字段名];
-- 示例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

2.7 连接查询

所谓的连接查询就是多表查询，当查询的字段来自于多个表时，就会用到连接查询。

连接查询按年代分类可以分为：

sql92标准：仅仅支持内连接。
sql99标准：支持内连接+外连接（左外和右外）+交叉连接，较为推荐。

按功能可以分为：

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接
- 右外连接
- 全外连接
交叉连接

2.7.1 sql92标准

2.7.1.1 等值连接

等值连接是我们使用最多的，就是求多张表的交集部分。它有如下特点：

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

如下图集合所示：

它的使用语法如下：

-- 语法
select 查询列表 from 表1,表2,... WHERE 连接条件
-- 示例1：查询女神名和对应的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

2.7.1.1.1 为表取别名

取别名就是为了提高语句的简洁度和区分多个重名的字段。

但注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定（即只能用别名.字段名的方式查询字段）。

-- 语法
select 查询列表 from 表1 [AS] 别名1,表2 [AS] 别名2,... WHERE 连接条件;
-- 示例：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

2.7.1.1.2 两个表的顺序可以调换

-- 示例：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

2.7.1.1.3 可以添加筛选条件

-- 语法
select 查询列表 from 表1,表2,... WHERE 连接条件 AND 筛选条件;
-- 示例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
-- 示例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

2.7.1.1.4 可以添加分组

-- 语法
select 查询列表 from 表1,表2,... WHERE 连接条件 [AND 筛选条件] [GROUP BY 分组字段];
-- 示例：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

2.7.1.1.5 可以添加排序

-- 语法
select 查询列表 from 表1,表2,... WHERE 连接条件 [AND 筛选条件] [GROUP BY 分组字段] [ORDER BY 排序字段 DESC|ASC];
-- 示例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

2.7.1.1.6 可以实现三表甚至更多表的查询

-- 语法
select 查询列表 from 表1,表2,... WHERE 连接条件1 AND 连接条件2 ... [AND 筛选条件] [GROUP BY 分组字段] [ORDER BY 排序字段 DESC|ASC];
-- 示例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

2.7.1.2 非等值连接

非等值连接指的是通过某种特定条件使两张表相连，WHERE后面跟着条件。

-- 示例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`# employees表中的salary字段在job_grades表中的lowest_sal和highest_sal范围内，这是一个条件
AND g.`grade_level`='A';

2.7.1.3 自连接

自连接必须借助别名来完成，顾名思义就是多张表其实都只有一张表，连接自己表中的字段。

用途可能是表中的某一字段的值正好是表中其他字段的值，比如评论表中的回复ID就是其他评论的主键ID。

-- 示例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

看下面的图可能有一个更为直观的感受：

2.7.2 sql99标准

sql99标准中不仅有内连接还有外连接。

它的基本语法格式是：

-- 语法
select 查询列表 from 表1 别名1 [连接类型] join 表2 别名2 on 连接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 筛选条件] [ORDER BY 排序列表]
-- 注：
-- 连接类型有：内连接inner、外连接之左外连接left outer、外连接之右外连接right outer、外连接之全外连接full outer、交叉连接cross。其中inner、outer可以省略。

2.7.2.1 内连接

内连接的基本语法为：

-- 语法
select 查询列表 from 表1 别名1 INNER join 表2 别名2 on 连接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 筛选条件] [ORDER BY 排序列表]

也分为等值、非等值、自连接三种情况。

特点如下：

①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

总之，内连接都是查询多表的交集。

2.7.2.1.1 等值连接

-- 示例1：查询员工名、部门名
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id;
-- 示例2：查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';
-- 示例3：查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
-- 示例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
-- 示例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

2.7.2.1.2 非等值连接

非等值连接就是通过限定条件来使多张表关联，比如说一张表中某字段在另一张表的某两个字段的范围之内。

-- 示例1：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
-- 示例2：查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

2.7.2.1.3 自连接

-- 示例1：查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;
-- 示例2：查询姓名中包含字符k的员工的名字、上级的名字（添加筛选）
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

2.7.2.2 外连接

外连接主要用于查询一个表中，而另外一个表中没有的记录。特点如下：

1、外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示null
- 外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表；右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

-- 语法
select 查询列表 from 主表 别名1 LEFT|RIGHT|FULL [OUTER] JOIN 从表 别名2 ON 连接条件;
-- 示例：左外连接
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`;

如下图：

如果要查找两张表的交集，那么只需要使用内连接即可，SQL语句如下：

SELECT * FROM beauty JOIN boys ON beauty.boyfriend_id=boys.id;

找出了交集，如下图所示：

2.7.2.2.1 左外连接

但我们需要找出主表中所有记录，如果有“男朋友”则显示出来，如果没有则以null值表示，那么主表中有而从表中没有的查询就需要用到外连接，这里要查询的所有记录在主表中用左外连接，SQL语句如下：

-- 示例：查询男朋友不在男神表的的女神名
SELECT * FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id;

结果如下图：

2.7.2.2.2 右外连接

右外连接和左外连接基本一样，不过主表换成了右边，从表变成了左边而已，现在让beauty为从表，boys为主表，产生了新的需求，查询女朋友不在女神表中的男神名（注：提供的SQL语句中表不满足需要，所以需要添加两条测试记录，如下图）

然后写SQL语句进行查询：

SELECT * FROM beauty RIGHT JOIN boys ON beauty.boyfriend_id=boys.id;

结果如下：

2.7.2.2.3 全外连接

全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的，所以它的SQL语句为：

-- mysql不支持下面的全外连接，所以会报错
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;

mysql 不支持直接写full outer join 或者 full join来表示全外连接但是可以用left right union right 代替。

但可以用下面的语句来间接达到全外连接的效果：

SELECT * FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id
UNION
SELECT * FROM beauty RIGHT JOIN boys ON beauty.boyfriend_id=boys.id;

如果要查询表1中有而表2中没有的以及表2中有而表1中没有的部分，则可以使用下面的SQL语句：

SELECT * FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id WHERE boys.id IS NULL# 这里要是从表的字段为NULL，左外连接的从表是LEFT JOIN后面的表
UNION # 连接两个查询结果集
SELECT * FROM beauty RIGHT JOIN boys ON beauty.boyfriend_id=boys.id WHERE beauty.boyfriend_id IS NULL;# 这里要是从表的字段为NULL，右外连接的从表是RIGHT JOIN前面的表

2.7.2.3 内外连接总结

左外连接

-- SQL语句如下：
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 左外连接中左边的是主表，右边的是从表

右外连接

-- SQL语句如下：
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 右外连接中右边的是主表，左边的是从表

内连接

-- SQL语句如下：
SELECT 查询列表 FROM A INNER JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 内连接求的是多张表的交集部分

左外连接去除交集

SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;# 将从表B的连接条件作为NULL值判断

右外连接去除交集

SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;# 将从表A的连接条件作为NULL值判断

全外连接

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key;# 全外连接就是求并集

全外连接去除交集

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL;

实例如下：

-- 左外连接
select * from beauty left join boys on beauty.boyfriend_id=boys.id;
-- 右外连接
select * from beauty right join boys on beauty.boyfriend_id=boys.id;
-- 内连接
select * from beauty inner join boys on beauty.boyfriend_id=boys.id;
-- 左外连接去除交集
select * from beauty left join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL;
-- 右外连接去除交集
select * from beauty right join boys on beauty.boyfriend_id=boys.id where beauty.boyfriend_id IS NULL;
-- 全外连接(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id;
-- 全外连接去除交集(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL OR beauty.boyfriend_id IS NULL;

2.7.3 sql92和sql99标准的比较

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

2.8 子查询

子查询就是出现在其他语句中的select语句，就称之为子查询或内查询，而外部的查询语句称之为主查询或外查询。

子查询按照结果集的行列数不同，可以分为如下几类：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集只有一行多列）

表子查询（结果集一般为多行多列）

一般来说，还可以按照子查询出现的位置进行划分：

在select后面：仅仅支持标量子查询
在from后面：支持表子查询
在where或having后面：标量子查询、列子查询、表子查询（注：最为重要，理应掌握）
在exists后面：表子查询

2.9.1 where或having后面

特点：

①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符（< >= <= = <>）使用；列子查询，一般搭配着多行操作符（in、any/some、all）使用
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

2.9.1.1 标量子查询

标量子查询即查询结果只有一行一列的结果集。看下面的案例：

-- 示例1：谁的工资比 Abel 高?
# 第一步：查询Abel的工资
select salary 
from employees 
where last_name='Abel';
# 第二步：查询员工信息，满足salary>Abel的工资
select *
from employees
where salary >(
    select salary 
    from employees 
    where last_name='Abel'  
);

-- 示例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
# 第一步：查询141号员工的job_id
select job_id
from employees
where job_id=141;
# 第二步：查询143号员工的salary
select salary
from employees
where job_id=143;
# 第三步：将第一步和第二步的结果作为子查询
select job_id,salary
from employees
where job_id=(
    select job_id
    from employees
    where job_id=141    
)
and salary>(
    select salary
    from employees
    where job_id=143
);

-- 示例3：返回公司工资最少的员工的last_name,job_id和salary
# 第一步：查询公司的最低工资
select MIN(salary)
from employees;
# 第二步：查询工资等于第一步结果的员工的last_name、job_id和salary
select last_name,job_id,salary
from employees
where salary=(
    select MIN(salary)
    from employees
);

-- 示例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
# 第一步：查询每个部门的最低工资
select MIN(salary),department_id
from employees
group by department_id;
# 第二步：查询50号部门的最低工资
select MIN(salary)
from employees
where department_id=50;
# 第三步：找出第一步结果中比第二步结果中大的记录
select MIN(salary),department_id
from employees
group by department_id
having MIN(salary)>(
    select MIN(salary)
    from employees
    where department_id=50
);

但要注意，如果子查询的结果是列表（即不是一行一列的情况），那么是非法使用标量子查询。例如下面的情况：

select MIN(salary),department_id
from employees
group by department_id
having MIN(salary)>(
    select salary
    from employees
    where department_id=50
);

2.9.1.2 列子查询

列子查询即子查询的结果为一列多行结果集。案例如下：

-- 示例1：返回location_id是1400或1700的部门中的所有员工姓名
# 第一步：查询location_id是1400或1700的部门id
select department_id
from employees
where location_id=1400 or location_id=1700;
# 第二步：查询员工的部门id是否在第一步的结果中，如果在则显示员工姓名
select last_name
from employees
where department_id in (
    select department_id
    from employees
    where location_id=1400 or location_id=1700
);

-- 示例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
# 第一步：查询job_id为'IT_PROG'的工资
select distinct salary
from employees
where job_id='IT_PROG';
# 第二步：查询其它工种中比第一步结果中任一工资低的员工信息
select last_name,employee_id,job_id,salary
from employees
where salary < ANY(
    select distinct salary
    from employees
    where job_id='IT_PROG'
) and job_id!='IT_PROG';

-- 示例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
# 第一步：查询job_id为'IT_PROG'的工资
select distinct salary
from employees
where job_id='IT_PROG';
# 第二步：查询其它工种中比第一步结果中任一工资低的员工信息
select last_name,employee_id,job_id,salary
from employees
where salary < ALL(# 注：和上面的代码仅此处不同
    select distinct salary
    from employees
    where job_id='IT_PROG'
) and job_id!='IT_PROG';

2.9.1.3 行子查询

结果集为一行多列或多行多列的子查询。案例如下：

-- 示例1：查询员工编号最小并且工资最高的员工信息
# 第一步：查询最小的员工编号
select MIN(employee_id)
from employees;
# 第二步：查询最高的工资
select MAX(salary)
from employees;
# 第三步：查询同时满足第一步和第二步结果的记录
select * 
from employees
wwhere employee_id=(
    select MIN(employee_id)
    from employees
)and salary=(
    select MAX(salary)
    from employees
);

2.9.2 select后面

在select后面仅仅支持标量子查询，即结果集为一行一列的子查询。案例如下：

-- 示例1：查询每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 员工个数
FROM departments d;
-- 示例2：查询员工号=102的部门名
SELECT (
    SELECT department_name,e.department_id
    FROM departments d
    INNER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102
    
) 部门名;

2.9.3 from后面

也可以将子查询放在from后面，将子查询结果充当一张表，但是要求必须起别名。案例如下：

-- 案例1：查询每个部门的平均工资的工资等级
# 第一步：查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id;
# 第二步：内连接第一步的结果集和job_grades表，筛选平均工资
select ag_dep.*,g.grade_level
from (
    select avg(salary),department_id
    from employees
    group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;

2.9.4 exists后面

exists是一个关键字，判断是否存在，如果存在则返回1，不存在则返回0，它的基本语法如下：

[NOT] EXISTS(完整的查询语句);
-- NOT是一个可选项，如果加上，表示不存在

案例如下：

-- 示例1：查询有员工的部门名
# 使用IN关键字完成
select department_name
from departments d
where d.department_id in(
	select department_id
	from employees
);
# 使用EXISTS关键字完成
select department_name
from departments d
where EXISTS(
	select *
	from employees e
	where d.department_id=e.department_id
);

2.9.5 IN、EXISTS、ANY、ALL关键字的学习

在前面我们都使用了这些关键字，下面来一个简单的学习。

2.9.5.1 带in关键字的子查询

使用IN关键字，进行子查询时，内层查询语句返回一个数据列，这个数据列中的值，将供外层查询语句进行比较操作。

使用NOT IN关键字与使用IN关键字，查询的结果正好相反。示例：

-- 带in关键字的子查询
select uname
from userinfo,us,sex
where us.uid=userinfo.uid and us.sid=sex.sid
and sex.sid in(
  select sid 
  from sex
  where svalue='男'
);

2.9.5.2 带比较运算符的子查询

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

使用比较运算符的子查询

符号	作用
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

示例如下：

-- 带比较运算符的子查询
select * 
from userinfo,us
where us.uid=userinfo.uid
and sid in (
  select sid
  from us
  where sid=2
);

2.9.5.3 带exists关键字的子查询

使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。

如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。

当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。

与EXISTS关键字刚好相反，使用NOT EXISTS关键字时，当返回的值是true时，外层查询语句不执行查询；当返回值是false时，外层查询语句将执行查询。示例如下：

-- 带exists关键字的子查询
select * from userinfo
where exists (
  select us.uid
  from us,userinfo
  where us.uid=userinfo.uid
  and sid=2
);

2.9.5.4 带any关键字的子查询

ANY关键字表示满足其中任意一个条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。示例如下：

-- 带any关键字的子查询
select * 
from userinfo
where uid < any(# uid<MAX();
  select us.uid 
  from us,userinfo
  where us.uid=userinfo.uid
  and sid=2
);

2.9.5.5 带all关键字的子查询

ALL关键字表示满足所有条件。

使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。示例如下：

-- 带all关键字的子查询
select * 
from userinfo
where uid > all(# 表示大于所有的
  select us.uid 
  from us,userinfo
  where us.uid=userinfo.uid
  and sid=2
);

2.9 分页查询

分页广泛应用于各大网站，如图：

即当要显示的数据一页显示不完时，就需要用到分页查询。

分页查询的基本语法如下：

-- 语法
select 查询列表 from 表 [连接类型 join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选条件 order by 排序字段] LIMIT [offset,] size;
-- offset表示要显示条目的起始索引，从0开始，是一个可选项，如果不写的话就从0开始。
-- size是指要显示多少条记录

注意：limit语句要放在查询语句的最后。

在实际开发中，我们只需要知道当前页码和每页显示条数就可以写SQL从数据库中获取数据记录了。

它们有如下关系：

-- pageIndex 当前页码，页码从1开始
-- pageSize 每页显示条数
select 查询列表 from 表 LIMIT (pageIndex-1)*pageSize,pageSize;

示例如下：

-- 示例1：查询前五条员工信息
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;
-- 示例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;
-- 示例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

注：如果要查找前几名或后几名通常要使用排序。

2.10 union联合查询

联合查询要使用的关键字是UNION，将多条查询语句的结果合并为一个结果。

基本语法如下：

查询语句1
UNION
查询语句2
UNION
...

联合查询主要用于，要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时（注：一张表是id和name字段，那么另外一张表也必须是查询相同类型、相同列数的字段，才能进行整合，联合查询是在行上进行拼接（多出来几行），而不是在列上进行拼接（多出来几列））。

联合查询的特点：

1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

示例如下：

-- 示例1：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;
# 或
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;
-- 示例2：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

3.DML语言的学习

DML是data manipulation language数据操作语言的缩写，主要是数据库的增删改操作。

3.1 插入语句

插入语句有2种方式实现，都需要使用到INSERT关键字。

3.1.1 插入方式一

基本语法如下：

-- 语法
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);

该种方式使用时的注意事项：

1、插入的类型要与列的类型一致，不能一个是int类型一个字符串类型。
2、不可以为null的列必须插入有效的值，可以为null的列可以选择插入null或者不写该列名。
3、列的顺序可以调换，但列名也必须相应的调换。
4、列的个数必须和值的个数一致。
5、可以省略列名，表示默认所有列，而且列的顺序和表中列的顺序一致。

示例如下：

-- 插入一条记录
INSERT INTO beauty(id,name,sex,phone,photo,boyfriend_id) VALUES(15,'张五柳','女','1999-01-02','12345678907',NULL,2);

3.1.2 插入方式二

基本语法如下：

INSERT INTO 表名 SET 列名=值,列名=值,...

示例：

INSERT INTO beauty set id=15,name='张五柳',sex='男';

3.1.3 两种插入方式的比较

第一种插入方式支持插入多行，第二种不支持。

-- 第一种插入方式可以插入多行，直接在后面添加括号插入数据即可
INSERT INTO beauty VALUE(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2),
(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2),
(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

第一种方式支持子查询，第二种不支持。

-- 第一种插入方式支持子查询，即直接在后面添加select查询语句进行插入赋值
INSERT INTO beauty(id,name,sex) select 26,'王桂','男';
INSERT INTO beauty(id,name,sex) select id,boyname,sex from boys where id<3;

3.2 修改语句

修改表记录的语法如下：

-- 修改单表的记录，必须掌握，使用较多
UPDATE 表名 SET 列=新值,列=新值,.. WHERE 筛选条件;

-- 修改多表记录（sql92标准语法）
UPDATE 表1 别名1,表2 别名2 SET 列=值,.. WHERE 连接条件 AND 筛选条件;
-- 修改多表记录（sql99标准语法）
UPDATE 表1 别名1 INNER|LEFT|RIGHT JOIN 表2 别名2 ON 连接条件 SET 列=值,... WHERE 筛选条件;

所以示例如下：

-- 修改单表的记录示例1：修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty SET phone = '13899888899'
WHERE name LIKE '唐%';
-- 修改单表的记录示例2：修改boys表中id好为2的名称为张飞，魅力值 10
UPDATE boys SET boyname='张飞',usercp=10 WHERE id=2;

-- 修改多表的记录示例1：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

3.3 删除语句

删除也有两种方式：delete和truncate。

3.3.1 删除方式一：delete

使用delete删除表记录的语法如下：

-- 单表删除，重点掌握
DELETE FROM 表名 WHERE 筛选条件

-- 多表的删除（sql92语法）
DELETE 表1的别名,表2的别名 FROM 表1 别名，表2 别名 WHERE 连接条件 AND 筛选条件;
-- 多表的删除（sql99语法）
DELETE 表1的别名,表2的别名 FROM 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件;

示例如下：

-- 单表的删除的示例1：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';

-- 多表的删除的示例1：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
-- 多表的删除的示例2：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

3.3.2 删除方式二：truncate

该种删除方式的语法如下：

TRUNCATE TABLE 表名;# 不跟任何筛选条件

示例如下：

-- 删除的示例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;# 不能添加筛选条件，直接清空表

3.3.3 两种删除方式的比较

	delete删除方式	truncate删除方式
是否可以添加where筛选条件	可以	不可以
效率	高	低
自增列变化	假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始	而truncate删除后，再插入数据，自增长列的值从1开始
是否有返回值	有，返回受影响行数	没有
是否能回滚事务	能回滚事务	不能回滚事务

4.DDL语言的学习

DDL是data defination language数据定义语言的缩写简称，主要负责建库、建表、修改表字段等。

4.1 库的管理

所谓库的管理就是创建数据库、修改数据库、删除数据库等。

4.1.1 创建数据库

创建数据库的语法：

CRATE DATABASE [IF NOT EXISTS] 数据库名;
-- 注：
-- IF NOT EXISTS是可选项，但推荐使用，意思是如果不存在该数据库才创建数据库，存在则不创建。
-- 例如：
create database IF NOT EXISTS users;# 创建用户表

4.1.2 修改数据库

修改数据库可以修改数据库名字，可以修改字符集。语法如下：

-- 对数据库重命名
RENAME DATABASE 数据库旧名 TO 数据库新名;
-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集名;

示例：

-- 对users数据库重命名
RENAME DATABASE users TO newusers;
-- 修改数据库的字符集
ALTER DATABASE users CHARACTER SET gbk;

4.1.3 删除数据库

删除数据库即删除整个仓库。语法如下：

DROP DATABASE [IF EXISTS] 数据库名;
-- 注：
-- IF EXISTS是一个可选项，表示如果存在该数据库才删除，不存在则不删除，避免报错。

4.2 表的管理

所谓表的管理就是对数据库表的创建、修改和删除操作。

4.2.1 创建表

创建数据库表的语法如下：

CRATE TABLE 表名(
	列名 列的类型[(长度)约束],
	列名 列的类型[(长度)约束],
	列名 列的类型[(长度)约束],
	...
	列名 列的类型[(长度)约束
);
-- 注：
-- 约束是可选项，不一定要填写
-- 最后一列的后面不需要添加逗号，其他每一列都需要添加逗号

所以示例如下：

CREATE TABLE books(
	id INT,# 编号
	book_name VARCHAR(20),# 图书名字
	book_price DOUBLE,# 图书价格
	author_id INT,# 图书作者编号
	publish_date DATETIME# 出版日期
);

4.2.2 修改表

修改表的基本语法如下：

ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 [列类型 约束];

4.2.2.1 修改列名

-- 语法
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 [列类型 约束];
-- 示例
ALTER TABLE book CHANGE COLUMN publish_date p_date DATETIME;

4.2.2.2 修改列的类型或约束

-- 语法
ALTER TABLE 表名 MODIFY COLUMN 列名 [列类型 约束];
-- 示例
ALTER TABLE books MODIFY COLUMN p_date TIMESTAMP;

4.2.2.3 添加新列

-- 语法
ALTER TABLE 表名 ADD COLUMN 列名 [列类型 约束];
-- 示例
ALTER TABLE books ADD COLUMN annual DOUBLE;

4.2.2.4 删除列

-- 语法
ALTER TABLE 表名 DROP COLUMN 列名;
-- 示例
ALTER TABLE books DROP COLUMN  annual;

4.2.2.5 修改表名

-- 语法
ALTER TABLE 旧表名 RENAME TO 新表名;
-- 示例
ALTER TABLE books RENAME TO book_author;

4.2.3 删除表

前面学过的delete和truncate都是删除表中的记录，而像下面的代码：

delete from 表名;
# 或
truncate from 表名;

其实本质上都是删除表中的记录（即只删除数据），上面的SQL语句是清空表中的记录，而下面的SQL语句是在数据库中删除这张表，直接删除数据+结构。

-- 语法
DROP TABLE [IF EXISTS] 表名;
-- 示例
DROP TABLE IF EXISTS book_author;

4.2.4 复制表

所谓复制表可以选择复制表的结构和数据。

4.2.4.1 仅仅复制表的结构

只复制原表的结构到新表中。

-- 语法
CREATE TABLE 新表名 LIKE 原表名;
-- 示例
CREATE TABLE copy LIKE author;

4.2.4.2 复制表的结构和数据

把原表的结构和数据都复制到新表中，即使用select查询语句查询原表数据和结构赋值到新表中。

-- 语法
CREATE TABLE 新表名 SELECT * FROM 原表名 [筛选条件];
-- 示例1：复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;
-- 示例2：只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';
-- 示例3：仅仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

4.3 数据类型

数据库表中每个字段都在MySQL中有对应的数据类型。

常见的数据类型分为三大类：数值型、字符型和日期型。

其中数值型又可以分为整型和小数等。

注：选择数据类型的原则是所选择的类型越简单越好，能保存数值的类型越小越好。

4.3.1 数值型

数值型又可以分为整型和小数型，小数型又可以分为定点数和浮点数。

整数类型	字节数	无符号( unsigned ) 范围	有符号( signed ) 范围( 默认）	用途
TINYINT	1	0~255	-128~127	小整数值
SMALLINT	2	0~65535	-32768~32767	大整数值
MEDIUMINT	3	0~16777215	-8388608~8388607	大整数值
INT	4	0~4294967295	-2147483648~2147483647	大整数值
INTEGER	4	0~4294967295	-2147483648~2147483647	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值

4.3.1.1 整型

常见的整型有：tinyint、smallint、mediumint、int/integer、bigint等。

它们的特点是：

① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配ZEROFILL使用！

示例如下：

-- 设置无符号和有符号的整数
CREATE TABLE t1(
	id1 INT, # 有符号整数
	id2 INT UNSIGNED # 无符号整数
)
INSERT INTO t1 VALUES(-1234,123456);
-- 设置整型长度，并搭配ZEROFILL
CREATE TABLE t2(
	id1 INT(7) ZEROFILL# 如果输入的整数不足7位，那么高位会用0填满7位
)
INSERT INTO t2 VALUES(123);

4.3.1.2 小数

小数在MySQL中分为定点数和浮点数，其中浮点数由于精度关系又分为float和double。如下所示：

小数
- 浮点型
  - float(M,D)
  - double(M,D)
- 定点型
  - decimal(M,D)

小数的特点是：

M表示整数部位+小数部位，D表示小数部位，如果超过范围则插入临界值。
M和D都可以省略，如果是decimal类型，那么M默认位10，D默认位0；如果是float和double类型，则会根据插入的数值的精度来决定精度。
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用。

示例如下：

CREATE TABLE tab_float(
	f1 FLOAT,# float类型
	f2 DOUBLE,# double类型
	f3 DECIMAL# decimal类型，它们的M和D都是省略的
);
INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);

4.3.2 字符型

字符型数据类型可以大概分为：

较短的文本
- char
- varchar
较长的文本
- text
- blob（较大的二进制）
其他
- binary和varbinary用于保存较短的二进制
- enum用于保存枚举
- set用于保存集合

char和varchar的特点如下：

	char	varchar
写法	char(M)	varchar(M)
M的含义	最大的字符数，可以省略，默认为1	最大的字符数，不可以省略
特点	固定长度的字符	可变长度的字符
空间的耗费	比较耗费，因为分配了指定长度的空间，如果赋予的值没有占满，那么其他空间浪费了	比较节省
效率	高	低

注意下enum和set的区别：

-- enum数据类型的使用：可以放一个列表，但插入时只能选择列表中的一个值
CREATE TABLE tab_char(
	c1 ENUM('a','b','c');
)
INSERT INTO tab_char VALUES('a');# 那么插入值只能在枚举类型中给定的值中选择一个，要么是'a'，要么是'b'，要么是'c'，只能选择一个，如果值不是其中的一个也不能插入成功。

-- set数据类型的使用：也可以放一个列表，但插入时可以选择列表中多个值，用英文逗号分隔，不区分大小写
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');# 可以选择多个值
INSERT INTO tab_set VALUES('a,c,d');

4.3.3 日期型

日期数据类型可以分为：

date ：只保存日期（即年月日）
time：只保存时间（即时分秒）
year：只保存年
datetime：保存日期+时间（即年月日+时分秒）
timestamp：保存日期+时间（即年月日+时分秒）

datetime和timestamp的区别：

	datetime	timestamp
字节	8	4
范围	1000——9999	1970——2038
时区的影响	不受影响	受影响

示例：

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);

4.4 常见约束

4.4.1 约束概述

4.4.1.1 约束定义

约束是数据库中一种用来限制表中数据的机制，是为了确保表中数据的准确性和可靠性。

4.4.1.2 约束的分类

约束按照内容可以分为六类：

NOT NULL：非空约束，要求该字段的值不能为空，必须有值，如用户名、密码等。
DEFAULT：默认约束，要求该字段有一个默认值，比如用户状态默认为1表示账户可以使用。
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空，一般表的id号作为主键并且自动增长。
UNIQUE：唯一约束，表示该字段具有唯一性，可以为空。
CHECK：检查约束，检查该字段的值，但MySQL中不支持，比如检查性别只能是'男'或'女'等。
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键约束，用于引用主表的某列的值。比如学生表的专业编号就要引用主表专业表的主键，员工表的部门编号等都是外键。

4.4.1.3 其他

一般来说添加约束是在创建表或者修改表的时候，在表中有数据后就不再添加约束了。

约束按照添加的位置可以分为两类：

列级约束：六大约束语法上都支持，但外键约束即使添加了也不会生效。
表级约束，除了非空约束、默认约束，其他的约束都支持。

主键和唯一约束有些类似，但也有区别：

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

关于外键的使用如下：

1、要求在从表（即使用了其他表主键的表，比如学生表中的专业编号就可以设置为外键，但学生表是从表而专业表是主表）设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

创建约束的基本语法如下：

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

4.4.2 创建表时添加约束

4.4.2.1 添加列级约束

在创建表时添加列级约束的语法如下：

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

直接在字段名和字段类型后面追加约束类型即可。

注意：列级约束实际上只支持默认、非空、主键和唯一约束，其他检查和外键约束语法上支持，但实际上写了也不会生效。

示例：

--　示例：创建一张学生表并添加列级约束
CRATE TABLE students(
    id int PRIMARY KEY,# 主键约束
    name varchar NOT NULL,# 非空约束
    gender char(1) CHECK(gender='男' OR gender='女'),# 检查约束，但不会生效，其中CHECK()括号中可以写where后面支持的筛选条件
    seat int UNIQUE,# 唯一约束
    age int DEFAULT 18,# 默认约束，直接将默认值添加在后面即可
    major_id int REFERENCES major(id)#外键约束，其中major_id是从表中的字段，REFERENCES是关键字，major()是主表名称加括号，括号内的id是主表major中的主键id
);

可以通过下面这个命令来查询表中所有的索引，包括主键、外键、唯一等。

SHOW INDEX FROM 表名;

4.4.2.2 添加表级约束

添加表级约束的语法如下：

CREATE TABLE 表名(
    字段名 字段类型,
    字段名 字段类型,
    ...
    [CONSTRAINT 约束名] 约束类型(字段名);
    [CONSTRAINT 约束名] 约束类型(字段名);
    [CONSTRAINT 约束名] 约束类型(字段名);
    ....
);
-- 注：
-- constraint的英文含义有“约束”、“限制”的含义。
-- [CONSTRAINT 约束名]是一个可选项，可以写也可以不写
-- 约束类型就是上面提到的PRIMARY、UNIQUE、DEFAULT、CHECK等
-- 括号内的字段名是当前表中的字段

示例如下：

--　示例：创建一张学生表并添加表级约束
CRATE TABLE students(
	id int,
	name varchar,
	gender char(1),
	seat int,
	age in,# 默认约束，直接将默认值添加在后面即可
	major_id,
	-- 添加表级约束
	CONSTRAINT pk PRIMARY KEY(id),# 主键约束
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 检查约束，但不会生效，其中CHECK()括号中可以写where后面支持的筛选条件
	CONSTRAINT uq UNIQUE(seat),# 唯一约束
	CONSTRAINT fk_students_major FOREIGN KEY(major_id) REFERENCES major(id)# #外键约束，其中CONSTRAINT是关键字，fk_students_major是外键名，可以任意书写，但一般规范是fk+从表名+主表名，以便于阅读，FOREIGN KEY()是外键约束名，major_id是从表中的字段，REFERENCES是关键字，major()是主表名称加括号，括号内的id是主表major中的主键id
);

但一般来说，通用写法是列级约束和表级约束一起使用，如下所示：

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

4.4.3 修改表时添加约束

修改表时添加约束的语法如下：

-- 添加列级约束
ALTER TABLE 表名 MOBIFY COLUMN 字段名 字段类型 新约束;

-- 添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） [外键的引用];

示例，有如下一张表，分别为其添加约束：

-- 一张没有添加过任何约束的表
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
)
DESC stuinfo;
-- 1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 直接修改字段即可
-- 2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
-- 3.添加主键约束
-- 3.1列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
-- 3.2表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
-- 4.添加唯一约束
-- 4.1 列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
-- 4.2 表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
-- 5.添加外键约束
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

4.4.4 修改表时删除约束

即将字段修改为没有约束的字段，就是删除约束。如下示例：

-- 1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

-- 2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

-- 3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

-- 4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

-- 5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

4.5 自增长列

自增长列又被称为标识列，可以不用手动插入值，而是系统提供默认增长值。

标识列有几个值得注意的地方：

1、标识列必须和主键搭配吗？不一定，但要求是一个key（主键或唯一约束）
2、一个表可以有几个标识列？至多一个（所以一般和主键搭配使用）！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长，可以通过手动插入值，设置起始值

创建自增长列的语法如下：

CRATE TABLE 表名(
	字段名 字段类型 [UNIQUE|PRIMARY KEY] AUTO_INCREMENT# 注：只能有一个自增长列，必须和UNIQUE或者PRIMARY KEY搭配使用，在它们后面添加AUTO_INCREMENT关键字即可，一般在创建表时指定标识列。
)

关于标识列的步长可以通过下面的语句查看：

-- 查看auto_increment变量的值
SHOW VARIABLES LIKE '%auto_increment%';

5.TCL语言的学习

5.1 概述

TCL是Transaction Control Language事务控制语言。

事务就是一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

比如说张三向李四转账1000块钱，要么转账成功要么转账不成功，不会存在张三转账了但李四没有收到的情况，只有两种可能：成功或失败。

事务的特性：ACID

原子性（Atomicity）：一个事务不可再分割，要么都执行要么都不执行。
一致性（Consistency）：一个事务执行会使数据从一个一致状态切换到另外一个一致状态。比如说转账之前两个的金额总数是2000，那么转账后还应该是2000。
隔离性（Isolation）：一个事务的执行不受其他事务的干扰。
持久性（Durability）：一个事务一旦提交，则会永久的改变数据库的数据。

可以通过show engines来查看mysql支持的存储引擎。

在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务。

5.2 事务的创建

事务的创建可以分为隐式事务和显式事务。

隐式事务：事务没有明显的开启和结束标记，比如insert、update和delete语句。

显式事务：事务具有明显的开启和结束标记。注意，必须先设置自动提交功能为禁用（set autocommit=0;），基本语法为：

-- 步骤1：开启事务
SET autocommit=0;# 设置自动提交功能禁用
START TRANSACTION;# 可选项，可以写也可以不写
-- 步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
-- 步骤3：结束事务
COMMIT;# 提交事务
ROLLBACK;# 回滚事务

示例：

-- 1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#commit;

-- 2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;# delete可以事务回滚，而truncate不能
ROLLBACK;

-- 3.演示savepoint的使用，savepoint就是回滚到指定处
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

5.3 数据库的隔离级别

数据库的隔离级别是在同时运行多个事务的时候才会讨论的，如果只是运行一个事务，那么没有讨论的必要，其实有点类似于保证多线程的安全性。

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：

脏读：对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
不可重复读：对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
幻读：对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行。

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响, 避免各种并发问题。

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

关于脏读、不可重复读和幻读的演示请参考博客：https://blog.csdn.net/cnds123321/article/details/116567151

数据库提供的4种事务隔离级别：

Oracle 支持的 2 种事务隔离级别：READ COMMITED，SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED。

Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ。

每启动一个 mysql 程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。

查看当前的隔离级别:

SELECT @@tx_isolation;

设置当前 mySQL 连接的隔离级别:

set transaction isolation level read committed;

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

set global transaction isolation level read committed;

6.其他

6.1 视图

6.1.1 视图概述

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

视图通常应用在：

多个地方用到了同样的查询结果，就可以使用视图来进行封装。
该查询结果使用的SQL语句较为复杂，那么就可以封装为视图，下次调用的SQL语句就较为简单了。

视图的好处：

可以重用SQL语句。
简化复杂的SQL操作，不必知道它的查询细节，相当于Java中封装的函数。
保护数据，提高安全性，比如用户表可以给第三方只提供用户名和手机号信息而其他更为详细的信息如密码、家庭地址就不提供，那么就保证了数据的安全使用。

视图和表的区别：

	视图	表
创建语法的关键字	CREATE VIEW	CREATE TABLE
是否实际占用物理空间	只是保存了SQL逻辑	保存了数据
使用	能增删改查，但一般不能增删改（因为有太多限制条件）	能自由增删改查

简单的示例：

-- 示例：查询姓张的学生名和专业名
-- 未使用视图的SQL语句
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';

-- 使用视图的SQL语句
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
SELECT * FROM v1 WHERE stuname LIKE '张%';

6.1.2 创建视图

创建视图的基本语法为：

CREATE VIEW 视图名
AS
查询语句;

示例如下：

-- 查询姓名中包含a字符的员工名、部门名和工种信息
-- 第一步：创建视图myv1
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;
-- 第二步：调用使用视图
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

6.1.3 修改视图

所谓的修改视图就是修改视图的查询语句，所以修改视图的基本语法为：

-- 修改视图方式一
CREATE OR REPLACE VIEW 视图名
AS
查询语句;

-- 修改视图方式二
ALTER VIEW 视图名
AS
查询语句;

示例如下：

-- 修改视图方式一的示例：
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

-- 修改视图方式二的示例：
ALTER VIEW myv3
AS
SELECT * FROM employees;

6.1.4 删除视图

删除视图和删除表一样，都是使用DROP关键字，它的语法如下：

-- 语法
DROP VIEW 视图名,视图名,...
-- 示例
drop view myv1,myv2,myv3;

6.1.5 查看视图

这里查看视图是查看视图的结构，而不是查询视图。

-- 语法
DESC 视图名;# 查看视图的结构
SHOW CREATE VIEW 视图名;# 查看视图的创建过程

6.1.6 更新视图

这些所谓的更新视图就是对视图进行增删改操作。

它们的插入修改和删除同普通表的增删改一样，例如：

-- 插入
INSERT INTO 视图名(列1,列2,...) VALUES(值1,值2,..);
-- 修改
UPDATE 视图名 SET 字段=新值,... WHERE 筛选条件;
-- 删除
DELETE FROM 视图名 WHERE 筛选条件;

但注意，具有以下特点的视图不能进行更新，所以一般不对视图进行增删改操作，仅仅查询。

包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

6.2 变量

在MySQL中变量分为系统变量和自定义变量，它们又有划分：

系统变量
- 全局变量
- 会话变量
自定义变量
- 用户变量
- 局部变量

6.2.1 系统变量

系统变量就是由MySQL系统提供的变量，不属于用户定义，属于服务器层面。

注意：全局变量需要添加GLOBAL关键字，会话变量需要添加SESSION关键字，而如果不写关键字，那么默认是会话级变量。

系统变量使用的基本语法如下：

-- 查看所有系统变量
SHOW GLOBAL|[SESSION] VARIABLES;# GLOBAL表示查看系统全局变量，SESSION表示查看系统会话变量，[SESSION]是一个可选项，如果不写则默认是会话变量
-- 查看满足部分条件的系统变量
SHOW GLOBAL|[SESSION] VARIABLES LIKE '%匹配字符%';# LIKE '%char'的语法与MySQL模糊查询语法一致，使用通配符匹配
-- 查看指定的系统变量的值
SELECT @@GLOBAL|[SESSION].系统变量名;# 例如：select @@global.hostname;#查看主机名
-- 为某个系统变量赋值
# 方式一
SET GLOBAL|[SESSION] 系统变量名=值;
# 方式二
SET @@GLOBAL|[SESSION].系统变量名=值;# 例如：set @@global.autocommit=1;

6.2.1.1 全局变量

全局变量的作用域是针对于所有会话（连接）有效，但不能跨重启（即重启数据库服务）。

所谓的会话就是一次连接，如图所示：

全局变量的常用语法如下：

-- 查看所有全局变量
SHOW GLOABL VARIABLES;# 必须写GLOBAL，表示全局
-- 查看满足条件的部分全局变量
SHOW GLOBAL VARIABLES LIKE '%匹配字符%';
-- 查看指定全局变量的值
SELECT @@GLOBAL.全局变量名;# 也可以添加上GLOBAL
-- 为某个全局变量赋值
SET @@GLOBAL.全局变量名='新值';
SET GLOBAL 全局变量名='新值';

6.2.1.2 会话变量

会话变量只针对于当前会话（连接）有效。会话变量的常用语法为：

-- 查看所有会话变量
SHOW [SESSION] VARIABLES;# 即使不写session，默认也是会话级变量
-- 查看满足条件的部分会话级变量
SHOW [SESSION] VARIABLES LIKE '%匹配字符%';
-- 查看指定会话变量的值
SELECT @@会话级变量名;# 默认就是会话级变量
SELECT @@SESSION.会话级变量名;# 也可以添加上SESSION
-- 为某个会话变量赋值
SET @@SESSION.会话级变量名='新值';
SET SESSION 会话级变量名='新值';

6.2.2 自定义变量

自定义变量顾名思义就是用户自己定义的变量，而不是由系统提供的。

使用自定义变量需要先声明再赋值，最后才能使用（查看、比较、运算等）。

6.2.2.1 用户变量

用户变量只针对于当前会话（连接）有效，作用域等同于会话变量。

用户变量的使用语法如下：

-- 第一步：声明并初始化用户变量，有下面三种方式
SET @变量名=值;# 方式一
SET @变量名:=值;# 方式二
SELECT @变量名:=值;# 方式三
-- 第二步：赋值（也可以更新变量的值），有两种方式
# 方式一（下面这三种都可以为变量赋值）
SET @变量名=值;# 方式一，也使用较多
SET @变量名:=值;# 方式一
SELECT @变量名:=值;# 方式一
# 方式二
SELECT 字段 INTO @变量名 FROM 表名;# 注，也使用较多
-- 第三步：使用（查看变量的值）
SELECT @变量名;

简单的示例演示下用户变量的使用：

-- 示例：声明两个变量，求和并打印
SET @n1=5;
SET @n2=10;
SET @sum=@n1+@n2;
SELECT @sum;

6.2.2.2 局部变量

局部变量仅仅作用在定义它的BEGIN END块中有效，并且要用在BEGIN END块中的第一句话。

它的使用语法如谢谢：

-- 第一步：声明局部变量
DECLARE 变量名 数据类型;
DECLARE 变量名 数据类型 [DEFAULT 值];# 可以添加默认约束
-- 第二步：赋值（更新变量的值）
# 方式一（下面三种都能赋值）
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
# 方式二
SELECT 字段 INTO 局部变量名 FROM 表名;
-- 第三步：使用（查看变量的值）
SELECT 局部变量名;

有一个简单的示例:

-- 示例：声明两个变量，求和并打印
DECLARE n1 INT DEFAULT 10;
DECLARE n2 INT;
SET n2=5;
DECLARE sum INT;
SET sum=n1+n2;
SELECT sum;

6.2.2.3 用户变量和全局变量的比较

	用户变量	全局变量
作用域	当前会话	定义它的`BEGIN END`块中
定义位置	会话的任何地方	`BEGIN END`块的第一句话
语法	加@符号	一般不用加@，需要指定类型
示例	SET @n1=5;	DECLARE n1 INT DEFAULT 5;

6.3 存储过程

存储过程和函数都类似于Java中定义的方法，可以提高代码的复用性，简化操作。

所谓的存储过程就是一组预先编译好的SQL语句的集合。

6.3.1 创建存储过程

创建存储过程的语法如下：

-- 语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN -- BEGIN和END相当于Java中的大括号{}
	存储过程体 -- 是一组合法的SQL语句
END

注意：

1、参数列表包含三部分，参数列表的语法如下：

-- 语法
参数模式 参数名 参数类型
-- 示例
IN name varchar(20)

参数名和参数类型很容易理解，参数模式是由MySQL系统规定的三种模式，它们分别是：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、存储过程体的注意事项：

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置。

重新设置存储过程结尾的结束标记的语法：

-- 语法
DELIMITER 结束标记 -- 结束标记可以是任意字符
-- 示例
DELIMITER $

6.3.2 调用存储过程

调用存储过程需要使用到关键字CALL，它的语法为：

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

6.3.3 存储过程实例

6.3.3.1 空参列表

即不传入参数。

-- 示例1：插入到admin表中五条记录
DELIMITER $# 指定结束标记
CREATE PROCEDURE mypro1()# 空参
BEGIN# 相当于Java方法中大括号{}
	INSERT INTO admin(username,password) VALUES('zs1','123456'),('zs2','123456'),('zs3','123456'),('zs4','123456'),('zs5','123456');
END $# 因为在存储过程体中每条SQL语句使用了分号结束语句，但存储过程什么时候结束系统并不知道，所以需要在END后面插入一个结束标记表示该存储过程已经完了

CALL mypro1()$ # 调用存储过程，注意：在调用的时候如果是在Navicat For MySQL软件上那么结束标记要写分号";"，否则报错，如果是在漆黑的DOS窗口中，那么就要使用定义的结束标记"$"否则不会结束
DELIMITER ;# 为了符合平时的习惯，还是改回来在创建存储过程完成后

6.3.3.2 创建带in模式参数的存储过程

所谓的带IN模式参数，就是传入参数，可以在参数列表设置形参。

传递一个带IN模式的参数示例如下：

-- 示例1：创建存储过程实现，根据女神名，查询对应的男神信息
DELIMITER $# 指定结束标记，由于我们恢复了分号的结束标记，所以需要重新设置
CREATE PROCEDURE mypro2(IN beautyName VARCHAR(20))# 第一个IN表示该参数是IN模式，beautyName是参数名称，VARCHAR(20)是参数类型
BEGIN
	SELECT *
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END $

CALL mypro2()$
DELIMITER ;# 为了符合平时的习惯，还是改回来在创建存储过程完成后

传递两个带IN模式参数的示例如下：

-- 示例2：创建存储过程实现，用户是否登录成功
DELIMITER $# 指定结束标记，由于我们恢复了分号的结束标记，所以需要重新设置
CREATE PROCEDURE mypro3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;# 声明一个用户变量来保存查询结果
	SELECT COUNT(*) INTO result# 赋值
	FROM admin
	WHERE admin.username=username# 如果重名，那么用表名进行区分
	AND admin.password=password;
	SELECT IF(result>0,'成功','失败');# 使用
END $

CALL mypro3()$# 调用存储过程
DELIMITER ;# 为了符合平时的习惯，还是改回来在创建存储过程完成后

6.3.3.3 创建带out模式参数的存储过程

所谓的out模式参数，就是该参数用来做返回值的。示例如下：

-- 示例1：根据输入的女神名，返回对应的男神名
DELIMITER $# 指定结束标记，由于我们恢复了分号的结束标记，所以需要重新设置
CREATE PROCEDURE mypro4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyName#赋值
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id=bo.id
	WHERE b.name=beautyName;
END$# 相当于原来分号作为结束标记，其实没必要空格的

DELIMITER ;# 这里先将结束标记设置回来，下面就可以直接使用分号作为结束标记了
SET @boyName='';# 创建一个用户变量来存储返回值
CALL mypro4('小昭',@boyName);# 调用存储过程，传入两个参数
SELECT @boyName;# 调用已经被赋值了的用户变量

带多个OUT模式参数的存储过程创建也是一样，不过是多添加几个参数而已，用逗号在参数列表中隔开即可。

6.3.3.4 创建带inout模式的参数的存储过程

所谓的INOUT模式的参数就是该参数既是传入参数，又是返回值。示例如下：

-- 示例1：传入a和b两个值，最终a和b都翻倍并返回
# 创建存储过程
DELIMITER $ -- 指定结束标记，由于我们恢复了分号的结束标记，所以需要重新设置
CREATE PROCEDURE mypro5(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END$

# 调用存储过程
DELIMITER ; -- 这里先将结束标记设置回来，下面就可以直接使用分号作为结束标记了
SET @m=10; -- 创建两个用户变量作为INOUT模式的参数
SET @n=20;
CALL mypro5(@m,@n);
SELECT @m,@n;

6.3.4 删除存储过程

删除存储过程的语法如下：

-- 语法
DROP PROCEDURE 存储过程名;
-- 示例
DROP PROCEDURE mypro1; -- 是正确的
-- 注意：无法删除多个存储过程，所以下面的删除方式是错误的
DROP PROCEDURE mypro1,mypro2; -- 错误的示例

6.3.5 查看存储过程

可以查看存储过程的创建过程，语法如下：

-- 语法
SHOW CREATE PROCEDURE 存储过程名;
-- 示例
SHOW CREATE PROCEDURE mypro1;
-- 注意：由于存储过程不是表，所以无法查看结构，下面的代码是错误的
DESC mypro1; -- 错误的示例

6.4 函数

函数同存储过程一样，都是一组预先编译好的SQL语句的集合，可以理解成批处理语句。

函数与存储过程的区别：

存储过程：可以有0个返回值，也可以有多个返回值，诗号做批量插入、批量更新。
函数：有且只有1个返回值（必须有一个返回值并且只能有一个返回值），适合处理数据后返回一个结果的情况。

6.4.1 创建函数

创建函数的语法如下：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN# 相当于Java方法中的大括号{}
	函数体
END

注意事项：

1、参数列表包括两部分：参数名和参数类型。参数列表的语法如下：

-- 语法
参数名 参数类型
-- 示例
name VARCHAR(20)

2、函数体，一定要有return语句，如果没有就会报错。如果return语句没有放在函数体的最后一行虽然不会报错，但不建议这么做。

3、函数体如果只有一句话，那么就可以省略BEGIN END。

4、使用DELIMITER语句设置结束标记。

5、注意，定义的函数名一定不能和系统已有的函数同名，否则会报错，但根据提示你可以察觉不了是函数名的问题。

6.4.2 调用函数

调用函数的语法如下：

SELECT 函数名(参数列表);

6.4.3 函数实例

6.4.3.1 无参有返回值

-- 示例1：返回公司的员工个数
# 创建函数
DELIMITER $ -- 将"$"符号作为结束标记
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0; -- 定义局部变量，存储员工个数
    SELECT COUNT(*) INTO c -- 赋值
    FROM employees;
    RETURN c;
END$

# 调用函数
DELIMITER ; -- 将分号恢复为结束标记
SELECT myf1();

6.4.3.2 有参有返回值

-- 示例1：根据员工名，返回它的工资
# 创建函数
DELIMITER $ -- 将"$"符号作为结束标记
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @salary=0; -- 定义用户变量，存储员工工资
	SELECT salary INTO @salary -- 赋值
	FROM employees
	WHERE last_name=empName;
	RETURN @salary; -- 返回值
END$

# 调用函数
DELIMITER ; -- 将分号恢复为结束标记
SELECT myf2('张飞');

6.4.4 查看函数

-- 语法
SHOW CREATE FUNCTION 函数名; -- 查看函数的创建过程
-- 示例
SHOW CREATE FUNCTION myf1;

6.4.5 删除函数

-- 语法
DROP FUNCTION 函数名;
-- 示例
DROP FUNCTION myf1;

6.5 流程控制结构

流程控制主要是顺序、分支和循环结构。顺序结构就是按照顺序写SQL语句，自是不必再说明了。

6.5.1 分支结构

可以用来实现分支结构的有IF函数、CASE函数等。

6.5.1.1 IF函数

IF函数使用的基本语法为：

-- 语法
IF(条件表达式, 值1, 值2)
-- 注释：
-- 可以用来实现双分支。如果条件表达式为真则得到值1，否则得到值2
-- 应用在BEGIN END代码块中或外面也可以

示例如下：

-- 示例
SELECT IF(10>5,'大','小');

6.5.1.2 CASE结构

CASE结构分为两种情况，分别对应Java分支结构中的switch...case和if..else if...else。

基本语法如下：

-- 语法：第一种情况，类似于Java中的switch...case
CASE 变量或表达式
    WHEN 值1 THEN 语句1;
    WHEN 值2 THEN 语句2;
    ...
    ELSE 语句n;
END CASE;
-- 注释：应用在BEGIN END代码块中或外面

-- 语法：第二种情况，类似于Java中的if...else if...else
CASE
    WHEN 条件1 THEN 语句1;
    WHEN 条件2 THEN 语句2;
    ...
    ELSE 语句n;
END CASE;
-- 注释：应用在BEGIN END代码块中或外面

示例如下：

-- 示例1：CASE的第一种情况
DELIMITER $
CREATE PROCEDURE mypro2(IN num INT)
BEGIN
	CASE num
		WHEN 1 THEN SELECT '优';
		WHEN 2 THEN SELECT '良';
		WHEN 3 THEN SELECT '中';
		ELSE SELECT '差';
	END CASE;
END $
DELIMITER ;
# 调用存储过程
CALL mypro2(3);

-- 示例2：CASE的第二种情况
DELIMITER $
CREATE PROCEDURE mypro3(IN num INT)
BEGIN
	CASE
		WHEN num<2 THEN SELECT '优';
		WHEN num<3 THEN SELECT '良';
		WHEN num<4 THEN SELECT '中';
		ELSE SELECT '差';
	END CASE;
END $
DELIMITER ;
# 调用存储过程
CALL mypro2(4);

6.5.1.3 IF结构

IF结构和IF函数是不一样的。

基本语法如下：

-- 语法
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
ELSE 语句n;
END IF; -- 表示IF结构结束了
-- 注释：只能用于BEGIN END块中

示例如下：

-- 示例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $
CREATE FUNCTION myfun1(score FLOAT) RETURNS CHAR
BEGIN
    DECLARE res CHAR DEFAULT ''; -- 定义变量来存储结果值
    IF score>90 THEN SET res='A';
    ELSEIF score>80 THEN SET res='B';
    ELSEIF score>60 THEN SET res='C';
    ELSE SET res='D';
    END IF;
    RETURN res;
END $
DELIMITER ; -- 恢复结束标记为分号
SELECT myfun1(89); -- 调用函数

6.5.2 循环结构

在MySQL中循环结构分为三类：while、loop、repeat。

在循环结构也需要用到循环控制，控制循环什么时候结束、什么时候继续。在MySQL中iterate类似于Java中的continue，跳出本次循环，继续下一次的循环；儿leave类似于Java中的break，跳出当前所在的循环。

6.5.2.1 while循环

它的语法如下：

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

类似于Java中的while循环：

while(循环条件){
	循环体;
}

示例如下：

-- 示例：批量插入，根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while1(IN cout INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=count DO
		INSERT INTO admin(username,password) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
END
DELIMITER ;
CALL pro_while1(100);

leave语句和iterate语句的简单使用示例如下：

-- 示例1：添加leave语句。批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $
CALL test_while1(100)$

-- 示例2：添加iterate语句。批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $
CALL test_while1(100)$

6.5.2.2 loop循环

它的语法如下：

-- 语法
[标签:] LOOP
	循环体;
END LOOP [标签];
-- 注释：可以用来模拟简单的死循环

6.5.2.3 repeat循环

它的语法如下：

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

6.6 索引

请查阅MySQL高级教程。

6.7 触发器

所谓的触发器就是在执行DELETE、INSERT或UPDATE语句时自动做一些我们想要完成的事情，比如当添加一名用户时，用户总数量加一。

只有DELTE、INSERT、UPDATE这三种语句能够支持触发器，其他的SQL语句不支持。

6.7.1 创建触发器

在创建触发器时，需要给出4条准确信息：

唯一的触发器名。
触发器关联的表。
触发器应该响应的活动（DELETE、INSERT、UPDATE）。
触发器的执行时机（AFTER、BEFORE）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

触发器的程序主体可以是一条SQL语句，也可以是多条SQL语句。

6.7.1.1 创建只有一条执行语句的触发器

创建只有一条执行语句的触发器语法如下：

-- 语法
CREATE TRIGGER 触发器名 (BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON 关联的表名 FOR EACH ROW 执行语句;
-- 注释
-- (BEFORE|AFTER) 是指触发器执行的时机，是在INSERT|UPDATE|DELETE执行之前还是之后触发，只有两个值可选，AFTER是之后，BEFORE是之前触发
-- (INSERT|UPDATE|DELETE) 是指触发事件，取值只有括号中三种，也只有这三种情况才会启动触发器
-- 关联的表名指的是要在哪张表上建立触发器，只有对该表执行INSERT、UPDATE或DELETE后才会触发触发器，而其他表不会
-- FOR EACH ROW表示对每个行都执行
-- 最后的执行语句可以是只有一条，也可以有多条

所以创建只有一条执行语句的触发器的示例如下：

-- 示例：创建一个在INSERT执行之后触发的触发器，触发器触发后向log表中添加一条日志记录
CREATE TRIGGER trigger_float AFTER INSERT 
ON tab_float FOR EACH ROW
INSERT INTO log(descr) VALUES(CONCAT('触发器激活执行，时间为：',NOW())); -- 触发器激活后打印这么一条语句

其中INSERT INTO log(descr) VALUES(CONCAT('触发器激活执行，时间为：',NOW()))就是触发器程序体，只有一条语句。

在log表中看下效果：

6.7.1.2 创建有多条执行语句的触发器

创建多条执行语句的触发器的语法如下：

-- 语法
DELIMITER $
CREATE TRIGGER 触发器名 (BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON 关联的表名 FOR EACH ROW BEGIN
    执行语句1;
    执行语句2;
    ...
    执行语句n;
END $ -- 作为结束标记
DELIMITER ; -- 恢复分号作为结束标记

示例如下：

-- 示例：创建一个在DELETE执行之后触发的触发器，触发器触发后向log表中添加两条日志记录
DELIMITER $
CREATE TRIGGER trigger_float AFTER DELETE 
ON tab_float FOR EACH ROW
BEGIN
	INSERT INTO log(descr) VALUES(CONCAT('触发器激活执行，时间为：',NOW())); -- 触发器激活后打印这么一条语句
	INSERT INTO log(descr) VALUES(CONCAT('删除了一条记录，时间为：',NOW()));
END $
DELIMITER ;

所以，在trigger_float表中执行DELETE删除语句后，可以在log表中查看到添加的记录，如图：

6.7.2 查看触发器

查看触发器可以查看所有的触发器信息，也可以查看指定的触发器信息。

6.7.2.1 查看所有触发器

语法如下：

-- 语法
SHOW TRIGGERS;

显示的是所有创建的触发器的基本信息，如图：

6.7.2.2 查看指定触发器

也可以查看指定触发器名的触发器信息。

所有触发器信息都存储在information_schema数据库下的triggers表中。

可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

所以语法如下：

-- 语法
SELECT * FROM information_schema.triggers
WHERE trigger_name='触发器名';#只需要指定触发器名，其他都是固定的语法

如图：

6.7.3 删除触发器

删除触发器的语法和删除表、存储过程、函数等的语法格式基本一样，如下所示：

-- 语法
DROP TRIGGER 触发器名;
-- 示例
DROP TRIGGER trigger_float;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

6.8 数据备份与还原

备份数据可以保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份。

注意：不能在DOS里的mysql下或MySQL-front这样的工具下运行下列语句。

6.8.1 数据备份

数据备份的语法如下：

-- 语法
mysqldump -u 用户名 -p 数据库名 [表名 表名 ...] > 文件名.sql#如果不写表名只写数据库名，那么默认备份所有表
-- 示例
mysqldump -u root -p test tb_users > C:\backupdb.sql# 其中root是用户名，test是数据库名，tb_users是test数据库中的表名，C:\backupdb.sql是保存路径名

注意：打开的DOS窗口必须是以管理员身份，不需要进入到mysql里面，即下面所示是错误的。

只能是普通的路径下执行该命令，如：

语句中不可以用分号结尾：

6.8.2 数据还原

数据还原只需要在普通的DOS窗口下执行，不需要进入到mysql里面，它的语法如下：

mysql -u 用户名 -p 数据库名 < 数据库备份文件名.sql