首页 > 安全资讯 >

数据库知识学习之SQL语句大全

16-11-09

数据库知识学习之SQL语句大全:1 检索数据;检索student_name单个列: SELECT student_name ;FROM Student。

1.检索数据

检索student_name单个列: 
SELECT  student_name 
FROM Student;

这里写图片描述

检索多个列:
SELECT student_id,student_name,student_age 
FROM Student;

这里写图片描述

检索所有列:
SELECT * 
FROM Student;
最好不要用"*"代替字段,这是一个低效的方法,因为DBMS要耗时把"*"转为字段

这里写图片描述

2.排序检索数据

按一个列排序检索:
SELECT student_name 
FROM Student  
ORDER BY student_name;

这里写图片描述

按多个列排序检索:
SELECT student_id,student_name,student_age 
FROM Student 
ORDER BY student_name,student_id;

这里写图片描述

按位置排序:
SELECT student_id,student_name,student_age 
FROM Student 
ORDER BY 2,3;

这里写图片描述

3.过滤数据

检查单个值:
SELECT student_id,student_name,student_age 
FROM Student 
WHERE student_age<22;

这里写图片描述

不匹配检查:
SELECT student_id,student_name,student_age 
FROM Student 
WHERE student_name <> 'Bruce';
where子句中少用 <> 操作符,它容易引起全表扫描

这里写图片描述

范围值检查:
SELECT student_id,student_name,student_age 
FROM Student 
WHERE student_age  BETWEEN 20 AND 22;
BETWEEN效率比IN高,能用BETWEEN就不要用IN

这里写图片描述

空值检查:
SELECT student_id,student_name,student_city,student_age,student_email 
FROM Student  
WHERE student_age IS NULL; 
NULL值判断,要全表扫描,也应避免

这里写图片描述

4.操作符数据过滤

AND操作符:
SELECT  student_id, student_name,student_city,student_age,student_email,
student_class 
FROM Student 
WHERE student_class = '2班' AND student_age<=20;

这里写图片描述

OR操作符:
SELECT student_id, student_name,student_city,student_age,student_email,
student_class 
FROM Student 
WHERE student_class = '2班' OR  student_class='1班';
where子句中用or来连接条件,可以用UNION ALL

这里写图片描述

IN操作符:
SELECT  student_id,student_name,student_city,student_age,
student_email,student_class 
From Student  
WHERE student_class IN('1班','2班')  
ORDER BY student_id;
易导致全表扫描,可以用表联结替换

这里写图片描述

NOT操作符(否定他之后的任何条件):
SELECT student_id,student_name,student_city,student_age,student_email,     student_class 
From Student  
WHERE  NOT student_class='1班' 
ORDER BY student_id;

这里写图片描述

5.通配符过滤

百分号通配符:
SELECT student_id,student_name,student_city,  student_age,student_email 
From Student  
WHERE student_name LIKE 'B%' 
ORDER BY student_id;

这里写图片描述

下划线通配符:
SELECT * 
FROM Student 
WHERE student_id LIKE '10_9'; 

这里写图片描述

方括号通配符:
SELECT student_name 
FROM Student 
WHERE student_name LIKE '[A]%' 
ORDER BY student_name;

这里写图片描述

6.计算字段

拼接字段:
SELECT student_name || '(' ||student_age||')'  
FROM Student 
ORDER  BY student_name;

这里写图片描述

RTRIM()函数,去掉右边所有空格:
SELECT RTRIM(student_name) || '(' || student_age || ')'  
FROM Student 
ORDER BY student_name;

这里写图片描述

使用别名:
SELECT RTRIM(student_name) || '(' || student_age || ')'  AS student_name1 
FROM Student 
ORDER BY student_name;

这里写图片描述

执行算数字段:
SELECT student_id,student_age,student_age*student_id AS num 
FROM Student;

这里写图片描述

7.使用函数处理数据

文本处理函数:
SELECT student_name ,UPPER(student_name) AS student_name1 
FROM Student 
ORDER BY student_name;

这里写图片描述

AVG()函数,返回所有列的平均值:
SELECT AVG(student_age) AS student_age1 
FROM Student;

这里写图片描述

COUNT()函数:1.使用COUNT(*) 对表中行的数目进行计数,包含空值NULL与非空值。2.使用COUNT()对特定列中有值的行进行计数,忽略NULL值
SELECT COUNT(*) AS student_id 
FROM Student; 
不带任何条件的count会引起全表扫描,要避免

