SQL语言学习笔记

1.从表格中选出一栏

SELECT store_name FROM Store_Information

 

2.选出表格中某栏位的所有值(不重复

SELECT DISTINCT store_name FROM Store_Information

 

3.选出营业额超过 $1,000 的资料(用WHERE关键字

SELECT store_name
FROM Store_Information
WHERE Sales > 1000

 

4.复杂条件选择(AND OR关键字)

SELECT store_name
FROM Store_Information

WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)

 

5.在几个栏内选择(关键字IN)

SELECT *
FROM Store_Information
WHERE store_name IN (‘Los Angeles’, ‘San Diego’)

 

6.在一个范围内取值(关键字BETWEEN)

SELECT *
FROM Store_Information
WHERE Date BETWEEN ‘Jan-06-1999’ AND ‘Jan-10-1999’

 

7.在栏位名中选择(关键字LIKE)

SELECT *
FROM Store_Information
WHERE store_name LIKE ‘%AN%’

  • ‘A_Z’: 所有以 ‘A’ 起头,另一个任何值的字原,且以 ‘Z’ 为结尾的字串。
  • ‘ABC%’: 所有以 ‘ABC’ 起头的字串。
  • ‘%XYZ’: 所有以 ‘XYZ’ 结尾的字串。
  • ‘%AN%’: 所有含有 ‘AN’ 这个套式的字串。

 

8. 选出内容升序、降序排列(关键字ORDER BY,默认ASC)

SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales(2) DESC       //对第二个栏位(Sales)降序排列

 

9.SQL函数

  • AVG (平均)
  • COUNT (计数)
  • MAX (最大值)
  • MIN (最小值)
  • SUM (总合)

    SELECT SUM(Sales) FROM Store_Information

     

    10.数出在表格中有多少笔资料被选出来(关键字COUNT)

    SELECT COUNT(store_name)      //SELECT COUNT(DISTINCT store_name) 有多少笔不同名的资料
    FROM Store_Information
    WHERE store_name is not NULL

     

    11.对各家店分别求和(关键字GROUP BY)

    SELECT store_name, SUM(Sales)
    FROM Store_Information
    GROUP BY store_name

     

    12.哪些店的总营业额有超过 $1,500(不能用WHERE,因为此项是算出的值。条件关键字HAVING

    SELECT store_name, SUM(sales)
    FROM Store_Information
    //GROUP BY store_name
    HAVING SUM(sales) > 1500

     

    13.ALIAS(别名)

    SELECT "表格别名"."栏位1" "栏位别名"
    FROM "表格名" "表格别名"

    SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
    FROM Store_Information A1
    GROUP BY A1.store_name

     

     14.表格链接

    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

     

    15.OUTER JOIN (外部连接)

    (表格之后加上一个 "(+)" 来代表说这个表格中的所有资料我们都要)

    SELECT A1.store_name, SUM(A2.Sales) SALES
    FROM Georgraphy A1, Store_Information A2
    WHERE A1.store_name = A2.store_name (+)   //表A1中没有,表A2中有
    GROUP BY A1.store_name

     

    16.Subquery结构

    SELECT SUM(Sales) FROM Store_Information
    WHERE Store_name IN
    (SELECT store_name FROM Geography
    WHERE region_name = ‘West’) 

    从另一个表格中西区店名与本表相同的店中,选出本表中这些店的营业额

     

    17.UNION 指令的目的是将两个 SQL 语句的结果合并起来(相同的只出现一次)

    SELECT Date FROM Store_Information
    UNION
    SELECT Date FROM Internet_Sales

    (相当于SELECT DISTINCT Date

     

    18.找出有店面营业额以及网络营业额的日子(结果中同一天会重复显示)

    SELECT Date FROM Store_Information
    UNION ALL
    SELECT Date FROM Internet_Sales

     

    19.INTERSECT 是选出量两表中数据的交集

    SELECT Date FROM Store_Information
    INTERSECT
    SELECT Date FROM Internet_Sales

     

    20.MINUS 指令找出(在第一个 SQL 语句的结果中)不在(在第二个 SQL 语句的结果中)的资料(差集)

    SELECT Date FROM Store_Information
    MINUS
    SELECT Date FROM Internet_Sales

     

    21.连接字符串

    • MySQL: CONCAT()
    • Oracle: CONCAT(), ||
    • SQL Server: +

    MySQL/Oracle:
    SELECT CONCAT(region_name,store_name) FROM Geography
    WHERE store_name = ‘Boston’;

    store_name为Boston处的region_name为East,所以结果为:EastBoston

    Oracle:
    SELECT region_name || ‘ ‘ || store_name FROM Geography
    WHERE store_name = ‘Boston’;

    结果为:East Boston

    SQL Server:
    SELECT region_name + ‘ ‘ + store_name FROM Geography
    WHERE store_name = ‘Boston’;

    结果为:East Boston

     

    22.SUBSTRING

    SUBSTR(str,pos):从第pos个字符读到底

    SUBSTR(str,pos,len):从第pos个字符读len个字符

    SELECT SUBSTR(store_name, 3)
    FROM Geography
    WHERE store_name = ‘Los Angeles’;

    结果: ‘s Angeles’

    SELECT SUBSTR(store_name,2,4)
    FROM Geography
    WHERE store_name = ‘San Diego’;

    结果: ‘an D’

     

    23.TRIM 函数是用来移除掉一个字串中的字头或字尾

    SELECT TRIM(‘   Sample   ‘);

    结果: ‘Sample’

    SELECT LTRIM(‘   Sample   ‘);

    结果: ‘Sample   ‘

    SELECT RTRIM(‘   Sample   ‘);

    结果: ‘   Sample’

     

    24.CREATE TABLE

    CREATE TABLE "表格名"
    ("栏位 1" "栏位 1 资料种类",
    "栏位 2" "栏位 2 资料种类",
    … )

    CREATE TABLE customer
    (First_Name char(50),
     Last_Name  char(50),
     Address      char(50),
     City           char(50),
     Country      char(25),
     Birth_Date  date)

     

    25.CREATE VIEW(视观表)

    CREATE VIEW V_REGION_SALES
    AS 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 * FROM V_REGION_SALES

     

    26.Create Index(建立索引)

    语法:CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

    CREATE INDEX IDX_CUSTOMER_LAST_NAME ON CUSTOMER (Last_Name)

     

    27.ALTER TABLE(修改表格)

  • 加一个栏位: ADD "栏位 1" " 资料种类"
  • 删去一个栏位: DROP "栏位 1"
  • 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
  • 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"

    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

     

    28.主键 (Primary Key)用来独一无二地确认一个表格中的每一行资料

    在建置新表格时设定主键的方式:

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

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

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

    以下则是以改变现有表格架构来设定主键的方式:

    MySQL:
    ALTER TABLE Customer ADD PRIMARY KEY (SID);

    Oracle:
    ALTER TABLE Customer ADD PRIMARY KEY (SID);

    SQL Server:
    ALTER TABLE Customer ADD PRIMARY KEY (SID);

    主键的栏位不能为NULL

     

    29.外来键:是一个(或数个)指向另外一个表格主键的栏位。

    建置 ORDERS 表格时指定外来键的方式

    MySQL:
    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));

    Oracle:
    CREATE TABLE ORDERS
    (Order_ID integer primary key,
    Order_Date date,
    Customer_SID integer references CUSTOMER(SID),
    Amount double);

    SQL Server:
    CREATE TABLE ORDERS
    (Order_ID integer primary key,
    Order_Date datetime,
    Customer_SID integer references CUSTOMER(SID),
    Amount double);

    改变表格架构来指定外来键:

    MySQL:
    ALTER TABLE ORDERS
    ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

    Oracle:
    ALTER TABLE ORDERS
    ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

    SQL Server:
    ALTER TABLE ORDERS
    ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

     

    30.DROP TABLE(清除表格)

    DROP TABLE customer

     

    31.TRUNCATE TABLE 清除一个表格中的所有资料

    TRUNCATE TABLE customer

     

    32.把资料插入表格(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

     

    33.修改表格中的资料(UPDATE

    UPDATE "表格名"
    SET "栏位1" = [新值]
    WHERE {条件}

    UPDATE Store_Information
    SET Sales = 500
    WHERE store_name = "Los Angeles"
    AND Date = "Jan-08-1999"

     

    34.数据库中去除一些资料(DELETE FROM

    DELETE FROM "表格名"
    WHERE {条件}

    DELETE FROM Store_Information
    WHERE store_name = "Los Angeles"

     

    参考资料:http://sql.1keydata.com/cn/

     

  • This entry was posted in Uncategorized. Bookmark the permalink.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s