SQL学习笔记（基础部分）

select column_name,column_name from table_name;

select * from table_name;

select distinct column_name,column_name from table_name;

select column_name,column_name 
from table_name
where column_name operator value;

select * from custormers where country = 'Germany' and (city = 'Berlin' or City = 'Beijing')

select column_name,column_name from table_name order by column_name,column_name ASC|DESC

insert into table_name
values(value1,value2,value3,...)

insert into table_name(column1,column2,column3,...)
values(value1,value2,value3,...)
#会形成新行，但只在指定列插入数据，custormid列会自动更新

update table_name
set column1=value1,column2=value2,...
where some_column=some_value
#通过where来限制更新行，缺失where语句会对整列都进行修改

delete from table_name where some_column = some_value

delete from table_name;
or
delete * from table_name;
#删除表中所有数据

#SQL Server / MS Access 语法
SELECT TOP number|percent column_name(s) FROM table_name;

#MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的 MySQL 语法 select column_name(s) from table name limit number;

#Oracle语法
select column_name(s) from table_name where rownum <= number;

select column_name(s) from table_name where column_name like pattern;

# %运用
SELECT * FROM Customers WHERE City LIKE '%es%';

#_运用
SELECT * FROM Customers WHERE City LIKE '_erlin';

# SQL[charlist]运用 选取以a、b、c开头的所有
SELECT * FROM Customers WHERE City LIKE '[a-c]%';

#选取 City 不以 "b"、"s" 或 "p" 开始的所有客户
SELECT * FROM Customers WHERE City LIKE '[!bsp]%';

select column_name(s)
from table_name
where colunmn_name in (value1,value2,...);

select column_name(s)
from table_name
where column_name in(select column from table_w3c where ...);

select column_name(s) from table_name where colunmn_name between value1 and value2;

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND CategoryID NOT IN (1,2,3);

select table1_name.column_name(s),table2_name.column_name(s) from table1_name inner join table2_name on table1_name.column = table2_name.column #基于表之间的共同字段相连接

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
#默认选取不同值，如果要允许重复值，使用union all

SELECT * INTO newtable [IN externaldb] #外部数据库 FROM table1;

INSERT INTO Customers (CustomerName, Country) SELECT SupplierName, Country FROM Suppliers WHERE Country='Germany';