Mysql数据库

随~缘

已于 2022-07-29 20:59:15 修改

阅读量376

点赞数

文章标签：数据库 mysql

于 2022-07-19 20:53:18 首次发布

本文链接：https://blog.youkuaiyun.com/qq_44865726/article/details/125880481

版权

1.数据库的分类

关系型数据库（SQL）
  	 	 MySQL.Oracle、SQL Server、SQLite、DB2
非关系型数据库（NOSQL）
		 Redis、MongoDB

2. 结构化查询语句分类

在这里插入图片描述

3.列类型分类

3.1 数值类型

在这里插入图片描述

3.2 字符串类型

在这里插入图片描述

char与varchar区别
	 char是一种固定长度的类型，无论储存的数据有多少都会固定长度，如果插入的长度小于定义长度，则会用空格进行填充。char最大长度是255字符。但查找效率高。
	 varchar是一种可变长度的类型，当插入的长度小于定义长度时，插入多长就存多长。varchar最大长度是65535个字节。但查找效率低。

3.3 日期和时间型数值类型

例子
  	create table test(
  		 id int not null  auto_increment,
  		 t_date date default null,
  		 t_date_time datetime not null default current_timestamp,
  		 t_timestamp timestamp not null default current_timestamp on update  current_timestamp,
  		 t_date_time2 datetime(3) not null default current_timestamp(3),
  		 t_time time,
  		 t_year year,
  		 primary key(id)
  	);
  	
  	insert into test(t_date,t_time,t_year) values
  	('2022-04-30',CURRENT_TIME,CURRENT_DATE),
  	(CURRENT_DATE,'17:22:01','2021')
  	
  	输出语句
  	id	 t_date  	   t_date_time	 			t_timestamp 	       t_date_time2			    t_time	   t_year
  	1   2022-01-30	 2022-01-30 10:00:36	 2022-01-30 10:00:36	 2022-01-30 10:00:36.195   10:00:36	 	2022
  	2   2022-01-30	 2022-01-30 10:00:36	 2022-01-30 10:00:36	 2022-01-30 10:00:36.195   17:22:01	 	2021
  	
  	date  仅包含日期，mysql存储和显示的格式是'YYYY-MM-DD'，可以表示的范围是'1000-01-01' to '9999-12-31'
  	time  仅包含时间，mysql存储和显示的格式是Hh:mm:ss，可以白哦是的范围是 '-838:59:59' to 838:59:59
  	year  YYYY格式的年份值，取值范围时'1901' to '2155'
  	datetime  包含日期、时间两部分，mysql存储和显示的格式是'YYYY-MM-DD hh:mm:ss'，可以表示的范围是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
  	timestamp  包含日期、时间两部分，可以表示的范围是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
  			   这里需要注意timestamp是有时区概念的，该类型可跟随时区变化，默认情况下是服务器的时区，如果一个数据库连接设置了时区参数，那么该类型的值会根据连接设置的时区来存储和返回值。
  			 
注意：
  	datetime、timestamp是可以有小数位的，一共最多可以有6位，所以在定义时可以是datetime(3)，默认情况下是datetime(0)，由于是0所以通常情况下就倍省略了；
  	timestamp的范围最大到'2038-01-19 03:14:07' UTC，所以很多情况下最好不用该类型，避免超过最大范围产生问题，可以datetime来代替；
  	datetime、tiemstap类型有自动初始话和更新为当前的日期和时间的功能；

4.重要示例(对表操作的基本用法)

