直至数据库管理阶段,大多数数据库都使用相同

作者: www.9159.com  发布:2019-11-09

1.数据库基本概念:

 

一.常用数据库软件

  • 大型数据库
  • Oracle Oracle是著名的Oracle(甲骨文)公司的数据库产品 , 它是世界上第一个商品化的关系型数据库管理系统 , 也是第一个推出和数据库结合的第四代语言开发工具的数据库产品。Oracle 公司的软件产品丰富 , 包括 Oracle 服务器产品 , Oracle 开发工具和Oracle 应用软件。其中最著名的就是 Oracle 数据库 , 目前最新的版本是 Oracle11g。
  • DB2 DB2 是 IBM 的关系型数据库管理系统 , DB2 有很多丌同的版本 , 可
    以运行在从掌上产品到大型机丌同的终端机器上。DB2 在高端数据库的主要竞争对手是 Oracle。
  • Sybase Sybase是美国Sybase公司研制的一种关系型数据库系统 , 是较早采用C/S 技术的数据库厂商 , 是一种典型的 UNIX 或 Windows NT 平台上客户机/服务器环境下 的大型数据库系统 , 在国内大中型系统中具有广泛的应用。
  • 中小型数据库
  • Sql Server Microsoft SQL Server 是运行在 Windows NT 服务器上 , 支持 C/S结构的数据库 管理系统。它采用标准 SQL 语言。 Mysql MySQL是一个小型关系型数据库管理系统 , 开发者为瑞典 MySQL AB公司。在 2008年 1 月 16 号被 Sun 公司收购。而 2009 年 SUN 又被 Oracle 收购。
  • Mysql MySQL 体积小、速度快、总体拥有成本低 , 尤其是开放源码 , 许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
  • 小型数据库
    Access Microsoft Office Access( 前名 Microsoft Access )是由微软发布的关联式数据库管理系统 , 是 Microsoft Office 的成员之一。

目录

数据库(Database, DB)
数据库管理系统(Database Management System, DBMS)
数据库管理员( Database Administrator, DBA)
数据库系统( Database System, DBS )

1.1.1. 文件存储

对数据的存储需求一直存在。保存数据的方式,经历了手工管理、文件管理等阶段,直至数据库管理阶段。

文件存储方式保存数据的弊端:

  • 缺乏对数据的整体管理,数据不便修改;
  • 不利于数据分析和共享;
  • 数据量急剧增长,大量数据不可能存在长期保存在文件中。

数据库应运而生,是人们存放数据、访问数据、操作数据的存储仓库。

 

二.RDBMS( Relational Database Management System )

关系型数据库管理系统是数据库软件中用来操纵和管理数据库的部分 , 用亍建立、使用和维护数据库 , 简称 rdbms。它对数据进行统一的管理和控制 , 以保证数据的安全性和完整性。

  • 什么是SQL?
  • DDL(Data Definition Language,数据定义语言)
    • 创建数据库
    • 创建表
    • 删除表
    • 更新表
  • DML(Data Manipulation Language,数据操纵语言)
    • 基本语句
      • 数据查询
      • 查询条件
      • 分组聚合
      • 数据排序
      • 数据插入
      • 数据删除
      • 数据更新
    • 进阶语句
      • 视图
      • 子查询
      • 函数
      • 谓词
      • CASE表达式
      • 表的加减法
      • 以列为单位对表进行联结
  • DCL(Data Control Language,数据控制语言)
    • 创建事务(START TRANSACTION) - 提交处理
    • 取消处理

2.关系型数据库:

1.1.2. DB和DBMS

数据库(Database,简称DB)是按照数据结构来组织、存储和管理数据的仓库。

数据库管理系统(Database Management System,简称DBMS):管理数据库的软件。

数据库建立了数据之间的联系,使用结构化的方式组织和处理数据,能够统一、集中及独立的管理数据,使数据的存取独立于使用数据的程序,实现了数据共享。

数据库的典型特征包括:数据的结构化,数据间的共享,减少数据的冗余度,以及数据的独立性。

数据库成为数据的知识仓库,并对这些数据的存储、访问、安全、数据一致性、并发操作及备份恢复负责。

 

