本文共 34416 字，大约阅读时间需要 114 分钟。

创建数据库

CREATE DATABASE IF NOT EXISTS  books

更改库的字符集

ALTER DATABASE books CHARACTER SET	gbk

库的删除

DROP DATABASE IF EXISTS books

表的创建

create table 表名(列名 列的类型  [长度 约束],列名 列的类型  [长度 约束],列名 列的类型  [长度 约束],列名 列的类型  [长度 约束])

create table book(			id int,#编号			bName VARCHAR(20), #图书名			price DOUBLE,#价格			authorId int ,#作者编号			publis datetime #出版日期);

表的修改

-------- alter table 表名 add|drop||MODIFY|CHANGE column 列名

修改列名

ALTER TABLE book CHANGE  COLUMN publis pub  datetime;

修改列的类型或约束

ALTER TABLE book MODIFY COLUMN  pub TIMESTAMP;

添加新列

ALTER TABLE author  ADD COLUMN annual DOUBLE;

删除列

ALTER TABLE suthor DROP COLUMN annual;

修改表名

ALTER TABLE book RENAME TO books;

表的删除

DROP TABLE (IF EXISTS) books;

表的复制

INSERT INTO author  VALUES(1,"鼎信"),(2,"fyh"),(3,"鼎信");添加数据

仅仅复制表的结构

CREATE TABLE copy LIKE author;

复制表的结构+数据

CREATE TABLE copy2 SELECT * FROM author;

常见数据类型

整型：tinyint smallint mediumint int/INTEGER bigint 日期型: date time year datetime  timestamp

进阶1：基础查询

/*语法：select 查询列表 from 表名;类似于：System.out.println(打印东西);特点：1、查询列表可以是：表中的字段、常量值、表达式、函数2、查询的结果是一个虚拟的表格*/USE myemployees;#1.查询表中的单个字段SELECT last_name FROM employees;#2.查询表中的多个字段SELECT last_name,salary,email FROM employees;#3.查询表中的所有字段#方式一：SELECT     `employee_id`,    `first_name`,    `last_name`,    `phone_number`,    `last_name`,    `job_id`,    `phone_number`,    `job_id`,    `salary`,    `commission_pct`,    `manager_id`,    `department_id`,    `hiredate` FROM    employees ;#方式二：   SELECT * FROM employees;  #4.查询常量值 SELECT 100; SELECT 'john';  #5.查询表达式 SELECT 100%98;  #6.查询函数  SELECT VERSION();   #7.起别名 /* ①便于理解 ②如果要查询的字段有重名的情况，使用别名可以区分开来  */ #方式一：使用asSELECT 100%98 AS 结果;SELECT last_name AS 姓,first_name AS 名 FROM employees;#方式二：使用空格SELECT last_name 姓,first_name 名 FROM employees;#案例：查询salary，显示结果为 out putSELECT salary AS "out put" FROM employees;#8.去重#案例：查询员工表中涉及到的所有的部门编号SELECT DISTINCT department_id FROM employees;#9.+号的作用/*java中的+号：①运算符，两个操作数都为数值型②连接符，只要有一个操作数为字符串mysql中的+号：仅仅只有一个功能：运算符select 100+90; 两个操作数都为数值型，则做加法运算select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型			如果转换成功，则继续做加法运算select 'john'+90;	如果转换失败，则将字符型数值转换成0select null+10; 只要其中一方为null，则结果肯定为null*/#案例：查询员工名和姓连接成一个字段，并显示为 姓名SELECT CONCAT('a','b','c') AS 结果;SELECT 	CONCAT(last_name,first_name) AS 姓名FROM	employees;

进阶2：条件查询

/*语法：	select 		查询列表	from		表名	where		筛选条件;分类：	一、按条件表达式筛选		简单条件运算符：> < = != <> >= <=		二、按逻辑表达式筛选	逻辑运算符：	作用：用于连接条件表达式		&& || !		and or not			&&和and：两个条件都为true，结果为true，反之为false	||或or： 只要有一个条件为true，结果为true，反之为false	!或not： 如果连接的条件本身为false，结果为true，反之为false		三、模糊查询		like		between and		in		is null	*/#一、按条件表达式筛选#案例1：查询工资>12000的员工信息SELECT 	*FROM	employeesWHERE	salary>12000;		#案例2：查询部门编号不等于90号的员工名和部门编号SELECT 	last_name,	department_idFROM	employeesWHERE	department_id<>90;#二、按逻辑表达式筛选#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金SELECT	last_name,	salary,	commission_pctFROM	employeesWHERE	salary>=10000 AND salary<=20000;#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息SELECT	*FROM	employeesWHERE	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;#三、模糊查询/*like		between andinis null|is not null*/#1.like/*特点：①一般和通配符搭配使用	通配符：	% 任意多个字符,包含0个字符	_ 任意单个字符*、#案例1：查询员工名中包含字符a的员工信息select 	*from	employeeswhere	last_name like '%a%';#abc#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资select	last_name,	salaryFROM	employeesWHERE	last_name LIKE '__n_l%';#案例3：查询员工名中第二个字符为_的员工名SELECT	last_nameFROM	employeesWHERE	last_name LIKE '_$_%' ESCAPE '$';#2.between and/*①使用between and 可以提高语句的简洁度②包含临界值③两个临界值不要调换顺序*/#案例1：查询员工编号在100到120之间的员工信息SELECT	*FROM	employeesWHERE	employee_id >= 120 AND employee_id<=100;#----------------------SELECT	*FROM	employeesWHERE	employee_id BETWEEN 120 AND 100;#3.in/*含义：判断某字段的值是否属于in列表中的某一项特点：	①使用in提高语句简洁度	②in列表的值类型必须一致或兼容	③in列表中不支持通配符*/#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECT	last_name,	job_idFROM	employeesWHERE	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';#------------------SELECT	last_name,	job_idFROM	employeesWHERE	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');#4、is null/*=或<>不能用于判断null值is null或is not null 可以判断null值*/#案例1：查询没有奖金的员工名和奖金率SELECT	last_name,	commission_pctFROM	employeesWHERE	commission_pct IS NULL;#案例1：查询有奖金的员工名和奖金率SELECT	last_name,	commission_pctFROM	employeesWHERE	commission_pct IS NOT NULL;#----------以下为×SELECT	last_name,	commission_pctFROM	employeesWHERE 	salary IS 12000;	#安全等于  <=>#案例1：查询没有奖金的员工名和奖金率SELECT	last_name,	commission_pctFROM	employeesWHERE	commission_pct <=>NULL;#案例2：查询工资为12000的员工信息SELECT	last_name,	salaryFROM	employeesWHERE 	salary <=> 12000;#is null pk <=>IS NULL:仅仅可以判断NULL值，可读性较高，建议使用<=>    :既可以判断NULL值，又可以判断普通的数值，可读性较低

DML语言

/*数据操作语言：插入：insert修改：update删除：delete*/#一、插入语句#方式一：经典的插入/*语法：insert into 表名(列名,...) values(值1,...);*/SELECT * FROM beauty;#1.插入的值的类型要与列的类型一致或兼容INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);#2.不可以为null的列必须插入值。可以为null的列如何插入值？#方式一：INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);#方式二：INSERT INTO beauty(id,NAME,sex,phone)VALUES(15,'娜扎','女','1388888888');#3.列的顺序是否可以调换INSERT INTO beauty(NAME,sex,id,phone)VALUES('蒋欣','女',16,'110');#4.列数和值的个数必须一致INSERT INTO beauty(NAME,sex,id,phone)VALUES('关晓彤','女',17,'110');#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致INSERT INTO beautyVALUES(18,'张飞','男',NULL,'119',NULL,NULL);#方式二：/*语法：insert into 表名set 列名=值,列名=值,...*/--2、查询在2018年11月30日成绩及格的同学的所有信息。-- 10 代表截取10个字符串select * from tbl_store WHERE LEFT(create_time, 10)='2021-03-31';select * from tbl_store WHERE DATE_FORMAT(create_time, '%Y-%m-%d')='2021-03-31';INSERT INTO beautySET id=19,NAME='刘涛',phone='999';#两种方式大pk ★#1、方式一支持插入多行,方式二不支持INSERT INTO beautyVALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2),(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2),(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);#2、方式一支持子查询，方式二不支持INSERT INTO beauty(id,NAME,phone)SELECT 26,'宋茜','11809866';INSERT INTO beauty(id,NAME,phone)SELECT id,boyname,'1234567'FROM boys WHERE id<3;#二、修改语句/*1.修改单表的记录★语法：update 表名set 列=新值,列=新值,...where 筛选条件;2.修改多表的记录【补充】语法：sql92语法：update 表1 别名,表2 别名set 列=值,...where 连接条件and 筛选条件;sql99语法：update 表1 别名inner|left|right join 表2 别名on 连接条件set 列=值,...where 筛选条件;*/## 1.修改单表的记录#案例1：修改beauty表中姓唐的女神的电话为13899888899UPDATE beauty SET phone = '13899888899'WHERE NAME LIKE '唐%';#案例2：修改boys表中id好为2的名称为张飞，魅力值 10UPDATE boys SET boyname='张飞',usercp=10WHERE id=2;

2.修改表的记录

#案例 1：修改张无忌的女朋友的手机号为114UPDATE boys boINNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`SET b.`phone`='119',bo.`userCP`=1000WHERE bo.`boyName`='张无忌';#案例2：修改没有男朋友的女神的男朋友编号都为2号UPDATE boys boRIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`SET b.`boyfriend_id`=2WHERE bo.`id` IS NULL;SELECT * FROM boys;#三、删除语句/*方式一：delete语法：1、单表的删除【★】delete from 表名 where 筛选条件2、多表的删除【补充】sql92语法：delete 表1的别名,表2的别名from 表1 别名,表2 别名where 连接条件and 筛选条件;sql99语法：delete 表1的别名,表2的别名from 表1 别名inner|left|right join 表2 别名 on 连接条件where 筛选条件;方式二：truncate语法：truncate table 表名;*/#方式一：delete#1.单表的删除#案例：删除手机号以9结尾的女神信息DELETE FROM beauty WHERE phone LIKE '%9';SELECT * FROM beauty;#2.多表的删除#案例：删除张无忌的女朋友的信息DELETE bFROM beauty bINNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`WHERE bo.`boyName`='张无忌';#案例：删除黄晓明的信息以及他女朋友的信息DELETE b,boFROM beauty bINNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`WHERE bo.`boyName`='黄晓明';#方式二：truncate语句#案例：将魅力值>100的男神信息删除TRUNCATE TABLE boys ;#delete pk truncate【面试题★】/*1.delete 可以加where 条件，truncate不能加2.truncate删除，效率高一丢丢3.假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。4.truncate删除没有返回值，delete删除有返回值5.truncate删除不能回滚，delete删除可以回滚.*/SELECT * FROM boys;DELETE FROM boys;TRUNCATE TABLE boys;INSERT INTO boys (boyname,usercp)VALUES('张飞',100),('刘备',100),('关云长',100);

进阶3：排序查询

/*语法：select 查询列表from 表名【where  筛选条件】order by 排序的字段或表达式;特点：1、asc代表的是升序，可以省略desc代表的是降序2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段3、order by子句在查询语句的最后面，除了limit子句*/#1、按单个字段排序SELECT * FROM employees ORDER BY salary DESC;#2、添加筛选条件再排序#案例：查询部门编号>=90的员工信息，并按员工编号降序SELECT *FROM employeesWHERE department_id>=90ORDER BY employee_id DESC;#3、按表达式排序#案例：查询员工信息 按年薪降序SELECT *,salary*12*(1+IFNULL(commission_pct,0))FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;#4、按别名排序#案例：查询员工信息 按年薪升序SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC;#5、按函数排序#案例：查询员工名，并且按名字的长度降序SELECT LENGTH(last_name),last_name FROM employeesORDER BY LENGTH(last_name) DESC;#6、按多个字段排序#案例：查询员工信息，要求先按工资降序，再按employee_id升序SELECT *FROM employeesORDER BY salary DESC,employee_id ASC;

进阶4：常见函数

/*概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名好处：1、隐藏了实现细节  2、提高代码的重用性调用：select 函数名(实参列表) 【from 表】;特点：	①叫什么（函数名）	②干什么（函数功能）分类：	1、单行函数	如 concat、length、ifnull等	2、分组函数		功能：做统计使用，又称为统计函数、聚合函数、组函数	常见函数：一、单行函数1、字符函数	concat拼接	substr截取子串	upper转换成大写	lower转换成小写	trim去前后指定的空格和字符	ltrim去左边空格	rtrim去右边空格	replace替换	lpad左填充	rpad右填充	instr返回子串第一次出现的索引	length 获取字节个数	2、数学函数	round 四舍五入	rand 随机数	floor向下取整	ceil向上取整	mod取余	truncate截断3、日期函数	now当前系统日期+时间	curdate当前系统日期	curtime当前系统时间	str_to_date 将字符转换成日期	date_format将日期转换成字符	DATEDIFF() 函数返回两个日期之间的天数。	DATEDIFF(date1,date2)	date1 和 date2 参数是合法的日期或日期/时间表达式。	注释：只有值的日期部分参与计算。4、流程控制函数	if 处理双分支	case语句 处理多分支		情况1：处理等值判断		情况2：处理条件判断	5、其他函数	version版本	database当前库	user当前连接用户*/#一、字符函数#1.length 获取参数值的字节个数SELECT LENGTH('john');SELECT LENGTH('张三丰hahaha');SHOW VARIABLES LIKE '%char%'#2.concat 拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;#3.upper、lowerSELECT UPPER('john');SELECT LOWER('joHn');#示例：将姓变大写，名变小写，然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;#4.substr、substring注意：索引从1开始#截取从指定索引处后面所有字符SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;#截取从指定索引处指定字符长度的字符SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_putFROM employees;#5.instr 返回子串第一次出现的索引，如果找不到返回0SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;#6.trimSELECT LENGTH(TRIM('    张翠山    ')) AS out_put;SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;#7.lpad 用指定的字符实现左填充指定长度SELECT LPAD('殷素素',2,'*') AS out_put;#8.rpad 用指定的字符实现右填充指定长度SELECT RPAD('殷素素',12,'ab') AS out_put;#9.replace 替换SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;#二、数学函数#round 四舍五入SELECT ROUND(-1.55);SELECT ROUND(1.567,2);#ceil 向上取整,返回>=该参数的最小整数SELECT CEIL(-1.02);#floor 向下取整，返回<=该参数的最大整数SELECT FLOOR(-9.99);#truncate 截断SELECT TRUNCATE(1.69999,1);#mod取余/*mod(a,b) ：  a-a/b*bmod(-10,-3):-10- (-10)/(-3)*（-3）=-1*/SELECT MOD(10,-3);SELECT 10%3;#三、日期函数#now 返回当前系统日期+时间SELECT NOW();#curdate 返回当前系统日期，不包含时间SELECT CURDATE();#curtime 返回当前时间，不包含日期SELECT CURTIME();#可以获取指定的部分，年、月、日、小时、分钟、秒SELECT YEAR(NOW()) 年;SELECT YEAR('1998-1-1') 年;SELECT  YEAR(hiredate) 年 FROM employees;SELECT MONTH(NOW()) 月;SELECT MONTHNAME(NOW()) 月;#str_to_date 将字符通过指定的格式转换成日期SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;#查询入职日期为1992--4-3的员工信息SELECT * FROM employees WHERE hiredate = '1992-4-3';SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');#date_format 将日期转换成字符SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;#查询有奖金的员工名和入职日期(xx月/xx日 xx年)SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期FROM employeesWHERE commission_pct IS NOT NULL;#四、其他函数SELECT VERSION();SELECT DATABASE();SELECT USER();## 五、流程控制函数#1.if函数： if else 的效果SELECT IF(10<5,'大','小');SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注FROM employees;#2.case函数的使用一： switch case 的效果/*java中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倍其他部门，显示的工资为原工资*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM employees;#3.case 函数的使用二：类似于 多重if/*java中：if(条件1){	语句1；}else if(条件2){	语句2；}...else{	语句n;}mysql中：case when 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2。。。else 要显示的值n或语句nend*/#案例：查询员工的工资的情况如果工资>20000,显示A级别如果工资>15000,显示B级别如果工资>10000，显示C级别否则，显示D级别SELECT salary,CASE WHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;

流程控制结构

/*顺序、分支、循环*/#一、分支结构#1.if函数/*语法：if(条件,值1，值2)功能：实现双分支应用在begin end中或外面*/#2.case结构/*语法：情况1：类似于switchcase 变量或表达式when 值1 then 语句1;when 值2 then 语句2;...else 语句n;end 情况2：case when 条件1 then 语句1;when 条件2 then 语句2;...else 语句n;end 应用在begin end 中或外面*/#3.if结构/*语法：if 条件1 then 语句1;elseif 条件2 then 语句2;....else 语句n;end if;功能：类似于多重if只能应用在begin end 中*/#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回DCREATE FUNCTION test_if(score FLOAT) RETURNS CHARBEGIN	DECLARE ch CHAR DEFAULT 'A';	IF score>90 THEN SET ch='A';	ELSEIF score>80 THEN SET ch='B';	ELSEIF score>60 THEN SET ch='C';	ELSE SET ch='D';	END IF;	RETURN ch;		END $SELECT test_if(87)$#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500CREATE PROCEDURE test_if_pro(IN sal DOUBLE)BEGIN	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;	END IF;	END $CALL test_if_pro(2100)$#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回DCREATE FUNCTION test_case(score FLOAT) RETURNS CHARBEGIN 	DECLARE ch CHAR DEFAULT 'A';		CASE 	WHEN score>90 THEN SET ch='A';	WHEN score>80 THEN SET ch='B';	WHEN score>60 THEN SET ch='C';	ELSE SET ch='D';	END CASE;		RETURN ch;END $SELECT test_case(56)$#二、循环结构/*分类：while、loop、repeat循环控制：iterate类似于 continue，继续，结束本次循环，继续下一次leave 类似于  break，跳出，结束当前所在的循环*/#1.while/*语法：【标签:】while 循环条件 do	循环体;end while【 标签】;联想：while(循环条件){	循环体;}*/#2.loop/*语法：【标签:】loop	循环体;end loop 【标签】;可以用来模拟简单的死循环*/#3.repeat/*语法：【标签：】repeat	循环体;until 结束循环的条件end repeat 【标签】;*/#1.没有添加循环控制语句#案例：批量插入，根据次数插入到admin表中多条记录DROP PROCEDURE pro_while1$CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN	DECLARE i INT DEFAULT 1;	WHILE i<=insertCount DO		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');		SET i=i+1;	END WHILE;	END $CALL pro_while1(100)$/*int i=1;while(i<=insertcount){	//插入		i++;}*/#2.添加leave语句#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止TRUNCATE TABLE admin$DROP PROCEDURE test_while1$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)$#3.添加iterate语句#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次TRUNCATE TABLE admin$DROP PROCEDURE test_while1$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)$/*int i=0;while(i<=insertCount){	i++;	if(i%2==0){		continue;	}	插入	}*/

二、分组函数

/*功能：用作统计使用，又称为聚合函数或统计函数或组函数分类：sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数特点：1、sum、avg一般用于处理数值型   max、min、count可以处理任何类型2、以上分组函数都忽略null值3、可以和distinct搭配实现去重的运算4、count函数的单独介绍一般使用count(*)用作统计行数5、和分组函数一同查询的字段要求是group by后的字段*/#1、 简单 的使用SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM employees;SELECT SUM(salary) FROM employees;SELECT AVG(salary) FROM employees;SELECT MIN(salary) FROM employees;SELECT MAX(salary) FROM employees;SELECT COUNT(salary) FROM employees;SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM employees;#2、参数支持哪些类型SELECT SUM(last_name) ,AVG(last_name) FROM employees;SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;SELECT MAX(last_name),MIN(last_name) FROM employees;SELECT MAX(hiredate),MIN(hiredate) FROM employees;SELECT COUNT(commission_pct) FROM employees;SELECT COUNT(last_name) FROM employees;#3、是否忽略nullSELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;SELECT COUNT(commission_pct) FROM employees;SELECT commission_pct FROM employees;#4、和distinct搭配 去重SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;#5、count函数的详细介绍 统计SELECT COUNT(salary) FROM employees;SELECT COUNT(*) FROM employees;SELECT COUNT(1) FROM employees;效率：MYISAM存储引擎下  ，COUNT(*)的效率高INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些#6、和分组函数一同查询的字段有限制SELECT AVG(salary),employee_id  FROM employees;

进阶5：分组查询

/*语法：select 查询列表from 表【where 筛选条件】group by 分组的字段【order by 排序的字段】;特点：1、和分组函数一同查询的字段必须是group by后出现的字段2、筛选分为两类：分组前筛选和分组后筛选		针对的表			位置		连接的关键字分组前筛选	原始表				group by前	where	分组后筛选	group by后的结果集    		group by后	having问题1：分组函数做筛选能不能放在where后面答：不能问题2：where——group by——having一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率3、分组可以按单个字段也可以按多个字段4、可以搭配着排序使用*/#引入：查询每个部门的员工个数SELECT COUNT(*) FROM employees WHERE department_id=90;#1.简单的分组#案例1：查询每个工种的员工平均工资SELECT AVG(salary),job_idFROM employeesGROUP BY job_id;#案例2：查询每个位置的部门个数SELECT COUNT(*),location_idFROM departmentsGROUP BY location_id;#2、可以实现分组前的筛选#案例1：查询邮箱中包含a字符的 每个部门的最高工资SELECT MAX(salary),department_idFROM employeesWHERE email LIKE '%a%'GROUP BY department_id;#案例2：查询有奖金的每个领导手下员工的平均工资SELECT AVG(salary),manager_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id;#3、分组后筛选 HAVING 分组后的筛选#案例：查询哪个部门的员工个数>5#①查询每个部门的员工个数SELECT COUNT(*),department_idFROM employeesGROUP BY department_id;#② 筛选刚才①结果SELECT COUNT(*),department_idFROM employeesGROUP BY department_idHAVING COUNT(*)>5;#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT job_id,MAX(salary)FROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary)>12000;#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资manager_id>102SELECT manager_id,MIN(salary)FROM employeesGROUP BY manager_idHAVING MIN(salary)>5000;#4.按多个字段分组#案例：查询每个工种每个部门的最低工资,并按最低工资降序SELECT MIN(salary),job_id,department_idFROM employeesGROUP BY department_id,job_idORDER BY MIN(salary) DESC;#5.添加排序#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT job_id,MAX(salary) mFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING m>6000ORDER BY m ;

进阶6：连接查询

/*含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行发生原因：没有有效的连接条件如何避免：添加有效的连接条件分类：	按年代分类：	sql92标准:仅仅支持内连接	sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接		按功能分类：		内连接：			等值连接			非等值连接			自连接		外连接：			左外连接			右外连接			全外连接				交叉连接*/SELECT * FROM beauty;SELECT * FROM boys;SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;#一、sql92标准#1、等值连接/*① 多表等值连接的结果为多表的交集部分②n表连接，至少需要n-1个连接条件③ 多表的顺序没有要求④一般需要为表起别名⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选*/#案例1：查询女神名和对应的男神名SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;#案例2：查询员工名和对应的部门名SELECT last_name,department_nameFROM employees,departmentsWHERE employees.`department_id`=departments.`department_id`;#2、为表起别名/* ①提高语句的简洁度 ②区分多个重名的字段注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定*/#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM employees  e,jobs jWHERE e.`job_id`=j.`job_id`;#3、两个表的顺序是否可以调换#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM jobs j,employees eWHERE e.`job_id`=j.`job_id`;#4、可以加筛选#案例：查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pctFROM employees e,departments dWHERE e.`department_id`=d.`department_id`AND e.`commission_pct` IS NOT NULL;#案例2：查询城市名中第二个字符为o的部门名和城市名SELECT department_name,cityFROM departments d,locations lWHERE d.`location_id` = l.`location_id`AND city LIKE '_o%';#5、可以加分组#案例1：查询每个城市的部门个数SELECT COUNT(*) 个数,cityFROM departments d,locations lWHERE d.`location_id`=l.`location_id`GROUP BY city;#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资SELECT department_name,d.`manager_id`,MIN(salary)FROM departments d,employees eWHERE d.`department_id`=e.`department_id`AND commission_pct IS NOT NULLGROUP BY department_name,d.`manager_id`;#6、可以加排序#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs jWHERE e.`job_id`=j.`job_id`GROUP BY job_titleORDER BY COUNT(*) DESC;#7、可以实现三表连接？#案例：查询员工名、部门名和所在的城市SELECT last_name,department_name,cityFROM employees e,departments d,locations lWHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`AND city LIKE 's%'ORDER BY department_name DESC;#2、非等值连接#案例1：查询员工的工资和工资级别SELECT salary,grade_levelFROM employees e,job_grades gWHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`AND g.`grade_level`='A';/*select salary,employee_id from employees;select * from job_grades;CREATE TABLE job_grades(grade_level VARCHAR(3), lowest_sal  int, highest_sal int);INSERT INTO job_gradesVALUES ('A', 1000, 2999);INSERT INTO job_gradesVALUES ('B', 3000, 5999);INSERT INTO job_gradesVALUES('C', 6000, 9999);INSERT INTO job_gradesVALUES('D', 10000, 14999);INSERT INTO job_gradesVALUES('E', 15000, 24999);INSERT INTO job_gradesVALUES('F', 25000, 40000);*/#3、自连接#案例：查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e,employees mWHERE e.`manager_id`=m.`employee_id`;

进阶6.2二、sql99语法

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

分类：

内连接（★）：inner

外连接	左外(★):left 【outer】	右外(★)：right 【outer】	全外：full【outer】交叉连接：cross */

一）内连接

/*语法：select 查询列表from 表1 别名inner join 表2 别名on 连接条件;分类：等值非等值自连接特点：①添加排序、分组、筛选②inner可以省略③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集*/

1、等值连接

#案例1.查询员工名、部门名SELECT last_name,department_nameFROM departments dINNER JOIN  employees eON e.`department_id` = d.`department_id`;#案例2.查询名字中包含e的员工名和工种名（添加筛选）SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.`job_id`=  j.`job_id`WHERE e.`last_name` LIKE '%e%';#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）#①查询每个城市的部门个数#②在①结果上筛选满足条件的SELECT city,COUNT(*) 部门个数FROM departments dINNER JOIN locations lON d.`location_id`=l.`location_id`GROUP BY cityHAVING COUNT(*)>3;#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）#①查询每个部门的员工个数SELECT COUNT(*),department_nameFROM employees eINNER JOIN departments dON e.`department_id`=d.`department_id`GROUP BY department_name#② 在①结果上筛选员工个数>3的记录，并排序SELECT COUNT(*) 个数,department_nameFROM employees eINNER JOIN departments dON e.`department_id`=d.`department_id`GROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）SELECT last_name,department_name,job_titleFROM employees eINNER 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、非等值连接

#查询员工的工资级别SELECT salary,grade_levelFROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;   #查询工资级别的个数>20的个数，并且按工资级别降序 SELECT COUNT(*),grade_levelFROM 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;   #三）自连接  #查询员工的名字、上级的名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`= m.`employee_id`;   #查询姓名中包含字符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%';   #二、外连接  /* 应用场景：用于查询一个表中有，另一个表没有的记录  特点： 1、外连接的查询结果为主表中的所有记录	如果从表中有和它匹配的，则显示匹配的值	如果从表中没有和它匹配的，则显示null	外连接查询结果=内连接结果+主表中有而从表没有的记录 2、左外连接，left join左边的是主表    右外连接，right join右边的是主表 3、左外和右外交换两个表的顺序，可以实现同样的效果  4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的 */ #引入：查询男朋友 不在男神表的的女神名  SELECT * FROM beauty; SELECT * FROM boys;  #左外连接 SELECT b.*,bo.* FROM boys bo LEFT OUTER JOIN beauty b ON b.`boyfriend_id` = bo.`id` WHERE b.`id` IS NULL;   #案例1：查询哪个部门没有员工 #左外 SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;   #右外   SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;   #全外   USE girls; SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id;  #交叉连接  SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;    #sql92和 sql99pk /* 功能：sql99支持的较多 可读性：sql99实现连接条件和筛选条件的分离，可读性较高 */

进阶7：子查询

/*含义：出现在其他语句中的select语句，称为子查询或内查询外部的查询语句，称为主查询或外查询分类：按子查询出现的位置：	select后面：		仅仅支持标量子查询		from后面：		支持表子查询	where或having后面：★		标量子查询（单行） √		列子查询  （多行） √				行子查询			exists后面（相关子查询）		表子查询按结果集的行列数不同：	标量子查询（结果集只有一行一列）	列子查询（结果集只有一列多行）	行子查询（结果集有一行多列）	表子查询（结果集一般为多行多列）*/

一、where或having后面

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

1.标量子查询★

#案例1：谁的工资比 Abel 高?#①查询Abel的工资SELECT salaryFROM employeesWHERE last_name = 'Abel'#②查询员工的信息，满足 salary>①结果SELECT *FROM employeesWHERE salary>(	SELECT salary	FROM employees	WHERE last_name = 'Abel');#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资#①查询141号员工的job_idSELECT job_idFROM employeesWHERE employee_id = 141#②查询143号员工的salarySELECT salaryFROM employeesWHERE employee_id = 143#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②SELECT last_name,job_id,salaryFROM employeesWHERE job_id = (	SELECT job_id	FROM employees	WHERE employee_id = 141) AND salary>(	SELECT salary	FROM employees	WHERE employee_id = 143);#案例3：返回公司工资最少的员工的last_name,job_id和salary#①查询公司的 最低工资SELECT MIN(salary)FROM employees#②查询last_name,job_id和salary，要求salary=①SELECT last_name,job_id,salaryFROM employeesWHERE salary=(	SELECT MIN(salary)	FROM employees);#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资#①查询50号部门的最低工资SELECT  MIN(salary)FROM employeesWHERE department_id = 50#②查询每个部门的最低工资SELECT MIN(salary),department_idFROM employeesGROUP BY department_id#③ 在②基础上筛选，满足min(salary)>①SELECT MIN(salary),department_idFROM employeesGROUP BY department_idHAVING MIN(salary)>(	SELECT  MIN(salary)	FROM employees	WHERE department_id = 50);

2.列子查询（多行子查询）★

结果集有多行	一般搭配多行操作符使用：any、all、in、not in	in： 属于子查询结果中的任意一个就行	any和all往往可以用其他查询代替#案例1：返回location_id是1400或1700的部门中的所有员工姓名#①查询location_id是1400或1700的部门编号SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700)#②查询员工姓名，要求部门号是①列表中的某一个SELECT last_nameFROM employeesWHERE department_id  <>ALL(	SELECT DISTINCT department_id	FROM departments	WHERE location_id IN(1400,1700));#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary#①查询job_id为‘IT_PROG’部门任一工资SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG'#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary
   
    'IT_PROG';#或SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary<(	SELECT MAX(salary)	FROM employees	WHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';
   

3、行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息SELECT * FROM employees WHERE ( employee_id, salary )=( 	SELECT MIN( employee_id ), MAX( salary )  	FROM employees );------------------------------#二、select后面/*仅仅支持标量子查询*/#案例：查询每个部门的员工个数 SELECT (	SELECT department_name	FROM departments d	INNER JOIN employees e	ON d.department_id=e.department_id	WHERE e.employee_id=102	) 部门名;#三、from后面/*将子查询结果充当一张表，要求必须起别名*/#案例：查询每个部门的平均工资的工资等级#①查询每个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_idSELECT * FROM job_grades;#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_salSELECT  ag_dep.*,g.`grade_level`FROM (	SELECT AVG(salary) ag,department_id	FROM employees	GROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;#四、exists后面（相关子查询）/*语法：exists(完整的查询语句)结果：1或0*/SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);#案例1：查询有员工的部门名#inSELECT department_nameFROM departments dWHERE d.`department_id` IN(	SELECT department_id	FROM employees)#existsSELECT department_nameFROM departments dWHERE EXISTS(	SELECT *	FROM employees e	WHERE d.`department_id`=e.`department_id`);#案例2：查询没有女朋友的男神信息#inSELECT bo.*FROM boys boWHERE bo.id NOT IN(	SELECT boyfriend_id	FROM beauty)#existsSELECT bo.*FROM boys boWHERE NOT EXISTS(	SELECT boyfriend_id	FROM beauty b	WHERE bo.`id`=b.`boyfriend_id`);

进阶8：分页查询 ★

/*应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求语法：	select 查询列表	from 表	【join type join 表2	on 连接条件	where 筛选条件	group by 分组字段	having 分组后的筛选	order by 排序的字段】	limit 【offset,】size;		offset要显示条目的起始索引（起始索引从0开始）	size 要显示的条目个数特点：	①limit语句放在查询语句的最后	②公式	要显示的页数 page，每页的条目数size		select 查询列表	from 表	limit (page-1)*size,size;		size=10	page  	1	0	2  	10	3	20	*/#案例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 ;三、SQL92语法1、等值连接语法：	select 查询列表	from 表1 别名,表2 别名	where 表1.key=表2.key	【and 筛选条件】	【group by 分组字段】	【having 分组后的筛选】	【order by 排序字段】特点：	① 一般为表起别名	②多表的顺序可以调换	③n表连接至少需要n-1个连接条件	④等值连接的结果是多表的交集部分2、非等值连接语法：	select 查询列表	from 表1 别名,表2 别名	where 非等值的连接条件	【and 筛选条件】	【group by 分组字段】	【having 分组后的筛选】	【order by 排序字段】3、自连接语法：	select 查询列表	from 表 别名1,表 别名2	where 等值的连接条件	【and 筛选条件】	【group by 分组字段】	【having 分组后的筛选】	【order by 排序字段】四、SQL99语法1、内连接语法：select 查询列表from 表1 别名【inner】 join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表limit 子句;特点：①表的顺序可以调换②内连接的结果=多表的交集③n表连接至少需要n-1个连接条件分类：等值连接非等值连接自连接2、外连接语法：select 查询列表from 表1 别名left|right|full【outer】 join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表limit 子句;特点：①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null②left join 左边的就是主表，right join 右边的就是主表  full join 两边都是主表③一般用于查询除了交集部分的剩余的不匹配的行3、交叉连接语法：select 查询列表from 表1 别名cross join 表2 别名;特点：类似于笛卡尔乘积

进阶9：联合查询

/*union 联合 合并：将多条查询语句的结果合并成一个结果语法：查询语句1union查询语句2union...应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时特点：★1、要求多条查询语句的查询列数是一致的！2、要求多条查询语句的查询的每一列的类型和顺序最好一致3、**union**关键字默认去重，如果使用union all 可以包含重复项*/#引入的案例：查询部门编号>90或邮箱包含a的员工信息SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;SELECT * FROM employees  WHERE email LIKE '%a%'UNIONSELECT * FROM employees  WHERE department_id>90;#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息SELECT id,cname FROM t_ca WHERE csex='男'UNION ALLSELECT t_id,tname FROM t_ua WHERE tGender='male';

DDL 常见约束：

/*	约束	含义： 一种限制，用于限制表中的数据,为了保证表中的数据的准确性和可靠性 	分类：六大约束			NOT NULL :非空 ，保证该字段的值不能为空			例如：姓名，学号		DEFAULT :默认，用于该字段有默认值			例如：性别		PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空			例如学号、员工编号等		UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空			例如座位号		CHECK:检查约束【mysql中不支持】			比如年龄、性别		FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值		在从表添加外键约束，用于引用主表中某列的值		比如学生表的专业编号，员工表的部门编号，员工表的工种编号	添加约束的时机：		1.创建表时		2.修改表时	约束的添加分类：		列级约束：			六大约束语法都支持，但外键约束没有效果		表级约束：			除了非空，默认，其他的都支持		*/CREATE TABLE 表名 (	字段名 字段类型 列级约束,	字段名 字段类型,	表级约束)

一、创建表时添加约束

#1.添加列级约束/*语法：直接在字段名和类型后面追加 约束类型即可。只支持：默认、非空、主键、唯一*/CREATE DATABASE test_students;USE test_students;DROP TABLE stuinfo;CREATE TABLE stuinfo (	id INT PRIMARY KEY COMMENT 'id',#主键	stuName VARCHAR ( 20 ) NOT NULL UNIQUE,#非空	gender CHAR ( 1 ) CHECK ( gender = '男' OR gender = '女' ),#检查	seat INT UNIQUE,#唯一	age INT DEFAULT 18,#默认约束	majorId INT REFERENCES major ( id ) #外键	);CREATE TABLE major ( id INT PRIMARY KEY, majorName VARCHAR ( 20 ) );#查看stuinfo中的所有索引，包括主键、外键、唯一SHOW INDEX FROM stuinfo;#2.添加表级约束/*语法：在各个字段的最下面 【constraint 约束名】 约束类型(字段名) */DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(	id INT,	stuname VARCHAR(20),	gender CHAR(1),	seat INT,	age INT,	majorid INT,		CONSTRAINT pk PRIMARY KEY(id),#主键	CONSTRAINT uq UNIQUE(seat),#唯一键	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键	);SHOW INDEX FROM stuinfo;#通用的写法：★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));

主键和唯一的大对比：

/*		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合	主键	√		×		至多有1个           √，但不推荐	唯一	√		√		可以有多个          √，但不推荐外键：	1、要求在从表设置外键关系	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求	3、主表的关联列必须是一个key（一般是主键或唯一）	4、插入数据时，先插入主表，再插入从表	删除数据时，先删除从表，再删除主表*/

二、修改表时添加约束

/*1、添加列级约束	alter table 表名 modify column 字段名 字段类型 新约束;2、添加表级约束	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.添加主键#①列级约束ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#②表级约束ALTER TABLE stuinfo ADD PRIMARY KEY(id);#4.添加唯一#①列级约束ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;#②表级约束ALTER TABLE stuinfo ADD UNIQUE(seat);#5.添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

三、修改表时删除约束

#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;SHOW INDEX FROM stuinfo;

TCL ：事务

/*	Transaction Control Language 事务控制语言事务：	一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。案例：转账张三丰  1000郭襄	1000update 表 set 张三丰的余额=500 where name='张三丰'意外update 表 set 郭襄的余额=1500 where name='郭襄'事务的特性：ACID原子性：一个事务不可再分割，要么都执行要么都不执行一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态隔离性：一个事务的执行不受其他事务的干扰持久性：一个事务一旦提交，则会永久的改变数据库的数据.事务的创建隐式事务：事务没有明显的开启和结束的标记比如insert、update、delete语句delete from 表 where id =1;显式事务：事务具有明显的开启和结束的标记前提：必须先设置自动提交功能为禁用set autocommit=0;步骤1：开启事务set autocommit=0;start transaction;可选的步骤2：编写事务中的sql语句(select insert update delete)语句1;语句2;...步骤3：结束事务commit;提交事务rollback;回滚事务savepoint 节点名;设置保存点事务的隔离级别：		  脏读		不可重复读	幻读read uncommitted：√		√		√read committed：  ×		√		√repeatable read： ×		×		√serializable	  ×             ×               ×mysql中默认 第三个隔离级别 repeatable readoracle中默认第二个隔离级别 read committed查看隔离级别select @@tx_isolation;设置隔离级别set session|global transaction isolation level 隔离级别;开启事务的语句;update 表 set 张三丰的余额=500 where name='张三丰'update 表 set 郭襄的余额=1500 where name='郭襄' 结束事务的语句;*/

事务并发问题

当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

脏读：一个事务读取到了另外一个事务未提交的数据不可重复读：同一个事务中，多次读取到的数据不一致幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题？

通过设置事务的隔离级别1、READ UNCOMMITTED2、READ COMMITTED 可以避免脏读3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读4、SERIALIZABLE可以避免脏读、不可重复读和幻读

设置隔离级别：

set session|global  transaction isolation level 隔离级别名;

案列

SHOW VARIABLES LIKE 'autocommit';SHOW ENGINES;#1.演示事务的使用步骤#开启事务SET autocommit=0;START TRANSACTION;#编写一组事务的语句UPDATE account SET balance = 1000 WHERE username='张无忌';UPDATE account SET balance = 1000 WHERE username='赵敏';#结束事务ROLLBACK;#commit;SELECT * FROM account;#2.演示事务对于delete和truncate的处理的区别SET autocommit=0;START TRANSACTION;DELETE FROM account;ROLLBACK;#3.演示savepoint 的使用SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=25;SAVEPOINT a;#设置保存点DELETE FROM account WHERE id=28;ROLLBACK TO a;#回滚到保存点SELECT * FROM account;

转载地址：https://blog.csdn.net/weixin_43392726/article/details/115390759 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！