Mysql数据库操作总结

常用操作

起别名

起别名时，如果别名有特殊字符，用双引号或者单引号将别名包括起来。（“别名”）

SELECT 
  字段名 AS 别名 
FROM
  表名 ;

去重

去重关键字：distinct。

SELECT DISTINCT 
  字段名
FROM
  表名 ;

字符串拼接

concat(字段名A，字段名B)

SELECT 
  CONCAT(字段名, 字段名) 
FROM
  表名 ;

查询结果为两个字段查询的结果拼接在一起

排序

order by 字段名 （asc或者desc）

asc表示升序排序，desc表示降序排序，默认是升序排序。同时Mysql的排序支持多关键字排序，也就是说，某一个字段的内容相同，可以在此基础上用另一个字段进行再排序。

SELECT 
  字段名 
FROM
  表名 
ORDER BY 字段名 DESC ;

多字段排序

SELECT 
 字段名 
FROM
  表名 
ORDER BY 字段名A ASC,字段名B ASC ;

条件查询

条件运算符

大于：> ，小于：< ，等于： = ，不等于： != 或者用 <>，大于等于： >= ，小于等于： <=

逻辑运算符

与逻辑： && 或者用 and，或逻辑： || 或者用 or，非逻辑： ! 或者用 not

SELECT 
  * 
FROM
  表名 
WHERE salary > 12000 
  AND salary < 120000 
  AND last_name = '小红' ;

模糊查询

like关键字与通配符

%通配符：匹配任意多个字符，包含0个字符。

_通配符：匹配单个字符。

在表中查询名字中第三个字符为e，以字符a结尾的人的信息

SELECT 
  * 
FROM
  employees 
WHERE first_name LIKE '__e%a' ;

转义字符

如果我们要模糊查询字段内容中包含%、_通配符的信息时，要使用转义字符进行转移。

默认转义字符：\

指定转义字符：ESCAPE '指定的转义字符'

下面两句查询语句效果是相同的

SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '_$_%' ESCAPE '$' ;

SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '_\_%' ;

between and关键字

使用between and 关键字进行查询，表示某字段的值在这一范围内，并且包含边界值。

例：查询员工id在100到200区间内的员工信息。（包含100和200）

SELECT 
  * 
FROM
  employees 
WHERE employee_id BETWEEN 100 
  AND 200 ;

in关键字

使用in关键字进行查询，表示某字段的值是否在in列表当中。in列表当中的值必须类型一致或者兼容，例如 '100' 和 100 就是兼容的。

例：查询名字在in列表当中的数据

SELECT 
  * 
FROM
  employees 
WHERE last_name IN ('Kochhar', 'De Haan') ;

is null 和 is not null 关键字

顾名思义，is null 就是查询某字段为null，is not null 就是查询某字段不为null。在Mysql数据库当中，判断等于null不能直接使用等号来进行判断。

例：查询条件为某字段为null

SELECT 
  * 
FROM
  employees 
WHERE commission_pct IS NULL ;

例：查询条件为某字段不为null

SELECT 
  * 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

分组查询

简单使用

下面的sql语句可以理解为，查询出job_id，然后按照job_id进行分组，分别得到每一组的最大工资。