三.SQL(Structured Query Language)

SQL( Structured Query Language )语言是用来在关系数据库上执行数据操作、检索及维护所使用的标准语言 , 是一个综合的、通用的关系数据库语言。大多数数据库都使用相同或者相似的语言来操作和维护数据库。SQL 语言可以用来查询数据 , 操纵数据 , 定义数据 , 控制数据 , 使软件开发人员、数据库管理员都可以通过 SQL 语言对数据库执行特定的操作。

什么是SQL?

SQL是Structured Query Language的缩写,意思是结构化查询语言,是一种在数据库管理系统(Relational Database Management System, RDBMS)中查询数据,或通过RDBMS对数据库中的数据进行更改的语言

常见的RDBMS有:

  • Oracle Database:甲骨文公司的RDBMS
  • SQL Server :微软公司的RDBMS
  • DB2:IBM 公司的RDBMS
  • PostgreSQL:开源的RDBMS
  • MySQL :开源的RDBMS

注:不同RDBMS的SQL语言略有不同

执行原理

用户在客户端通过SQL语言,将需要的数据和对数据进行的操作的请求发送给RDBMS,RDBMS 根据该语句的内容返回所请求的数据,或者对存储在数据库中的数据进行更新。

根据对RDBMS 赋予的指令种类的不同,SQL 语句可以分为以下三类:

  • DDL(Data Definition Language,数据定义语言)
    • CREATE: 创建数据库和表等对象
    • DROP: 删除数据库和表等对象
    • ALTER: 修改数据库和表等对象的结构
  • DML(Data Manipulation Language,数据操纵语言)
    • SELECT:查询表中的数据
    • INSERT:向表中插入新数据
    • UPDATE:更新表中的数据
    • DELETE:删除表中的数据
  • DCL(Data Control Language,数据控制语言)
    • COMMIT: 确认对数据库中的数据进行的变更
    • ROLLBACK: 取消对数据库中的数据进行的变更
    • GRANT: 赋予用户操作权限
    • REVOKE: 取消用户的操作权限

Oracle Oralce、IBM DB2、MS SQL /Server、SyBase SyBase、IBM Informix、MySQL、Access

1.1.3. 关系数据库简介

关系是一个数学概念,描述两个元素间的关联或对应关系。所以关系型数据库,即是使用关系模型把数据组织到数据表(Table)中。现实世界可以用数据来描述。

主流数据库产品:

  • Oracle(Oracle)
  • DB2(IBM)
  • SQL Server(MS)
  • MySQL(Oracle)

四.DBA( Database Administrator )

数据库管理员

DDL(Data Definition Language,数据定义语言)

3.SQL的简介:

1.1.4. 表的概念

在关系数据库中,数据被存放于二维数据表(Table)中。

一个关系数据库由多个数据表组成,数据表是关系数据库的基本存储结构,由行和列组成,行(Row)也就是横排数据,也经常被称作记录(Record),列(Column)就是纵列数据,也被称作字段(Field)。表和表之间是存在关联关系的。

 

五.Table( 表 )

表是数据库存储的基本单元 , 对应亍现实世界中的实体对象 , 比如部门、职员等 , 表是一个二维结构 , 由行和列组成 , 横向为行(Row) , 也叫记(Record) , 用来表示实体的数据 , 比如一个职员的相关信息。纵向为列(Column) , 也叫作字段(Filed) , 用来表示实体的属性 , 比如职员的薪水。在软件开发技术比如 Java 中 , 现实世界的实体使用对象来描述 , 所以数据表和对象之间存在一种对应关系。数据表相当亍类(Class) , 数据表的行就是某个对象的实例(Instance) ,其中每个列都是对象实例的属性(Field)。

创建数据库

CREATE DATABASE shop;

SQL 全名是结构化查询语言(Structured Query Language)

1.2. 主流关系型数据库

 

