MySql常用基本命令

一、常用命令

1. 查看当前所有数据库

show databases;

2. 打开指定的库

use 库名;

3. 查看当前库的所有表

 show tables;

4. 查看其他库的所有表

 show tables from 库名;

5. 创建表

 create table 表名(

                    列名  列类型，

                    列名  列类型，

                    ...
                                            );

6. 查看表结构

 desc 表名;

7. 查看服务器版本

方式一：登录到mysql服务端

select version();

方式二：命令行

mysql --version

二、语法规范、建议

1. 建议关键字大写，表名、列名小写

2. 每条命令最好用分号结尾

3. 注释 

        单行注释：

# 注释文字

        单行注释：

-- 注释文字

        多行注释：

/*注释文字*/

三、 SQL学习

3.1 基础查询

select表示 选择 过滤 查看

语法：

select 查询列表 from 表名;

-- 查询列表可以是：表中的字段、常量值、表达式、函数

-- 查询的结果是一个虚拟的表格

1）查询表中的单个字段

SELECT last_name FROM employees;

2）查询表中的多个字段(逗号隔开)

SELECT last_name, salary, email FROM employees;

3）查询表中所有字段（*）

SELECT * FROM employees;

4）查询常量值

SELECT 100;

SELECT 'John';

5）查询表达式

SELECT 100%98;

6）查询函数

SELECT VERSION();

7）为字段起别名  使用AS，也可以省略AS使用空格

SELECT 100*98 AS 别名；

SELECT last_name AS 姓，firsrt_name AS 名 FROM employees;

SELECT last_name  姓，firsrt_name 名 FROM employees;

-- 案例 查询salary，显示结果为 out put (有空格需要加双引号 区分)

SELECT last_name "out put" FROM employees;

8）去重 关键字 DISTINCT

-- 显示员工表中的部门id

SELECT department_id FROM employees;

-- 去重 关键字 DISTINCT

SELECT DISTINCT department_id FROM employees;

9）+号的作用  运算符 

select '100'+90; -- 如果可以转换成数字，则数字相加 此句即select 190;

10）CONCAT 用于连接

-- 查询员工名和姓并连接成一个字段，并显示为 姓名

SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees;

3.2 条件查询

语法：       

SELECT

      查询列表

FROM
      表名

WHERE       

      筛选条件;

-- 执行顺序 先执行From 表名判断表是否存在， 再判断筛选条件， 最后Select

分类：

1）按条件表达式筛选

条件运算符：> < =  >= <=  = 还有 不等于<>

-- case1 查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
-- case2 查询部门编号不等于90的员工名和部门编号
SELECT last_name, department_id FROM employees WHERE departmen_id<>90;

2）按逻辑表达式筛选（用于连接条件表达式）（true false）

逻辑运算符： && ||  ！ and or not

-- case1 查询工资在1w到2w之间的员工名、工资和奖金
SELECT last_name, salary, commision_pct 
FROM employees 
WHERE salary>=10000 AND salary<=20000;
-- case2 查询部门编号不在90到110之间，或者工资高于1w5的员工信息
SELECT *
FROM employees
WHERE NOT(department_id >= 90 AND department_id<=110) OR salary>15000

3）模糊查询

like 一般和通配符搭配使用、 
between and (相当于 a<= x <=b)、
in、
is null 、is not null

通配符：
1）%为通配符 包含任意多个字符 包括0个
2）_为通配符 包含任意单个字符

a）like

-- case1 员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';  
-- 其中%为通配符 包含任意多个字符 包括0个

-- case2 员工名中第三个字符为e,第5个字符为a的员工信息
SELECT * FROM employees WHERE last_name LIKE '__e_a%';  
-- 其中_为通配符 包含一个任意字符

-- case3 员工名中第2个字符为_的员工信息，此时需要转义
SELECT * FROM employees WHERE last_name LIKE '_\_%';  
-- 其中\为转义符

-- 也可以自定义转义字符 ESCAPE关键字创建转义符
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';  

b）between and (相当于 a<= x <=b)

-- case1 查询员工id在100-120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
--相当于
SELECT * FROM employees WHERE employee_id >= 100 AND employee_id <= 120;

c）in (相当于=)

-- case1 查询员工的工种编号是是 IT_PROG、AD_VP、AD_PRES中的一种的员工信息
SELECT * FFROM employees WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');
-- 相当于
SELECT * FFROM employees 
WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES'

d）is null 、 is not null (=或者不等于<>不能用于判断null)

-- case1 查询有奖金的员工名和奖金率
SELECT last_name, commision_pct FROM employees WHERE conmmision_pct IS NOT NULL;

e）<=> 安全等于 可以用于判断普通值和NULL值 (可读性较差)

-- case1 查询没有奖金的员工名
SELECT last_name FROM employees WHERE conmmision_pct <=> NULL;
-- case2 查询工资=12000的员工名
SELECT last_name FROM employees WHERE salary <=> 12000;

 3.3 排序查询

语法：

SELECT 查询列表
FROM 表名
WHERE 筛选条件
ORDER BY 排序列表 [asc | desc]

-- case1 查询员工信息， 要求工资从高到底排序
SELECT * FROM employees ORDER BY salary DESC;

-- case2 查询部门编号>=90的员工信息，按入职时间先后排序
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate;

-- case3 按年薪高低显示员工的信息 和 年薪（年薪按表达式降序）
SELECT *, salary*12*(1+IFNULL(commision_pct, 0)) 年薪 
FROM employees 
ORDER BY salary*12*(1+IFNULL(commision_pct, 0)) DESC;

