• 数据库02作业


    首先建库

    mysql>create database homework;
    Query OK, 1 row affected (0.00 sec)
    

    使用库

    use homework;
    

    建表

    mysql>create table employee(
        -> id int null unique auto_increment,
        -> name varchar(20) not null,
        -> sex enum('male','female') not null default 'male',
        -> age int(3) not null default 28,
        -> hire_date date not null,
        -> post varchar(50),
        -> post_comment varchar(100),
        -> salary double(15,2),
        -> office int);
        
        Query OK, 0 rows affected (0.03 sec)
    

    查看建好的表格

    mysql> desc employee;
    +--------------+-----------------------+------+-----+---------+----------------+
    | Field        | Type                  | Null | Key | Default | Extra          |
    +--------------+-----------------------+------+-----+---------+----------------+
    | id           | int(11)               | NO   | PRI | NULL    | auto_increment |
    | name         | varchar(20)           | NO   |     | NULL    |                |
    | sex          | enum('male','female') | NO   |     | male    |                |
    | age          | int(3)                | 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,在表格中添加字段

    mysql> alter table employee add depart_id int(11);
    Query OK, 0 rows affected (0.06 sec)
    

    在表格中添加数据

    mysql> insert into employee(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)
        -> ;
    

    查看表格

    mysql> select * from employee;
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    | id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    |  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
    |  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
    |  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
    |  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
    |  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
    |  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
    |  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
    |  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
    |  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
    | 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
    | 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
    | 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
    | 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
    | 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
    | 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
    | 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
    | 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
    | 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    18 rows in set (0.00 sec)
    
    1. 查看岗位是teacher的员工姓名、年龄
    mysql> select name ,age from employee where post = 'teacher';
    +------------+-----+
    | name       | age |
    +------------+-----+
    | alex       |  78 |
    | wupeiqi    |  81 |
    | yuanhao    |  73 |
    | liwenzhou  |  28 |
    | jingliyang |  18 |
    | jinxin     |  18 |
    | 成龙       |  48 |
    +------------+-----+
    7 rows in set (0.00 sec)
    
    1. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
    mysql> select name ,age from employee where post = 'teacher' and age >30;
    +---------+-----+
    | name    | age |
    +---------+-----+
    | alex    |  78 |
    | wupeiqi |  81 |
    | yuanhao |  73 |
    | 成龙    |  48 |
    +---------+-----+
    4 rows in set (0.00 sec)
    
    1. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

      这里要注意的是在MySQL中没有 1000<xxx<9000的形式,所以要使用两次and

    mysql> select name,age,salary from employee where 1000<salary and salary<9000 and post = 'teacher';
    +-----------+-----+---------+
    | name      | age | salary  |
    +-----------+-----+---------+
    | wupeiqi   |  81 | 8300.00 |
    | yuanhao   |  73 | 3500.00 |
    | liwenzhou |  28 | 2100.00 |
    +-----------+-----+---------+
    3 rows in set (0.00 sec)
    
    1. 查看岗位描述不为NULL的员工信息

      在查找null的时候要使用is 而不是 =号

    mysql> select * from employee where post is not null;
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    | id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    |  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
    |  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
    |  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
    |  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
    |  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
    |  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
    |  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
    |  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
    |  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
    | 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
    | 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
    | 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
    | 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
    | 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
    | 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
    | 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
    | 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
    | 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
    +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    18 rows in set (0.00 sec)
    
    1. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
    mysql> select name ,age,salary from employee where salary in (10000,9000,30000);
    +------------+-----+----------+
    | name       | age | salary   |
    +------------+-----+----------+
    | jingliyang |  18 |  9000.00 |
    | jinxin     |  18 | 30000.00 |
    | 成龙       |  48 | 10000.00 |
    +------------+-----+----------+
    3 rows in set (0.00 sec)
    
    1. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
    mysql> select name ,age,salary from employee where salary not in (10000,9000,30000);
    +-----------+-----+------------+
    | name      | age | salary     |
    +-----------+-----+------------+
    | egon      |  18 |    7300.33 |
    | alex      |  78 | 1000000.31 |
    | wupeiqi   |  81 |    8300.00 |
    | yuanhao   |  73 |    3500.00 |
    | liwenzhou |  28 |    2100.00 |
    | 歪歪      |  48 |    3000.13 |
    | 丫丫      |  38 |    2000.35 |
    | 丁丁      |  18 |    1000.37 |
    | 星星      |  18 |    3000.29 |
    | 格格      |  28 |    4000.33 |
    | 张野      |  28 |   10000.13 |
    | 程咬金    |  18 |   20000.00 |
    | 程咬银    |  18 |   19000.00 |
    | 程咬铜    |  18 |   18000.00 |
    | 程咬铁    |  18 |   17000.00 |
    +-----------+-----+------------+
    15 rows in set (0.00 sec)
    
    1. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

      模糊查询要使用like,然后使用% 占位符

    mysql> select name,salary*12 as annnual_year from employee where name like 'jin%' and post = 'teacher';
    +------------+--------------+
    | name       | annnual_year |
    +------------+--------------+
    | jingliyang |    108000.00 |
    | jinxin     |    360000.00 |
    +------------+--------------+
    2 rows in set (0.00 sec)
    
  • 相关阅读:
    HDU 5640 King's Cake
    HDU 5615 Jam's math problem
    HDU 5610 Baby Ming and Weight lifting
    WHU1604 Play Apple 简单博弈
    HDU 1551 Cable master 二分
    CodeForces659C Tanya and Toys map
    Codeforces 960E 树dp
    gym 101485E 二分匹配
    Codeforces 961E 树状数组,思维
    Codeforces Round #473 (Div. 2) D 数学,贪心 F 线性基,模板
  • 原文地址:https://www.cnblogs.com/lyyblog0715/p/11761100.html
Copyright © 2020-2023  润新知