一、数据导入指令:source 类的全路径
在命令行客户端登录mysql,使用 source 指令导入
mysql> source d:\mysqldb.sql
二、列的别名
- 重命名一个列
- 便于计算
- 紧跟列名,也可以在列名和别名之间加入关键字AS,别名使用双引号,以便在别名中包含空格或特殊的字符并区分大小写。
- AS 可以省略
- 建议别名简短,见名知意
举例
SELECT last_name AS name, commission_pct comm
FROM employees;
三、去除重复行
- 默认情况下,查询会返回全部行,包括重复行。
SELECT department_id
FROM employees;
- 在SELECT语句中使用关键字 DISTINCT 去除重复行
SELECT DISTINCT department_id
FROM employees;
- DISTINCT 需要单独使用,否则无实际意义
错误案例:(同时对两个列去重)
SELECT DISTINCT department_id,salary
FROM employees;
四、空值参与运算
- 所有运算符或列值遇到null值,运算的结果都为null
- null不是空,也不是0
五、着重号
- 错误的
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> SELECT * FROM `ORDER`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
- 结论
- 我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。
如果有,要在SQL语句中使用一对``(着重号)引起来。
六、显示表结构
使用DESCRIBE 或 DESC 命令,显示表结构。
DESCRIBE employees;
或
DESC employees;
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)