六.Oracle支持的数据类型

  • 数字
  • number(n) 数字( 最长 n 位 )
  • number(n , m) 浮点数( 总长 n 为 , 小数点后 m 位 )
  • 例:number(7,2) 表示最大数为 99999.99
  • 字符串
  • char(n) 表示定长字符串( 方便查询 )最长放入 n 个字符 , 放入的数据如果不够 n 个字符则补空格 , 无论如何都占 n 个字符长度。
  • varchar(n) 表示变长字符串( 节省空间 )最长放入 n 个字符 , 放入的数据是几个长度就占多大空间
  • varchar2(n) Oracle 自己定义的变长字符串
  • 日期
  • date 日期
  • 二进制
  • BLOB 二进制数据 最大长度4G

创建表

CREATE TABLE Product(product_id     CHAR      NOT NULL, product_name   VARCHAR NOT NULL, product_type   VARCHAR  NOT NULL, sale_price     INTEGER      , purchase_price INTEGER      , regist_date    DATE         , PRIMARY KEY (product_id));

每一列的数据类型是必须要指定的,数据类型包括:

  • INTEGER 整数型
  • NUMERIC ( 全体位数, 小数位数)
  • CHAR 定长字符串
  • VARCHAR 可变长字符串
  • DATE 日期型

DML语句(数据操作语言)Insert、Update、 Delete、Merge(SQL Server、Oracle数据库中可用,MySQL、PostgreSQL中不可用)
DDL语句(数据定义语言)Create、Alter、 Drop、Truncate
DCL语句(数据控制语言)Grant、Revoke
事务控制语句Commit 、Rollback、Savepoint

1.2.1. Oracle数据库概述

Oracle是当今著名的Oracle(甲骨文)公司的数据库产品,它是世界上第一个商品化的关系型数据库管理系统,也是第一个推出和数据库结合的第四代语言开发工具的数据库产品。

Oracle采用标准的SQL结构化查询语言,支持多种数据类型,提供面向对象的数据支持,具有第四代语言开发工具,支持UNIX、 WINDOWS、OS/2等多种平台。Oracle公司的软件产品丰富,包括Oracle服务器产品,Oracle开发工具和Oracle应用软件。其中 最著名的就是Oracle数据库,目前最新的版本是Oracle 12c。

 

七.练习Sql语句

  • 1.建表
--建表
-- 表名不能超过 30 个字符
-- 表名、列名是自由定义的
-- 所有的 SQL 语句都是以“ ; ”结尾
CREATE TABLE user_test(
            id  number(4),
            password char(6),
            name char(20),
            phone char(11),
            email varchar(20)
);
  • 2.修改表格的列
alter table user_test modify email varchar(50);
  • 3插入数据
insert into user_test values(1001,'123456','张三','13468857714','linglongxin24@163.com');

*4. 查询数据

SELECT * from user_test;
  • 5.数据准备
  • 创建部门表并插入数据
CREATE TABLE dept_test(
            dept_id NUMBER(2),
            dept_name CHAR(20),
            dept_location CHAR(20)
);

desc dept_test;

INSERT INTO dept_test VALUES(10,'developer','beijing');

INSERT INTO dept_test VALUES(20,'account','shanghai');

INSERT INTO dept_test VALUES(30,'sales','guangzhou');

INSERT INTO dept_test VALUES(40,'operations','tianjin');

SELECT * FROM dept_test;
  • 创建员工表并插入数据
CREATE TABLE emp_test(

            emp_id NUMBER(4),
            name VARCHAR(20),
            job VARCHAR(20),
            salary NUMBER(7,2),
            bonus NUMBER(7,2),
            hire_date DATE,
            manager NUMBER(4),
            dept_test_id NUMBER(2)
);

DESC emp_test;

SELECT * FROM EMP_TEST;

INSERT INTO emp_test VALUES(1001,'张无忌','Manager','10000','2000',TO_DATE('2010-01-12','yyyy-mm-dd'),1005,10);
INSERT INTO emp_test VALUES(1002,'刘苍松','Analyst', 8000 , 1000 ,TO_DATE('2011-01-12','yyyy-mm-dd'),1001,10);
INSERT INTO emp_test VALUES(1003,'李翊' ,'Analyst',9000 , 1000 ,TO_DATE('2010-02-11','yyyy-mm-dd'),1001,10);
INSERT INTO emp_test VALUES(1004,'郭芙蓉','Programmer',5000, NULL ,TO_DATE('2010-02-11','yyyy-mm-dd'),1001,10);

