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文件

CREATE DATABASE mysql_shiyan;

use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10',
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL,
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone),
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
 );
 
CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
 );

#INSERT INTO department(dpt_name,people_num) VALUES('\u90e8\u95e8',\u4eba\u6570);

INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
INSERT INTO department(dpt_name,people_num) VALUES('dpt2',12);
INSERT INTO department(dpt_name,people_num) VALUES('dpt3',10);
INSERT INTO department(dpt_name,people_num) VALUES('dpt4',15);


#INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(\u7f16\u53f7,'\u540d\u5b57',\u5e74\u9f84,\u5de5\u8d44,\u7535\u8bdd,'\u90e8\u95e8');

INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(01,'Tom',26,2500,119119,'dpt4');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(02,'Jack',24,2500,120120,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(03,'Rose',22,2800,114114,'dpt3');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(04,'Jim',35,3000,100861,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(05,'Mary',21,3000,100101,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(06,'Alex',26,3000,123456,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(07,'Ken',27,3500,654321,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(08,'Rick',24,3500,987654,'dpt3');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(09,'Joe',31,3600,110129,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(10,'Mike',23,3400,110110,'dpt4');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(11,'Jobs',3600,019283,'dpt2');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(12,'Tony',3400,102938,'dpt3');






#INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(\u7f16\u53f7,'\u5de5\u7a0b\u540d','\u5f00\u59cb\u65f6\u95f4','\u7ed3\u675f\u65f6\u95f4','\u90e8\u95e8\u540d');

INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(01,'proj_a','2015-01-15','2015-01-31','dpt2');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(02,'proj_b','2015-01-15','2015-02-15','dpt1');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(03,'proj_c','2015-02-01','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(04,'proj_d','2015-02-15','2015-04-01','dpt3');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(05,'proj_e','2015-02-25','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(06,'proj_f','2015-02-26','2015-03-01','dpt2');