java学习总结（十一）：数据库

一、数据库简介

数据在内存：

优点：读写速度快

缺点：程序结束后数据丢失

保存到文件

优点：数据可以永久保存

缺点：

1、频繁的IO操作，效率不高

2、数据的管理非常不方便，需要把所有的数据整体都读取出来才能操作

数据库：

1、数据永久保存

2、数据管理非常方便

卸载：

1、控制面板-程序和功能，卸载MySql

2、C:\ProgramData删除MySql目录

数据库是以表为组织单位存储数据的。

二、单表

PRIMARY KEY 主键，不能重复，唯一确定一条记录 （unique+not null）

表中的任何列都可以作为主键，只要它满足一下条件：

1、任意两行都不具有相同的主键值

2、每一行都必须具有一个主键值（主键列不允许空置NULL）

3、主键列中的值不允许修改或更新

4、主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

AUTO_INCREMENT 自动增长

varchar(10) char(10)区别：

相同点：都可以最大放10个字符

不同点：char(10)不管输入的是多少都会占10个字符，例如输入名字“张三”只有两个字符，

但是使用char(10)在数据库里面还是占10个字符的空间。

使用varchar(10)最大支持是10个字符，但是实际长度就是输入字符长度，例如输入名字“张三”只有两个字符，

那么在varchar(10)里面就只占两个字符。

Duplicate entry '1' for key 'PRIMARY'

select

数据库中以表为组织单位存储数据。

表类似我们的Java类，每个字段对应类里面的属性。

那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。

1. 类--------------------表
2. 类中属性-------------表中字段（列）
3. 对象------------------记录（行）

-- 列出所有的数据库
SHOW DATABASES;
  
-- 创建数据库
CREATE DATABASE study DEFAULT CHARACTER SET utf8;
  
-- 删除数据库
DROP DATABASE study;
  
-- ----------------------------------
-- 数据库表的操作
-- 切换数据库
USE study;
-- 创建表
CREATE TABLE student(
    id INT,
    `name` CHAR(10),
    age INT,
    gender CHAR(1)
);
  
-- 查看所有表
SHOW TABLES;
-- 查看表的结构
DESC student; -- description
-- 删除表
DROP TABLE student;
  
-- 更改表的结构
-- 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
-- 删除字段
ALTER TABLE student DROP COLUMN address;
-- 修改表的字段
ALTER TABLE student CHANGE address addr CHAR(20);
-- 修改表的名字
ALTER TABLE student RENAME TO stu;
  
-- 创建表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(10),
    age INT,
    gender CHAR(1)
);
-- * 代表查询所有的列
SELECT * FROM student;
  
-- 插入数据
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO student(id,`name`,age,gender) VALUES(1,'wangwu',23,'男');
INSERT INTO student(id,`name`,age,gender) VALUES(3,'赵六',23,'男');
INSERT INTO student VALUES(4,'赵六22',33,'男');
-- 插入部分字段值(必须把前面的字段名都写上)
INSERT INTO student(`name`,age,gender) VALUES('小张11',23,'男');
-- 一次插入多条数据
INSERT INTO student(`name`,age,gender) VALUES('小张77',23,'男'),('小王',22,'男');
  
-- 修改数据
UPDATE student SET age=age+1;
UPDATE student SET age=age+1,name='zhangsan' WHERE id=7;

  
-- 删除数据
DELETE FROM student; -- 删除表中所有数据（很少使用，是非常危险）
DELETE FROM student WHERE age=24; -- 所有age是24的数据都被删除了，可能有多条数据都是age=24
DELETE FROM student WHERE id=12; -- 因为id是主键是唯一的，所以根据id删除只能删除唯一的一条数据
DELETE FROM student WHERE id=1 OR id=2;
DELETE FROM student WHERE id IN(1,2);
-- TRUNCATE删除表里面所有数据，自增的id会重新初始化为初始值1
TRUNCATE TABLE student;
  