-- case4 按姓名的长度显示员工的姓名和工资（按函数排序）
SELECT LENGTH(last_name) 字节长度, last_name, salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

-- case5 员工信息，要求先按工资排序  再按员工编号排序 （按多个字段排序）
SELECT *
FROM employees 
ORDER BY salary ASC, employee_id DESC;

3.3 函数

单行函数

分组函数：做统计使用，又称为统计函数、聚合函数、组函数。

3.3.1 单行函数

1）字符函数

1. 
LENGTH()函数  获取字符串长度

2. 
CONCAT() 拼接多个字符串

3.
UPPER()、LOWER()  大小写转换  

4. 
SUBSTR()、SUBSTRING()  子字符串
SELECT SUBSTR('1234567', 5);  -- 输出‘567’,左边闭区间
SELECT SUBSTR('1234567', 1, 3)  -- 输出‘123’,左边闭区间

5. 
INSTR()  返回子串在原字符串的起始索引，找不到返回0（索引从1开始）
SELECT INSTR('12345', '34')  -- 输出3

6.
TRIM()  去掉字符串首尾的某个字符，默认空格
SELECT TRIM('    1 23     '); -- 输出 ‘1 23’
SELECT TRIM('a' FROM 'aaa1aa23aaaaa');  -- 输出 ‘1aa23’

7.
LPAD() 左填充  用指定的字符实现左填充至指定长度
RPAD() 右填充
SELECT LPAD('123', 5, '0'); -- 输出 '00123'

8.
REPLACE() 替换函数
SELECT REPLACE('123aa456aa', 'a', 'b'); --输出 ‘123bb456bb’

2）数学函数

1. 
round() 四舍五入
SELECT ROUND(1.617); -- 2
SELECT ROUND(1.617, 2); -- 四舍五入 保留2位小数 1.62

2.
ceil() 向上取整
floor() 向上取整
truncate() 截断

SELECT TRUNCATE(1.6999, 1); --指定位数截断 1.6

3. mod() 取余  mod(a, b) = a- a/b*b

3）日期函数

1. now() 返回当前系统 日期 时间

2. curdate() 返回日期

3. curtime() 返回时间

4. year() month() ... 获取指定的部分，年、月、日、小时、分钟、秒 
monthname()返回英文名称

5. 将日期格式的字符改成指定格式的日期
STR_TO_DATE('9-13-1999', '%m-%d-%Y')  --1999-09-13
将日期转成字符
DATE_FORMAT('2018/6/6', '%Y年%m月%d日') -- 2018年06月06日

4）流程控制函数（分支语句）

a）if函数

SELECT IF(10>5, '大'， '小')  -- 输出 大

-- 查询是否有奖金
SELECT commision_pct, IF(commision_pct IS NULL, '没有奖金', '有奖金') 备注;
-- 输出两列，一列是commision_pct, 另一列是备注 

b) case函数

case语法1：（类似switch case）

case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
...
else 要显示的值n或者语句n; -- 默认情况
end

case语法2：（类似多重if）

case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 默认语句n;
end

-- 查询员工的工资，要求
-- 部门号=30， 显示的工资为1.1倍
-- 部门号=40， 显示的工资为1.2倍
-- 其他，显示原始工资

SELECT salary 原始工资, department_id, 
CASE department_id
WHEN 30 THEN salary*1.1;
WHEN 40 THEN salary*1.2;
ELSE salary;
END
AS 新工资
FROM employees;

3.3.2 聚合函数（分组函数）

sum avg max min count

ps1：sum，avg， max, min, count会忽略null值

ps2：可以和DISTINCT搭配，去除重复

ps3：count函数的特殊用法

--统计行数（行必须有非null值）
SELECT COUNT(*) FROM employees; 
--统计1个数
SELCET COUNT(1) FROM employees; 

 ps4：和分组函数一同查询的字段要求是group by后的字段

3.4 分组查询

比如统计每个部门的平均工资，就需要用到分组查询，将每个部门的薪资分成一组。

分组 GROUP BY 子句语法：

SELECT column(要求出现在group by后面), group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

ps：查询列表必须特殊，要求是分组函数和group by后出现的字段

1）分组前筛选 和 分组后筛选 的用法如下

-- case 1 查询每个工种的最高薪资
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id;

-- case 2 查询每个位置上的部门个数
SELECT COUNT(*), location_id
FROM  departments
GROUP BY location_id;

-- case 3 查询邮箱中包含字符a的，每个部门的平均工资（分组前的筛选）
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

-- case 4 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

-- case 5 查询哪个部门的员工个数>2  （分组后的筛选 HAVING）
-- 思路 先查询每个部门的员工个数，再筛选
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2; -- HAVING子句实现了筛选

-- case 6 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id
HAVING MAX(salary) > 12000;

-- case 6 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪些，以及其最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

2）按函数的分组筛选

-- case 1 按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有几个
SELECT COUNT(*), LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

3）按多个字段分组

-- case 1 查询每个部门每个工种的员工平均工资
SELECT AVG(salary), job_id, department_id
FROM employees
GROUP BY job_id, department_id;

4）添加排序的分组查询

-- case 1 查询每个部门每个工种的员工平均工资>10000的平均工资，并且按平均工资降序排序
SELECT AVG(salary), job_id, department_id
FROM employees
GROUP BY job_id, department_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;

3.5 连接查询（多表查询）

内连接：查询表之间有相同的部分

外连接：用于查询一个表有，另一个表没有的记录。

SELECT name, boyName FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;　--添加有效条件

 

不写了太累了  直接上b站看吧， 麻了................................