开源数据库MySQL DBA运维实战-SQL-2

一.DML

1.目的

• 在MySQL管理软件中，DDL定义数据库结构。
• 通过SQL语句中的DML语言来实现数据的操作，包括使用：
  1.insert 实现数据的插入
  2.delete 实现数据的删除
  3.update 实现数据的更新

2.插入数据insert

• 完整插入
  语法：insert into 表名 values （值1，值2，值3…值n）；
• 部分插入
  语法：insert into 表名 （列名1，列名2） values （值1，值2）；

3.更新数据update

• 语法：update 表名 set 列名=值 where condition；
• 示例1：

1.准备一张表

mysql> create table t6(id int, name varchar(20));
mysql> insert into t6 values (1,'aa');
mysql> insert into t6 values (2,'bb');

2.更新数据（把bb改成cc）

mysql> update   t6   set   name='cc' where id=2;

3.查询结果

mysql> select * from t6;

• 示例2：
  修改mysql数据库管理员root账户密码

mysql> update mysql.user set authentication_string=password("Aa123456") where user="root";
mysql> flush privileges;   #刷新
mysql> exit

4.删除数据delete

• 语法：delete from 表名 where condition；
• 示例：（删除id为2的用户记录）

mysql> delete from t6 where id=2;

二.DQL

1.目的

• 在MySQL管理软件中，可以通过SQL语句中的DQL语言来实现数据的SELECT 查询操作，互联网用户查询余额，查询装备，查询商品的操作。

2.MySQL查询

2.1准备环境

• 素材1

#准备一张三列信息的表

mysql> create table t3 (id int,name varchar(20),age  int);

#插入数据

mysql> insert into t3 values (1,"zhangsan",23);
mysql> insert into t3 values (2,"lisi",24);
mysql> insert into t3 values (3,"wangwu",18);

• 素材2

1.表结构

2.结构语句

mysql> create database company;
mysql> CREATE TABLE company.employee5(
     -> id int primary key AUTO_INCREMENT not null,
     -> name varchar(30) not null,
     -> sex enum('male','female') default 'male' not null,
     -> hire_date date not null,
     -> post varchar(50) not null,
     -> job_description varchar(100),
     -> salary double(15,2) not null,
     -> office int,
     -> dep_id int
     -> );

3.查看表结果

mysql> desc employee5;

4.插入数据

mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
 -> ('jack','male','20180202','instructor','teach',5000,501,100),
 -> ('tom','male','20180203','instructor','teach',5500,501,100),
 -> ('robin','male','20180202','instructor','teach',8000,501,100),
 -> ('alice','female','20180202','instructor','teach',7200,501,100),
 -> ('aofa','male','20180202','hr','hrcc',600,502,101),
 -> ('harry','male','20180202','hr',NULL,6000,502,101),
 -> ('emma','female','20180206','sale','salecc',20000,503,102),
 -> ('christine','female','20180205','sale','salecc',2200,503,102),
 -> ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
 -> ('gougou','male','20180205','sale','',2200,503,102);

2.2简单查询

• 查看所有列
  select * from 表名; #前提是需要进入数据库。不进入数据库，就请输入库名。
• 查看部分列
  select 列1，列2，列3 FROM 表名;
• 查看年薪
  select name, salary, salary*14 from employee5;

2.3条件查询

• 1.但条件查询where

#查询hr部门员工姓名

mysql> select name,post from employee5 where post='hr';

• 2.多条件查询and/or

#查询hr部门的员工姓名并且工资大于1000

mysql> select name,salary from employee5 where post='hr' and salary>1000;

#查询所有部门的员工姓名并且工资是6000或者8000的员工

mysql> select name,salary from employee5 where salary=6000 or salary=8000;

• 3.关键字between and

#薪资在5000到15000

mysql> select name,salary from employee5 where salary between 5000 and 15000;

#不在5000到15000之间使用not

mysql> select name,salary from employee5 where salary not between 5000 and 15000;

• 4.关键字in集合查询

#工资可能是4000，也可能是5000，还有可能是9000，怎么查？

mysql> select name, salary from employee5 where salary=4000 or salary=5000 or salary=6000 or salary=9000;

#优解

mysql> select name, salary from employee5 where salary in (4000,5000,6000,9000);
mysql> select name, salary from employee5 where salary not in (4000,5000,6000,9000);

• 5.关键字is null

#没有岗位描述的

mysql> select name,job_description from employee5 where job_description is not null;

• 6.关键字like模糊查询

#好像有个员工姓阿
#通配符’%’代表多个任意字符
#注意不是shell的"*"星号。mysql使用"%"

