当前位置：Gxlcms > 数据库问题 > MySQL单表的CRUD及多表查询

MySQL单表的CRUD及多表查询

时间：2021-07-01 10:21:17 帮助过：18人阅读

　　增、删、改

　　查：

　　　　单表查询

　　　　　　简单查询、where约束、group by分组、聚合查询、having过滤、order by排序、limit限制、正则匹配

　　　　多表查询

　　　　　　连表查询：交叉查询、>內连查询、左外连接查询、右外连接查询、全外链接查询、连接结果筛选查询　　　　　　　　

　　　　　　子查询　：带关键字in的子查询、带比较运算符的子查询、带关键字exists的布尔判断结果查询　

表记录增删改总结：

MySQL数据操作： DML

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

使用INSERT实现数据的插入

UPDATE实现数据的更新

使用DELETE实现数据的删除

使用SELECT查询数据以及。　　　

#增insert into：
    （1）单条插入
        insert into 表名 value(值1,值2,...);
    （2）多条插入
        insert into 表名 values(值1,值2,...),(值1,值2,...),...;
    （3）指定字段插入
        insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);
    （4）查询结果插入
        insert into 表名(字段1,字段2,字段3,...) select (字段1,字段2,字段3,...) from 表名1;
#删delete from：
    （1）清空列表
        delete from 表名;
    （2）删除某条记录
        delete from 表名 where 条件;
#改update set：
    （1）更新数据
        update 表名 set 字段=值 where 条件;

mysql表记录的增删改操作

增：　　

（1）单条插入
        insert into 表名 value(值1,值2,...);
（2）多条插入
        insert into 表名 values(值1,值2,...),(值1,值2,...),...;
（3）指定字段插入
        insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);
（4）查询结果插入
        insetr into 表名(字段1,字段2,字段3,...) select (字段1,字段2,字段3,...) from 表名1;

插入数据insert into

删：　　

删delete from：
    （1）清空列表
        delete from 表名;
    （2）删除某条记录
        delete from 表名 where 条件;

删除数据delete

改：　　

改update set：
    （1）更新数据
        update 表名 set 字段=值 where 条件;
语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

更新数据update

单表查： 　　

查select from：
    （1）单表查询语法：
        select distinct 字段1，字段2... from  表名
                                                    where 条件
                                                    group by 字段
                                                    having  条件
                                                    order by 字段[asc/desc]
                                                    limit n/m,n

    （2）执行优先级：from>where>group by>select>having>order by>limit
        1.找到表:from
        2.拿着where指定的约束条件，去文件/表中取出一条条记录
        3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
        4.执行select（去重）
        5.将分组的结果进行having过滤
        6.将结果按条件排序：order by
        7.限制结果的显示条数limit

    （3）简单查询：
        1.重命名查询as:
            select * from 表名
            select 字段 as 自定义字段名 from 表名
        2.去重查询distinct：
            select distinct 字段 from 表名
        3.四则运算查询+-*/%等：
            select 字段*n from 表名;
        4.定义格式查询concat：
            select concat(‘字符串‘,字段1,‘字符串‘,字段2...) from 表名;
            select concat_ws(‘连接字符串‘,字段1，字段2 ,...) from 表名;


    (4)where约束条件：
        1.比较运算：>,<,=,>=,<=,!=,<>
        2.between m and n  m到n之间的值（包括m、n）
        3.in（a,b,c...）   在a,b,c...中的值
        4.逻辑运算：and、or 、not
        5.模糊条件 like ‘%‘/‘_‘      通配符：%表示任意个字符；  _下划线表示一个字符

    （5）group by 分组：
        分组查询获取其他字段：
            GROUP BY关键字和GROUP_CONCAT()函数一起使用
    （6）having过滤：
        Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
    （7）order by排序：
        asc 顺序  desc 逆序
    （8）limit限制条数
        limit n #默认初始位置为0 取n个
        limit m,n #从m开始，往后取n个

    （9）聚合函数：
        聚合函数聚合的是组的内容，若是没有分组，则默认一组。
        count()        max()        min()        avg()        sum()

查询语法、聚合函数及关键字优先级

查select from：
    （1）单表查询语法：
        select distinct 字段1，字段2... from  表名
                                                    where 条件
                                                    group by 字段
                                                    having  条件
                                                    order by 字段[asc/desc]
                                                    limit n/m,n

    （2）执行优先级：from>where>group by>select>having>order by>limit
        1.找到表:from
        2.拿着where指定的约束条件，去文件/表中取出一条条记录
        3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
        4.执行select（去重）
        5.将分组的结果进行having过滤
        6.将结果按条件排序：order by
        7.限制结果的显示条数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,