CREATE TABLE IF NOT EXISTS student7(
  age int(3) UNSIGNED DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
  
  
#修改表名
  	alter table student7 rename as stu;
#给表添加字段
  	alter table stu add address varchar(50) not null;
#修改表中的字段名
  	alter table stu change age score int(3) default 100;
#删除表中的字段
  	alter table stu drop address;
#修改字段长度
	alter table stu modify column email varchar(50)
#删除表
	drop table stu;
#修改表中的字符集
	alter table stu CHARSET='utf8' COLLATE='utf8_unicode_ci'

#unsigned
  	CREATE TABLE `student_unsigned` (
  	  `name` varchar(20) DEFAULT NULL,
  	  `age` int(3) unsigned DEFAULT NULL
  	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  	
#zerofill
  	#(零填充):从左侧开始填充0(左侧不会改变数值大小),所以负数的时候就不能使用zerofill,一旦使用zerofill就相当于确定该字段为unsigned
  	#所以  `age` int(4)  zerofill DEFAULT NULL COMMENT '我很帅'   创建之后会自动变成   `age` int(4) unsigned zerofill DEFAULT NULL COMMENT '我很帅'
  	CREATE TABLE `student_unsigned_zerofill` (
  	  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  	  `age` int(4) unsigned zerofill DEFAULT NULL COMMENT '我很帅'
  	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
#AUTO_INCREMENT(可以在最后给这个赋一个初始值)
  	CREATE TABLE `student_auto` (
  	  `stuId` int(10) NOT NULL AUTO_INCREMENT,
  	  `name` varchar(20) DEFAULT NULL,
  	  PRIMARY KEY (`stuId`)
  	) ENGINE=InnoDB AUTO_INCREMENT=10091 DEFAULT CHARSET=utf8;
  
#default
  	CREATE TABLE `student_default` (
  	  `name` varchar(20) DEFAULT NULL,
  	  `gender` varchar(2) DEFAULT '男',
  	  `s_sex` varchar(10) not null default ''
  	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  	
#可以使用数据库与自带的函数
	select SUM(1+2);
	
	
注
	打开数据库事务的两种方式
    	1.	我的电脑 右键---管理  可以查看到服务---找到mysql
    	2.	net start mysql  可以直接打开mysql服务

5.delete与truncate的区别

区别一：
     truncate删除数据后重新写数据会从1开始，而delete删除数据后只会从删除前的最后一行续写；内存空间上，truncate省空间		

区别二：
    delete是DML语句中的，truncate和drop都属于DDL语句，因此事务上有区别
    delete在事务中，由于属于DML语句，所以可以进行回滚和提交操作（由操作者）
    truncate和drop则属于DDL语句，在事务中，执行后会自动commit，所以不可以回滚；
区别三：
	delete可以在后续加上where进行针对行的删除 				delete from 表名
    truncate和drop后面只能加上表名，直接删除表，无法where		truncate 表名
    
注意：
	drop和truncate不能够激活触发器，因为该操作不记录各行删除

6.group by

执行顺序
		from-where-group by-having-select-order by-limit	
佣金：
		买卖货物时，中间人或仲介人所得的金钱

CREATE TABLE `emp`  (
    `empno` int(11) NULL DEFAULT NULL commet '员工编号',
    `ename` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL comment='员工名称',
    `job` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL comment '工作名称',
    `mgr` int(11) NULL DEFAULT NULL,
    `hiredate` date NULL DEFAULT NULL comment '入职时间',
    `sal` decimal(7, 2) NULL DEFAULT NULL comment '工资',
    `comm` decimal(7, 2) NULL DEFAULT NULL comment '佣金',
    `deptno` int(11) NULL DEFAULT NULL comment '部门编号'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of emp
  -- ----------------------------
  	INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
  	INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
  	INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
  	INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
  	INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
  	INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
  	INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
  	INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
  	INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
  	INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
  	INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
  	INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
  	INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
  	INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
  	INSERT INTO `emp` VALUES (7988, 'LiSa', '保洁', 7780, '2022-07-22', 10000.00, NULL, 50);
  	
  	
  	#求各个部门的最高工资
  		select deptno,max(sal) from emp group by deptno;
  		解释：
  			group by 字段    是对后面的字段进行分组的
  			deptno这个字段中10有3个，20有5个，30有6个，50有1个
  			然后这里有max(sal)聚合函数，然后就相当于求每个组里面最大的数。
  			即先分组，然后生成一个临时表，
  			10
  			20
  			30
  			50
  			然后分好组之后，这里10有3个别分成1组，然后使用聚合函数把这组里的最大数找到


    #查询出mgr出现过两次以上（group by 字段 having ）
   	    select mgr from emp group by mgr having count(mgr)>2
   	    解释：
   	    	由执行顺序可知，先执行group by对mgr进行分组
   	    	7902
  			7698
  			7839
  			7566
  			
  			7788
  			7782
  			7780
  			中间有个null值，这就是分组后的情况，但是后面还有having,条件限制，
  			count(mgr)>2是获得例如7902有多少条,与2比较，7968有多少条，与2比较..以此类推，对于每个已经分好组的字段的值都会获得
  			
  	#查询工资总和大于9000的部门编号以及工资和
  		select deptno,sum(sal) from emp group by deptno having sum(sal)>9000

6.1 group by执行流程

select deptno,max(sal) from emp group by deptno

  1. 创建内存临时表，表里有两个字段deptno和max(sal) ；
  
  2. 全表扫描emp的记录，依次取出deptno= 'X'的记录,即：从全表的第一行到最后一行；
  
  	2.1 判断临时表中是否有为 deptno='X'的行，没有就插入一个记录 (X,1);
  
  	2.2 如果临时表中有deptno='X'的行，就比较这两个sal值的大小，取大的
  	
  3.循环2.1和2.2直到遍历完所有行数据

7.where与having

having子句：对分组后[select deptno,sum(sal) from emp group by deptno]的数据进行过滤. where是在分组前对数据进行过滤

where与having区别	
	having后面可以使用分组函数，where后面不可以使用分组函数。  
	分组函数总共5个,分别为:sum(求和)、max、min、avg(求平均)、count(计数)	
	
#查询工资总和大于9000的部门编号以及工资和
  select deptno, sum(sal) from emp group by deptno having sum(sal)>9000
  
#查询每个部门的部门编号以及每个部门工资大于1500的人数
  select  deptno,count(*) as '部门工资大于1500的人数' from emp where sal>1500 group by deptno;

  注：
    where是先于group by执行的，所以会先过滤sal>1500，再根据过滤玩的数据进行分组，分完组之后再执行select后的聚合函数

8.not与is null

not用法 表示取反   where not 字段

#挑选非女性的全部信息
   select * from stu where not gender='male'	   

is null用法		 字段 is not null == not 字段 is null
#展示stu表中的sname不是空的所有数据
  select * from stu where sname is not null;
  select * from stu where not sname is null;

9.模糊查询

like搭配两个通配符 _ 和 %         where 字段 like
  		_表示任意一个字符  %表示任意0到n个字符
  		
  #查询出姓名由5个字母构成的学生记录
  	select * from stu where sname like '_____'  #写了5个下划线
  #查询姓名由5个字母构成，并且第5个字母为i的学生记录
  	select * from stu where sname like '____i';
  #查询姓名以z开头的学生记录
  	select * from stu where sname like 'z%';
  #查询姓名中第2个字母为i的学生记录
  	select * from stu where sname like '_i%'
  #查询姓名中包含a字母的学生记录
  	select * from stu where sname like '%a%';

10.外键

语法格式
  constraint 外键的名字自己起 foreign key(本表的sid) references student(sid)

  DROP TABLE IF EXISTS `student`;
  CREATE TABLE `student`  (
    `sid` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    `sex` varchar(10)  DEFAULT '男',
    PRIMARY KEY (`sid`)
  ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of student
  -- ----------------------------
  INSERT INTO `student` VALUES (1001, '张三', '男');
  INSERT INTO `student` VALUES (1002, '李四', '男');
  INSERT INTO `student` VALUES (1003, '王五', '男');	


  DROP TABLE IF EXISTS `score`;
  CREATE TABLE `score`  (
    `id` int(11) ,
    `score` int(11),
    `sid` int(11),
    CONSTRAINT `fk_score_sid` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`) 
  ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of score
  -- ----------------------------
  INSERT INTO `score` VALUES (1, 80, 1001);
  此表的sid是依赖于student表的sid，换句话说，score中的sid只能从student表中的sid中拿值

11.in关键字

作用是查找对应的多条记录，比起原始写法要简单

  select * from stu where sid in('s_1001','s_1002','s_1003');		 
  等价于
  select * from stu where sid='s_1001' and sid='s_1002' and sid='s_1003';


  select * from stu where sid not in('s_1001','s_1002','s_1003');
  等价于
  select * from stu where sid!='s_1001' and sid!='s_1002' and sid!='s_1003';

12.between与distinct

左右都是闭区间
  	select * from stu where age>=20 and age<=40;
  	等价于
  	select * from stu where age between 20 and 40;		
  	
distinct + 字段
	select distinct sal from emp;		
	
	
查看雇员的月薪与佣金之和	
	select *,sal+ifnull(comm,0) as '月薪和佣金之和' from emp;
注意
	任何值与null做加法运算，结果都是NULL，所以使用ifnull进行判断与替换

13.order by

#查询所有学生记录，按年龄升序排序									 表名+order by + 字段
   select * from stu order by age
#降序排序														 表名+order by + 字段 +desc
  select * from stu  order by age desc
#查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序    	 表名+order by + 字段 +desc，字段 asc
  select * from emp order by sal desc,empno asc;											 
#查询工资高于1600的员工记录，并按照薪资进行排序排列(排序默认是升序，所以asc可以不写)			
  select * from emp where sal>1600 order by sal asc

14.聚合函数与limit

#count() 统计指定列不为null的记录行数
    select count(comm) as '有佣金的人数'from emp; 
#count(*) 表示只要这一行，只要有一个数据，而其它字段没有数据，都会被计算的
    select count(*) as '数据数目' from emp; 
#max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
    select max(sal) as '最高工资' from emp;
#min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
    select min(sal) as '最高工资' from emp;
#sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
    select sum(sal) as '员工工资总和' from emp;
#avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
    select avg(sal) as '员工平均工资' from emp;
#查询emp表中月薪与佣金之和大于2500元的人数                  			  
    select count(*) as '月薪大于2500的人数' from emp where sal+ifnull(comm,0)>2500;
    执行流程：
    	  先执行from后面的语句，先从emp这张表中查找大于2500的所有信息，然后再根据查找的是字段，还是别的进行具体输出，这里查找的是count(*)

limit子句  
    第一行的数据下标是0，第二行的下标是1。前一个参数是行数位置，第二个参数是获取多少行
    
#查询一行记录，从0开始，获取1条数据
  	select * from emp limit 0,1;
#查询前三名分数的学生信息
	select * from student order by score desc limit 0,3;

15.多个关键字一起查询

查询语句书写顺序：
    select-from-where-group by-having-order by-limit   即：过滤 分组 分组后过滤 排序 limit
查询语句执行顺序：
	from-where-group by-having-select-order by-limit
	
例子没找到，以后找到再补充吧

16.多表查询

16.1 连接查询

连接查询会产生笛卡尔积

#假设集合A={a,b} 集合 B={0,1,2}则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
  select * from t1,t2;   -- t1中有4条数据 (1,a),(2,b),(3,c),(4,d)  t2中有3条数据(5,d),(6,e),(7,f) 
  会先拿(1,a)与t2中的三条数据拼接起来，再拿(2,b)与t2中的三条数据拼接起来,以此类推
  
#连接查询会进行逐个匹配，所以会产生错误数据，因此需要进行过滤 使用主外键关系作为条件来去除无用信息 
（14条emp，4条dept   所以会产生56条数据）
  	select * from emp,dept where emp.deptno = dept.deptno
#由于有两个表都有deptno，单输出是两个，所以需要去除一个
  	select emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno
#但是这个表名太长了，给表取个短的名字  as关键字是可以省略的
  	select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname,d.loc from emp as e,dept as d where e.deptno = d.deptno

16.2 左连接和右连接

左外连接--- 以左为主，左表全部输出，如果右表满足左表条件输出，不满足左表条件也输出。因为左表是主表，需要把表内的信息全输出
	select * from emp e left outer join dept d on e.deptno = d.deptno

17.主外键关系

创建教师信息表teacher
  	CREATE TABLE IF NOT EXISTS `teacher` (
  	  `tno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '教师编号',
  	  `tname` VARCHAR (20) NOT NULL COMMENT '姓名',
  	  `tsex` VARCHAR (20) NOT NULL COMMENT '性别',
  	  `tbirthday` DATETIME COMMENT '出生日期',
  	  `prof` VARCHAR (20) COMMENT '职称',
  	  `depart` VARCHAR (20) NOT NULL COMMENT '科系'
  	) ;

创建课程表course
  	CREATE TABLE IF NOT EXISTS `course` (
  	  `cno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '课程编号',
  	  `cname` VARCHAR (20) NOT NULL COMMENT '课程名称',
  	  `tno` VARCHAR (20) NOT NULL COMMENT '授课教师编号',
  	  CONSTRAINT fk_course_tno FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
  	) ;


向教师信息teacher表添加数据
  	INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  	VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
  	INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  	VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
  	INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
  	VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
  	INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  	VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
  	INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  	VALUES (832,'小','女','1977-08-14','助教','电子工程系');

删除操作
  	delete from teacher where tname='王萍'          -- 不能被删除
  	由于teacher表的tno被course的tno关联了(course中写了主外键关系)，couse表中的tno是从teacher表中拿的，不能自己创造。
  	所以course表中的tno拿了teacher表中的tno数据，teacher表中的改行tno数据就不能被删除，这里的王萍就不能被删除

18.经典例题

-- 创建学生信息表student
		CREATE TABLE IF NOT EXISTS `student` (
		  `sno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '学号',
		  `sname` VARCHAR (20) NOT NULL COMMENT '姓名',
		  `ssex` VARCHAR (20) NOT NULL COMMENT '性别',
		  `sbirthday` DATETIME COMMENT '出生日期',
		  `class` VARCHAR (20) COMMENT '班级'
		) ;

-- 创建教师信息表teacher
		CREATE TABLE IF NOT EXISTS `teacher` (
		  `tno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '教师编号',
		  `tname` VARCHAR (20) NOT NULL COMMENT '姓名',
		  `tsex` VARCHAR (20) NOT NULL COMMENT '性别',
		  `tbirthday` DATETIME COMMENT '出生日期',
		  `prof` VARCHAR (20) COMMENT '职称',
		  `depart` VARCHAR (20) NOT NULL COMMENT '科系'
		) ;
		
-- 创建课程表course
		CREATE TABLE IF NOT EXISTS `course` (
		  `cno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '课程编号',
		  `cname` VARCHAR (20) NOT NULL COMMENT '课程名称',
		  `tno` VARCHAR (20) NOT NULL COMMENT '授课教师编号',
		  CONSTRAINT fk_course_tno FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
		) ;
		
-- 创建成绩表score
		CREATE TABLE IF NOT EXISTS `score` (
		  `sno` VARCHAR (20) NOT NULL COMMENT '学生学号',
		  `cno` VARCHAR (20) NOT NULL COMMENT '课程编号',
		  `degree` NUMERIC (4, 1) COMMENT '成绩',
		  CONSTRAINT fk_score_sno FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
		  CONSTRAINT fk_score_cno FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
		) ;
		
#------------------------------------------------------------------------------------------------

#向学生student表添加数据
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾华' 
		,'男' ,'1977-09-01','95033');
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明' 
		,'男' ,'1975-10-02','95031');
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王丽' 
		,'女' ,'1976-01-23','95033');
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李军' 
		,'男' ,'1976-02-20','95033');
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳' 
		,'女' ,'1975-02-10','95031');
		INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陆君' 
		,'男' ,'1974-06-03','95031');

