Select * from store_information where store_name in ('los angeles', 'san diego');
Select * from store_information where date between 'jan-06-1999' and 'jan-10-1999';
Select * from store_information where store_name like '%an%';
Select store_name, sales, date from store_information order by sales desc;
Avg(平均)
Count(计数)
Max(最大值)
Min(最小值)
sum(总和)
Select sum(sales) from store_information;
Select count(distinct store_name) from store_ginformation
Select store_name, sum(sales) from store_information group by store_name;
Select store_name, sum(sales) from store_information group by store_name having
Sum(sales)>1500
Alias(别名)
Select A1.store_name store, sum(A1.sales) 'total sales' from Store_information A1 group by A1.store_name
Join(连接)
Delete
Delete from store_information where store_name ='los angeles'
Update
Update store_information
Set sales =500
Where store_name ='los angeles'
And date ='jan-08-1999';
Insert into
一次输入一笔
Insert into store_information(store_name,sales,date) values('los angeles', 900,'jan-10-1999');
一次输入多笔
Insert into store_information(store_name,sales,date)
Select store_name,sales,date
From sales_information
Where year(date) =1998
Truncate table
表格中的资料会完全消失,但表格本身会继续存在
Truncate table customer;
Drop table
从数据库中清除一个表格
Drop table customer
主键(primary key)
Create table customer
(SID integer,
Last_name varchar(30),
First_name varchar(30),
Primary key(SID)
);
Alter table customer ADD primary key (SID);
Alter table
加一个栏位---add
删去一个栏位--drop
改变栏位名称--change
改变栏位的资料种类--modify
Alter table customer add gender char(1);
Alter table customer change address addr char(50);
Alter table customer modify addr char(30);
Alter table customer drop gender;
Create index索引
索引可以帮助我们从表格中快速地找到需要的资料。
Create index 'index_name' on 'table_name' (column_name)
Create view 虚拟表格
Table customer (first_name char(50),
Last_name char(50),
Address char(50),
City char(50)
Country char(25),
Birth_date date);
Create table 建立表格
Create table customer
(first_name char(50),
Last_name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_date date
)
TRIM 移除掉一个字串中的字头或字尾的空白
Mysql:Trim(),Rtrim(),Ltrim()
Oracle:RTRIM(),LTRIM()
SQL Server: RTRIM(),LTRIM()
Select trim(' sample ');
Substring 用来抓出一个栏位资料中的一部分。
Mysql: substr(), substring()
Oracle:substr()
Sql server:substring()
Select substr(store_name,3) from geography where store_name ='Los Angeles';
Select substr(store_name,2,4) from geography where store_name ='san diego';
Union 目的的是将两个SQL语句的结果合并起来。
当我们用union这个指令时,我们只会看到不同的资料值。(类似select distinct)
Select date from store_information union select date from internet_sales;
subquery在一个SQL语句中放入另一个SQL语句。
第一,它可以被用来连接表格。另外,有的时候subquery是唯一能够连接两个表格的方式
Select sum(sales) from store_information where store_name in (select store_name
From geography where region_name = 'west')