1. SELECT语句
1.1 SELECT...
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> SELECT 2*3, 8;
+-----+---+
| 2*3 | 8 |
+-----+---+
| 6 | 8 |
+-----+---+
1 row in set (0.01 sec)
mysql>
1.2 SELECT ... FROM
- SELECT 标识选择哪些列 FROM 标识从哪个表中选择
# DUAL 伪表
mysql> SELECT 1+1, 5 FROM DUAL;
+-----+---+
| 1+1 | 5 |
+-----+---+
| 2 | 5 |
+-----+---+
1 row in set (0.00 sec)
mysql>
- 选择全部列
mysql> SELECT * FROM jobs;
+------------+---------------------------------+------------+------------+
| job_id | job_title | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| HR_REP | Human Resources Representative | 4000 | 9000 |
| IT_PROG | Programmer | 4000 | 10000 |
| MK_MAN | Marketing Manager | 9000 | 15000 |
| MK_REP | Marketing Representative | 4000 | 9000 |
| PR_REP | Public Relations Representative | 4500 | 10500 |
| PU_CLERK | Purchasing Clerk | 2500 | 5500 |
| PU_MAN | Purchasing Manager | 8000 | 15000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| SA_REP | Sales Representative | 6000 | 12000 |
| SH_CLERK | Shipping Clerk | 2500 | 5500 |
| ST_CLERK | Stock Clerk | 2000 | 5000 |
| ST_MAN | Stock Manager | 5500 | 8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)
mysql>
- 选择特定的列
mysql> SELECT job_title, min_salary FROM jobs;
+---------------------------------+------------+
| job_title | min_salary |
+---------------------------------+------------+
| Public Accountant | 4200 |
| Accounting Manager | 8200 |
| Administration Assistant | 3000 |
| President | 20000 |
| Administration Vice President | 15000 |
| Accountant | 4200 |
| Finance Manager | 8200 |
| Human Resources Representative | 4000 |
| Programmer | 4000 |
| Marketing Manager | 9000 |
| Marketing Representative | 4000 |
| Public Relations Representative | 4500 |
| Purchasing Clerk | 2500 |
| Purchasing Manager | 8000 |
| Sales Manager | 10000 |
| Sales Representative | 6000 |
| Shipping Clerk | 2500 |
| Stock Clerk | 2000 |
| Stock Manager | 5500 |
+---------------------------------+------------+
19 rows in set (0.00 sec)
mysql>
1.3 列的别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特
殊的字符并区分大小写 - AS 可以省略
- 建议别名简短，见名知意
mysql> SELECT job_title AS title, min_salary 最小工资, max_salary "max salary" FROM jobs;
+---------------------------------+--------------+------------+
| title | 最小工资 | max salary |
+---------------------------------+--------------+------------+
| Public Accountant | 4200 | 9000 |
| Accounting Manager | 8200 | 16000 |
| Administration Assistant | 3000 | 6000 |
| President | 20000 | 40000 |
| Administration Vice President | 15000 | 30000 |
| Accountant | 4200 | 9000 |
| Finance Manager | 8200 | 16000 |
| Human Resources Representative | 4000 | 9000 |
| Programmer | 4000 | 10000 |
| Marketing Manager | 9000 | 15000 |
| Marketing Representative | 4000 | 9000 |
| Public Relations Representative | 4500 | 10500 |
| Purchasing Clerk | 2500 | 5500 |
| Purchasing Manager | 8000 | 15000 |
| Sales Manager | 10000 | 20000 |
| Sales Representative | 6000 | 12000 |
| Shipping Clerk | 2500 | 5500 |
| Stock Clerk | 2000 | 5000 |
| Stock Manager | 5500 | 8500 |
+---------------------------------+--------------+------------+
19 rows in set (0.00 sec)
mysql>
1.4 去除重复行
- 在SELECT语句中使用关键字DISTINCT去除重复行
- 注意：DISTINCT 其实是对后面所有列名的组合进行去重
mysql> SELECT DISTINCT min_salary FROM jobs;
+------------+
| min_salary |
+------------+
| 4200 |
| 8200 |
| 3000 |
| 20000 |
| 15000 |
| 4000 |
| 9000 |
| 4500 |
| 2500 |
| 8000 |
| 10000 |
| 6000 |
| 2000 |
| 5500 |
+------------+
14 rows in set (0.01 sec)
mysql> SELECT DISTINCT min_salary,max_salary FROM jobs;
+------------+------------+
| min_salary | max_salary |
+------------+------------+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
| 20000 | 40000 |
| 15000 | 30000 |
| 4000 | 9000 |
| 4000 | 10000 |
| 9000 | 15000 |
| 4500 | 10500 |
| 2500 | 5500 |
| 8000 | 15000 |
| 10000 | 20000 |
| 6000 | 12000 |
| 2000 | 5000 |
| 5500 | 8500 |
+------------+------------+
15 rows in set (0.00 sec)
mysql>
1.5 空值参与运算
- 所有运算符或列值遇到null值，运算的结果都为null
- 这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长
度是空。而且，在 MySQL 里面，空值是占用空间的
mysql> SELECT employee_id,salary,commission_pct,12*salary*(1+commission_pct) "annual_sal" FROM employees;
+-------------+----------+----------------+------------+
| employee_id | salary | commission_pct | annual_sal |
+-------------+----------+----------------+------------+
| 100 | 24000.00 | NULL | NULL |
| 101 | 17000.00 | NULL | NULL |
| 102 | 17000.00 | NULL | NULL |
| 103 | 9000.00 | NULL | NULL |
| 104 | 6000.00 | NULL | NULL |
| 105 | 4800.00 | NULL | NULL |
| 106 | 4800.00 | NULL | NULL |
| 107 | 4200.00 | NULL | NULL |
| 108 | 12000.00 | NULL | NULL |
| 109 | 9000.00 | NULL | NULL |
| 110 | 8200.00 | NULL | NULL |
| 111 | 7700.00 | NULL | NULL |
| 112 | 7800.00 | NULL | NULL |
| 113 | 6900.00 | NULL | NULL |
| 114 | 11000.00 | NULL | NULL |
| 115 | 3100.00 | NULL | NULL |
| 116 | 2900.00 | NULL | NULL |
| 117 | 2800.00 | NULL | NULL |
| 118 | 2600.00 | NULL | NULL |
| 119 | 2500.00 | NULL | NULL |
| 120 | 8000.00 | NULL | NULL |
| 121 | 8200.00 | NULL | NULL |
| 122 | 7900.00 | NULL | NULL |
| 123 | 6500.00 | NULL | NULL |
| 124 | 5800.00 | NULL | NULL |
| 125 | 3200.00 | NULL | NULL |
| 126 | 2700.00 | NULL | NULL |
| 127 | 2400.00 | NULL | NULL |
| 128 | 2200.00 | NULL | NULL |
| 129 | 3300.00 | NULL | NULL |
| 130 | 2800.00 | NULL | NULL |
| 131 | 2500.00 | NULL | NULL |
| 132 | 2100.00 | NULL | NULL |
| 133 | 3300.00 | NULL | NULL |
| 134 | 2900.00 | NULL | NULL |
| 135 | 2400.00 | NULL | NULL |
| 136 | 2200.00 | NULL | NULL |
| 137 | 3600.00 | NULL | NULL |
| 138 | 3200.00 | NULL | NULL |
| 139 | 2700.00 | NULL | NULL |
| 140 | 2500.00 | NULL | NULL |
| 141 | 3500.00 | NULL | NULL |
| 142 | 3100.00 | NULL | NULL |
| 143 | 2600.00 | NULL | NULL |
| 144 | 2500.00 | NULL | NULL |
| 145 | 14000.00 | 0.40 | 235200.00 |
| 146 | 13500.00 | 0.30 | 210600.00 |
| 147 | 12000.00 | 0.30 | 187200.00 |
| 148 | 11000.00 | 0.30 | 171600.00 |
| 149 | 10500.00 | 0.20 | 151200.00 |
| 150 | 10000.00 | 0.30 | 156000.00 |
| 151 | 9500.00 | 0.25 | 142500.00 |
| 152 | 9000.00 | 0.25 | 135000.00 |
| 153 | 8000.00 | 0.20 | 115200.00 |
| 154 | 7500.00 | 0.20 | 108000.00 |
| 155 | 7000.00 | 0.15 | 96600.00 |
| 156 | 10000.00 | 0.35 | 162000.00 |
| 157 | 9500.00 | 0.35 | 153900.00 |
| 158 | 9000.00 | 0.35 | 145800.00 |
| 159 | 8000.00 | 0.30 | 124800.00 |
| 160 | 7500.00 | 0.30 | 117000.00 |
| 161 | 7000.00 | 0.25 | 105000.00 |
| 162 | 10500.00 | 0.25 | 157500.00 |
| 163 | 9500.00 | 0.15 | 131100.00 |
| 164 | 7200.00 | 0.10 | 95040.00 |
| 165 | 6800.00 | 0.10 | 89760.00 |
| 166 | 6400.00 | 0.10 | 84480.00 |
| 167 | 6200.00 | 0.10 | 81840.00 |
| 168 | 11500.00 | 0.25 | 172500.00 |
| 169 | 10000.00 | 0.20 | 144000.00 |
| 170 | 9600.00 | 0.20 | 138240.00 |
| 171 | 7400.00 | 0.15 | 102120.00 |
| 172 | 7300.00 | 0.15 | 100740.00 |
| 173 | 6100.00 | 0.10 | 80520.00 |
| 174 | 11000.00 | 0.30 | 171600.00 |
| 175 | 8800.00 | 0.25 | 132000.00 |
| 176 | 8600.00 | 0.20 | 123840.00 |
| 177 | 8400.00 | 0.20 | 120960.00 |
| 178 | 7000.00 | 0.15 | 96600.00 |
| 179 | 6200.00 | 0.10 | 81840.00 |
| 180 | 3200.00 | NULL | NULL |
| 181 | 3100.00 | NULL | NULL |
| 182 | 2500.00 | NULL | NULL |
| 183 | 2800.00 | NULL | NULL |
| 184 | 4200.00 | NULL | NULL |
| 185 | 4100.00 | NULL | NULL |
| 186 | 3400.00 | NULL | NULL |
| 187 | 3000.00 | NULL | NULL |
| 188 | 3800.00 | NULL | NULL |
| 189 | 3600.00 | NULL | NULL |
| 190 | 2900.00 | NULL | NULL |
| 191 | 2500.00 | NULL | NULL |
| 192 | 4000.00 | NULL | NULL |
| 193 | 3900.00 | NULL | NULL |
| 194 | 3200.00 | NULL | NULL |
| 195 | 2800.00 | NULL | NULL |
| 196 | 3100.00 | NULL | NULL |
| 197 | 3000.00 | NULL | NULL |
| 198 | 2600.00 | NULL | NULL |
| 199 | 2600.00 | NULL | NULL |
| 200 | 4400.00 | NULL | NULL |
| 201 | 13000.00 | NULL | NULL |
| 202 | 6000.00 | NULL | NULL |
| 203 | 6500.00 | NULL | NULL |
| 204 | 10000.00 | NULL | NULL |
| 205 | 12000.00 | NULL | NULL |
| 206 | 8300.00 | NULL | NULL |
+-------------+----------+----------------+------------+
107 rows in set (0.00 sec)
mysql>
1.6 着重号
- 我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在
SQL语句中使用一对``（着重号）引起来
mysql> SELECT * FROM order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1
mysql>
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.01 sec)
mysql>
1.7 对常数进行查询
- SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的
取值是我们指定的，而不是从数据表中动态取出的 - SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个
固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数
mysql> SELECT '民族' corporation,region_id, region_name FROM regions;
+-------------+-----------+------------------------+
| corporation | region_id | region_name |
+-------------+-----------+------------------------+
| 民族 | 1 | Europe |
| 民族 | 2 | Americas |
| 民族 | 3 | Asia |
| 民族 | 4 | Middle East and Africa |
+-------------+-----------+------------------------+
4 rows in set (0.00 sec)
mysql>
2. 显示表结构
- 使用DESCRIBE 或 DESC 命令，表示表结构
mysql> DESC regions;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| region_id | int | NO | PRI | NULL | |
| region_name | varchar(25) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> DESCRIBE regions;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| region_id | int | NO | PRI | NULL | |
| region_name | varchar(25) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>
其中，各个字段的含义分别解释如下：
- Field：表示字段名称
- Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的
- Null：表示该列是否可以存储NULL值
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一
部分；MUL表示在列中某个给定值允许出现多次 - Default：表示该列是否有默认值，如果有，那么值是多少
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等
3. 过滤
- 使用WHERE 子句，将不满足条件的行过滤掉
WHERE子句紧随 FROM子句
mysql> SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90;
+-------------+-----------+---------+---------------+
| employee_id | last_name | job_id | department_id |
+-------------+-----------+---------+---------------+
| 100 | King | AD_PRES | 90 |
| 101 | Kochhar | AD_VP | 90 |
| 102 | De Haan | AD_VP | 90 |
+-------------+-----------+---------+---------------+
3 rows in set (0.01 sec)
mysql>