#向教师信息teacher表添加数据
		INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
		VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
		INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
		VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
		INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
		VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
		INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
		VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

#向课程course表添加数据
		INSERT INTO course(cno,cname,tno)VALUES ('3-105' ,'计算机导论',825);
		INSERT INTO course(cno,cname,tno)VALUES ('3-245' ,'操作系统' ,804);
		INSERT INTO course(cno,cname,tno)VALUES ('6-166' ,'数字电路' ,856);
		INSERT INTO course(cno,cname,tno)VALUES ('9-888' ,'高等数学' ,831);
		
-- 向成绩score表添加数据
		INSERT INTO score(sno,cno,degree)VALUES (103,'3-245',86);
		INSERT INTO score(sno,cno,degree)VALUES (105,'3-245',75);
		INSERT INTO score(sno,cno,degree)VALUES (109,'3-245',68);
		INSERT INTO score(sno,cno,degree)VALUES (103,'3-105',92);
		INSERT INTO score(sno,cno,degree)VALUES (105,'3-105',88);
		INSERT INTO score(sno,cno,degree)VALUES (109,'3-105',76);
		INSERT INTO score(sno,cno,degree)VALUES (101,'3-105',64);
		INSERT INTO score(sno,cno,degree)VALUES (107,'3-105',91);
		INSERT INTO score(sno,cno,degree)VALUES (108,'3-105',78);
		INSERT INTO score(sno,cno,degree)VALUES (101,'6-166',85);
		INSERT INTO score(sno,cno,degree)VALUES (107,'6-166',79);
		INSERT INTO score(sno,cno,degree)VALUES (108,'6-166',81);