INSERT INTO emp_test VALUES(1005 , '张三丰' , 'President' ,15000 , NULL ,TO_DATE('2008-02-15','yyyy-mm-dd'),NULL,20);
INSERT INTO emp_test VALUES(1006 , '燕小六' , 'Manager' ,5000 ,400 , '01-FEB-09' , 1005 , 20);
insert into emp_test values(1007 , '陆无双' , 'clerk' ,3000 , 500 , '01-FEB-09' , 1006 , 20) ;
insert into emp_test values(1008 , '黄蓉' , 'Manager' ,5000 , 500 , '1-MAY-09' , 1005 , 30) ;
insert into emp_test values(1009 , '韦小宝' , 'salesman' ,4000 , null , '20-FEB-09' , 1008 , 30) ;
insert into emp_test values(1010 , '郭靖' , 'salesman' ,4500 , 500 , '10-MAY-09' , 1008 , 30) ;



SELECT * FROM emp_test;
  • 6.练习基本查询语句
--计算员工的名字、月薪和年薪?
SELECT name,salary,salary*12 year_salary
    FROM emp_test;

--计算员工的月收入?
--空值和任何数据做算数运算 , 结果为空(null)
--正确写法:如果 bunus 的值是 null , 则取 0
SELECT name,salary,bonus,salary+ nvl(bonus,0) month_salary
    FROM emp_test;

--指定字段揑入值
    INSERT INTO emp_test (emp_id,name) VALUES(1011,'于泽成');

--查询 emp_xxx 表 , 如果没有职位 , 显示'no position' , 如果有职位 , 显示员工的职位
SELECT name,NVL(job,'no positon') job
            FROM emp_test;

--查询 emp_xxx 表 , 如果没有入职时间 , 显示为 2016-12-12 , 否则原样显示
SELECT name,NVL(hire_date,TO_DATE('2016-12-12','yyyy-mm-dd')) hire_date
            FROM emp_test;

--连接字符串用CONCAT(str1,str2,...)和Oracle有区别,Oracle用||
SELECT emp_id,name||' job is '||job detail
            FROM emp_test;
--复制表
CREATE TABLE emp_test2 AS SELECT * FROM emp_test;
SELECT * FROM emp_test2;

--DISTINCT注意:distinct 必须( 只能 )跟在 select 后边,distinct 指所有列的唯一组合
--机构中有多少种职位?
SELECT DISTINCT job FROM emp_test;
--员工分布在哪些部门?
SELECT DISTINCT dept_test_id FROM emp_test;
--查询每个部门不重复的职位
SELECT DISTINCT job,dept_test_id FROM emp_test;

--薪水高亍 10000 元的员工数据?
SELECT * FROM emp_test WHERE salary>10000; 

--职位是 Analyst 的员工数据?SQL 语句大小写丌敏感 , 数据大小写敏感
SELECT * FROM emp_test WHERE LOWER(job)='analyst'; 

--薪水大亍 5000 并且小亍 10000 的员工数据?>=<=;between and
SELECT * FROM emp_test WHERE salary>=5000 AND salary<=10000; 
SELECT * FROM emp_test WHERE salary BETWEEN 5000 AND 10000; 

--入职时间在 2011 年的员工?
--错误写法
SELECT * FROM emp_test WHERE hire_date=TO_DATE('2011','yyyy'); 
--正确写法
SELECT * FROM emp_test WHERE hire_date BETWEEN TO_DATE('2011-01-01','yyyy-mm-dd') AND TO_DATE('2011-12-31','yyyy-mm-dd') ; 


--列出职位是 Manager 或者 Analyst 的员工
SELECT * FROM emp_test WHERE job IN('Manager','Analyst');

--列出职位中包含有 sales 字符的员工数据?
SELECT * FROM emp_test WHERE LOWER(job) LIKE '%sales%';

--列出职位中第二个字符是 a 的员工数据?
SELECT * FROM emp_test WHERE LOWER(job) LIKE '_a%' ;

