搜集所闻所见的SQL语句，无论简单还是复杂，只按时间顺序，持续更新。。。
CREATE DATABASE mydb1
创建一个名为 mydb1 的数据库。如果这个数据库已经存在会报错
CREATE DATABASE IF NOT EXISTS mydb1
如果名为 mydb1 的数据库不存在就创建该库
DROP DATABASE mydb1
删除一个名为 mydb1 的数据库。如果这个数据库已经删除会报错
DROP DATABASE IF EXISTS mydb1
如果名为 mydb1 的数据库存在就删除该库
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
创建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');
插入数据到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’;
更新数据到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中的数据
CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;
创建一个数据库用户
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;//上条的优化，把comm中的null转为0
SELECT *, sal+IFNULL(comm,0) total FROM emp;//上条的优化，取列名为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;//统计表数据条数
SELECT COUNT(comm) cnt FROM emp;//统计表中comm有数据的条数
SELECT COUNT(*) FROM emp WHERE sal > 2500;
SELECT COUNT(comm), COUNT(mgr) FROM emp;//统计comm有数据的条数和mgr有数据的条数
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;//内链接
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基础知识总结
