DDL数据库知识点
DDL(data definition language) | 数据定义语言 |
---|---|
DML | 数据操作语言 增改查 |
DQL | 数据查询语DCL |
DCL | 数据控制语言 |
1、数据库
1.创建数据库
create database [if not exists] +数据库名字+[字符集]+[排序规则]
2 .删除数据库
drop databases[ if exists] +数据库名字
3.使用数据库
`use +数据库名字
4.查询当前数据库
show databases();
2、表
1.查询所有表
show tables;
2.查询表结构
desc 表名
3.查询指定表的创建的语句
show create table 表名字
4.创建表实例
CREATE TABLE `student2` ( `id` int(11) NOT NULL, `age` varchar(10) DEFAULT NULL, `name` varchar(16) NOT NULL, `gender` char(1) DEFAULT '?', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 //最后一行可以省略5.修改表
添加字段
alter table 表名 ADD 字段名 类型(长度);
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名字 新字段名字 类型(长度);
删除字段
alter table 表名 drop 字段名;
修改表名字
alter table 表名 rename to 新表名;
删除表
drop table [if exists]表名;
删除表,并重新创建
truncate table 表名;
3、视图
创建视图
-- create view 视图名 as (复杂查询语句); create view emp_view as select e.ename,e.sal,e.comm,d.dname from emp e inner join d on e.dept = d.deptno;
删除视图
drop view 视图名;
作用:
-
简化查询:视图创建的本意就是将经常要使用的查询语句封装起来,然后后面的使用就只需要调用视图明就可以,不用频繁的写相同的查询语句。
-
重写格式化数据:
-
频繁访问数据库:
-
过滤数据:视图是一个生成的是一个虚拟表,物理上不存在,而这张虚拟表中就可以将想显示的内容,而后面创建视图也只能在这些内容上进行查询,提高了数据的安全性。
4、索引
创建索引
create index 索引名称 on 需要建立索引的表名 (column1 [ASC|DESC], column2 [ASC|DESC], ...); -- (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。 -- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
删除索引
drop index 索引名 on 表名;
5、存储过程
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
理解为方法。
创建存储过程
-- 创建存储过程 create procedure mypro(in a int,in b int,out sum int) begin set sum = a+b; end; call mypro(1,2,@s);-- 调用存储过程 select @s;-- 显示过程输出结果 -- create procedure 用来创建过程; -- mypro 用来定义过程名称; -- (in a int,in b int,out sum int)表示过程的参数,其中 in 表示输入参数,out 表示输出参数。类似于 Java 定义方法时的形参和返回值; -- begin 与end 表示过程主体的开始和结束,相当于 Java 定义方法的一对大括号; -- call用来调用过程,@s 是用来接收过程输出参数的变量 -- 存储过程的参数 -- MySQL 存储过程的参数用在存储过程的定义,共有三种参数类型: -- IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量); -- OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量); -- INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)。
删除存储过程
drop PROCEDURE 存储过程名;
变量的声明
DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 1 -- declare用于声明变量; -- variable_name表示变量名称; -- datatype为 MySQL 的数据类型; -- default用于声明默认值; -- 变量赋值 SET 变量名 = 表达式值 [,variable_name = expression ...]
案例
-- 设置 界位符 ,即程序执行的结束符号 DELIMITER $$; -- 存在删除 drop PROCEDURE if EXISTS pro_teacher; create PROCEDURE pro_teacher(in p_tid int,out p_tname VARCHAR(20)) begin select tname into p_tname from teacher where tid = p_tid; end$$; -- 调用存储过程 call pro_teacher(3,@name); -- 获取存储过程的返回值 select @name;
6、函数
创建函数
create function 函数名(参数列表) returns 返回类型 begin 函数体 End -- 调用 select 函数名(参数列表)查看:show create function 函数名 删除: drop function 函数名
7、触发器
创建触发器
CREATE <触发器名> < BEFORE | AFTER > <INSERT | UPDATE | DELETE > ON <表名> FOR EACH Row<触发器主体> -- 注意:每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER,因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。 -- 创建有多个执行语句的触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN 语句执行列表 END
查看触发器
show TRIGGERs;
删除触发器
DROP TRIGGER 触发器名;
案例
-- 创建触发器检测jobinfo表数据 delimiter // -- 设置结束符 create trigger update_trigger after UPDATE on jobinfo for each row begin -- 把更新之后的值放入lishi表中,代表进行的操作 insert into lishi(jiance) select new.jiance; end; // delimiter ; -- 重新设置回来
DML语句
主要为添加数据,修改数据。
添加数据
insert into 表名 (字段1,字段2,....)values(值1,值2,....)//需要一一对应。
insert into 表名 values (值1,值2,....)
修改数据
update 表名 set 字段1=值1 字段2=值2 ,... [where 条件]
set为要更改后的值
删除数据
delete from 表名 [where 条件]
DQL语句
普通查询
select * from 表名 where 条件 ;
having 和where区别
having是在分组后对数据进行过滤 where是在分组前对数据进行过滤 having后面可以使用聚合函数 where后面不可以使用聚合
多条件查询(or and)
select * from 表名 where 条件1 and 条件2; -- 并列关系 select * from 表名 where 条件3 or 条件4; -- 或关系
模糊查询(_ %)
-- %:表示所有 -- _:表示匹配任意某个字符 -- 5、模糊查询 只针对字符串(varchar类型) select * from emp where ename like "n%"; select * from emp where ename like "_7";
子查询
select * from (select 某列 from 表名);
使用子查询的规则:
1)子查询必须“自身就是一个完整的查询”。即,它必须至少包括一个SELECT子句和 FROM子句。
2)子查询SELECT语句不能有ORDER BY子句。因为ORDER BY字句只能对最终查询结 果排序,如果显示的输出需要按照特定顺序显示,那么ORDER BY子句应该作为外部 查询的最后一个子句列出。
3)子查询“必须包括在一组括号中”,以便将它与外部查询分开。
4)如果将子查询放在外部查询的WHERE或HAVING子句中,那么该子查询只能位于比 较运算符的“右边”。
分页查询
-- 10、分页查询 从几开始,查几条数据。 select * from emp limit 0,10;
聚合函数查询
-- 6、聚合函数查询 select count(*), avg(sal),sum(sal),max(sal),min(sal) from emp;
排序查询
select * from 表名 order by desc; -- 降序 -- 默认升序,也可自己设置 asc -- 多条件排序 -- 按工资从低到高,及入职时间从大到小显示员工信息(有升序有降序) select * from emp order by sal asc,hiredate desc
表连接查询
内连接 ---inner join ...on 查询到的为两张表相同的部分 ```mysql -- 1.内连接 inner join ...on select * from dept inner join emp on dept.deptno = emp.deptno; ``` ### 左外连接 ---left join...on 查询到的为条件相同,但是在left左边表要显示更全 ```mysql -- 2. 左外连接 left join...on 根据emp来匹配左边全显示,右边只显示部分 select * from emp left join dept on dept.deptno = emp.deptno; ``` 右外连接 --- right join...on 查询到的为条件相同,但是在right右边表要显示更全 ```mysql -- 3. 右外连接 right join ... on 根据emp来匹配 ,右边表全显示, select * from dept right join emp on dept.deptno = emp.deptno; ```
分组查询
-- 7、分组查询
联合查询
标量子查询
-- 标量子查询 1行1列 -- 查询比平均薪资高的员工信息 select avg(sal) from emp; select * from emp where sal > (select avg(sal) from emp);
条件查询
-- 8、分组和条件查询 select * from emp where ename = 'n7' order by sal desc;
列子查询
-- 列子查询 -- 1行多列 -- 查询名字为张三,工资为2000的员工信息 select * from emp where ename = '张三' and sal = 2000; select * from emp where (ename,sal) = (select ename,sal from emp where ename = '张三' and sal = 2000); -- 多行一列 in 关键字 -- 查询名字叫张三,李四的员工信息 -- 包含张三李四的 = 改为in select * from emp where ename = '张三' or ename = '李四'; select * from emp where ename in (select ename from emp where ename = '张三' or ename = '李四'); -- 不包含张三李四 = 改为 not in select * from emp where ename not in (select ename from emp where ename = '张三' or ename = '李四'); -- 查询比部门编号为999的所有员工工资都高的信息 -- 这里的max(sal)等价于all select * from emp where sal > (select max(sal) from emp where deptno = 999); select * from emp where sal > all (select sal from emp where deptno = 999);