mysql 记录所有操作_mysql记录操作

最新推荐文章于 2024-01-03 20:38:45 发布

伊噜咔

最新推荐文章于 2024-01-03 20:38:45 发布

阅读量470

点赞数

文章标签： mysql 记录所有操作

本文链接：https://blog.csdn.net/weixin_36139240/article/details/113223459

版权

一介绍

MySQL数据操作： DML

========================================================

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

使用INSERT实现数据的插入

UPDATE实现数据的更新

使用DELETE实现数据的删除

使用SELECT查询数据以及。

*========================================================*

二插入数据INSERT

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

4. 插入查询结果

语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)

SELECT (字段1,字段2,字段3…字段n) FROM 表2

WHERE …;

三更新数据UPDATE

语法：

UPDATE 表名 SET

字段1=值1,

字段2=值2,

WHERE CONDITION;

示例：

UPDATE mysql.user SET password=password(‘123’)

where user=’root’ and host=’localhost’;

四删除数据DELETE

语法：

DELETE FROM 表名

WHERE CONITION;

示例：

DELETE FROM mysql.user

WHERE password=’’;

练习：

更新MySQL root用户密码为mysql123

删除除从本地登录的root用户以外的所有用户

五查询数据SELECT

1.单表查询的语法

SELECT 字段1,字段2... FROM 表名

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数

2.关键字的执行优先级(重点)

1.找到表：from

2.拿到where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体做为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序:order by

8.限制结果的显示条数

3.简单查询

company.employee

员工id id int

姓名 emp_name varchar

性别 sex enum

年龄 age int

岗位 post varchar

薪水 salary double

create table emp(

id int primary key auto_increment,

name varchar(10) not null,

sex enum('male','female') not null default 'male',

age int(3) unsigned not null,

hire_date date not null,

post varchar(10),

salary int

);

#查看表的结构

mysql> desc emp;

+--------+----------------------+------+-----+---------+----------------+

+--------+----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | enum('male','femle') | NO | | male | |

| age | int(3) unsigned | NO | | NULL | |

| salary | int(11) | YES | | NULL | |

+--------+----------------------+------+-----+---------+----------------+

#插入记录

insert emp(name,sex,age,hire_date,post,salary) values

('alex','male',78,'20150302','teacher',1000000),

('wupeiqi','male',81,'20130305','teacher',8300),

('yuanhao','male',73,'20140701','teacher',3500),

('liwenzhou','male',28,'20121101','teacher',2100),

('yy','female',48,'20150311','sale',3000),

('dd','female',38,'20101101','sale',2000),

('mm','female',18,'20110312','sale',1000),

('arther','male',28,'20160311','operation',10000),

('egon','male',18,'19970312','operation',20000),

('tank','female',18,'20130311','operation',19000),

('gg','male',18,'20150411','operation',18000);

#简单查询

=====查看全部=====

select * from emp;

=====查看指定字段下的记录=====

select name,sarlary from emp;

#避免重复distinct

select distinct post from emp;

#通过四则运算查询

select name,salary*12 from emp;

select name,salary*12 as year_salary from emp;

为新的记录取字段名，不然会默认salary*12为字段

#定义显示格式

concat() 函数用于连接字符串

select concat()

select concat('nick_name: ',name,' year_salary: ', salary*12) as annual_salary

from emp;

+-------------------------------------------+

| annual_salary |

+-------------------------------------------+

| nick_name: alex year_salary: 12000000 |

| nick_name: wupeiqi year_salary: 99600 |

| nick_name: yuanhao year_salary: 42000 |

| nick_name: liwenzhou year_salary: 25200 |

| nick_name: yy year_salary: 36000 |

| nick_name: dd year_salary: 24000 |

| nick_name: mm year_salary: 12000 |

| nick_name: arther year_salary: 120000 |

| nick_name: egon year_salary: 240000 |

| nick_name: tank year_salary: 228000 |

| nick_name: gg year_salary: 216000 |

+-------------------------------------------+

合并字符串并且成为新的记录在annual_salary字段下

concat_ws() 第一个参数为分隔符

select concat_ws(':',name,salary*12) as year_sarlary from emp;

+-----------------+

| year_sarlary |

+-----------------+

| alex:12000000 |

| wupeiqi:99600 |

| yuanhao:42000 |

| liwenzhou:25200 |

| yy:36000 |

| dd:24000 |

| mm:12000 |

| arther:120000 |

| egon:240000 |

| tank:228000 |

| gg:216000 |

+-----------------+

结合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;

+--------------+

| new_name |

+--------------+

| alex_bigsb |

| wupeiqi_sb |

| yuanhao_sb |

| liwenzhou_sb |

| yy_sb |

| dd_sb |

| mm_sb |

| arther_sb |

| egon |

| tank_sb |

| gg_sb |

+--------------+

小练习：

1.查出所有员工的名字，薪资，格式为

select concat('') as new_name from emp;

select concat('') as new_salary from emp;