#1 查询score中选学多门课程的同学中分数为非最高分成绩的记录。(即：去掉每门课中最高的分数且是选多门课程的)
		select* from score a where degree <(select MAX(degree) from score b where a.cno=b.cno) AND 
				sno IN(select sno from score group by sno having count(*)>1);
		
		--   a.cno=b.cnp保证这两个表比较的是同一个课程号且已经在b表中选出同一个课程号的最大的分数
		--   对sno进行分组，分完组之后，保证每组的sno个数大于1
		
#2 查询出“计算机系“教师所教课程的成绩表。
		select sno,cno ,degree from score 
			where cno IN 
			(select cno from course where tno IN (select tno from teacher where depart='计算机系'))
			
			course表中的授课教师编号是依赖于teacher表中的教师编号的
			即：可以没有课程编号（外键），但不能没有教师编号（主键）。授课教师编号是依赖于教师编号确定的。没有教师，则一定没有授课教师
			foregin key(tno)  这个就是外键，外键这个语句是写在自己家的，哪个表写哪个表中的字段就是外键。course表写了外键关联语句，则course中的tno就是外键
			外键若要使用了主键中的内容，则主键中被引用的内容就不可以被首先删除
			
#3 查询选修某课程的同学人数多于5人的教师姓名。
		select tname from teacher where tno IN 
			   (select tno from course where cno IN (select cno from score group by cno having count(*)>5))
		--  course中的cno是课程编号，tno是授课教师编号
		--  score中的cno是课程编号，sno是学生学号，学号对应同一课程编号是多对1，只要保证每组中的cno大于5，就可以保证有大于5个学生选这门课
		
			score(sno,cno,degree)VALUES (103,'3-245',86);
		    score(sno,cno,degree)VALUES (105,'3-245',75);
			score(sno,cno,degree)VALUES (109,'3-245',68);
			score(sno,cno,degree)VALUES (103,'3-105',92);
			score(sno,cno,degree)VALUES (105,'3-105',88);
			
	    --  通过找到大于5个学生的课程编号，再通过课程编号可以找到对应的tno授课教师编号，再通过授课教师编号找到对应的tname
	    

