database
自己整理
# 数据库
SQLserver Microsoft公司 不开源
MySQL Oracle公司 开源
Oracle Oracle公司 不开源
MariaDB MySQL的分支 完全替代mysql
> DBMS DataBase Management System
> 非关系型 nosql(not only sql) 更强大
# 端口号
65535 个
# character
Unicode 占内存 3字节
utf-8 有多少字节存多少字节
gbk 中国
utf8mb4 utf8的超集
# sqlyog 连接mysql8.0 以上 改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ##修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; ##更新一下用户的密码 password 为自己想要重新设置的密码
FLUSH PRIVILEGES; ##刷新权限
# window删除服务
dos窗口 sc delete MySQL
# 查询效率
> 多表联查
产生临时表 先过滤数据在建立临时表 临时表就小很多
SELECT s.sno,g.cno,s.degree
FROM
(SELECT cno,MAX(degree) maxd FROM scores GROUP BY cno) g
LEFT JOIN
(SELECT sno,cno,degree FROM scores) s
ON g.cno=s.cno AND g.maxd=s.degree;
> a% %a% a__
a% 最快 首先开头的就去掉很多
%a% 慢 值中所有的字符都需要 遍历一遍
数据库安装 MariaDB 配置环境变量bin
默认端口号3306 账号密码root lvjing 字符集utf-8
命令行
测试mysql -V
链接数据库 mysql -uroot -plvjing
# 数据库操作
新建 create database lvjing default character set utf8;
查看 show databases;
删除 drop database lvjing;
使用 use lvjing;
退出 exit
版本 select version();
时间 select now();
当前 select database();
# 表操作
创建 create table lvyingying ( id int(11),name varchar(100) );
create table lv(
id int unsigned primary key not null auto_increment,
name varchar(20) not null
);
查看 show tables;
删除 drop table lvyingying;
结构 desc lvyingying;
重命名 rename table lvjing to zhaoqing;
添加字段 alter table lvyingying add column age varchar(8) default '123';
修改字段 alter table lvyingying modify age varchar(8) default '456'; 不重命名
alter table lvyingying change age sex varchar(8) default '789'; 重命名
删除字段 alter table lvyingying drop sex;
alter table lvjing alter column age int(10)
创建索引 create index id_index on lvyingying(id);
删除索引 drop index
# 数据操作 CRUD 增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)
新增 insert into lvyingying values( 1,'lvjing');
insert into lvyingying values( 2,'jing'),(3,'kjkl');
insert into lvyingying (id,tel) values (null,123),(null,345);
查询 select * from lvyingying;
select name as '姓名',id '序号' from lvyingying where age = 23;
修改 update lvyingying set name = 'lvjing',age = 23 where id = 1;
删除 delete from lvyingying where id = 5;
truncate删除所有记录 truncate table lvjing;
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数
# 数据类型
位bit 字节B KB MB G T
1字节 = 8位 一个汉字 java中两个字节 u8中 一个汉字占3字节
字符 char 长度固定,查询高效 2000 varchar 查到可变,效率低,给的值多长就多长 4000 大文本
数字 int(10) bigint
float double 不约束小数位数
numberic(5,2) decimal(4,1) 123.4 decimal 数据类型存储精确的数字表示形式，没有近似值。
decimal(p,s) 0 <= s <= p <= 38，
日期 data() YYYY-MM-DD time() HH:MM:SS
datetime() YYYY-MM-DD HH:MM:SS timestamp()存的是秒数1970-01-01 YYYY-MM-DD HH:MM:SS
图片、声音
图片: blob
而只存储其访问路径，文件则存放在磁盘上。
# 约束
not null 不能为空
unique 唯一,不能重复
primary key 主键 唯一,不能是null 可以放在创建表的最后 primary key (id)
foreign key 外键 foreign key(user_id) references tb_user(id) 两个表有外键约束, 只要还在关联外键约束就不能删除表中数据 删除外键后才能删除数据
int unsigned 无符号整形
auto_increment 自增
check 限制列中值的范围 放在创建表的最后 check(age > 0 and age < 110)
default 向列中插入默认值
# 约束
create table `l`(
`id` int primary key auto_increment,
`title` varchar(10) not null,
`name` varchar(10) unique,
`age` int default 20,
check(age>0 and age<110)
> primary key (`id`)
> foreign key(`id`) references v(`lid`) # v中的lid 是l中对应id 互为外键
);
> 添加外键 alter table l add constraint fk_l_v foreign key(`id`) references v(`lid`);
> 撤销外键 alter tabke l drop foreign key fk_l_v;
> 添加主键 alter table l add primary key (`lid`);
> 删除主键 alter table l drop primary key ;
> 添加主键自增 alter table npt change id id int not null auto_increment primary key;
# 基本函数
全大写 lower() select lower(name) from lv;
全小写 upper() select upper(name) from lv;
长度 length() select length(name) from lv;
截取子串 substr() select substr(name,2,3) from lv;
拼串 concat() select concat(name,'hello') from lv;
concat_ws('~',a,a,a,...)连接多个字符串 select concat_ws('-',sid,sname,sdegree) from students; 01-lvjing-100
group_concat()
# group_concat() 重名的人 名字只出现一次,且显示所有id
# .. 将group by 产生的同一个分组的值连接起来,返回一个字符串结果
# .. 语法: group_concat( [distinct]要连接的字段 [order by 排序字段] [separator'分隔符'] )
select sname,group_concat(sid order by sid separator '-') sid from students group by sname;
select sname,group_concat(concat_ws('~',sname,sid)) from students group by sname;
替换 replace() select replace(name,'l','w') from lv;
小数四舍五入 round() select round(age/10) from lv;
小数向上取整 ceil() select ceil(age/10) from lv;
小数向下取整 floor() select floor(age/10) from lv;
数据唯一标识 uuid() select uuid();
年月日时分秒 now() select now();
年月日 curdate() select curdate();
时分秒 curtime() select curtime();
年 year() select year(now());
月 month() select month(now());
日 day() select day(now());
时 hour() select hour(now());
分 minute() select minute(now());
秒 second() select second(now());
日期转字符串 date_format(now(),'%Y-%m-%d %H:%i:%s')
字符串转日期 str_to_date('2014-06-14','%Y-%m-%d')
每月最大日期 last_day(now())
select date_format(now(),'%Y-%m-01'),last_day(now())
转义字符 反斜杠 \
# 条件查询
where
select * from lv where id = 1;
select name,id+age from lv where sex = 0;
or
select name form where id = 1 or id = 2;
去重 distinct
select distinct name from lv;
模糊查询 like %
select * from lv where name like '%n'; 以n结束
select * from lv where name like 'n%'; 以n开始
select * from lv where name like '%n%'; 包含n
select * from lv where name like 'n_'; _表示一个字符
过滤null
select * from lv where sex is null; 过滤sex是null
select * from lv where sex is not null; 过滤sex不是null
字段值为null时替换
nvl
select nvl(lvjing,'lv') from emp;
范围
select * from lv where id <= 5 and id >= 1;
select * from lv where id between 1 and 5;
分页
select * from lv limit 3; 前三条
select * from lv limit 1,3; 三条,从1开始
select * from lv limit 0,3; 三条,从0开始
排序
select * from lv order by age asc; 小到大
select * from lv order by age desc; 大到小
别名
select name as '姓名' from lv;
select name '姓名' from lv;
是否为null
select ifnull(age,23) from lv; 如果是null就替换为指定值 因为null不参与运算
算年薪 奖金为零
select ename 姓名,sal*13+ifnull(comm,0) *13 年薪 from emp
# 高级应用
聚合
总数 count() select count(*) from lv;
最大值 max() select max(age) from lv;
最小值 min() select min(age) from lv;
求和 sum() select sum(age) from lv;
平均数 avg() select avg(age) from lv;
分组
group by having
select name,max(age) from lv group by sex;
select name,min(age) from lv group by sex having min(age) = 23;
union 把两个结果集相加 重复的值去掉 两个结果集列对应，个数和类型一致
union all 结果集中所有的值 包括重复的值
多条件判断 case when then else end
case degree when 'A' then '优' else '不及格' end
case degree when 'B' then '良' else '不及格' end
case degree when 'C' then '中' else '不及格' end
case when degree = 'A' then '优' when degree = 'B' then '良' when degree = 'C' then '中' else '不及格' end
case when round(avg(sc.sdegree),2) is null then 0 else round(avg(sc.sdegree)) end
判断 if
if(a,b,c) a为true 结果为b 否则为c if(sva=1,"男","女")
时间差函数
timestampdiff(year,st.sbirth,now()) timestampdiff(year,A,B) A和B相差的年数 month day hour minute second
MyBatis MySQL limit分页含运算动态语句 limit 不能识别运算符
<bind name="key_offset" value="(pageNum-1)*pageSize"></bind>
select * from table limit #{key_offset},#{pageSize}
# 查询每科成绩最高的 学生姓名 课程名称 成绩
select st.sname,co.cname,s.degree from
(select cno,max(degree) maxd from score group by cno) g
left join
(select sno,cno,degree from score) s on g.cno = s.cno and s.degree = g.maxd
left join student st on s.sno = st.sno
left join course co on s.cno = co.cno;
# 创建数据库 中间表的创建
drop database if exists lvjing;
create database lvjing default character set utf8mb4 collate utf8mb4_ general_ci;
set names utf8mb4;
set foreign_ key_checks = 0;
> drop dabase if exists jt; exists存在
> create database jt default character set utf8mb4 collate utf8mb4_ general_ci;
collate在sql中是用来定义排序规则的
_BIN 指定使用向后兼容的二进制排序顺序。
_BIN2 指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。
_Stroke 按笔划排序
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分
_KI(KS) 是否区分假名类型，KI不区分，KS区分
_WI(WS) 是否区分全半角，WI不区分，WS区分
> set foreigh_ key_checks=0; 禁用外键
> set names 'xxx'
命令可以使 character_set_client、character_set_connection、character_set_results 三者统一：
client (character_set_client) -----> character_set_connection -------> mysqld ------> client(character_set_results) 减少编码转换的需要。
character_set_client 是指客户端发送过来的语句的编码;
character_set_connection 是指mysqld收到客户端的语句后，要转换到的编码；
而 character_set_results 是指server执行语句后，返回给客户端的数据的编码
# 创建表
drop table if exists lv;
create table lv(
`id` int(4) auto_increment comment 'ID',
`title` varchar(50) not null comment '标题',
primary key (`id`)
UNIQUE KEY `uk_configinfo_datagrouptenant` (`id`)
)engine=innodb auto_increment=2 comment='通知' default charset=utf8;
> id int(4) auto_increment comment 'ID',
> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='菜单管理';
engine=innodb 引擎
auto_increment=1 从开始自增
comment注释
# 提高加载速度 加锁
LOCK TABLES `sys_depts` WRITE;
INSERT INTO `sys_depts` VALUES ();
UNLOCK TABLES;
> LOCK TABLES为当前线程锁定表。
> UNLOCK TABLES释放被当前线程持有的任何锁。
当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表会自动被解锁。
> 如果一个线程获得在一个表上的一个READ锁，该线程和所有其他线程只能从表中读。
> 如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。
# drop delete truncate
drop 删除库 表
delete truncate 删除表中数据 delete可以删除满足条件的数据 truncate删除所有数据
对应自增数据 delete不会使自增值清零 truncate是删除表数据和定义,再重建表,所有自增从头开始
# where having
> where 中不能出现聚合函数
> 使用group where是对分组前的数据进行过滤 having是对分组后的数据进行过滤
> having中不能乱使用字段
# 子查询
> 不支持order by limit
# 子查询
> 单行子查询 =
tony这个年龄的人数
select count(0) form student where age = (select age from student where name = "tony");
> 多行子查询 in
公司id为100 和200人员的工作的详细信息
select * from empw where job in (select job from emp where id in (100,200));
# 表关联
1:1
1:n
n:1
n:n 使用中间表存储两个表的主键(复合主键)
# 多表联查
> 笛卡尔积
select * from dept,emp; 两张表的数据拼接起来(乘积 表A 12条记录, 表B 15条记录, 查到12*15=216条)
不会用到笛卡尔积 但是在使用多表查询时,查询过程中 先构建大的结果集(笛卡尔积),再根据条件进行过滤
> join
和 inner join效果一样 ( 12条数据 交集 -- 1:1 )
> 内连接
inner join 两个表的交集
> 左连接
left join 左表中的数据都展示 右表中没有的展示null ( 12条数据 所有左表 -- 1:1 )
> 右连接
right join 右表中的数据都展示 左表中没有的展示null ( 15条数据 所有左表 -- 1:1 )
> select d.name,e.ename,e.job from emp e inner join dept d on e.deptno = d.deptno where d.dname = 'research';
---
# testone : testtwo 1:1
create table testone(id int primary key auto_increment,name varchar(10));
insert into testone values (null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer"),(null,"qer");
create table testtwo(id int primary key auto_increment,age varchar(2));
insert into testtwo values (null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8"),(null,"8");
select * from testone,testtwo; # 180 12*15 乘积 笛卡尔积
select * from testone tto inner join testtwo ttt on tto.id = ttt.id; # 12 交集
select * from testone tto left join testtwo ttt on tto.id = ttt.id; # 12 所有testone
select * from testone tto right join testtwo ttt on tto.id = ttt.id; # 15 所有testtwo
---
# 索引
> 主键索引 alter table table_name add primary key(column_name);
> 普通索引 create index index_name on tb_lvjing(`id`);
> 唯一索引 create unique index index_name on table_name(column_name)
> 复合索引 create index index_name on table_name(id,name)
> 删除索引 drop index index_name on table_name
> 查看索引 show index from emp
> 普通缩影 alter table emp add index index_name (column_name)
> 唯一索引 alert table emp add unique(phone)
> 复合索引 alert table emp add index idx_phone_cardno(phone,cardno)
> 删除索引 alert table emp drop index phone
> 最左特性: 创建索引(k1,k2,k3) 相当于（k1）、(k1,k2)和(k1,k2,k3)
> 优点: 查询快
> 缺点
在数据量大的时候, 新增数据需要更新索引 需要的时间就会很多
> explain 检查这个sql语句是否使用了索引
explain
select * from teacher where tname = '易天';
# 视图
第一次查询的结果放在视图中, 第二次出现相同的内容 直接在视图中查询
查询完的就是一个表(视图) 不能再进行优化
新建: create view 视图名 as sql语句
查看: select * from 视图名
> 优点
一定程度保证数据的安全性,只能看到结果集
执行sql语句的到的几个集存在视图中, 下次不必再写,再查库
> 缺点
占内存 数据量大 数据重复
库中数据有更新,视图需要重新构建
sql不能进行优化
# 事务
> 条件
原子性 一致性 隔离性 持久性 ACID
原子性: 一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节. 如果事务执行过程中发生错误,那么这个事务会回滚到事务发生之前的状态,像没有执行过一样
一致性: 在事务开始之前和事务结束以后,数据库的完整性没有被破坏. 这表示写入的资料必须完全符合所有的预设规则,包含资料的精确度,串联性,以及后续数据库可以自发地完成预定的工作
隔离性: 数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致.
持久性: 事务处理结束后,对数据的修改是持久性的,即便系统故障也不会丢失.
> 隔离级别
读未提交read uncommitted 读提交read committed 可重复读repeatable read 串行化serializable
mysql默认 可重复读 select @@tx_isolation repeadable-read
> 可重复读 同一个事务同一个select结果集相同
在事务执行期间会锁定该事务以任何方式引用的所有行
同一个事务中发出 同一个 select两次或多次 结果是一样的
可以多次检索同一行集,并对他们执行任意操作,直到提交或回滚
> 多事务并发执行时 并发问题 ?? 脏读, 不可重复读, 幻读(统计) -------修改事务的隔离级别
脏读: 别的人读取了别人没有提交的事务 A(a1,a2,a3) B() A执行a1没提交, B获取了a1,B的a1就是脏读 =隔离级别
读别人已提交的
不可重复读: A事务没结束,B把事务更新了 A读多次,每次一样,但出现了不一样就是不可重复读 =不让B更新 加锁
幻读: A正在统计100, B删了10 A读到100 , 实际90 =给表加锁
脏读 :: 一个事务中访问了另一个事务未提交的数据
不可重复读 :: 一个事务查询同一条记录多次, 查到的结果不一样
幻读 :: 一个事务查询两次得到的记录条数不一致
> 数据库引擎
InnoDB 就是把写的SQL语句翻译
> 事务操作
set @@autocommit = 0; 1自动 0手动
begin; 开启事务
savepoint lvjing; 保存点
delete from lj; 操作
rollback to lj; 回滚到保存点
release savepoint lvjing; 释放保存点
commit; 提交事务 提交之后检查点就自动作废,删除了
* 数据库可不可以自己判断事务是否执行失败, 进行回滚 还是要自己手动在错误中回滚 ???????????????????????????????
* 数据库如何判断 事务中 操作正确还是错误
* 什么情况下 会发生回滚
> spring中对数据库操作 发生异常时进行回滚事务
> 回滚: 删库之后执行一条查询show tables会报错,发生回滚
[--删除表中数据--查数据会回滚--]
set @@autocommit = 0;
begin;
savepoint zhaojing;
delete from lv;
select * from lv;
rollback to zhaojing;
release savepoint zhaojing;
commit;
select * from lv;
# PowerDesigner
新建模型 Model types -> Physical Data Model -> Physical Diagram
添加表 -> 双击表进行编辑 General表 Colunms字段 右键字段properties属性
导出sql语句 数据库 -> Generate Database
导入 文件 -> Reverse Engineer -> Database
# sql定义 执行顺序
(8) SELECT (9) DISTINCT column,... 选择字段、去重
(6) AGG_FUNC(column or expression),... 聚合
(1) FROM [left_table] 选择表
(3) <join_type> JOIN <right_table> 链接
(2) ON <join_condition> 链接条件
(4) WHERE <where_condition> 条件过滤
(5) GROUP BY <group_by_list> 分组
(7) HAVING <having_condition> 分组过滤
(10) ORDER BY <order_by_list> 排序
(11) LIMIT count OFFSET count; 分页
1. from [left_table]
2. on <join_condition>
3. <join_type> join <right_table>
4. where <where_condition>
5. group by <group_by_list>
6. agg_func(column or expression),...
7. having <having_condition>
8. select
9. distinct column,...
10. order by <order_by_list>
11. limit count offset count;
* java -varsion (什么jvm)
* set java_ home (检查JAVA_HOME)
* mysql -uroot -proot (登录数据库)
* status 登录数据库后(查看数据库版本)
* cd .. (上一级目录)
* mvn -v 在maven/bin 目录中cmd (查看mvn版本)
* node -v cmd运行(查看node版本) npm -v
# 导出数据库
* mysqldump -uroot -proot ry-config>d:/ry-config.txt (导出数据库表)
# 查询总结 SQL优化
> 查询, 一个表A中有的行 另一个B没有, 需要B中列的 A中行有的值
.. 先把两个表连接起来,B中没有的就是null, 再对大表进行操作
select * from students st left join scores sc on st.sid = sc.sid group by st.sid having avg(sc.sdegree) <= 60 or sc.sdegree is null;
> A B中都有 需要按条件查询
.. 可以先把 B中满足条件查出来 再联合A
select st.sname,st.sid,sc.a from students st inner join ( select sid,avg(sscore) a from scores group by sid having avg(sscore) >= 60) sc on st.sid = sc.sid;
> group_concat() concat_ws() ifnull()
.. ifnull() 的使用 在需要判断为null的地方 添加 ifnull(degree,0) 结果: 不是null的为degree的值 是null为0
select sname,group_concat(concat_ws('~',st.sid,st.sname,ifnull(sc.degree,0))) degree from students st
left join (select sid,sum(sdegree) degree from scores group by sid) sc on st.sid = sc.sid
group by st.sname
sname degree
吴兰 06~吴兰~65,11~吴兰~0
周梅 05~周梅~163
孙风 03~孙风~240
李云 04~李云~100
王菊 08~王菊~0
赵雷 01~赵雷~269,10~赵雷~0,9~赵雷~0
郑竹 07~郑竹~187,12~郑竹~0
钱电 02~钱电~210
> SQL优化
01. 索引 alter table lvjing add index index_name(name);
02. 视图
03. 先过滤表中的数据, 再进行分组 多表联查
4. 避免全表扫描
不使用select * ..(全表扫描) 使用具体字段 select id,name ..
where中or也会全表扫描
5. 数据类型的使用
字符串类型使用varchar 尽量不用cahr 占用内存
使用数值类型代替字符串类型 例: 1-男 2-女
6. like %
select id,name from lvjing where name like '吕%';
7. 索引失效
!=，<>，is null，is not null 使用这些会索引失效
distinct 过滤字段要少
where中对字段进行表达式操作
使用索引列上内置函数索引失效
8. 提升性能 优化表
批量插入数据性能提升 比一条一条快 原因: 新增sql语句主要事务控制,多条需要多个事务的开启关闭
分批删除数据 一次太多可能会造成锁表
伪删除 1-正常 0-已删除
group by 先过滤数据再分组
where和order by中常出现的字段就创建索引。
使用union all
# 解决navicat sql_mode=only_full_group_by
> 在my.ini中 [mysqld] 中添加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# oracle 使用
> 安装 口令:123456
> port: 1521 2030
> 客户端: pl/sql
mysql 数据库--表--数据
oracle 用户--表--数据
> 1. 创建用户 给用户角色权限 使用创建的用户登录
> table为 用户管理数据的表
> 存储过程, 触发器, 视图, ->不用了,mysql可以完全代替 表, 自增序列, 登录用户
> 2. 创建表 编辑数据
> [--不同--]
> 1. 日期 +-----mysql %Y%m%d %H%i%s YYYY-mm-dd HH:MM:SS
dd-mon-yy 09-6月-99 1999年6月9号
修改: alter session set nls_date_format = 'yyyy-mm-dd';
> 2. 一个汉字3个字节 +-----mysql 使用utf-8,一个汉字占3个字节
> 3. decode 条件判断 +-----mysql没有的函数
select sname,ssex,decode(ssex,0,'男',1,'女') sex from stu;
> 4. case when then else end +----- 与mysql一样
select sname,ssex,case ssex when '0' then '女' else '男' end sex from stu;
> 5. 第一条数据 +----mysql limit 1 +----sql server select top 1
select * from stu where rownum <= 2;
> [虚表] +--------mysql没有
构成select的语法规则, 表中只有一条记录 虚拟的表
select 1 from dual;
select "lvjing" from dual;
select sysdate from dual;
select sys_guid() from dual;
> [序列] sequence 和主键自增效果 +-----mysql没有
新建sequence 名: STU_ID_SEQ
--第一次访问先执行nextval
select STU_ID_SEQ.nextval from dual
--获取当前值(必须执行过nextval)
select STU_ID_SEQ.currval from dual
--给id赋值,序列会自动+1
insert into stu(sid) values(STU_ID_SEQ.nextval)
> [视图] 只能用来查询 不能删除/修改
create or replace view STU_V as select * from stu where ssex = 0; select这句SQL语句不能再优化,优化后的结果就和视图中的数据不一样
select * from STU_V;
> [触发器] 定义触发器 要发生的行为 执行触发器
记录生效点：BEFORE/AFTER
记录的操作：INSERT/UPDATE/DELETE
--设置触发器 ---禁用,因为无法控制程序员的权限,可能有安全隐患
create or replace trigger UPDATE_STU_TRI
before update on stu
for each row
declare
-- local variables here
begin
--如果 SEX>0,统一设置成1
IF :NEW.SSEX>5 THEN
:NEW.SSEX := 1;
END IF;
end UPDATE_STU_TRI;
--使用触发器:
SELECT * FROM STU
update stu set ssex=10;--触发器执行,都是1了
update stu set ssex=3;--不满足触发器,就是3
**--------------------- 数据库注意 -----------------------**
改oracleIP
获取当前电脑网络ip 命令提示符->ipconfig
oracle安装目录下
D:\oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
修改 HOST = 192.168.1.6
mysql端口改为8088
命令提示符
sqlplus / as sysdba
exec dbms_xdb.sethttpport(8088)
笔记1
01-26 day19
--数据库
关系型数据库
非关系型数据库 nosql
Mysql
Oracle
--Mysql
行和列
DOS
可视化工具
--数据库结构
数据库 - 表 - 数据
--SQL语言
Structured Query Language
新增 删除 修改 查询
insert delete update select
DML 数据操作 insert delete update select
DDL 数据库定义
--操作库
新建 CREATE DATABASE jt_yh default character set utf8;
Query OK, 1 row affected (0.002 sec)
查看 show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jt_yh |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.001 sec)
删除 drop database jt_yh;
Query OK, 0 rows affected (0.004 sec)
--工具
navicat
--创建表
1.选中库 use 名字; use yhmisdb2012; Database changed
2.新建表 create table 表名(字段名称 字段类型(最大长度));
字符串varchar '1号店' 整数 int 10
MariaDB [yhmisdb2012]> create table tb_door(
-> id int(11),
-> door_name varchar(100),
-> tel varchar(20)
-> );
3.展示表 show tables;
4.删除表 drop table tb_door2;
5.展示表结构 desc 表名;
--数据操作 CRUD444444444444444
1.新增
#1.#是SQL里的注释: 向tb_door表里插入数据,
#括号里varchar类型的值,使用一对单引号包裹
#括号里需要指定每个字段的值 值的顺序要和字段顺序匹配
insert into 表名 values(字段的值,字段的值);
只给一部分字段赋值
insert into tb_door(id,tel) values(6,'18340000885')
2.查询
所有列 select * from tb_door
指定列 select id,door_name from tb_door
3.修改
update 表名 set 字段名 = 字段值
没有限制条件,会修改表里的所有数据
4.删除
delete from 表名
5.排序
6.记录总数
7.表中数据自动维护的
删除之后新增,值不连续,已经用过了
--数据库里的数据类型
字符:
char:长度固定,查询高效,占内存
varchar:长度可变,效率低,看值多长就用多少内存
数字
int float double
日期
date time datetime timestamp
--主键 id 主键约束 唯一的不能为空, 数据库自增
id int(11) primary key auto_increment
指定主键列,且自增
create table user(
id int(11) primary key auto_increment,
name varchar(50)
);
insert into user values(null,'lvjing');
--非空约束 not null
create table user(
id int(11) primary key auto_increment,
name varchar(50) not null
);
insert into user values(null,'lvjing');//ok
insert into user values(null,null);//不行
--唯一约束 unique
唯一约束的列不能出现重复的值
create table user(
id int(11) primary key auto_increment,
name varchar(50) not null,
username int(3) unique
);
--检查约束 check
01-27
--基础函数
lower SELECT 'ABC',LOWER('ABC') from dual; --数据转小写
upper select upper(dname) from dept --数据转大写
length select length(dname) from dept --数据的长度
substr SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
#substr(name.2,3)
#1是截取谁 2是开始 3是结束 [2,3]
concat select dname,concat(dname,'123') X from dept --拼接数据
replace select dname,replace(dname,'a','666') X from dept --把a字符替换成666
round select comm,round(comm) from emp四舍五入
ceil向上取整
floor向下取整
select comm,ceil(comm) ,floor(comm) from emp
SELECT UUID()
select now() --年月日 时分秒
select curdate() --年与日
select curtime() --时分秒
select now(),year(now()),month(now()),day(now()) from emp;
select now(),hour(now()),minute(now()),second(now()) from emp;
select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符
--条件处理数据
#where id ＞ １ 条件查询
select *from dept;
insert into dept values(null,'nihao','一区');
select *from dept where deptno > 1;
select *from dept where loc = '一区';
select dname from dept where loc = '一区';
#and or where 1=1类似没条件 where 1=0条件不成立
select dname from dept where loc = '一区' and deptno = 1;
select * from dept where loc = '一区' or deptno = 2;
select * from emp;
#求和
select empno,sal+comm from emp;
#distinct
select distinct loc from dept;
#like 模糊查询
# % 通配符
#--以l开头的 l%
select * from emp where ename like 'l%' ;
#--以a结束的
select * from emp where ename like '%a' ;
#--中间包含a的
select * from emp where ename like '%a%' ;
#--l后面有两个字符的 _代表一个字符位置
select * from emp where ename like 'l__' ;
select * from emp where ename= 'jack';
#null
select * from emp where comm is null;
select * from emp where comm is not null;
#between and
select * from emp where sal>3000 and sal<9000;
#sal [3000,10000] between and 等效
select * from emp where sal between 3000 and 10000;
select * from emp where mgr between 2 and 3;
#limit 分页
#limit 3 展示3调数据
#limit 0,3 0是从哪条数据开始 3是从开始之后列出几条
select * from emp limit 2;
select * from emp limit 1,2;
select * from emp limit 0,3;
#order by
#默认asc升序,desc倒序
select * from emp order by sal asc;
select * from emp order by sal desc;
#统计年薪
select * from emp ;
select ename,job,sal*13+comm*13 from emp ;
#别名 列的名字 as
select ename as 姓名,sal*13+comm*13 as 年薪 from emp ;
select ename 名字,sal*13+comm*13 工资 from emp ;
#ifnull(1,2) 判断是否为null 如果是就使用默认值替换
#1是字段名,2是默认值
select comm,ifnull(comm,0) X from emp ;
select ename 名字,sal*13+ifnull(comm,0)*13 工资 from emp ;
#聚合函数
#max min sum avg count
SELECT COUNT(*) from emp;
select count(1) from emp;
select count(comm) from emp;#只统计非null的 效率慢
select max(sal),min(sal) from emp;
select sum(comm),avg(comm) from emp;
#分组 group by 写非聚合函数
select * from emp;
select deptno,max(sal) from emp group by deptno;
select job,max(sal) from emp group by job;
select job,deptno,max(sal) from emp group by job,deptno;
select job,max(sal) from emp group by job having max(sal)>10000;
oracle
mysql 数据库--表--数据
oracle 用户--表--数据
mysql int double varchar
oracle int/integer double/number varchar2
cgb2012 0614
system root
特有的
视图:
01-28********陈子殊
Gitee码云 nutony /2012 /res
PowerDesigner 表数据
JDBC技术
--事物
-*4个条件ACID
1.原子性 :
2.一致性
3.隔离性
4.持久性
set @@autocommit = 0; //默认是1自动提交，0手动提交
begin; //开启事务
commit; //提交事务
rollback; //回滚事务（就当什么事情也没有发生，数据保持一致）
sqlyog 3306
--隔离级别
读未提交（Read uncommitted）
同一个用户可看过程,不同用户查询看不到中间数据(数据库事物的隔离级别实现的)
读提交（read committed）
同一个,不同用户也可看到结果
可重复读（repeatable read）
串行化（Serializable）
-*删库跑路
预防:存储点 savepoint
开启事物,begin;
设置存储点,savepoint zhao;(名称自定义)
删除操作,delete from emp;
删除正确-提交事物,commit;
删除错误-回滚到保存点,rollback to zhao;
回滚直接结束,rollback;
回滚到保存点后,保存点就会失效
误操作数据恢复
对数据库修改,新增,删除
--子查询
subquery
在select中嵌套一个select语句 嵌套查询
/*columns字段
SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE id = 200);
单值子查询 =
条件:子查询的结果只能返回单个的字段和单个的值,此时才能用=号,,,
多值字查询 in
SELECT * FROM emp WHERE job IN (SELECT job FROM emp WHERE id = 100 OR id = 200);
SELECT * FROM emp WHERE job IN (SELECT job FROM emp WHERE id IN (100,200));
条件:返回结果可以使多个值,子查询返回的必须只有一列
--表关联
表之间关系, 直接关系
dept 部门表/ emp 员工表/ empext 员工扩展信息表/student 学生表
1.一对一 1:1 主表:从表 主表的主键就是子表的主键pk,也是子表外键fk
2.一对多 1:n 在子表中添加记录时增加一个外键fk 主从表
3.多对一 n:1
4.多对多 n:n 需要一张中间表来存储两边的主键,两个合起来家叫做复合主键
老师对学生(一个老师教多个学生,一个学生听多个老师的课)
使用箭头指示关系 箭头 主表 箭尾 从表
from 主表 inner join 次表 on 条件 and 条件 ,
inner join
--主从表里对应的都有的展示,其他不展示
left join ,
--左边表中的数据都展示,对应右边没有的数据为null
right join ,
--右边表中的数据都展示,对应左边没有的数据为null
********************
SELECT d.dname,e.ename,e.job FROM emp e INNER JOIN dept d ON e.deptno = d.deptno where d.dname = 'research';
主键在使用多表联查中没有必然联系
在联查中使用的两个表的联系不一定是主键
--索引
index 数据库表的索引,快速访问数据库表中的记录表的作用
全表遍历
一个一个找
索引表
排序,
索引表遍历
排序
二分查找
从数据中间取值,判断大小,在下一个区间再取值
***主键自动创建索引
***在创建索引时会额外占用空间,并排序
主键自动创建索引,索引单独占用空间,创建是排序,利用二分法查找数据，数据量越大越明显
索引大户句酷优化第一步
优点：性能高
缺点：额外占用空间,也成为冗余
新增,修改,删除都会造成索引需要重构(重新排序) 需要额外的时间,效率就低了
一般在where条件中字段,在order by排序中的字段菜需要创建索引
主键索引 primary key
唯一索引
alter table empext add unique(phone);
数据库可以进行数据唯一值查询
复合索引 多个索引列
alter table empext add idx_phone_cardno(phone,cardno);
复合索引,不是唯一性的,可以重复
删除索引
alert table empext drop index phone;
2021-01-29
rollback;事务回滚,回滚之后就提交了
savepoint zhao;rollback to zhao;commit;回滚到保存点
事务,不理解
索引
使用情况(什么列):常在where条件或者order by排序频繁出现的列,单独创建索引
--PowerDesigner
general columns preview
mysql 字段类型
varchar(11) int(9) long(100)
numerice/decimal(8,2)
datatiome/timestemp
外键 商品分类表:商品表 1:n
商品表中有外键
实际操作中不创建外键
建表 先建没关系的
P主键 F外键 M非空
numric(8,2) 浮点数 8位 2个小数(四舍五入,999999.99) mysql中使用decimal 64.12
外键约束
多个表有相同的数据,在某一个表中增加数据会出错
测试时不加外键约束,开发者添加正确数据,程序正式上线时再添加外键
SQL执行顺序
--超级文档面试题40
1-28题
02-01
pdm 物理数据模型
java访问数据表
sql执行顺序
from
on(多表联查)
join
where
group by
聚合
having
select
distinct
order by
limit
JDBC
JAVA访问数据库 程序和数据库交互
java database connectivity
三大框架中的mybatis框架底层封装了jdbc
规范 java接口 java.sql.* 有jar包
数据库迁移
**
开发步骤 jdbc
eclipse
lib src 拷jar包
build path
TestJDBC.java类
localhost 等价于127.0.0.1
git 团队协作开发
版本控制
CVS,SVN,Git(Gitee)
保存多个版本的文档,代码
版本控制
add 把工作空间扔到本地索引(日志)
commit 把本地索引扔到本地仓库(文件副本)
push 把本地仓库扔到远程仓库
工作空间 workspace 自己的代码
本地索引 index 暂存区 保存日志(新建文件,删除文件)
本地仓库
远程仓库
工作空间->本地索引->本地仓库->远程仓库
clone
pull 拉取新的
远程仓库->工作空间
master主分支
git bash
*
02-02
statement
preparedStatement
callStatement
注册驱动
获取链接
创建PS对象
设置?参数值
执行SQL
解析结果集
Git
代码管理,多个历史备份
工作空间
本地索引
本地仓库
远程仓库
add
commit
push
clone/pull
文件夹(名字与仓库一致),右键
Git 全局设置:
git config --global user.name "赵庆"
git config --global user.email "2792837349@qq.com"
:config 配置
:-- 参数
:global 全局
创建 git 仓库:
mkdir web2012
-创建目录
cd web2012
-进入目录
git init (执行一次)
-初始化 一套系统
-作用:产生 .git文件, 使文件夹web2012受控
touch README.md
-touch触摸 创建README.md文件 默认空
git add README.md (反复执行)
-把文件提交到本地索引(暂存区)
git commit -m "first commit" (反复执行)
-提交本地索引到本地仓库 -m参数 备注信息(为什么提交)
-把远程仓库与本地仓库建立联系
-当前目录web2012 与 https://gitee.com/zhao-qing-jing/web2012.git 建立联系
git remote add origin https://gitee.com/zhao-qing-jing/web2012.git
-origin 原始库 (分支)
git push -u origin master (反复执行)
-master主分支
-u 参数
-推送,本地仓库提交到远程仓库
已有仓库?
cd existing_git_repo
git remote add origin https://gitee.com/zhao-qing-jing/web2012.git
git push -u origin master
-
ls dir
git add . 代表本目录中所有的变化
git add mst.sql
提交到本地仓库 -m提示信息
git commit -m "html"
指定推送到远程仓库
git push -u origin master
git push
分支brach
master主分支,
fork分支
1564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
