【mysql】数据库连接查询，约束

一、数据库整体总结

1.数据库的了解DB、DBMS、DBA作用
2.mysql的安装、安装程序、压缩版，卸载方式
3.基本命令
mysql -u root -p
password:

sql类型：
DDL、DML、DQL、DCL --

DDL：
	create  alter drop table  database
	查看数据库：show  databases;
	create database；
	进入数据库  use 库名；
	create table 表名
	show tables；
	desc table；
	查看字段：show colunms from 表名
	drop table 表名；
	drop database 库名；
	alter table  表名  add/change/modify/drop/rename
	#注意：change和modify  后面接的字段写法
#mysql数据类型：
	1.数字类型
		int  double  float  decimal、
	2.时间
		date  datetime
	3.字符串
		varchar  char  text  blob(二进制)
	#注意：char和varchar
DML：
	insert into  。。。、update、delete from。。。
    1.单行插入、多行插入
    2.update 表名 set 字段名=‘更新的值’  where  。。
      where条件  ---一次修改一个字段
    3.delete from 表名 where。。。  根据where限制的条件进行删除操作
DQL：*****
	select 语句
	select * from 表名;
	1.起别名  as （as是可以省略的）
	select 字段1 as 别名 from 表名  
	2.去重 distinct  
	select 去重 字段
    3.排序 order by 
    升序  降序
    执行顺序
    4.条件查询  *****
    where  逻辑条件  and  or  ！=
    	   比较条件：<,>,<>/!=,<=,>=, is null , is not null = between.. and ...(闭区间，前面小的，后面大的)
    模糊查询：
    	like  %  _  (限制字符)
    	not like 
    5.分支查询：
    	case 
    		when 条件1 then 结果1
			when 条件2 then 结果2    	
			when 条件3 then 结果3
			else 默认结果
		end
	6.聚合函数
		sum（）求和
		avg（）平均值
		max（）最大值
		min（）最小值
		count（）计数
	7.子查询（掌握）
		两个查询链接在一起
	
		连接接查询
	8.时间查询
	9.字符串查询
	10.函数查询
		

二、连接查询（补充）

合并查询：
#注意：使用union进行合并结果集，会去除两张表中重复的数据
并集的效果
#1.内连接（inner join on）
表连接查询
内连接组合两张表，并且基于两张表中的关联关系来连接它们。使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。
select 列名 from 表名1 连接方式 表名2 on 连接条件
内连接有两个标准 （sql）
#查询所有有部门的员工信息（不包含没有部门的员工）
select * from t_employees inner join t_jobs on t_employees.job_id = t_jobs.job_id;
#注意：我们交点的地方永远是数据
#经验：在mysql中，第二种的形式可以作为内链接查询，但是不符合sql标准，但是能用。
内连接有两个标准 （mysql）
 select * from t_employees,t_jobs where t_employees.job_id =t_jobs.job_id;
#2.外连接

sql99：左外连接，右外连接，全外连接（mysql编写不了）。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。
#3.左外（left join on）
#查询所有员工的信息，以及对应的部门名称（没有部门也在查询之中 名称以null添加）
mysql> 
select t_employees.employee_id,t_employees.first_name,t_employees.salary,t_departments.department_name from t_employees  left join t_departments on t_employees.department_id = t_deaprtments.department_id;

说明：左外连接以左边的表为准，一次向右边进行匹配。匹配到了字段，那么显示，那么没有匹配到，用null填充
#4.右外（right join on）


#5.全外（mysql中  union）

#6.三个表 join on（当作了解）
#查询所有员工的员工号，名字、部门名称、所在国家ID
 select * 
 from t_employees as e inner join t_departments as d on e.department_id =   d.department_id 
 inner join  t_locations as l on l.country_id = d.country_id;
#注意：三个表连接的话  要先找到中间的表 

详细整合说明

主题：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#数据自行插入

一、内链接：select * from a_table a inner join b_table bon a.a_id = b.b_id;
组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

二、外连接：
应用场景：用于查询一个表中有，另一表中没有的记录
左外连接
关键字：left join on / left outer join on
语句：select * from a_table a left join b_table bon a.a_id = b.b_id;
说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

