javaSE基础:MySql基础

目录

1、数据基础知识

2、DML

3、DQL

4、DCL

5、约束

6、多表查询

---

1、数据基础知识

数据库：DataBase    DB
含义：用于存储和管理数据的仓库
特点：1.持久化存储数据，数据库就是一个文件系统
2.方便存储和管理数据
3.使用统一的方式SQL（Structured Query Language）
4.常见的数据库软件：Oracle    MySQL    Microsoft SQL Server
Table（表）：
Field（字段）、Row（行）、Column(列)、
主键（primary Key）：数据行的唯一标识，别的行不会重复，不能为空（null）
业务主键，逻辑主键（推荐）

1、标识符：（1）、第一个字符字母，下划线，#，@
（2）、不能有空格，特殊字符
（3）、若是保留字要有【】 或“”    如【table】"my table"

2、所有者：dbo(默认用户)  guest
3、存储结构：逻辑存储结构
物理存储结构：一个数据库至少包含一个数据文件和一个事物日志文件.log
4、（1）数据库文件：主要数据文件.mdf Primary File    次要数据文件.ndf Secondary File    事务日志文件.ldf Transaction Log
(2)数据库文件组（File Group）
5、规则：文件与文件组不能有一个以上数据库使用
data和.ldf不属于同一文件或文件组
.ldf不属于任何文件组
一个file只能存在于一个group
6、字符加''
句完加，

一般在属性名，表名等自定义名称加上``        【在~键的引号】

7、windows系统下MySql是不区分大小写的，也仅限于windows系统。

/*SQL分类
DDL(data definition language)数据定义语言 定义数据库对象：creat,drop,alter

DML(data mainipulation language)数据操作语言 增删改表中的数据：insert,delete,update

DQL(data query language)数据查询语言 查询表中的数据：select,where

DCL(data control language)数据控制语言：grant,revoke

1.操作数据库：CRUD
creat:创建
	create database 数据库名称
	*创建数据库并判断是否存在
	create database if not exists 数据库名称；
	*创建数据库并指定字符集
	create database 数据库名称 character set 字符集；
	*练习：创建db1数据库，判断是否存在，并制定字符集为gbk
	create database if not exists db1 character set gbk；
retrieve[检索；查询；重新得到]:查询
	show databases（查询所有数据库名称）
	show create database 数据库名称（查询数据库的创建语句/查询数据库字符集）
update:修改
	*修改数据库字符集
	alter database 数据库名称 character set 字符集名称；
delete:删除
	*删除数据库
	drop database 数据库名称；
//使用数据库
	*查询正在使用的数据库
	select database();
	*使用数据库
	use database
2.操作表
C
	creat table 表名称(
	列名1	数据类型1，
	列名2	数据类型2，
	………………
	)；最后一列不需要加逗号
	*复制表
	creat table 表名 like 被复制的表名
R
	*查询数据库的所有表名称
	show tables;
	*查询表结构
	desc 表名；
U	修改表名
	alter table 表名 rename to 新表名
	修改表字符集
	alter table 表名 character set 字符集名称；
	添加一列
	alter table 表名 add 列名 数据类型；
	修改表某一列名称，类型
	*改列名并改数据类型		alter table 表名 change 列名 新列名 新数据类型； 
	*改数据类型				alter table 表名 modify 列名 新数据类型；
	删除列
	alter table 表名 drop 列名；

D	
	*drop table 表名；
	*drop table if exists 表名；
*/
 /*
数据类型：
int:整数类型
	age int,
double:小数类型
	score double(小数最多多少位，小数点保留多少位)，
date:日期
		只包含年月日，yyyy-mm-zz
datetime:日期		
		包含年月日时分秒，yyyy-mm-zz hh:ii:jj
timestamp:时间戳类型，yyyy-mm-zz hh:ii:jj
		如果将来不赋值，则自动使用系统当前时间
varchar:字符串
			name varchar(n)：最大n个字符
			
 */

2、DML

/*DML：								sql server没有double
1.添加数据			
	*insert into 表名（列名1，列名2，……列名n） values（值 1，值2，……值n）；
	注意：省略列名，默认给所有字段挨个添加值，少了就报错
	除了数字类型，要用引号''
2.删除数据
	*delete from 表名 【where 条件】		SQL中的【】可以省略
	如果不加条件，则删除表中所有记录（不推荐使用：效率低）
	*truncate table 表名		删除表，然后创造一模一样的空表
3.修改数据 
	*update 表名 set 列名1=值1，列名2=值2…………【where 条件】
	注意：不加条件，会修改所有行的数据



 */

3、DQL

