• day41

    今日内容概要

    • 创建表的完整语法

    • 约束条件(在数据类型的基础上在进行约束)

      • unsigned

      • zerofill

      • default

      • not null

      • unique

      • primary key

      • auto_increment

    • 补充一些别的SQL语句

    • 其他查询关键字

      • select

      • from

      • where

      • order by

      • limit

      • having

      • regexp

      • and or in not in between and like 等

    创建表的完整语法

    1. create table t1(id int, name varchar(43), age int);
    2.  
    3. create table 库名.表名（字段名 数据类型 约束条件，字段名1 数据类型 约束条件)；
    4.  
    5. insert into 库民.表名 values（1，2）；

    1.字段名和数据类型是必须写的，不能忽略
    2.约束条件是可选的，可有可无，而且有多个
    3.表结构中最一个字段不能有逗号

    约束条件

    约束条件其实就是在数据类型的基础之上在做约束
    1.unsigened (无符号)
    1. mysql> create table t1(id int unsigned);
    2. Query OK, 0 rows affected (0.04 sec)
    3.  
    4. mysql> insert into t1 values(-10);
    5. ERROR 1264 (22003): Out of range value for column 'id' at row 1
    6. mysql> insert into t1 values(10);
    7. Query OK, 1 row affected (0.01 sec)
    8.  
    9. mysql> select * from t1;
    10. +------+
    11. | id   |
    12. +------+
    13. |   10 |
    14. +------+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql>
    2.zerofill (0填充）
    1. mysql> create table t2(id int(5) zerofill);
    2. Query OK, 0 rows affected (0.04 sec)
    3.  
    4. mysql> insert into t2 values(123);
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> select * from t2;
    8. +-------+
    9. | id    |
    10. +-------+
    11. | 00123 |
    12. +-------+
    13. 1 row in set (0.00 sec)
    14.  
    15. mysql>
    3.default (默认值）
    1. mysql> create table t3(id int,name varchar(32) default 'wzc');
    2. Query OK, 0 rows affected (0.02 sec)
    3.  
    4. mysql> insert into t3(id) values(1);
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> select * from t3;
    8. +------+------+
    9. | id   | name |
    10. +------+------+
    11. |    1 | wzc  |
    12. +------+------+
    13. 1 row in set (0.00 sec)
    4.not null(非空）
    1. mysql> create table t4(id int,name varchar(32) not null);
    2. Query OK, 0 rows affected (0.02 sec)
    3.  
    4. mysql> insert into t4(id) values(1);
    5. ERROR 1364 (HY000): Field 'name' doesn't have a default value
    6. mysql> select * from t4;
    7. Empty set (0.00 sec)
    5.unique(唯一）
    单列唯一
    1. mysql> create table t5(id int,name varchar(32) unique);
    2. Query OK, 0 rows affected (0.02 sec)
    3.  
    4. mysql> insert into t5 values(1,'wzc');
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> insert into t5 values(2,'wzc');
    8. ERROR 1062 (23000): Duplicate entry 'wzc' for key 'name'

    多列唯一

    1. mysql> create table t6(id int, ip varchar(32),port varchar(32),unique(ip,port));
    2. Query OK, 0 rows affected (0.03 sec)
    3.  
    4. mysql> insert into t6 values(1,'192.168.150.1',3306);
    5. Query OK, 1 row affected (0.01 sec)
    6.  
    7. mysql> insert into t6 values(1,'192.168.150.1',3307);
    8. Query OK, 1 row affected (0.00 sec)
    9.  
    10. mysql> insert into t6 values(1,'192.168.150.2',3307);
    11. Query OK, 1 row affected (0.00 sec)
    12.  
    13. mysql> insert into t6 values(1,'192.168.150.2',3307);
    14. ERROR 1062 (23000): Duplicate entry '192.168.150.2-3307' for key 'ip'
    6.primary key(主键）
    主键单纯从约束上来看，它相当于是非空且唯一
    1. mysql> create table t7(id int primary key);
    2. Query OK, 0 rows affected (0.04 sec)
    3.  
    4. mysql> desc t7;
    5. +-------+---------+------+-----+---------+-------+
    6. | Field | Type    | Null | Key | Default | Extra |
    7. +-------+---------+------+-----+---------+-------+
    8. | id    | int(11) | NO   | PRI | NULL    |       |
    9. +-------+---------+------+-----+---------+-------+
    10. 1 row in set (0.02 sec)

    主键本身是一种索引，索引能够加快查询速度

    InnoDB存储引擎规定每一张表都要有一个主键，但是，我之前创建的表都没有指定主键，表是怎么创建成功的？
    是因为InnoDB存储引擎内部有一个隐藏的主键，这个主键我们看不到，它也不能够加快查询速度，仅仅是为了帮助我们把表创建成功，所以，以后我们创建表的时候都主动的创建一个主键，我们自己创建的主键能够加快查询速度，因为是一个索引。
    一般情况下，主键应该创建id字段，一张表中不只是有一个主键，可以有多个主键，但是，大多数情况下，都只有一个

    7.auto_incrment(自增）

    每一次主动比上一次加一，一般情况下配合主键使用

    1. mysql> create table t8(id int primary key auto_increment,name varchar(32));
    2. Query OK, 0 rows affected (0.03 sec)
    3.  
    4. mysql> insert into t8(name) values('wzc');
    5. Query OK, 1 row affected (0.01 sec)
    6.  
    7. mysql> insert into t8(name) values('wzc');
    8. Query OK, 1 row affected (0.00 sec)
    9.  
    10. mysql> select * from t8;
    11. +----+------+
    12. | id | name |
    13. +----+------+
    14. |  1 | wzc  |
    15. |  2 | wzc  |
    16. +----+------+
    17. 2 rows in set (0.00 sec)
    18.  
    19. mysql> delete from t8 where id=2;
    20. Query OK, 1 row affected (0.00 sec)
    21.  
    22. mysql> insert into t8(name) values('wzc');
    23. Query OK, 1 row affected (0.00 sec)
    24.  
    25. mysql> select * from t8;
    26. +----+------+
    27. | id | name |
    28. +----+------+
    29. |  1 | wzc  |
    30. |  3 | wzc  |
    31. +----+------+
    32. 2 rows in set (0.00 sec)

    补充一些sql语句

    1.增加字段

    1. mysql> desc t9;
    2. +-------+-------------+------+-----+---------+-------+
    3. | Field | Type        | Null | Key | Default | Extra |
    4. +-------+-------------+------+-----+---------+-------+
    5. | id    | int(9)      | YES  |     | NULL    |       |
    6. | name  | varchar(32) | YES  |     | NULL    |       |
    7. +-------+-------------+------+-----+---------+-------+
    8. 2 rows in set (0.02 sec)
    9.  
    10. mysql> alter table t9 add age varchar(32);
    11. Query OK, 0 rows affected (0.04 sec)
    12. Records: 0  Duplicates: 0  Warnings: 0
    13.  
    14. mysql> desc t9;
    15. +-------+-------------+------+-----+---------+-------+
    16. | Field | Type        | Null | Key | Default | Extra |
    17. +-------+-------------+------+-----+---------+-------+
    18. | id    | int(9)      | YES  |     | NULL    |       |
    19. | name  | varchar(32) | YES  |     | NULL    |       |
    20. | age   | varchar(32) | YES  |     | NULL    |       |
    21. +-------+-------------+------+-----+---------+-------+
    22. 3 rows in set (0.02 sec)
    23.  
    24. mysql> alter table t9 add age varchar(32) after id;# 增加在指定字段后
    25. Query OK, 0 rows affected (0.04 sec)
    26. Records: 0  Duplicates: 0  Warnings: 0
    27.  
    28. mysql> desc t9;
    29. +-------+-------------+------+-----+---------+-------+
    30. | Field | Type        | Null | Key | Default | Extra |
    31. +-------+-------------+------+-----+---------+-------+
    32. | id    | int(9)      | YES  |     | NULL    |       |
    33. | age   | varchar(32) | YES  |     | NULL    |       |
    34. | name  | varchar(32) | YES  |     | NULL    |       |
    35. +-------+-------------+------+-----+---------+-------+
    36. 3 rows in set (0.02 sec)
    37.  
    38. mysql> alter table t9 add gender varchar(32) first;# 增加在最前面
    39. Query OK, 0 rows affected (0.04 sec)
    40. Records: 0  Duplicates: 0  Warnings: 0
    41.  
    42. mysql> desc t9;
    43. +--------+-------------+------+-----+---------+-------+
    44. | Field  | Type        | Null | Key | Default | Extra |
    45. +--------+-------------+------+-----+---------+-------+
    46. | gender | varchar(32) | YES  |     | NULL    |       |
    47. | id     | int(9)      | YES  |     | NULL    |       |
    48. | age    | varchar(32) | YES  |     | NULL    |       |
    49. | name   | varchar(32) | YES  |     | NULL    |       |
    50. +--------+-------------+------+-----+---------+-------+
    51. 4 rows in set (0.01 sec)

    2.删除字段

    1. mysql> desc t9;
    2. +-------+-------------+------+-----+---------+-------+
    3. | Field | Type        | Null | Key | Default | Extra |
    4. +-------+-------------+------+-----+---------+-------+
    5. | id    | int(9)      | YES  |     | NULL    |       |
    6. | name  | varchar(32) | YES  |     | NULL    |       |
    7. | age   | varchar(32) | YES  |     | NULL    |       |
    8. +-------+-------------+------+-----+---------+-------+
    9. 3 rows in set (0.02 sec)
    10.  
    11. mysql> alter table t9 drop age;
    12. Query OK, 0 rows affected (0.04 sec)
    13. Records: 0  Duplicates: 0  Warnings: 0
    14.  
    15. mysql> desc t9;
    16. +-------+-------------+------+-----+---------+-------+
    17. | Field | Type        | Null | Key | Default | Extra |
    18. +-------+-------------+------+-----+---------+-------+
    19. | id    | int(9)      | YES  |     | NULL    |       |
    20. | name  | varchar(32) | YES  |     | NULL    |       |
    21. +-------+-------------+------+-----+---------+-------+
    22. 2 rows in set (0.02 sec)
    23.

    4.修改字段名 
    modify：只能改字段数据类型完整约束，不能改字段名（数据类型可改范围）
    change：可以改字段名

    1. mysql> alter table t9 modify age varchar(64);
    2. Query OK, 1 row affected (0.07 sec)
    3. Records: 1  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> desc t9;
    6. +--------+-------------+------+-----+---------+-------+
    7. | Field  | Type        | Null | Key | Default | Extra |
    8. +--------+-------------+------+-----+---------+-------+
    9. | gender | varchar(32) | YES  |     | NULL    |       |
    10. | id     | int(9)      | YES  |     | NULL    |       |
    11. | age    | varchar(64) | YES  |     | NULL    |       |
    12. | name   | varchar(32) | YES  |     | NULL    |       |
    13. +--------+-------------+------+-----+---------+-------+
    14. 4 rows in set (0.02 sec)
    15.  
    16. mysql> alter table t9 change name hobby varchar(32);
    17. Query OK, 0 rows affected (0.01 sec)
    18. Records: 0  Duplicates: 0  Warnings: 0
    19.  
    20. mysql> desc t9;
    21. +--------+-------------+------+-----+---------+-------+
    22. | Field  | Type        | Null | Key | Default | Extra |
    23. +--------+-------------+------+-----+---------+-------+
    24. | gender | varchar(32) | YES  |     | NULL    |       |
    25. | id     | int(9)      | YES  |     | NULL    |       |
    26. | age    | varchar(64) | YES  |     | NULL    |       |
    27. | hobby  | varchar(32) | YES  |     | NULL    |       |
    28. +--------+-------------+------+-----+---------+-------+
    29. 4 rows in set (0.02 sec)

    数据准备

    1. create table emp(
    2.   id int primary key auto_increment,
    3.   name varchar(20) not null,
    4.   sex enum('male','female') not null default 'male', #大部分是男的
    5.   age smallint(3) unsigned not null default 28,
    6.   hire_date date not null,
    7.   post varchar(50),
    8.   post_comment varchar(100),
    9.   salary double(15,2),
    10.   office int, #一个部门一个屋子
    11.   depart_id int
    12. );
    13.  
    14. insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
    15. ('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
    16. ('kevin','male',81,'20130305','teacher',8300,401,1),
    17. ('tony','male',73,'20140701','teacher',3500,401,1),
    18. ('owen','male',28,'20121101','teacher',2100,401,1),
    19. ('jack','female',18,'20110211','teacher',9000,401,1),
    20. ('jenny','male',18,'19000301','teacher',30000,401,1),
    21. ('sank','male',48,'20101111','teacher',10000,401,1),
    22. ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
    23. ('呵呵','female',38,'20101101','sale',2000.35,402,2),
    24. ('西西','female',18,'20110312','sale',1000.37,402,2),
    25. ('乐乐','female',18,'20160513','sale',3000.29,402,2),
    26. ('拉拉','female',28,'20170127','sale',4000.33,402,2),
    27. ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
    28. ('程咬金','male',18,'19970312','operation',20000,403,3),
    29. ('程咬银','female',18,'20130311','operation',19000,403,3),
    30. ('程咬铜','male',18,'20150411','operation',18000,403,3),
    31. ('程咬铁','female',18,'20140512','operation',17000,403,3);

    查询关键字之where

    1.查询id大于等于3小于等于6的数据

    1. mysql> select * from emp where id >= 3 and id <= 6;
    2. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    3. | id | name  | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
    4. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    5. |  3 | tony  | male   |  73 | 2014-07-01 | teacher | NULL         |  3500.00 |    401 |         1 |
    6. |  4 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         |  2100.00 |    401 |         1 |
    7. |  5 | jack  | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
    8. |  6 | jenny | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
    9. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    10. 4 rows in set (0.00 sec)
    11.  
    12. mysql> select * from emp where id between 3 and 6;
    13. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    14. | id | name  | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
    15. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    16. |  3 | tony  | male   |  73 | 2014-07-01 | teacher | NULL         |  3500.00 |    401 |         1 |
    17. |  4 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         |  2100.00 |    401 |         1 |
    18. |  5 | jack  | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
    19. |  6 | jenny | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
    20. +----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
    21. 4 rows in set (0.00 sec)

    2.查询薪资是20000或者18000或者17000的数据

    1. mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
    2.  
    3. mysql> select * from emp where salary in (20000,18000,17000);

    3.查询员工姓名中包含o字母的员工姓名和薪资
    在你刚接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的aql语句
    先是查哪张表 from emp
    再是根据什么条件去查 where name like ‘%o%’
    再是对查询出来的数据筛选展示部分 select name,salary

    1. mysql> select name,salary from emp where name like '%o%';
    2. +------+------------+
    3. | name | salary     |
    4. +------+------------+
    5. | tom  | 1000000.31 |
    6. | tony |    3500.00 |
    7. | owen |    2100.00 |
    8. +------+------------+
    9. 3 rows in set (0.00 sec)

     4.查询员工姓名是由四个字符组成的员工姓名与其薪资

    1. select name,salary from emp where like '____';
    2.  
    3. mysql> select name,salary from emp where char_length(name) = 4;

     5.查询id小于3或者大于6的数据

    1. mysql> select * from emp where id < 3 or id > 6;
    2.  
    3. mysql> select * from emp where id not between 3 and 6;

     6.查询薪资不在20000，18000，17000范围的数据

    mysql> select * from emp where salary not in (20000,18000,17000);

    7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is

    mysql> select name,post from emp where post_comment is NULL;

    查询关键字之group by分组

    分组：按照某个指定的条件将单个单个的个体分成一个个整体
    单纯的分组是没有意义的
    再MySQL中分组之后，只能够获得分组的依据！按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到

    分组一般配合聚合函数使用：sum max min avg count
    分组的关键字：group by
    1.分组之后默认可以获取所有的字段信息
    2.分组之后，展示的数据都是每个组的第一条数据

    1.按部门分组

    1. mysql> select * from emp group by post;
    2.  
    3. mysql> select post from emp group by post;# 查看分组

    2.获取每个部门的最高工资

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

    补充：在显示的时候还可以给字段取别名

    1. mysql> select post '部门',max(salary) as '最高工资' from emp group by post;
    2. +-----------+--------------+
    3. | 部门      | 最高工资     |
    4. +-----------+--------------+
    5. | operation |     20000.00 |
    6. | sale      |      4000.33 |
    7. | teacher   |   1000000.31 |
    8. +-----------+--------------+
    9. 3 rows in set (0.00 sec)

     每个部门的最低工资

    mysql> select post,min(salary) from emp group by post;

    每个部门的平均工资

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

    每个部门的工资总和

    mysql> select post,sum(salary) from emp group by post;

    每个部门的人数

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

    分组补充函数

    如果真的需要获取分组以外的数据字段，可以使用group_concat()，分组之后使用
    每个部门的员工姓名

    1. mysql> select post,group_concat(name) from emp group by post;
    2. +-----------+------------------------------------------------+
    3. | post      | group_concat(name)                             |
    4. +-----------+------------------------------------------------+
    5. | operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
    6. | sale      | 拉拉,乐乐,西西,呵呵,哈哈                       |
    7. | teacher   | sank,jenny,jack,owen,tony,kevin,tom            |
    8. +-----------+------------------------------------------------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> select post,group_concat(name,'|',sex) from emp group by post;
    12.  
    13. mysql> select post,group_concat(name,'|',sex,'|',post) from emp group by post;
    14.  
    15. mysql> select post,group_concat(distinct name ) from emp group by post;
    16. +-----------+------------------------------------------------+
    17. | post      | group_concat(distinct name )                   |
    18. +-----------+------------------------------------------------+
    19. | operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
    20. | sale      | 拉拉,乐乐,西西,呵呵,哈哈                       |
    21. | teacher   | sank,jenny,jack,owen,tony,kevin,tom            |
    22. +-----------+------------------------------------------------+
    23.  
    24. mysql> select post,group_concat(distinct name separator '%') from emp group by post;

    concat 不分组使用

    1. mysql> select concat(name,sex) from emp;
    2. +------------------+
    3. | concat(name,sex) |
    4. +------------------+
    5. | tommale          |
    6. | kevinmale        |
    7. | tonymale         |
    8. | owenmale         |
    9. | jackfemale       |
    10. | jennymale        |
    11. | sankmale         |
    12. | 哈哈female       |
    13. | 呵呵female       |
    14. | 西西female       |
    15. | 乐乐female       |
    16. | 拉拉female       |
    17. | 僧龙male         |
    18. | 程咬金male       |
    19. | 程咬银female     |
    20. | 程咬铜male       |
    21. | 程咬铁female     |
    22. +------------------+
    23. 17 rows in set (0.00 sec)
    24.  
    25. select concat(name,'|', sex) from emp;
    26.

    concat_ws()

    1. mysql> select post,concat_ws('|',name,age,sex)from emp;
    2. +-----------+-----------------------------+
    3. | post      | concat_ws('|',name,age,sex) |
    4. +-----------+-----------------------------+
    5. | teacher   | tom|78|male                 |
    6. | teacher   | kevin|81|male               |
    7. | teacher   | tony|73|male                |
    8. | teacher   | owen|28|male                |
    9. | teacher   | jack|18|female              |
    10. | teacher   | jenny|18|male               |
    11. | teacher   | sank|48|male                |
    12. | sale      | 哈哈|48|female              |
    13. | sale      | 呵呵|38|female              |
    14. | sale      | 西西|18|female              |
    15. | sale      | 乐乐|18|female              |
    16. | sale      | 拉拉|28|female              |
    17. | operation | 僧龙|28|male                |
    18. | operation | 程咬金|18|male              |
    19. | operation | 程咬银|18|female            |
    20. | operation | 程咬铜|18|male              |
    21. | operation | 程咬铁|18|female            |
    22. +-----------+-----------------------------+
    23. 17 rows in set (0.00 sec)

    关键字之having过滤

    where与having都是筛选功能，但是有区别
    where在分组之前对数据进行筛选
    having在分组之后对数据进行筛选

    1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.

    1. 先筛选出年龄在30岁以上的
    2. mysql> select * from emp where age > 30;
    3. +----+--------+--------+-----+------------+---------+--------------+------------+--------+-----------+
    4. | id | name   | sex    | age | hire_date  | post    | post_comment | salary     | office | depart_id |
    5. +----+--------+--------+-----+------------+---------+--------------+------------+--------+-----------+
    6. |  1 | tom    | male   |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
    7. |  2 | kevin  | male   |  81 | 2013-03-05 | teacher | NULL         |    8300.00 |    401 |         1 |
    8. |  3 | tony   | male   |  73 | 2014-07-01 | teacher | NULL         |    3500.00 |    401 |         1 |
    9. |  7 | sank   | male   |  48 | 2010-11-11 | teacher | NULL         |   10000.00 |    401 |         1 |
    10. |  8 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         |    3000.13 |    402 |         2 |
    11. |  9 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         |    2000.35 |    402 |         2 |
    12. +----+--------+--------+-----+------------+---------+--------------+------------+--------+-----------+
    13. 6 rows in set (0.00 sec)
    14.  
    15. 再进行分组，按照部门分组
    16. mysql> select avg(salary) as avg_salary from emp where age > 30 group by post;
    17. +---------------+
    18. | avg_salary    |
    19. +---------------+
    20. |   2500.240000 |
    21. | 255450.077500 |
    22. +---------------+
    23. 2 rows in set (0.00 sec)
    24.  
    25. 保留平均薪资大于100000的部门
    26. mysql> select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;
    27. +---------------+
    28. | avg_salary    |
    29. +---------------+
    30. | 255450.077500 |
    31. +---------------+
    32. 1 row in set (0.00 sec)

     关键字之distinct去重

    带主键的数据去重有没有意义？ 没有，主键本身就是唯一的

    1. mysql> select distinct sex from emp;
    2. +--------+
    3. | sex    |
    4. +--------+
    5. | male   |
    6. | female |
    7. +--------+
    8. 2 rows in set (0.00 sec)

    关键字之order by排序

    select * from emp order by salary; # 默认升序排

    select * from emp order by salary desc; # 降序排

    先按照age降序排，在年龄相同的情况下再按照薪资升序排

    select * from emp order by age desc,salary;

    多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样

    统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

    1. 20岁以上的员工
    2. mysql> select * from emp where age >20;
    3. 各部门的平均薪资
    4. mysql> select avg(salary) from emp where age > 20 group by post having avg(salary) >1000;
    5.  
    6. mysql> select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;

    关键字之limit分页

    限制展示条数

    1. mysql> select * from emp limit 3;
    2.  
    3. 查询工资最高的人的详细信息
    4. mysql> select * from emp order by salary desc limit 1;
    5.  
    6. 分页显示
    7. mysql> select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
    8.

    关键字之regxp正则

    1. mysql> select * from emp where name regexp '^j.*(n|y)$';
    2. +----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+
    3. | id | name  | sex  | age | hire_date  | post    | post_comment | salary   | office | depart_id |
    4. +----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+
    5. |  6 | jenny | male |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
    6. +----+-------+------+-----+------------+---------+--------------+----------+--------+-----------+
    7. 1 row in set (0.00 sec)


     

  • 相关阅读:
    基于WEB+APP的模拟工业仪器的监控预警系统
    LeetCode538：把二叉搜索树转换为累加树
    Flink面试题
    react 项目搭建步骤
    【前端知识】Three 学习日志（一）—— Three.js 的简单尝试
    自定义jenkins镜像提示FontConfiguration.head错误
    Springboot整合JPA多数据源（Oracle+Mysql）
    redisson有几种分布式算法
    Python爬虫教程——解析网页中的元素
    node.js后端及Vue前端跨域解决方案
  • 原文地址：https://blog.csdn.net/qq_67257222/article/details/134012009