Mysql
数据类型
1数值型：bit(基本不是0就是1)，tinyint,int,bigint
2字符型：char(定长)，varchar(变长），longtext
3日期型：date(年月日)，datetime(年月日时分秒)，timestamp(精确到毫秒)
4枚举类型：enum(慎用，性能有问题)
5集合形：set
约束
1主键 primary key:不重复，不为空 自增长： auto increment 主键一般都是自增长
2唯一建 unique：不重复
3非空not null
4外键 foreign key Referencrs 存在依赖
mysql支持4大约束，oracle支持5个，check 例：check(gender in ('男'，'女'))
CREATE TABLE course(
`cno` int(5) auto_increment COMMENT '课程编号，主键',
`cname` varchar(50) DEFAULT '' NOT NULL COMMENT '课程名称',
PRIMARY KEY(`cno`)
)
insert into course(cname) values ('Java');
insert into course(cname) values ('.Net');
insert into course(cname) values ('C#');
CREATE TABLE student16(
`id` BIGINT auto_increment,
`stuno` int(6) default 0 not null,
`stuname` varchar(50) default '' not null,
`birthday` date,
`cnum` int(5) default 0 not null,
PRIMARY KEY(`id`),
FOREIGN KEY(`cnum`) REFERENCES course(`cno`)
)
复制表
create table class_copy as select * from class
但是无法复制约束
数据库操作DML-增删改
1增加记录 insert into 表名(列名列表) values (值列表)
没写列名列表就要全部添加
添加多行记录
insert into userinfo(name,regtime)
values
('Tom1','2019-8-8 16:9:27'),
('Tom2','2019-8-8 16:9:27'),
('Tom3','2019-8-8 16:9:27');
2修改记录 update 表名 set 修改哪些列【where 哪些行】
binary 区分大小写
3删除数据 delete from 表名 {where 哪些行}
DML 数据操作语言:数据的增删改
DQL:查询
DDL数据定义语言：数据库对象的增删改
TPL事务控制语言:提交commit，回滚roollback
事务：一组DML语句
set autocommit=false 关闭自动提交
事务四大特性
① 原子性 ：组成事务的DML语句，要么全成功【commit】，要么全失败【rollback】，不会出现半成功半失败的情况
② 一致性 ：事务一旦结束，数据保持一致状态
③ 隔离性 ：事务之间互不影响
④ 永久型 ：事务一旦提交不能回滚
当执行了dcl或者ddl，上面的事务隐式提交
锁：这边要用，必须等待另一边把事务结束，提交完成
select 看什么 from 数据源
null值参与运算，结果一定是空值
select empno,ename,sal,sal*12+IFNULL(comm,0) 年薪,comm
from emp
列别名 通常用在运算表达式或函数上
特殊别名加双引【别命中有空格，特殊字符，区分大小写】
查询去重 distinct
特殊比较运算符
between and
in(参数列表) 等于其中任意一个
like 模糊查询 % 任意多个字符 _一个字符
转译符 \
不能用like 通配事件，不允许
is null
条件查询中不能使用列别名
排序
order by 根据列名排序
默认是升序 asc 降序是desc
排序可以用列别名，列序号（查询的第几列）因为运行顺序靠后
limit
函数
类似java中的方法，通常都有返回值
单行函数，一个返回值
多行函数 ，多个返回值
虚表dual
round (x,y)将x精确到小数点y位
truncate(x,y)截断 将x从小数点后y位截断
字符函数
length() 看看参数多长 char_length(返回多少个字符)
concat()连接 也可以连接列名
insert(x,y,z,a)从y开始，到第三个，把x中的替换成a
lower() |lcase() 将字符串全部装换成小写
upper()|ucase()将字符串全部装换成大写
replace(x,y,z)将x中的y,用z替换
substring(x,y)从x中的y开始截
日期函数
curtime() 时分秒 current_date() 获取当期日期函数 年月日
now() 年月日时分秒
timediff(x,y)时间差
datediff(x,y)天数差
date() time() year() month() day()选取时间的各个部分
-DATE_FORMAT(date,format)：格式化日期；(重点)
select ename,hiredate,date_format(hiredate,'%Y-%m-%d %H:%i:%s') date
from emp
-- 计算时间间隔 TIMESTAMPDIFF()
select TIMESTAMPDIFF(hour,'2008-8-8','2019-8-30')
from dual
CASE
select empno,ename,deptno,(case deptno
when 10 then '会计部'
when 20 then '销售部'
when 30 then '管理部'
else '小卖部' END) 部门
多表连接
多表连接 ：连接1张以上的表
笛卡尔积 ： 一张表中的所有记录和另一张表中所有记录，都发生连接
查询员工姓名，工资，工资等级
SELECT ename,sal,grade,losal,hisal
FROM emp
JOIN salgrade
ON sal BETWEEN losal and hisal
自连接（内连接）
查询员工姓名和直接上级姓名
SELECT e.ename,m.ename
FROM emp e
JOIN emp m
ON e.mgr = m.empno
了解 : 交叉连结CROSS JOIN(笛卡尔积)、
自然连接NATURAL JOIN(相同类型、名称的字段做等值连接)、
USING 子句(指定某一列做等值连接)
掌握
join on
左【外】连接 以关键字左边表为主表，肯定显示
右【外】连接 以关键字右边表为主表，肯定显示
分组函数
在 WHERE 条件中不能直接使用
SUM,AVG,MAX,MIN,COUNT
MAX 最大值,MIN 最小值
NULL : ①影响运算；②不能比较
-- 分组函数不计算NULL值
group by(写在where 后面，order by前面)
使用分组函数时：查看字段不能随意写
SELECT 子句中，非分组函数必须写在GROUP BY后面语法才能通过
WHERE 筛选组前条件
HAVING 组后条件
查询每个部门的平均工资，部门编号,只显示平均工资2000以上的部门
SELECT AVG(sal) avgsal,deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
子查询(嵌套查询)
子查询如果返回多行记录，不能使用单行比较运算符（>,<,=,>=,!=,<=）
oracle支持分组函数嵌套，mysql不支持，需要额外嵌套一层查询
查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
-- 注意：oracle支持分组函数嵌套，mysql不支持，需要额外嵌套一层查询
select d.deptno,dname,count(empno) counts
from emp e
join dept d
on e.deptno = d.deptno
group by d.deptno,dname
having counts > (select avg(counts)
from (select count(empno) counts
from emp e
group by deptno) a)
多行运算符
in
any 任意一个
all 全部
not in (查询，参数) 不能有NULL,必须处理
-- 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
select ename,sal,d.deptno,avgsal
from emp e
join (select deptno,avg(sal) avgsal from emp group by deptno) d
on e.deptno = d.deptno
where sal > avgsal
相关（高级）子查询
select ename,sal
from emp e
where sal = (select max(sal) from emp where deptno = e.deptno)
删除注意的问题：外键问题，默认情况下，外键是删不掉的
视图
本质就是（命名的）查询,如果你想频繁的调用这个查询，就建一个视图
CREATE VIEW emp_20
AS
select * from emp where deptno = 20
drop view emp_20
索引
index
优点 ：加快查询速度
-- 缺点 ：①占内存；②降低了增删改速度
-- 索引是作用在列上的
-- 主键，唯一键，外键自带索引
-- 什么样的列适合加索引 ：经常作为查询条件的，数据量大的时候
-- 建表直接给索引
CREATE TABLE index_tab(
`id` BIGINT auto_increment,
`name` varchar(20),
PRIMARY KEY(`id`),
index(`name`)
)
-- 建表后给索引
CREATE TABLE index_tab1(
`id` BIGINT auto_increment,
`name` varchar(20),
PRIMARY KEY(`id`)
)
CREATE INDEX aaa
ON index_tab1(`name`)
触发器
触发器就是某个表发生一个事件（增删改操作），然后自动的执行预先编译好的SQL语句，执行相关操作。触发器事件跟触发器中的SQL语句是原子性的（要么同时执行，要么同时不执行），这样保证了数据的完整性。
存储过程
简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；
缺点：1不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；
2.不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；
