SQL基本操作

• mysql.exe :the mysql command-line tool
• mysqld.exe : the mysql server

connect to SQLServer

> mysql -h 127.0.0.1 -P3306 -u root  -p54321
> mysql -u root -p
> 54321

comman show

• show
  
  • databases
  • tables
  • column from ‘table’ –describe ‘table’
  • create database | create table
  • grants
  • errors | warnings

Date-Manipulation Language

SELECT

comman select

select distinct *
from table
order by prod-name, prod-price DESC
limit 5

• AS , Function
• where, Group by, Having(like where), Order by, limit

select vend_id, Count(*) AS num_prods
FROM products
WHERE prod-price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2

select vend_id, Count(*) AS num_prods
from products
Group by vend_id
Having Cunt(*) >= 2;

select order_num, SUM(quantity*item_price) AS ordertotal
from orderotems
Group by order_num
Having SUM(quantity*item_price) >= 50
Order by ordertotal;

comman where

• operator : + … ,between and
• and, or, in, not(in,between,exists)
• like ‘%’, ’ _ ‘

• regexp binary ‘[123]’, ‘.’, ‘^’, ‘$’

• UNION : 针对多个where条件，当需要保留where条件中的重复（所用条件同时满足），必须用UNION

function

• 文本处理函数：RTrim(), LTrim(), Concat()
• 日期和时间处理函数：
• 数值处理函数： Abs(), Cos(), Sin(), Tan(), Exp(), Sqrt(), Mod(), Pi(), Rand()
• aggregate function: [distinct]
  
  • AVG()–ignore null–,
  • COUNT()–COUNT(* )
  • MAX(), MIN(), 对数值和日期，或者相应排序的文本
  • SUM()

镶嵌子查询、联结表(natual join, outer join)

• 镶嵌子查询、联结表

#
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN ( SELECT order_num
                                       FROM orderitems
                                       WHERE prod_id = 'TNT2'))

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderotems.order_num = orders.order_num
  AND prod_id = 'TNT2';

• 内外联结

#列出所有产品以及订购数量
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customer.cust_id = orders.cust_id;

#列出所有产品以及订购数量，包括那些至今尚未下订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

• 使用聚集函数

#对客户下了多少订单进行统计，包括没有人订购的产品
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id ;

全文本收索–索引

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

SELECT note_text,
      Match(note_text) Against('rabbit') AS rank
FROM productnotes;

• 布尔文本搜索

INSERT

INSERT INTO customers
VALUES(NULL, ' ', )

UPDATE, DELETE

UPDATE customers
SET cust_email = NULL
WHERE cust_in = 10000;

DELETE FROM customers
WHERE cust_id = 10006;

TRUNCATE TABLE customers

Date-Definition Language

创建和操纵表

• 创建

• 更新

ALTER TABLE vendors
ADD vend_phone CHAR(20);

ALTER　TABLE vendors
DROP COLUMN vend_phone;

• 删除

DROP TABLE customer2;

• 重命名

RENAME TABLE customer2 TO customer;