mysql> select * from employee5 where name like 'al%';

#注意不是shell的“？”问号。mysql使用"_"下划线
#通配符’_’代表1个任意字符

mysql> select * from employee5 where name like 'al___';

2.4查询排序

• 1.例如以工资升序排列

mysql> select * from 表名 order by 工资的列名 asc;

• 2.例如以工资降序排列

mysql> select * from 表名 order by 工资的列名 desc;

• 3.工资最高的前5名

#默认为升序

mysql> select * from employee5 order by salary desc limit  5; 

2.5扩展任务-MySQL多表查询

• 1.前言
  如何根据部门总表中，查询出分表信息
  

• 2.分类
  a.多表连接查询：复合条件连接查询
  b.子查询：根据查询结果查询
  c.准备工作

#准备员工信息表
mysql> create table info(
name char(50),
age int,
dep_num int,
level_num int);

mysql> desc info;

mysql> insert into info values 
('zhangsan',23,101,1),
('lisi',25,102,2),
('wangwu',30,102,3),
('zhaosi',30,103,4),
('sunba',35,NULL,NULL);

mysql> select * from info;

• 3.多表的连接查询

a.分类

交叉连接1	生成笛卡尔积，它不使用任何匹配条件
内连接	只连接匹配的行
外连接	左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配；右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配

b.交叉连接

特点：全部组合（A表5行，B表7行，最后5*7=35行）
语法：生成笛卡尔积，它不使用任何匹配条件，touch {a…c}{1…3}
示范：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info,department;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| zhangsan |   23 |     101 | hr       |
| zhangsan |   23 |     101 | tec      |
| zhangsan |   23 |     101 | exp      |
| zhangsan |   23 |     101 | admin    |
| lisi     |   25 |     102 | hr       |
| lisi     |   25 |     102 | tec      |
| lisi     |   25 |     102 | exp      |
| lisi     |   25 |     102 | admin    |
| wangwu   |   30 |     102 | hr       |
| wangwu   |   30 |     102 | tec      |
| wangwu   |   30 |     102 | exp      |
| wangwu   |   30 |     102 | admin    |
| zhaosi   |   30 |     103 | hr       |
| zhaosi   |   30 |     103 | tec      |
| zhaosi   |   30 |     103 | exp      |
| zhaosi   |   30 |     103 | admin    |
| qianqi   |   30 |     104 | hr       |
| qianqi   |   30 |     104 | tec      |
| qianqi   |   30 |     104 | exp      |
| qianqi   |   30 |     104 | admin    |
| sunba    |   35 |    NULL | hr       |
| sunba    |   35 |    NULL | tec      |
| sunba    |   35 |    NULL | exp      |
| sunba    |   35 |    NULL | admin    |
+----------+------+---------+----------+
24 rows in set (0.00 sec)

c.内连接
特点：两列相同时，才会显示
需求：显示员工的部门信息
语法：select 字段列表 from 表1，表2 where 表1.字段=表2.字段；
示例：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info,department where info.dep_num = department.dep_num;

d.外连接
特点：两列相同时显示，并以左/右表为主
语法：A表 left join B表 on 条件是
外连接（左连接left join on）：找出所有员工及所属的部门，包括没有部门的员工，查看所有员工的部门信息。
示例：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info left join department on info.dep_num = department.dep_num;

外连接(右连接right join on)：

#显示所有部门的员工信息

mysql> select info.name,info.age,info.dep_num,department.dep_name from info right join department on info.dep_num = department.dep_num;

#找出公司所有部门中年龄大于25岁的员工

mysql> select info.name,info.age,info.dep_num,department.dep_name from info right join department on info.dep_num = department.dep_num and age > 25;

#找出公司所有部门中的员工，对他们的年龄排序

mysql> select info.name,info.age,info.dep_num,department.dep_name from info right join department on info.dep_num = department.dep_num order by age ASC;

• 4.子查询

a.简介

子查询是指：父查询 需要 依赖 子查询的结果。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等

b.带IN关键字的子查询（范围）

#查询年龄大于等于25岁的部门

 mysql> select dep_num,dep_name from department where dep_num in (select distinct dep_num from info where age >=25);

c.带EXISTS关键字的子查询（返回值）

简介：EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。Ture或False，当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
示例：

#如果部门101存在（返回为真），查询所有员工信息。
#true

mysql> select * from info where exists (select * from department where dep_num=102);   #有结果

#false

mysql> select * from info where exists (select * from department where dep_num=105);   #无结果