MYSQL数据库-SELECT详解

将SQL文件导入数据库中

　　$   source /url/file_name.sql

=======================================================

SELECT基本格式：

　　$ SELECT col FROM t_name WHERE condition;

=======================================================

AND | OR:

 

=======================================================

IN | NOT IN: 筛选某列在或不在某个范围内的结果：

　　$ SELECT name,in_dpt From employee WHERE in_dpt IN('dpt3','dpt4');

=======================================================

通配符 _ | %,关键字LIKE和通配符一起使用

　　_代表一个未指定字符，%代表不定个未指定字符。

　　$ SELECT ... WHERE phone LIKE '1101__';

=======================================================

结果排序 ORDER BY，ASC是升序，DESC是降序

　　$ select col from t_name order by col_name desc;

=======================================================

SQL内置计算函数：

　　COUNT | SUM | AVG | MAX | MIN

COUNT函数可用于任何数据类型，而另四个函数都只能对数字类数据做计算。

=======================================================

通过子查询，可以处理多个表。

　　查询Tom所在部门的的部门名，以及该部门所作的工程数量。

　　$ select of_dpt,count(proj_name) as count_project from project where

　　$ of_dpt in (select in_dpt from employee where name = 'Tom');

======================================================

连接查询（JOIN）

如果需要显示多个表中的数据，就需要使用连接操作（JOIN）：

　　$ select id,name,people_num from employee,department where employee.in_dpt = department.dpt.dpt_name order by id;

等价于：

　　$ select id,name,people_num from employee JOIN department ON employee.in_dpt = department.dpt_name order by id;

 

SQL文件

 1 CREATE DATABASE mysql_shiyan;
 2 
 3 use mysql_shiyan;
 4 
 5 CREATE TABLE department
 6 (
 7   dpt_name   CHAR(20) NOT NULL,
 8   people_num INT(10) DEFAULT '10',
 9   CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
10  );
11 
12 CREATE TABLE employee
13 (
14   id      INT(10) PRIMARY KEY,
15   name    CHAR(20),
16   age     INT(10),
17   salary  INT(10) NOT NULL,
18   phone   INT(12) NOT NULL,
19   in_dpt  CHAR(20) NOT NULL,
20   UNIQUE  (phone),
21   CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
22  );
23  
24 CREATE TABLE project
25 (
26   proj_num   INT(10) NOT NULL,
27   proj_name  CHAR(20) NOT NULL,
28   start_date DATE NOT NULL,
29   end_date   DATE DEFAULT '2015-04-01',
30   of_dpt     CHAR(20) REFERENCES department(dpt_name),
31   CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
32  );

View Code