精华内容
下载资源
问答
  • mysql查询语句汇总

    2020-11-01 18:42:43
    select * from table where …; insert into table ( 列…) values ( 值…); update table set 列名=新值 where …; delete from table where …;...2.查询入职员工时间排名倒数第三的员工所有信息 sele

    select * from table where …;
    insert into table ( 列…) values ( 值…);
    update table set 列名=新值 where …;
    delete from table where …;
    一些常见例子:

    1.查找入职最晚员工
    select * from employees where hire_date IN (select max(hire_date) from employees);

    2.查询入职员工时间排名倒数第三的员工所有信息
    select * from employees order by hire_date desc limit 2,1;

    3.查找各个部门当前领导当前薪水详情以及其对应部门编号(连表查询)
    select salaries.*,dept_manager.dept_no
    from salaries left join dept_manager
    on salaries.emp_no = dept_manager.emp_no
    where salaries.to_date=‘9999-01-01’
    and dept_manager.to_date=‘9999-01-01’
    order by salaries.emp_no asc;

    4.查找所有已经分配部门的员工的last_name和first_name以及dept_no(连表查询)
    select employees.last_name,employees.first_name,dept_emp.dept_no
    from employees,dept_emp
    on employees.emp_no=dept_emp.emp_no;

    5.查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工(连表查询)
    select employees.last_name,employees.first_name,dept_emp.dept_no
    from employees left join dept_emp
    on employees.emp_no=dept_emp.emp_no;

    6.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t (group by 和having 的使用)
    select emp_no,count(distinct salary) as t
    from salaries
    group by emp_no
    having count(distinct salary)>15;

    7.找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示(group by 的使用)
    select distinct salary from salaries
    where to_date=‘9999-01-01’
    group by salary
    order by salary desc

    8.获取所有非manager的员工emp_no(not in)
    select emp_no from employees
    where emp_no not in (select emp_no from dept_manager)

    9.获取所有部门中当前员工当前薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列
    select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary)
    from dept_emp left join salaries
    on dept_emp.emp_no=salaries.emp_no
    and salaries.to_date=‘9999-01-01’
    and dept_emp.to_date=‘9999-01-01’
    group by dept_emp.dept_no;

    10.查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列(题目不能使用mod函数)
    select * from employees
    where emp_no%2 = 1
    and last_name != ‘Mary’
    order by hire_date desc;

    11.批量插入数据
    insert into actor(actor_id,first_name,last_name,last_update)
    values (1,‘PENELOPE’,‘GUINESS’,‘2006-02-15 12:34:33’),
    (2,‘NICK’,‘WAHLBERG’,‘2006-02-15 12:34:33’)

    展开全文
  • mysql 查询语句汇总学习

    千次阅读 多人点赞 2018-10-25 11:08:37
    数据库的操作作为计算机行业的必备技能,这里将用的最多的数据库查询语句汇总到这里(参考链接:https://blog.csdn.net/github_37767025/article/details/67636061),供大家交流学习 (一)单表查询 1.查询一张表...

    数据库的操作作为计算机行业的必备技能,这里将用的最多的数据库查询语句汇总到这里(参考链接:https://blog.csdn.net/github_37767025/article/details/67636061),供大家交流学习

    (一)单表查询

    1.查询一张表:     select * from 表名;

     

    2.查询指定字段:select 字段1,字段2,字段3….from 表名;

     

    3.where条件查询:select 字段1,字段2,字段3 frome 表名 where 条件表达式;

    例:select * from t_studect where id=1;

           select * from t_student where age>22;

     

    4.带in关键字查询:select 字段1,字段2 frome 表名 where 字段 [not]in(元素1,元素2);

    例:select * from t_student where age in (21,23);

           select * from t_student where age not in (21,23);

     

    5.带between and的范围查询:select 字段1,字段2 from 表名 where 字段 [not]between 取值1 and 取值2;

    例:select * frome t_student where age between 21 and 29;

           select * frome t_student where age not between 21 and 29;

     

    6.带like的模糊查询:select 字段1,字段2… frome 表名 where 字段 [not] like ‘字符串’;

        “%”代表任意字符;

        “_”代表单个字符;

    例:select * frome t_student where stuName like ‘张三”;

           select * frome t_student where stuName like ‘张三%”;

           select * frome t_student where stuName like ‘%张三%”;//含有张三的任意字符

           select * frome t_student where stuName like ‘张三_”

     

    7.空值查询:select 字段1,字段2…from 表名 where 字段  is[not] null;

     

    8.带and多条件查询:

    select 字段1,字段2…from 表名 where 条件表达式1 and 条件表达式2 [and 条件表达式n]

    例:select * from t_student where gradeName=’一年级’ and age=23;

     

    9.带or的多条件查询

    select 字段1,字段2…frome 表名 where 条件表达式1 or 条件表达式2 [or 条件表达式n]

    例:select * frome t_student where gradeName=’一年级’ or age=23;//或者,条件只要满足一个

     

    10.distinct去重复查询:select distinct 字段名 from 表名;

     

    11.对查询结果排序order byselect 字段1,字段2…from 表名 order by 属性名 [asc|desc]

    例:select * frome t_student order by age desc;//降序,从大到小

           select * frome t_student order by age asc;//升序,asc默认可以不写

     

    12.分组查询group by

    group by 属性名 [having 条件表达式][with rollup]

    1.单独使用(毫无意义,不能单独使用);

     

    2.与group_concat()函数一起使用;

    例:select gradeName,group_concat(stuName) from t_student group by gradeName;

     

    3.与聚合函数一起使用;

    例:select gradeName,count(stuName) from t_student group by gradeName;

     

    4.与having一起使用(显示输出的结果);

    例:select gradeName,count(stuName) from t_student group by gradeName having count(stuName)>3;

    5.与with rollup 一起使用(最后加入一个总和行);

    例:select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;

      

    13.limit 分页查询:select 字段1,字段2,…from 表名 limit 初始位置,记录数;

    例子:select * from t_student limit 0,5;

     

     

    (二)多表连接查询

     

    表一:t_book

     

    表二:t_bookType

     

    表三:t_priceLevel

     

     

    select * from t_book,t_bookType;

     

    1.内连接查询(两张或以上的表连接起来查询需要的数据)

    根据表一的bookTypeId查询出所有bookTypeName

    select * from t_book,t_bookType where t_book.bookTypeId=t_bookType.id;

     

    查询某几个字段:

    select bookNme,author from t_book,t_bookType where t_book.bookTypeId=t_bookType.id;

     

     

    2.外连接查询(两张或以上的表连接起来查询某张表的信息)

     

    3.左连接查询

    select * from t_book left join t_bookType on t_book.bookTypeId=t_bookType.id;

    如下图:表一(左边表)t_book的数据全部查出 表二没有的字段用null代替

     

    4.右连接查询

    select * from t_book right join t_bookType on t_book.bookTypeId=t_bookType.id;

    查出表二(右边表)的所有信息,表一没有的用null代替

     

    5.多条件连接查询

    select * from t_book,t_bookType where t_book.bookTypeId=t_bookType.id and t_book.price>70;

     

     

     

    子查询

    1.带in关键字的子查询(一个查询语句的条件可能落在另一个select语句的查询结果中)

    select * from t_book where bookType in(select id from t_bookType);

    select * from t_book where bookType not in(select id from t_bookType);

     

    2.带比较运算符的子查询(子查询可以使用比较运算符)

    select * from t_book where price>=(select price from t_priceLevel where priceLevel=1);

     

    3.带exists关键字的子查询(加入子查询查询到记录,则进行外层查询,否则,不执行外层查询)

    select * from t_book where exists(select * from t_booktype);

    select * from t_book where not exists(select * from t_booktype);

     

    4.带any关键字的子查询(any关键字表示满足其中任一条件)

    select * from t_book where price>= any(select price from t_priceLevel);

     

    5.带all关键字的子查询(all关键字表示满足所有条件)

    select * from t_book where price>= all(select price from t_priceLevel);

     

     

    合并查询

    1.union

    使用union关键字是,数据库系统会将所有的查询结果合并到一起,然后去掉相同的记录;

    select id from t_book union select id from t_bookType;

    2.union all

    使用union all,不会去除掉重复的记录;

    select id from t_book union all select id from t_bookType;

    展开全文
  • 1、将查询结果直接写入数据库中的表(已经建好) INSERT INTO final_song_avescore SELECT * FROM songidavescore WHERE SongId IN (  SELECT SongID FROM user_seed ); 2、显示联合查询结果 SELECT SeedID FROM...

    1、将查询结果直接写入数据库中的表(已经建好)

    INSERT INTO final_song_avescore
    SELECT * FROM songidavescore WHERE SongId IN (
                       SELECT SongID  FROM user_seed
    );


    2、显示联合查询结果

    SELECT SeedID FROM `seed_songid` WHERE SeedID NOT IN (
                       SELECT SeedID FROM final_seed
    )


    3、分组查询语句

    SELECT SeedID,COUNT(SongID) AS 'Song Count' FROM seed_songid GROUP BY SeedID ORDER BY COUNT(SongID) DESC;

    展开全文
  • 前言:本篇文章主要简介下MySQL中where,group by ,order by ,limit,join,union ,union all,子表等查询语法。测试数据准备create table emp ( empno numeric(4) not null, ename varchar(10), job varchar(9), mgr ...

    前言:

    本篇文章主要简介下MySQL中where,group by ,order by ,limit,join,union ,union all,子表等查询语法。

    测试数据准备

    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查询语法汇总的详细内容,更多关于MySQL查询语法的资料请关注脚本之家其它相关文章!

    展开全文
  • 数据库查询语句 *****mysql 多表关系 查询语句 索引添加数据补充:将一个查询结果插入到另一张表中create table student(name char(10),gender int);insert into student values("jack",1);insert into student ...
  • 创建表: create table ... mysql 查询select语句汇总 标签:limit 支持 student pid 另一个 最大 max 多表 进入 本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源:...
  • 1、mysql查询今天、昨天、上周今天select * from 表名 where to_days(时间字段名) = to_days(now());昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 17天SELECT * FROM 表名 where ...
  • Mysql复杂查询语句汇总

    千次阅读 2018-05-23 08:48:44
    1、mysql查询今天、昨天、上周今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) &lt;= 1 7天 SELECT * FROM ...
  • mysql数据库基本查询语句汇总 使用数据库: USE DATABASE db_name; DESTINCT //去除相同的记录; 显示表: SHOW DATABASES; SHOW TABLES; SHOW TABLES FROM db_name; SHOW COLUMNS FROM tb_name...
  • Mysql常用语句汇总

    2019-08-15 14:20:55
    Mysql常用查询语句汇总 https://blog.csdn.net/weixin_41189380/article/details/83375281 sprintf_s(sql_insert, 1000, “select * from customer where producenumber=(’%s’)”, producenumber);
  • 9 连接查询mysql支持三种类型的连接查询:内连接、右外连接、左外连接。 select * from 表名 [inner|across|left|right] join 表2 on 表1.列 = 表2.列(条件) inner|across 代表内连接 left|right 代表外连接 内...
  • 这里我对MySQL数据库的基本查询语句进行了汇总,如下
  • 查询数值型数据:  SELECT * FROM tb_name WHERE sum > 100;  查询谓词:>,=,,!=,!>,!,=   二查询字符串  SELECT * FROM tb_stu WHERE sname = '小刘'  SELECT * FROM tb_stu WHERE ...
  • MySQL 常用语句汇总

    2021-02-21 15:37:13
    MySQL 常用语句: 语句 功能说明 示例 select 数据查询 SELECT 列名称 FROM 表名称 distinct 数据去重 SELECT DISTINCT 列名称 FROM 表名称 where 有条件地从表中选取数据 SELECT 列名称 FROM 表名称 ...
  • DQL语句: 数据查询语言,用来查询记录(数据);主要是对表记录的查询。(重点)   一:基本查询   1,查看所有列:select * from 表名;  (其中“*”表示所有列)     2,查询指定字段信息:  ...
  • 目录 数据库查询语句 ***** 添加数据补充: 所有的select 关键字 where 条件 distinct 去除重复记录 指定字段 取别名 group by having order ...
  • mysql支持三种类型的连接查询:内连接、右外连接、左外连接。 select * from 表名 [inner|across|left|right] join 表2 on 表1.列 = 表2.列(条件) inner|across 代表内连接 left|right 代表外连接 内连接:...
  • 多表查询:  分类:合并结果集,连接查询,子查询   一:合并结果集 如果两张表的列类型和列数相同,可以进行合并, 关键字:union(去除重复行);union all(不去除重复行)   例: select * from cd ...
  • MySQL常用语句汇总

    2021-01-10 22:19:42
    day16-mysql基础&约束 学习目标 第1章 数据库介绍 1.1 数据库概述 什么是数据库 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以对数据库中 的数据...
  • MysqL实例基于sql中的数据查询语句汇总》要点:本文介绍了MysqL实例基于sql中的数据查询语句汇总,希望对您有用。如果有疑问,可以联系我们。where条件表达式--统计函数代码如下:Select count(1) from student;--...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 576
精华内容 230
关键字:

mysql查询语句汇总

mysql 订阅