- 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;