这里写图片描述

MAX()函数:返回指定列最大值,要求指定列名,忽略NULL值的行。
SELECT MAX( student_age) AS student_age 
FROM Student;

这里写图片描述

MIN()函数:
SELECT MIN( student_age) AS student_age 
FROM Student;

这里写图片描述

SUM()函数:
SELECT SUM(student_age) AS student_age1 
FROM Student 

这里写图片描述

8.数据分组

创建分组:
SELECT student_id,COUNT(*) AS student_id1 
FROM Student 
GROUP BY student_id;

这里写图片描述

9.子查询

使用子查询过滤:
SELECT student_id,student_name,student_city 
FROM Student 
WHERE student_id 
IN (SELECT teacher_id FROM Teacher WHERE student_name 
IN (SELECT student_name FROM Person WHERE student_id = '1001'));

这里写图片描述

作为计算字段使用子查询:
SELECT student_id,student_name,student_city,(SELECT COUNT(*) 
FROM Student 
WHERE Student.student_id = Person.student_id) AS orders FROM Person; 

这里写图片描述

10.联结表

创建联结:
SELECT student_name,teacher_name,teacher_city 
FROM Student,Teacher 
WHERE Student.student_id=Teacher.teacher.id; 

这里写图片描述

内部联结:
SELECT student_name,teacher_name,teacher_city 
FROM Student
INNER JOIN ON Student.student_id=Teacher.teacher_id;

这里写图片描述

联结多个表:
SELECT Student.student_name,teacher_name,teacher_city,Person.student_city 
FROM Person, Teacher,Student 
WHERE Student.student_id = Teacher.teacher_id AND Teacher.teacher_id = Person.student_id;

这里写图片描述

11.插入数据

数据插入:
INSERT INTO Student(student_id,student_name,student_city,student_age,student_email)  
VALUES( '1006' , 'MJ' ,'上海' , NULL , '2032366511@qq.com'); 

这里写图片描述

插入部分行:
INSERT INTO Student(student_id,student_name,student_city,student_age) 
VALUES( ' 1011' , 'KUFU' ,'北京' ,21);

这里写图片描述

插入检索出的数据:
INSERT INTO  Person(student_id,student_name,student_city,student_age)  
SELECT student_id,student_name,student_city,student_age 
FROM Student 
WHERE student_id='1006';

这里写图片描述

复制数据:从一个表复制到另一个表(运行中创建的表):
CREATE TABLE StudentCopy AS
SELECT *
FROM Student;

这里写图片描述

12.更新和删除数据

更新表中特定行:
UPDATE Student SET student_email='2222222@qq.com'
WHERE student_id='1001';

这里写图片描述

删除表中特定行:
DELETE FROM Student 
WHERE student_id='1001';

这里写图片描述

13.创建表

create table Student1
(
          student_id           number(10),            
          student_name         char( 10 ),            
          student_city         char( 10 ),            
          student_age          char( 10 ),            
          student_email        char( 20 ));

这里写图片描述

增加列:
ALTER TABLE Student 
ADD student_class char(50);

这里写图片描述

删除列:
ALTER TABLE Student1 
DROP COLUMN student_phone;

这里写图片描述

重命名表:
rename Student1 to Student;

这里写图片描述

删除表:
DROP TABLE student;

这里写图片描述

14.试图
创建视图:CREATE VIEW 只能用于创建不存在的试图

用试图简化复杂的联结:
CREATE VIEW ProduceStudent AS 
SELECT teacher_name
FROM Student,Teacher,Person 
WHERE Student.student_id=Teacher.teacher_id 
AND Person.student_id=Student.student_id;

这里写图片描述

用试图重新格式化检索出的数据:
CREATE VIEW StudentLocations 
AS SELECT RTRIM(student_name) ||  '('|| RTRIM(student_city)|| ')'  
AS student_title 
FROM Student;

这里写图片描述

检索出数据:
SELECT * 
FROM StudentLocations;

这里写图片描述

15.管理事物

DELETE FROM Student;
ROLLBACK;

这里写图片描述

COMMIT:DELETE OrderItems 
       WHERE order_num = 12345 ;
       DELETE Orders 
       WHERE order_num = 12345 ;
       COMMIT ;  

这里写图片描述

创建保留点:SAVEPOINT delete1;
          ROLLBACK TO delete1;

这里写图片描述

相关文章
最新文章
热点推荐