MySQL主要有哪些语法
发表于：2024-11-30 作者：千家信息网编辑
千家信息网最后更新 2024年11月30日，下文我给大家简单讲讲关于MySQL主要有哪些语法，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完MySQL主要有哪些语法对大家多少有点帮助吧。测试数据准备create t
千家信息网最后更新 2024年11月30日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安全错误
数据库的锁怎样保障安全
深圳迈集软件开发公司
彩虹引擎技能数据库英文
编程软件开发哪家好
开票盘服务器
原神卡池概率与服务器有关吗
北京多功能网络技术开发价值
肉羊产业发展数据库
软件开发专业现在过时了吗
浦东新区技术软件开发中心收费
厦门驿盟网络技术有限公司
网站服务器开小差
工业图控系统软件开发报价
网络安全与执法中的管理问题
网络安全论坛 永久会址
爱保定网络安全吗
辽宁特色软件开发诚信经营
龙之谷服务器推荐
非关系型数据库不支持事务
服务器内存条分类
潮州电商软件开发
佳木斯软件开发公司价格
鼎鹏网络技术有限公司
网络技术学校推荐
非关系型数据库概论
工业图控系统软件开发报价
异能都市服务器注册人数己满
树型 数据库
软件开发流程api
图片上传到远程服务器
ug许可证服务器连接失败
