基本概念：
- 数据库DB(database)：
- 数据库管理系统DBMS(database management system)：
- 管理数据库的一套软件
- 比如Oracle、MySQL、SQL server、DB2
- 介于应用程序和操作系统之间
- 数据库应用程序：
- 使用数据库应用程序与DBMS进行通信，访问和管理数据库中的数据
- 数据库服务器：
- 计算机+数据库管理系统
- 一个数据库服务器可以管理多个数据库，一个数据库下包含多个表
- SQL语言
- 全称：Structured Query Language，结构化查询语言
- 由IBM在70年代开发出来，80年代被确定为关系型数据库语言的标准
- 主要包含，对数据的定义、操作、查询、控制，四大类操作
- 常见的数据库产品
- Oracle公司的Oracle
- 微软公司的SQL Server
- IBM公司的DB2
- 10gen公司的MongoDB：
- Oracle公司的MySQL，早期为瑞典的MySQL AB公司开发
- 数据库引擎：
SQL语言
- 数据定义语言：DDL(Data Definition Language)
- 主要进行对数据库、表的定义，比如CREATE、ALTE、RDROP等操作
- 数据操作语言：DML(Data Manipulation Language)
- 向数据库中添加、删除、修改数据，比如INSERT、UPDATE、DELETE等操作
- 数据查询语言：DQL(Data Query Language)
- 从数据库中查询数据，主要是SELECT操作
- 用得最多的操作
- 数据控制语言：DCL(Data Control Language)
- 用于控制用户对数据库的访问权限，比如GRANT、REVOKE、COMMIT、ROLLBACK等操作
- 有一个标准的SQL语言（当前最新版本是SQL:2016），在各家DBMS上通用，但各个DBMS又对标准的SQL进行了扩展，学习的时候要注意
MySQL安装、初步使用的注意事项
- 版本的下载：
- 安装方式：
- 典型安装Typical：只包含服务器、命令行客户端、命令行使用程序
- 定制安装Custom：
- 完全安装Complete：各种组件全部安装
- 配置：
- 包括详细配置和标准配置
- 详细配置：
- 服务器类型：会影响到对内存、磁盘、过程等的决策
- 开发者类型：适用于开发者，内存占用少
- 服务器类型：专门用提供服务
- 专用MySQL服务器：该机器专门用来作为MySQL的服务器，资源开销最大
- 数据库用途：
- 多功能数据库：同时使用InnoDB和MyISAM引擎
- 事务处理数据库：同时使用InnoDB和MyISAM引擎，但InnoDB能分配到更多的资源
- 非事务处理数据库：只是用MyISAM引擎
- 最大并发连接数
- 决策支持(Decision Support)：不需要大量的并发连接
- 联机事务处理(Online Transaction Processing)：需要大量的并发连接，最大500
- 人工设置(Manual Setting)：默认连接数15
- 默认端口3306
- 字符集：
- 标准字符集(Standard Character Set)：支持西欧语言
- 支持多语言(Best Support For Multilingualism)：UTF-8字符集
- 人工选择(Manual Selected Default Character Set/Collection)：自己选择，GBK；GB2312；UTF-8
- 配置文件：
- my.ini：正在使用的配置文件，一定会被读取
- 其他：my-huge/large/medium/small/template/innodb-heavy-4G.ini等可能会被读取到
- 缺少根元素
- 删除：C:\Users\用户名\AppData\Roaming\Oracle\MySQL Notifier下的配置文件
数据库的基本操作
# 命令行下登录到数据库：开始菜单中找到“MySQL 5.7 Command Line Client” 打开
# 输入root用户的密码，即可登录
# 在MySQL workbench中操作更加方便
# MySQL Workbench中建立连接，输入connection name; connection method; hostname port; username password等，下次直接点击这个连接就能连上数据库
# 在建立好的连接上面点击右键，“Copy JDBC Connection String to Clipboard”，可以用在将来的jdbc编程中
# 点击该连接，连接上数据库
SHOW DATABSASES; # 查看有哪些数据库。系统默认有information_scheme;mysql;performance_scheme;sys四个；另有sakila和world两个示例库
CREATE DATABASE newDB; # 创建一个数据库，名叫newDB
SHOW CREATE DATABASE newDB; # 查看数据库newDB的信息，可以看到是Character Set UTF-8
ALTER DATABASE newDB DEFAULT CHARACTER SET gbk COLLATE gbk_bin; # 将newDB的默认字符集改为gbk
DROP DATABASE newDB; # 删除newDB这个数据库
MySQL的数据类型
- 整数类型：
- TINYINT：1个字节；0～28-1；-27～27-1；
- SMALLINT：2个字节；0～216-1；-215～215-1；
- MEDIUMINT：3个字节；0～224-1；-223～223-1；
- INT：4个字节；0～232-1；-231～231-1；
- BIGINT：8个字节；0～264-1；-263～263-1；
- 浮点数与定点数
- FLOAT：4个字节；
- DOUBLE：8个子ie；
- DECIMAL(M,D)；M+2个字节；M表示总长度，D表示小数点后的长度
- 日期与时间
- YEAR：
- 1个字节；1901～2155；YYYY；0000
- 四位字符串或数字：'2016'和2016皆可；
- 两位字符串：'16'表示2016；'69'表示2069；'70'表示1970；'99'表示1999；'00'表示2000
- 两位数字：1表示2001；69表示2069；70表示1970；99表示1999；0表示0000
- DATE：
- 4个字节；1000-01-01～9999-12-03；YYYY-MM-DD；0000-00-00
- 完整字符串'2016-12-16'和'20161216'都表示2016年12月16日
- 年简写字符串：'16-12-16'和'161216'都表示2016年12月16日
- 年简写数字：161216和12-12-16，都表示2016年12月16日
- 函数与常量：NOW()和CURRENT_DATE都表示当前系统日期
- TIME：
- 3个字节；-838:59:59～838:59:59；HH:MM:SS；00-00-00
- 'D HH:MM:SS'：D表示天数，可以是[0,34]；比如'3 12:24:36'表示84:24:36
- 'HHMMSS'或者HHMMSS：295416表示29:54:16
- 常量与函数：CURRENT_TIME和NOW()表示当前系统时间
- DATETIME：8个字节；1000-01-01 00:00:00～9999-12-31 23:59:59；YYYY-MM-DD HH:MM:SS；0~0
- TIMESTAMP：
- 4个字节；1970-01-01 00:00:01～2038-01-19 03:14:07；YYYY-MM-DD HH:MM:SS；0~0
- 与DATETIME相同，只是取值范围更小
- CURRENT_TIMESTAMP和NULL和无输入，都表示当前系统时间
- 如果插入的数据非法，那么会把零值插入
- 字符串与二进制
- CHAR：
- 固定长度字符串：CHAR(4)表示最大可存储4个字节，并且始终占用4个字节
- VARCHAR
- 可变长度字符串：VARCHAR(4)表示最大可存储4个字节，实际占用实际的字节数+1，实际最大可占用5个字节
- BINARY
- VARBINARY
- BLOB
- 数据量很大的二进制数据，大小与下面的TEXT相同
- 按照二进制编码进行比较和排序；
- TINYBLOB：
- BLOB：
- MEDIUMBLOB：
- LONGBLOB：
- TEXT
- 表示大文本数据，如文章评论和内容等
- 按照文本模式进行比较和排序
- TINYTEXT：[0,255]个字节；0.25K
- TEXT：[0,65535]个字节；64K
- MEDIUMTEXT：[0,16777215]个字节；16M
- LONGTEXT：[0,4294967295]个字节；4096M
- ENUM
- 枚举类型；定义一个枚举类型：ENUM('枚举值1','枚举值2','枚举值3','枚举值4','枚举值5')
- 存入数据库的实际是这个枚举值的编号，而不是值本身
- SET
- 与ENUM类似，表示的是字符串对象；SET('值1','值2','值3','值4','值5')
- 存入数据库的也是其编号，而不是字符串本身
- BIT
- 表示二进制数据；定义BIT(M)，M表示值的位数，[1,64]
- 如果长度小于M，那么左边用0补齐
约束
- 约束：约束就是一个条件，表示被约束的数据应当满足约束条件
- 为什么有约束：因为为了后续的各种计算和操作，数据库中的数据不能是杂乱无章的，要符合一定的规则
- 常见约束：
- 主键约束：PRIMARY KEY
- 外键约束：FOREIGN KEY
- 非空约束：NOT NULL
- 唯一性约束：UNIQUE
- 默认值约束：DEFAULT
- 主键约束PRIMARY KEY
- 用主键约束的列的数据可以唯一的表示这一条记录(这一行数据)
- 主键约束的数据不能重复，不能为空，每张表只能有一个主键约束
- 是否可重用，得看具体的数据库
- 单字段主键：
- 主键约束在一个字段上，这个字段的值唯一标识一条记录
- 多字段主键：
- 主键约束在多个字段上，这几个字的的值拼接起来唯一标识一条记录
- 外键约束FOREIGN KEY
- 非空约束
- 唯一约束UNIQUE
- 默认约束DEFAULT
- 默认约束的字段，如果没有插入数据，那么会自动插入默认值
表的基本操作
USE newDB; # 进入newDB数据库，后续的操作都在这个数据库中
CREATE TABLE teacher( # 创建一个名为teacher的表
tea_id TINYINT PRIMARY KEY, # 教师编码，主键约束，唯一且非空
tea_ID_num CHAR(18), # 身份证号
tea_name VARCHAR(10), # 姓名
tea_gender ENUM('男','女'), # 性别
tea_birth DATE # 出生日期
);
SHOW TABLES; # 查看这个数据库下的表
SHOW CREATE TABLE teacher; # 查看teacher这个表
DESCRIBE teacher; # 查看teacher这个表的字段信息
DESC teacher; # DESCRIBE的简写
ALTER TABLE teacher RENAME TO tea; # 修改表名：teacher--->tea
DESC tea;
ALTER TABLE tea CHANGE tea_gender tea_sex ENUM('男','女'); # 修改字段名：tea_gender--->tea_sex，数据类型为ENUM('男','女')，注意数据类型不可省略，即使一样也不可省略
ALTER TABLE tea MODIFY tea_sex ENUM('男','女','其他'); # 修改数据类型：tea_sex的新数据类型为ENUM('男','女','其他')
ALTER TABLE tea DROP tea_birth; # 删除字段：删除tea_birth字段
ALTER TABLE tea ADD tea_birth DATE NOT NULL FIRST; # 添加字段：添加tea_birth字段，非空，位置为first，就是第一个字段，(也可以是是AFTER 字段名)，
ALTER TABLE tea MODIFY tea_birth DATE AFTER tea_sex; # 修改字段位置：修改tea表下的tea_birth字段的位置为tea_sex之后
DROP TABLE tea; # 删除数据表。特别小心，表跟表之间往往存在关联
CREATE TABLE teacher( # 新建一张表
tea_id TINYINT PRIMARY KEY AUTO_INCREMENT, # tea_id字段为主键，且自增(AUTO_INCREMENT，适合于任何整数类型)，即每添加一条记录，这个字段的值就增加1
tea_ID_num CHAR(18),
tea_name VARCHAR(10),
tea_gender ENUM('男','女','其他'),
tea_birth DATE
);
CREATE TABLE teacher(
tea_id TINYINT,
tea_ID_num CHAR(18),
tea_name VARCHAR(10),
tea_gender ENUM('男','女','其他'),
tea_birth DATE,
PRIMARY KEY(tea_id,tea_ID_num) # 设置多字段主键
);
添加、更新、删除数据
CREATE TABLE teacher( # 创建个teacher表
tea_id TINYINT,
tea_ID_num CHAR(18),
tea_name VARCHAR(10),
tea_gender ENUM('男','女','其他'),
tea_birth DATE,
PRIMARY KEY(tea_id) # tea_id为主键
);
INSERT INTO teacher(tea_id,tea_ID_num,tea_birth,tea_name,tea_gender)
values(1,'102315198102215425','1981-02-21','王洪','女'); # 添加一条记录，值的顺序跟字段名的顺序匹配，字段名的顺序跟表里面的字段顺序无关
INSERT INTO teacher VALUES(2,'102315198503136216','李刚','男','1985-03-13'); # 添加一条记录：省略字段名，值的顺序跟表中字段的顺序一致
INSERT INTO teacher(tea_id,tea_name,tea_gender)
VALUES (3,'张华','男'); # 添加一条记录，只给部分字段添加，被忽略的字段，不能有非空约束或者得有默认值
INSERT INTO teacher VALUES(4,'102315197601213162','王和','女','1976-01-21'),
(5,'102315198205168435','阿Q','男','1982-05-16');
# 一次添加两条记录，省略了字段名
INSERT INTO teacher
SET tea_id=6, # 字段=值的形式
tea_name='李飞',
tea_gender='男';
SELECT * FROM teacher; # 查询teacher中的所有的记录
UPDATE teacher # 更新一条信息
SET tea_ID_num='102315197001207511',tea_birth='19700120'
WHERE tea_id=3; # 特别注意，不能少了WHREE字句
UPDATE teacher
SET tea_gender=null # 删除一条记录某个字段的值，可将其UPDATE为null
WHERE tea_id=1;
DELETE FROM teacher WHERE tea_id=1; # 删除符合特定条件的记录：使用DELETE的时候，对条件的限定千万不能少，否则可能会删除整张表的记录
UPDATE teacher
SET tea_gender='其他'; # 没有限定条件，会导致tea_teacher的值全部改为'其他'
DELETE FROM teacher; # 删除teacher表的所有记录
TRUNCATE TABLE teacher; # 清空teacher表
DELETE与TRUNCATE的区别
- 二者都能将整个表的数据全部删除
- DELETE是DML语句；TRUNCATE是DDL语句
- DELETE后面一般要跟WHERE字句来限定要删除的记录，TRUNCATE则不能跟WHERE字句，而是删除整张表的记录
- 对于有AUTO_INCREMENT约束的字段，DELETE后，从原来的记录的值开始自增；TRUNCATE则重新从1开始
简易的单表查询
SELECT tea_name,tea_gender FROM teacher; # 查询2个字段
SELECT tea_id,
tea_ID_num,
tea_birth,
tea_name,
tea_gender FROM teacher; # 查询5个字段，结果按照这个顺序排列
SELECT * FROM teacher; # 用通配符查询所有字段。注意效率较低
SELECT * FROM teacher WHERE tea_id>3; # 单条件查询所有字段
SELECT * FROM teacher WHERE tea_id BETWEEN 2 AND 6; # BETWEEN X AND Y。[X,Y]且X<Y
SELECT * FROM teacher WHERE tea_id NOT BETWEEN 2 AND 5; # 对BETWEEN AND 取反
SELECT * FROM teacher WHERE tea_id IN (1,3,4); # IN查询，相等匹配多个值
SELECT * FROM teacher WHERE tea_id_num IS NULL; # 匹配NULL
SELECT * FROM teacher WHERE tea_id_num IS NOT NULL; # 匹配NOT NULL
SELECT DISTINCT tea_gender FROM teacher; # 查询结果去重
SELECT DISTINCT tea_name,tea_gender FROM teacher; # DISTINCT可用于多个字段
SELECT * FROM teacher WHERE tea_name LIKE '王_'; # LIKE模煳匹配，下划线匹配单个字符，不包括空字符串，用\转义
SELECT * FROM teacher WHERE tea_ID_num LIKE '102315197%'; # %匹配若干个字符，包括空字符串，用\转义
SELECT * FROM teacher WHERE tea_id>2 AND tea_id<6; # AND连接多个条件，交集
SELECT * FROM teacher WHERE tea_id>3 OR tea_birth>'1982-01-01'; # OR连接两个条件，并集
SELECT * FROM teacher WHERE tea_name LIKE '李_' AND tea_gender='男';
SELECT * FROM teacher WHERE
tea_id<5 AND
tea_name LIKE '王_' AND
tea_birth>'1980-01-01'; # AND连接3个条件
SELECT * FROM teacher WHERE
tea_name LIKE '李_' AND tea_birth>'1980-01-01' # AND 与 OR联用，AND优先级高于OR
OR
tea_name LIKE '王_' AND tea_birth<'1980-01-01';
