快速回顾SQL,满血复活
相关文章:
(一)SQL基础三步曲(小白必会)
(二)SQL基础三步曲(小白必会)
SQL基础知识:
select 查询结果 如: [学号,平均成绩:组函数avg(成绩)]
from 从哪张表中查找数据 如:[涉及到成绩:成绩表score]
where 查询条件 如:[b.课程号='0003' and b.成绩>80]
group by 分组 如:[每个学生的平均:按学号分组](oracle,SQL server中出现在select 子句后的非分组函数,必须出现 在group by子句后出现),MySQL中可以不用
having 对分组结果指定条件 如:[大于60分]
order by 对查询结果排序 如:[增序: 成绩 ASC / 降序: 成绩 DESC];
limit 使用limt子句返回topN(对应这个问题返回的成绩前两名)如:[ limit 2 ==>从0索引开始读取2个]
limit==>从0索引开始 [0,N-1]
文章目录
- 一、常用SQL语句
- 1、创建数据库
- 2、drop、truncate和delete的区别
- 3、备份sql server
- 4、创建新表
- 5、删除新表
- 6、增加一个列
- 7、主键
- 8、索引
- 9、视图
- 10、几个简单的基本的sql语句
- 11、AND / OR
- 12、ALTER TABLE
- 13、AS
- 14、BETWEEN
- 15、CREATE DATABASE
- 16、CREATE TABLE
- 17、CREATE INDEX
- 18、CREATE VIEW
- 19、DELETE
- 20、DROP DATABASE
- 21、DROP INDEX
- 22、DROP TABLE
- 23、GROUP BY
- 24、HAVING
- 25、IN
- 26、INSERT INTO
- 27、INNER JOIN
- 28、LEFT JOIN
- 29、RIGHT JOIN
- 30、FULL JOIN
- 31、LIKE
- 32、ORDER BY
- 33、SELECT *
- 34、SELECT DISTINCT
- 35、SELECT INTO
- 36、SELECT TOP
- 37、TRUNCATE TABLE
- 38、UNION去重并排序
- 39、UNION ALL直接返回合并的结果,不去重也不排序;
- 40、UPDATE
- 41、WHERE
- 总结
一、常用SQL语句
1、创建数据库
CREATE DATABASE database-name
2、drop、truncate和delete的区别
删除数据库
drop database dbname
相同点:
TRUNCATE TABLE和DELETE都可以删除整个数据库表的记录。
不同点:
1.DELETE:可以回退,可以有条件的删除
(DML(Data Manipulation Language)数据操作语言)
DELETE FROM 表名 WHERE 条件
--删除表中数据,没有条件时,删除所有
delete from Scores where ID in(7,8)
2.TRUNCATE TABLE:无法回退,删除速度比delete快,默认所有的表内容都删除
TRUNCATE不能触发任何Delete触发器。
在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比Delete操作后的表要快得多。 DDL(Data Definition Language)数据定义语言。不能对TRUNCATE使用ROLLBACK命令。
在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销,而TRUNCATE则不会被撤销。
TRUNCATE TABLE 表名;
3.DROP TABLE:用于删除表(表的结构、属性以及索引也会被删除)
DROP TABLE 表名
应用范围细节:
1.TRUNCATE 只能对TABLE; DELETE可以是table和view
2.TRUNCATE 和DELETE只删除数据, DROP则删除整个表(结构和数据)
truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。
注意点
在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。
3、备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、删除新表
drop table tabname
6、增加一个列
Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、主键
添加主键: Alter table tabname add primary key(col)
删除主键: Alter table tabname drop primary key(col)
8、索引
创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
9、视图
创建视图:create view viewname as select statement
删除视图:drop view viewname
--创建视图,全部定义属性列名,需要定义列名的情况:
----某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
----多表连接时选出了几个同名列
----需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score=(select max(Score) from Scores)
10、几个简单的基本的sql语句
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
11、AND / OR
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
12、ALTER TABLE
ALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name
----------------------------------------
--新增一列
alter table Scores add Birthday datetime
--删除一列
alter table Scores drop column Birthday
--修改表中的一列
alter table Scores alter column [Student] varchar(20) not null
13、AS
SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name AS table_alias
14、BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
15、CREATE DATABASE
CREATE DATABASE database_name
16、CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)
17、CREATE INDEX
CREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
18、CREATE VIEW
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
19、DELETE
DELETE FROM table_name
WHERE some_column=some_value
or
DELETE FROM table_name
(Note: Deletes the entire table!!)
DELETE * FROM table_name
(Note: Deletes the entire table!!)
20、DROP DATABASE
DROP DATABASE database_name
21、DROP INDEX
DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
22、DROP TABLE
DROP TABLE table_name
23、GROUP BY
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
24、HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
25、IN
SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
26、INSERT INTO
--插入单条数据
insert into Scores
values('张三','英语','95')
--------------------------------------------------
--多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select '李四','语文','89'
union all
select '李四','英语','78'
----------------------------------------------------
INSERT INTO table_name
VALUES (value1, value2, value3,....)
or
INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)
27、INNER JOIN
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
28、LEFT JOIN
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
29、RIGHT JOIN
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
30、FULL JOIN
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
31、LIKE
SELECT column_name(s)
FROM table_name
WHERE column_nameLIKE pattern
32、ORDER BY
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
33、SELECT *
SELECT *
FROM table_name
34、SELECT DISTINCT
SELECT DISTINCT column_name(s)
FROM table_name
35、SELECT INTO
SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
36、SELECT TOP
SELECT TOP number|percent column_name(s)
FROM table_name
37、TRUNCATE TABLE
TRUNCATE TABLE table_name
38、UNION去重并排序
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
39、UNION ALL直接返回合并的结果,不去重也不排序;
union all比union性能好
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
40、UPDATE
update Scores
set Student='王五',Score='94'
where ID=10
41、WHERE
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
总结
其实,说多,也不多。重在练习,很久没有用,手也会生。SQL对于每个人的基础和侧重点,也是不一样的。