数据库SQL语句期末总复习 |
您所在的位置：网站首页 › sql基本命令大全表查询 › 数据库SQL语句期末总复习 |
|
文章目录
SQL的分类DDL数据定义语言数据库的定义与撤销基本表的定义与维护索引的建立与删除
DQL数据查询语言单表查询查询结果排序分组查询连接查询嵌套查询集合查询
DML数据操作语言插入数据更新数据删除数据
视图视图的定义与删除查询视图更新视图
TCL （不考）DCL（不考）
SQL的分类
SQL语句的分类 DDL(数据定义语言)：create、drop、alter对表结构进行增删改 DQL(数据查询语言)：select查询语句 DML(数据操作语言)：insert、delete、update对表中数据进行增删改 DCL(数据控制语言)：grant、revoke授权与撤销授权 TCL(事务控制语言)：commit、rollback提交事务与回滚事务
DDL数据定义语言
数据库的定义与撤销
-- 创建数据库
create database ;
-- 删除数据库
drop database ;
基本表的定义与维护
约束条件（Constraint）： not null（非空约束）：约束的字段不允许出现空值unique（唯一约束）：约束的字段不允许重复primary key（主键约束）：用于定义表的主键，起唯一标识作用foreign key（外键约束）：用于定义表的外键，建立两张表之间的联系 foreign key () references ()check（检查约束）：用来约束检查字段值所允许的范围 在多个字段上定义的check约束必须为表级约束 格式：check () -- 创建表 create table ( , , ); -- 删除表 drop table [cascade|restrict] -- cascade(默认):在删除表时，表中的数据、表本身以及在该表上建立的索引和视图全部删除 -- restrict:只有在清空表中的记录以及表上的索引和视图，才能执行删除表操作 -- 修改表结构 -- 添加新列 alter table add [ , ...]; -- 删除列 alter table drop column [cascade|restrict]; -- 添加约束 alter table add constraint ; -- 删除约束 alter table drop constraint ; -- 修改列 alter table alter column ;列级约束和表级约束 在单个字段上定义的约束使用列级约束 在多个字段上定义的约束必须使用表级约束 注意：not null只有列级约束没有表级约束 create table t_user( userId int primary key, --列级约束 userName char(6), userAge int not null, -- 列级约束 userTel char(11), unique(userName,userTel) -- 表级约束 ); 索引的建立与删除索引的分类： unique（唯一索引）clustered（聚集索引）nonclustered（非聚集索引）复合索引 -- 创建索引 create index on ([asc|desc],[asc|desc], ...); -- 删除索引 drop index on ; -- 索引可以建立在单列或者多列上,格列之间用逗号隔开 -- 例 create unique index xxx on t_user(userId asc,userAge desc); //sc（默认）升序 desc降序 DQL数据查询语言 单表查询 -- 查询表中所有数据 select * from ; -- 查询指定列 select [,, ...] from ; -- 案例：找出所有零件的名称及重量 select PNAME,WEIGHT from P; -- 条件查询 where关键字 select from where ; -- 案例：供应工程J1零件P1的的供应商号码SNO select SNO from SPJ where JNO='J1' and PNO='P1' --去除重复记录 distinct关键字 select distinct from ; //注意：distinct只能放在第一个列名前面 --案例：供应工程J1零件的供应商号码SNO select distinct SNO from SPJ where JNO='J1'常用的查询条件 运算符说明=，或!=，=等于，不等于，小于，小于等于，大于，大于等于between … and …两个值之间，等同于>=and2900 group by deptno; //错误,原因是where的执行顺序大于group by这种情况只能用having过滤 -- 一条完整的sql语句的执行顺序 select ... 5 from ... 1 where ... 2 group by ... 3 having ... 4 order by ... 6 //数字越小表示越早执行 连接查询连接查询的分类： 内连接 等值连接非等值连接自连接 外连接 左外连接右外连接全连接 --内连接之等值连接 select [,,...] from inner join //inner可以省略 on = where ; -- 案例：求供应数量超过300的供应信息,包括供应商名,零件名,项目名和供应数量。 select S.SNAME,P.PNAME,J.JNAME,SPJ.QTY from SPJ join S on SPJ.SNO=S.SNO join J on SPJ.JNO=J.JNO join P on SPJ.PNO=P.PNO where SPJ.QTY>300; --内连接之非等值连接 select [,,...] from inner join //inner可以省略 on between and ; --内连接之自身连接（将一张表看作两张表） -- 案例：查询每一门的课程的间接先修课程 select C1.Cname,C2.Cpno as '间接先修课程' from Course C1 join Course C2 on C1.Cpno=C2.Cno --外连接 select [,,...] from [left|right] outer join //outer可以省略 on = //left左外连接right右外连接 where ; -- 案例：查询所有的学生的选课情况，要求没有选课的情况也能在结果显示出来 select Student.Sname,SC.Cno from SC right outer join Student on SC.Sno=Student.Sno给表取别名有什么好处？ 1、执行效率高 2、可读性好 嵌套查询 -- 嵌套查询（子查询）可以出现在哪？ --子查询可以出现在select、from、where、having后面 select ...(select...) from ...(select...) where ...(select...) -- where子句中使用子查询 select * from emp where sal > //父查询/外层查询 (select avg(sal) from emp); //子查询/内层查询 --from子句种使用子查询 select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t //子查询/内层查询 join salgrade s on t.avgsal between s.losal and s.hisal; -- select子句中使用子查询 select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname //子查询/内层查询 from emp e; 集合查询 union 并集运算intersect 交集运算except 差集运算 -- union将查询结果合并起来,系统会自动去掉重复记录 如果需要保留重复记录可以使用union all select * from Doctor where Department='内科' union select * from Dactor where Title='主任医师' -- intersect返回多条查询语句中都包含的重复数据 select dId from Doctor where Department='呼吸科' intersect select dId from CureFee； --except比较左右两个查询结果的差集，并从左侧的查询结果中返回在右侧查询结果中找不到的数据 select dId from Doctor where Department='呼吸科' except select dId from CureFee DML数据操作语言 插入数据 -- 插入数据 insert into valuesu关键字 -- 插入一条数据 注意：未指定属性列 插入的列值数据顺序应严格按照表中各列定义的顺序 insert into values (,,,...) //未指定属性列 insert into (,) values(,) //指定属性列 -- 插入多条数据 insert into values (,,,...), (,,,...), (,,,...), (,,,...) 更新数据 -- 更新数据 update set关键字 update set = where ; //如果省略where将会修改表中的所有数据 --案例：把全部红色零件的颜色改成蓝色 update P set COLOR='蓝' where COLOR='红'; -- 带子查询的修改语句 -- 案例：将所有CS系的成绩不及格学生的成绩加5分 update SC set Grade=Grade+5 where Sno in (select Sno from Student where Sdept='cs') 删除数据delete和truncate的区别 truncate删除数据的速度比delete快delete删除数据可回滚，truncate删除数据不可回滚 --删除数据 delete关键字 delete from where -- 删除全部数据 truncate关键字 truncate table //谨用,最好别用 视图 视图的定义与删除 -- 创建视图 create view as [with check option] -- 删除视图 drop view -- 修改视图 alter view as [with check option]with check option:当对视图进行update、insert和delete操作时，要保证更新、插入或删除的行满足视图定义中的子查询的条件。 查询视图通过视图检索数据时，对查询语句几乎没有什么限制，但在修改数据时却存在这较多的限制 -- 视图查询语法与表查询相同 select * from 更新视图1、如果视图带有with check option那么在进行数据更新的时候操作的数据必须满足子查询的条件 2、不能再使用了distinct、group by、having语句的视图上插入数据 -- 插入数据（与基本表插入数据用法相同） insert into values(,,,...) -- 修改数据（与基本表修改数据用法相同） update set [=,=...] where --删除数据 delete from where //如果视图建立再多张表上不允许删除 TCL （不考） 什么事务？ 一个事务是一个完整的业务逻辑单元，不可再分事务的四大特性ACID 原子性：事务是最小的工作单元，不可再分 一致性：事务必须保证多条DML语句同时成功或者失败 隔离性：事务A与事务B之间存在隔离性 持久性：将数据持久化到硬盘事务才算成功结束事务的隔离级别 第一级别：读未提交(read uncommitted) 第二级别：读已提交(read committed) 第三级别：可重复读(repeatable read) 第四级别：串行化读(serializable) SqlServer默认第二级别 MySql默认第三级别 -- 设置事务隔离级别 set global transaction isolation level //这是Mysql中的设置方式 set transaction isolation level //这是sqlServer中的设置方式 --设置第一级别 set global transaction isolation level read uncommitted --设置第四级别 set global transaction isolation level serializable --查看隔离级别 select @@global.tx_isolation; //这是Mysql中的查看方式 dbcc useroptions; //这是sqlServer中的查看方式 -- 关闭事务自动提交 set autocommit = 0; //mysql start transaction; //mysql begin transaction; //sqlserver -- 提交事务 commit; --回滚事务 rollback; DCL（不考）常用数据对象权限 all: 所有可用的权限create: 创建库、表和索引lock_tables: 锁定表alter: 修改表delete: 删除表insert: 插入表或列select: 检索表或列的数据create_view: 创建视图show_databases: 列出数据库drop: 删除库、表和视图 -- 以下这些都是关于mysql用户权限设置的 sqlserver的请点文章末尾链接 --创建mysql用户 create user ''@'localhost' identified by ''; create user 'myuser'@'localhost' identified by 'mypassword'; -- 查看用户在否存在 select host, user, password from mysql.user where user=''; -- 设置用户权限 grant [,,...] on . to ''@'localhost'; -- 例 给myuser赋予对whynode中t_user表的插入和查询操作 grant select,insert on whynode.t_user to 'myuser'@'localhost'; -- 给myuser赋予所有库所有表的所有权限 grant all on *.* to 'myuser'@'localhost'; -- 查看用户权限 show grants for 'myuser'@'localhost'; -- 撤销用户权限 revoke [,,...] on . from ''@'localhost'; revoke insert on whynode.t_user from 'myuser'@'localhost'; -- 刷新权限 flush privilegesSqlServer创建用户及权限授予 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |