【MySql】mysql语法

显示数据库语句：
SHOW DATABASES
当前数据库中的所有表
SHOW TABLES;
查看表的字段信息
DESC employee;
查看当前使用的数据库
select database();

查看表格的创建细节
SHOW CREATE TABLE user;
显示数据库创建语句：
SHOW CREATE DATABASE db_name
 
数据库删除语句：
DROP DATABASE [IF EXISTS] db_name 

修改表的名称：Rename table 表名 to 新表名
RENAME TABLE employee TO user;

ALTER ============================================
在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。
ALTER TABLE employee MODIFY job varchar(60);

删除image列,一次只能删一列。
ALTER TABLE employee DROP image;

修改database的字符集为utf8;
ALTER database mydb2 character set utf8;
修改表的字符集为gbk
ALTER TABLE user CHARACTER SET gbk;

列名name修改为username
ALTER table user change column name username varchar(20);
ALTER TABLE user CHANGE name username varchar(100);

修改操作 UPDATE:

将所有员工薪水修改为5000元。
UPDATE emp SET salary=5000 

将姓名为’zs’的员工薪水修改为3000元。
UPDATE emp SET salary=3000 WHERE name=’zhangsan’;

将姓名为’aaa’的员工薪水修改为4000元, gender改为female。
UPDATE emp SET salary=4000,gender='female' WHERE name='aaa';

将性别为male的薪水在原有基础上增加1000元。
UPDATE emp SET salary=salary+1000 WHERE gender='male';  

删除操作 DELETE

删除表中名称为’zs’的记录。
DELETE FROM emp WHERE name=‘zs’;

删除表中所有记录。
DELETE FROM emp;

使用truncate删除表中记录。
TRUNCATE TABLE emp;

DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

模糊查询

查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';
模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';

查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';
其中“%”匹配0~n个任何字母。

查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';

查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';

字段控制查询

去除重复记录
SELECT DISTINCT dept_no FROM dept;
比如要获取有多少个部门时，可以查询出所有的部门no，然后去重就得出有多少部门了。

查看雇员的月薪与佣金之和
comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。
下面使用了把NULL转换成数值0的函数IFNULL：
SELECT *,sal+IFNULL(comm,0) FROM emp;

给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时，是可以省略AS关键字的：
SELECT *,sal+IFNULL(comm,0)  total FROM emp;

多列排序
SELECT * from emp order by sal, empno desc;
先按sal排序，当出现sal一样的情况下再按empno排序

聚合函数 sum avg max min count

统计指定列不为NULL的记录行数；
查询emp表中有佣金的人数：
SELECT COUNT(comm) cnt FROM emp;
SELECT COUNT(*) AS cnt FROM emp;

查询emp表中月薪大于2500的人数：
SELECT COUNT(*) FROM emp WHERE sal > 2500;
统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

分组查询

查询每个部门的部门编号和每个部门的工资和：
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;


查询工资总和大于9000的部门编号以及工资和：
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
注：having与where的区别:
1.having是在分组后对数据进行过滤, where是在分组前对数据进行过滤
2.having后面可以使用聚合函数(统计函数),where后面不可以使用聚合函数。

常用数据类型：

int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10)  'abc       '
varchar：可变长度字符串类型；varchar(10) 'abc'
text：字符串类型;
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

GROUP BY语法

GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
某个员工信息表结构和数据如下：

屏幕快照 2018-05-01 下午9.29.50.png
我想列出每个部门最高薪水的结果，sql语句如下：
select dept , max(salary) AS MAXIMUM FROM STAFF GROUP BY DEPT
查询结果如下：
屏幕快照 2018-05-01 下午9.31.31.png

将where子句与group by子句一起使用
例如，查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT dept,edlevel,MAX(salary) AS MAXIMUM FROM STAFF WHERE hiredate > '2010-01-01' GROUP BY dept,edlevel
查询结果如下：

屏幕快照 2018-05-01 下午9.35.18.png

GROUP BY子句之后使用Having子句
例如，查找雇员数超过2个的部门的最高和最低薪水：
SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING COUNT(*) > 2 ORDER BY dept
查询结果如下：

屏幕快照 2018-05-01 下午9.40.12.png
例如，查找雇员平均工资大于3000的部门的最高薪水和最低薪水：
SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING AVG(salary) > 3000 ORDER BY dept
屏幕快照 2018-05-01 下午9.42.21.png

SQL JOIN

不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
INNER JOIN 与 JOIN 是相同的。

屏幕快照 2018-05-01 下午9.50.11.png

引用两个表
我们可以通过引用两个表的方式，从两个表中获取数据：
谁订购了产品，并且他们订购了什么产品？

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 

屏幕快照 2018-05-01 下午9.51.50.png

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

这个结果集和上面一样，两个表最多有5个结果集

LEFT JOIN 关键字

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

屏幕快照 2018-05-01 下午9.58.45.png

两个表最多有7个结果集

RIGHT JOIN 关键字

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

屏幕快照 2018-05-01 下午10.05.03.png

两个表最多有7个结果集

FULL JOIN 关键字

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

屏幕快照 2018-05-01 下午10.09.43.png

两个表最多有8个结果集