mysql-数据（记录）相关操作（增删改查）及权限管理-白红宇

一、介绍

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

使用INSERT实现数据的插入

UPDATE实现数据的更新

使用DELETE实现数据的删除

使用SELECT查询数据以及。

二、插入数据

1. 插入完整数据（顺序插入）    语法一：    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);    语法二：    INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据    语法：    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录    语法：    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);        4. 插入查询结果    语法：    INSERT INTO 表名(字段1,字段2,字段3…字段n)                     SELECT (字段1,字段2,字段3…字段n) FROM 表2                    WHERE …;

三、更新数据

语法：    UPDATE 表名 SET        字段1=值1,        字段2=值2,        WHERE CONDITION;

四、删除数据

语法：    DELETE FROM 表名         WHERE CONITION;

五、查询数据

单表查询语法：    SELECT 字段1,字段2... FROM 表名                  WHERE 条件                  GROUP BY field                  HAVING 筛选                  ORDER BY field                  LIMIT 限制条数多表查询语法：    SELECT 字段列表    FROM 表1 INNER|LEFT|RIGHT JOIN 表2    ON 表1.字段 = 表2.字段;

select关键字的定义顺序：

SELECT DISTINCT 
     
      FROM 
      
       
         JOIN 
        
         ON 
         
          WHERE 
          
           GROUP BY 
           
            HAVING 
            
             ORDER BY 
             
              LIMIT

select关键字的执行顺序：

(7)     SELECT (8)     DISTINCT 
     
      (1)     FROM 
      
       (3)     
       
         JOIN 
        
         (2)     ON 
         
          (4)     WHERE 
          
           (5) GROUP BY 
           
            (6) HAVING 
            
             (9) ORDER BY 
             
              (10) LIMIT

（一）、单表查询

company.employee    员工id      id                  int                 姓名        emp_name            varchar    性别        sex                 enum    年龄        age                 int    入职日期     hire_date           date    岗位        post                varchar    职位描述     post_comment        varchar    薪水        salary              double    办公室       office              int    部门编号     depart_id           int#创建表create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#插入记录#三个部门：教学，销售，运营insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

表数据

简单查询：

#简单查询    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id     FROM employee;    SELECT * FROM employee;    SELECT name,salary FROM employee;#避免重复DISTINCT    SELECT DISTINCT post FROM employee;    #通过四则运算查询    SELECT name, salary*12 FROM employee;    SELECT name, salary*12 AS Annual_salary FROM employee;    SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式   CONCAT() 函数用于连接字符串   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary    FROM employee;      CONCAT_WS() 第一个参数为分隔符   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary    FROM employee;   结合CASE语句：   SELECT       (           CASE           WHEN NAME = 'egon' THEN               NAME           WHEN NAME = 'alex' THEN               CONCAT(name,'_BIGSB')           ELSE               concat(NAME, 'SB')           END       ) as new_name   FROM       emp;

1.where约束：

where字句中可以使用：

　　1. 比较运算符：> < >= <= <> !=

　　2. between 80 and 100 值在10到20之间

　　3. in(10,20,30) 值是10或20或30

　　4. like 'abc%'

　　 %表示任意多字符

　　 _表示一个字符

　　5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 not and or

#1:单条件查询    SELECT name FROM employee        WHERE post='sale';        #2:多条件查询    SELECT name,salary FROM employee        WHERE post='teacher' AND salary>10000;#3:关键字BETWEEN AND    SELECT name,salary FROM employee         WHERE salary BETWEEN 10000 AND 20000;    SELECT name,salary FROM employee         WHERE salary NOT BETWEEN 10000 AND 20000;    #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)    SELECT name,post_comment FROM employee         WHERE post_comment IS NULL;    SELECT name,post_comment FROM employee         WHERE post_comment IS NOT NULL;            SELECT name,post_comment FROM employee         WHERE post_comment=''; 注意''是空字符串，不是null    ps：        执行        update employee set post_comment='' where id=2;        再用上条查看，就会有结果了#5:关键字IN集合查询    SELECT name,salary FROM employee         WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;        SELECT name,salary FROM employee         WHERE salary IN (3000,3500,4000,9000) ;    SELECT name,salary FROM employee         WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询    通配符’%’    SELECT * FROM employee             WHERE name LIKE 'eg%';    通配符’_’    SELECT * FROM employee             WHERE name LIKE 'al__';

2.正则表达式查询：

SELECT * FROM employee WHERE name REGEXP 'on$';除了模糊查询 还可以使用正则表达式查询小结：对字符串匹配的方式WHERE name = 'onclose';WHERE name LIKE 'on%';WHERE name REGEXP 'on$';

3.group by 分组：

#查看MySQL 5.7默认的sql_mode如下：mysql> select @@global.sql_mode;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#！！！注意ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

语法：

单独使用GROUP BY关键字分组    SELECT post FROM employee GROUP BY post;    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUP BY关键字和GROUP_CONCAT()函数一起使用    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY与聚合函数一起使用    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

#如果没有设置ONLY_FULL_GROUP_BY,也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的mysql> set global sql_mode='ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)mysql> quit #设置成功后，一定要退出，然后重新登录方可生效Byemysql> use db1;Database changedmysql> select * from emp group by post; #报错ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BYmysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数

4.聚合函数：

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组示例：    SELECT COUNT(*) FROM employee;    SELECT COUNT(*) FROM employee WHERE depart_id=1;    SELECT MAX(salary) FROM employee;    SELECT MIN(salary) FROM employee;    SELECT AVG(salary) FROM employee;    SELECT SUM(salary) FROM employee;    SELECT SUM(salary) FROM employee WHERE depart_id=3;

5.having过滤：

“Where” 是一个约束声明，使用Where来约束来自数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

执行优先级从高到低：where > group by > having

