开源数据库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); #无结果