SQL简明笔记上
1. SELECT语句
查询 SELECT statement
SELECT column_combination --1
FROM table_combination --2
WHERE conditional_expression--3
ORDER BY column_ordermode_combination--4
GROUP BY column_combination--5
HAVING function_condition--6
Note:
1.1 include function(column_name),function contains AVG,SUM,COUNT,MAX,MIN.
1.2 可以使用DISTINCT修饰column_name表示记录不重复
2.1 可以查询一个表也可以多表查询,之间用逗号隔开
3.1 column_name LIKE pattern
,通配符有_和%
3.2 column_name IN (values_set)
离散值
3.3 column_name BETWEEN value1 AND value2
连续值
3.4 sample conditions would be comnbinated by AND|OR
4.1 ordermode:ASC|DESC
,ASC means ascending.
5.1 all except function(column)
6.1 function(column) expression
2. TABLE DEFINE 语句
建表 CREATE TABLE statement
MySQL:
CREATE TABLE table_name
(column_first vartype NOT NULL,
column_second vartype CHECK(column_second_expressionn),
column_third vartype UNIQUE,
column_fourth vartype,
column_fifth vartype,
Primary Key (column_fourth),
Foreign Key (column_fourth) REFERENCES another_table(primaryKey))
Oracle/SQL Server:
CREATE TABLE table_name
(column_first vartype Primary Key,
column_second vartype NOT NULL,
column_third vartype UNIQUE,
column_fourth vartype CHECK(column_fourth_expression),
column_fifth vartype Foreign Key REFERENCES another_table(primaryKey))
Note:
- CONTRAINT {NOT NULL,UNIQUE,CHECK,PRIMARY KEY,FOREIGN KEY} 可以省略
- column可以是很多项
- vartype:{
int,float,date,double,char,bigint,smallint,tinyint,bit,decimal,numeric,money,smallmoney,float,real,datetime,smalldatetime, varchar,txt,nvachar,ntext,binary,varbinary,image,cursor,sql_variant,table,timestamp,uniqueidentifier
}
改变表的架构 ALTER TABLE statement
ALTER TABLE table_name
change_mode
Note:
1.ADD PRIMARY KEY (column_name)
2.ADD FOREIGN KEY (column_name) REFERENCES another_table(primaryKey)
3.ADD column_name vartype
4.DROP column_name
5.CHANGE original_column_name new_column_name vartype
6.MODIFY column_name column_new_name
虚拟的表VIEW和INDEX
视图可以被被当作虚拟表格。他跟表格的不同是,表格中有实际存储资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。
CREATE VIEW view_name
AS select_statement
索引可以帮助我们从表格中快速地找到需要的资料。语法如下:
CREATE INDEX index_name
ON table_name(column_name_combination)
Note:
1.默认名一般取作V_name和IDX_name
2.索引可以取一个栏位名,也可以取多个,索引越少速度越快
3. INSERT 语句
增加记录 INSERT INTO VALUES
INSERT INTO table_name(column_combination)
VALUES(value_combination)
跨表增加记录 INSERT INTO SELECT
INSERT INTO table_name(column_combination)
SELECT_statement
4. UPDATE 语句
UPDATE table_name
SET column_name=new_value
WHERE conditional_expression
5. 删除语句
删除表格
DROP TABLE table_name
清空表格
TRUNCATE TABLE table_name
删除记录
DELETE FROM table_name
WHERE conditional_expression
6. 子查询subquiry
SELECT语句返回的结果可以看成一个值(1*1),一个值的集合(1*n)(用于IN条件语句中),或者一个表(n*n)
当一个SELECT语句结果作为另一个WHERE语句的一部分时,即为子查询,可以使用别名等。
如果子查询与外部查询的表格无法,即为simple subquery;若内部查询要利用外部查询提到的表格,则为Correlated Subquery)
SELECT column_combination
FROM table_combination
WHERE column_name IN|LIKE|>|...
(SELECT_statement)
SQL简明笔记上
1. SELECT语句
查询 SELECT statement
SELECT column_combination --1
FROM table_combination --2
WHERE conditional_expression--3
ORDER BY column_ordermode_combination--4
GROUP BY column_combination--5
HAVING function_condition--6
Note:
1.1 include function(column_name),function contains AVG,SUM,COUNT,MAX,MIN.
1.2 可以使用DISTINCT修饰column_name表示记录不重复
2.1 可以查询一个表也可以多表查询,之间用逗号隔开
3.1column_name LIKE pattern
,通配符有_和%
3.2column_name IN (values_set)
离散值
3.3column_name BETWEEN value1 AND value2
连续值
3.4 sample conditions would be comnbinated byAND|OR
4.1 ordermode:ASC|DESC
,ASC means ascending.
5.1 all except function(column)
6.1 function(column) expression
2. TABLE DEFINE 语句
建表 CREATE TABLE statement
MySQL:
CREATE TABLE table_name
(column_first vartype NOT NULL,
column_second vartype CHECK(column_second_expressionn),
column_third vartype UNIQUE,
column_fourth vartype,
column_fifth vartype,
Primary Key (column_fourth),
Foreign Key (column_fourth) REFERENCES another_table(primaryKey))
Oracle/SQL Server:
CREATE TABLE table_name
(column_first vartype Primary Key,
column_second vartype NOT NULL,
column_third vartype UNIQUE,
column_fourth vartype CHECK(column_fourth_expression),
column_fifth vartype Foreign Key REFERENCES another_table(primaryKey))
Note:
- CONTRAINT {NOT NULL,UNIQUE,CHECK,PRIMARY KEY,FOREIGN KEY} 可以省略
- column可以是很多项
- vartype:{
int,float,date,double,char,bigint,smallint,tinyint,bit,decimal,numeric,money,smallmoney,float,real,datetime,smalldatetime, varchar,txt,nvachar,ntext,binary,varbinary,image,cursor,sql_variant,table,timestamp,uniqueidentifier
}
改变表的架构 ALTER TABLE statement
ALTER TABLE table_name
change_mode
Note:
1.
ADD PRIMARY KEY (column_name)
2.ADD FOREIGN KEY (column_name) REFERENCES another_table(primaryKey)
3.ADD column_name vartype
4.DROP column_name
5.CHANGE original_column_name new_column_name vartype
6.MODIFY column_name column_new_name
虚拟的表VIEW和INDEX
视图可以被被当作虚拟表格。他跟表格的不同是,表格中有实际存储资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。
CREATE VIEW view_name
AS select_statement
索引可以帮助我们从表格中快速地找到需要的资料。语法如下:
CREATE INDEX index_name
ON table_name(column_name_combination)
Note:
1.默认名一般取作V_name和IDX_name
2.索引可以取一个栏位名,也可以取多个,索引越少速度越快
3. INSERT 语句
增加记录 INSERT INTO VALUES
INSERT INTO table_name(column_combination)
VALUES(value_combination)
跨表增加记录 INSERT INTO SELECT
INSERT INTO table_name(column_combination)
SELECT_statement
4. UPDATE 语句
UPDATE table_name
SET column_name=new_value
WHERE conditional_expression
5. 删除语句
删除表格
DROP TABLE table_name
清空表格
TRUNCATE TABLE table_name
删除记录
DELETE FROM table_name
WHERE conditional_expression
6. 子查询subquiry
SELECT语句返回的结果可以看成一个值(1*1),一个值的集合(1*n)(用于IN条件语句中),或者一个表(n*n)
当一个SELECT语句结果作为另一个WHERE语句的一部分时,即为子查询,可以使用别名等。
如果子查询与外部查询的表格无法,即为simple subquery;若内部查询要利用外部查询提到的表格,则为Correlated Subquery)
SELECT column_combination
FROM table_combination
WHERE column_name IN|LIKE|>|...
(SELECT_statement)