Mysql和JDBC简单总结知识点

JavaWeb
什么是JavaWeb？
使用Java语言开发互联网项目，简单理解：使用Java语言开发网站

数据库 DateBase 简称：DB
什么数据库？
用于存储和管理数据的仓库
数据库的特点：
1.持久化存储数据的，其实数据库就是一个文件系统
2.方便存储和管理数据
3.使用了统一的方式操作数据库 SQL

MySQL ：开源免费的数据库，小型的数据库，已经被Oracle收购，MySQL6.x版本也开始收费

MySQL的卸载
1.去MySQL的安装目录找到my.ini文件
datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
2.卸载mysql

3.删除C:/ProgramData目录下的MySQL文件夹

MySQL服务 后天程序 没有页面
通过：window10 控制面板->系统和安全->管理工具->服务
第二种：
win+r service.msc 也可以打开服务
net stop mysql 命令：结束MySQL的服务
net start mysql 命令：启动MySQL的服务

MySQL登录：
1.mysql -uroot -p密码
2.mysql -hip -uroot -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
MySQL退出：
1.exit
2.quit


MySQL的目录
1.mysql安装目录
配置文件 my.ini
2.MySQL数据目录
数据库：文件夹
表：文件
数据：文件的内容
SQL
什么是SQL？
structured Query Language:结构化查询语言
其实就是定义了操作所有关系性数据库的规则（DBMS）
每一种数据库操作的方式存在不一样的地方 称为方言

SQL通用语法
1.SQL语句可以单行或多行书写，以分号结尾
2.可以使用空格和缩进来增加语句的可延续
3.MySQL数据的SQL语句不区分大小写，关键字建议使用大写
3中注释
单行注释：-- 注释内容 或 # 注释内容 （MySQL特有）
多行注释：/* 注释 */
把光标移到SQL语句中，按F9就会执行SQL语句

