数据库
数据库的基本概念
- 数据库的英文单词 : DataBase 简称 : DB
什么数据库 ?
- 用于存储和管理数据的仓库
数据库的特点
- 持久化存储数据的。其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库 -- SQL（适用于关系型数据库）
我们先了解几个数据库：
- MYSQL: 开源免费的数据库，小型的数据库.已经被 Oracle 收购了.MySQL6.x 版本开始收费
- Oracle: 收费的大型数据库，Oracle 公司的产品。Oracle 收购 SUN 公司，收购 MYSQL
- DB2:IBM 公司的数据库产品,收费的。常应用在银行系统中
- SQLServer: MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
- SyBase:已经淡出历史舞台。但其所在公司提供了一个非常专业数据建模的工具：PowerDesigner
- SQLite: 嵌入式的小型数据库，应用在手机端
常用数据库:MYSQL，Oracle
MySQL
在 web 应用中，使用的最多的就是 MySQL 数据库，原因如下:
- ①开源、免费
- ②功能足够强大，足以应付 web 应用开发（最高支持千万级别的并发访问）
MySQL本来为sun公司所属，被 Oracle 收购后，MySQL6.x 版本开始收费
MySQL的安装和卸载我不多说了，我说一下配置
配置
MySQL服务启动三种方式①手动
②cmd--> services.msc 打开服务的窗口
③使用管理员打开cmd
- net start mysql : 启动mysql的服务
- net stop mysql:关闭mysql服务
MySQL登录
- ①mysql -uroot -p密码（账户一般都为root）（mysql -uroot -p回车后，在输入的密码就是不可见的，可以保护信息）
- ②mysql -hip地址 -uroot -p连接目标的密码
- ③mysql --host=ip地址 --user=root --password=连接目标的密码
MySQL退出
- ①exit
- ②quit
MySOL目录结构：
①MySQL安装目录：
- basedir="我们安装时所选的地址"
②MySQL数据目录：
- datadir="C:/ProgramData/MySQL/MySQL Server5.5/Data/"（一般为这个，看你下的是几版本了，我下的是5.5版本）（此地址为数据库，表，数据的所在地）
（以上的目录信息我们可以在我们安装时选的地址中的配置文件 “my.ini” 中看到）
我们要知道MySQL中的几个概念
- 数据库：其实就是个文件夹
- 表：其实是数据库中文件
- 数据：数据（其实是文件的内容）
它们的成分模型图大致如下
(计算机安装了MySQL数据库服务器软件后就可以称我们的计算机是一个MySQL服务器了，服务器指的是硬件Java软件)
SQL
我们要对数据库，表，数据进行操作的话，就需要知道SQL
什么是SQL?
- SQL(Structured Query Language)：结构化查询语言
- 其实就是定义了操作所有关系型数据库（Relational DBMS）的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”
SQL通用语法
- SQL 语句可以单行或多行书写，以分号（；）结尾（分号是为了结束当前功能）（在图形化管理MySQL数据库的工具SQLyog中，一条命令不用“；”也行，但同时执行多条命令没“；”会报错）
- 可使用空格和缩进来增强语句的可读性
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
3种注释
- 单行注释:- - （空格）注释内容【空格不能省略】 或 #（空格可有可无）注内容(mysql 特有)（要带分号）
- 多行注释:/ *注释 * /（不带分号）
SQL语句可以分为四大类，对数据库，表，数据进行操作时用到的SQL语句是不一样的
SQL分类
- ①DDL(Data Definition Language)数据定义语言。用来定义数据库对象：数据库，表，列等。关键字：create，drop,alter 等
- ②DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert，delete，update 等
- ③DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字 : select，where 等
- ④ DCL(Data control Language)数据控制语言(了解)。用来定义数据库的访问权限和安全级别，及创建用户。关键字: GRANT、REVOKE 等
这四大类的使用场景结合上图模型图
DDL：操作数据库、表
提到操作，我们就需要知道CRUD
①操作数据库
C(Create)：创建
- 创建数据库： create database 数据库名称;
- 创建数据库，判断不存在，再创建： create database if not exists 数据库名称;
- 创建数据库，并指定字符集： create database 数据库名称 character set 字符集名;
练习 : 创建db4数据库，判断是否存在，并制定字符集为gbk
- create database if not exists db4 character set gbk;
R(Retrieve)：查询
- 查询所有数据库的名称： show databases;
- 查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称;
一般我们查询会出现下面四个数据库，但我们在数据目录只能发现下面三个数据库（文件夹）
而第一个：information_schema数据库是用来描述数据库MySQL里面的一些信息，比如说有哪些表，有哪些库的名字，但这里面存放的表不是真正的表，而是视图（以后会了解）
U(Update)：修改
- 修改数据库的字符集 alter database 数据库名称 character set 字符集名称
D(Delete)：删除
- 删除数据库为： drop database 数据库名称;
- 判断数据库存在，存在再删除
- drop database if exists 数据库名称;
使用数据库
- 查询当前正在使用的数据库名称： select database();
- 使用数据库：（相当于进入文件夹）use 数据库名称;
②操作表
C(Create)：创建
语法：
create table 表名(列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n ); 注意：最后一列，不需要加逗号 (,)
数据库中的一些常见数据类型：
- int：整数类型 如：age int
- double：小数类型 如：score double(5,2)【5为数字的位数，2为保留的小数位，比如：999.99，有5位数，保留了两位小数】
- date：日期，只包含年月日（yyyy-MM-dd）
- datetime：日期，包含年月日时分秒（yyyy-MM-dd HH:mm:ss）
- timestamp：时间戳类型，包含年月日时分秒（yyyy-MM-ddHH:mm: ss） 如果将来不给这个时间戳类型的字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
- varchar：字符串 name varchar(20)，姓名最大20个字符（如zhangsan：8个字符；张三：2个字符）
了解这些后，就可以建一个表了，比如，下面这个表
任务：建一个表
操作展示
CREATE TABLE student(id INT,NAME VARCHAR(35), age INT, score DOUBLE(4,1), brith DATE, date_change TIMESTAMP );
详细的数据类型如下
复制表
- create table 表名 like 被复制的表名:
R(Retrieve)：查询
- 查询某个数据库中所有的表名称
- show tables; 展示：
- 查询某个表的字符集:查询某个表的创建语句 show create table 表名;
- 查询表结构
- desc 表名; 展示：
U(Update)：修改
- 修改表名： alter table 表名 rename to 新的表名; rename table 原表名 to 新表名;
- 修改表的字符集： alter table 表名 character set 字符集名称;
- 添加一列 alter table 表名 add 列名 数据类型;
- 修改列名称类型 alter table 表名 modify 列名 新数据类型; alter table 表名 change 列名 新列名 新数据类型;
- 删除列 alter table 表名 drop 列名;
D(Delete):删除
- drop table 表名;
- drop table if exists 表名;
DML：增删改表中数据
添加数据：
语法：
- insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意：
- 列名和值要一一对应
- 如果表名后，不定义列名，则默认给所有列添加值
- insert into 表名 values(值1,值2，..值n);除了数字类型，其他类型需要使用引号(单双都可以)引起来
删除数据：
语法：
- delete from 表名 where 条件
注意：
- 如果不加条件，则删除表中所有记录
- 如果要删除所有记录 delete from 表名;（不推荐使用。有多少条记录就会执行多少次删除操作）（适用于删除一行的情况） truncate table 表名;（DDL命令）（推荐使用，效率更高 先删除表，然后再创建一张一样的表）（table可以省略）
修改数据：
语法:
- update 表名 set 列名1 = 值1，列名2 = 值2,...where条件;
注意:
- 如果不加任何条件，则要将表中所有记录全部修改
DQL:查询表中的记录
语法：
select 字段列表from 表名列表where 条件列表group by 分组字段 having分组之后的条件 order by 排序 limit 分页限定
给大家准备一些数据便于操作
CREATE TABLE student (id INT, -- 编号NAME VARCHAR(20), -- 姓名age INT, -- 年龄 sex VARCHAR(5), -- 性别 address VARCHAR(100), -- 地址 math INT, -- 数学 english INT -- 英语 ); INSERT INTO student(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);
表大概如下
基础查询
- 多个字段的查询 select 字段名1，字段名2... from 表名; 注意：如果查询所有字段，则可以使用*来替代字段列表 select * from 表名;
- 去除重复 distinct（如果选择多个字段的话，必须显示的数据完全一样才能去重）
SELECT DISTINCT 字段名1,字段名2 FROM 表名如：SELECT DISTINCT math,english FROM student
- 计算列 一般可以使用四则运算计算一些列的值。 (一般只会进行数值型的计算) ifnull （表达式1,表达式2）：null参与的运算，计算结果都为null 表达式1：哪个字段需要判断是否为null 表达式2：如果该字段为null后的替换值
SELECT IFNULL(字段名,替换值) FROM 表名如：SELECT IFNULL(english,0) FROM student
- 起别名as：as也可以省略
SELECT 字段名1 AS 想改的名1,字段名2 AS 想改的名2 FROM 表名如：SELECT math+IFNULL(english,0) AS 总分 FROM student
条件查询
where子句后跟条件
运算符
- <，>，<=，>=，=，<>（不等于）
- between...and
- in(集合)
- LIKE：模糊查询 占位符：_：单个任意字符%：多个任意字符
- is null
- and 或&&
- or 或||
- not 或 !
如：查询student表中年龄大于20岁的学生
SELECT * FROM student WHERE age > 20;SELECT * FROM student WHERE age>= 20;
查询年龄等于20岁
SELECT * FROM student WHERE age = 20;
查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;SELECT * FROM student WHERE age <> 20;
查询年龄大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 && age <=30;SELECT * FROM student WHERE age >= 20 AND age <=30;SELECT * FROM student WHERE age BETWEEN 20 AND 30;
查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age=25;SELECT * FROM student WHERE age IN (22,18,25);
查询英语成绩为null
SELECT * FROM student WHERE english = NULL; (不对的) 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 "_化%"
查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE'_ _ _'
查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
排序查询:order by
语法：order by 子句
- order by 排序字段1 排序方式1，排序字段2 排序方式2...
排序方式：
- ASC：升序，默认的
- DESC：降序
SELECT * FROM 表名 ORDER BY 字段名 排序方式如：SELECT*FROM student ORDER BY math DESC
注意：
- 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件
聚合函数：将一列数据作为一个整体，进行纵向的计算（即查看有几行数据）
- count：计算个数 一般选择非空的列：主键（后面会说到主键） count(*)、count(1)、count(列名)
SELECT COUNT( * ) FROM 表名如：SELECT COUNT( * ) FROM student
【count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略为NULL的值。count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计】
- max：计算最大值
- min：计算最小值
SELECT MAX(字段名) FROM 表名如：SELECT MAX(math) FROM studentSELECT MIN(IFNULL(english ,0))AS 英语 FROM student
- sum：计算和
SELECT SUM(字段名) FROM 表名如： SELECT SUM(english) 英语总分 FROM student
- avg：计算平均值
SELECT AVG(字段名) FROM 表名如：SELECT AVG(math) 数学平均分 FROM studentSELECT AVG(IFNULL(english,0)) 英语平均分 FROM student
注意： 聚合函数的计算，排除null值
解决方案：
- ①选择不包含非空的列进行计算
- ②IFNULL函数
分组查询:group by ,having
语法：
- group by 分组字段;
SELECT 分组字段,聚合函数 FROM 表名 GROUP BY 分组字段如：SELECT sex FROM student GROUP BY sexSELECT sex ,SUM(english) FROM student GROUP BY sex
注意：
- 分组之后查询的字段可以为：分组字段,聚合函数
SELECT 分组字段,聚合函数 FROM 表名 WHERE 条件 GROUP BY 字段分组 HAVING 聚合函数如： SELECT sex 性别 ,AVG(IFNULL(english,0))平均分,COUNT(*)人数 FROM student WHERE IFNULL(english,0)>70 GROUP BY sex HAVING 人数>2
where 和 having 的区别?
- where 在分组之前进行限定，如果不满足条件，则不参与分组
- having在分组之后进行限定，如果不满足结果，则不会被查询出来
- where 后不可以跟聚合函数，having可以进行聚合函数的判断
（我们可以这样理解，聚合函数是对结果集的计算，而where正处于确定结果集的过程）
分页查询：limit
- 语法：limit 开始的索引,每页查询的条数;
- 公式：开始的索引 = (当前的页码 - 1)* 每页显示的条数--如每页显示3条记录
SELECT * FROM 表名 LIMIT 页码的索引,每页显示的条数;如： SELECT * FROM student LIMIT 0,3; -- 第1页SELECT * FROM student LIMIT 3,3; -- 第2页SELECT * FROM student LIMIT 6,3; -- 第3页 如果第一个参数是 0 可以省略写； 如：select * from student limit 5;
注意：limit 是一个MySQL"方言"
约束
概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类
- 主键约束: primary key
- 非空约束:not null
- 唯一约束 :unique
- 外键约束:foreign key
非空约束: not null，某一列的值不能为null
- 创建表时添加约束
CREATE TABLE 表名(字段名 数据类型 NOT NULL);如：CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 );
- 创建表完后，添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;如：ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
- 删除name的非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;如：ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束 : unique，某一列的值不能重复注意：
- 唯一约束可以有NULL值但是只能有一条记录为null
在创建表时，添加唯一约束
CREATE TABLE 表名(字段名 数据类型 UNIQUE);如：CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 手机号 );
删除唯一约束
ALTER TABLE 表名 DROP INDEX 字段名;如：ALTER TABLE stu DROP INDEX phone number;
在表创建完后，添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIOUE;如：ALTER TABLE stu MODIFY phone number VARCHAR(20) UNIOUE;ALTER TABLE 表名 ADD UNIQUE(字段名);如：ALTER TABLE stu1 ADD UNIQUE(score);
主键约束：primary key注意：
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
在创建表时，添加主键约束
create table 表名(字段名 数据类型 primary key);如：create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) );
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;如：ALTER TABLE stu DROP PRIMARY KEY;
创建完表后，添加主键
ALTER TABLE 表名 MODIFY 字段名 INT PRIMARY KEY;如：ALTER TABLE stu MODIFY Id INT PRIMARY KEY;ALTER TABLE 表名 ADD PRIMARY KEY(字段名1，字段名2)如：ALTER TABLE stu1 ADD PRIMARY KEY(score)
自动增长：
概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长（当我们填入较大的值之后，会自增较大的值，当我们再填入较小的之后，不会自增较小的值，而是依然自增较大的值）
要求：在创建表时，添加主键约束，并且完成主键自增长
create table 表名(字段名 数据类型 primary key auto_increment);如：create table stu( id int primary key auto_increment，-- 给id添加主键约束 name varchar(20) );
删除自动增长
ALTER TABLE 表名 MODIFY 字段名 数据类型;如：ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO INCREMENT;如：ALTER TABLE stu MODIFY id INT AUTO INCREMENT;
注意：当我们在定义主键的字段上定义自增长后，要先删除自增长，才能删除主键
外键约束：foreign key,让表于表产生关系，从而保证数据的正确性
给大家一些数据，便于操作
-- 创建部门表(id,dep_name,dep_location)-- 一方，主表CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(20) ); -- 创建员工表(id,name,age,dep_id) -- 多方，从表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT, -- 外键对应主表的主键 CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ); -- 添加 2 个部门 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); -- 添加员工,dep_id 表示员工所在的部门 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
创建员工表(id,name,age,dep_id)
创建部门表(id,dep_name,dep_location)
在创建表时，可以添加外键
语法
create table 表名(...外键列constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称));
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
创建表之后，添加外键（外键值可以为null，但不能为除了主键值以外的其他值）
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称); REFERENCES 主表名称(主表字段名称);
级联操作
添加级联操作
语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称);REFERENCES 主表名称(主表字段名称) ON UPDATE CASCADE ON DELETE CASCADE;
分类：
- 级联更新：ON UPDATE CASCADE
- 级联删除：ON DELETE CASCADE