SQL入门

1.概念

•数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。
•数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
•注意:通常情况下,经常会用数据库来表示他们使用的数据库软件,这经常会引起混淆,确切的说,数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的。

1.1常见的数据库管理系统

•Oracle:目前比较成功的关系型数据库管理系统。运行稳定、功能齐全、性能超群、技术领先。主要应用在大型的企业数据库领域。
•DB2:IBM的产品。
•SQL Server:Microsoft的产品。软件界面友好、易学易用,在操作性和交互性方面独树一帜。
•PostgreSQL:加州大学伯克利分校以教学目的开发的数据库系统,支持关系和面向对象的数据库,属于自由数据库管理系统。
MySQL:免费的数据库系统。被广泛用于中小型应用系统。体积小、速度快、总体拥有成本低,开放源代码。2008年被SUN收购,2009年SUN被Oracle收购

1.2数据库服务器、数据库和表的关系
  • 所谓安装数据库服务器,只是在机器上装了一个数据库管理程序,这个管理程序可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。
  • 为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体的数据。
  • 数据库服务器、数据库和表的关系如图所示:


1.3数据在数据库中存储方式
1.3操作数据库
  • 数据库管理系统可以通过SQL(结构化查询语言)来定义和操作数据,维护数据的完整性和安全性,以及进行各种数据库的管理等。

2.SQL

2.1什么是SQL:
  • SQL:Structure Query Language。
  • SQL被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后来被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。
  • SQL优点:
    –不是某个特定数据库供应商专有的语言。几乎所有重要的数据库管理系统都支持SQL。
    –简单易学,该语言的语句都是由描述性很强的英语单词组成,且这些单词的数目不多。
    –高度非过程化,即用SQL操作数据库,只需指出“做什么”,无须指明“怎么做”,存取路径的选择和操作的执行由DBMS自动完成。
2.2SQL分类
  • DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
  • DML(Data Manipulation Language):数据操作语言,用来操作数据库表中的记录(数据);
  • DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
  • DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;

DDL:Data Definition Language
•作用:用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。
•常用关键字:
CREATE ALTER DROP

3.操作数据库

安装步骤省略,解析下命令操作

进入mysql指令
mysql -u root -p

数据库简单操作
(1)创建数据库:create database 数据库名称
(2)查看数据库:show databases;
(3)删除数据库:drop database 数据库名称
(4)打开数据库:use 数据库名称
(5)建表

// 创建表(personId设置了关键值则不能重复)
create table personTable (personId int, name varchar(20), gender varchar(10), primary key(personId)); 
// 查看表格
show tables
// 在表中添加新列
alter table personTable add age int;

// 查看表结构信息
desc personTable;

// 删除表
drop table personTable;

// 复制表
create table personInfo like personTable;

(6)增删改查 SQL语句等等
查询数据:select * from 表名 where 字段 = 值;
插入数据:insert into 表名 (字段1, ……) values (值1, ……);
更新数据:update 表名 字段=值,…,字段n=值n where 字段=值;
删除数据:delete from 表名 where 字段=值;

// 查看数据
select * from personInfo; // 查询全部
select * from personInfo where age = 35; // 条件查询
// 插入数据
insert into personInfo (personId, name, gender, age) values (1000, 'devZhang', "male", 35);
// 更新数据
update personInfo set age = 25,name = "uiChen",gender='female' where personId = 1001;
// 删除数据
delete from personInfo where age = 23;

(7)exit命令退出mysql:

exit
4.查询语句举例

查询所有列

SELECT * FROM stu;

查询指定列

SELECT sid, sname, age FROM stu;

查询性别为女,并且年龄50的记录

SELECT * FROM stu WHERE gender='female' AND ge<50;

查询学号为S_1001,或者姓名为liSi的记录

SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

查询学号为S_1001,S_1002,S_1003的记录

SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

查询学号不是S_1001,S_1002,S_1003的记录

SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL;

查询年龄在20到40之间的学生记录