#4 假设使用如下命令建立了一个grade表：
		CREATE TABLE IF NOT EXISTS `grade` (
		  `low` INT (3) COMMENT '底限',
		  `upp` INT (3) COMMENT '上限',
		  `rank` CHAR(1) COMMENT '等级'
		) ;

		#向grade表中添加数据
		INSERT INTO grade VALUES(90,100,'A');
		INSERT INTO grade VALUES(80,89,'B');
		INSERT INTO grade VALUES(70,79,'C');
		INSERT INTO grade VALUES(60,69,'D');
		INSERT INTO grade VALUES(0,59,'E');

		#现查询所有同学的sno、cno和rank列。
		select score.sno,score.cno,score.degree,grade.rank from score,grade
			 where score.degree between grade.low and grade.upp;
	


#4 查询至少有2名男生的班号。
		select class from student where ssex='男' group by class having count(*)>1
		
		错误示例
			select class from student group by (select class from student where ssex='男')  having count(*)>1
			
#5 查询所有任课教师的tname和depart.
		select tname,depart from teacher  where tname IN (select DISTINCT tname from teacher,course,score 
			where teacher.tno=course.tno AND course.cno=score.cno)		
			
		-- 根据主外键关系，解决多表查询中出现的笛卡尔积，导致生成错误数据
		-- score中的学生学号sno、课程编号cno
		-- course中的课程编号cno、教师编号tno
		-- teacher中的教师编号tno
		
		-- 需要先找到任课教师，任课教师编号一定在score表中，因为学生学号对应教师编号，有学生则一定有老师，
		-- 再根据从score表中找到的课程编号，使用到course表中，从而找到教师编号。最终输出teacher表中的教师信息