三、右连接（右外连接）
关键字：right join on / right outer join on
语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;
说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

三、约束

---

• 问题：在往已创建表中新增数据时，可不可以新增两行相同列值得数据？
• 如果可行，会有什么弊端？

1 实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复、实体唯一。

1.1 主键约束

PRIMARY KEY 唯一，标识表中的一行数据，此列的值不可重复，且不能为 NULL

#为表中适用主键的列添加主键约束
CREATE TABLE subject(
	subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为 NULL
  subjectName VARCHAR(20),
  subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);#error 主键 1 已存在

1.2 唯一约束

UNIQUE 唯一，标识表中的一行数据，不可重复，可以为 NULL

#为表中列值不允许重复的列添加唯一约束
CREATE TABLE subject(
	subjectId INT PRIMARY KEY,
  subjectName VARCHAR(20) UNIQUE,#课程名称唯一！
  subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(2,'Java',40);#error 课程名称已存在

1.3 自动增长列

AUTO_INCREMENT 自动增长，给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，和主键配合。

#为表中主键列添加自动增长，避免忘记主键 ID 序号
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长，会从 1 开始根据添加数据的顺序依次加 1
  subjectName VARCHAR(20) UNIQUE,
  subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectName,subjectHours) VALUES('Java',40);#课程编号自动从 1 增长
INSERT INTO subject(subjectName,subjectHours) VALUES('JavaScript',30);#第二条编号为 2

三、域完整性约束

限制列的单元格的数据正确性。

3.1 非空约束

NOT NULL 非空，此列必须有值。

#课程名称虽然添加了唯一约束，但是有 NULL 值存在的可能,要避免课程名称为NULL
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT
)charset=utf8;

INSERT INTO subject(subjectName,subjectHours) VALUES(NULL,40);#error，课程名称约束了非空

3.2 默认值约束

DEFAULT 值 为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

#当存储课程信息时，若课程时长没有指定值，则以默认课时 20 填充
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT DEFAULT 20
)charset=utf8;

INSERT INTO subject(subjectName,subjectHours) VALUES('Java',DEFAULT);#课程时长以默认值 20 填充

3.3 引用完整性约束

• 语法：CONSTRAINT 引用名 FOREIGN KEY（列名） REFERENCES 被引用表名(列名)
• 详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

#创建专业表
CREATE TABLE Speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
	SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

#创建课程表(课程表的SpecialId 引用专业表的 id)
CREATE TABLE subject(
	subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT DEFAULT 20,
  specialId INT NOT NULL,
  CONSTRAINT fk_subject_specialId  FOREIGN KEY(specialId) REFERENCES Speciality(id)  #引用专业表里的 id 作为外键，新增课程信息时，约束课程所属的专业。
)charset=utf8;

