名词概念
- DDL数据库定义语言:CREATE、DROP
- DML数据库操作语言:INSERT、UPDATE、DELETE
- DQL数据库查询语言:SELECT
- DCL数据库控制语言:GRANT、REVOKE访问权限
常用SQL
select version();
select user();
select system_user();
select 100*3-1 as jieguo;
select @@auto_increment_increment -- 查询数据库自增步长
show databases;
use school;
show create database school;
show create table student;
desc student;
update user set password=password(‘123‘) where user=‘root‘ and host=‘localhost‘;
flush privileges;
-- 修改表名
ALTER TABLE teacher RENAME AS teacher1;
-- 增加字段
ALTER TABLE teacher1 ADD age INT(11);
-- 修改字段约束MODIFY
ALTER TABLE teacher1 MODIFY age VARCHAR(11);
-- 修改字段名CHANGE
ALTER TABLE teacher1 CHANGE age age1 INT(11);
-- 删除字段
ALTER TABLE teacher1 DROP age1;
-- 删除表如果存在
DROP TABLE IF EXISTS teacher1;
-- `字段名`用这个符号更安全
-- 常用函数
-- 绝对值
select ABS(-8)
-- 向上取整
select ceiling(9.4)
-- 向下取整
select floor(9.4)
-- 返回随机数
select rand()
-- 判断一个数的符号
select sign(10)
-- 字符串函数
-- 字符串长度
select char_length(‘ssssssss‘)
-- 拼接字符串
select concat(‘i‘,‘o‘,‘u‘)
-- 查询 替换
select insert(‘woai‘,1,2,‘xihuan‘)
-- 大小写替换
select lower(‘YeYuQiuChi‘);
select upper(‘YeYuQiuChi‘);
-- 返回第一次出现字串的索引
select instr(‘YeYuQiuChi‘,‘iu‘);
-- 替换字符串
select replace(‘YeYuQiuChi‘,‘iu‘,‘oo‘);
-- 截取字符串
select substr(‘YeYuQiuChi‘,3,4)
-- 反转字符串
select reverse(‘YeYuQiuChi‘);
-- 聚合函数 分组:符合分组条件的记录为同一组
-- MD5(‘123456‘);数据库级别MD5加密
数据类型
数值类型(宽度)
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | 此处略 | 此处略 | 极大整数值 |
FLOAT | 4 bytes | 此处略 | 此处略 | 单精度 浮点数值 |
DOUBLE | 8 bytes | 此处略 | 此处略 | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
时间和日期
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59‘/‘838:59:59‘ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型(字符长度)
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
引擎
建表的时候即可以选择
- INNODB:支持事务、行锁、安全等,
- MYISAM:支持全文检索等
外键
方式一,创建表的时候,增加约束(麻烦,比较复杂)(导出的表结构是这种写法)
方式二,创建表完成之后添加外键关系
-- 创建表之后添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上两种操作都是物理外键,数据库级别的外键,容易造成困扰,外键概念必须在应用层解决(阿里开发手册)
联表查询
join on、自连接
事务
ACID原则:atomicity原子性、consistency一致性、isolation隔离性、durability持久性
关于隔离性的四个隔离级别:未授权读取(读未提交),授权读取(读已提交),可重复读取和串行化
隔离级别 | 脏读 | 非重复读 | Phantom read |
---|---|---|---|
READ_UNCOMMITTED | allowed | allowed | allowed |
READ_COMMITTED | prevented | allowed | allowed |
REPETABLE_READ | prevented | prevented | allowed |
SERIALIZABLE | prevented | prevented | prevented |
(Phantom幻读,prevented阻止,allowed这里指代可能出现)
set autocommit = 0;-- 关闭自动提交事务
start transaction;
-- 一顿操作
commit;
ROLLBACK;
set autocommit = 1;-- 默认是开启事务自动提交的
SAVEPOINT 保存点名;-- 设置一个事务的保存点
ROLLBACK to SAVEPOINT 保存点;-- 回滚到保存点
RELEASE SAVEPOINT 保存点;-- 撤销保存点
索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
- 主键索引(PRIMARY KEY)
- 唯一的标识,只能有一个列作为主键
- 常规索引(KEY/INDEX)
- 默认的,可通过key或index关键字设置
- 唯一索引(UNIQUE KEY)
- 避免重复的行记录
- 全文索引(FullText)
- 在特定的数据库引擎下才有
- 联合索引
show index from student;-- 显示索引信息
alter table school.`student` add fulltext index `fullindex_studentName`(`studentName`);
-- EXPLAIN分析sql执行状况
EXPLAIN select * from student where match(studentName) against(‘赵‘)
加索引原则
- 索引不是越多越好
- 经常变动的字段不宜加索引
- 小数据量表不加索引
- 索引通常加在常用来查询的字段上
索引的数据结构
- Hash类型的索引
- Btree,Innodb默认的
用户管理
CREATE USER xiaoming IDENTIFIED BY ‘123456‘;-- 创建用户
set password = password(‘123456‘);-- 改当前用户密码
set password for xiaoming = password(‘123456‘);-- 改指定用户密码
rename user xiaoming to xiaoming2;-- 重命名
GRANT SELECT, INSERT ON test.user TO ‘pig‘@‘%‘;-- 授权
GRANT ALL ON *.* TO ‘pig‘@‘%‘;
show grant for xiaoming;-- 查询权限
revoke all privileges on *.* from xiaoming2;-- 撤销权限
drop user xiaoming;--删除用户
备份
- 拷贝物理文件
- 可视化工具中手动导出
- 使用命令行mysqldump导出
- mysqldump -hlocalhost -uroot -proot school student >D:/a.sql
- source d:/a.sql
规范数据库设计
糟糕的数据库设计
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
设计过程
- 需求分析:业务、表
- 概要设计:设计关系ER图
三大范式
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。而通常用的最多的就是第一范式(1NF,列不可再分)、第二范式(2NF,在1nf基础上,一张表只描述一件事情)、第三范式(3NF,在2nf的基础上,每个字段必须与主键相关,不相关的拆开)
但是阿里要求关联查询的表不得超过三张,意味着表不能分得太细(综合考虑)
第一范式(1NF):要求数据库表的每一列都是不可分割的原子数据项。
举例说明:
在上面的表中,“家庭信息”和“学校信息”列均不满足原子性的要求,故不满足第一范式,调整如下:
可见,调整后的每一列都是不可再分的,因此满足第一范式(1NF);
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
举例说明:
在上图所示的情况中,同一个订单中可能包含不同的产品,因此主键必须是“订单号”和“产品号”联合组成,
但可以发现,产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关,但是订单金额和订单时间仅与“订单号”相关,与“产品号”无关,
这样就不满足第二范式的要求,调整如下,需分成两个表:
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
举例说明:
上表中,所有属性都完全依赖于学号,所以满足第二范式,但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”,
而不是主键“学号”,所以需做如下调整:
这样以来,就满足了第三范式的要求。
ps:如果把上表中的班主任姓名改成班主任教工号可能更确切,更符合实际情况,不过只要能理解就行。
jdbc
while(resultSet.next()){}//这种数据结构都是链表形式
SQL注入问题:JDBC执行sql语句传参时,用Java拼接sql时,不讲武德,本来正规是传参数,结果不按规矩,传了一段拼接得sql进去,以图谋不轨,这个问题是跟网站得安全相关的。PreparedStatement对象就是用来防止sql注入。
数据库连接池
项目中一定需要连接池,自己搞网站,断开了数据库连接再重新建立连接巨慢
- DBCP
- C3P0
- 阿里Druid