sql 实例一个

mysql> CREATE DATABASE microservice_mallmanagement;
Query OK, 1 row affected (0.01 sec)

mysql> USE microservice_mallmanagement;
Database changed
mysql> DROP TABLE IF EXISTS `tb_order`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `tb_order` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `createtime` datetime DEFAULT NULL,
    ->   `number` varchar(255) DEFAULT NULL,
    ->   `userid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> INSERT INTO `tb_order` VALUES ('1', '2017-10-09 10:15:44', '201709181459001', '1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `tb_order` VALUES ('2', '2017-10-24 18:22:12', '201709181459008', '1');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> DROP TABLE IF EXISTS `tb_user`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `tb_user` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `address` varchar(255) DEFAULT NULL,
    ->   `username` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO `tb_user` VALUES ('1', 'beijing', 'shitou');
Query OK, 1 row affected (0.01 sec)

mysql> show tables;
+---------------------------------------+
| Tables_in_microservice_mallmanagement |
+---------------------------------------+
| tb_order                              |
| tb_user                               |
+---------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+----------+
| id | address | username |
+----+---------+----------+
|  1 | beijing | shitou   |
+----+---------+----------+
1 row in set (0.00 sec)

mysql> select * from tb_order;
+----+---------------------+-----------------+--------+
| id | createtime          | number          | userid |
+----+---------------------+-----------------+--------+
|  1 | 2017-10-09 10:15:44 | 201709181459001 |      1 |
|  2 | 2017-10-24 18:22:12 | 201709181459008 |      1 |
+----+---------------------+-----------------+--------+
2 rows in set (0.00 sec)

mysql> create table user_info(user_id int primary key,user_name varchar(30),user_age int);
Query OK, 0 rows affected (0.06 sec)

mysql> create table relation_define(relation_id int primary key,relation_name varchar(32));
Query OK, 0 rows affected (0.12 sec)

mysql> create table user_relations(user_id int,relation_id int,user_rel_id int);
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> desc user_info;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| user_id   | int(11)     | NO   | PRI | NULL    |       |
| user_name | varchar(30) | YES  |     | NULL    |       |
| user_age  | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into user_info values(1,'james',18),(2,'Rose',25),(3,'jack',50);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user_info;
+---------+-----------+----------+
| user_id | user_name | user_age |
+---------+-----------+----------+
|       1 | james     |       18 |
|       2 | Rose      |       25 |
|       3 | jack      |       50 |
+---------+-----------+----------+
3 rows in set (0.00 sec)

mysql> desc relation_define;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| relation_id   | int(11)     | NO   | PRI | NULL    |       |
| relation_name | varchar(32) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into relation_define values(1,'同事'),(2,'父子'),(3,'朋友');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc user_relations;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| user_id     | int(11) | YES  |     | NULL    |       |
| relation_id | int(11) | YES  |     | NULL    |       |

mysql> select user_id,relation_id  from user_relations where user_rel_id union select user_rel_id as user_id,relation_id from user_relations where user_id=1;
+---------+-------------+
| user_id | relation_id |
+---------+-------------+
|       1 |           1 |
|       2 |           2 |
|       1 |           3 |
|       2 |           1 |
|       3 |           3 |
+---------+-------------+
5 rows in set (0.01 sec)

mysql> select user_id,relation_id  from user_relations where user_rel_id=1 union select user_rel_id as user_id,relation_id from user_relations where user_id=1;
+---------+-------------+
| user_id | relation_id |
+---------+-------------+
|       2 |           1 |
|       3 |           3 |
+---------+-------------+
2 rows in set (0.00 sec)

mysql> select a.user_name,b.relation_name from user_info a,relation_define b, (select user_id,relation_id  from user_relations where user_rel_id=1 union select user_rel_id as user_id,relation_id from user_relations where user_id=1) c  where a.user_id=c.user_id and b.relation_id=c.relation_id;
+-----------+---------------+
| user_name | relation_name |
+-----------+---------------+
| Rose      | 同事          |
| jack      | 朋友          |
+-----------+---------------+
2 rows in set (0.01 sec)

 

select ifnull(select distinct salary from Employee order by salary desc limit 1,1),null) as secondHighSalary