-- 查询数据
-- 显示所有列(字段)数据
-- 学习时候可以写*，但是在企业开发中需要什么字段就写什么字段，检索不需要的列会降低检索速度和应用程序的性能
SELECT * FROM student;
SELECT id,`name`,age,gender FROM student;
-- 查询指定列
SELECT `name`,age FROM student;
-- 查询时候添加常量列，通过as可以起别名
SELECT id,`name`,age AS '年龄','java2403' AS '班级' FROM student;
-- 查询时候合并列，字段可以当成java里面的变量来运算
SELECT id,`name`,(php+java) AS '总成绩' FROM student;
-- 查询时候去掉重复的记录
SELECT DISTINCT address FROM student;

-- 计算字段（列）：
-- 计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。
-- 只有数据库知道SELECT语句中哪些列是实际的列表，哪些列是计算字段。
-- 从客户端(如应用程序)来看，计算字段的数据与其他列的数据的返回方式相同。
  
-- 条件查询 where ,数据库表一般包含大量的数据，很少需要检索表中所有行
SELECT * FROM student WHERE `name`='小王';
  
-- 逻辑条件: and（同时成立） or(只要有一个成立)
SELECT * FROM student WHERE `name`='小王' AND address='青岛';
SELECT * FROM student WHERE `name`='小王' OR address='北京';
  
-- 比较运算: >  <  >=  <=  != 不等于也可以写成：<>
SELECT * FROM student WHERE java>=70 AND java<=80;
-- between and (等价于>= and <=)
SELECT * FROM student WHERE java BETWEEN 70 AND 80;
-- 查询地址不是青岛的学生信息 <>
SELECT * FROM student WHERE address != '青岛';

-- 空值NULL：无值(no value)，它与字段包含0、空字符串或紧紧包含空格不同
SELECT * FROM student WHERE address IS NULL; -- IS NOT NULL

-- 聚合查询
-- 聚合查询函数：sum(),avg(),max(),min(),count()
-- 统计学生php的总成绩（sum求和）
SELECT SUM(php) AS 'php总成绩' FROM student;
-- 统计学生php的平均值
SELECT AVG(php) AS 'php平均值' FROM student;
-- 统计学生php的最大值
SELECT MAX(php) AS 'php最大值' FROM student;
-- 统计学生表里面一共有多少学生
SELECT COUNT(*) AS '总人数' FROM student;
SELECT COUNT(id) AS '总人数' FROM student;
SELECT COUNT(address) AS '总人数' FROM student;
-- 注意：如果指定列明，则COUNT会忽略指定列的值为NULL的行，用*则不忽略。

-- 查询排序 desc:descending asc:ascending
-- 语法：order by 字段  asc/desc  默认是asc升序，可以不写
SELECT * FROM student ORDER BY php;
SELECT * FROM student ORDER BY php ASC;
SELECT * FROM student ORDER BY php DESC;
-- 多个条件排序
-- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
SELECT * FROM student ORDER BY php DESC, java ASC;
-- order by要放在sql语句的最后
  

-- 分组查询(group by)
-- 需求：查询男女分别有多少人
-- 性别  人数
-- 男    3
-- 女    2
SELECT gender AS '性别',COUNT(*) AS '人数'
FROM student GROUP BY gender;
-- 分组之后的条件筛选用HAVING
SELECT gender AS '性别',COUNT(*) AS '人数'
FROM student GROUP BY gender HAVING COUNT(*)>1;

总结：

1、如果分组中包含具有NULL值的行，则NULL将作为一个分组返回。

2、GROUP BY字句必须出现在WHERE字句之后，ORDER BY字句之前。

3、WHERE过滤行（行级过滤），而HAVING过滤分组（组级过滤），WHERE所有技术和选项都适用于HAVING，语法使用一样。

字段属性设置:

1、not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空

2、auto_increment: 设定int类型字段的值可以“自增长”，即其值无需“写入”，而会自动获得并增加

此属性必须随同 primary key 或 unique key 一起使用。primary key = unique key + not null

3、[primary] key： 设定为主键。是唯一键“加强”：不能重复并且不能使用null，并且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;

通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。

但：一个表只能设定一个主键。

4、unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。

Duplicate entry 'zhangsan' for key 'name'

5、default ‘默认值’： 设定一个字段在没有插入数据的时候自动使用的值。

6、comment ‘字段注释’

三、多表

学生表、班级表、课程表、班级课程表