SQL分类
1.DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表 列表 关键字：create drop alter 等
2.DML(Date Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改，关键字：insert delete update 等
3.DQL(Date Query Language)数据查询语言
用来查询数据库中表的记录(数据)，关键字：select where 等
4.DCL(Date Control Language)数据控制语言
用来定义数据库的访问权限和安全级别，以及创建用户，关键字：GRANT REVOKE 等。

DDL：操作数据库，表
1.操作数据库：CRUD
1.C(create ):创建
create database 数据库名称；
创建数据库，判断不存在，在创建；
create database if not exists 数据库名称；
创建数据库，并指定字符集
create database 数据库名称，character 字符集名称
创建db4数据库，判断是否存在，并制定字符集gbk
create database db4 if not exists db4 character set gbk;
2.R(Retrieve)：查询
查询所有数据库的名称：
show databases；
查询某个数据库的字符集，查询某个数据库的创建语句
show create database 数据库名称；
3.U(update):修改
修改数据库的字符集
alter database 数据库名称 character set 字符集名称；
4.D(Delete)：删除
删除数据库
drop database 数据库名称；
判断数据库存在，存在在删除
drop database if exists 数据库名称；
5.使用数据库
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称；
2 操作表

1.C(create ):创建
语法：
create table 表名(
列名1 数据类型1，
列名2 数据类型2，
…
列名n 数据类型n
)；
数据类型：
1.int :整数类型
age int,
2.double :小数类型
score double(5,2)
3.date 日期，只包含年月日，yyyy-MM-dd
4.datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timestamp:时间错类型， 包含年月日时分秒， yyyy-MM-dd HH:mm:ss
注意：如果将来不给这个字段赋值，或赋值为null 则默认使用当前系统时间，来自动赋值
6.varchar:字符串
name varchar(20):姓名最大10个字符
zhangsan 8个字符 张三 2个字符
创建表：

 create table nihao(
  id int,
  name varchar(32),
  age int,
  socre double(4,1),
  birthday date,
  insert_time timestamp
);

注意：最后一列，不需要逗号。

 1. 只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;或CREATE TABLE 新表 LIKE 旧表 ;
注意上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。
2.复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
3.复制旧表的数据到新表(假设两个表结构一样) 
INSERT INTO 新表 SELECT * FROM 旧表
4.复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

DDL(查询数据&表名，修改表名，增加列，删除表)
1.R(Retrieve)：查询
查询某个数据库中所有的表名称
show tables;
查询表结构
desc 表名；
2.U(update):修改
修改表名：

 	 alter table 表名 rename to 新的表名；
	 alter table student rename to stu;
  修改表的字符集
	 alter table 表名character set 字符集
   添加一列
  	  alter table 表名 add 列名 数据类型；
  	   alter table student add nihao varchar(21);

修改列名称 类型

alter table 表名 change 列名 新列名 新数据类型；
ALTER TABLE nihao5 CHANGE xingming xm VARCHAR(32);
这是修改数据类型：alter table 表名 **modify** 列 数据类型；
ALTER TABLE nihao5 MODIFY xm INT;
 删除列
alter table 表名 drop 列名；
ALTER TABLE nihao5 DROP xm;

3.D(Delete)：删除
drop table 表名；
drop table if exists 表名；

DML:增删改表中的数据
1.添加数据：
inset into 表名 （列名1，列名2，）values(值1，值2)；
注意：
1.列名和值要一一对应
2.如果表名后，不定义列名 则默认给所有列添加值
inset into 表名 values（值1.。。。值n）；
3.除了数字类型，其他类型需要使用引号(单双都可以)引起来
2.删除数据：
语法：
delete from 表名 【where 条件】
delete from student where id=1;
注意：
1.如果不加条件，则删除表中所有的记录
2.如果删除所有记录
1.delete from 表名：-- 不推荐使用，有多少条记录就会执行多少次操作，效率低
2.truncate table 表名：-- 先删除表，然后在创建一张一样的表 ，效率高。
3.修改数据
语法:
update 表名 set 列名1=值1，列名2=值2，… [where 条件]
UPDATE student SET age=111,score=100 WHERE id=1;
注意：
1.如果不加任何条件，则会将表中所有的记录全部修改
DQL:查询表中的记录
select * from 表名；
1.语法：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
order by
分组之后的条件
limit
分页限定

2.基础查询
1.多个字段的查询
select 字段名1，字段名2…from 表名；
注意：如果查询所有的字段，则可以使用*来替代字段列表
2.去除重复：

• distinct
  3.计算列
  *一般可以使用四则运算计算一些列的值（一般只会进行数值型的计算）
  *ifnull(表达式1，表达式2):null 参与的运算，计算的结果都为null
  表达1：那个字段需要判断是否为null
  如果该字段为null后面的替换值
  4.起别名：
  as :as也可以省略

练习：

CREATE TABLE student3 (
 id INT, #编号
 NAME VARCHAR(20), #姓名
 age INT, -- 年龄
 sex VARCHAR(5), #性别
 address VARCHAR(100), #地址
 math INT, #数学
 english INT #英语
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
SHOW TABLES;
SELECT *FROM student3;

-- 去除重复的结果集
SELECT DISTINCT address FROM student;
根据姓名地址去重
SELECT DISTINCT NAME,address FROM student;

计算math 和English 分数之和(如果有null，参与的运算 计算结果都为null)
SELECT NAME ,math,english ,math+IFNULL(english,0) FROM student;
–起别名
SELECT NAME ,math,english ,math+IFNULL(english,0) AS 总分 FROM student;
as 可以省略！

3.条件查询
1.where 子句后跟条件
2.运算符
*> < >= <= = <>
*BETWEEN…AND
*IN(集合)
*LIKE

• IS NULL
• • AND 或&&
• or 或 ||
• not 或 ！

–年龄在 20至30之间
–年龄是 10 20 23
SELECT *FROM student WHERE age BETWEEN 20 AND 30;
SELECT *FROM student WHERE age IN (10,20,23);

–查询英语成绩为null
SELECT *FROM student WHERE english IS NULL;
–查询英语成绩不为null
SELECT *FROM student WHERE english IS NOT NULL;

• LIKE:模糊查询
• *占位符：

•      *_:单个任意字符
  

•      *%：多个任意字符
  

– 查询姓马的
SELECT *FROM student WHERE NAME LIKE “马%”;
– 查询姓名第二字是化的 人
SELECT *FROM student WHERE NAME LIKE “化%";
– 查询三个字的姓名的人
SELECT * FROM student WHERE NAME LIKE "__”;
– 查询所有包含有德的人
SELECT *FROM student WHERE NAME LIKE “%德%”;

day2
DQL:查询语句
1.排序查询
*语法：order by 子句
order by 排序字段1 排序方式1 ，排序字段2 排序方式…
*排序方式：
ASC:升序 （默认的）
* DESC: 降序
* SELECT FROM student ORDER BY math ASC;
SELECT *FROM student ORDER BY math DESC;
SELECT FROM student ORDER BY math ASC ,english DESC;
* 注意：
* 如果有多个排序条件，则当前的条件值一样时，才会判断第二条件
2.聚合函数： 将一列数据作为一个整体，经i选哪个纵向的计算
count:计算格数
max: 计算最大值
min:计算最小值
sun:计算和
avg:计算平均值
SELECT COUNT(id)FROM student;
SELECT COUNT()FROM student;
SELECT COUNT(IFNULL(english,0))FROM student;

SELECT MAX(math)FROM student;

SELECT MIN(math)FROM student;

SELECT SUM(math)FROM student;

SELECT AVG(math)FROM student;
*注意：聚合函数的计算，派出null值
解决方案：
1.选择不包含非空的列进行计算
2.IFNULL函数
3.分组查询
*语法：group by 分组字段；
– 按照性别，分别查询 男 女同学的平均分
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), COUNT(id)FROM student WHERE math>70 GROUP BY sex;
– 按照性别，分别查询 男 女同学的平均分 人数 分数低于70不参与分组 分组之后人数大于2个
SELECT sex,AVG(math), COUNT(id)FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;
注意：
1.分组之后查询的字段：分组字段，聚合函数
2.where 和having 的区别？
1.where在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后进行 限定，如果不满足结果，则不会被查询出来。
2.where后不可以跟聚合函数，having可以进行聚合函数的判断
在聚合函数后面起别名 having 后面就可以根据起的别名进行判断。

4.分页查询
*语法：limit 开始索引，每页查询的条数；
公式：开始的索引=(当前的页码-1)*每页显示的条数
例如第二页：(2-1）*3=3
– 每页显示3条记录
SELECT *FROM student LIMIT 0,3;-- 第一页
SELECT *FROM student LIMIT 3,3;-- 第二页
SELECT *FROM student LIMIT 6,3;-- 第三页
注意：limit是一个mysql的 方言

前面四个模块的都是单表查询

约束
概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性
四种约束的分类
按住shift+tab 缩进回来
1.主键约束：primary key
注意：
1. 含义：并且且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
– 删除主键
ALTER TABLE stu DROP PRIMARY KEY;
– 创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长： 只跟上一条记录有关

• 概念:如果某一列的数值类型的，使用auto_increment 可以来完成值的自动增长
  CREATE TABLE stu(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(32)
  );
  – 删除自动增长
  ALTER TABLE stu MODIFY id INT;
  – 添加自动增长
  ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

2.非空约束：not null 某一列的值不能为null
– 创建表添加非空约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(32) NOT NULL
);
– 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
– 创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(30) NOT NULL;

3.唯一约束：unique 某一列的值不能重复
1.注意：
*唯一约束可以有null值，但是只能有一条记录为null
2. – 在创建表时，添加是唯一约束
CREATE TABLE stu(
id INT,
phone_name VARCHAR(32) UNIQUE
);
3 – 删除唯一约束
ALTER TABLE stu DROP INDEX phone_name ;
4 – 在创建表完成后，添加唯一约束
ALTER TABLE stu MODIFY phone_name VARCHAR(32) UNIQUE;

4.外键约束：foreign key 让表产生关系，从而保证数据的正确性
1.在创建表时，可以添加外键
*语法：
create table 表名(
语法:
。。。。
外键列
constranint 外键名称 foregin key 外键列名称 references 主表名称（主表列名称）
);
2，删除外键
alter table 表名 drop foreign key 外键名称；
3.创建表之后，添加外键
alter table 表名 add constraint 外键名称 forgeign key(外键字段名称) references 主表名称（主表列名称）；

多表之间的关系

实现方式
一对多 在多的一方建立外键，指向一的一方的主键。

在多的一方建立外键，指向一的一方的主键
实现方式
多对多
必须有两个字段

实现方式
一对一

案例

– 创建旅游线路分类表 tab_category
– cid 旅游线路分类主键，自动增长
– cname 旅游线路分类名称非空，唯一，字符串 100
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
)
– 添加旅游线路分类数据：
insert into tab_category (cname) values (‘周边游’), (‘出境游’), (‘国内游’), (‘港澳游’);
select * from tab_category;
– 创建旅游线路表 tab_route
/
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
)
– 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, ‘【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿’, 1499,‘2018-01-27’, 1),
(NULL, ‘【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园’, 699, ‘2018-02-22’, 3),
(NULL, ‘【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】’, 1699, ‘2018-01-27’, 2),

