MySQL主要有哪些语法
发表于：2025-11-08 作者：千家信息网编辑
千家信息网最后更新 2025年11月08日，下文我给大家简单讲讲关于MySQL主要有哪些语法，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完MySQL主要有哪些语法对大家多少有点帮助吧。测试数据准备create t
千家信息网最后更新 2025年11月08日MySQL主要有哪些语法
下文我给大家简单讲讲关于MySQL主要有哪些语法，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完MySQL主要有哪些语法对大家多少有点帮助吧。
测试数据准备
create table emp ( empno numeric(4) not null, ename varchar(10), job varchar(9), mgr numeric(4), hiredate datetime, sal numeric(7, 2), comm numeric(7, 2), deptno numeric(2));create table dept ( deptno numeric(2), dname varchar(14), loc varchar(13));create table salgrade ( grade numeric, losal numeric, hisal numeric);insert into dept values (10, 'ACCOUNTING', 'NEW YORK');insert into dept values (20, 'RESEARCH', 'DALLAS');insert into dept values (30, 'SALES', 'CHICAGO');insert into dept values (40, 'OPERATIONS', 'BOSTON');insert into salgrade values (1, 700, 1200);insert into salgrade values (2, 1201, 1400);insert into salgrade values (3, 1401, 2000);insert into salgrade values (4, 2001, 3000);insert into salgrade values (5, 3001, 9999);insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
1.模糊查询
mysql> select * from emp where ename like '%S%'; +-------+-------+---------+------+---------------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 || 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |+-------+-------+---------+------+---------------------+---------+------+--------+5 rows in set (0.00 sec)mysql> select * from emp where ename like 'S%'; +-------+-------+---------+------+---------------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 |+-------+-------+---------+------+---------------------+---------+------+--------+2 rows in set (0.01 sec)mysql> select * from emp where ename like '%S';+-------+-------+---------+------+---------------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |+-------+-------+---------+------+---------------------+---------+------+--------+3 rows in set (0.00 sec)mysql> select * from emp where ename like '_O%';+-------+-------+---------+------+---------------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |+-------+-------+---------+------+---------------------+---------+------+--------+2 rows in set (0.00 sec)总结：%表示任意0个或多个字符，可匹配任意类型和长度的字符；_表示任意单个字符，匹配单个任意字符。
2.排序
mysql> select * from emp order by sal;+-------+--------+-----------+------+---------------------+---------+---------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 || 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 || 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 || 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 || 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 || 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 || 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)mysql> select * from emp order by sal asc;+-------+--------+-----------+------+---------------------+---------+---------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 || 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 || 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 || 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 || 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 || 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 || 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)mysql> select * from emp order by sal desc;+-------+--------+-----------+------+---------------------+---------+---------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 || 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 || 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 || 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 || 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 || 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 || 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)总结：order by排序默认按asc升序来排列也可指定desc降序排列
3.限制多少行
mysql> select * from emp limit 3;+-------+-------+----------+------+---------------------+---------+--------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+----------+------+---------------------+---------+--------+--------+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 || 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 || 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |+-------+-------+----------+------+---------------------+---------+--------+--------+3 rows in set (0.00 sec)mysql> select * from emp order by sal desc limit 3;+-------+-------+-----------+------+---------------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+-----------+------+---------------------+---------+------+--------+| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 || 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 |+-------+-------+-----------+------+---------------------+---------+------+--------+3 rows in set (0.00 sec)总结：limit限定显示前多少行，可与order by联合使用
4.聚合函数
count() sum()函数用法：#1.各个部门的薪水和mysql> select deptno,sum(sal) from emp group by deptno;+--------+----------+| deptno | sum(sal) |+--------+----------+| 10 | 8750.00 || 20 | 10875.00 || 30 | 9400.00 |+--------+----------+3 rows in set (0.01 sec)#2.group by字段必须出现在select字段后面 各个部门的各个岗位的薪水和mysql> select deptno,job, sum(sal) from emp group by deptno ,job;+--------+-----------+----------+| deptno | job | sum(sal) |+--------+-----------+----------+| 10 | CLERK | 1300.00 || 10 | MANAGER | 2450.00 || 10 | PRESIDENT | 5000.00 || 20 | ANALYST | 6000.00 || 20 | CLERK | 1900.00 || 20 | MANAGER | 2975.00 || 30 | CLERK | 950.00 || 30 | MANAGER | 2850.00 || 30 | SALESMAN | 5600.00 |+--------+-----------+----------+9 rows in set (0.01 sec)#3.having 薪水和>5000的各个部门的各个岗位mysql> select deptno,job, sum(sal) -> from emp -> group by deptno ,job -> having sum(sal)>5000; +--------+----------+----------+| deptno | job | sum(sal) |+--------+----------+----------+| 20 | ANALYST | 6000.00 || 30 | SALESMAN | 5600.00 |+--------+----------+----------+2 rows in set (0.00 sec)#4.常用组合where order limit select deptno,job, sum(sal) as sum_salfrom emp where job='SALESMAN'group by deptno ,jobhaving sum(sal)>5000 order by sum(sal) desc limit 1;
下面介绍下join及union的用法
数据准备：
create table testa(aid int,aname varchar(40));create table testb(bid int,bname varchar(40),age int);insert into testa values(1,'xiaoming');insert into testa values(2,'LY');insert into testa values(3,'KUN');insert into testa values(4,'ZIDONG');insert into testa values(5,'HB');insert into testb values(1,'xiaoming',10);insert into testb values(2,'LY',100);insert into testb values(3,'KUN',200);insert into testb values(4,'ZIDONG',110);insert into testb values(6,'niu',120);insert into testb values(7,'meng',130);insert into testb values(8,'mi',170);
5.left join
mysql> select -> a.aid,a.aname, -> b.bid,b.bname,b.age -> from testa as a -> left join testb as b on a.aid=b.bid; +------+----------+------+----------+------+| aid | aname | bid | bname | age |+------+----------+------+----------+------+| 1 | xiaoming | 1 | xiaoming | 10 || 2 | LY | 2 | LY | 100 || 3 | KUN | 3 | KUN | 200 || 4 | ZIDONG | 4 | ZIDONG | 110 || 5 | HB | NULL | NULL | NULL |+------+----------+------+----------+------+5 rows in set (0.00 sec)总结：a left join b a表全，用b表去匹配a表LEFT JOIN 关键字会从左表 (a) 那里返回所有的行，即使在右表 (b) 中没有匹配的行,匹配不到的列用NULL代替
6.right join
mysql> select -> a.aid,a.aname, -> b.bid,b.bname,b.age -> from testa as a -> right join testb as b on a.aid=b.bid;+------+----------+------+----------+------+| aid | aname | bid | bname | age |+------+----------+------+----------+------+| 1 | xiaoming | 1 | xiaoming | 10 || 2 | LY | 2 | LY | 100 || 3 | KUN | 3 | KUN | 200 || 4 | ZIDONG | 4 | ZIDONG | 110 || NULL | NULL | 6 | niu | 120 || NULL | NULL | 7 | meng | 130 || NULL | NULL | 8 | mi | 170 |+------+----------+------+----------+------+7 rows in set (0.00 sec)总结：a right join b b表全，用a表去匹配b表RIGHT JOIN 关键字会右表 (b) 那里返回所有的行，即使在左表 (a) 中没有匹配的行,匹配不到的列用NULL代替
7.inner join
mysql> select -> a.aid,a.aname, -> b.bid,b.bname,b.age -> from testa as a -> inner join testb as b on a.aid=b.bid; +------+----------+------+----------+------+| aid | aname | bid | bname | age |+------+----------+------+----------+------+| 1 | xiaoming | 1 | xiaoming | 10 || 2 | LY | 2 | LY | 100 || 3 | KUN | 3 | KUN | 200 || 4 | ZIDONG | 4 | ZIDONG | 110 |+------+----------+------+----------+------+4 rows in set (0.00 sec)mysql> select -> a.aid,a.aname, -> b.bid,b.bname,b.age -> from testa as a -> join testb as b on a.aid=b.bid; +------+----------+------+----------+------+| aid | aname | bid | bname | age |+------+----------+------+----------+------+| 1 | xiaoming | 1 | xiaoming | 10 || 2 | LY | 2 | LY | 100 || 3 | KUN | 3 | KUN | 200 || 4 | ZIDONG | 4 | ZIDONG | 110 |+------+----------+------+----------+------+4 rows in set (0.00 sec)总结：inner join 与join 效果一样在表中存在至少一个匹配时，INNER JOIN 关键字返回行
8.union与union all
mysql> select aid,aname from testa -> union -> select bid,bname from testb;+------+----------+| aid | aname |+------+----------+| 1 | xiaoming || 2 | LY || 3 | KUN || 4 | ZIDONG || 5 | HB || 6 | niu || 7 | meng || 8 | mi |+------+----------+8 rows in set (0.01 sec)mysql> select aid,aname from testa -> union all -> select bid,bname from testb;+------+----------+| aid | aname |+------+----------+| 1 | xiaoming || 2 | LY || 3 | KUN || 4 | ZIDONG || 5 | HB || 1 | xiaoming || 2 | LY || 3 | KUN || 4 | ZIDONG || 6 | niu || 7 | meng || 8 | mi |+------+----------+12 rows in set (0.00 sec)总结：
union 会去重 union all不去重
大家觉得MySQL主要有哪些语法这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。
字符
语法
关键
关键字
薪水
部门
函数
单个
字段
岗位
数据
篇文章
准备
排序
重大
下文
主题
兴趣
内容
升序
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
福州app手机软件开发公司
网络安全和谐校园漫画
认证服务器授权服务器
网络安全工具写生素材
服务器跟电脑的处理器
wr703n 打印机服务器
远程服务器串口怎么连接
网络安全设计应用
网络安全法对高校的要求
生家庭教师局网络安全
网络安全培训制度
小米路由vpn服务器
路由器的拨号网络服务器
国家网络安全产业园区长沙区
阿里网络安全年薪
山东服务器虚拟化解决方案
网络技术导航网站
e站服务器贴吧
教你开一个mc服务器
常见的网络安全威胁来自哪些方面
深德软件开发
软件开发可以转岗什么
网络安全员调研文章
数据存储服务器价格和成本
mysql数据库购买
什么是网络安全接入方案
三丰云服务器安装sql
sd数据库官网
复古传奇三端互通服务器
软件开发效率改进建议