--查询数据库中有多少个名字中包含 'EMP' 的表?
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME LIKE '%EMP%';


SELECT * FROM emp_test;

-- 查询哪些员工没有奖金?
SELECT * FROM emp_test WHERE bonus IS NULL;

--薪水丌在 5000 至 8000 的员工?
SELECT * FROM emp_test WHERE salary NOT BETWEEN 5000 AND 8000;

--不是部门 20 和部门 30 的员工?
SELECT * FROM emp_test WHERE dept_test_id NOT IN(20,30);

insert into emp_test values(1012 , 'text_test' , 'salesman' ,4500 , 500 , TO_DATE('2011-01-01','yyyy-mm-dd') , 1008 , 30) ;
--如果要查询的数据中有特殊字符( 比如_或% ),
-- 在做模糊查询时 ,
-- 需要加上符号表示转义 , 如果是Oracle并且用 escape 短语指明转义字符mysql不用
SELECT name FROM emp_test WHERE name LIKE '%_%' ESCAPE '' ;

删除表

DROP TABLE Product;

4.SQL常用的一些字符:

1.2.2. DB2数据库概述

DB2是IBM的关系型数据库管理系统,DB2有很多不同的版本,可以运行在从掌上产品到大型机不同的终端机器上。DB2 Universal Database Personal Edition和DB2 Universal Database Workgroup Edition分别是为OS/2和Windows系统的单用户和多用户提供的数据库管理系统。

DB2在高端数据库的主要竞争对手是Oracle。

 

更新表

-- 在表中增加一列(ADD COLUMN)ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR;-- 在表中删除一列(DROP COLUMN)ALTER TABLE Product DROP COLUMN product_name_pinyin;-- 变更表名RENAME TABLE Poduct to Product;

4.1 连接运算符 ||
4.2 使用字段别名 as
4.3 去除重复行 distinct
4.4 比较运算符 > < (!= or <>) between and
4.5 查询结果排序 order by asc(desc)
4.6 模糊查询 like
(
% 表示零或多个字符
_ 表示一个字符
对于特殊符号可使用ESCAPE 标识符来查找
escape表示*后面的那个符号不当成特殊字符处理,就是查找普通的_符号
)
4.7 逻辑运算符 or and not
4.8 in操作 not in

1.2.3. Sybase数据库

Sybase是美国Sybase公司研制的一种关系型数据库系统,是较早采用C/S技术的数据库厂商,是一种典型的UNIX或Windows NT平台上客户机/服务器环境下的大型数据库系统。 Sybase通常与Sybase SQL Anywhere用于客户机/服务器环境,前者作为服务器数据库,后者为客户机数据库,采用该公司研制的PowerBuilder为开发工具,在国内大中 型系统中具有广泛的应用。

SYBASE主要有三种版本,一是UNIX操作系统下运行的版本,二是Novell Netware环境下运行的版本,三是Windows NT环境下运行的版本。对UNIX操作系统目前广泛应用的为SYBASE 10 及SYABSE 11 for SCO UNIX。

2010年Sybase被SAP收购。

 

DML(Data Manipulation Language,数据操纵语言)

1.2.4. SQL Server数据库概述

Microsoft SQL Server是运行在Windows NT服务器上,支持C/S结构的数据库管理系统。它采用标准SQL语言,微软公司对它进行了部分扩充而成为事务SQL(Transact-SQL)。

SQL Server最早是微软为了要和IBM竞争时,与Sybase合作所产生的,其最早的发展者是Sybase,和Sybase数据库完全兼容。在与 Sybase终止合作关系后,微软自主开发出SQL Server 6.0版,往后的SQL Server即均由微软自行研发。最新的版本是SQL Server 2012,上一版本是2008。

Microsoft SQL Server几个初始版本适用于中小企业的数据库管理,但是后来它的应用范围有所扩展,已经触及到大型、跨国企业的数据库管理。

 

基本语句

1.2.5. MySQL数据库概述

MySQL是一个开放源码的小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。

与其它的大型数据库例如Oracle、IBM DB2等相比,MySQL自有它的不足之处,如规模小、功能有限等,但对于一般个人使用者和中小型企业来说,MySQL提供的功能已经绰绰有余,而且由于 MySQL是开放源码软件,因此可以大大降低总体拥有成本,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

