数据库变量类型
数据库基本命令
创建数据库
create database <数据库名字>;
创建表
create table student(id int auto_increment,name varchar(255),primary key(id),constraint <外键名字> foreign key(name) references <另一个表名>(name)) default charset=utf-8;
外键：表A中的主键ID，对应表B中的A_ID，创建表B的时候，constraint<外键名字> foreign key (B_ID) references B (ID)，A_ID是表A中ID的外键
增加表字段
alter table student add score int defalut null;
删除表字段
alter table student drop column score;
- 删除已有数据的表字段，数据会被删除吗？
修改表字段
alter table tablename modify column price varchar(20);
增加主键
alter table student contraint <主键名字> primary key (id);
增加外键
alter table student contraint <外键名字> foreign key (name) references <表名>(name);
删除外键
alter table student drop foreign key <外键名字>;
删除表
drop table <表名>;
新增表数据
insert into <表名>(id,name,sex,grade) values (‘张三’，‘女’，91)；
删除表数据
delete from tablename where id = 1;
修改表数据
update tablename set name = ‘lisa’ where name = ‘张三’；
order by
单个排序标准 asc正序 desc倒序（空值在前）
多个排序标准 order by A asc， B desc[先按照A正序排，A相同，按照B倒序排]
group by
分组，值相等的为一组，再去计算集函数
集函数
count() avg() min() max() sum()
select s.id,c.name,count(s.sex),avg(s.score)
from student s
LEFT JOIN student_class sc on s.s_id = sc.c_id
Left join class c on sc.s_id = c.c_id
where s.sex = '女'
group by c.c_name
order by c.c_id asc;
单个分组标准
多个分组标准
group by A ,B（先按照A分组，再在A分组的基础上，每一组按照B分组）
having
作用于组
group by A having count(A) > 3（选择统计结果大于3的组）
where
作用于基表或视图
两者结合:找到课程成绩都及格，且选修了3门以上课程的学生学号及课程数
select sno,count(cno)
from sc where grade > 60
group by sno
having count(cno) > 3
通配符
like ‘’ %[0,无穷) _[1] a,b,c
查询满足条件的前5条记录
Oracle
SELECT * FROM TABLE1 WHERE ROWNUM<=N
Mysql
SELECT * FROM TABLE1 LIMIT N
UNION与UNION ALL
select的列数量一致，且字段类型基本一致
UNION过滤重复行，UNION ALL不过滤重复行，因为没有排序，删除等操作，所以效率比UNION高
mysql内连接与外连接，区别
内连接inner join 左连接、右连接、全连接outer join 左排除连接、右排除连接、外排除连接
SQL中的drop、delete、truncate的区别
drop删除表和表数据，delete、truncate删除表数据
delete可以删除table、view的数据；truncate可以删除table数据
delete放在事务中可以回滚，truncate、drop不能回滚。
删除速度：drop>truncate >delete
所以
删除整个表，drop
删除部分表数据：delete+where
删除所有表数据，保留表：truncate
SQL 中使用正则
oracle：select * from tablename where REGEXP_LIKE(name , ‘正则表达式’)；
mysql：select * from tablename where name like ‘%正则表达式%’； select * from tablename where name REGEXP ‘%正则表达式%’;
主键、外键、候选键、超键
主键：primary key 非空，一个表只有一个
外键：对应另外一张表的主键
候选键：最小超键
超键：在关系中唯一标识元组的属性集成为关系模式的超键。超键包含候选键、主键
sql 安全
1.用户端：防止SQL注入，利用or、 引号注入
2.内部人员：最小权限原则
3.开发人员：SQL报错，不可以直接返回给用户，防止泄露数据库信息
4.开发与测试人员：规范编码，不能将登陆信息硬编码到程序中
5.DBA：有完善的数据备份与恢复机制，可以在出现突发状况时力挽狂澜
6.内部规范：所有的用户使用一个共用的帐号是不合理的,每个用户一定要有它自己的登录（我经历过的公司都是共用一个账号的，具体实施有待了解。）
数据库事务
单个逻辑单元执行一系列操作：增删改查等等
原子性：要么全部执行，要么全部不执行【事务回滚】
一致性：事务执行，不影响数据库的一致性
独立性：两个以上的事务不会出现交替运行的状态
持久性：事务运行成功后，数据库更新是永久的
数据库事务语句
开始 begin transaction
提交 commit transaction
回滚 rollback transaction
设置保存点savepoint，作为回滚的标记点。回滚的情况：在一个事务中回滚调用某个事务时，回滚
不同的回滚情况释放的锁是不一样的，锁：表级锁、行级锁、数据锁
存储过程
预编译的代码块，执行效率高；一个存储过程代替大量的T-SQL，可以降低网络通信；确保数据安全
有哪些功能：定义变量，入参、出参、条件语句、循环语句、游标、自定义函数、触发器等
索引
- 索引的本质是一种数据结构，帮助数据库高效获取数据
- 索引的优缺点：
提高检索效率，降低io成本；
索引排序，降低排序成本，降低CPU消耗
降低更新表的速度，比如增删改操作，不仅要保存数据还要保存索引信息。 - 什么时候创建索引？什么时候不需要索引？
主键、外键、频繁查询的字段、分组统计依据字段、排序依据字段 适合创建索引
表数据少的字段、经常增删改的字段、不经常做查询的字段、过滤性小的字段 不适合创建索引 - 索引种类，从功能上划分
普通索引（无约束）、唯一索引（数据唯一约束）、主键索引（非空约束）、全文索引 - 索引种类，从物理实现分
聚集索引（按照主键排序）、非聚集索引（二次查找：索引项（顺序存储）、索引指向的内容（随机）） - 索引存储在硬盘上 防止数据丢失
乐观锁与悲观锁
-
目的：并发控制
-
乐观锁：假设不会发生并发冲突，提交时检查是否有其他事务修改了该数据，如果有，则回滚。不使用锁，不会产生任何锁或死锁。但是如果两个事务同时读取了数据库的某一行，修改，可能会遇到问题。
-
悲观锁：假设会发生并发冲突，提前屏蔽可能会违反数据完整性的操作（使用排他锁），可能会导致死锁
1.在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）
2.如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常
3.如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了
4.其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常 -
适用情况
乐观锁，适用于并发情况出现的几率很小的情况
悲观锁，适用于并发情况出现概率大，数据安全级别高的情况。 -
使用索引查询一定能提高查询性能吗？
不一定。索引本身需要存储空间与维护，不必要的索引反而会使查询时间变慢。
视图
虚拟的表，建立一个表将多表的查询结果联合起来，获取数据更容易。数据权限上，可以暴露部分字段给访问者。
三个范式
一张数据表的表结构所符合的某种设计标准的级别。
检验3NF要求的数据库设计：是否存在数据冗余过大、插入异常、修改异常、删除异常的问题。
对数据库查询速度方面的优化
1.数据库设计：表结构（范式）、索引、分区、固定长度的字段、限制字段长度
2.数据库I/O：增加缓冲区、存在表的级联，不同的表放在不同磁盘，增加I/O速度
3.SQL语句：优化，减少比较次数；限制返回的条目
4.java：数据库连接，反复使用的查询，使用PreparedStatement
参考文章：https://blog.csdn.net/weixin_30363263/article/details/80114142
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