SELECT 
  MAX(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;

分组前添加筛选条件

分组前添加筛选条件使用where，将查询到的结果再进行分组。

SELECT 
  MAX(salary) AS 最高工资,
  job_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY job_id ;

分组后添加筛选条件

分组后添加筛选条件用having，将分组后的数据再筛选。

SELECT 
  MAX(salary) AS 最高工资,
  job_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY job_id 
HAVING 最高工资 > 10000 ;

以函数为分组条件

可以以函数来当作分组的条件。

下面的查询就是以名字的长度来进行分组

SELECT 
  MAX(salary) AS 最高工资,
  LENGTH(last_name) AS 长度 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY 长度 
HAVING 最高工资 > 10000 ;

按多字段分组

下面的sql语句将查询结果按照部门id和工种id进行分组，部门id和工种id相同的分为一组。

SELECT 
  AVG(salary) 平均工资,
  department_id 部门id,
  job_id 工种id 
FROM
  employees 
GROUP BY 部门id,
  工种id 
ORDER BY 平均工资 ;

多表查询

当查询的字段来自多个表时，就需要使用多表查询

等值连接

按照字段的内容进行连接

下面的sql语句从两个表中查找数据，将beauty表中的boyfriend_id字段和boys表中id字段进行连接。

SELECT 
  `name`,
  boyName 
FROM
  boys,
  beauty 
WHERE beauty.boyfriend_id = boys.id ;

如果有查询的不同表之间存在字段名相同的情况，我们就需要用表名来限定字段。

下面的sql语句对表名取别名，通过表名来限定字段，**对表名取了别名之后，不能再用原表名来限定字段。 **

SELECT 
  emp.last_name,
  emp.job_id,
  job.job_title 
FROM
  employees emp,
  jobs job 
WHERE emp.`job_id` = job.`job_id` ;

上面的sql语句标准为sql92，我们还可以参照sql99的标准写成如下形式，效果相同，语法不同。

SELECT 
  emp.last_name,
  emp.job_id,
  job.job_title 
FROM
  employees emp 
  INNER JOIN jobs job 
    ON emp.`job_id` = job.`job_id` ;

** 多个表联合查询时，只需要添加 inner join 表名 on 连接条件 即可。**

非等值连接

下面的sql语句连接条件是一个范围，根据工资的范围显示工资的等级。

SELECT 
  emp.last_name,
  emp.job_id,
  job.grade_level 
FROM
  employees emp,
  job_grades job 
WHERE emp.`salary` BETWEEN job.`lowest_sal` 
  AND job.`highest_sal` ;

sql99标准写法如下：

SELECT 
  emp.last_name,
  emp.job_id,
  job.grade_level 
FROM
  employees emp 
  INNER JOIN job_grades job 
    ON emp.`salary` BETWEEN job.`lowest_sal` 
    AND job.`highest_sal` ;

自连接

顾名思义，就是当前表自己和自己连接在一起

例：下面的sql语句将自己表中的员工信息和上级信息连接在一起，自连接时，由于是在同一张表中查询，需要给表设置别名，这样才能区分查询的字段。

SELECT 
  员工.`employee_id` 员工id,
  员工.`last_name` 员工名,
  上级.`employee_id` 上级id,
  上级.`last_name` 上级名 
FROM
  employees 员工,
  employees 上级 
WHERE 员工.`manager_id` = 上级.`employee_id` ;

sql99标准写法如下：

SELECT 
  员工.`employee_id` 员工id,
  员工.`last_name` 员工名,
  上级.`employee_id` 上级id,
  上级.`last_name` 上级名 
FROM
  employees 员工 
  INNER JOIN employees 上级 
    ON 员工.`manager_id` = 上级.`employee_id` ;

外连接

外连接查询结果为主表中的所有记录，如果从表当中有匹配的数据，就显示匹配的值，否则显示null。

左外连接

left (outer) join 表名 on 连接条件

以左边的表为主表，右边的表为从表建立连接

SELECT 
  b1.`name`,
  b1.`boyfriend_id`,
  b2.`id`,
  b2.`boyName` 
FROM
  beauty b1 
  LEFT OUTER JOIN boys b2 
    ON b1.`boyfriend_id` = b2.`id` ;

右外连接

right (outer) join 表名 on 连接条件

以右边的表为主表，左边的表为从表建立连接

SELECT 
  b1.`name`,
  b1.`boyfriend_id`,
  b2.`id`,
  b2.`boyName` 
FROM
  beauty b1 
  RIGHT OUTER JOIN boys b2 
    ON b1.`boyfriend_id` = b2.`id` ;

全外连接

Mysql不支持这种形式

全外连接查询结果=内连接结果+表1中有但是表2中没有的+表2中有但是表1中没有的

交叉连接

cross join 表名

查询结果为两个表的笛卡尔乘积

SELECT 
  * 
FROM
  beauty b1 
  CROSS JOIN boys b2 ;

子查询

出现在语句内部的select语句，称为子查询或内查询，查询语句内部嵌套了其他select语句的查询称为外查询或主查询。

按结果集的行列数不同划分如下：

标量子查询：结果集一行一列

列子查询：结果集一列多行

行子查询：结果集一行多列

表子查询：结果集一般为多行多列

where后面的标量子查询

子查询的结果为一行一列并且放在where后面

下面的sql语句查询工种和员工id141的员工相同，并且工资大于员工id为143的人的信息

SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id = 
  (SELECT 
    job_id 
  FROM
    employees 
  WHERE employee_id = 141) 
  AND salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE employee_id = 143) 
ORDER BY salary DESC ;

where后面的列子查询

操作符：

in/not in ：等于或不等于列表中的任意一个。

any/some：两个关键字达到的效果相同，都是和子查询的结果的某一个值进行比较，满足其中一个条件即可。

all：和子查询的结果的所有值进行比较，满足所有条件才行。

使用in/not in，查询location_id是1400或1700的部门中的所有员工姓名

SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;

使用any/some，查询其他工种中比job_id为IT_PROG任一工资低的员工的员工号、姓名、job_id以及salary。

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id != 'IT_PROG' ;