2008年1月16日,Sun正式收购MySQL。2009年4月20日,SUN被Oracle公司收购 。目前的最新版本是MySQL5.6.

 

数据查询

-- 查询出所有的列SELECT * FROM Product;-- 从表中查询出需要的列SELECT product_id, product_name, purchase_price  FROM Product;-- 为列设定别名SELECT product_id AS id,       product_name AS name,       purchase_price AS “价格”  FROM Product;-- 将查询出的一列指定为常数SELECT '2009-02-24' AS date, product_id, product_name  FROM Product;-- 从查询结果中删除重复行SELECT DISTINCT product_type  FROM Product;

1.3. SQL概述

查询条件

SELECT product_name, product_type  FROM Product; WHERE product_type = '衣服';

1.3.1. 结构化查询语言

SQL(Structured Query Language) 是结构化查询语言的缩写。

SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言,可以用来查询数据,操纵数据,定义数据,控制数据,所有数据库都使用相同或者相似的语言。

SQL可分为:

  • 数据定义语言(DDL) : Data Definition Language
  • 数据操纵语言(DML) : Data Manipulation Language
  • 事务控制语言(TCL):Transaction Control Language)
  • 数据查询语言(DQL):Data Query Language
  • 数据控制语言(DCL) : Data Control Language

执行SQL语句时,用户只需要知道其逻辑含义,而不需要知道SQL语句的具体执行步骤。

 

算数运算符

  • 加 +
  • 减 -
  • 乘 *
  • 除 /
SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2"  FROM Product;

1.3.2. 数据定义语言(DDL)

用于建立、修改、删除数据库对象,包括创建语句(CREATE)、修改语句(ALTER)、删除语句(DROP),比如使用CREATE TABLE创建表,使用ALTER TABLE修改表,使用DROPTABLE删除表等动作。这类语言不需要事务的参与,自动提交。

 

比较运算符

  • 等于 =
  • 不等于 <>
  • 大于 >
  • 大于等于 >=
  • 小于 <
  • 小于等于 <=
SELECT product_name, product_type, regist_date  FROM Product WHERE regist_date < '2009-09-27'; -- 将算数运算符和比较运算符结合使用SELECT product_name, sale_price, purchase_price  FROM Product WHERE sale_price - purchase_price >= 500; -- 不能对NULL使用比较运算符,正确的方法是SELECT product_name, purchase_price  FROM Product WHERE purchase_price IS NULL;SELECT product_name, purchase_price  FROM Product WHERE purchase_price IS NOT NULL;

1.3.3. 数据操作语言(DML)

用于改变数据库数据,包括INSERT、UPDATE、DELETE三条语句。其中,INSERT语句用于将数据插入到数据库 中,UPDATE语句用于更新数据库中已存在的数据,DELETE用于删除数据库中已存在的数据。DML语言和事务是相关的,执行完DML操作后必须经过 事务控制语句提交后才真正的将改变应用到数据库中。

 

逻辑运算符

  • NOT

    (也就是sale_price<1000)

SELECT product_name, product_type, sale_priceFROM ProductWHERE NOT sale_price >= 1000; 
  • AND

    AND运算符在其两侧的查询条件都成立时整个查询条件才成立,其意思相当于“并且”。

SELECT product_name, purchase_price  FROM Product WHERE product_type = '厨房用具'   AND sale_price >= 3000;
  • OR

    运算符在其两侧的查询条件有一个成立时整个查询条件都成立,其意思相当于“或者”。

SELECT product_name, purchase_price  FROM Product WHERE product_type = '厨房用具'    OR sale_price >= 3000;

1.3.4. 数据控制语言(TCL)

用来维护数据一致性的语句,包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句,其中COMMIT 用来确认已经进行的数据库改变, ROLLBACK语句用来取消已经进行的数据库改变,当执行DML操作后(也就是上面说的增加、修改、删除等动作),可以使用COMMIT语句来确认这种 改变,或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点,使当前的事务可以回退到指定的保存点,便于取消部分改变。

 