/*
DQL:
	select * from 表名；
	
	语法：
	select 
		字段列表							
	from
		表名列表																	
	where	
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	identity（初始值，每次增加多少）
		分页限定
	
	基础查询：
	1.查询多个字段
		select 字段名1，字段名2………from 表名;
				SELECT name,age FROM student--查询姓名，年龄列
	2.去除重复：
		select distinct 字段名1，字段名2………from 表名;
				SELECT DISTINCT address FROM student;--去除重复结果集
	3.计算列
				
	4.起别名
	AS 可以省略										
	--计算math与english的和【相加前提：是可以转换为数值类型的数据 且与我们的目的相同
							如：字符串类型+数值类型 ：只起连接作用】
				SELECT name,math english,math+english FROM student;	
				SELECT name,math english,math+english AS 总分 FROM student;
				SELECT name,math 数学,english 英语,math+english 总分 FROM student;
	
	条件查询：
1.where后跟条件
				--查询年龄大于20
				SELECT * FROM student WHERE age>20;
				--查询年龄大于等于20小于等于30
				SELECT * FROM student WHERE age>=20 AND age<=30;
				SELECT * FROM student WHERE age BETWEEN 20 AND 30;
				--查询年龄22,19,25
				SELECT * FROM student WHERE age=22 OR age=19 OR age=25;
				SELECT * FROM student WHERE age IN (22,19,25);
				--查询英语缺考的
				SELECT * FROM student WHERE english is null;
				SELECT * FROM student WHERE english is not null;
				--null值转换
				SELECT ISNULL(english,0) FROM student
				SELECT ISNULL(english+math,0+math) FROM student	--如果english是null值，转换成0




	2.运算符：
	与或非:
				AND OR 
	大于等于小于：
				等于=	不等于<>或！=
	in(集合) 
	between…and……
	like模糊查询
	*like占位符_：单个任意字符
				%：0或多个任意字符
					SELECT * FROM student WHERE name like'马%';
					SELECT * FROM student WHERE name like'_化%';

					SELECT * FROM student WHERE name like'___';		

					SELECT * FROM student WHERE name like '%马%';

	
	is null 
					--null值转换
					SELECT IFNULL(english,0) FROM student
					SELECT IFNULL(english+math,0+math) FROM student	--如果english是null值，转换成0

	
	
	3 交并差运算
			交运算：
					例题：利用交运算，查询彭欢和朱志奇老师教授的同一门课名字
					select a.course_name
					from course a join teacourse b
					on a.course_id=b.course_id
					join teacher c
					on b.tea_id=c.tea_id
					where teacher_name='彭欢'
					intersect
					select a.course_name
					from course a join teacourse b
					on a.course_id=b.course_id
					join teacher c
					on b.tea_id=c.tea_id
					where teacher_name='朱志奇'
			并运算：多个结果集合并成一个【字段类型兼容】
					例题：利用并运算，查询所以老师和学生的名字
					select student_name as 老师和学生名字 from student
					union 【union all 去除重复行】
					select teacher_name from teacher
			差运算：
					例题：利用差运算，查询彭欢老师教授，但朱志奇老师没上的课程名字			
					select a.course_name
					from course a join teacourse b
					on a.course_id=b.course_id
					join teacher c
					on b.tea_id=c.tea_id
					where teacher_name='彭欢'
					except
					select a.course_name
					from course a join teacourse b
					on a.course_id=b.course_id
					join teacher c
					on b.tea_id=c.tea_id
					where teacher_name='朱志奇'
 */