#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES('Java');
INSERT INTO Speciality(SpecialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',30,1);#专业 id 为 1，引用的是专业表的 Java
INSERT INTO subject(subjectName,subjectHours) VALUES('C#MVC',10,2);#专业 id 为 2，引用的是专业表的 C#

• 注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）

10.3 约束创建整合

创建带有约束的表。

10.3.1 创建表

列名	数据类型	约束	说明
GradeId	INT	主键、自动增长	班级编号
GradeName	VARCHAR(20)	唯一、非空	班级名称

CREATE TABLE Grade(
	GradeId INT PRIMARY KEY AUTO_INCREMENT,
	GradeName VARCHAR(20) unique NOT NULL
)CHARSET=UTF8;

列名	数据类型	约束	说明
student_id	VARCHAR(50)	主键	学号
Student_name	VARCHAR(50)	非空	姓名
sex	CHAR(2)	默认填充’男’	性别
borndate	DATE	非空	生日
phone	VARCHAR(11)	无	电话
GradeId	INT	非空，外键约束：引用班级表的 gradeid。	班级编号

CREATE TABLE student(
  student_id varchar(50) PRIMARY KEY,
  student_name varchar(50) NOT NULL,
  sex CHAR(2) DEFAULT '男'
  borndate date NOT NULL,
  phone varchar(11),
  gradeId int not null，
  CONSTRAINT fk_student_gradeId  FOREIGN KEY(gradeId) REFERENCES Grade(GradeId)  #引用Grade表的GradeId列的值作为外键，插入时约束学生的班级编号必须存在。
);

• 注意：创建关系表时，一定要先创建主表，再创建从表
• [删除关系表时，先删除从表，再删除主表。]

IDEA的使用

    /*
    * 字体大小的改变
    * file----->settings---->Font--->调整  直接ok  向Apply再ok
    *
    * 背景颜色
    *
    * 创建模板
    *
    * 快捷键
    *
    * */
   IDEA创建带前缀的包名方式：
   	1.选中项目中的src右键new，找到package
   	2.输入带前缀的包名（例如：com,it.qf.test）
   	3.再次选中src,重复1，2步骤，输入（com.itqf.testX）
   	4.创建完成之后，形成带前缀名的包
   //注意  
   		//如果你的jdk不在你的C盘默认的位置，那么创建项目时的jdk不会主动的识别，需要手动加入。
   		
//简单使用
    //    快捷键   alt + 回车  万能键
//    主函数的快捷键main
//    运行：1.选中包中的文件  右键找到run
//    2.主函数运行
//    3.工具运行（文件执行了一次之后）
    public static void main(String[] args) {

        System.out.println("hello world");

    }

四、JDBC的介绍

4.1.如何使用Java代码操作数据库

使用客户端工具访问数据库，需要我们手动建立连接，输入对应的用户名和密码，编写sql，点击执行，查看最终的效果（受影响的行数）

4.2实际操作中，采用的方式

实际操作过程中，当用户的数据发生改变的时候，不可能通过客户端进行修改对应的sql。数据量比较大的时候，操作起来也是复杂的。

4.3什么是JDBC？

JDBC(Java Databaes Connectivity):只存在于Java，也是Java连接数据库的的规范，根据特定的厂家实现驱动。

是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

4.4mysql的驱动

mysql-connector-java-5.1.40.jar mysql5.XX的工具jar

mysql给与我们的连接数据的工具箱

我们导入jar和使用jar包过程，实质上就是打开工具箱进行工具的拿取

导入jar包的步骤：

​ 1.需要建立一个名为lib的文件夹（位置一般放在web项目的web-info下）

​ 2.找到mysql的驱动jar包，

​ 3.将jar包放入到lib文件中

​ 4.右键选中jar包，下拉找到Add as libiary，点击，选择OK即可

4.5jdbc的核心思想

Java中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式，由数据库厂商土工的驱动（Driver数据库驱动）

4.6JDBC的核心API

他的核心全部由接口和类组成

实质：利用jdbc进行连接的方式就是实现或者调用数据库的这些接口和类

class	java.sql.DirverManager	管理多个数据库的驱动类，提供了获取数据库连接的方法
interface	java.sql.connection	代表一个数据库的连接（当connection不是null的时候，代表已有连接）
interface	Statement	发送sql语句执行到数据库
interface	ResultSet	结果集。保存sql执行完毕之后的结果数据
class	SQLException	c处理数据库应用的时候出现的异常

总结：
	Dirver  数据库驱动  
	基本的实现需要利用反射进行
	
	connection：数据库连接  需要利用驱动进行使用连接
	
	
	statement  当连接成功的时候，利用它进行sql的实现
	
基本实现jdbc连接的步骤：
	    //    1.加载驱动
    Class.forName("com.mysql.jdbc.Driver");
//    2.获取连接
//    url:统一资源定位符  遵循jdbc的格式
//    jdbc:驱动类型://本机地址：端口号/数据库的名字 
//    jdbc:mysql://localhost(127.0.0.1):3306/库名   ====  url
//    root   用户名
//     root    密码
//    https://www.baidu.com/
    Connection connection = DriverManager.getConnection("","","");

//    3.获取执行sql的对象

//    4.执行sql

//    5.处理结果

//    6.释放资源