SELECT * FROM stu WHERE age>=20 AND age<=40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

查询性别非男的学生记录

SELECT * FROM stu WHERE gender!='male';
或者
SELECT * FROM stu WHERE gender<>'male';
或者
SELECT * FROM stu WHERE NOT gender='male';

查询姓名不为null的学生记录

SELECT * FROM stu WHERE NOT sname IS NULL;
或者
SELECT * FROM stu WHERE sname IS NOT NULL;

查询姓名由5个字母构成的学生记录

SELECT * FROM stu WHERE sname LIKE '_____';
模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母,5个“_”表示5个任意字母。

查询姓名由5个字母构成,并且第5个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '____i

查询姓名中第2个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '_i%';

查询姓名中包含“a”字母的学生记录

SELECT * FROM stu  WHERE sname LIKE '%a%';

去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT:

SELECT DISTINCT sal FROM emp;

查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不是数值类型,那么会出错。

SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
SELECT *,sal+IFNULL(comm,0) FROM emp;

在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total:

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时,是可以省略AS关键字的:
SELECT *,sal+IFNULL(comm,0) total FROM emp;

查询所有学生记录,按年龄升序排序

SELECT * FROM stu ORDER BY sage ASC;
或者
SELECT * FROM stu ORDER BY sage;

查询所有学生记录,按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;
5. 聚合函数是用来做纵向运算的函数:
  • COUNT():统计指定列不为NULL的记录行数;
  • MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
  • MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
  • SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
  • AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
    COUNT
当需要纵向统计时可以使用COUNT()。
* 查询emp表中记录数:
SELECT COUNT(*) AS cnt FROM emp;

* 查询emp表中有佣金的人数:
SELECT COUNT(comm) cnt FROM emp;
注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。

* 查询emp表中月薪大于2500的人数:
SELECT COUNT(*) FROM emp
WHERE sal > 2500;
* 统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

* 查询有佣金的人数,以及有领导的人数:
SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM和AVG

当需要纵向求和时使用sum()函数。
* 查询所有雇员月薪和:
SELECT SUM(sal) FROM emp;

* 查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal), SUM(comm) FROM emp;

* 查询所有雇员月薪+佣金和:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

* 统计所有员工平均工资:
SELECT SUM(sal), COUNT(sal) FROM emp;
或者
SELECT AVG(sal) FROM emp;

MAX和MIN

* 查询最高工资和最低工资:
SELECT MAX(sal), MIN(sal) FROM emp;
6.分组查询

当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部分来分组。

* 查询每个部门的部门编号和每个部门的工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

* 查询每个部门的部门编号以及每个部门的人数:
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

* 查询每个部门的部门编号以及每个部门工资大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
6.HAVING 和 LIMIT 句子
* 查询工资总和大于9000的部门编号以及工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
  
    注:having与where的区别:
        1.having是在分组后对数据进行过滤.
          where是在分组前对数据进行过滤
        
2.having后面可以使用分组函数(统计函数)
          where后面不可以使用分组函数。
WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。

LIMIT用来限定查询结果的起始行,以及总行数。

* 查询5行记录,起始行从0开始
SELECT * FROM emp LIMIT 0, 5;

注意,起始行从0开始,即第一行开始!

8* 查询10行记录,起始行从3开始
SELECT * FROM emp LIMIT 3, 10;

* 分页查询
如果一页记录为10条,希望查看第3页记录应该怎么查呢?
第一页记录起始行为0,一共查询10行;
第二页记录起始行为10,一共查询10行;
第三页记录起始行为20,一共查询10行;
* 分页查询
    查询语句书写顺序:select – from- where- group by- having- order by-limit
    查询语句执行顺序
7.数据的完整性

作用:保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束

  • 完整性的分类:
  • 实体完整性:
  • 域完整性:
  • 引用完整性:
7.1 实体完整性

实体:即表中的一行(一条记录)代表一个实体(entity)
实体完整性的作用:标识每一行数据不重复。
约束类型: 主键约束(primary key) 唯一约束(unique) 自动增长列(auto_increment)
主键约束(primary key)
注:每个表中要有一个主键。
特点:数据唯一,且不能为null
例:

第一种添加方式:
CREATE TABLE student(
id int primary key,
name varchar(50)
);
第二种添加方式:此种方式优势在于,可以创建联合主键
CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid,stuid)
);
第三种添加方式:
CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student  ADD  PRIMARY  KEY (id);

唯一约束(unique): 特点:数据不能重复。

CREATE TABLE student(
  Id int primary key,
  Name varchar(50) unique
);

自动增长列(auto_increment)
sqlserver数据库 (identity) oracle数据库( sequence)

给主键添加自动增长的数值,列只能是整数类型
CREATE TABLE student(
Id int primary key auto_increment,
Name varchar(50)
);

INSERT INTO student(name) values(‘tom’);
7.3 域完整性

域完整性的作用:限制此单元格的数据正确,不对照此列的其它单元格比较
域代表当前单元格
域完整性约束:数据类型 非空约束(not null) 默认值约束(default)
check约束(mysql不支持)check(sex='男' or sex='女')
数据类型:(数值类型、日期类型、字符串类型)
非空约束:not null

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);

INSERT INTO student values(1,’tom’,null);

默认值约束 default

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10) default ‘男’
);

insert into student1 values(1,'tom','女');
insert into student1 values(2,'jerry',default);
7.2 引用完整性(参照完整性)

外键约束:FOREIGN KEY

例:
CREATE TABLE student(
sid int pirmary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);

create table score(
    id int,
    score int,
    sid int , -- 外键列的数据类型一定要与主键的类型一致
    CONSTRAINT fk_score_sid foreign key (sid) references student(id)
);
第二种添加外键方式。
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);
7.4 表与表之间的关系
  • 一对一:例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。设计从表可以有两种方案:
    在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束,
    给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。
  • 一对多(多对一):最为常见的就是一对多!一对多和多对一,这是从哪个角度去看得出来的。t_user和t_section的关系,从t_user来看就是一对多,而从t_section的角度来看就是多对一!这种情况都是在多方创建外键!
  • 多对多:例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。


8.多表查询

多表查询有如下几种:

  • 合并结果集;UNION 、 UNION ALL
  • 连接查询
    内连接 [INNER] JOIN ON
    外连接 OUTER JOIN ON
    (1) 左外连接 LEFT [OUTER] JOIN
    (2)右外连接 RIGHT [OUTER] JOIN
    (3)全外连接(MySQL不支持)FULL JOIN
    自然连接 NATURAL JOIN
    *子查询
8.1.合并结果集
  1. 作用:合并结果集就是把两个select语句的查询结果合并到一起!
  2. 合并结果集有两种方式:
 * UNION:去除重复记录,例如:SELECT * FROM t1 UNION SELECT * FROM t2;
 * UNION ALL:不去除重复记录,例如:SELECT * FROM t1 UNION ALL SELECT * FROM t2。
  1. 要求:被合并的两个结果:列数、列类型必须相同。
8.2 连接查询 (非常重要)

连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。



连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的,那么怎么去除重复的,不想要的记录呢,当然是通过条件过滤。通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。
你能想像到emp和dept表连接查询的结果么?emp一共14行记录,dept表一共4行记录,那么连接后查询出的结果是56行记录。
也就你只是想在查询emp表的同时,把每个员工的所在部门信息显示出来,那么就需要使用主外键来去除无用信息了



使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
// 在多表查询中,在使用列时必须指定列所从属的表,例如emp.deptno表示emp表的deptno列。

上面查询结果会把两张表的所有列都查询出来,也许你不需要那么多列,这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname  FROM emp,dept  WHERE emp.deptno=dept.deptno;

还可以为表指定别名,然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;