(NULL, ‘香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】’, 799, ‘2018-04-10’, 4);
select * from tab_route;
/*
创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
/
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default ‘男’,
telephone varchar(11),
email varchar(100)
)
– 添加用户数据
INSERT INTO tab_user VALUES
(NULL, ‘cz110’, 123456, ‘老王’, ‘1977-07-07’, ‘男’, ‘13888888888’, ‘66666@qq.com’),
(NULL, ‘cz119’, 654321, ‘小王’, ‘1999-09-09’, ‘男’, ‘13999999999’, ‘99999@qq.com’);
select * from tab_user;
/
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
– 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)

范式

数据库的备份和还原
1.命令行：
语法：
备份：mysqldump -u账户 -p密码 数据库名称>保存的路径
还原：
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件 。source 文件路径

2.图形化工具

day3
例子：
#创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
INSERT INTO dept (NAME) VALUES (‘开发部’),(‘市场部’),(‘财务部’);
#创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), – 性别
salary DOUBLE, – 工资
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-10-07’,3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘蜘蛛精’,‘女’,4500,‘2011-03-14’,1);

笛卡尔积：
有两个集合 A,B取这两个集合的所有组成情况
要完成多表查询，需要消除无用的数据

多表的查询

多表查询的分类：
1.内连接查询
隐式的内连接：
– 查询所有员工信息和对应的部门信息
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
t1.name,
t1.gender,
t2.name
FROM
emp t1,
dept t2
WHERE
t1.id=t2.id;
显示内连接
语法:select 字段列表 from 表名1（inner）join 表名2 on 条件
例如：
SELECT *FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
SELECT *FROM emp JOIN dept ON emp.dept_id=dept.id;
使用内连接查询的条件：
1.从哪些表中查询数据
2.条件是什么
3.查询哪些字段

2.外连接查询
1.左外连接
*语法：select 字段列表 from 表1 left(outer) join 表2 on 条件

• 查询的是左表所有数据以及其交集部分 交集部分就是条件( t1.dept_id=t2.`id)
• 其实内连接就是查询交集的部分
  – 查询所有的员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
  SELECT t1.,t2.nameFROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id;
  2.右外连接 *
  *语法：select 字段列表 from 表1 right(outer) join 表2 on 条件
  查询的是右表所有数据以及其交集部分
  怎么区分左右表呢？
  emp t1 LEFT JOIN dept t2
  emp就称为左表
  dept就称为右表

3.子查询
概念：查询中嵌套查询，称嵌套查询为子查询
– 查询工资最高的员工信息
– 查询最高的工资是多少
SELECT MAX(salary) FROM emp;
– 查询员工信心， 并且工资等于9000的
SELECT *FROM emp WHERE emp.salary=9000;
– 一条sql就完成这个操作 子查询
SELECT *FROM emp WHERE emp.salary=(SELECT MAX(salary) FROM emp);

子查询不同情况
1.子查询的结果是单行单列的：
*子查询可以作为条件，使用运算符去判断 运算符：> < >= <= =
– 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary<(SELECT AVG(salary)FROM emp);
2.子查询的结果是多行单列的：
*子查询可以作为条件，使用运算符in来判断
– 查询 财务部和市场部所有的员工信息
SELECT id FROM dept WHERE NAME='财务部’OR NAME=‘市场部’;
SELECT *FROM emp WHERE dept_id=3 OR dept_id=2;
SELECT *FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME='财务部’OR NAME=‘市场部’);
3.子查询的结果是多行多列的：
*子查询可以作为一张虚拟表参与查询
– 查询员工入职日期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 t1,dept t2 WHERE t1.dept_id=t2.idAND t1.join_date>‘2011-11-11’;

多表查询的练习
– 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, – 部门id
dname VARCHAR(50), – 部门名称
loc VARCHAR(50) – 部门所在地
);

– 添加4个部