/*
DQL:
1.排序查询
		*order by 排序字段1 排序方式1，排序字段2 排序方式2
	ASC	或默认 升序									SELECT * FROM student ORDER BY math ASC;
													SELECT * FROM student ORDER BY math DESC
	DESC 降序										--按照数学排，如果数学相等按照英语排
													SELECT * FROM student ORDER BY math ASC,english ASC;*（第一条件不符，再排第二个）
	
		

2.聚合函数 （不能出现在WHERE语句，只能出现在HAVING语句和子查询中）
	将列数据作为一个整体，进行纵向运算
	count:计算个数									SELECT COUNT(name) FROM student
			一般选择非空，主键
			count(*)
	max:计算最大值
													SELECT MAX(ISNULL(english,0)) FROM student
	min：计算最小值
	sum：求和										SELECT SUM(math) FROM student
													
	avg：平均值										
	注意：计算会排除null值，所以要进行null值转换	SELECT COUNT(ISNULL(english,0)) FROM student

3.分组查询:统计具有相同特征的一组数据
		语法：group by 列名		一般另一个列涉及计算（列名与需要计算的列可以不是同一张表，但要有所关联）
		*SELECT dept_id,COUNT(id)
		FROM 表名
		GROUP BY dept_id

		
		即 按照该列名下 行数据的相等的数据，分为一行（组）进行具体计算，一般在select语句中把该列名写在涉及计算列名前，方便观察。		
		注意：1.分组之后查询的字段：分组字段或聚合函数		--查询男女同学平均分，个数
*where与having的区别								SELECT sex,AVG(math)FROM student GROUP BY sex
					SELECT sex,AVG(math) 数学平均分,COUNT(id) 个数 FROM student GROUP BY sex
					--查询男女同学平均分，如果分数低于70不参与运算
					SELECT sex,AVG(math) FROM student WHERE math>70 GROUP BY sex
					--查询男女同学平均分，如果分数低于70不参与运算,分组之后人数要大于2
					SELECT sex,AVG(math) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2
			1.where分组之前要进行限定，如果不满足条件则不参与分组。having在分组之后进行限定，如果不满足结果则不会被查询出来。
			2.where后不能跟聚合函数判断
		
	

4.分页查询（方言）
		语法：limit 从哪开始查的索引，每页查询的条数（只适用于MySQL）
					CREATE TABLE hi1(--创建表的同时，增加自定增长
					id int identity(1,1) primary key,
					name varchar(20) 	
					ALTER TABLE hi	--修改成自动增长
					drop column id
					GO
					alter table hi
					add id int identity(1,1)
					GO									




 */

4、DCL

/*
DCL:
    DBA：数据库管理人员
    管理用户：
        添加：CREAT USER '用户名'@'主机名'IDENTIFIED BY'密码'
        删除：DROP USER '用户名'@'主机名'
        修改：--修改lisi用户密码
            UPDATE USER SET PASSWORD=PASSWORD('新密码')WHERE USER='用户名'；
             --忘记密码
            1.cmd --net stop mysql        管理员运行
            2.使用无验证启动mysql服务
            mysql --skip-grant tables
            3.打开新cmd窗口
            输入mysql
            4.修改密码 后关闭两个窗口
            5.在任务管理器停止mysqld.exe进程
            6.net start mysql
            
            
            
        查询：1.切换到SQL数据库
                2.SELECT * FROM USER
                *通配符：%表示可以在任意主机使用用户登录数据库
    权限管理：
        查询权限：
            SHOW GRANTS FOR '用户'@'主机名'
        授予权限：
            grant 权限列表 on 数据库名 to '用户名'@'主机名'
        收回权限：
            revoke 权限列表 on 数据库名.表名 FROM '用户名'@'主机名'

 */

5、约束

/*约束：
	对表中的数据进行限定，保证表的正确性、有效性、完整性
主键约束PK（MySQL）：primary key
	*ALTER TABLE 表名
	ADD CONSTRAINT 约束名 primary key(列名)【约束名一般为英语字母加下划线加表名】
唯一性约束UQ（MySQL）：unique
	值不能重复，包括NULL（Mysql中，不包括null）
检查约束：check
默认约束：default
外键约束（MySQL）：foreign key
	当一个表的数据大量重复有冗余时，可以拆成两个表，让这两个表关联一下
		*添加外键
		
		*外键级联：两个表可以一起改动
		级联更新：on update cascade
		级联删除:on delete cascade
	
	
非空约束（MySQL）： not null					--将student的id定为非空
	*ALTER TABLE 表名							ALTER TABLE student
	ALTER COLUMN 列名 数据类型 not null			alter column id int not null 
												alter column id int null 
												--将student的id定为主键
												ALTER TABLE student 
												add constraint pk_student primary key(id)
												ALTER TABLE student 
												drop constraint pk_student		--删除外键约束 
												CREATE TABLE stu(				--创建唯一约束
												id int,
												phone_number varchar(20) UNIQUE）
												ALTER TABLE hi
												ADD CONSTRAINT uq_NAME unique(NAME)
	*如果一列是int，使用identity(初始值，每次增加值)或AUTO_INCREMENT完成自动增长，一般是主键			CREATE TABLE hi1(--创建表的同时，增加自定增长
																						id int identity(1,1) primary key,
																						name varchar(20) 	
																						ALTER TABLE hi	--修改成自动增长
																						drop column id
																						GO
																						alter table hi
																						add id int identity(1,1)
																						GO					*/																	

6、多表查询