在这里插入图片描述

		-- 当其中的一个表的外键是另一个表的主键，进行笛卡尔积的时候，它们所对应的数据是不会出现匹配错误的问题的
		-- 上表是新生成的一张新表，通过主外键方式进行关联，不会出现数据不匹配问题
		-- 同理，三张表的关联，是第三张表与新生成的表通过主外键的方式进行关联

#6  查询成绩比该课程平均成绩低的同学的成绩表。
		SELECT sno,cno,degree FROM score a WHERE a.degree<(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno)
		-- 原理：如下表所示
		-- 有一点需要注意的是：不会出现子句返回多条语句而抛出错误，原因就是每一轮中子句返回的都是一条语句

在这里插入图片描述

自连接
	  自连接说白了其实就是两张表结构和数据内容完全一样的表，在做数据处理的时候，我们通常会给它们分别重命名来加以区分
      注意：
    	不重命名不行，不然数据库也不认识它们谁是谁，然后进行关联
     
      eg
          select t1.tname,t2.tname from teacher t1,teacher t2
          
  		  输出
  				李诚	李诚
  				王萍	李诚
  				刘冰	李诚
  				张旭	李诚
  				李诚	王萍
  				王萍	王萍
  				刘冰	王萍
  				张旭	王萍
  				李诚	刘冰
  				王萍	刘冰
  				刘冰	刘冰
  				张旭	刘冰
  				李诚	张旭
  				王萍	张旭
  				刘冰	张旭
  				张旭	张旭
  		即先把t1遍历一遍，t2从第一个数据开始不动
  		然后把t2遍历一遍，t2从第二个数据开始不动
  		.....
  		
  所有的自连接都是基于这个，然后对其加上附加条件

	eg2（重要）

  				INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  				VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
  				INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  				VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
  				INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
  				VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
  				INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) 
  				VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

  	    查询“计算机系”与“电子工程系“相同职称的教师的tname和prof。
  		求解
  			
  			这个sql语句的子句，不加a.depar=b.deqprt也可以执行正确结果，供以后探讨
  	  		select tname,prof FROM teacher a where 
				prof IN(select prof from teacher b where a.depart!=b.depart and a.depart=b.depart)

			这个使用到的是自连接
  			select tname,prof from teacher a WHERE prof IN(select t1.prof from teacher t,teacher t1 where t.depart!=t1.depart and t.prof=t1.prof)

        	原理：
        		t.depart!=t1.depart，可以生成得数据有
        		
  		      			计算机系  	电子工程系 
  						计算机系  	电子工程系
  						电子工程系   计算机系
  						电子工程系   计算机系
  						计算机系  	电子工程系
  						计算机系  	电子工程系	助教  助教
  						电子工程系   计算机系
  						电子工程系   计算机系	助教  助教
  						
  				 t.prof = t1.prof  再一次进行过滤	
  						计算机系  	电子工程系	助教  助教
  						电子工程系   计算机系	助教  助教


  #向成绩score表添加数据
  		INSERT INTO score(sno,cno,degree)VALUES (103,'3-245',86);
  		INSERT INTO score(sno,cno,degree)VALUES (105,'3-245',75);
  		INSERT INTO score(sno,cno,degree)VALUES (109,'3-245',68);
  		INSERT INTO score(sno,cno,degree)VALUES (103,'3-105',92);
  		INSERT INTO score(sno,cno,degree)VALUES (105,'3-105',88);
  		INSERT INTO score(sno,cno,degree)VALUES (109,'3-105',76);
  		INSERT INTO score(sno,cno,degree)VALUES (101,'3-105',64);
  		INSERT INTO score(sno,cno,degree)VALUES (107,'3-105',91);
  		INSERT INTO score(sno,cno,degree)VALUES (108,'3-105',78);
  		INSERT INTO score(sno,cno,degree)VALUES (101,'6-166',85);
  		INSERT INTO score(sno,cno,degree)VALUES (107,'6-166',79);
  		INSERT INTO score(sno,cno,degree)VALUES (108,'6-166',81);
  
  #查询成绩比该课程平均成绩低的同学的成绩表（理解很重要）
  select * from score as  s1 where s1.degree<
  	   (select avg(degree) from score as s2 where s1.cno=s2.cno);
  		先拿第一行数据86与子句进行比较，子句中，s1表指针目前指向的是第一行，所以s1.cno是获得s1表中的第一行的数据，
  		然后与s2中的cno进行比较，可以看到这又是一个where语句，它执行过程是拿第一个表中的cno与自身查询的这个表，也就是s2中的数据进行比较，
  		也就是一个是定值(s1.cno)，另一个是变化的(s2.cno)，仿照where degree<80理解，子句中全部比完之后，返回一个结果集，
  		然后s1.degree指向了第二行，s1.cno也是从第二行获取数据，然后又是一个定值和一个变值，返回结果集，以此类推


  select * from score where degree<80; 
  		  先拿第一行数据与80进行比较，把结果进行存储。
  	      拿第二行数据与80进行比较，把结果进行存储
  		  ....
  		  最后全部行比完之后，返回一个结果集

any和all的用法
   	any，all是Mysql当中的逻辑运算符，作用是将子查询返回的单列值的集合与查询的单个值作比较。any，some前面需跟比较运算符
   	比较运算符包括（>, >=, <, <=, !=, =）

all
  	select …from …where c > all(…)
  	表示c列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
  	
any
  	  select…from…where c > any(…) 
  	  表示c列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
      eg
			查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的个人信息,并按degree从高到低次序排序 	
    		select * from score where cno='3-105' and 
    			degree >any(select degree from score where cno='3-245') order by degree desc

函数的用法
	year(参数)函数	        ----参数格式  例如 1972-02-10
	month(参数)函数			----参数格式  例如 1972-02-10
	
	查询student表中每个学生的姓名和年龄。
  			select sname,year(NOW())-year(sbirthday) from student
    查询student表中每个学生的姓名和生日的月份。
  			select sname,month(sbirthday) from student

19.生成sql脚本导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，
但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！
所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

   mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

在这里插入图片描述

现在可以在D盘下找到qing.sql文件了！

注意:
	mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

20.执行sql脚本恢复数据

前提：必须先创建数据库名


执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！
执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！
大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！
SOURCE C:\mydb1.sql

在这里插入图片描述

还可以通过下面的方式来执行脚本文件：
  	mysql -uroot -p123 mydb1<c:\mydb1.sql
  	mysql –u用户名 –p密码 数据库<要执行脚本文件路径

    这种方式无需登录mysql！

    注意：
  		在CMD下 命令不能加分号

