shiyanlou:~/ $ git clone https://github.com/shiyanlou/SQL4 [15:06:24]
Cloning into 'SQL4'...
remote: Counting objects: 9, done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 9 (delta 1), reused 9 (delta 1), pack-reused 0
Unpacking objects: 100% (9/9), done.
Checking connectivity... done.
shiyanlou:~/ $ cd SQL4 [15:06:35]
shiyanlou:SQL4/ (master) $ ls [15:06:47]
MySQL-04-01.sql MySQL-04-02.sql
shiyanlou:SQL4/ (master) $ vi MySQL-04-01.sql [15:06:49]
shiyanlou:SQL4/ (master*) $ vi MySQL-04-02.sql [15:07:21]
shiyanlou:SQL4/ (master*) $ vi MySQL-04-01.sql [15:07:36]
shiyanlou:SQL4/ (master*) $ pwd [15:07:50]
/home/shiyanlou/SQL4
shiyanlou:SQL4/ (master*) $ mysql -uroot -p [15:07:56]
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 42
Server version: 5.5.50-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> source /home/shiyanlou/SQL4/MySQL-04-01.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> source /home/shiyanlou/SQL4/MySQL-04-02.sql
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)
mysql> select name,age from employee;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Jack | 24 |
| Rose | 22 |
| Jim | 35 |
| Mary | 21 |
| Alex | 26 |
| Ken | 27 |
| Rick | 24 |
| Joe | 31 |
| Mike | 23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.01 sec)
mysql> select name,age from employee where age>25;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Jim | 35 |
| Alex | 26 |
| Ken | 27 |
| Joe | 31 |
+------+------+
5 rows in set (0.01 sec)
mysql> select name,age,phone from employee where name='Mary';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Mary | 21 | 100101 |
+------+------+--------+
1 row in set (0.00 sec)
mysql> select name,age from employee where age<25 OR age>30;
+------+------+
| name | age |
+------+------+
| Jack | 24 |
| Rose | 22 |
| Jim | 35 |
| Mary | 21 |
| Rick | 24 |
| Joe | 31 |
| Mike | 23 |
+------+------+
7 rows in set (0.01 sec)
mysql> select naem ,age from employee where age>25 and age<30;
ERROR 1054 (42S22): Unknown column 'naem' in 'field list'
mysql> select name,age from employee where age>25 and age<30;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Alex | 26 |
| Ken | 27 |
+------+------+
3 rows in set (0.01 sec)
mysql> select name,age from employee where age between 25 and 30;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Alex | 26 |
| Ken | 27 |
+------+------+
3 rows in set (0.01 sec)
mysql> select name,age,phone,in_dpt from employee where in_dpt in ('dpt3','dpt4');
+------+------+--------+--------+
| name | age | phone | in_dpt |
+------+------+--------+--------+
| Tom | 26 | 119119 | dpt4 |
| Rose | 22 | 114114 | dpt3 |
| Rick | 24 | 987654 | dpt3 |
| Mike | 23 | 110110 | dpt4 |
| Tony | NULL | 102938 | dpt3 |
+------+------+--------+--------+
5 rows in set (0.00 sec)
mysql> select name,age,phone,in_dpt from employee where in_dpt not in ('dpt1','dpt3');
+------+------+--------+--------+
| name | age | phone | in_dpt |
+------+------+--------+--------+
| Tom | 26 | 119119 | dpt4 |
| Jack | 24 | 120120 | dpt2 |
| Mary | 21 | 100101 | dpt2 |
| Joe | 31 | 110129 | dpt2 |
| Mike | 23 | 110110 | dpt4 |
| Jobs | NULL | 19283 | dpt2 |
+------+------+--------+--------+
6 rows in set (0.01 sec)
mysql> select name,age,phone from employee where phone like '1101__';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Joe | 31 | 110129 |
| Mike | 23 | 110110 |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select name,age,phone from employee where name like 'J%';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Jack | 24 | 120120 |
| Jim | 35 | 100861 |
| Joe | 31 | 110129 |
| Jobs | NULL | 19283 |
+------+------+--------+
4 rows in set (0.00 sec)
mysql> select name,age,salary,phone from employee order by salary desc;
+------+------+--------+--------+
| name | age | salary | phone |
+------+------+--------+--------+
| Jobs | NULL | 3600 | 19283 |
| Joe | 31 | 3600 | 110129 |
| Ken | 27 | 3500 | 654321 |
| Rick | 24 | 3500 | 987654 |
| Mike | 23 | 3400 | 110110 |
| Tony | NULL | 3400 | 102938 |
| Alex | 26 | 3000 | 123456 |
| Mary | 21 | 3000 | 100101 |
| Jim | 35 | 3000 | 100861 |
| Rose | 22 | 2800 | 114114 |
| Jack | 24 | 2500 | 120120 |
| Tom | 26 | 2500 | 119119 |
+------+------+--------+--------+
12 rows in set (0.01 sec)
mysql> select max(salary) as max_salary,min(salary) from employee;
+------------+-------------+
| max_salary | min(salary) |
+------------+-------------+
| 3600 | 2500 |
+------------+-------------+
1 row in set (0.01 sec)
mysql> select of_dpt,count(proj_name) as count_project from project where of_dpt in (select in_pt from employee where name='Tom');
ERROR 1054 (42S22): Unknown column 'in_pt' in 'field list'
mysql> select of_dpt,count(proj_name) as count_project from project where of_dpt in (select in_pt from employee where name='Tom');
ERROR 1054 (42S22): Unknown column 'in_pt' in 'field list'
mysql> select of_dpt,count(proj_name) as count_project from project where of_dpt in (select in_pt from employee where name='Tom');
ERROR 1054 (42S22): Unknown column 'in_pt' in 'field list'
mysql> select of_dpt,count(proj_name) as count_project from project where of_dpt in (select in_fpt from employee where name='Tom');
ERROR 1054 (42S22): Unknown column 'in_fpt' in 'field list'
mysql> select of_dpt,count(proj_name) as count_project from project where of_dpt in (select in_dpt from employee where name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4 | 2 |
+--------+---------------+
1 row in set (0.01 sec)
mysql> select in_dpt from empployee where name='Tom';
ERROR 1146 (42S02): Table 'mysql1_shiyan.empployee' doesn't exist
mysql> select in_dpt from employee where name='Tom';
+--------+
| in_dpt |
+--------+
| dpt4 |
+--------+
1 row in set (0.01 sec)
mysql> select id,name,people_num from employee,department where employee.in_dpt = department.dpt_name order by id;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
| 1 | Tom | 15 |
| 2 | Jack | 12 |
| 3 | Rose | 10 |
| 4 | Jim | 11 |
| 5 | Mary | 12 |
| 6 | Alex | 11 |
| 7 | Ken | 11 |
| 8 | Rick | 10 |
| 9 | Joe | 12 |
| 10 | Mike | 15 |
| 11 | Jobs | 12 |
| 12 | Tony | 10 |
+----+------+------------+
12 rows in set (0.00 sec)
mysql> select dpt_name from department;
+----------+
| dpt_name |
+----------+
| dpt1 |
| dpt2 |
| dpt3 |
| dpt4 |
+----------+
4 rows in set (0.00 sec)
mysql> select in_dpt from employee;
+--------+
| in_dpt |
+--------+
| dpt1 |
| dpt1 |
| dpt1 |
| dpt2 |
| dpt2 |
| dpt2 |
| dpt2 |
| dpt3 |
| dpt3 |
| dpt3 |
| dpt4 |
| dpt4 |
+--------+
12 rows in set (0.01 sec)
mysql> select id,name,people_num from employee join department on employee.in_dpt = department.dpt_name;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
| 4 | Jim | 11 |
| 6 | Alex | 11 |
| 7 | Ken | 11 |
| 2 | Jack | 12 |
| 5 | Mary | 12 |
| 9 | Joe | 12 |
| 11 | Jobs | 12 |
| 3 | Rose | 10 |
| 8 | Rick | 10 |
| 12 | Tony | 10 |
| 1 | Tom | 15 |
| 10 | Mike | 15 |
+----+------+------------+
12 rows in set (0.01 sec)
mysql>