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

 1 #INSERT INTO department(dpt_name,people_num) VALUES('u90e8u95e8',u4ebau6570);
 2 
 3 INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
 4 INSERT INTO department(dpt_name,people_num) VALUES('dpt2',12);
 5 INSERT INTO department(dpt_name,people_num) VALUES('dpt3',10);
 6 INSERT INTO department(dpt_name,people_num) VALUES('dpt4',15);
 7 
 8 
 9 #INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(u7f16u53f7,'u540du5b57',u5e74u9f84,u5de5u8d44,u7535u8bdd,'u90e8u95e8');
10 
11 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(01,'Tom',26,2500,119119,'dpt4');
12 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(02,'Jack',24,2500,120120,'dpt2');
13 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(03,'Rose',22,2800,114114,'dpt3');
14 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(04,'Jim',35,3000,100861,'dpt1');
15 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(05,'Mary',21,3000,100101,'dpt2');
16 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(06,'Alex',26,3000,123456,'dpt1');
17 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(07,'Ken',27,3500,654321,'dpt1');
18 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(08,'Rick',24,3500,987654,'dpt3');
19 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(09,'Joe',31,3600,110129,'dpt2');
20 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(10,'Mike',23,3400,110110,'dpt4');
21 INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(11,'Jobs',3600,019283,'dpt2');
22 INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(12,'Tony',3400,102938,'dpt3');
23 
24 
25 
26 
27 
28 
29 #INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(u7f16u53f7,'u5de5u7a0bu540d','u5f00u59cbu65f6u95f4','u7ed3u675fu65f6u95f4','u90e8u95e8u540d');
30 
31 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(01,'proj_a','2015-01-15','2015-01-31','dpt2');
32 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(02,'proj_b','2015-01-15','2015-02-15','dpt1');
33 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(03,'proj_c','2015-02-01','2015-03-01','dpt4');
34 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(04,'proj_d','2015-02-15','2015-04-01','dpt3');
35 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(05,'proj_e','2015-02-25','2015-03-01','dpt4');
36 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(06,'proj_f','2015-02-26','2015-03-01','dpt2');

View Code