SQL实践笔记——基础语法

环境：CentOS 7 + MariaDB 10 + Mysql_testDB_employees

 

• 选择employees的所有列，条件是first_name=Duangkaew或last_name=Botman且emp_no < 10011，并将hire_date列按照降序排列

MariaDB [employees]> SELECT * FROM employees WHERE (first_name = 'Duangkaew' OR last_name = 'Botman') AND emp_no < 20000 ORDER BY hire_date DESC;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  17402 | 1956-10-22 | Tuval      | Botman      | M      | 1995-12-28 |
|  14050 | 1963-11-05 | Duangkaew  | Botman      | M      | 1995-10-30 |
|  14532 | 1961-03-30 | Duangkaew  | Docker      | F      | 1994-07-21 |
|  10167 | 1958-05-23 | Duangkaew  | Rassart     | M      | 1992-04-04 |
|  17111 | 1960-07-16 | Danil      | Botman      | M      | 1991-11-03 |
|  18881 | 1952-09-19 | Heejo      | Botman      | F      | 1991-03-22 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau    | F      | 1989-08-24 |
|  16204 | 1958-02-26 | Duangkaew  | Shackell    | M      | 1989-01-20 |
|  17345 | 1959-01-02 | Duangkaew  | Strandh     | M      | 1988-09-25 |
|  17886 | 1956-08-28 | Jayesh     | Botman      | M      | 1988-04-23 |
|  19421 | 1959-05-05 | Duangkaew  | Rosis       | M      | 1988-04-02 |
|  14427 | 1959-03-08 | Duangkaew  | Percebois   | F      | 1986-08-23 |
|  16910 | 1953-03-06 | Kazunori   | Botman      | M      | 1986-05-29 |
|  12709 | 1955-08-09 | Ronghao    | Botman      | M      | 1985-12-02 |
|  16974 | 1960-10-14 | Duangkaew  | Shokrollahi | M      | 1985-08-27 |
|  11148 | 1953-01-23 | Zhanqiu    | Botman      | F      | 1985-07-06 |
+--------+------------+------------+-------------+--------+------------+
16 rows in set (0.01 sec)

 

• DISTINCT，仅列出不同的值

MariaDB [employees]> SELECT DISTINCT gender FROM employees WHERE emp_no < 10100;
+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.00 sec)

 

• 在employees表中添加一行信息

MariaDB [employees]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (999999, '1988-07-27', 'Christan', 'Cliff', 'M', '2014-09-10');
Query OK, 1 row affected (0.00 sec)

MariaDB [employees]> SELECT * FROM employees WHERE emp_no = 999999;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 999999 | 1988-07-27 | Christan   | Cliff     | M      | 2014-09-10 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

 

• 更新employees表中emp_no = 999999的birth_data为1988-04-21

MariaDB [employees]> UPDATE employees SET birth_date = '1988-04-21' WHERE emp_no = 999999;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> SELECT * FROM employees WHERE emp_no = 999999;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 999999 | 1988-04-21 | Christan   | Cliff     | M      | 2014-09-10 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

 

• 删除emp_no = 987654的行

MariaDB [employees]> SELECT * FROM employees WHERE emp_no = 987654;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 987654 | 0000-00-00 | Christan   | Cliff     | M      | 0000-00-00 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

MariaDB [employees]> DELETE FROM employees WHERE emp_no = 987654;
Query OK, 1 row affected (0.00 sec)

MariaDB [employees]> SELECT * FROM employees WHERE emp_no = 987654;
Empty set (0.00 sec)