注：用having就一定要用group by，用group by不一定要有having。只要条件里面的字段, 不是表里面原先有的字段就需要用having。

6.order by 排序：

按单列排序    SELECT * FROM employee ORDER BY salary;#默认ASC升序    SELECT * FROM employee ORDER BY salary ASC;    SELECT * FROM employee ORDER BY salary DESC;#降序按多列排序:先按照age排序，如果年纪相同，则按照薪资排序    SELECT * from employee        ORDER BY age,        salary DESC;

7.limit 限制查询记录数：

示例：    SELECT * FROM employee ORDER BY salary DESC         LIMIT 3;                    #默认初始位置为0 ，查询3条数据        SELECT * FROM employee ORDER BY salary DESC        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条    SELECT * FROM employee ORDER BY salary DESC        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

（二）、多表查询

SELECT 字段列表    FROM 表1 INNER|LEFT|RIGHT JOIN 表2    ON 表1.字段 = 表2.字段;

1.交叉连接（笛卡尔积）：

交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合，结果集的行数是两个表的行数的乘积。（效率最低）

mysql> select * from employee,department;+----+------------+--------+------+--------+------+--------------+| id | name       | sex    | age  | dep_id | id   | name         |+----+------------+--------+------+--------+------+--------------+|  1 | A       | male   |   18 |    200 |  200 | 技术         ||  1 | A       | male   |   18 |    200 |  201 | 人力资源     ||  1 | A       | male   |   18 |    200 |  202 | 销售         ||  1 | A       | male   |   18 |    200 |  203 | 运营         ||  2 | B       | female |   48 |    201 |  200 | 技术         ||  2 | B       | female |   48 |    201 |  201 | 人力资源     ||  2 | B       | female |   48 |    201 |  202 | 销售         ||  2 | B       | female |   48 |    201 |  203 | 运营         ||  3 | C       | male   |   38 |    201 |  200 | 技术         ||  3 | C       | male   |   38 |    201 |  201 | 人力资源     ||  3 | C       | male   |   38 |    201 |  202 | 销售         ||  3 | C       | male   |   38 |    201 |  203 | 运营         |+----+------------+--------+------+--------+------+--------------+

2.内连接：只连接匹配的行

INNER JOIN（内连接），也成为自然连接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果，内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+| id | name      | age  | sex    | name         |+----+-----------+------+--------+--------------+|  1 | A     |   18 | male   | 技术         ||  2 | B     |   48 | female | 人力资源     ||  3 | C  |   38 | male   | 人力资源     |+----+-----------+------+--------+--------------+#上述sql等同于mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

3.外连接之左连接：

返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

4.外连接之右连接：

与左连接正好相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

5.全外连接：显示左右两个表全部记录

在内连接的基础上增加左边有右边没有的，右边有左边没有的。是左外连接和右外连接的并集。

注：mysql不支持全外连接full join 但是可以用下面的方式实现（union与union all的区别：union会去掉相同的纪录）

select * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id;

（三）、子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、

1.带in关键字的子查询

#查询平均年龄在25岁以上的部门名select id,name from department    where id in         (select dep_id from employee group by dep_id having avg(age) > 25);#查看技术部员工姓名select name from employee    where dep_id in         (select id from department where name='技术');#查看不足1人的部门名(子查询得到的是有人的部门id)select name from department where id not in (select distinct dep_id from employee);

2.带比较运算符的子查询

#查询大于部门内平均年龄的员工名、年龄select t1.name,t1.age from emp t1inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2on t1.dep_id = t2.dep_idwhere t1.age > t2.avg_age;

3.带exists关键字的子查询

#department表中存在dept_id=203，Turemysql> select * from employee    ->     where exists    ->         (select id from department where id=200);#department表中存在dept_id=205，Falsemysql> select * from employee    ->     where exists    ->         (select id from department where id=204);Empty set (0.00 sec)

六、权限管理

#授权表user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段columns_priv #该表放行的权限，针对：某一个字段 #查看自己的权限 show grants;#创建用户create user  用户名@"主机地址" identified by "密码";create user 'A'@'1127.0.0.1' identified by '123';create user 'B'@'192.168.1.%' identified by '123';#指定ipcreate user 'C'@'%' identified by '123';#任意用户#授权：对文件夹，对文件，对文件某一字段的权限查看帮助：help grant常用权限有：select,update,alter,deleteall可以代表除了grant之外的所有权限语法: grant [权限的名称 select insert.... | all ] on 数据库.表名  to 用户名@主机地址;特点: 如果授权时，用户不存在则直接自动创建用户#针对所有库的授权:*.*grant select on *.* to 'A'@'localhost' identified by '123'; #只在user表中可以查到A用户的select权限被设置为Y 没有 Grant_priv（授权）的权限#针对某一数据库：db1.*grant select on db1.* to 'B'@'%' identified by '123'; #只在db表中可以查到B用户的select权限被设置为Y#针对某一个表：db1.t1grant select on db1.t1 to 'C'@'%' identified by '123';  #只在tables_priv表中可以查到C用户的select权限#针对某一个字段：grant select (id,name),update (age) on db1.t3 to 'D'@'localhost' identified by '123'; #可以在tables_priv和columns_priv中看到相应的权限#删除权限revoke 权限的名称 on 数据库.表名  from 用户名@"主机名" ;revoke select on db1.* from 'A'@'%';#删除用户drop user 用户名@"主机地址";

注：添加权限之后，要记得刷新权限

flush privileges;

如果要让用户拥有所有权限，可以执行下面的命令：

grant [权限的名称 select insert.... | all ] on 数据库.表名  to 用户名@主机地址 with grant option;        with grant option 这个用户可以将他有的权限授予别的账户

焚膏油以继晷，恒兀兀以穷年。