DDL(数据库定义语言)
<1> 数据库相关
1.1）创建数据库：
create database if not exists 库名 character set utf8 collate utf8_general_ci;
1.2）切换数据库：
use 库名；
1.3）显示建库语句：
show create database 库名；
1.4）显示数据库：
show database [like '匹配条件']；
1.5）删除数据库:
drop database 库名；
<2>表相关
2.1）创建表：
create table if not exists 表名
(字段名 字段类型 字段约束,字段名 字段类型 字段约束....)
character set utf8;
sid int primary key auto_increment; 主键自动递增
2.2）显示建表语句：
show create table 表名；
2.3）显示数据库中的表：
show tables [like '匹配条件']；
2.4）复制表结构：
create table [if not exists] 新表名 like 旧表名；
2.5）修改表名：
rename table 旧表名 to 新表名；
2.6）删除表：
drop table 表名；
2.7）增加列：
alter table 表名 add (列1，列2........)
2.8）修改列：
2.8.1）修改字段名及其类型：
alter table 表名 change 旧列名 新列名 新列名类型;
2.8.2）修改字段类型：
alter table 表名 modify 列名 新数据类型；
2.9）删除列：
alter table 表名 drop 列名，列名......
DML
1.1）增加行：
insert into 表名（字段1，字段2......）values（值1，值2......);
1.2）删除行：
delete from 表名 where 字段名 in (值1,值2....);
delete from 表名 [where 字段名 = 值1,字段名 = 值2];
1.3）修改行：
update 表名 set 字段名 = 值1，字段名 = 值2....[where id = ?];
DQL
1.1） select * from 表名；
1.2）select 字段1，字段2...... from 表名 [where 检索条件]；
1.3）按照某个字段排序
select * from 表名 order by 字段名[asc/desc];
1.4）模糊查询：%C:以C结尾, C%: 以C开头
select * from 表名 where 字段名 like '%条件%'；
1.5）分页查询：起始行号从0开始，如果和order by连用，order by在前。
select * from 表名 limit 起始行号，行数；
例如：列出总成绩，并且按照降序排序，并且只打印3行
select name,sum(score) from score group by name order by sum(score) desc limit 3;
1.6）聚合函数： 聚合函数不能直接在where后面使用
avg(字段名): 该字段的平均数
max(字段名): 该字段的最大值
min(字段名): 该字段的最小值
count(字段名):该字段的记录数
sum(字段名): 该字段的总和
(1) 平均成绩： as:给字段起别名
select avg(age) as '平均成绩' from student;
(2)最大成绩
select max(age) as '最大成绩' from student;
（3）select * from student where age = (select max(age) from student);
(4)找出比平均年龄小的
select * from student where age < (select avg(age) from student);
(5)张三的平均年龄
select avg(score) from score where name = '张三'；
（6)该字段的记录数
select count(sid) from student;
运算符
逻辑： not(非) or(或) and(与)
1.1）范围查找
select * from 表名 where 字段名 [not] between 值1 and 值2；
1.2）在一组值中匹配
select * from 表名 where 字段名[not] in (值1，值2，值3......);
1.3）将null作为索引的条件
select * from 表名 where name is [not] null;
分组和多表联查
分组 group by 排序 order by
select 字段名 聚合函数，聚合函数 from 表名 group by 字段名 [having 筛选条件]；
注意：
1.前面已经执行完了才会执行having
2.select后面出现的字段名必须是group by 后面的字段名
(1)列出每个人的平均成绩和总成绩
select name,avg(score) as '平均成绩',sum(score) as '总成绩' from score group by name;
(2)列出平均成绩大于70的人名和成绩
select name,avg(score) as '平均成绩' from score group by name having avg(score) > 70;
(3)列出平均成绩 > 70 总成绩 > 180 的人
select name,avg(score) as '平均成绩,sum(score) as '总成绩' from score group by name
having avg(score) > 70 or sum(score) > 180;
(4)列出总成绩并且按照升序排列
select name,sum(score) from score group by name order by sum(score);
(5)列出每科的平均成绩
select obj,avg(score) as '平均成绩' from score group by obj;
(6) 列出总成绩并且按照降序排列，并且值打印3行
select name,sum(score) from score group by name order by sum(score) desc limit 3;
多表联查：
left join:
返回包括左表中的所有记录和右表连接字段相等的记录
因为left join是以左表为主表，所以只要左表有数据，
不管右表有没有数据（如果右表没有数据则为null）查询结果都会存在。
right join:
返回包括右表中的所有的记录和左表连接字段相等的记录
inner join:
等值连接，只返回两个表中连接字段相等的值
select chinese.name from
chinese inner join math
on
chinese.name = math.name;union去重：
union不会去重,union可以连接多个查询语句,每个表的结构必须一致
select name from chinese
union
select name from math;练习：
逻辑是组织下面有部门，部门下面有用户，
组织和部门通过organization_id字段关联，部门和用户通过department_id关联
（1）两张表的查询
查询所有组织信息以及下属中所有部门信息
select * from
t_organization AS o
left join
t_department AS d
on
o.organization_id = d.organization_id; select chinese.name,chinese.job,math.obj,math.score from
chinese left join math
on
chinese.name = math.name;（2）三张表查询
查询所有组织信息以及下属所有部门信息以及下属所有用户信息：
select * from
t_organization AS o
left join
t_department AS d
ON
o.organization_id = d.organization_id
left join
t_user AS u
ON
d.department_id = u.department_id; 数据库的还原备份
数据库备份步骤：
1)新开一个终端
2) mysqldump -u root -p12345678 数据库名字1 > 新路径/数据库名字2.sql;
3)回到之前的终端,删除旧的数据库: drop database 数据库名字1;
4)新建一个数据库, 起名为: 数据库名字2;
5)选择数据库: use 数据库名字2;
6)解析数据: source 新路径/数据库名字2.sql;
Mysql优化
(1) in 和 not in 要慎用,否则会导致全表扫描
select id from zhou where number in (1,2,3);
修改为: select id from zhou where number between 1 and 3;
(2) 应尽量避免在where子句中使用or来连接条件,
否则会导致引擎放弃使用索引而进行全表扫描
select id from zhou where num = 10 or num = 20;
修改为: select id from zhou where num = 10
union all
select id from zhou where num = 20;
(3) 如果在where子句中使用参数,也会导致全表扫描
select id from zhou num = @num;
修改为强制查询使用索引:
select id from zhou with(index(索引名)) where num = @num;
(4)尽量避免在where子句中对字段进行表达式操作,将导致引擎放弃使用索引而全表扫描
select id from zhou where num/2 = 100;
修改为: select id from zhou where num = 100 * 2;
(5)任何时候都不要使用select * from zhou,用具体的字段代替 * ,不要返回不需要的字段
(6)如果使用到临时表,在存储过程的最后务必将所有的临时显示删除.
先 truncate table 然后 drop table 这样可以避免系统表的较长时间锁定
外键
（1）可以为两张表或者多张表建立关联，保证了数据之间的一致性和完整性等
一个表的外键肯定是另一张表的主键，主键所在的表称之为主表,外键所在的表称之为从表
一张表虽然只有一个主键,但可以有多个外键， 目前只有mysql引擎为innoDB时才支持外键(默认是innoDB)
(2)外键的约束模式:
1.set null: 闲置模式
主表记录被删除或者更改,从表相关记录的外键置为null;
2.cascade: 级联操作
主表中删除或者更新了某条信息,从表中与该表记录有关的记录也发生改变
3.district: 严格模式 no action: 和district一样
当从表中有外键数据和主表关联,主表中该条记录就不能删除或者更新
(父表不能删除或者更新一个被子表引用的记录)
(3)添加外键:
constraint: 约束
references: 参考
foreign key:外键约束
cascade: 主变从变 set null: 主变从为空(null)
alter table st add constraint te_fy_st
foreign key(tid) references te(tid)
on delete set null on update set null; (4)删除外键
alter table st drop foreign key te_fy_st;索引
(1) 作用:
当某个表中某个字段经常作为查询条件(where 后面),并且表中有大量的数据,
该表经常作为查询条件,这时就可以将该字段作为索引,提高查询效率, 但是降低了增删改的效率
2.1单例索引: 基于表中某一经常查询列来创建索引
create [unique] index 索引名 on 表名(字段名); unique:创建唯一索引
基于多列来创建的索引,比如经常需要根据某两列来进行order by排序时会使用到基于多列来创建的索引,
比如经常需要根据 某两列来进行order by排序时会使用到
---基于myemp表的deptno salary 两列来创建复合索引
create index idx_myemp_deptno_salary on myemp(depton,salary);
---在执行以下查询时,会自动调用上面的索引
保证order by 列的顺序和创建索引时,列的顺序一致,复合索引才会起作用
select id,name,deptno,salary from myemp order by deptno ASC, salary DESC;
(3)索引的重建和删除
3.1更新索引
如果表的索引列上经常执行DML操作,就需要对索引执行更新重建
语法: atler index 索引名 REBULLD;
3.2删除索引
索引表中索引类列中有不合理的索引,会导致操作性能下降
语法: drop index 索引名
(4)索引的使用场景:
1.为经常出现在where子句中列建立索引
2.为经常出现在order by, distinct(关键字去重)后面的列建立索引
如果是复合索引,索引后面的列顺序要和这些关键字后面列顺序一致
3.为经常出现做表连接的连接条件列上建立索引
4.不要在经常DML操作的列上建立索引
5.不要在数据量少的表上建立索引
6.限制表的索引数目,索引不是越多越好
7.删除很少被使用,不合理的索引
视图
视图是一张虚拟的表,本质上是对基表的一条select查询语句.
然后给这条查询语句进行命名,即为视图名称.
当基表数据发生改变时,视图数据也发生改变.
视图的作用:
1.简化复杂查询
2.限制数据访问
创建视图
create [or replace] view 视图名 as select语句;
select语句可以是普通查询;可以是连接查询;可以是联合查询;可以是子查询.
例如:
create view myview as select name,age,height from viewable;
create or replace view myview as select name,avg(age) from viewtable group by name;
create or replace view myview as select * from viewtable where age > 22;
删除视图
drop view 视图名;
delete from 视图名 where 删除条件
修改视图
update 视图名 set 修改条件 where 原始数据
例如:update myview set age = 36 where age = 25;
alter view 视图名字 as 新的select语句
例如: alter view my_view as select id,name,sex from my_student;
create or replace view 视图名 as select […..] from [基表名]
例如: create or replace view sname_age as select name,age from viewtable;
create or replace view myview as select * from viewtable;
查看视图
视图是一张虚表,像查看表一样查看视图就好了
show tables [like……] (可以使用模糊查询) ：查看数据库已有视图
Desc 视图名：查看视图详情
select * from 视图名
show create view 视图名
注意点
1.对视图进行更新操作(删 改),基表也会进行相应的更新
2.对基表进行操作,视图也会进行相应的更新
3.如果视图所代表的查询语句中有
聚合函数( SUM( ), MIN( ), MAX( ), COUNT( )等 )
分组(GROUP BY)关键字,
那么having, union或 union all ,from子句中不可更新视图,
where子句中的子查询,使用临时表的都不能更新
create or replace view myview as select name,avg(age) from viewtable group by name;
(1)不能进行更新操作
update myview set name = 'zz' where name = '碧瑶';
(2)不能进行删除操作
delete from myview where name = '碧瑶';
(3)不能进行插入操作
insert into myview values('道玄',52,58.6);
4.视图是一张虚表,不能存储数据,所以一般插入操作,不通过视图来执行.
5.删除(drop)视图,不会影响基表.
触发器
概念: (可以看成是监听器)
数据库中一个用于表相关的,存储一段PL/SQL程序,主要作用是用监听表中DML操作,
当指定表上执行了insert update delete这些操作的语句时,
就会自动触发存储在触发器中的PL/SQL语句
触发器类型
(1)语句集触发器(针对表)
在指定操作语句执行之前或之后会执行一次触发器中的PL/SQL,无论这个操作影响了多少数据(一般是insert delete)
(2)行级触发器(针对行)
触发器语句作用的每一行记录都会备被触发,可以使用
:new和 :old两个变量来记录这一行数据被影响之前和之后的状态(一般是update)
创建触发器
delimiter ;;
create trigger 触发器名称
after/before // (触发器工作的时机)
update/delete/insert // (触发器监听事件)
on 表名 // (触发器监听的目标表)
for each row //(行级监视，mysql固定写法，oracle不同)
begin
sql语句集........ //（触发器执行动作，分号结尾）
end ;; // 结束限定符
作用
多张相关联的表,达到数据同步
操作表 被操作表
我们为操作表添加触发器,当我们为操作表进行更新时(增删改),达到某个条件时,就会触发事件(sql语句集)并执行事件.
删除触发器
drop trigger if exist 触发器名称
查询数据库触发器
show triggers;
触发器的使用场景
1）复杂的安全性检查
2）数据的确认
3）数据库审计
4）数据的备份同步
扫一扫
4027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
