SQL经典40句

登陆数据库：

mysql -uroot –p

passwd

 

查看库列表：

show databases;

 

使用指定库

use test;

 

查看表列表

show tables;

 

查看表结构

desc customer;

 

清除表数据

truncate table customer;

 

 

创建数据库并设置字符集

create database test character set utf8;

 

修改数据库字符集

alter database test character set utf8;

Select:

SELECT Store_Name FROM Store_Information;

 

Distinct:

SELECT DISTINCT Store_Name FROM Store_Information;

 

Where:

SELECT Store_Name 
FROM Store_Information 
WHERE Sales > 1000;

 

and or:

SELECT Store_Name 
FROM Store_Information 
WHERE Sales > 1000 
OR (Sales < 500 AND Sales > 275);

 

In:

SELECT * 
FROM Store_Information 
WHERE Store_Name IN ('Los Angeles', 'San Diego');

 

Between:

SELECT * 
FROM Store_Information 
WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';

 

Like:

SELECT * 
FROM Store_Information 
WHERE Store_Name LIKE '%AN%';

 

order by:

SELECT Store_Name, Sales, Txn_Date 
FROM Store_Information 
ORDER BY Sales DESC;

 

函数:

SELECT SUM(Sales) FROM Store_Information;

 

Count:

SELECT COUNT (Store_Name) 
FROM Store_Information 
WHERE Store_Name IS NOT NULL;

 

group by:

SELECT Store_Name, SUM(Sales) 
FROM Store_Information 
GROUP BY Store_Name;

 

Having:

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;

 

（左/内）连接:

SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES 
FROM Geography A1, Store_Information A2 
WHERE A1.Store_Name = A2.Store_Name 
GROUP BY A1.Region_Name;

 

外部连接：

SELECT A1.Store_Name, SUM(A2.Sales) SALES 
FROM Georgraphy A1, Store_Information A2 
WHERE A1.Store_Name = A2.Store_Name (+) 
GROUP BY A1.Store_Name;

 

Subquery：

SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography 
WHERE region_name = 'West');

 

Union：

SELECT Txn_Date FROM Store_Information
UNION
SELECT Txn_Date FROM Internet_Sales;

 

union all：

SELECT Txn_Date FROM Store_Information
UNION ALL
SELECT Txn_Date FROM Internet_Sales;

 

Intersect：

SELECT Txn_Date FROM Store_Information
INTERSECT
SELECT Txn_Date FROM Internet_Sales;

 

Minus：

SELECT Txn_Date FROM Store_Information
MINUS
SELECT Txn_Date FROM Internet_Sales;

 

Concat：

SELECT CONCAT (Region_Name, Store_Name) FROM Geography 
WHERE Store_Name = 'Boston';

 

Substring：

SELECT SUBSTR (Store_Name, 3) 
FROM Geography 
WHERE Store_Name = 'Los Angeles';

 

Trim：

SELECT TRIM('   Sample   ');

SELECT LTRIM('   Sample   ');

SELECT RTRIM('   Sample   ');

 

create table：

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);

 

create view（视观表）：

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;

 

create index：

CREATE INDEX IDX_CUSTOMER_LAST_NAME
ON Customer (Last_Name);

CREATE INDEX IDX_CUSTOMER_LOCATION
ON Customer (City, Country);

 

alter table：

ALTER TABLE Customer ADD Gender char(1);

ALTER TABLE Customer DROP Gender;

ALTER TABLE Customer CHANGE Address Addr char(50);

ALTER TABLE Customer MODIFY Addr char(30);

 

主键：

CREATE TABLE Customer 
(SID integer, 
Last_Name varchar(30), 
First_Name varchar(30), 
PRIMARY KEY (SID));

ALTER TABLE Customer ADD PRIMARY KEY (SID);

 

外键：

CREATE TABLE ORDERS 
(Order_ID integer, 
Order_Date date, 
Customer_SID integer, 
Amount double, 
PRIMARY KEY (Order_ID), 
FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID));

ALTER TABLE ORDERS 
ADD FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID);

 

drop table：

DROP TABLE Customer;

 

truncate table：

TRUNCATE TABLE Customer;

 

insert into：

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999');

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
SELECT store_name, Sales, Txn_Date
FROM Sales_Information
WHERE Year (Txn_Date) = 1998;

 

Update：

UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles' 
AND Txn_Date = 'Jan-08-1999';

 

delete from：

DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';