搜集所聞所見的SQL語句压怠，無論簡單還是復雜池充，只按時間順序桩引，持續(xù)更新。纵菌。阐污。
CREATE DATABASE mydb1
創(chuàng)建一個名為 mydb1 的數(shù)據(jù)庫。如果這個數(shù)據(jù)庫已經(jīng)存在會報錯
CREATE DATABASE IF NOT EXISTS mydb1
如果名為 mydb1 的數(shù)據(jù)庫不存在就創(chuàng)建該庫
DROP DATABASE mydb1
刪除一個名為 mydb1 的數(shù)據(jù)庫咱圆。如果這個數(shù)據(jù)庫已經(jīng)刪除會報錯
DROP DATABASE IF EXISTS mydb1
如果名為 mydb1 的數(shù)據(jù)庫存在就刪除該庫
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
創(chuàng)建stu表
ALTER TABLE stu ADD (classname varchar(100));
給 stu 表添加 classname 列
ALTER TABLE stu MODIFY gender CHAR(2);
修改 stu 表的 gender 列類型為 CHAR(2)
ALTER TABLE stu change gender sex CHAR(2);
修改 stu 表的 gender 列名為 sex
ALTER TABLE stu DROP classname;
刪除列：刪除 stu 表的 classname 列
ALTER TABLE stu RENAME TO student;
修改 stu 表名稱為 student
INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');
INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');
插入數(shù)據(jù)到stu表
UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;
更新數(shù)據(jù)到stu表
DELETE FROM stu WHERE sid=’s_1001’003B;
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;
TRUNCATE TABLE stu;// 先DROP TABLE笛辟，再 CREATE TABLE，速度快無法回滾
刪除stu中的數(shù)據(jù)
CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;
創(chuàng)建一個數(shù)據(jù)庫用戶
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;
給用戶授權
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
取消授權
SHOW GRANTS FOR user1@localhost;
查看授權
DROP USER ‘user1’@localhost;
刪除權限
use mysql;
alter user '用戶名'@localhost identified by '新密碼';
修改密碼
SELECT * FROM stu;
SELECT sid, sname, age FROM stu;
SELECT * FROM stu WHERE gender='female' AND age<50;//且
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';//或
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003')
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');
SELECT * FROM stu WHERE age IS NULL;
SELECT * FROM stu WHERE age>=20 AND age<=40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
SELECT * FROM stu WHERE gender!='male';//非
SELECT * FROM stu WHERE gender<>'male';//非
SELECT * FROM stu WHERE NOT gender='male';
SELECT * FROM stu WHERE NOT sname IS NULL;
SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE sname LIKE '_ _ _ _ _';
SELECT * FROM stu WHERE sname LIKE '_ _ _ _i';
SELECT * FROM stu WHERE sname LIKE 'z%';//以“z”開頭
SELECT * FROM stu WHERE sname LIKE '_i%';//以"某i"開頭
SELECT * FROM stu WHERE sname LIKE '%a%';//包含“a”字母
SELECT DISTINCT sal FROM emp;//去重
SELECT *,sal+comm FROM emp;//查詢sal+comm之和
SELECT *, sal+IFNULL(comm,0) FROM emp;//上條的優(yōu)化序苏，把comm中的null轉(zhuǎn)為0
SELECT *, sal+IFNULL(comm,0) total FROM emp;//上條的優(yōu)化手幢，取列名為total
SELECT * FROM stu ORDER BY sage ASC;//按年齡升序排序
SELECT * FROM stu ORDER BY sage;
SELECT * FROM stu ORDER BY age DESC;//降序
SELECT * FROM emp ORDER BY age DESC ,empno ASC;//如果年齡相同時，按empno升序排序
SELECT COUNT(*) AS cnt FROM emp;//統(tǒng)計表數(shù)據(jù)條數(shù)
SELECT COUNT(comm) cnt FROM emp;//統(tǒng)計表中comm有數(shù)據(jù)的條數(shù)
SELECT COUNT(*) FROM emp WHERE sal > 2500;
SELECT COUNT(comm), COUNT(mgr) FROM emp;//統(tǒng)計comm有數(shù)據(jù)的條數(shù)和mgr有數(shù)據(jù)的條數(shù)
SELECT SUM(sal) FROM emp;//sal的總和
SELECT SUM(sal), SUM(comm) FROM emp;
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
SELECT SUM(sal), COUNT(sal) FROM emp;//平均值
SELECT AVG(sal) FROM emp;//平均值
SELECT MAX(sal), MIN(sal) FROM emp;//最高和最低
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;
SELECT deptno ,COUNT(*)
FROM emp
WHERE sal>1500`
GROUP BY deptno;
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
SELECT * FROM emp LIMIT 0, 5;//查詢 5 行記錄忱详，起始行從 0 開始
SELECT * FROM emp LIMIT 3, 10;
select staff.name,deptname from staff,deptno where
staff.name=deptno.name;//內(nèi)鏈接
select staff.name,deptname from staff left join deptno onstaff.name=deptno.name;//外連接（左連接）
select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;//外連接（右連接）
查詢表中的列
特別鳴謝：
史上最全SQL基礎知識總結(jié)