分组聚合

  • 常用的五个聚合函数:
    • COUNT: 计算表中的记录数
    • SUM: 计算表中数值列中数据的合计值
    • AVG: 计算表中数值列中数据的平均值
    • MAX: 求出表中任意列中数据的最大值
    • MIN: 求出表中任意列中数据的最小值
SELECT product_type, COUNT  FROM Product GROUP BY product_type; -- 计算某一列的行数SELECT COUNT(purchase_price)  FROM Product;  -- 计算删除重复数据后的行数SELECT COUNT(DISTINCT product_type)  FROM Product;  -- SUM/AVG函数只能对数值类型的列使用,而MAX/MIN函数原则上可以适用于任何数据类型的列SELECT MAX(regist_date), MIN(regist_date)  FROM Product;
  • GROUP BY和WHERE并用时SELECT语句的执行顺序:

    FROM → WHERE → GROUP BY → SELECT

  SELECT purchase_price, COUNT    FROM Product   WHERE product_type = '衣服'GROUP BY purchase_price;
  • 为聚合结果指定条件
  SELECT product_type, COUNT    FROM ProductGROUP BY product_type  HAVING COUNT = 2;

1.3.5. 数据查询语言(DQL)

用来查询所需要的数据。使用最广泛,语法灵活复杂。

 

数据排序

  • 子句的书写顺序

    SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

  • 子句的执行顺序:

    FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

  SELECT product_id, product_name, sale_price, purchase_price    FROM ProductORDER BY sale_price;
  • 升序或降序 注意:默认升序
  SELECT product_id, product_name, sale_price, purchase_price    FROM ProductORDER BY sale_price DESC;

1.3.6. 数据控制语言(DCL)

用于执行权限的授予和收回操作、创建用户等,包括授予(GRANT)语句,收回(REVOKE)语句,CREATE USER语句,其中GRANT用于给用户或角色授予权限, REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与,是自动提交的。

 

数据插入

-- 包含列清单INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');-- 省略列清单START TRANSACTION; INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');COMMIT;-- 从其他表中复制数据INSERT INTO ProductCopy (product_id, product_name, product_type,sale_price, purchase_price, regist_date)SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date  FROM Product;

数据删除

-- 清空表DELETE FROM Product;-- 指定删除对象(搜索型DELETE)DELETE FROM Product WHERE sale_price >= 4000;

数据更新

-- 更新整列UPDATE Product   SET regist_date = '2009-10-10';   -- 指定条件的更新(搜索型UPDATE)UPDATE Product   SET sale_price = sale_price * 10 WHERE product_type = '厨房用具'; -- 多列更新UPDATE Product   SET sale_price = sale_price * 10,       purchase_price = purchase_price / 2 WHERE product_type = '厨房用具';

进阶语句

视图

注意:定义视图时不能使用ORDER BY子句

-- 创建视图(CREATE VIEW)CREATE VIEW ProductSum (product_type, cnt_product)AS  SELECT product_type, COUNT    FROM ProductGROUP BY product_type; -- 使用视图SELECT product_type, cnt_product  FROM ProductSum; -- 删除视图(DROP VIEW)DROP VIEW ProductSum;

子查询

-- 在FROM子句中直接书写定义视图的SELECT语句SELECT product_type, cnt_product  FROM   (  SELECT product_type, COUNT AS cnt_product       FROM Product   GROUP BY product_type   ) AS ProductSum;  -- 在WHERE子句中使用标量子查询SELECT product_id, product_name, sale_price  FROM Product WHERE sale_price > (SELECT AVG(sale_price)                       FROM Product);-- 关联子查询SELECT product_type, product_name, sale_price  FROM Product AS P1  WHERE sale_price > (SELECT AVG(sale_price)                       FROM Product AS P2                       WHERE P1.product_type = P2.product_type                   GROUP BY product_type);

注意:

  • 能够使用常数或者列名的地方,无论是SELECT 子句、GROUP BY 子句、HAVING 子句,还是ORDER BY 子句,几乎所有的地方都可以使用标量子查询。
  • 这里起到关键作用的就是在子查询中添加的WHERE 子句的条件。该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。

