mysqladmin -u root -p DROP home
DROP DATABASE home;

mysqladmin -u root -p CREATE home
CREATE DATABASE home;  /* This is comments. */
mysql -u root -p home < D:\home.sql -- This is comments

数据库操作语句（Data Mainpulation Language，DML）
包括 SELECT、INSERT、UPDATE、DELETE 等


数据定义语言（Data Definition Language，DDL）
包括 CREATE、 DROP、 ALTER 等


数据控制语言（Data Control Language， DCL）
包括 GRANT、REVOKE、BEGIN 、 COMMIT 、ROLLBACK 等

INSERT INTO customer (mid, nam, sex) VALUES('H0003', '黎明', 0);
INSERT INTO customer (mid, nam, birth) VALUES('G0008', '杜海涛', '1975-04-18');
INSERT INTO customer (mid, nam, sex) VALUES('X0008', '爱新觉罗', 1);
INSERT INTO customer VALUES('Z007', '玄烨', NULL, 1); /* 注意顺序，必须全部指定 */



UPDATE customer SET sex=0, birth='1987-08-22' WHERE mid='T0001';
UPDATE customer SET birth=NULL; /* 对所有记录生效 */



DELETE FROM customer WHERE mid = 'Z0007';
DELETE FROM customer; /* 删除所有记录 */
TRUNCATE TABLE customer; /* 也会删除所有记录 */

SELECT * FROM customer;
SELECT mid, nam FROM customer;
SELECT mid, nam FROM customer WHERE birth>='1970/1/1';
SELECT mid, nam FROM customer WHERE birth>='1970-1-1';
SELECT mid, nam FROM customer WHERE birth<>'1970-1-1'; /* 不等于 */
SELECT mid, nam FROM customer WHERE nam IS NULL;  /* IS NOT NULL*/
SELECT mid, nam FROM customer WHERE nam LIKE '小%'; /* NOT LIKE */
SELECT mid, nam FROM customer WHERE price BETWEEN 3000 AND 5000; /* NOT BETWEEN */
SELECT mid, nam FROM customer WHERE mid IN ('G0001','G0002','G0003'); /* NOT IN */

SELECT nam, birth, sex FROM customer WHERE ( birth <= '1976-1-1' OR birth >= '1980/1/1') AND sex=1;
SELECT nam, birth, sex FROM customer ORDER BY sex ASC, birth DESC;
/* 省略排序方式（ASC、DESC），默认：ASC*/
/* MySQL 中，NULL 被看成最小值 */

SELECT sex, birth, COUNT(mid) FROM customer GROUP BY sex, birth;
SELECT nam, birth, SUM(mid) FROM customer GROUP BY birth;
SELECT nam, birth AS bir, SUM(mid) AS sum_id FROM customer GROUP BY birth;
/* AVG:求平均值，COUNT:对非NULL记录进行计数，MAX:最大值，MIN:最小值，SUM：求和*/

SELECT pname, price * 0.9 AS inside_price FROM product;
/* + - * /  DIV % */
SELECT 5 DIV 2; /* 2, 整数除法，只取整数部分 */
SELECT 5 % 2; --1
SELECT 29 MOD 9; --求模
SELECT MOD(34.5, 3); -- 1.5，求模

数据库函数

SELECT LENGTH('理由'); -- 4，返回字节数
SELECT CHAR_LENGTH('理由'); --2，字符串长度
SELECT BIT_LENGTH('理由'); -- 32，比特数

SELECT FLOOR(2.5); --2
SELECT CEILING(2.5); -- 3
SELECT FLOOR(-2.5); -- -3
SELECT CEILING(-2.5); -- -2

SELECT ROUND(114.566); -- 115
SELECT ROUND(114.566, 2); -- 114.57
SELECT ROUND(114.566, -2); -- 100

SELECT DATE_ADD('2012-11-21 12:00:00', INTERVAL 2 MONTH) AS newtime;
SELECT DATE_ADD('2012-11-21 12:00:00', INTERVAL -2 HOUR) AS newtime;
SELECT DATE_ADD('2012-11-21 12:00:00', INTERVAL '10:50' MINUTE_SECOND) AS newtime;

SELECT EXTRACT(YEAR_MONTH FROM birth) FROM customer;
SELECT EXTRACT(YEAR_MONTH FROM '1988-10-19 12:00:00') AS birth;

SELECT nam, CASE sex WHEN 0 THEN '男' WHEN 1 THEN '女' ELSE '0TH' END AS sex FROM customer;
SELECT pname, CASE WHEN price < 1000 THEN '低' WHEN price <= 3000 THEN '一般' ELSE '高' END AS price FROM product;

SELECT user.name, order_basic.oid FROM order_basic INNER JOIN user ON order_basic.uid = user.uid; -- 内连接形式
SELECT u.name, o.oid FROM order_basic AS o INNER JOIN user AS u ON o.uid = u.uid;

SELECT u.name, o.oid FROM user AS u
LEFT OUTER JOIN order_basic AS o ON u.uid = o.uid;
/* 左连接，左侧表（user）中数据全部抽出 */

SELECT u.name, o.oid FRO