2.查出所有的岗位(去重复)

select distinct post from emp;

3.查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

select concat_ws(':',name,12*salary) as annual_year from emp;

4.where约束

where字句中可以使用：

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

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

3.in(80,90,100)值是80或90或100

4.like'egon%'

pattern可以是%或_,

%表示任意多字符

_表示一个字符

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

#1.单条件查询

select name from emp where post='sale';

#2.多条件查询

select name,salary from emp where post='teacher' and salary>10000;

#3.关键字between and

select name,salary from emp where salary between 10000 and 20000;

#4.关键字is null(判断某个字段是否为null不能用等号，需要用is)

select name,post from emp where post is null;

select name,post from emp where post is not null;

#5.关键字in 集合查询

select name,salary from emp where salary in (3000,3500,4000,9000);

select concat_ws(':',name,salary) as t1 from emp where salary in (3000,3500,4000,9000);

#6.关键字like模糊查询

通配符'%',表示后面的多个字符

select * from emp where name like'eg%';

通配符'_'，表示后面的一个字符

select * from emp where name like'ego_';

正则：

select * from emp where name regexp'^ale';

确定头部，通过尾部模糊查找

select * from emp where name regexp'her$';

确定尾部，通过头部模糊查找

select * from emp where name regexp'm{2}';

5.分组查询：group by

5.1什么是分组？为什么分组？

#1.首先明确一点：分组发生在where之后，即分组是基于where之后的到的记录而进行的

#2.分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按性别分组

#3.为何要分组呢？

取每个部门的最高工资(没有分组取得就是所有部门的最高工资)

取每个部门的员工数

取男人数女人数

小窍门：'每'这个字后面的字段就是我们分组的依据

#4.大前提：

可以按照任意字段分组，但是分组完毕后，看不到组内信息，只能查看到字段，

但该字段相当于接口，我们借助聚合函数操作此接口即可得到信息

应用：

select * from emp group by post; #报错

ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY

select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数

单独使用group by 关键字分组

select post from emp group by post;

注意：我们按照Post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。(如果select * from emp group by post;成立，那么查看的是所有组的信息，那么分组就没有他的意义了)，简而言之需要对每个字段下的信息进行个性化的操作，就有其意义。

如：

取出每个部门下的最高薪资，那么有了其分组的意义。

select post, max(salary) from emp group by post;

+-----------+-------------+

| post | max(salary) |

+-----------+-------------+

| operation | 20000 |

| sale | 3000 |

| teacher | 1000000 |

+-----------+-------------+

group by 关键字和group_concat()一起使用

查看每个部门员工的名字

select post,group_concat(name) from emp group by post;

select post,group_concat(name) as emp_member from emp group by post;

由于只能查询到的字段是post，当需要显示字段name下的信息时，需要用到group_concat()

group by 与聚合函数一起使用

select post,count(id) as count from emp group by post;

查看每个组内有多少人(一个id号对应一个人，且不会重复)

强调:

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义，

多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

在单表操作时select 的字段会受到group by 字段的影响，只能select该字段以及聚合函数的字段，它的原理是比如当以publish_id分组，来封装每个出版社的图书以及每本书的属性，但是select字段的话由于只能取字段下的唯一记录，此时只有publish_id以及聚合函数出来的结果是唯一，而其他字段下可能会存在多条记录那么就不能取出。

多表操作同理，但是根据publish_id建立的连表，然后以publish_id分组后，对应的publish表每个字段下的记录是唯一由于是用其主键分组，所以publish表的其他字段的记录能取出，而被分组的基表book表的字段不能取出。

例子

# book表 # publish表

id name publish_id id name addr

1诛仙 11 北京出版社北京

2 Python 12 东京出版社东京

3 霸王 2

4 hello 2

# 单表根据publish_id分组

id name publish_id

1、2 诛仙、Python 1

3、4 霸王、hello 2

# select取的字段由于严格模式只能取当下字段下唯一的记录，所以只能取publish_id以及聚合函数结果

# 多表根据publish_id进行连表以及分组

id name publish_id id name addr

1、2 诛仙、Python 1 1 北京出版社北京

3、4 霸王、hello 2 2 东京出版社东京

# 此时由于根据publish_id分组，publish_id唯一那么关联的publish表中的相关字段唯一

# 可以取出publish表中所有字段以及聚合函数结果

5.2聚合函数

#强调：聚合函数聚合的组的内容，若是没有分组，则默认一组

select count(*) from emp;

select count(*) from emp where id=1;

select max(salary) from emp;

select min(salary) from emp;

select avg(salary) from emp;

select sum(salary) from emp;

select sum(salary) from emp where id=3;

#在指定id情况下salary唯一，此聚合函数没有意义

6.having过滤

having与where的区别：

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

1.where发生在分组group之前，因而where中可以有任意字段，但是绝对不能使用聚合函数

2.having发生在分组group by之后，因为having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数

