二十一、Mysql数据库运维实战--SQL2(增删改查)

一、DML

        目的     

           在MySQL管理软件中，DDL已经定义了数据库结构。

             那么如何对其中的数据进行管理呢？

        可以通过SQL语句中的DML语言来实现数据的操作，包括使用

        INSERT    实现数据的    插入

        DELETE    实现数据的    删除

        UPDATE    实现数据的    更新。

        1、插入数据INSERT

                完整插入       

                  INSERT  INTO  表名     VALUES (值1,值2,值3…值n);

                部分插入

                 INSERT INTO 表名（列名，列名）  VALUES (值1,值2);

        2、更新数据UPDATE

                语法    UPDATE   表名   SET   列名=值   WHERE   CONDITION;

                示例1

                        准备一张表

                         create table t6(id int, name varchar(20));

                         insert into t6 values (1,'aa');

                        insert into t6 values (2,'bb');

                        更新数据

                        需求：把bb改成cc

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

                        查询结果

                         select * from t6;

                示例2

                        修改mysql数据库管理员root账户的密码。

                        update mysql.user set authentication_string=password("123456") where user="root";

          3、删除数据DELETE

                      语法   DELETE   FROM    表名   WHERE   CONDITION;

                        示例：需求：删除id为2 的用户记录。

                        mysql> delete from t6 where id=2;

二、DQL

        1.目的

                在MySQL管理软件中，可以通过SQL语句中的DQL语言来实现数据的

                SELECT 查询操作

                互联网用户查询余额，查询装备，查询商品的操作。

        2.MySQL查询

                一、准备环境

                        素材1

                                准备一张表，包含

                                三列信息

                                        id  int  序号        

                                        name  varchar  姓名

                                        age int  年龄

                              示例   create   table   t3   (id   int,name   varchar(20),age  int);

                再插入测试数据

                        insert   into   t3   values  (1,"zhangsan",23);

                        insert   into   t3   values  (2,"lisi",24);

                        insert   into   t3   values  (3,"wangwu",18);

                        素材2

                        

                        结构语句

                        create database company;

                         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
                                     ); 

                        查看表结构

                         desc employee5;

                        
 

                 ​​​​​​​        插入数据

                                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);

         二、简单查询

                简单查询

                查看所有列         SELECT    *    FROM   表名;        前提是需要进入数据库。

                查部分列            SELECT   列1，列2，列3    FROM     表名;

                通过四则运算查询

                    SELECT name, salary, salary*14 FROM employee5;

                

         三、条件查询

                单条件查询where

                        查询hr部门的员工姓名        

                        SELECT  name,post  FROM employee5 WHERE post='hr';

                 多条件查询AND/OR

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

                       SELECT name,salary  FROM employee5  WHERE post='hr' AND salary>1000;

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

                        SELECT name, salary FROM employee5   WHERE salary=6000 OR salary=8000

               关键字BETWEEN AND 在什么之间

                        需求：查一查薪资在5000到15000

                        SELECT name,salary FROM employee5  WHERE salary BETWEEN 5000 AND 15000;

                        需求：不在5000~15000呢？请使用NOT

                        SELECT name,salary FROM employee5  WHERE salary NOT BETWEEN 5000 AND 15000;

                关键字IN集合查询

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

                OR可以组合多条件，效率如何？
        SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;

                优化

                是什么什么
                SELECT name, salary FROM employee5  WHERE salary IN (4000,5000,6000,9000) ;

                不是什么什么
                SELECT name, salary FROM employee WHERE salary NOT IN (4000,5000,6000,9000) ;

        

                关键字IS NULL

                   没有岗位描述的

                        空
SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

                        非空
SELECT name,job_description FROM employee5  WHERE job_description IS NOT NULL;

                        错误示范，空格''
SELECT name,job_description FROM employee5   WHERE job_description='';

                关键字LIKE模糊查询

                        好像有个员工姓阿        

                        SELECT * FROM employee5   WHERE name LIKE 'al%';

                        通配符’%’代表多个任意字符

                        注意不是shell的"*"星号。mysql使用"%"

                        好像有个员工姓阿  

                        SELECT * FROM employee5 WHERE name LIKE 'al___';

                        注意不是shell的“？”问号。mysql使用"_"下划线

                        通配符’_’代表1个任意字符

        四、查询排序

                        例如以工资升序排列    

                        SELECT    *     FROM     表名   ORDER       BY     工资的列名     ASC;

                        例如以工资降序排列

                        SELECT    *     FROM     表名   ORDER BY    工资的列名     DESC;

                        工资最高的前五名

                        SELECT * FROM employee5 ORDER BY salary DESC  LIMIT  5;    

          

        五、MySQL多表查询

前言        

                        如何根据部门总表中，查询出分表信息

                        

1、分类

 1.多表连接查询        

 复合条件连接查询

 2.子查询

   根据查询结果查询。

准备工作

准备员工信息表

create table info(
name char(50),
age int,
dep_num int,
level_num int);

desc info;

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

select * from info;

准备部门信息表

create table department(
dep_num int,
dep_name varchar(50),
dep_des varchar(100));

insert into department values
(101,'hr','recruit,training'),
(102,'tec','system,network,service'),
(103,'exp','C++,python,php'),
(104,'admin','administrator');

desc department;

select * from department;

二、多表的连接查询

         分类

                交叉连接        生成笛卡尔积，它不使用任何匹配条件

                        特点        全部组合（A表5行，B表7行，最后5*7=35行）

                        语法        生成笛卡尔积，它不使用任何匹配条件        touch {a..c}{1..3}

                        示范        select info.name,info.age,info.dep_num,department.dep_name from info,department;

                        

 

                内连接         只连接匹配的行

                        

 

                外连接     

                        左连接：    会显示左边表内所有的值，不论在右边表内匹不匹配

                        需求        找出所有员工及所属的部门，包括没有部门的员工

                                        查看所有员工的部门信息。

                        示例   select info.name,info.age,info.dep_num,department.dep_name from info left join department on info.dep_num = department.dep_num;

                        

 

 

                        右连接：    会显示右边表内所有的值，不论在左边表内匹不匹配

                        需求1        显示所有的部门的员工信息。

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

                        

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

                        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;

                        

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

                         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;

                         

 三、子查询

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

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

               需求  查询员工年龄大于等于25岁的部门

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

                

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

                简介    

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

            示例

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

                true

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

                false

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