坚持自己的目标

SQL语句大全

上一篇 / 下一篇  2017-09-18 15:47:59 / 个人分类:SQL

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')

 

 


TAG: SQL SQL语句

 

评分:0

我来说两句

wudemin

wudemin

从事软件测试---智能家居

日历

« 2024-03-29  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 25594
  • 日志数: 21
  • 图片数: 1
  • 文件数: 5
  • 建立时间: 2016-11-09
  • 更新时间: 2017-09-18

RSS订阅

Open Toolbar