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 FROM user AS u
RIGHT OUTER JOIN order_basic AS o ON u.uid = o.uid;
/* 右连接,右侧表(order_basic)中数据,全部抽出*/
多表连接
SELECT ob.oid, ob.odate, p.pname, p.price, od.quantity, u.name FROM(
( order_basic AS ob INNER JOIN order_detail AS od ON ob.oid = od.oid)
INNER JOIN product AS p ON od.pid = p.pid )
INNER JOIN user AS u ON ob.uid = u.uid;
子查询
SELECT * FROM product WHERE price > (SELECT AVG(price) FROM product );
/* 子查询,结果,返回单一值 */
SELECT name, address FROM user WHERE uid NOT IN ( SELECT uid FROM order_basic WHERE odate = '2010-07-24' );
/* 子查询,返回多个值 */
SELECT name, address FROM user WHERE EXISTS ( SELECT * FROM order_basic WHERE user.uid = order_basic.uid );
/* 相关子查询 */