emp_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
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum(‘male‘,‘female‘) | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_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,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(‘姓名: ‘,emp_name,‘  年薪: ‘, salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(‘:‘,emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = ‘jingliyang‘ THEN
               emp_name
           WHEN emp_name = ‘alex‘ THEN
               CONCAT(emp_name,‘_BIGSB‘)
           ELSE
               concat(emp_name, ‘SB‘)
           END
       ) as new_name
   FROM
       employee;

简单查询详解

# 1 查出所有员工的名字，薪资,格式为
#     <名字:egon>    <薪资:3000>
# 2 查出所有的岗位（去掉重复）
# 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
‘‘‘
# select concat(‘<名字：‘,emp_name,‘>‘,‘<薪资：‘,salary,‘>‘) from employee;
# select concat(‘<名字：‘,emp_name,‘>‘),concat(‘<薪资：‘,salary,‘>‘) from employee;
# select distinct post from employee;
# select emp_name,salary*12 as annual_year from employee;
‘‘‘

简单查询示例

　 where约束：　

where字句中可以使用：

　　1. 比较运算符：> < >= <= <> !=
　　2. between 80 and 100 值在80到100之间
　　3. in(80,90,100) 值是80或90或100
　　4. like ‘e%‘
　　通配符可以是%或_，
　　 %表示任意多字符
　　 _表示一个字符
　　5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

#1:单条件查询
    SELECT emp_name FROM employee
        WHERE post=‘sale‘;
        
#2:多条件查询
    SELECT emp_name,salary FROM employee
        WHERE post=‘teacher‘ AND salary>10000;

#3:关键字BETWEEN AND
    SELECT emp_name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment=‘‘; 注意‘‘是空字符串，不是null
    ps：
        执行
        update employee set post_comment=‘‘ where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT emp_name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT emp_name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE emp_name LIKE ‘eg%‘;

    通配符’_’
    SELECT * FROM employee 
            WHERE emp_name LIKE ‘al__‘;

where条件查询详解技术图片

# 1. 查看岗位是teacher的员工姓名、年龄
# 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
# 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
# 4. 查看岗位描述不为NULL的员工信息
# 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
# 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
# 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
‘‘‘
# select emp_name,age from employee where post=‘teacher‘;
# select emp_name,age from employee where post=‘teacher‘ and age>30;
# select emp_name,age,salary from employee where post=‘teacher‘ and salary between 1000 and 9000;
# select *from employee where post_comment is not null;
# select emp_name,age,salary from employee where post=‘teacher‘ and salary in(10000,9000,30000);
# select emp_name,age,salary from employee where post=‘teacher‘ and salary  not in (10000,9000,30000);
# select emp_name,salary*12 as annul_salary from employee where post=‘teacher‘ and emp_name like ‘jin%‘;
‘‘‘

where查询示例

　　group by 分组：　　

单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询

                  

	 	
                    
                        
                            
                                < 上一篇
Mysql优化（一）
                            
                            
                                 下一篇 >
WinDbg扩展

人气教程排行

229次 1 oracle 用户解锁和修改用户密码

229次 2 解决：安装SQL Server 2008 Native Client遇到错误（在Navicat premium新建sqlserver连接时需要）：An error occurred during ...HRESULT: 0x80070422（注意尾部的错误号）

229次 3 rocksdb 编译步骤

229次 4 adb命令开关蓝牙及NFC

229次 5 解决数据库连接错误您在wp-config.php文件中提供的数据库用户名和密码可能不正确，或者无法连接到localhost上的数据库服务器，这意味着您的主机数据库服务器已停止工作。

229次 6 使用java自动填充，实现mysql的创建修改时间的自动填充

229次 7 Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb

228次 8 关于mysql创建数据库中字符集和排序规则的选择

228次 9 Linux设置Mysql开机自启动服务

227次 10 SQL2008：WITH MOVE 子句可用于重新定位一个或多个文件

227次 11 图数据库Neo4j在GIS系统的应用

227次 12 SpringBoot配置 druid 数据源配置慢SQL记录

227次 13 SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

227次 14 docker-compose启动MySQL并配置远程登录

225次 15 Mysql安装（for mac）

225次 16 kettle将图片转换至二进制存储至数据库

225次 17 adb的安装及配置

224次 18 使用PHP控制MODBUS-RTU设备

224次 19 MySQL 查询时间差值大于某一个值的记录

224次 20 MongoDB内存配置 --wiredTigerCacheSizeGB