ps:分组group by之后才可以使用聚合函数，如果where中使用聚合函数，代表已经分完组了，那么将完全违背其执行的优先级,同理分完组之后having只能使用聚合函数不是其他字段。

例子：

mysql> select * from emp where salary>99999;

+----+------+------+-----+------------+---------+---------+

| id | name | sex | age | hire_date | post | salary |

+----+------+------+-----+------------+---------+---------+

| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |

+----+------+------+-----+------------+---------+---------+

mysql> select * from emp having salary>99999;

+----+------+------+-----+------------+---------+---------+

| id | name | sex | age | hire_date | post | salary |

+----+------+------+-----+------------+---------+---------+

| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |

+----+------+------+-----+------------+---------+---------+

1 row in set (0.00 sec)

#没有指定分组，默认了group by 的是总表

mysql> select post from emp group by post having salary>99999;

ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

#指定分组之后

========================================

mysql> select post,max(salary) from emp group by post having salary>99999;

ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

mysql> select post,max(salary) from emp group by post having max(salary)>99999;

+---------+-------------+

| post | max(salary) |

+---------+-------------+

| teacher | 1000000 |

+---------+-------------+

显示每个部门最高薪资大于99999的薪资

练习：

1.查询男生平均薪资大于3000的职位

select post,avg(salary) from emp where sex='male' group by post having avg(salary)>3000;

+-----------+-------------+

| post | avg(salary) |

+-----------+-------------+

| operation | 16000.0000 |

| teacher | 253475.0000 |

+-----------+-------------+

2.查询各岗位包含的员工个数大于3的岗位名(岗位内包含员工名字、个数)

select post,count(id),group_concat(name) from emp group by post having count(id)>3;

+-----------+-----------+--------------------------------+

| post | count(id) | group_concat(name) |

+-----------+-----------+--------------------------------+

| operation | 4 | arther,egon,tank,gg |

| teacher | 4 | alex,wupeiqi,yuanhao,liwenzhou |

+-----------+-----------+--------------------------------+

3.查询各岗位平均薪资大于10000的岗位、平均工资

select post , avg(salary) from emp group by post having avg(salary)>10000;

+-----------+-------------+

| post | avg(salary) |

+-----------+-------------+

| operation | 16750.0000 |

| teacher | 253475.0000 |

+-----------+-------------+

4.查询各岗位平均薪资大于10000且小于10000的岗位名、平均工资

select post , avg(salary) from emp group by post having avg(salary) between 10000 and 20000;

+-----------+-------------+

| post | avg(salary) |

+-----------+-------------+

| operation | 16750.0000 |

+-----------+-------------+

7.查询排序：order by

按单列排序

select * from emp order by salary;

select * from emp order by salary asc;

select * from emp order by salary desc; #从大到小

按多列排序

select * from emp order by age,salary desc;

#先按照age排序，如果年纪相同，则按照薪资排序

8.限制查询的记录数:limit

示例：

select * from emp order by salary desc limit 3;

+----+-----------+--------+-----+------------+---------+---------+

| id | name | sex | age | hire_date | post | salary |

+----+-----------+--------+-----+------------+---------+---------+

| 1 | alex | male | 78 | 2015-03-02 | teacher | 1000000 |

| 2 | wupeiqi | male | 81 | 2013-03-05 | teacher | 8300 |

| 3 | yuanhao | male | 73 | 2014-07-01 | teacher | 3500 |

| 4 | liwenzhou | male | 28 | 2012-11-01 | teacher | 2100 |

| 5 | yy | female | 48 | 2015-03-11 | sale | 3000 |

+----+-----------+--------+-----+------------+---------+---------+

被限制只能看到三条信息，默认初始位置为0

select * from emp limit 5,5;

从第5条开始，即先查询出第6条，然后包含这一条在内往后差5条

+----+--------+--------+-----+------------+-----------+--------+

| id | name | sex | age | hire_date | post | salary |

+----+--------+--------+-----+------------+-----------+--------+

| 6 | dd | female | 38 | 2010-11-01 | sale | 2000 |

| 7 | mm | female | 18 | 2011-03-12 | sale | 1000 |

| 8 | arther | male | 28 | 2016-03-11 | operation | 10000 |

| 9 | egon | male | 18 | 1997-03-12 | operation | 20000 |

| 10 | tank | female | 18 | 2013-03-11 | operation | 19000 |

+----+--------+--------+-----+------------+-----------+--------+

六权限管理

#授权表

user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段

db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段

tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段

columns_priv #该表放行的权限，针对：某一个字段

#按图解释：

user：放行db1，db2及其包含的所有

db：放行db1，及其db1包含的所有

tables_priv:放行db1.table1，及其该表包含的所有

columns_prive:放行db1.table1.column1，只放行该字段

伊噜咔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 记录所有操作_mysql记录操作

一介绍MySQL数据操作： DML========================================================在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据的插入UPDATE实现数据的更新使用DELETE实现数据的删除使用SELECT查询数据以及。*=============================...
复制链接

扫一扫