数据库命令语句及存储过程

1.数据库相关命令语句

1.truncate与delete

TRUNCATE TABLE：删除内容、不删除定义、释放空间。
DELETE TABLE:删除内容、不删除定义，不释放空间。
DROPTABLE：删除内容和定义，释放空间。

1.truncate table 表名，只能删除表中全部数据。 delete from 表名 where ….，可以删除表中全部数据，也可以删除部分数据。

2.delete from 记录是一条条删的，所删除的每行记录都会进日志， 而 truncate 一次性删掉整个页，因此日志里面只记录页释放.

3.Truncate 删除后，不能回滚。Delete 可以回滚。

4.Truncate 的执行速度比 Delete 快。

5.Delete 执行后，删除的数据占用的存储空间还在，还可以恢复数据。Truncate 删除的数据占用的存储空间不在，不可以恢复数据

2.增删改查

插入一行数据：
insert into stu(stuName,stuAge,stuSex) values(‘张三’,’20’,’男’)

增加列： 1. alter table tableName add （column）columnName varchar(30) （一定 要有数据类型！！！）

删除列： 1. alter table tableName drop （column） columnName

3.count()

count(*) 跟 count(1) 的结果一样，返回记录的总行数，都包括 对 NULL 的统计，而 count(column) 是不包括 NULL 的统计。

4.limit 用法

一.如果给定两个参数， 第一个参数指定第一个返回记录行的偏移量， 第二个参数指定返回记录行的最大数目。 初始记录行的偏移量是 0(而不是 1)

SELECT * FROM table LIMIT 5,10; //检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行， 可以指定第二个参数为 -1：

二.如果只给定一个参数， 它表示返回最大的记录行数目。

SELECT * FROM table LIMIT 5; //检索前 5 个记录行； LIMIT n 等价于 LIMIT 0,n。

5.group by

有一个学生表， 有三个字段： name 、 course 、 score ，
每一个学生都有三门课程， 比如数学、 语文、 英语， 写 sql 语
句， 查找出三门课程的成绩都大于 80 的学生。

SELECT S.name
FROM Student S
GROUP BY S.name
Having MIN(S.score)>=80

select distinct A.name form Student A 
where A.name  not in(
select distinct S.name form Student S where S.score<80)

6. Exist

EXISTS 关键字表示存在。 使用 EXISTS 关键字时， 内层查询语句不返回查询的记录，而是返回一个真假值。
如果内层查询语句查询到符合条件的记录， 就返回一个真值（true） ， 否则， 将返回一个假值（false） ：

1. 当返回的值为 true 时， 外层查询语句将进行查询。
2. 当返回的值为 false 时， 外层查询语句将不进行查询或者查询不出任何记录。

如果 department 表中存在 d_id 取值为 1003 的记录， 则查询 employee 表中 age 大于24 岁的记录。

SELECT * FROM employee
WHERE age>24 AND EXISTS
(SELECT d_name FROM department WHERE d_id=1003);

2.数据库连接

1.内连接（inner join）：只显示符合连接条件的记录。

[TEST1@orcl#16-12 月-11] SQL>select * from t1;

ID	NAME
1	aaa
2	bbb

[TEST1@orcl#16-12 月-11] SQL>select * from t2;

ID	AGE
1	20
3	30

[TEST1@orcl#16-12 月-11] SQL>select * from t1 inner join t2 on t1.id=t2.id;

ID	NAME	ID	AGE
1	aaa	1	20

2.外连接分左外连接、右外连接、全外连接三种。

1）左外连接（LEFT JOIN 或 LEFT OUTER JOIN ）
即以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。
显示左表的全部记录及右表符合连接条件的记录。
[TEST1@orcl#16-12 月-11] SQL>select * from t1 leftjoin t2 on t1.id=t2.id;
ID NAME ID AGE

---

1 aaa 1 20
2 bbb NULL NULL

2）右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN ）
[TEST1@orcl#16-12 月-11] SQL>select * from t1 right join t2 on t1.id=t2.id;
ID NAME ID AGE

---

1 aaa 1 20
NULL NULL 3 30
即以右表为基准，到左表找匹配的数据，找不到匹配的用NULL补齐。
显示右表的全部记录及左表符合连接条件的记录

3）全外连接（FULL JOIN 或 FULLOUTER JOIN）
除了显示符合连接条件的记录外，在2个表中的其他记录也显示出来。

3.存储过程的概念以及优缺点

存储过程：就是一些编译好了的 sql 语句，这些 SQL 语句代码
像一个方法一样实现一些功能（对单表或多表的增删改查），然后
再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

优点

1.存储过程因为 SQL 语句已经预编译过了，因此运行的速度比较快。
2.存储过程在服务器端运行，减少客户端的压力。
3.允许模块化程序设计，就是说只需要创建一次过程，以后在程
序中就可以调用该过程任意次，类似方法的复用。
4.减少网络流量，客户端调用存储过程只需要传存储过程名和相 关参数即可，与传输 SQL 语句相比自然数据量少了很多。
5. 增强了使用的安全性，充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。程序员直接调用存储过程，根本不知道表结构是什么，有什么字段，没有直接暴露表名以及字段名给程序员。

缺点：

调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因
为存储过程是依赖于具体的数据库）