/*
一、多表查询：join on
		*select 列名	from 表明列表	where……								                    
        SELECT * FROM a,b		
                        --笛卡尔积 比如集合a,b a有两条数据，b有三条数据	则a,b有3*2*1条数据                
		多表查询分类：内连接查询：查询左右表交集部分
						隐式内连接：使用where消除无用条件		
                        SELECT * FROM emp,dept WHERE emp.dept_id=dept.id
					    --查询员工表的名称，性别，部门表名称
						SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE
                                                               emp.dept_id=dept.id     
						SELECT --起别名，先写from再写select
													t1.name,t1.gender,t2.name
                                                    FROM
													emp t1,
													dept t2							
													WHERE
													t1.dept_id=t2.id							                                            	
						--查询入职日期在2011-11-11之后员工信息和部门信息
													SELECT * FROM emp t1,dept t2 WHERE                 						       																			
                                                  t1.dept_id=t2.id AND 
                                                    t1.join_date>'2011-11-11'
						显式内连接：
						*select 字段列表	from 表名1 【inner】可省略 join 表名2	on	连接条件
								SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id
						        注意：从那些表中查	查询条件	查询哪些字段
									
					外链接查询
					左外连接（内连接inner变成lift）：查询的是左表所有数据及交集部分
						*select 字段列表	from 表名1	left 【outer】 join 表2 on 连接条件
								
					右外连接：查询的是右边所有数据及交集部分			
                                            

					完全外连接【mysql不支持，可用union替代】：
                        查询所有数据，不满足连接条件的部分用null补齐					
						*select 字段列表	from 表名1	 full【outer】 join 表2 on 连接条件	
                    							
																					
//====================================================================================================================================================================================
				 二、子查询：子查询结果是单行单列的，可以做条件	【用=判断条件】					
										--查询工资最高员工信息
							子查询结果是多行单列的，可以做条件【可以加in或or判断条件】				
										SELECT MAX(salary) FROM emp;//一组单行单列的数据
							子查询结果是多行多列的，可以看成一个虚拟表【加（）】					
										SELECT * FROM emp WHERE emp.salary=9000
																两步合一步：	
										SELECT * FROM emp WHERE emp.salary=(SELECT MAX(salary) FROM emp);
										--查询工资小于平均数的人信息	单行单列
										SELECT * FROM emp WHERE emp.salary<(SELECT AVG(salary) FROM emp)
							查询同时还要判断是否存在情况 exists/not exists
										--如果7号课程已经开课，查询选修了课程号为7的学生学
                                                                     号，姓名和联系电话                    
										Select StdInfoNum,StdInfoNmae,StdInfoTel
										from dbo.StudentInfo
										where exists(
										select * from dbo.StudentCourse
										where StdInfoID=dbo.StudentInfo.StdInfoID
										and exists(
										select * from dbo.TeachCourse
										where CourseInfoID=7 and 
										TeachCourseID=dbo.StudentCourse.TeachCourseID
										)
										)
											
			单行单列：子查询可以作为条件，使用正常运算符
			多行单列：使用运算符in或all或any来判断
							--查询财务部和市场部员工信息	多行单列
							select dept_id from dept where name='财务部'OR name='市场部'//        一组多行单列的数据：3 ,2
							SELECT * FROM emp WHERE dept_id IN(3,2) /Select * FROM emp where dept_id=3 OR dept_id=2
							SELECT * FROM emp WHERE dept_id IN(SELECT id as dept_id FROM dept where name='财务部'OR name='市场部') --as可省略
							--查找比所有课程类别ID为1的课程学时要高的课程名和课程种类
							select CourseInfoName,CourseTypeID
							from dbp.CourseInfo
							where CourseInfoTime > all
							(select CourseInfoTime from dbo.CourseInfo
							where CourseTypeID=1)
											
			多行多列：子查询作为虚拟表参与查询
							--查询入职日期在2011-11-11之后员工信息和部门信息
							SELECT * from emp where emp.join_date >'2011-11-11'//一组多行多列的数据
							
							SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date>'2011-11-11'
							) t2 WHERE t1.id=t2.dept_id	
							//转化为普通内连接也就是
							SELECT * from emp a,dept b where a.id=b.id AND a.join_date >'2011-11-11' 
							--查询所有已获得副教授职称的女教授的姓名，学历，专业，职称
							Select * from (
							select TeachInfoName,TeachInfoDegree,TeachInfoSpec,TeachInfoTitle
							from dbp.TeachInfo as t1
							where TeachInfoTitle='副教授') as t2
							where t1.TeachInfoSex='女'




 */
USE STUMS
CREATE TABLE dept(
id int identity(1,1) primary key,
name varchar(20)
)
INSERT INTO dept (name) 
VALUES('开发部'),('市场部'),('财务部')
…………………………………………………………………………………………………………………………………………………………………………………………………
 CREATE TABLE emp(
id int identity(1,1) primary key,
name varchar(20),
gender char(1),
salary float, --工资
join_date date,
dept_id int,
foreign key(dept_id) references dept(id) 
)
INSERT INTO emp(name,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(name,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(name,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(name,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-07-01',3);
INSERT INTO emp(name,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2000-06-16',1);