一对一、一对多、多对多

关系型数据库：MySql、SqlServer、Oracle

相同的数据出现多次绝不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一个数据一个表，各表通过某些共同的值互相连接，所以才叫关系数据库。

将数据存储到多个表能更有效的存储，更方便的处理，但这些好处是有代价的：如果数据存储在多个表中，怎么用一条SELECT语句就检索出数据呢？答案是使用：子查询、联结

非关系型数据库：Redis ，MongoDB 速度非常快（日志信息）

可以理解为一个大的Map结构

-- 多对多
-- 班级表
CREATE TABLE banji(
     id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');

SELECT * FROM banji;
 
-- 学生表 reference:参考，引用
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    age INT,
    gender CHAR(1),
    banji_id INT,
    FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',3);

SELECT * FROM student;
 
-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);

SELECT * FROM course;
 
-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY KEY AUTO_INCREMENT,
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id,course_id), -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键
    FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键
);
INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);

SELECT * FROM banji_course;
-- 子查询：嵌套查询，一个查询语句结果是另一个查询语句的条件
-- 查询班级是Java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='Java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='Java1812');
 
-- 班级是Java1807班或者Java1812班所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812'; -- 1,2
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812');
 
-- 计算字段使用子查询：班级id 班级名字 班级人数
-- 执行这条查询遵循下面的步骤：
-- 1、从banji表检索班级列表
-- 2、对检索出的每个banji，统计其在student表中的数量
SELECT id,`name`,(SELECT COUNT(*) FROM student WHERE student.banji_id=banji.id) AS total_count
FROM banji
ORDER BY `name`;

total_count是一个计算字段，它是由括号中的子查询建立的，该子查询对检索出的每个banji执行一次

总结：

1、"="：要求子查询只有一个结果。 "in"：子查询可以有多个结果。

2、子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。

3、能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

子查询也可以使用下面的联结来实现

四、等值连接

从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留

笛卡尔积：

由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将时第一个表中的行数乘以第二个表中的行数。通俗的说就是查询所得的结果行数是两张表行数的乘积。

返回笛卡尔积的联结，也称为叉联结cross join。

等值连接:

注意：联结查询非常消耗资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

五、内联结

内连接有两种写法，一种是inner join，另一种是join，这两种写法都是一样的，可以理解为join是inner join的缩写。

等值连接和内连接的效果一样，但是开发中建议使用内连接

-- 学生姓名 班级名称 课程名称 学分

SELECT *

FROM student s,banji b,banji_course bc,course c

WHERE s.banji_id=b.id AND b.id=bc.banji_id AND bc.course_id=c.id;

总结：多表查询主要是注意下面两点

1、整个查询涉及到几张表，涉及到几张表就连接这几张表。

2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）

六、inner join on、left join on、right join on区别

inner join on 只有左右两个表有关联的才查询出来

left join on 左表中都显示出来，右表没有显示空

right join on 右表都显示，左表没有显示空

左连接，也成为左外连接：从左表那里返回所有的行，即使在右表中没有匹配的行

右连接，也成为右外连接

SELECT * FROM student as s LEFT JOIN banji as b on s.banji_id=b.id;

七、模糊查找

语法形式：字段 like '要查找字符'

说明：

1、like模糊查找用于对字符类型的字段进行字符匹配查找。

2、要查找的字符中，有两个特殊含义的字符：% , _:

2.1: %含义是：代表0或多个的任意字符

2.2: _含义是：代表1个任意字符

3、语法：like '%关键字%'

SELECT * FROM student WHERE `name` LIKE '张%'; -- 以张开头 SELECT * FROM student WHERE `name` LIKE '张_'; -- 以张开头，而且名字是两个字 SELECT * FROM student WHERE `name` LIKE '%张%'; -- 名字里面只要有张就可以

注意: NULL

通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL，

SELECT * FROM student WHERE `name` LIKE '%';

不会匹配name为NULL的行

注意：

SQL的通配符很有用，但这种功能是有代价的，即通配符搜索要消耗更长的处理时间，使用通配符的技巧：

1、不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2、在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处 '%张'。把通配符置于开始处，搜索起来是最慢的。