函数

函数大致可以分为以下几种。

  1. 算术函数(用来进行数值计算的函数)
  2. 字符串函数(用来进行字符串操作的函数)
  3. 日期函数(用来进行日期操作的函数)
  4. 转换函数(用来转换数据类型和值的函数)
  5. 聚合函数(用来进行数据聚合的函数)
  • 算数函数

    ABS —— 绝对值

    MOD —— 求余

    ROUND (对象数值, 保留小数的位数) —— 四舍五入

  • 字符串函数

    CONCAT (字符串1, 字符串2, 字符串3) —— 拼接

    LENGTH —— 字符串长度

    LOWER —— 小写

    UPPER —— 大写

    REPLACE (对象字符串,替换前的字符串,替换后的字符串) —— 替换

    SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)—— 截取

  • 日期函数

    CURRENT_DATE —— 当前日期

    CURRENT_TIME —— 当前时间

    CURRENT_TIMESTAMP —— 当前的日期和时间

    EXTRACT (日期元素 FROM 日期)

  • 转换函数

    CAST(转换前的值 AS 想要转换的数据类型)—— 类型转换

    COALESCE (数据1,数据2,数据3……) —— 将NULL转换为其他值

谓词

  • LIKE

  • BETWEEN

  • IS NULL和IS NOT NULL

  • EXIST和NOT EXIST

  • IN和 NOT IN

    注意:在使用IN 和NOT IN 时是无法选取出NULL 数据的。

CASE表达式

SELECT product_name,       CASE WHEN product_type = '衣服'            THEN CONCAT('A:', product_type)            WHEN product_type = '办公用品'            THEN CONCAT('B:', product_type)            WHEN product_type = '厨房用具'            THEN CONCAT('C:',product_type)            ELSE NULL       END AS abc_product_type  FROM Product;

表的加减法

  • 表的加法
SELECT product_id, product_name   FROM ProductUNIONSELECT product_id, product_name  FROM Product2;

通过UNION 进行并集运算时可以使用任何形式的SELECT 语句,WHERE、GROUP BY、HAVING 等子句都可以使用,但是ORDER BY 只能在最后使用一次。

注意:UNION会删去两个表中的重复记录。如果想保留重复记录,可以在UNION后面加ALL

  • 选取表中的公共部分(INTERSECT)

MySQL不支持INTERSECT

  • 表的减法

MySQL不支持EXCEPT

以列为单位对表进行联结

  • 内联结(INNER JOIN)
    SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price      FROM ShopProduct AS SPINNER JOIN Product AS P        ON SP.product_id = P.product_id;

像这样使用联结运算将满足相同规则的表联结起来时,WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用.

  • 外联结(OUTER JOIN)
         SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price           FROM ShopProduct AS SP LEFT OUTER JOIN Product AS P             ON SP.product_id = P.product_id;
  • 三张以上的表的联结
    SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,            P.sale_price,IP.inventory_quantity      FROM ShopProduct AS SP INNER JOIN Product AS P        ON SP.product_id = P.product_idINNER JOIN InventoryProduct AS IP        ON SP.product_id = IP.product_id     WHERE IP.inventory_id = 'P001';

DCL(Data Control Language,数据控制语言)

创建事务(START TRANSACTION) - 提交处理

START TRANSACTION;    -- 将运动T恤的销售单价降低1000日元    UPDATE Product       SET sale_price = sale_price - 1000     WHERE product_name = '运动T恤';    -- 将T恤衫的销售单价上浮1000日元    UPDATE Product       SET sale_price = sale_price + 1000     WHERE product_name = 'T恤衫';COMMIT;

取消处理

START TRANSACTION;    -- 将运动T恤的销售单价降低1000日元    UPDATE Product       SET sale_price = sale_price - 1000     WHERE product_name = '运动T恤';    -- 将T恤衫的销售单价上浮1000日元    UPDATE Product       SET sale_price = sale_price + 1000     WHERE product_name = 'T恤衫';ROLLBACK;

参考:

本文由9159.com发布于www.9159.com,转载请注明出处:直至数据库管理阶段,大多数数据库都使用相同

关键词: