MySQL

1.1数据概念

什么是数据库

数据就是存储数据的仓库，其本质就是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库的数进行增加，修改，删除及查询等

什么是数据管理系统

数据管理系统：指一种操作和管理数据的大型软件，用于建立，使用和维护数据库，对数据库进行统一管理和控制，一保证数据库的安全性和完整性。用户通过数据据管理系统访问数据库中表的数据

数据库与数据库管理系统的关系

1.2SQL语句分类

数据定义语言：简称DDL，用来定义数据库对象：数据库，表，列等。关键字：create ，alter ，drop等
数据操作语言：简称DML，用来对数库中表的记录进行更新，关键字：insert ，delete ，update等

insert into 表名 (列1 ,列2) values (值1 ,值2)
update 表名 set 列名1=值1 , 列名2=值2
delete from 表名 where 列名= 值

数据控制语言:简称DCL,用来定义数据库的访问权限和安全级别及创建用户
数据查询语言：简称DQL，用来查询数据库中表的记录，关键字：select ， from ，where等

select xxx from xxx where xxx group by xxx having xxx order by xxx limit xxx

where使用的关键字：= ，！= ，<> ，< , > ,>= , <= ,in ,between xxx and xxx , is full , is not full ,and ,or ,not

通配符：_ 、% 例如：like'%张%' 、like'张_' ，like'张三%'

SQL通用语法

SQL语句可以单行或多行,以分号结尾
可使用空格和缩进增强语句的可读性
MySQL数据的SQL语句不区分大小写,关键字建议使用大写

　　SELECT * FROM user;

同样使用/**/的方式完成注释
MySQL中我们常用的的数据类型

类型名称	说明
int(integer)	整数类型
double	小数类型
float	小数类型
decimal(m,d)	指定整数位于小数位长度的小数类型
date	日期类型,格式yyyy-MM-dd,包含年月日,不包含时分秒
datetime	日期类型,格式yyyy-MM-dd HH:MM:SS,包含年月日时分秒
timestamp	日期类型,时间戳
varchar	文本类型

2.2DDL之数据库操作:database

创建数据库

格式：
 create database 数据库名;
 create database 数据库名 character set 字符集;

例如：
 #创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
 CREATE DATABASE webdb_1;
 #创建数据库 并指定数据库中数据的编码
 CREATE DATABASE webdb_2 CHARACTER SET utf8;

查看数据库

查看数据库MySQL服务器中的所有的数据库:
 show databases;

查看某个数据库的定义的信息:
 show create database 数据库名;
例如：
 show create database webdb_1;

删除数据库

 drop database 数据库名称;
例如：
 drop database webdb_2

使用数据库

查看正在使用的数据库:
 select database();

其他的数据库操作命令
切换数据库：
 use 数据库名;
例如：
 use webdb_1;

2.3DDL之表操作:table

创建表

格式：
 create table 表名(
    字段名 类型(长度) [约束],
    字段名 类型(长度) [约束],
    ...
 );

常用数据类型：

数值类型

int 整型    (0,4291967295)
bigint 极大整型    (0，18 446 744 073 709 551 615)
double 浮点型，例如：double(5,2)表示最多5为，其中必有2位小数，及最大值为999.99

字符串类型

char 固定长度字符串类型；char(10)    不常用
varchar(n) 可变长度字符串类型；varchar(10)    常用
text 字符串类型；大数据量类型。

其他类型

blob 字节类型；适合存：图片、音频、视频

日期和时间类型

date 日期类型，格式为：yyyy-MM-dd
time 时间类型，格式为：hh:mm:ss
timestamp 时间戳类型 yyyy-MM-dd hh:mm:ss    会自动赋值
datetime 日期时间类型 yyyy-MM-dd hh:mm:ss

解析：int（11）代表什么意思？

11代表的并不是长度，而是字符显示宽度。在字段类型为 int 时，无论你显示宽度设置为多少，int 类型能存储的最大值和最小值永远都是固定的

结论：

1、如果一个字段设置了无符号和填充零属性，那么无论这个字段存储什么数值，数值的长度都会与设置的显示宽度一致，如上述例子中的字段 b，插入数值 1 显示为00000000001，左边补了 10 个零直至长度达到 11 位；

2、设置字段的显示宽度并不限制字段存储值的范围，比如字段 d 设置为 int(5)，但是仍然可以存储 1234567890 这个 10 位数字；

3、设置的字符宽度只对数值长度不满足宽度时有效，如 d 字段 int(5)，插入 1 时，长度不足 5，因此在左边补充 4 个零直到 5 位，但是插入 1234567890 时超过了 5 位，这时的显示宽度就起不了作用了。

约束：

primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。

例如：创建分类表

 CREATE TABLE category (

  cid INT primary key, #分类ID

  cname VARCHAR(100) #分类名称

 )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='xxxxxxx';

--ENGINE=InnoDB 存储引擎=InnoDB，记录是以行的形式存储的

--DEFAULT CHARSET=utf8 默认数据集=utf8

--ROW_FORMAT=COMPACT

row_format就代表了当前使用的行记录结构类型

InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据

--COMMENT='xxxxxxx'

查看表

查看数据库中的所有表：
 格式：show tables;

查看表结构：
 格式：desc 表名;
 例如：desc category;

删除表

格式：drop table 表名;
例如：drop table category;

修改表结构格式

alter table 表名 add 列名类型(长度) [约束];

作用：修改表添加列.

例如：#1，为分类表添加一个新的字段为分类描述 varchar(20)

 ALTER TABLE category ADD `desc` VARCHAR(20);

alter table 表名 modify 列名类型(长度) 约束;

作用：修改表修改列的类型长度及约束.

例如：#2, 为分类表的描述字段进行修改，类型varchar(50) 添加约束 not null

 ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;

alter table 表名 change 旧列名新列名类型(长度) 约束; 作用：修改表修改列名.

例如：#3, 为分类表的分类名称字段进行更换更换为 description varchar(30)

 ALTER TABLE category CHANGE `desc` description VARCHAR(30);

alter table 表名 drop 列名;

作用：修改表删除列.

例如：#4, 删除分类表中description这列

 ALTER TABLE category DROP description;

rename table 表名 to 新表名;

作用：修改表名

例如：#5, 为分类表category 改名成 category2

 RENAME TABLE category TO category2;

alter table 表名 character set 字符集(了解);

作用：修改表的字符集

例如：#6, 为分类表 category 的编码表进行修改，修改成 gbk

ALTER TABLE category CHARACTER SET gbk;

2.4 DML数据操作语言

插入表记录：insert

语法：

-- 向表中插入某些字段

insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..);

-- 向表中插入所有字段,字段的顺序为创建表时的顺序

insert into 表 values (值1,值2,值3..);

注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
如果要插入空值，可以不写字段，或者插入null。
例如：

 INSERT INTO category(cid,cname) VALUES('c001','电器');
 INSERT INTO category(cid,cname) VALUES('c002','服饰');  
 INSERT INTO category(cid,cname) VALUES('c003','化妆品');
 INSERT INTO category(cid,cname) VALUES('c004','书籍');
 INSERT INTO category(cid) VALUES('c005');
 INSERT INTO category(cname,cid) VALUES('耗材','c006');

更新表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

语法：
更新所有记录的指定字段

update 表名 set 字段名=值,字段名=值,...;

更新符合条件记录的指定字段

update 表名 set 字段名=值,字段名=值,... where 条件;

注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起

删除记录：delete

语法：

delete from 表名 [where 条件];

2.5 DOS操作数据乱码解决

我们在dos命令行操作中文时，会报错

insert into category(cid,cname) values(‘c010’,’中文’);

ERROR 1366 (HY000): Incorrect string value: '\xB7\xFE\xD7\xB0' for column 'cname' at row 1

错误原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk

查看MySQL内部设置的编码

 show variables like 'character%';
 查看所有mysql的编码

需要修改client、connection、results的编码一致（GBK编码）

解决方案1：在cmd命令窗口中输入命令，此操作当前窗口有效，为临时方案。

set names gbk;

解决方案2：安装目录下修改my.ini文件，重启服务所有地方生效。

3.SQL约束

3.1 主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

添加主键约束

方式一：在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)

缺点:只能使用一个字段作为主键

CREATE TABLE persons  (
   id_p int PRIMARY KEY,
   lastname varchar(255),
   firstname varchar(255),
   address varchar(255),
   city varchar(255)
  )

方式二：创建表时，在所有字段之后,使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是符合主键)

 CREATE TABLE persons  (  
　　firstname varchar(255),  
　　lastname varchar(255),  
　　address varchar(255),  
　　city varchar(255),  
　　PRIMARY KEY (firstname,lastname)
  )

方式三：创建表之后，通过修改表结构，声明指定字段为主键：
格式：ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)

CREATE TABLE persons  (
  firstname varchar(255),
  lastname varchar(255),
  address varchar(255),
  city varchar(255)
  )
  ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)  
或者  
　AlTER TABLE person ADD CINSTRAINT pk_id PRIMARY KEY (firstname,lastname)

删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

 ALTER TABLE persons DROP PRIMARY KEY

在实际创建表的时候,很少使用真实业务数据作为主键(业务主键:如学号,课程号);大部分时候是使用逻辑性字段(字段没有业务含义,值是什么都没有关系),将这种字段主键成为逻辑主键

3.2 自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键

 CREATE TABLE persons  (
  p_id int PRIMARY KEY AUTO_INCREMENT,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
  city varchar(255)
  )

向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：

 INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')  INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')

扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：

 ALTER TABLE persons AUTO_INCREMENT=100

面试题

问：针对auto_increment ，删除表中所有记录使用 delete from 表名或使用 truncate table 表名，二者有什么区别？

删除方式：

delete 一条一条删除，不清空auto_increment记录数。

truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

3.3 非空约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：

 CREATE TABLE persons  (  id_p int NOT NULL,  lastname varchar(255) NOT NULL,  firstname varchar(255),  address varchar(255),  city varchar(255)  )

3.4 唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束；而且本质的区别就是唯一值默认允许为空，而且是多个为空

添加唯一约束

与主键添加方式相同，共有3种

方式一：创建表时，字段之后直接跟unique/unique key

 CREATE TABLE persons  (  id_p int UNIQUE,  lastname varchar(255) NOT NULL,  firstname varchar(255),  address varchar(255),  city varchar(255)  )

方式二：创建表时，在所有字段之后增加unique key(字段列表)

 CREATE TABLE persons  (  id_p int,  lastname varchar(255) NOT NULL,  firstname varchar(255),  address varchar(255),  city varchar(255),  UNIQUE (id_p)  )

方式三：创建表后，修改表结构，声明字段唯一：

 ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

删除唯一约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE persons DROP INDEX 名称

例如

ALTER TABLE persons DROP INDEX firstnanme;

如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。

3.5 默认约束

default 约束用于指定字段默认值。当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。

添加默认约束，在创建表时候添加

CREATE TABLE student( 　　　　　　id int pirmary key, 　　　　　　name varchar(50) not null, 　　　　　　sex varchar(10) default '男' 　　　　); 　　　　-- 批量插入(MySQL中的方式，别的数据库软件不好使哦) 　　　　INSERT INTO student1 values(1,'jerry','女'); 　　　　INSERT INTO student1 values(2,'tom',default); -- sex插入的是默认值 　　　　INSERT INTO student1 values(3,'bruce',null); -- sex插入的是null值

删除默认约束

 ALTER TABLE t_user MODIFY user_id INT(10);

4.SQL语句(DQL)

4.1 DQL准备工作和语法

DQL数据查询语言（重要）

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

查询返回的结果集是一张虚拟表。

查询关键字：SELECT

语法：SELECT 列名 FROM 表名;

WHERE --> GROUP BY --> HAVING --> ORDER BY

语法：

SELECT selection_list /*要查询的列名称*/

FROM table_list /*要查询的表名称*/

WHERE condition /*行条件*/

GROUP BY grouping_columns /*对结果分组*/

HAVING condition /*分组后的行条件*/

ORDER BY sorting_columns /*对结果分组*/

LIMIT offset_start, row_count /*结果限定*/

学生表：stu

字段名称	字段类型	说明
sid	char(6)	学生学号
sname	varchar(50)	学生姓名
age	int	学生年龄
gender	varchar(50)	学生性别
CREATE TABLE stu ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) );
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male'); INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female'); INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male'); INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female'); INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male'); INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female'); INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male'); INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female'); INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male'); INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

雇员表：emp

字段名称	字段类型	说明
empno	int	员工编号
ename	varchar(50)	员工姓名
job	varchar(50)	员工工作
mgr	int	领导编号
hiredate	date	入职日期
sal	decimal(7,2)	月薪
comm	decimal(7,2)	奖金
deptno	int	部分编号

CREATE TABLE emp ( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm decimal(7,2), deptno INT );

INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL,20); INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400,30); INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL,30); INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL,10); INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL,20); INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL,10); INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0,30); INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL,10);

部分表：dept

字段名称	字段类型	说明
deptno	int	部分编码
dname	varchar(50)	部分名称
loc	varchar(50)	部分所在地点

SELECT * FROM stu WHERE sname LIKE '____i';

CREATE TABLE dept( deptno INT, dname varchar(14), loc varchar(13) );

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

1　基础查询

1.1　查询所有列

SELECT * FROM stu;

1.2　查询指定列

SELECT sid, sname, age FROM stu;

2　条件查询

2.1　条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

  =、!=、<>、<、<=、>、>=

            BETWEEN...AND；

            IN(set);    NOT IN(set);    可以有多个值，用逗号隔开

            IS NULL 、IS NOT NULL

            AND;    OR;     NOT;

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

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

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

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

2.4　查询学号为S_1001，S_1002，S_1003的记录

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

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

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

2.6　查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL;

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

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

2.8　查询性别非男的学生记录

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

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

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

3　模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

通配符:

_：任意一个字符

%：任意0~n个字符

例如： '%张%' 小张、张、张三、小张三、小小张、小小张三丰

'张_' 张三、张四

'张%' 张、张三、张三丰、张三小丰

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

SELECT * FROM stu WHERE sname LIKE '_____';

模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

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

SELECT * FROM stu WHERE sname LIKE '____i';

3.3　查询姓名以“z”开头的学生记录

SELECT * FROM stu WHERE sname LIKE 'z%'; 其中“%”匹配0~n个任何字母。

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

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

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

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

4　字段控制查询

4.1　去除重复记录

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

SELECT DISTINCT sal FROM emp; SELECT DISTINCT sal,mgr FROM emp;

4.2　查看雇员的月薪与佣金之和

　　因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

4.3　给列名添加别名

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

SELECT *,sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的。

SELECT *,sal+IFNULL(comm,0) total FROM emp; SELECT sname 姓名,gender 性别 FROM stu;   (AS均可以省略，空格就相当于AS)

　　　　　5　排序

order by 列名 asc(默认升序,可以不用写) 、desc(降序)

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

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

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

SELECT * FROM stu ORDER BY age DESC;

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

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

6　聚合函数查询

sum avg max min count

聚合函数是用来做纵向运算的函数：

COUNT(): 统计指定列中不为NULL的记录行数；

MAX(): 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序(即字典顺序)运算；

MIN(): 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序(即字典顺序)运算；

SUM(): 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG(): 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1　COUNT()

当需要纵向统计时可以使用COUNT()。

查询emp表中记录数并改名

 SELECT COUNT(*) AS cnt FROM emp;  特别注意：COUNT(*)计数所有列也即计数所有行

查询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(cmm),COUNT(mgr) FROM emp;

查询有佣金的人数且有领导的人数

 SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL AND mgr IS NOT NULL;

6.2　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)+ SUM(comm) FROM emp;

统计所有员工平均工资

   SELECT AVG(sal) FROM emp;

6.3　MAX() 和 MIN()

查询最高工资和最低工资

 SELECT MAX(sal),MIN(sal) FROM emp;

7　分组查询

排序语法：select xxx from xxx where xxx order by xxx

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

注：凡和聚合函数同时出现的列名，一定要写在group by 之后。

例如：查询stu中female与male的人数：

SELECT COUNT(*) FROM stu WHERE gender IS NOT NULL GROUP BY gender;

7.1　分组查询

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno; 凡和聚合函数同时出现的列名，一定要写在group by 之后。

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;  凡和聚合函数同时出现的列名，一定要写在group by 之后。

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

7.2　HAVING子句

查询工资总和大于9000的部门编号以及工资和：

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

查询工资大于1500，工资总和大于6000的部门编号以及工资和，按工资总和降序排序

SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 \ GROUP BY deptno HAVING SUM(sal)>6000 ORDER BY sum(sal) DESC;

注：having与where的区别:

1、having是对分组后的数据进行过滤。

where是对分组前的数据进行过滤。

2、having后面可以使用聚合函数(统计函数)。

where后面不可以使用聚合函数(即使用基本数据)。

3、having是对分组后数据的约束。

where是对分组前记录的条件，如果某行记录没有满足 where子句的条件，那么这行记录不会参加分组。

8　LIMIT 方言

LIMIT 用来限定查询结果的起始行，以及总行数。即后面要学的分页技术。

8.1　查询5行记录，起始行从0开始，即从第一行开始

SELECT * FROM emp LIMIT 0, 5;

注意：起始行从0开始，即第一行开始！5表示要查询5行。

8.2　查询10行记录，起始行从3开始，即从第4行开始

SELECT * FROM emp LIMIT 3, 10;

8.3　分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；

分页的思路：

SELECT * FROM emp LIMIT 0, 3; 第一页：从第1行开始，读3行 SELECT * FROM emp LIMIT 3, 3; 第二页：从第4行开始，读3行 SELECT * FROM emp LIMIT 6, 3; 第三页：从第7行开始，读3行 SELECT * FROM emp LIMIT 9, 3; 第四页：从第10行开始，读3行

例如：

int currentPage = 1; // 当前页

int pageSize = 3; // 每页的条数

若当前页为1,则 LIMIT 0, 3; （1-1）*3=0

若当前页为2,则 LIMIT 3, 3; （2-1）*3=3

若当前页为3,则 LIMIT 6, 3; （3-1）*3=6

若当前页为4,则 LIMIT 9, 3; （4-1）*3=9

......

所以：若当前页为currentPage,则 LIMIT (currentPage-1)*pageSize, pageSize;

......

8.3　查询代码的书写顺序和执行顺序

查询语句书写顺序：select – from - where - group by - having - order by - limit

查询语句执行顺序：from - where - group by - having - select - order by - limit （MySQL中演示不了，Oracle中可以演示）

实际开发过程中,通常需要很多表才能完成,要有外键必须先有主键，外键关联/引用/参照主键，主键和外键的数据类型必须一致。

外键约束：foreign key

例：

　 -- 学生表(主表)

CREATE TABLE student(
 stuid VARCHAR(10) primary key,
 stuname VARCHAR(50) not null );

第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。

CREATE TABLE score(
 stuid VARCHAR(10), --外键列的数据类型一定要与主键列的数据类型一致
 score INT,
 courseid INT,
 CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid)
 );

CONSTRAINT(constraint:约束)

FOREIGN KEY(foreign key:外键)

REFERENCES(references:引用/参照/关联)

第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。

-- 分数表(次表/子表)

CREATE TABLE score(
 stuid VARCHAR(10), --外键列的数据类型一定要与主键列的数据类型一致
 score INT,
 courseid INT
 );
 ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);

select * from student;
 insert into student values('1001','张三丰');
 insert into student values('1002','张无忌');
 insert into student values('1003','王尼玛');
 insert into student values('1004','王老五');
 insert into student values('1002','张无忌');

select * from score;
 insert into score values('1001','98',1);
 insert into score values('1002','95',1);
 insert into score values('1002','67',2);
 insert into score values('1003','83',2);
 insert into score values('1003','57',3);

SQL中 PK、UK、DF、CK、FK 的意思：

--主键约束 PK

　　在表外修改：alter table xxx add constraint PK_字段 primary key(字段);

　　在表中修改：constraint PK_字段 primary key(字段),

　　在表中修改：字段字段类型 primary key,

　　--唯一约束 UK

　　在表外修改：alter table xxx add constraint UK_字段 unique key(字段);

　　在表中修改：constraint UK_字段 unique key(字段),

　　在表中修改：字段字段类型 unique,

　　--默认约束 DF

　　在表外修改：alter table xxx add constraint DF_字段 default('默认值') for 字段;

　　在表中修改：constraint DF_字段 default('默认值') for 字段,

　　在表中修改：字段字段类型 default('默认值'),

　　--检查约束 CK

　　在表外修改：alter table xxx add constraint CK_字段 check(约束。如：len(字段)>1);

　　在表中修改：constraint CK_字段 check(约束。如：len(字段)>1),

　　在表中修改：字段字段类型 check(约束。如：len(字段)>1),

　　--外键约束 FK

　　在表外修改：alter table xxx add constraint FK_主表_子表_主表主键字段 foreignkey(子表外键字段) references 主表(主表主键字段);

　　在表中修改：constraint FK_主表_子表_主表主键字段 foreign key(子表外键字段) references 主表(主表主键字段),

　　在表中修改：字段字段类型 foreign key(子表外键字段) references 主表(主表主键字段),

1、表与表之间的关系

一对一：

例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。

人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。

设计从表可以有两种方案：

方式1：在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；即：字段字段类型 unique,

方式2：给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

　　示例：用方式2

create table QQ(
 qqid int primary key,
 password varchar(50) );　　　　　　

create table QQDetail(
 qqid int primary key,
 name varchar(50),
 address varchar(200) );

alter table QQDetail add constraint fk_QQ_QQDetail foreign key(qqid) references QQ(qqid);
 或者 
alter table QQ add constraint fk_QQ_QQDetail foreign key(qqid) references QQDetail(qqid);

注意：虽然是一对一，但是维护关系不一样，那么主从表关系也不一样。(也就是说仍然有主从表的关系)

一对多(多对一)：

最为常见的就是一对多！一对多和多对一，这是从哪个角度去看或者说以谁为参照物。

　　-- 学生表(主表)

CREATE TABLE student( 
tuid VARCHAR(10) primary key,
stuname VARCHAR(50) not null
 );

　　第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。

　　-- 分数表(次表/子表)

CREATE TABLE score(
 stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致     
 score INT,
 courseid INT,     
 CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid) );

CONSTRAINT(constraint:约束)

FOREIGN KEY(foreign key:外键)

REFERENCES(references:引用/参照/关联)

　第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。

　-- 分数表(次表/子表)

CREATE TABLE score(
 stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致     
 score INT,
 courseid INT
 );

ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);

多对多：

　例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。

　例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

create table teacher( tid int primary key, tname varchar(20) );

create table stu( sid int primary key, sname varchar(50) );

　　　　　 --中间表

create table tea_stu_rel( tid int, sid int );

alter table tea_stu_rel add constraint fk_teacher_rel foreign key(tid) references teacher(tid); alter table tea_stu_rel add constraint fk_stu_rel foreign key(sid) references stu(sid);

回到顶部

六、多表查询（重要）

　　多表查询有如下几种

1. 合并结果集查询(UNION、UNION ALL)
2. 连接查询

内连接查询 [INNER] JOIN ON

外连接查询 OUTER JOIN ON

左外连接查询 LEFT [OUTER] JOIN

右外连接查询 RIGHT [OUTER] JOIN

全外连接查询(MySQL不支持) FULL JOIN

自然连接查询 NATURAL JOIN

3. 子查询
4. 自连接查询

1、合并结果集查询(UNION、UNION ALL)

　　作用：合并结果集就是把两个select语句的查询结果合并到一起。

合并结果集有两种方式：

UNION：去除重复记录，

例如：SELECT * FROM t1 UNION SELECT * FROM t2;

UNION ALL：不去除重复记录，

例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2;

　要求：被合并的两个结果：列数、列类型必须相同。

-- 联合查询

CREATE TABLE A( NAME VARCHAR(10), score INT );

CREATE TABLE B( NAME VARCHAR(10), score INT );

-- 批量插入

INSERT INTO A VALUES('a',10),('b',20),('c',30);  INSERT INTO B VALUES('a',10),('b',20),('d',40);

--合并结果集查询

SELECT * FROM A  UNION  SELECT * FROM B; SELECT * FROM A  UNION ALL SELECT * FROM B;

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;

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

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

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

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

2.1　内连接查询 [INNER] JOIN ON

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

SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;  -- INNER 可以省略。MySQL 默认连接方式为内连接。

SELECT s.stuid, s.stuname, s.score FROM student s, score c  WHERE s.stuid = c.stuid AND score>70;  -- 99查询法(老的查询方法)

SELECT s.stuid, s.stuname, s.score FROM student s  INNER JOIN score c ON s.stuid = c.stuid WHERE score>70;  -- 新的标准查询法

　INNER可以省略。

　特别注意：ON相当于WHERE，一般多用于主外键条件关联。

　　　　 不是主外键条件也可以啊！说白了，ON就是筛选的条件。

　　　　　　内连接的特点：查询结果必须满足条件。

例如我们向emp表中插入一条记录。　　　　　　

　　其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

2.2　外连接查询(左外连接查询、右外连接查询) [OUTER] JOIN ON

