• 数据库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)
    
  • 相关阅读:
    HTTP压力测试工具apache的ab的使用方法
    [转]Memcached 协议中英文对照
    [转]Windows下删除.svn文件夹的最简易方法
    [转]50 个便利的 Web 设计工具
    Navicat数据存放位置和备份数据库路径设置
    Silverlight第三方控件
    MVC3.0中使用JQuery.DataTable插件。
    什么是句柄?为什么会有句柄?HANDLE
    WPF内存释放
    ArcGis for wpf 符号渲染
  • 原文地址:https://www.cnblogs.com/lyyblog0715/p/11761100.html
Copyright © 2020-2023  润新知