標籤：資料庫 sql
1.插入語句：INSERT [INTO] table [(column1, column2, column3, . . .)] VALUES(value1, value2, value3, . . .);
例：INSERT INTO user VALUES(NULL, ‘test‘, ‘123456‘);
例：INSERT INTO user (username, password) VALUES(‘test‘, ‘123456‘);
實際上還有一種方式：例：INSERT INTO user SET username=‘test‘, password=‘123456‘;
2.查詢語句：SELECT [option] item [INTO file_details] FROM tables [WHERE condition] [GROUP BY group_type] [HAVING where_definition][ORDER BY order_type] [LIMIT limit_criteria] [PROCEDURE proc_name(arguments)] [lock_option];①簡單查詢例：SELECT * FROM user WHERE uid=4;②多表查詢基本的多表查詢例：SELECT user.username, userinfo.age, userinfo.sex, userinfo.phone FROM user, userinfo WHERE user.uid=userinfo.uid;左關聯SELECT user.uid, uid.name, orders.orderid FROM user LEFT JOIN orders ON user.uid=orders.uid;在沒有使用做關聯的情況下只會返回滿足條件的記錄，如果使用左關聯左表將全部返回即使右表並不匹配，所查的右邊的值將用NULL代替（換句話說：左關聯將返回所有滿足條件的記錄，還會將左表沒有返回的記錄也返回，所需的右表資料如orders.orderid用NULL填充）③使用子查詢基本的子查詢例：SELECT uid, amount FROM orders WHERE amount=(SELECT max(amount) FROM orders);關聯子查詢例：SELECT isbn, title FROM books WHERE exists (SELECT * FROM orders WHERE orders.isbn=books.isbn); //內部查詢引用外部查詢資料行子查詢例：SELECT c1, c2, c3 FROM t1 WHERE (c1, c2, c3) IN (SELECT c1, c2, c3 FROM t2);使用子查詢作為暫存資料表例：SELECT * FROM (SELECT uid, username FROM user WHERE city=‘Beijing‘) AS user_beijing;④合計函數與分組合計函數：avg()count()min()max()std()stddev()sum(). . . 分組通常結合合計函數使用，對結果集進行分組例：SELECT uid, avg(amount) FROM orders GROUP BY uid;HAVING類似於WHERE，只用於合計與分組，SQL中增加HAVING的原因是，WHERE中無法使用合計函數例：SELECT uid, avg(amount) FROM orders GROUP BY uid HAVING avg(amount)>100;⑤排序與限制的使用例：SELECT username, password FROM user ORDER BY username ASC; //查詢user表並按username欄位升序排序例：SELECT username, password FROM user ORDER BY uid DESC; //查詢user表並按uid欄位降序排序例：SELECT username, password FROM user LIMIT 4; //查詢user表前四條記錄例：SELECT username, password FROM user LIMIT 3, 4; //查詢user表從第三條開始之後4條記錄
3.更新語句：UPDATE [LOW_PRIORITY] [IGNORE] table SET column=expression1, column2=expression2, . . .[WHERE condition] [ORDER BY order_criteria] [LIMIT number];例：UPDATE user SET password=‘111111‘; //將user表中所有人密碼設成111111例：UPDATE user SET password=‘111111‘ WHERE uid=4; //將user表中uid等於4的記錄的密碼設成111111例：UPDATE user SET password=‘111111‘ ORDER BY uid DESC LIMIT 5; //將user表中按uid倒序的前5條記錄的密碼設成111111這裡的ORDER BY通常與LIMIT結合使用，單獨使用ORDER BY沒有意義
4.刪除語言：DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [WHERE condition] [ORDER BY order_cols] [LIMIT number];例：DELETE FROM user; //刪除user所有資料例：DELETE FROM user WHERE uid=4; //刪除uid=4的記錄例：DELETE FROM user ORDER BY uid DESC LIMIT 5; //刪除user表中按uid倒序的前5條資料
提示：[]表示可選 在欄位名上加上反引號``可以避免欄位名與資料庫關鍵字衝突 字串值需要用引號‘‘引起來，數值不需要