使用any/some代码可读性不高，我们通常采用如下写法达到相同的效果。因为any是满足一个条件即可，那么可以写作小于查询结果集中最高工资。

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT DISTINCT 
    MAX(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id != 'IT_PROG' ;

使用all，查询其他部门中比job_id为IT_PROG的部门所有工资都低的员工的员工号、姓名、job_id以及salary。

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id != 'IT_PROG' ;

all代码可读性也不高，由于all是要满足所有的条件，那么可以用min函数来简化。

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT DISTINCT 
    MIN(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id != 'IT_PROG' ;

where后面的行子查询

查询员工编号最小并且工资最高的员工信息

通过标量子查询能够达到上面的要求

SELECT 
  * 
FROM
  employees 
WHERE employee_id = 
  (SELECT 
    MIN(employee_id) 
  FROM
    employees) 
  AND salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;

上面的代码略显重复，我们可以通过行子查询来简化。

SELECT 
  * 
FROM
  employees 
WHERE (employee_id, salary) = 
  (SELECT 
    MIN(employee_id),
    MAX(salary) 
  FROM
    employees) ;

select 后面的子查询

子查询位于select后面的，仅仅支持标量子查询（即一行一列）。

将子查询语句直接写在select后面，下面的sql语句查询部门id和员工个数，select后面的子查询一般可以用其他的方式代替，并且效率更高。

SELECT 
  department_id,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.department_id) AS 员工个数 
FROM
  departments d ;

from 后面的子查询

下面的sql语句查询每个部门的平均工资的工资等级，相当于将from后面的子查询语句的结果当作了一个表，然后和job_grades表进行连接。

SELECT 
  ag_dep.*,
  job.`grade_level` 
FROM
  (SELECT 
    AVG(salary) ag,
    department_id 
  FROM
    employees 
  GROUP BY department_id) ag_dep 
  INNER JOIN job_grades job 
    ON ag_dep.ag BETWEEN lowest_sal 
    AND highest_sal ;

exists 后面的子查询

这种查询不常用，了解即可。并且这种查询的功能可以用前面其他的查询方式代替，并且可读性更高。

查询结果为1或者0，当exists后面的子查询有值时为1，查询结果为空时为0.

下面的sql语句查询是否有员工的工资大于30000

SELECT 
  EXISTS 
  (SELECT 
    employee_id 
  FROM
    employees 
  WHERE salary = 30000) ;

分页查询

limit A , B ： 从第A条数据开始往后数B条数据返回，索引从0开始计算。

接下来的sql语句查询从第1条数据开始往后数5条数据。（即前五条数据）

SELECT 
  * 
FROM
  employees 
LIMIT 0, 5 ;

联合查询

查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的信息一致时，就要使用联合查询。

使用联合查询注意事项：

1.多条查询语句的查询列数是一致的。

2.多条查询语句查询的每一列的类型和内容最好顺序一致。

3.union关键字默认去重，要显示全部的数据用union all。

SELECT 
  id,
  cname,
  csex 
FROM
  t_ca 
WHERE csex = '男' 
UNION
#union all
SELECT 
  t_id,
  tName,
  tGender 
FROM
  t_ua 
WHERE tGender = 'male' ;

插入数据

基本使用

字段名和数据要对应，顺序可以调换，可以为null的字段可以插入null。

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUE (数据1, 数据2, ...) ;

可以省略列名，默认插入所有列，而且列的顺序和表中列的顺序一致。

INSERT INTO 表名 VALUE (数据1, 数据2, ...) ;

插入多行

** 可以插入多行数据，用逗号分隔开即可。**

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUE (数据1, 数据2, ...),
(数据1 .1, 数据2 .1, ...),
(数据3 .1, 数据3 .1, ...) ;

支持子查询

** 支持子查询，可以将查询得到的数据插入进表中。注意格式、类型、长度的统一。**

INSERT INTO 表名 (字段名1, 字段名2, ...) 
SELECT 
  字段名A,
  字段名B,
  ...
FROM
  表2 ;

修改数据

修改单表数据

UPDATE 
  表名 
SET
  字段名1 = 新值,
  字段名2 = 新值 
WHERE 筛选条件 ;

修改多表数据

修改多表的数据和查询类似，先连接不同的表，相当于建立了一个虚拟的表，再根据条件修改这个虚拟的表。

UPDATE 
  表1 别名1 
  INNER|left|right JOIN 表2 别名2 
    ON 连接条件 SET 字段名 = 新值 
WHERE 筛选条件 ;

删除数据

单表删除

DELETE 
FROM
  表名 
WHERE 筛选条件 ;

truncate 全部删除，不能加where条件，删除效率比delete高。

使用truncate删除数据后，自增长列的值从1开始，而delete从断点开始。

truncate删除没有返回值，并且不能回滚。

TRUNCATE TABLE 表名 ;

多表删除

DELETE 
  表1,
  表2 
FROM
  表1 
  INNER|left|right JOIN 表2 
    ON 连接条件 
WHERE 筛选条件 ;