[MySQL优化2]不用SELECT * FROM table;

假设有一张employees表，它有8列：员工人数，姓氏，名字，分机，电子邮件，办公室代码，报告，职位等。如果要仅查看员工的名字，姓氏和职位，请使用以下查询：SELECT lastname, firstname, jobtitle FROM employees;而不是使用SELECT * FROM employees;
①使用星号(*)可能会返回不使用的列的数据。 它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
②如果明确指定列，则结果集更可预测并且更易于管理。 想象一下，当您使用星号(*)并且有人通过添加更多列来更改表格数据时，将会得到一个与预期不同的结果集。
③使用星号(*)可能会将敏感信息暴露给未经授权的用户。

mysql> select lastname, firstname, jobtitle from employees;
+-----------+-----------+----------------------+
| lastname | firstname | jobtitle |
+-----------+-----------+----------------------+
| Murphy | Diane | President |
| Patterson | Mary | VP Sales |
| Firrelli | Jeff | VP Marketing |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)

 

mysql> select lastname, firstname, jobtitle from employees where jobtitle = 'Sales rep';
+-----------+-----------+-----------+
| lastname | firstname | jobtitle |
+-----------+-----------+-----------+
| Jennings | Leslie | Sales Rep |
| Thompson | Leslie | Sales Rep |
+-----------+-----------+-----------+
17 rows in set (0.00 sec)

 

字段名不分大小写jobtitle = 'Sales rep'和jobtitle = 'sales rep是一样的
sql> select lastname,firstname,jobtitle from employees where jobtitle = 'sales rep' and officeCode = 1;
+----------+-----------+-----------+
| lastname | firstname | jobtitle |
+----------+-----------+-----------+
| Jennings | Leslie | Sales Rep |
| Thompson | Leslie | Sales Rep |
+----------+-----------+-----------+
2 rows in set (0.01 sec)

 

like的使用
mysql> select lastname, firstname, jobtitle from employees where jobtitle like 'VP%';
+-----------+-----------+--------------+
| lastname | firstname | jobtitle |
+-----------+-----------+--------------+
| Patterson | Mary | VP Sales |
| Firrelli | Jeff | VP Marketing |
+-----------+-----------+--------------+
2 rows in set (0.00 sec)

like 'vp%'表示以vp开头
like '%ing'表示以ing结尾
like 'ket'表示包含ket