带你入门mysql常用命令

约定：

本篇博文的数据库名称为 “test”，数据表有employees, salaries, dept_emp等数据表，方便大家理解每条命令的涵义，并根据自己的需求更改sql语句。
数据表的结构主要来源于**[牛客网](https://www.nowcoder.com)**，**特此感谢**，数据表的结构如下：

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

1. 创建数据库

Create database test

2. 创建数据表

就employees表为例，创建流程如下：

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

primary key 代表主键，not null 表示值不能为空

3. 创建视图

创建一个关于employees的视图，命名为v_emloyees，只取emp_no, first_name, last_name属性，操作如下：

CREATE view as v_employees as 
    select emp_no,first_name,last_name from employees;

4. 创建索引

以employees表为例，对emp_no创建唯一索引uni_idx_emp_no，对first_name创建普通索引idx_firstname
索引的使用：
查询emp_no = 100000的数据:

CREATE UNIQUE INDEX uni_idx_emp_no on employees(emp_no);
  CREATE INDEX idx_firstname on employees(first_name);
  select * from employees FORCE INDEX uni_idx_emp_no WHERE emp_no=100000

5. 创建触发器

构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中。
其中audit表为：
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
创建触发器的sql语句为：

CREATE TRIGGER audit_log after insert on employees
  begin
        insert into audit values(new.emp_no,new.to_date);
  end;

6. 修改表名

修改employees 表名为 yuangong

alter table employees rename to yuangong

7. 增加表的属性

在employees表中增加salary 属性，默认值为1000000

alter table employees add column salary int(11) default 1000000;

8. 批量插入数据及如何插入带时间字段的数据表

批量插入employees表（时间字段注意看）：
上面是普通的手动插入操作，但我们在开发中更多情况可能是通过参数给values赋值，对于int型用’%d’通配符，对于char用’%s’通配符，对于datetime类型，用‘%s’通配符，但是传入的datetime字段一定要是时间字段格式的！这点得注意。以下是示例sql代码：

insert or ignore into employees values(1,"2017-08-05","mike","jerry",'M','2017-08-16'),(1,"2017-08-05","Joe","Lily",'M','2017-08-16');

datetime字符串类型如何转成时间格式，可参考：

insert or ignore into employees values('%d','%s','%s','%s','%s','%s');

关于mysql中date类型的数据操作可参考 MySQL 获得当前日期时间 函数

9. 联合查询inner join,left join,right join

联合查询中inner join ,left join,right join 的概念可参考： SQL中inner join、outer join和cross join的区别
这里，我们要查询employees表中员工对应的工资(salary)，而工资在salaries表中，哈哈，这就要用到inner join 联合查询了，查询语句如下：

select e.emp_no,s.salary 
  from employees as e inner join salaries as s on e.emp_no=s.emp_no;

10. 查询表中最多/最少的数据（以最多查询为例）

用max，min函数可查询表中最多/最少数据，以salaries表为例：

select emp_no,max(salary) from salaries;

11. 查询表中第二多/第二少的数据（以第二多查询为例）

查询第二多/第二少的数据有两种方法，第一种的思想是通过limit命令，只显示排序后的第二条数据；第二种思想是将最多/最少的数据去掉，再查询的不就是第二多/第二少的数据啦~

#第一种写法
select emp_no,salary from salaries order by salary desc limit 1,1;

#第二种写法
select s1.emp_no,s1.max(salary) from salaries as s1
  where  s1.salary not in (select max(salary) from salaries);

12. 查询每组中某一属性最多的数据

比如这里我们要查询每个部门（dept_emp）中工资（salary）最高的员工信息，那该怎么做呢，根据前面的介绍，需要用到max，inner join 等函数，具体sql语句如下：

select de.dept_emp,max(s.salary) 
from salaries as s inner join dept_emp as de on de.emp_no=s.emp_no
group by de.dept_emp;

13. 查询表奇数行/偶数行的数据

查询奇数行/偶数行数据的思想是，对每一条数据进行排序（排名），统计每条数据在第几行，然后根据where语句进行筛选即可，实现过程对于新手来说可能比较绕，以employees表为例，sql语句如下，大家可以根据自己需求将表名替换即可。

SELECT e1.first_name FROM 
  (SELECT e2.first_name, 
    (SELECT COUNT(*) FROM employees AS e3 
     WHERE e3.first_name <= e2.first_name) 
   AS num FROM employees AS e2) AS e1
WHERE e1.num % 2 = 1;

14. 获取某一属性的增长情况

比如在这里，我们要获取每个员工的薪资（salary）涨幅，实现方法：

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no) AS sCurrent
INNER JOIN (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth;

15. 对employees表进行排序，按照first_name的后三个字母的规则，降序排列

这里涉及到Substr的使用，sql语句如下：

select * from employees order by substr(first_name,-3)
  select * from employees order by substr(first_name,length(first_name)-2);

16. 按照某一属性值对表中样本排名

这一个的思想和查询奇数行/偶数行的数据的思想一致，以salaries表为例，sql语句如下

SELECT s1.emp_no,s1.salary,COUNT(s2.salary) as rank
from salaries as s1,salaries as s2
where s1.salary<=s2.salary
order by s1.emp_no asc;

17. 查找字符串’10,A,B’ 中逗号’,’出现的次数cnt。

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt;

18. 拼接数据表属性

拼接主要用到了concat（）函数，具体用法可参考：MySQL中函数CONCAT及GROUP_CONCAT
以employees为例，拼接first_name 和 last_name ，返回值为name的sql语句：

SELECT concat(first_name," ",last_name) as name from employees;

感谢：
*1.再次感谢牛客网提供的数据表，以便把具体写法呈现给大家
2.感谢wasrehpic大犇提供的sql思路，让我在了解mysql操作上少走了不少弯路*