　　外连接的特点：查询出的结果存在不满足条件的可能。

　　左外连接查询：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;  -- OUTER可以省略。以表emp为主。

　　左外连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

　　这么说你可能不太明白，我们还是用上面的例子来说明。

　　其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。

　　但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

右外连接查询

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;  -- OUTER可以省略。以表dept为主。

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

　　例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

连接查询心得：

　　连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。

　　通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。

　　这个条件大多数情况下都是使用主外键关系去除(一般使用内连接查询)。

　　两张表的连接查询一般会有一个主外键关系(没有关系的话，你查个鬼啊！)，三张表的连接查询就一般会有两个主外键关系，

　　所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。

　　如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

特别注意：我自己测试过，两张表的主外键关系可以alter修改表的主次表关系，使这两张表有了主外键关系；

两种表也可以不有主外键关系，只要他们对应的字段和字段类型相同就行。

多张表查询

法一：内连接查询(99查询法)：

SELECT * FROM student s, score c, course cc  WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid; SELECT s.stuid, s.stuname, c.score, cc.cname FROM student s, score c, course cc  WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid;

　　法二：新的内连接查询

SELECT * FROM student s JOIN score c ON s.stui = c.stuid  JOIN cource cc ON c.courseid = cc.coureseid;

小结：

　　合并查询：union、unionall

　　连接查询：

　　　　内连接查询：[inner] join in

　　　　左外连接查询：left [outer] join in

　　　　右外连接查询：right [outer] join in

　　多个表查询：n表连接查询，至少有n-1个关系条件。

2.3、自然连接查询(NATURAL JOIN)

　　大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。

　　而自然连接无需你去给出主外键等式，它会自动找到这一等式：两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

　　当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept; -- 没写条件，默认内连接查询。 SELECT * FROM emp NATURAL LEFT JOIN dept; -- 默认左外连接 SELECT * FROM emp NATURAL RIGHT JOIN dept;  -- 默认右外连接

3、子查询(非常重要)

　一个select语句中包含另一个完整的select语句。

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

　　子查询出现的位置：

where后，作为主句的条件来用。
from后，作表。

当子查询出现在where后作为条件时，还可以使用如下关键字：

any
all

　　子查询结果集的形式：

单行单列（用于条件）
单行多列（用于条件）
多行单列（用于条件）
多行多列（用于表）

练习1：查询工资高于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');

练习2：查询与SCOTT在同一个部门的员工。

SELECT *FROMemp WHERE depton=( SELECT depton FROM emp WHERE ename ='SCOTT'); SELECT * FROM emp WHERE depton=(20); -- 两句等价

子查询结果集作为条件
子查询结果集形式为单行单列

练习3：查询工资高于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关键字）

练习4：查询工作和工资与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');

练习5：查询有2个以上直接下属的员工信息。(即如果mgr中的数据有出现两次以上一样的，说明该编号对应的人有两个以上的直接下属)

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

子查询结果集作为条件
子查询结果集形式为单行多列

练习6：查询员工编号为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; SELECT e.ename, e.sal, d.dname, d.loc FROM emp e,  (SELECT * FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;

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

小结：表dept是一张表的表名，表示一张表，

　dept AS d(dept d),给表dept起了个新名字d，则d也表示一张表，

dept 等价于 select * from dept。

4、自连接查询

自己连接自己，起别名(即把自己看成两张表，为了区分，所以每个自己要起一个名字)

　　求7369员工的经理的编号和姓名

SELECT ename,empno FROM emp WHERE empno = ( SELECT mgr FROMemp WHERE empno =7369);

　　求7369员工编号和姓名以及该员工的经理编号和姓名

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

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

　　普通版本(会有问题)

SELECT * FROM emp WHERE sal IN( SELECTMAX(sal) FROM emp GROUP BY deptno);

　　改进版本

SELECT e1.* FROM emp e1, (SELECT MAX(sal) maxsal, deptno d FROM emp GROUP BY deptno) e2   --部门最高工资和部分号一起组成的表 WHERE e1.deptno = e2.d AND e1.sal = e2.maxsal;

posted @ 2019-09-05 21:49 IT界一个小学生阅读(469) 评论(0) 编辑收藏举报

刷新页面返回顶部

IT界一个小学生

MySQL

1.1数据概念

1.2SQL语句分类

公告