20.union与union all

create table t1(
  a int(1),
  b varchar(2)
  );
  
create table t2(
  c int(1),
  d varchar(2)
  );

  INSERT INTO `t1` VALUES (1, 'a');
  INSERT INTO `t1` VALUES (2, 'b');
  INSERT INTO `t1` VALUES (3, 'c');
  INSERT INTO `t1` VALUES (4, 'd');

  INSERT INTO `t2` VALUES (4, 'd');
  INSERT INTO `t2` VALUES (5, 'e');
  INSERT INTO `t2` VALUES (6, 'g');


union 去除重复记录 （被合并的两个结果：列数于列类型必须相同）
 	select * from t1 union	select * from t2;
 	
  	 输出内容
  	    	1	a
  			2	b
  			3	c
  			4	d
  			5	e
  			6	g
  			
union all 不去除重复记录（被合并的两个结果：列数于列类型必须相同）
    select * from t1 union all select * from t2;
  	  		1	a
  			2	b
  			3	c
  			4	d
  			4	d
  			5	e
  			6	g


  查询所有教师和同学的name、sex和birthday.（输出的列数相同）
  SELECT DISTINCT sname AS NAME,ssex AS sex,sbirthday AS birthday FROM student 
  UNION 
  SELECT DISTINCT tname AS NAME,tsex AS sex,Tbirthday AS birthday FROM teacher