内连接
上面的连接语句就是内连接,但它不是SQL标准中的查询方式,可以理解为方言!SQL标准的内连接为:

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
INNER可以省略,MySQL默认的连接方式就是内连接
不使用WHERE,而是使用ON;

内连接的特点:查询结果必须满足条件。例如我们向emp表中插入一条记录:


其中deptno为50,而在dept表中只有10、20、30、40部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足e.deptno=d.deptno这个条件。
外连接
外连接(左连接、右连接)
外连接的特点:查询出的结果存在不满足条件的可能。
左连接:

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL。
这么说你可能不太明白,我们还是用上面的例子来说明。
其中emp表中“张三”这条记录中,部门编号为50,而dept表中不存在部门编号为50的记录,所以“张三”这条记录,
不能满足e.deptno=d.deptno这条件。但在左连接中,
因为emp表是左表,所以左表中的记录都会查询出来,
即“张三”这条记录也会查出,但相应的右表部分显示NULL。

右连接
右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。例如在dept表中的40部门并不存在员工,但在右连接中,如果dept表为右表,那么还是会查出40部门,但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;


连接查询心得:
连接不限与两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件。

8.3自然连接

大家也都知道,连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:
λ 两张连接的表中名称和类型完全一致的列作为条件,例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!
当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!

SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
8.4子查询(非常重要)

一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就是子查询语句了。

  • 子查询出现的位置:
    where后,作为条为被查询的一条件的一部分;
    from后,作表;
  • 当子查询出现在where后作为条件时,还可以使用如下关键字:
    any
    all
  • 子查询结果集的形式:
    单行单列(用于条件)
    单行多列(用于条件)
    多行单列(用于条件)
    多行多列(用于表)

工资高于JONES的员工。
分析:

查询条件:工资>JONES工资,其中JONES工资需要一条子查询。

第一步:查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES'

第二步:查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (${第一步})

结果:
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

工资高于30号部门所有人的员工信息

SELECT * FROM emp WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30);

查询条件:工资高于30部门所有人工资,其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步:查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;

第二步:查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (${第一步})

结果:
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 *子查询作为条件
 *子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

查询工作和工资与MARTIN(马丁)完全相同的员工信息

查询条件:工作和工资与MARTIN完全相同,这是子查询

第一步:查询出MARTIN的工作和工资
SELECT job,sal FROM emp WHERE ename='MARTIN'

第二步:查询出与MARTIN工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果:
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')

有2个以上直接下属的员工信息

SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

* 子查询作为条件
* 子查询形式为单行多列

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

分析:(无需子查询)
查询列:员工名称、员工工资、部门名称、部门地址
查询表:emp和dept,分析得出,不需要外连接(外连接的特性:某一行(或某些行)记录上会出现一半有值,一半为NULL值)
条件:员工编号为7788

第一步:去除多表,只查一张表,这里去除部门表,只查员工表
SELECT ename, sal FROM emp e WHERE empno=7788

第二步:让第一步与dept做内连接查询,添加主外键条件去除无用笛卡尔积
SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, dept d 
WHERE e.deptno=d.deptno AND empno=7788

第二步中的dept表表示所有行所有列的一张完整的表,这里可以把dept替换成所有行,但只有dname和loc列的表,这需要子查询。
第三步:查询dept表中dname和loc两列,因为deptno会被作为条件,用来去除无用笛卡尔积,所以需要查询它。
SELECT dname,loc,deptno FROM dept;

第四步:替换第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, (SELECT dname,loc,deptno FROM dept) d 
WHERE e.deptno=d.deptno AND e.empno=7788

*子查询作为表
*子查询形式为多行多列

自连接:自己连接自己,起别名
求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno , e1.ename,e2.mgr,e2.ename 
        FROM emp e1, emp e2 
        WHERE e1.mgr = e2.empno AND e1.empno = 7369;

练习:
求各个部门薪水最高的员工所有信息

select e.* from emp e,
(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal

推荐阅读更多精彩内容