1. 数据库应用
1.1 概述
1.1.1 什么是数据库
简而言之,就是存储数据,管理数据的仓库。
数据库的好处
- 持久化数据到本地。
- 可以实现结构化查询,方便管理。
DB:数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS:数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器。
SQL:结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。
常见的数据库管理系统分为:
- 关系型数据库, Oracle、MySQL、SQLServer、Access
- 非关系型数据库, MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
1.1.2 关系型和非关系型
早期发展的数据库建立在数据的紧密关系基础之上(如:父子关系、师生关系),我们称其为关系型数据库,也称为传统数据库;现今数据库建立在数据的松散关系基础之上(如:中国人和美国人、中国人和印度人、视频、音频),我们称其为非关系型数据库nosql(not only sql)。业界总在争论nosql能否干掉传统数据库,很多初学者也有这个困惑。以我来看,两者没有矛盾,它们各有特点,根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破,领土不断被蚕食,规模一再的缩小,虽然无法全面被替代,但却早已风光不在,沦落到一偶之地,Oracle的衰落就是最好的证明,早期只要是全球大企业无一例外都是部署Oracle,但现在都在去Oracle化,阿里就已经全面排斥Oracle。
1.1.3 关系型数据库
关系型数据库有特定的组织方式,其以行和列的形式存储数据,以便于用户理解。
关系数据库的表采用二维表格来存储数据,是一种按行与列排列的具有相关信息的逻辑组,它类似于 Excle 工作表。一个数据库可以包含任意多个数据表。表中的一行即为一条记录。数据表中的每一列称为一个字段,表是由其包含的各种字段定义的,每个字段描述了它所含有的数据的意义,数据表的设计实际上就是对字段的设计。创建数据表时,为每个字段分配一个数据类型,定义它们的数据长度和其他属性。行和列的交叉位置表示某个属性值,如“数据库原理”就是课程名称的属性值。
关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。
1.2 数据库存储数据的特点
1)将数据放到表中,表再放到库中。
2)一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3)表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
4)表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的“属性”。
5)表中的数据是按行存储的,每一行类似于java中的“对象”。
2. SQL语法概述
2.1 概述
结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ “S-Q-L”),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
SQL 是1986年10 月由美国国家标准局(ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。
2.2 SQL语言分类
DQL(Data Query Language):数据查询语言 (SELECT)
DML(Data Manipulation Language)
数据操纵语句,用于查询与修改数据记录用于添加、删除、修改、查询数据库记录(简称CRUD),并检查数据完整性,包括如下SQL语句:
- INSERT:添加数据到数据库中
- UPDATE:修改数据库中的数据
- DELETE:删除数据库中的数据
- SELECT:选择(查询)数据 (SELECT是SQL语言的基础,最为重要)
DDL(Data Definition Languge)
数据定义语言,用于定义数据库的结构,比如创建、修改或删除数据库对象,包括如下SQL语句:
- CREATE TABLE:创建数据库表
- ALTER TABLE:更改表结构、添加、删除、修改列长度
- DROP TABLE:删除表
- CREATE INDEX:在表上建立索引
- DROP INDEX:删除索引
DCL(Data Control Language)
数据库控制语言,用于定义用户的访问权限和安全级别。只有管理员才有相应的权限,包括如下SQL语句:
- GRANT:授予访问权限
- REVOKE:撤销访问权限
- COMMIT:提交事务处理
- ROLLBACK:事务处理回退
- SAVEPOINT:设置保存点
- LOCK:对数据库的特定部分进行锁定
2.3 SQL 的语言规范
-
必须只能包含 A–Z,a–z,0–9, 共 63 个字符
-
字段名必须以字母开头,尽量不要使用拼音,多个单词用下划线隔开,而非java语言的驼峰规则。
-
必须保证你的字段没有和保留字、数据库系统或常用方法冲突(如where,order,group)
-
必须不能和用户定义的其他对象重名
-
不能在对象名的字符间留空格
-
值,除了数值型,字符串型和日期时间类型使用单引号(’’)
-
别名,尽量使用双引号(“”),而且不建议省略 as
-
所有标点符号使用英文状态下的半角输入方式
-
必须保证所有()、单引号、双引号是成对结束的
-
数据库、表名不得超过 30 个字符,变量名限制为 29 个(不同数据库,不同版本会有不同)
-
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
-
在命令行中的要求:一个语句可以分开多行编写,以;或\g 结束
-
mysql 对于 SQL 语句不区分大小写,SQL 语句关键字尽量大写。
-
可以使用
1)#单行注释
2)–空格单行注释
3)/* 多行注释 */
2.4 MySQL 的数据类型
常用的数据类型有:
- 整型(xxxint)
- 位类型(bit)
- 浮点型(float 和 double、real)
- 定点数(decimal,numeric)
- 日期时间类型(date,time,datetime,year)
- 字符串(char,varchar,xxxtext)
- 二进制数据(xxxBlob、xxbinary)
- 枚举(enum)
- 集合(set)
- 图片
1)整型
整数列的可选属性有三个:
- M: 宽度(在 0 填充的时候才有意义,否则不需要指定)。
- unsigned: 无符号类型(非负)。
- zerofill: 0 填充,(如果某列是 zerofill,那么默认就是无符号),如果指定了 zerofill 只是表示不够 M 位时,用 0 在左边填充,如果超过 M 位,只要不超过数据存储范围即可。
原来,在 int(M) 中,M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用4 bytes
2)浮点型
对于浮点列类型,在 MySQL 中单精度值使用 4 个字节,双精度值使用 8 个字节。
- MySQL 允许使用非标准语法(其他数据库未必支持,因此如果设计到数据迁移,则最好不要这么用):FLOAT(M,D)或 DOUBLE(M,D)。这里,(M,D)表示该值一共显示 M 位,其中 D 表示小数点后几位,M 和 D 又称为精度和标度。
例如,定义为 FLOAT(5,2)的一个列可以显示为-999.99-999.99。M 取值范围为 0-255。D 取值范围为 0-30,同时必须<=M。 - 如果存储时,整数部分超出了范围(如上面的例子中,添加数值为 1000.01),MySql 就会报错,不允许存这样的值。如果存储时,小数点部分若超出范围,就分以下情况:若四舍五入后,整数部分没有超出范围,则只警告,但能成功操作并四舍五入删除多余的小数位后保存,例如在FLOAT(5,2)列内插入999.009,近似结果是999.01。
若四舍五入后,整数部分超出范围,则 MySql 报错,并拒绝处理。如 999.995 和-999.995 都会报错。 - 说明:小数类型,也可以加 unsigned,但是不会改变数据范围,例如:float(3,2) unsigned 仍然只能表示 0-9.99的范围。
- float 和 double 在不指定精度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示
- REAL 就是 DOUBLE ,如果 SQL 服务器模式包括 REAL_AS_FLOAT 选项,REAL 是 FLOAT 的同义词而不是 DOUBLE
的同义词。
注意:在编程中,如果用到浮点数,要特别注意误差问题,因为浮点数是不准确的,所以我们要避免使用“=”来
判断两个数是否相等。如果希望保证值比较准确,推荐使用定点数数据类型。
3)位类型(了解)
BIT 数据类型可用来保存位字段值。BIT(M)类型允许存储 M 位值。M 范围为 1~64,默认为 1。
BIT 其实就是存入二进制的值,类似 010110。如果存入一个 BIT 类型的值,位数少于 M 值,则左补 0。如果存入一个 BIT 类型的值,位数多于 M 值,MySQL 的操作取决于此时有效的 SQL 模式:如果模式未设置,MySQL 将值裁剪到范围的相应端点,并保存裁减好的值。如果模式设置为 traditional(“严格模式”),超出范围的值将被拒绝并提示错误,并且根据 SQL 标准插入会失败。
对于位字段,直接使用 SELECT 命令将不会看到结果,可以用 bin()或 hex()函数进行读取。
4)定点型
- DECIMAL 在 MySQL 内部以字符串形式存放,比浮点数更精确。定点类型占 M+2 个字节
- DECIMAL(M,D)与浮点型一样处理规则。M 的取值范围为 0-65,D 的取值范围为 0-30,而且必须<=M,超出范围会报错。
- DECIMAL 如果指定精度时,默认的整数位是 10,默认的小数位为 0。
5)日期时间类型
- 对于 year 类型,输入的是两位,“00-68”表示 2000-2069 年,“70-99”表示 1970-1999 年。记比较麻烦,建议使用 4 位标准格式。
- ‘YYYY-MM-DD HH:MM:SS’或’YY-MM-DD HH:MM:SS’,'YYYY-MM-DD’或’YY-MM-DD’格式的字符串。(允许“不严格”)
语法:任何标点符都可以用做日期部分或时间部分之间的间割符。例如,‘98-12-31 11:30:45’、‘98.12.31 11+30+45’、‘98/12/31 113045’ 和 '98@12@31 11|30|45’是等价的。 - ‘YYYYMMDD’或’YYMMDD’格式的没有间割符的字符串,假定字符串对于日期类型是有意义的。例如,‘19970523’ 和’970523’被解释为 ‘1997-05-23’,但’971332’是不合法的(它有一个没有意义的月和日部分),将变为’0000-00-00’。
- 对于包括日期部分间割符的字符串值,如果日和月的值小于 10,不需要指定两位数。‘1979-6-9’与’1979-06-09’ 是相同的。同样,对于包括时间部分间割符的字符串值,如果时、分和秒的值小于 10,不需要指定两位数。
'1979-10-30 1:2:3’与’1979-10-30 01:02:03’相同。 - 数字值应为 6、8、12 或者 14 位长。如果一个数值是 8 或 14 位长,则假定为 YYYYMMDD 或 YYYYMMDDHHMMSS格式,前 4 位数表示年。如果数字 是 6 或 12 位长,则假定为 YYMMDD 或 YYMMDDHHMMSS 格式,前 2 位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。
- 一般存注册时间、商品发布时间等,不建议使用 datetime 存储,而是使用时间戳,因为 datetime 虽然直观,但不便于计算。而且 timestamp 还有一个重要特点,就是和时区有关。
6)字符
char,varchar,text 的区别
char 是一种固定长度的类型,varchar 则是一种可变长度的类型,它们的区别是:
-
char 如果不指定(M)则表示长度默认是 1 个字符。varchar 必须指定(M)。
-
char(M)类型的数据列里(最多容纳2000个字符),每个值都占用 M 个字符,如果某个长度小于 M,MySQL 就会在它的右边用空格字符补足。(例如,char(11)存储abc,占11位。在检索操作中那些填补出来的空格字符将被去掉,如果存入时右边本身就带空格,检索时也会被去掉)
-
在 varchar(M)类型的数据列里(最多容纳4000个字符),每个值只占用刚好够用的字符再加上一个到两个用来记录其长度的字节。(例如,varchar(11)存储abc,只占3位。如果是gbk,一个汉字占2个字节,utf8编码计算的话,一个汉字在u8下占3个字节。即总长度为 L 字符+1/2 字字节,在Oracle中,为varchar2)
由于某种原因 char 固定长度,所以在处理速度上要比 varchar 快速很多,但相对费存储空间,所以对存储不大,但在速度上有要求的可以使用 char 类型,反之可以用 varchar 类型来实例。
- text 文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用 char,varchar来代替。还有 text 类型不用加默认值,加了也没用。
注:不同数据库版本长度限制可能会有不同
哪些情况使用 char 更好?
- 存储很短的信息,比如门牌号码 101,201……这样很短的信息应该用 char,因为 varchar 还要占个 byte 用于存储信息长度,本来打算节约存储的现在得不偿失。
- 固定长度的,比如使用 uuid 作为主键,那用 char 应该更合适。因为他固定长度,varchar 动态根据长度的特性就消失了,而且还要占个长度信息。
- 十分频繁改变的 column。因为 varchar 每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于 char 来说是不需要的。
- MyISAM 和 MEMORY 存储引擎中无论使用 char 还是 varchar 其实都是作为 char 类型处理的。除此之外,建议使用 varchar 类型。特别是 InnoDB 存储引擎。
7)二进制值类型(了解)
包括:xxxBLOB 和 xxxBINARY
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR 类型,但是不同的是,它们存储的不是字符字符串,而是二进制串。所以它们没有字符集,并且排序和比较基于列值字节的数值值。当保存 BINARY(M)值时,在它们右边填充 0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节很重要(因为空格和 0x00 是不同的,0x00<空格),包括 ORDER BY 和 DISTINCT 操作。比如插入’a ‘会变成’a \0’。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB和 LONGBLOB。它们只是可容纳值的最大长度不同。分别与四种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT对应有相同的最大长度和存储需求。在 TEXT 或 BLOB 列的存储或检索过程中,不存在大小写转换。BLOB 和 TEXT 列不能有默认值。BLOB 或 TEXT 对象的最大大小由其类型确定,但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet 变量的值更改消息缓存区的大小,但必须同时修改服务器和客户端程序。
8)枚举(ENUM)
MySql 中的 ENUM 是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值:
- 可以插入空字符串""和 NULL(如果运行 NULL 的话)。
- 如果你将一个非法值插入 ENUM(也就是说,允许的值列之外的字符串),如果是严格模式,将不能插入,如果是非严格模式,将选用第一个元素代替,并警告。
- ENUM 最多可以有 65,535 个成员,需要 2 个字节存储。
- 当创建表时,ENUM 成员值的尾部空格将自动被删除。
值的索引规则如下:
- 来自列规定的允许的值列中的值从 1 开始编号。
- 空字符串错误值的索引值是 0。
- NULL 值的索引是 NULL。
9)集合(SET)
- SET 和 ENUM 类型非常类似,也是一个字符串对象,里面包含 0~64 个成员。
- SET 和 ENUM 存储上有所不同,SET 是根据成员的个数决定存储的字节数。
- SET 和 ENUM 最主要的区别在于 SET 类型一次可以选择多个成员,而 ENUM 则只能选择一个。
10)特殊的 NULL 类型
Null 类型特征:
- 所有的类型的值都可以是 null,包括 int、float 等数据类型
- 空字符串””,不等于 null,0 也不等于 null,false 也不等于 null
- 任何运算符,判断符碰到 NULL,都得 NULL
- NULL 的判断只能用 is null,is not null
- NULL 影响查询速度,一般避免使值为 NULL
为什么建表时,加 not null default ‘’ / default 0
- 不想让表中出现 null 值. 为什么不想要的 null 的值。
- 不好比较,null 是一种类型,比较时,只能用专门的 is null 和 is not null 来比较。 碰到运算符,一律返回 null。
- 效率不高,影响提高索引效果。 因此,我们往往在建表时 not null default ‘’/
11)图片
blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
2.5 MySQL 的运算符
(1)算术运算符:+ - * /(除也可以写成 div) %(取模可以写成 mod)
(2)比较运算符:= > >= < <= !=(不等于还可以写成<>) <=>(安全等于)
(3)逻辑运算符:&&(逻辑与也可以写成 and) ||(逻辑或也可以写成 or) not(逻辑非)
(4)范围:表达式 between … and … (也可以写成 表达式>=… and 表达式 <=…)
表达式 not between … and …(也可以写成 表达式<… || 表达式 >…)
(5)集合:in (值,值,值…) not in(值,值,值…)
(6)模糊查询:LIKE NOT LIKE,通配符:%表示 0-n 个字符,_下划线代表一个字符
(7)位运算符:&(按位与) |(按位或)^(按位异或)~(按位取反)>>(右移)<<(左移)
(8)NULL 值判断,is null 或 is not null,如果使用 null=null,null<>null,null=0,null<>0,null=false 等都不对。不过 xxx is null 可以使用 xxx <=> null ,xxx is not null 可以写成 not xxx <=> null
结论:所有的运算符遇到 NULL 结果都是 NULL,除了<=>
#NULL 值判断与处理
#查询奖金百分比不为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL;
#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL;
#关于 null 值计算
#所有运算符遇到 null 都是 null
#计算实际的薪资: basic_salary + salary * 奖金百分比
#函数:IFNULL(表达式,用什么值代替)
SELECT eid,basic_salary + performance_salary *(1+ commission_pct) FROM t_salary;#错误的
SELECT eid,basic_salary + performance_salary *(1+ IFNULL(commission_pct,0)) FROM t_salary;
#<=>安全等于
#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;
3. MySQL简单的使用
MySQL 是一种开放源代码的关系型数据库管理系统,开发者为瑞典 MySQL AB 公司。在 2008 年 1 月 16 号被 Sun公司收购。而 2009 年,SUN 又被 Oracle 收购。目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL 作为网站数据库(Facebook, Twitter, YouTube)。阿里提出“去 IOE”,更多网站也开始选择 MySQL。
3.1 MySQL服务的启动和停止
关系型数据库分为桌面文件共享型数据库,例如 Access,和 C/S 架构的网络共享型数据库,例如:MySQL,Oracle等。MySQL 软件的服务器端必须先启动,客户端才可以连接和使用使用数据库。
方式一:图形化方式
- “我的电脑/计算机”–>右键–>“管理”–>“服务”–>启动和关闭 MySQL
- “开始菜单”–>“控制面板”–>“管理工具”–>“服务”–>启动和关闭 MySQL
- “任务管理器”–>“服务”–>启动和关闭 MySQL
方式二:通过管理员身份运行
- net start 服务名(启动服务)
- net stop 服务名(停止服务)
3.2 MySQL服务的登录和退出
方式一:通过mysql自带的客户端(只限于root用户)
方式二:通过windows自带的客户端
-
登录:mysql -h 主机名 -P 端口号 -u 用户名 -p 密码
例如:mysql -h localhost -P 3306 -u root -proo -
退出:exit或ctrl+C
3.2.1修改用户密码
mysqladmin 命令用于修改用户密码。
mysqladmin 命令格式:mysqladmin -u 用户名 -p 旧密码 password 新密码
例如:
1、给 root 加个密码 ab12
首先在 DOS 下进入目录 mysql\bin,然后键入以下命令:
mysqladmin -u root -password ab12
注:因为开始时 root 没有密码,所以-p 旧密码一项就可以省略了。
2、再将 root 的密码改为 djg345
mysqladmin -u root -p ab12 password djg345
3.2.2 新增用户
grant on
命令用于增加新用户并控制其权限。
grant on 命令格式:
grant select on 数据库.* to 用户名@登录主机 identified by “密码”
例:
1、增加一个用户 test1,密码为 abc,让他可以在任何主机上登录,并对所有数据库有查询、 插入、修改、删除的权限。首先用 root 用户连入 MYSQL,然后键入以下命令:
grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”
但增加的用户是十分危险的,你想如某个人知道 test1 的密码,那么他就可以在 internet 上 的任何一台电脑上登录你的 mysql 数据库并对你的数据可以为所欲为了,解决办法如下。
2、 增加一个用户 test2 密码为 abc,让他只可以在 localhost 上登录,并可以对数据库 mydb 进行查询、插入、修改、删除的操作(localhost 指本地主机,即 MYSQL 数据库所在的那台主 机),这样用户即使用知道 test2 的密码,他也无法从 internet 上直接访问数据库,只能通过 MYSQL 主机上的 web 页来访问了。
3.3 MySQL的常见命令
注 意
• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写。
• 使用缩进提高语句的可读性。
3.3.1 查看服务器的版本
- 方式一:登录到mysql服务端
select version();
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
- 方式二:没有登录到mysql服务端
mysql --version 或 mysql --V
user当前连接用户
3.3.2 数据库的常见操作
1)创建数据库
create database 库名 DEFAULT CHARACTER SET utf8;(设置默认字符集 UTF-8)
mysql> create database cgb DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)
其中使用SQLyog创建数据库时
字符集说明:
一般选择utf8.下面介绍一下utf8与utfmb4的区别。
utf8mb4兼容utf8,且比utf8能表示更多的字符。至于什么时候用,看你的做什么项目了。
看unicode编码区从1 ~ 126就属于传统utf8区,当然utf8mb4也兼容这个区,126行以下就是utf8mb4扩充区,什么时候你需要存储那些字符,你才用utf8mb4,否则只是浪费空间。
排序说明:
排序一般分为两种:
utf_bin和utf_general_ci
bin 是二进制, a 和 A 会别区别对待.
例如你运行:
-
SELECT * FROM table WHERE txt = ‘a’ 那么在utf8_bin中你就找不到 txt = ‘A’ 的那一行, 而 utf8_general_ci 则可以.
-
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
-
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会照成不良后果
-
u- tf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
-
utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
-
utf8_general_ci校对速度快,但准确度稍差。(准确度够用,一般建库选择这个)
-
utf8_unicode_ci准确度高,但校对速度稍慢。
2)删除数据库库
drop database 库名;
mysql> drop database cgb;
Query OK, 0 rows affected (0.04 sec)
3)查看当前所有的数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cgb2015 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
4)打开指定的库
use 库名;
mysql> use cgb2015;
Database changed
5)查看当前正在使用哪个数据库
select database();
mysql> select database();
+------------+
| database() |
+------------+
| cgb2015 |
+------------+
1 row in set (0.00 sec)
注意:要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上“数据库名.”。
3.3.3 表的常用操作
1)查看当前库的所有表
show tables;
mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses |
| dept |
| emp |
| scores |
| students |
| tb_door |
| tb_user |
| tb_user_addr |
| teachers |
| test |
+-------------------+
10 rows in set (0.01 sec)
2)查看指定库中的所有表
show tables from 库名;
mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
35 rows in set (0.01 sec)
3)创建表
CREATE TABLE 表名称(
字段名 1 数据类型 1 主键 自增长,
字段名 2 数据类型 2 非空 默认值,
字段名 3 数据类型 3
)
ENGINE=当前表格的引擎
AUTO_INCREMENT=自增长的起始值
DEFAULT CHARSET=表数据的默认字符;
create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);
Query OK, 0 rows affected (0.04 sec)
4)查看指定表的结构
desc 表名;
mysql> desc tb_door;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| door_name | varchar(100) | YES | | NULL | |
| tel | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
SHOW CREATE TABLE 表名(查看表的定义)
mysql> SHOW CREATE TABLE tb_door;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_door | CREATE TABLE `tb_door` (
`id` int NOT NULL AUTO_INCREMENT,
`door_name` varchar(100) DEFAULT NULL,
`tel` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
5)删除表
drop table 表名;
注意:数据和结构都被删除
mysql> drop table tb_door;
Query OK, 0 rows affected (0.04 sec)
6)重命名表
alter table 表名 rename 新表名;
rename table 表名 to 新表名;
mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses |
| dept |
| emp |
| scores |
| students |
| tb_door |
| tb_user |
| tb_user_addr |
| teachers |
| test |
+-------------------+
10 rows in set (0.01 sec)
mysql> rename table test to data_test;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+-------------------+
| Tables_in_cgb2015 |
+-------------------+
| courses |
| data_test |
| dept |
| emp |
| scores |
| students |
| tb_door |
| tb_user |
| tb_user_addr |
| teachers |
+-------------------+
10 rows in set (0.00 sec)
3.3.4 表内的常用操作
1)向表中插入记录
insert into 表名 value(字段1属性,字段2属性,字段3属性,...);
#向表中添加指定字段记录,没有被指定的字段会添加默认值
insert into 表名(字段名1,字段名2,字段名3) value(字段1属性,字段2属性,字段3属性,...);
#向tb_door表中插入2条记录
mysql> insert into tb_door values(null,'永和大王1店',666);
mysql> insert into tb_door values(null,' 永和大王2店',888);
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
1、值的顺序、个数与字段列表中字段的顺序、个数一致
- 如果个数少了就报 Column count doesn’t match value count
- 如果 VALUES 前面的()中没有列出字段,那么默认就是为表中的所有字段赋值,那么个>数与顺序与表结构中字段定义的一致
2、关于自增长列,默认值列,允许为 NULL 列的赋值
(1)如果字段列表列出了字段名,那么值列表中就要为其赋值,哪怕它是自增长列,有默认值列,可以为 NULL 值的列。
- InnoDB 表的自动增长列可以手动插入合适的值,但是插入的值如果是 NULL 或者 0,则实际插入的将是自动增长后的值;
- 如果列声明了“默认约束”那么对应的位置可以赋值具体的值,也可以使用“DEFAULT”,表示使用默认值;
- 如果列允许了 NULL 值,那么可以为对应的字段可以赋值为具体值也可以赋值为 NULL
(2)对于没有列出的字段,像自增列就自动赋值,像默认值列就自动赋默认值,像允许 NULL 的列就自动赋 NULL值
3、VALUES 也可以写成 VALUE,但是 VALUES 是标准写法
4、可以同时插入多行
5、如果插入从表的数据,要注意查看主表参照字段的值是否存在
6、值的位置可以是常量值、表达式、函数
2)查询表中记录
select *(全表)/字段名(指定)from 表名;
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name | tel |
+----+-------------------+------+
| 1 | 永和大王1店 | 666 |
| 2 | 永和大王2店 | 888 |
| 3 | 永和大王1店 | 666 |
| 4 | 永和大王2店 | 888 |
+----+-------------------+------+
4 rows in set (0.00 sec)
- 如果 SELECT 后面是*,那么表示查询所有字段
- SELECT 后面的查询列表,可以是表中的字段,常量值,表达式,函数
- 查询的结果是一个虚拟的表
- select 语句,可以包含 5 种子句:依次是 where、 group by、having、 order by、limit 必须照这个顺序。
3)删除表中记录
删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行,而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。
Delete * from 表名 where 条件;
or
TRUNCATE TABLE 表名;
mysql> Delete from tb_door where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> Delete from tb_door where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name | tel |
+----+-------------------+------+
| 1 | 永和大王1店 | 666 |
| 2 | 永和大王2店 | 888 |
+----+-------------------+------+
2 rows in set (0.00 sec)
Truncate使用注意事项
-
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
-
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
-
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。
-
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
-
TRUNCATE TABLE 不能用于参与了索引视图的表。
-
对用TRUNCATE TABLE删除数据的表上增加数据时,要使用UPDATE STATISTICS来维护索引信息。
-
如果有ROLLBACK语句,DELETE操作将被撤销,但TRUNCATE不会撤销。
不能对以下表使用 TRUNCATE TABLE
- 由 FOREIGN KEY 约束引用的表。(您可以截断具有引用自身的外键的表。)
- 参与索引视图的表。
- 通过使用事务复制或合并复制发布的表。
- 对于具有以上一个或多个特征的表,请使用 DELETE 语句。
- TRUNCATE TABLE 不能激活触发器,因为该操作不记录各个行删除。
注意:
1、如果不加 where 条件,表示删除整张表的数据,表结构保留。
delete from 表名;
删除整张表的数据还可以使用 truncate 表名;
区别:
- truncate 相当于删除表再重建一张同名结构的表,操作后得到一张全新表,而 delete 是在原有表中删除数据。如果决定清空一张表的数据,truncate 速度更快一些。
- TRUNCATE 语句不能回滚
2、如果删除主表的记录,要注意查看从表的外键是否有依赖该行的值,如果有
(1)如果外键是 on delete RESTRICT 或 on delete NO ACTION,那么要先处理从表的数据,才能删除
(2)如果外键是 on delete SET NULL 或 on delete CASCADE,那么删除时从表的对应记录也会被置空或跟着删除。
3、可以一次删除多个表的数据
例如:两个表没有建立外键,但逻辑上有外键关系,也可以通过删除多个表的数据来实现级联删除
4、所有正在运行的相关事务被提交。
5、所有相关索引被删除。
drop、truncate与delete的区别
- 效率方面:drop > truncate > delete
- delete语句是DML语言,不会自动提交,这个操作会放在rollback segement中,事物提交后才生效;如果有相应的触发器(trigger),执行的时候将被触发。truncate、drop是DDL语言,操作后即生效,执行后会自动提交,原数据不会放到rollback中,不能回滚,操作不会触发trigger。
- drop一般用于删除整体性数据 如表,模式,索引,视图,完整性限制等;当你不再需要该表时,用 drop;
- 当你仍要保留该表,但要删除所有记录时,用 truncate 或者;
- 当你要删除部分记录时(always with a WHERE clause), 用 delete。
4)修改记录
update 表名 set 修改后的属性 where 条件;
UPDATE 表名称 SET 字段名 1 = 值 1, 字段名 2=值 2,...... 【WHERE 条件】;
UPDATE 表 1,表 2,...... SET 表 1.字段名 1 = 值 1, 表 1.字段名 2=值 2,表 2.字段 1 = 值 1, 表 2.字段 2=值 2...... 【WHERE 条件】;
#修改tb_door表中id为1的记录
mysql> update tb_door set tel=555 where id=1;
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name | tel |
+----+-------------------+------+
| 1 | 永和大王1店 | 555 |
| 2 | 永和大王2店 | 888 |
+----+-------------------+------+
2 rows in set (0.00 sec)
1、如果不写 where 条件,会修改所有行。
2、值可以是常量值、表达式、函数。
3、可以同时更新多张表,如果两个表没有建立外键,但逻辑上有外键关系。
4、如果修改从表外键字段的数据,要注意查看主表参照字段的值是否存在。
5、如果修改主表的被参考的字段的值,要注意查看从表的外键是否有依赖该值,如果有
- 如果外键是 on update RESTRICT 或 on update NO ACTION,那么要先处理从表的数据,才能修改
- 如果外键是 on update SET NULL 或 on update CASCADE,那么直接修改,从表的外键字段会自动处理
5)对列操作
#增加一列
alter table 表名 add column 列名 数据类型; #默认在最后
alter table 表名 add column 列名 数据类型 after 某一列;#在某列之后
alter table 表名 add column 列名 数据类型 first;#在首列
mysql> alter table tb_door add column loc varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from tb_door;
+----+-------------------+------+------+
| id | door_name | tel | loc |
+----+-------------------+------+------+
| 1 | 永和大王1店 | 555 | NULL |
| 2 | 永和大王2店 | 888 | NULL |
+----+-------------------+------+------+
2 rows in set (0.00 sec)
#修改列类型
alter table 表名 modify column 列名 数据类型;
alter table 表名 modify column 列名 数据类型 after 某一列;
alter table 表名 modify column 列名 数据类型 first;
#删除列
alter table 表名 drop column 列名;
mysql> alter table tb_door drop column loc;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from tb_door;
+----+-------------------+------+
| id | door_name | tel |
+----+-------------------+------+
| 1 | 永和大王1店 | 555 |
| 2 | 永和大王2店 | 888 |
+----+-------------------+------+
2 rows in set (0.00 sec)
3.4 数据库的备份与恢复
mysqldump:备份数据库
mysqldump 备份数据库语句的基本语法格式如下:
mysqldump –u user –h host –ppassword dbname[tbname, [tbname...]]> filename.sql
- user 表示用户名称;
- host 表示登录用户的主机名称;
- password 为登录密码;
- dbname 为需要备 份的数据库名称;
- tbname 为 dbname 数据库中需要备份的数据表,可以指定多个需要备份的表;
- 右箭头符号“>”告诉 mysqldump 将备份数据表的定义和数据写入备份文件;filename.sql 为备份文件的名称。
【例】使用 mysqldump 命令备份数据库中的所有表,执行过程如下: 打开操作系统命令行输入窗口,输入备份命令如下:
输入密码之后,MySQL 便对数据库进行了备份,在 C:\backup 文件夹下面查看刚才备份过的文件。
mysql 和 source:还原数据库
对于已经备份的包含 CREATE、INSERT 语句的文本文件,可以使用 mysql 命令导入到数据库中。
备份的 sql 文件中包含 CREATE、INSERT 语句(有时也会有 DROP 语句)。mysql 命令可以直接执行文件中的这些语句。其语法如下:
mysql –u user –p [dbname] < filename.sql
- user 是执行 backup.sql 中语句的用户名;
- -p 表示输入用户密码;
- dbname 是数据库名。
如果 filename.sql 文件为 mysqldump 工具创建的包含创建数据库语句的文件,执行的时候不需要指定数 据库名。
【例 1】使用 mysql 命令将 C:\backup\booksdb_20130301.sql 文件中的备份导入到数据库中,输 入语句如下:
mysql –u root –p booksDB < C:/backup/booksdb_20130301.sql
执行该语句前,必须先在 MySQL 服务器中创建 booksDB 数据库,如果不存在恢复过程将会出错。命令执行成功之后 booksdb_20130301.sql 文件中的语句就会在指定的数据库中恢复以前的表。
如果已经登录 MySQL 服务器,还可以使用 source 命令导入 sql 文件。source 语句语法如下:
source filename
【例 2 】 使 用 root 用 户 登 录 到 服 务 器 , 然 后 使 用 source 导 入 本 地 的 备 份 文 件
booksdb_20110101.sql,输入语句如下:
命令执行后,会列出备份文件 booksDB_20130301.sql 中每一条语句的执行结果。source 命令 执行成功后,booksDB_20130301.sql 中的语句会全部导入到现有数据库中。
mysqlhotcopy:快速恢复数据库
mysqlhotcopy 备份后的文件也可以用来恢复数据库,在 MySQL 服务器停止运行时,将备份的 数据库文件复制到 MySQL 存放数据的位置(MySQL 的 data 文件夹),重新启动 MySQL 服务即 可。如果以根用户执行该操作,必须指定数据库文件的所有者,输入语句如下:
chown -R mysql.mysql /var/lib/mysql/dbname
【例】从 mysqlhotcopy 复制的备份恢复数据库,输入语句如下:
cp -R /usr/backup/test usr/local/mysql/data
执行完该语句,重启服务器,mysql将恢复到备份状态。
4. DQL 数据查询语言基础
4.1 准备测试数据
1)部门表 dept
字段名称 | 数据类型 | 是否为空 | 备注 |
---|---|---|---|
deptno | int | 部门编号,PK主键 | |
dname | varchar(20) | Y | 部门名称 |
loc | varchar(13) | Y | 部门所在地点 |
#部门表
CREATE TABLE dept(
depton INT PRIMARY KEY AUTO_INCREMENT,#设置主键,自动递增
dname VARCHAR(20),
loc VARCHAR(13),
);
INSERT INTO dept VALUES(NULL,'accounting','一区');
INSERT INTO dept VALUES(NULL,'research','二区');
INSERT INTO dept VALUES(NULL,'operations','二区');
2)员工表 emp
字段名称 | 数据类型 | 是否为空 | 备注 |
---|---|---|---|
empno | int | 员工编号,PK主键 | |
ename | varchar(10) | Y | 员工名称 |
job | varchar(10) | Y | 职位 |
mgr | int | Y | 上级编号 |
hiredate | datetime | Y | 入职时间 |
sal | double | Y | 月工资 |
comm | NUMERIC(8,2) | Y | 奖金 |
deptno | int | Y | 所属部门,FK外键 |
CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),#奖金
deptno int
);
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);
4.2 基础查询
语法:
SELECT 要查询的东西
FROM 表名;
类似于Java中 :System.out.println(要打印的东西);
特点:
- 通过select查询完的结果 ,是一个虚拟的表格,不是真实存在
- 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
字符函数
1)LOWER
lower() --数据转小写
SELECT LOWER(字段名) FROM 表名;
SELECT dname,LOWER(dname) FROM dept;
dname LOWER(dname)
---------- --------------
accounting accounting
operations operations
research research
2)UPPER
upper() --数据转大写
SELECT UPPER(字段名) FROM 表名;
SELECT dname,UPPER(dname) FROM dept;
dname UPPER(dname)
---------- --------------
accounting ACCOUNTING
operations OPERATIONS
research RESEARCH
3)LENGTH
length() --数据的长度(底层用了UTF-8字符集,一个字母或数字占1字节,一个汉字占3字节)
SELECT LENGTH(字段名) FROM 表名;
SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;
dname LENGTH(dname) loc LENGTH(loc)
---------- ------------- ------ -------------
accounting 10 一区 6
operations 10 二区 6
research 8 二区 6
4)SUBSTR
substr(num1,num2) --截取子串从num1到num2截取数据,数据下标从1开始
SELECT SUBSTR(字段名,num1,num2) FROM 表名;
SELECT dname,SUBSTR(dname,1,3) FROM dept;#从第一个字符开始截取,截取出来3个字符
SELECT dname,SUBSTR(dname,2) FROM dept;#从第二个字符开始都截取完
SELECT dname,SUBSTR(dname,2,5) FROM dept;#从第二个字符开始,截取出来5个字符
SELECT dname,SUBSTR(dname,1,3),SUBSTR(dname,2),SUBSTR(dname,2,5) FROM dept;
dname SUBSTR(dname,1,3) SUBSTR(dname,2) SUBSTR(dname,2,5)
---------- ----------------- --------------- -------------------
accounting acc ccounting ccoun
operations ope perations perat
research res esearch esear
5)CONCAT
connat() --拼接字符串
select CONCAT(字段名,'要拼接的字符串') from dept;
SELECT dname,CONCAT(dname,'123','ABC') FROM dept;#在dname字段属性后拼接'123''ABC'
dname CONCAT(dname,'123','ABC')
---------- ---------------------------
accounting accounting123ABC
operations operations123ABC
research research123ABC
6)REPLACE
replace() --将指定字符串替换
SELECT REPLACE(字段名,'指定被替换的字符串','替换的字符串') FROM 表名;
SELECT dname,REPLACE(dname,'a','666') FROM dept;#把a字符替换成666
dname REPLACE(dname,'a','666')
---------- --------------------------
accounting 666ccounting
operations oper666tions
research rese666rch
7)DISTINCT
distinct() --使用distinct关键字,去除重复的记录行
SELECT DISTINCT 字段名 FROM 表名;
SELECT loc FROM dept;
loc
-----------
一区
二区
二区
SELECT DISTINCT loc FROM dept;
loc
-----------
一区
二区
8)\ 转义字符
将 \ 后的sql语句符号转为字符
#' 作为sql语句符号,内容中出现单撇就会乱套,进行转义即可
#单引号是一个SQL语句的特殊字符
#select 'ab'cd' (这样写会报错)
#数据中有单引号时,用一个\转义变成普通字符
SELECT 'ab\'cd' ;#显示字符串ab'cd
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
数学函数
1)ROUND
round() --数值四舍五入,并保留num位
SELECT ROUND(字段名,num) FROM; 表名;#数值四舍五入,并保留num位
SELECT comm,ROUND(comm) FROM emp;#直接四舍五入取整
comm ROUND(comm)
------- -------------
(NULL) (NULL)
2000.00 2000
1000.00 1000
200.12 200
200.58 201
SELECT comm,ROUND(comm,1) FROM emp;#四舍五入并保留一位小数
comm ROUND(comm,1)
------- ---------------
(NULL) (NULL)
2000.00 2000.0
1000.00 1000.0
200.12 200.1
200.58 200.6
2)CEIL
ceil() --数值向上取整
SELECT CEIL(字段名) FROM 表名;
SELECT comm,FLOOR(comm) FROM emp;
comm FLOOR(comm)
------- -------------
(NULL) (NULL)
2000.00 2000
1000.00 1000
200.12 200
200.58 200
3)FLOOR
floor() --数值向下取整
SELECT CEIL(字段名) FROM 表名;
SELECT comm,FLOOR(comm) FROM emp;
comm FLOOR(comm)
------- -------------
(NULL) (NULL)
2000.00 2000
1000.00 1000
200.12 200
200.58 200
rand 随机数
mod取余
truncate截断
日期函数
1)NOW
now() --当前系统日期+时间
SELECT NOW();
NOW()
---------------------
2021-07-04 21:44:46
2)CURDATE
curdate() --当前系统日期
SELECT CURDATE();
CURDATE()
------------
2021-07-04
3)CURTIME
curtime() --当前系统时间
SELECT CURTIME();
CURTIME()
-----------
21:46:09
year() 年
month() 月
day() 日
hour() 时
minute() 分
second() 秒
SELECT NOW(),
YEAR(NOW()),MONTH(NOW()),DAY(NOW()),
HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
NOW() YEAR(NOW()) MONTH(NOW()) DAY(NOW()) HOUR(NOW()) MINUTE(NOW()) SECOND(NOW())
------------------- ----------- ------------ ---------- ----------- ------------- ---------------
2021-07-04 21:46:58 2021 7 4 21 46 58
SELECT YEAR('1997-06-02 20:20:20') AS Y,
MONTH('1997-06-02 20:20:20') AS MON,
DAY('1997-06-02 20:20:20') AS D,
HOUR('1997-06-02 20:20:20') AS H,
MINUTE('1997-06-02 20:20:20') AS MIN,
SECOND('1997-06-02 20:20:20') AS S;
Y MON D H MIN S
------ ------ ------ ------ ------ --------
1997 6 2 20 20 20
4)DATE_FORMAT
date_format 日期格式,将日期转换成字符
#统计2018年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2018-01-01';
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1
200 tony 总监 100 2015-02-02 10000 2000.00 2
300 hana 经理 200 2017-02-02 8000 1000.00 2
str_to_date 将字符转换成日期
聚合函数
我们经常需要汇总数据而不用把他们的实际检索出来,为此SQL提供了专门的函数。使用这些函数,SQL查询可用于检索,以便分析和报表生成
AVG(【DISTINCT】 expr) 返回 expr 的平均值
COUNT(【DISTINCT】 expr)返回 expr 的非 NULL 值的数目
MIN(【DISTINCT】 expr) 返回 expr 的最小值
MAX(【DISTINCT】 expr) 返回 expr 的最大值
SUM(【DISTINCT】 expr)返回 expr 的总和
特点:
- 以上五个分组函数都忽略null值,除了count(*)
- sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
- 都可以搭配distinct使用,用于统计去重后的结果
- count的参数可以支持:字段、* 、常量值,一般放1,建议使用 count(*)
#聚合所有员工薪水
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp;
#max最大值 min最小值 sum求和 avg平均数
max(sal) MIN(sal) SUM(sal) AVG(sal)
-------- -------- -------- ----------
90000 3000 114500 22900
#count 个数
SELECT COUNT(*) FROM emp #低效
SELECT COUNT(1) FROM emp #高效
COUNT(1)
----------
5
SELECT COUNT(empno) FROM emp
COUNT(empno)
--------------
5
SELECT COUNT(comm) FROM emp #按照字段名统计个数,如果字段值是null将不做统计
COUNT(comm)
-------------
4
用 count(*),count(1),谁好呢?
其实,对于 myisam 引擎的表,没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb 的表,用 count(*)直接读行数,效率很低 , 因为 innodb 真的要去数一遍.
4.3 条件查询
条件查询:根据条件过滤原始表的数据,查询到想要的数据
语法:
SELECT
要查询的字段|表达式|常量值|函数
FROM
表
WHERE
条件 ;
分类:
一、条件表达式
示例:salary>10000
条件运算符:
< >= <= = != <>
二、逻辑表达式
示例:salary>10000 && salary<20000逻辑运算符:
- and(&&):两个条件如果同时成立,结果为true,否则为false
- or(||):两个条件只要有一个成立,结果为true,否则为false
- not(!):如果条件成立,则not后为false,否则为true
三、模糊查询
示例:last_name like ‘a%’
1)WHERE
where() --用来过滤数据(注意:where中不能使用列别名!!)
SELECT *(表内)/字段名(字段内)
FROM 表名
WHERE 条件;
#SQL的执行顺序 from where select
#查询部门编号=1的记录
SELECT * FROM dept WHERE deptno = 1; #只查一条--高效
deptno dname loc
------ ---------- --------
1 accounting 一区
#查询在二区的的部门
SELECT * FROM dept WHERE loc = "二区";
deptno dname loc
------ ---------- --------
3 operations 二区
2 research 二区
#查询在一区的编号是1的部门名称
SELECT dnameFROM dept WHERE loc = "一区" AND depton = 1;#and 高效
dname
----------
accounting
#查询在一区的部门或者编号是3的部门名称#or
SELECT dname FROM dept WHERE loc = "一区" OR deptno = 3; #or 低效
dname
------------
accounting
operations
2)LIKE
like --模糊查询,其中通配符%代表0到n个字符,通配符下划线_代表1个字符
#like 模糊的条件%占位符匹配0~N个字符
SELECT * FROM 表名 WHERE 字段名 LIKE 'xx%'; #以xx开头的部门
SELECT * FROM 表名 WHERE 字段名 LIKE '%o%';#模糊的条件,查询内容里包含xx的部门
SELECT * FROM 表名 WHERE 字段名 LIKE '%ch'; #以xx结尾的部门
#明确的条件,查询名称为accounting的部门
SELECT * FROM dept WHERE dname = 'accounting';
deptno dname loc
------ ---------- --------
1 accounting 一区
#查询以ch结尾的部门
SELECT * FROM dept WHERE dname LIKE '%ch';
deptno dname loc
------ -------- --------
2 research 二区
#查询在一区的部门或者名称包含ting的部门
SELECT * FROM dept WHERE loc="一区" OR dname LIKE '%ting%';
deptno dname loc
------ ---------- --------
1 accounting 一区
3)NULL
is null --过滤字段值为空的数据
is not null --过滤字段值不为空的数据
select * from 表名 where 字段名 is null --过滤字段值为空的数据
select * from 表名 where 字段名 is not null --过滤字段值不为空的数据
#查询comm 是空的员工的信息
SELECT * FROM emp WHERE comm IS NULL;
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------ --------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1
#查询comm 不为空的员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL;
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
200 tony 总监 100 2015-02-02 10000 2000.00 2
300 hana 经理 200 2017-02-02 8000 1000.00 2
400 leo 员工 300 2019-02-22 3000 200.12 2
500 liu 员工 300 2019-03-19 3500 200.58 2
4)IFNULL
在SQL中null不参与运算,运算中出现null值就为null,需要使用ifnull(字段名,转换值)进行转换
#查询每个员工的月薪
SELECT * , sal + comm FROM emp;
#奖金为null 月薪不为null 相加后变为了null,数据出现了错误
empno ename job mgr hiredate sal comm deptno sal + comm
------ ------ ------ ------ ---------- ------ ------- ------ ------------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1 (NULL)
200 tony 总监 100 2015-02-02 10000 2000.00 2 12000
300 hana 经理 200 2017-02-02 8000 1000.00 2 9000
400 leo 员工 300 2019-02-22 3000 200.12 2 3200.12
500 liu 员工 300 2019-03-19 3500 200.58 2 3700.58
#改进
SELECT *, sal + IFNULL(comm,0) FROM emp;
#null不参与运算,需要特殊处理
empno ename job mgr hiredate sal comm deptno sal + ifnull(comm,0)
------ ------ ------ ------ ---------- ------ ------- ------ ----------------------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1 90000
200 tony 总监 100 2015-02-02 10000 2000.00 2 12000
300 hana 经理 200 2017-02-02 8000 1000.00 2 9000
400 leo 员工 300 2019-02-22 3000 200.12 2 3200.12
500 liu 员工 300 2019-03-19 3500 200.58 2 3700.58
5)BETWEEN AND
between 条件1 and 条件2 --在条件1和条件2之间查询
SELECT * FROM 表名 WHERE 字段名 BETWEEN 条件1 AND 条件2;
#查询工资(5000,10000)的员工信息
SELECT * FROM emp WHERE SAL>5000 AND SAL<10000;
#等效于
SELECT * FROM emp WHERE SAL BETWEEN 5000 AND 10000;
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
300 hana 经理 200 2017-02-02 8000 1000.00 2
6)LIMIT
limit --在mysql中,通过limit进行分页查询。
例如:分数最高的记录<按分数排序后,limit n,返回前n条。
SELECT * FROM 表名 LIMIT num;#从表中取num行数据
SELECT * FROM 表名 LIMIT num1,num2;#从表中num1行开始取,取num2行数据
SELECT * FROM emp LIMIT 2;#取前两条
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1
200 tony 总监 100 2015-02-02 10000 2000.00 2
SELECT * FROM emp LIMIT 1,3;#从1开始(第2条记录)开始,取三条记录
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
200 tony 总监 100 2015-02-02 10000 2000.00 2
300 hana 经理 200 2017-02-02 8000 1000.00 2
400 leo 员工 300 2019-02-22 3000 200.12 2
流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
4.4 排序查询
语法:
SELECT
要查询的东西
FROM
表
WHERE
条件
ORDER BY 排序的字段|表达式|函数|别名 【asc | desc】;
ORDER BY
order by – 排序
- ASC 升序(默认的)
- DESC 降序
#order by 排序 升序(默认的)、降序
#按照入职日期排序,默认升序 ASC可不写
SELECT * FROM emp WHERE hiredate < '2018-1-1' ORDER BY hiredate ASC;
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1
200 tony 总监 100 2015-02-02 10000 2000.00 2
300 hana 经理 200 2017-02-02 8000 1000.00 2
500 liu 员工 300 2019-03-19 3500 200.58 2
400 leo 员工 300 2019-02-22 3000 200.12 2
#统计2015年以前入职的老员工,默认升序
SELECT * FROM emp WHERE hiredate < '2018-1-1' ORDER BY hiredate;
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------- --------
100 jack 副总 (NULL) 2002-05-01 90000 (NULL) 1
200 tony 总监 100 2015-02-02 10000 2000.00 2
300 hana 经理 200 2017-02-02 8000 1000.00 2
#查询每个员工入职了几年,并按降序排列
SELECT ename,YEAR(NOW())-YEAR(hiredate) AS working_age FROM emp ORDER BY YEAR(NOW())-YEAR(hiredate) DESC;
ename working_age
------ -------------
jack 19
tony 6
hana 4
leo 2
liu 2
4.5 分组查询
用于对查询的结果进行分组统计
语法
SELECT
查询的字段,分组函数
FROM
表
GROUP BY 分组的字段;
特点:
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
- 针对的表 位置 关键字
- 分组前筛选: 原始表 group by的前面 where
- 分组后筛选: 分组后的结果集 group by的后面 having
4、可以按多个字段分组,字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
GROUP BY
用于对查询的结果进行分组统计group by表示分组, having 子句类似where过滤返回的结果
关于 mysql 的 group by 的特殊:
- 在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的,换句话说,SELECT 列表中最好不要出现 GROUP 。
- 如果查询时,出现了聚合列和非聚合列,通常要按照非聚合列分组
#统计 每个职位的人数
SELECT COUNT(1) FROM emp GROUP BY job;
job COUNT(1)
------ ----------
副总 1
总监 1
经理 1
员工 2
#查询 每种岗位的平均工资和岗位名称
SELECT AVG(sal),job FROM emp GROUP BY job;#按非聚合列分组
AVG(sal) job
-------- --------
90000 副总
10000 总监
8000 经理
3250 员工
#查询 每个岗位的平均工资 再过滤出<10000的
SELECT job,AVG(sal)
FROM emp
#可以分组前过滤用where,但是where里不能出现聚合函数
#分组之前需要过滤,使用where --高效
#WHERE AVG(sal)<10000 (运行报错 Invalid use of group function)
GROUP BY job;
#分组后需要过滤,使用having --相对低效
HAVING AVG(sal)>10000;
job avg(sal)
------ ----------
经理 8000
员工 3250
5. 约束 (CONSTRAINTS)
字段约束
数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据的完整性要从以下四个方面考虑:
-
实体完整性(Entity Integrity)
例如,同一个表中,不能存在两条完全相同无法区分的记录 -
域完整性(Domain Integrity)
例如:年龄范围 0-120,性别范围“男/女” -
引用完整性(Referential Integrity)
例如:员工所在部门,在部门表中要能找到这个部门 -
用户自定义完整性(User-defined Integrity)
例如:用户名唯一、密码不能为空等,本部门经理的工资不得高于本部门职工的平均工资的 5 倍。
根据约束的特点,分为几种:
- 键约束:主键约束、外键约束、唯一键约束
- Not NULL 约束:非空约束
- Check 约束:检查约束
- Default 约束:缺省约束
主键约束 primary key
主键: Primary key,简称 PK,数据库主键作用保证实体的完整性,可以是一个列或多列的组合。
主键约束: 如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。
主键自增策略: 当主键为数值类型时,为了方便维护,可以设置主键自增策略(auto_increment),设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中。而且每次用完AUTO_INCREMENT值,都会自增1。
设置主键与查看某个表的约束和索引
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
SHOW INDEX FROM 表名称;
SHOW CREATE TABLE 表名;
SHOW INDEX FROM 表名称;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
------ ---------- ----------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ------------- ------- ------------
dept 0 PRIMARY 1 deptno A 5 (NULL) (NULL) BTREE YES (NULL)
dept 1 dname_index 1 dname A 4 (NULL) (NULL) YES BTREE YES (NULL)
dept 1 fuhe 1 dname A 4 (NULL) (NULL) YES BTREE YES (NULL)
dept 1 fuhe 2 loc A 4 (NULL) (NULL) YES BTREE YES (NULL)
SHOW CREATE TABLE 表名;
Table Create Table
------ -----------------------------------------------------------------------
dept CREATE TABLE `dept` (
`deptno` int NOT NULL AUTO_INCREMENT,
`dname` varchar(20) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`),
KEY `dname_index` (`dname`),
KEY `fuhe` (`dname`,`loc`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3
如何删除主键?
删除主键约束,不需要指定主键名,一个表只有一个主键
alter table 表名称 drop
添加主键约束,例如将id设置为主键:
#主键是一条记录的唯一标识,具有唯一性,不能重复
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
PRIMARY KEY (id)
);
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
INSERT INTO tb_user (id,NAME) VALUES(1,'hellen');
#第二句插入就会报错:提示主键1的值已经存在,重复了
Query : INSERT INTO tb_user (id,NAME) VALUES(1,'hellen')
Error Code : 1062
Duplicate entry '1' for key 'PRIMARY'
- 主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值,如果是多列组合的主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
- 每个表有且最多只允许一个主键约束。
- MySQL 的主键名总是 PRIMARY,就算自己命名了主键约束名也没用。
- 当创建主键约束时,MySQL 默认在对应的列上建立主键索引。删除主键时,也会直接删除主键索引。
外键约束 forgrein key
外键约束:Foreign key: 简称 FK。外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系,使用关键字references进行连接。
- 在创建外键约束时,如果不给外键约束名称,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
- 当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是列名,不是外键的约束名
- 删除外键时,关于外键列上的普通索引需要单独删除。
注意:
- 在从表上建立外键,而且主表要先存在。
- 从表的外键列,在主表中引用的只能是键列(主键,唯一键,外键)。
- 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样
- 一个表可以建立多个外键约束
- 从表的外键值必须"在主表中能找到"或者为空,从而约束了从表的外键列的值的添加和修改。
- 当主表的记录被从表参照时,主表中被参考记录的删除和更新也会受到限制。
- 默认情况下,主表和从表是严格依赖关系 RESTRICT。当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
- 但是有一种是级联“修改、删除”:
- ON DELETE SET NULL(级联置空):当外键设置了 SET NULL,当主表的相关记录删除时,从表对应的字段改为NULL。注意从表外键字段得允许为空才行
- ON DELETE CASCADE(级联删除):当外键设置了 CASCADE(级联),当主表的相关记录删除时,从表对应的行都删除了。
- 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
- 如果要删除表,需要先删除从表,才能删除主表。
DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除,慎用会丢失数据
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),#check核查 age的数据需要大于0小于200
);
CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(200),
#tb_user_address中user_id字段录入tb_user表不存在的主键值,将报错
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
DESC tb_user;
如何删除外键约束?
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;
如何删除外键列上的索引?
ALTER TABLE 表名称 DROP INDEX 外键列索引名;
ALTER TABLE t_emp DROP
唯一约束 unique
唯一约束: Unique key,简称 UK,如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一,也可以多个列组合值的唯一。
- MySQL 会给唯一约束的列上默认创建一个唯一索引。
- 删除唯一键只能通过删除唯一索引的方式删除,删除时需要指定唯一索引名,唯一索引名就是唯一约束名一样。
如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同,如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
如何删除唯一性约束?
ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
注意:如果忘记名称,可以通过查看表的约束或索引的方式查看
添加唯一约束,例如为username添加唯一约束及非空约束:
Name字段创建了唯一约束,插入数据时数据库会进行检查,如果插入的值相同,就会检查报错:
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user(
id INT,
NAME VARCHAR(30) UNIQUE NOT NULL,--唯一约束且非空
phone VARCHAR(20) UNIQUE NOT NULL,--唯一约束且非空
email VARCHAR(30) UNIQUE NOT NULL,--唯一约束且非空
PRIMARY KEY (id)
);
DESC tb_user;
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');--NAME的值要唯一,重复会报错的
INSERT INTO tb_user (id,NAME) VALUES(2,'tony');
#执行上面语句出错:
Query : INSERT INTO tb_user (id,NAME) VALUES(2,'tony')
Error Code : 1062
Duplicate entry 'tony' for key 'name'
#展示表结构:
DESC tb_user;
主键和唯一键的区别:
(1)主键是非空,唯一键允许空
(2)主键一个表只能一个,唯一键可以有多个
非空约束 not null
非空约束: 如果为一个列添加了非空约束,那么这个列的值就不能为空,为空会报错,但可以重复。
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(30) UNIQUE NOT NULL,
age INT,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(30) UNIQUE NOT NULL,
PRIMARY KEY (id)
);
DESC tb_user;
#id为自增主键,null值无效,数据库会自动用下一个id值替代
#age因为运行为null,所以可以设置为null
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);
*********************************************************************************
create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK
检查约束 check
注意: MySQL 不支持 check 约束,但可以使用 check 约束,而没有任何效果。
例如:age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),#录入age超过200将报错
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
默认约束 default
默认约束: default,默认值,在插入数据时某列如果没指定其他的值,那么会将默认值添加到新记录。
默认值
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #default 默认值 如果不添加指定值就默认添加数据 “男”
phone CHAR(18),
age INT,
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
自增列(AUTO_INCREMENT)
CREATE TABLE t_stu(
sid INT PRIMARY KEY AUTO_INCREMENT, #给主键设置自增键
sname VARCHAR(100) NOT NULL,
gender CHAR NOT NULL DEFAULT '男',
birthday DATE, address VARCHAR(200)
);
关于自增长 auto_increment:
- 整数类型的字段才可以设置自增长。
- 当需要产生唯一标识符或顺序值时,可设置自增长。
- 一个表最多只能有一个自增长列
- 自增长列必须非空
- 自增长列必须是主键列或唯一键列。
- InnoDB 表的自动增长列可以手动插入,但是插入的值如果是空或者 0,则实际插入的将是自动增长后的值。
6. 视图 view
概述
视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。
通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询中使用的表,使用视图动态生成。
基表:用来创建视图的表叫做基表base table
视图的诸多优点
1)简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
2)安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。
总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。
创建视图
//基本格式:
create view <视图名称>[(column_list)]
as select语句
with check option;
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
1)OR REPLACE:表示替换已有视图
2)ALGORITHM:表示视图选择算法,默认算法是UNDEFINED(未定义的):
MySQL自动选择要使用的算法 ;merge合并;temptable临时表
3)select_statement:表示select语句
4)[WITH [CASCADED | LOCAL] CHECK OPTION]:表示视图在更新时保证在视图的权限范围之内
- cascade是默认值,表示更新视图的时候,要满足视图和表的相关条件
- local表示更新视图的时候,要满足该视图定义的一个条件即可
TIPS:推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项,可以保证数据的安全性
在单表上创建视图
mysql> create view v_F_players(编号,名字,性别,电话)
-> as
-> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
-> where SEX='F'
-> with check option;
Query OK, 0 rows affected (0.00 sec)
mysql> desc v_F_players;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 编号 | int(11) | NO | | NULL | |
| 名字 | char(15) | NO | | NULL | |
| 性别 | char(1) | NO | | NULL | |
| 电话 | char(13) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from v_F_players;
+--------+-----------+--------+------------+
| 编号 | 名字 | 性别 | 电话 |
+--------+-----------+--------+------------+
| 8 | Newcastle | F | 070-458458 |
| 27 | Collins | F | 079-234857 |
| 28 | Collins | F | 010-659599 |
| 104 | Moorman | F | 079-987571 |
| 112 | Bailey | F | 010-548745 |
+--------+-----------+--------+------------+
5 rows in set (0.02 sec)
在多表上创建视图
mysql> create view v_match
-> as
-> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
-> from
-> PLAYERS a,MATCHES b,TEAMS c
-> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_match;
+----------+-----------+---------+-----+------+--------+----------+
| PLAYERNO | NAME | MATCHNO | WON | LOST | TEAMNO | DIVISION |
+----------+-----------+---------+-----+------+--------+----------+
| 6 | Parmenter | 1 | 3 | 1 | 1 | first |
| 44 | Baker | 4 | 3 | 2 | 1 | first |
| 83 | Hope | 5 | 0 | 3 | 1 | first |
| 112 | Bailey | 12 | 1 | 3 | 2 | second |
| 8 | Newcastle | 13 | 0 | 3 | 2 | second |
+----------+-----------+---------+-----+------+--------+----------+
5 rows in set (0.04 sec)
视图将我们不需要的数据过滤掉,将相关的列名用我们自定义的列名替换。视图作为一个访问接口,不管基表的表结构和表名有多复杂。
如果创建视图时不明确指定视图的列名,那么列名就和定义视图的select子句中的列名完全相同;
如果显式的指定视图的列名就按照指定的列名。
注意:显示指定视图列名,要求视图名后面的列的数量必须匹配select子句中的列的数量。
查看视图
1、使用show create view语句查看视图信息
mysql> show create view v_F_players\G;
*************************** 1. row ***************************
View: v_F_players
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`v_F_players` AS select `PLAYERS`.`PLAYERNO` AS `编号`,
`PLAYERS`.`NAME` AS `名字`,`PLAYERS`.`SEX` AS `性别`,
`PLAYERS`.`PHONENO` AS `电话` from `PLAYERS`
where (`PLAYERS`.`SEX` = 'F') WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
2、视图一旦创建完毕,就可以像一个普通表那样使用,视图主要用来查询
mysql> select * from view_name;
3、有关视图的信息记录在information_schema数据库中的views表中
mysql> select * from information_schema.views
-> where TABLE_NAME='v_F_players'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: TENNIS
TABLE_NAME: v_F_players
VIEW_DEFINITION: select `TENNIS`.`PLAYERS`.`PLAYERNO` AS `编号`,`TENNIS`.`PLAYERS`.`NAME` AS `名字`,`TENNIS`.`PLAYERS`.`SEX` AS `性别`,`TENNIS`.`PLAYERS`.`PHONENO` AS `电话` from `TENNIS`.`PLAYERS` where (`TENNIS`.`PLAYERS`.`SEX` = 'F')
CHECK_OPTION: CASCADED
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)
视图的更改
CREATE OR REPLACE VIEW语句修改视图
基本格式:
create or replace view view_name as select语句;
在视图存在的情况下可对视图进行修改,视图不在的情况下可创建视图
ALTER语句修改视图
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
DML操作更新视图
因为视图本身没有数据,因此对视图进行的dml操作最终都体现在基表中
mysql> create view v_student as select * from student;
mysql> select * from v_student;
+--------+--------+------+
| 学号 | name | sex |
+--------+--------+------+
| 1 | 张三 | M |
| 2 | 李四 | F |
| 5 | 王五 | NULL |
+--------+--------+------+
mysql> update v_student set name='钱六' where 学号='1';
mysql> select * from student;
+--------+--------+------+
| 学号 | name | sex |
+--------+--------+------+
| 1 | 钱六 | M |
| 2 | 李四 | F |
| 5 | 王五 | NULL |
+--------+--------+------+
当然,视图的DML操作,不是所有的视图都可以做DML操作。
有下列内容之一,视图不能做DML操作:
- select子句中包含distinct
- select子句中包含组函数
- select语句中包含group by子句
- select语句中包含order by子句
- select语句中包含union 、union all等集合运算符
- where子句中包含相关子查询
- from子句中包含多个表
- 如果视图中有计算列,则不能更新
- 如果基表中有某个具有非空约束的列未出现在视图定义中,则不能做insert操作
drop删除视图
删除视图是指删除数据库中已存在的视图,删除视图时,只能删除视图的定义,不会删除数据,也就是说不动基表:
DROP VIEW [IF EXISTS] view_name [, view_name] ...
如果视图不存在,则抛出异常;使用IF EXISTS选项使得删除不存在的视图时不抛出异常。
使用WITH CHECK OPTION约束
对于可以执行DML操作的视图,定义时可以带上WITH CHECK OPTION约束
作用:
对视图所做的DML操作的结果,不能违反视图的WHERE条件的限制。
示例:创建视图,包含1960年之前出生的所有球员(老兵)
mysql> create view v_veterans
-> as
-> select * from PLAYERS
-> where birth_date < '1960-01-01'
-> with check option;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_veterans;
+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
| PLAYERNO | NAME | INITIALS | BIRTH_DATE | SEX | JOINED | STREET | HOUSENO | POSTCODE | TOWN | PHONENO | LEAGUENO |
+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
| 2 | Everett | R | 1948-09-01 | M | 1975 | Stoney Road | 43 | 3575NH | Stratford | 070-237893 | 2411 |
| 39 | Bishop | D | 1956-10-29 | M | 1980 | Eaton Square | 78 | 9629CD | Stratford | 070-393435 | NULL |
| 83 | Hope | PK | 1956-11-11 | M | 1982 | Magdalene Road | 16A | 1812UP | Stratford | 070-353548 | 1608 |
+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
3 rows in set (0.02 sec)
此时,使用update对视图进行修改:
mysql> update v_veterans
-> set BIRTH_DATE='1970-09-01'
-> where PLAYERNO=39;
ERROR 1369 (HY000): CHECK OPTION failed 'TENNIS.v_veterans'
因为违反了视图中的WHERE birth_date < '1960-01-01’子句,所以抛出异常;
利用with check option约束限制,保证更新视图是在该视图的权限范围之内。
嵌套视图
嵌套视图:定义在另一个视图的上面的视图
mysql> create view v_ear_veterans
-> as
-> select * from v_veterans
-> where JOINED < 1980;
使用WITH CHECK OPTION约束时,(不指定选项则默认是CASCADED)
可以使用CASCADED或者 LOCAL选项指定检查的程度:
1)WITH CASCADED CHECK OPTION:检查所有的视图
例如:嵌套视图及其底层的视图
2)WITH LOCAL CHECK OPTION:只检查将要更新的视图本身
对嵌套视图不检查其底层的视图
定义视图时的其他选项
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
1、ALGORITHM选项:选择在处理定义视图的select语句中使用的方法
-
UNDEFINED:MySQL将自动选择所要使用的算法
-
MERGE:将视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分
-
TEMPTABLE:将视图的结果存入临时表,然后使用临时表执行语句
缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED
2、DEFINER选项:指出谁是视图的创建者或定义者
-
definer= ‘用户名’@‘登录主机’
-
如果不指定该选项,则创建视图的用户就是定义者,指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同
3、SQL SECURITY选项:要查询一个视图,首先必须要具有对视图的select权限。
但是,如果同一个用户对于视图所访问的表没有select权限,那会怎么样?
SQL SECURITY选项决定执行的结果:
-
SQL SECURITY DEFINER:定义(创建)视图的用户必须对视图所访问的表具有select权限,也就是说将来其他用户访问表的时候以定义者的身份,此时其他用户并没有访问权限。
-
SQL SECURITY INVOKER:访问视图的用户必须对视图所访问的表具有select权限。
缺省SQL SECURITY选项等同于SQL SECURITY DEFINER
视图权限总结:
使用root用户定义一个视图(推荐使用第一种):u1、u2
1)u1作为定义者定义一个视图,u1对基表有select权限,u2对视图有访问权限:u2是以定义者的身份访问可以查询到基表的内容;
2)u1作为定义者定义一个视图,u1对基表没有select权限,u2对视图有访问权限,u2对基表有select权限:u2访问视图的时候是以调用者的身份,此时调用者是u2,可以查询到基表的内容。
7. 索引 index
定义
MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构,是对数据库表中一列或多列的值进行排序的一种结构。可以得到索引的本质:索引是数据结构。可以简单理解为是一种排好序的快速查找的数据结构。
索引是一个单独的、物理的数据库结构,它帮助数据库高效的进行数据的检索。它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。在数据之外,数据库系统还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。由此可知,索引是要消耗数据库空间的。而约束是一种逻辑概念。
一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
例如:一本字典,如何快速找到某个字,可以给字典加目录,对数据库来说,索引的作用即是给"数据"加目录。
设有 N 条随机记录,不用索引,平均查找 N/2 次,那么用了索引之后呢。如果是 btree(二叉树)索引,log2N,如果是hash(哈希)索引,时间复杂度是 1。
为何索引快?
明显查询索引表比直接查询数据表要快的多,首先,索引表是排序了,可以类似二分查找,非常有效的提高了查询的速度。其过程先是到事先排序好的索引表中检索查询,找到其主键后,就直接定位到记录所在位置,然后直接返回这条数据。
- 排序,tree结构,类似二分查找
- 索引表小
优点:
- 索引是数据库优化。
- 表的主键会默认自动创建索引。
- 每个字段都可以被索引。
- 大量降低数据库的IO磁盘读写成本,极大提高了检索速度。
- 索引事先对数据进行了排序,大大提高了查询效率。
缺点:
- 索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间。
- 索引表中的内容,在业务表中都有,数据是重复的,空间是“浪费的”。
- 虽然索引大大提高了查询的速度,但对数据的增、删、改的操作需要更新索引表信息,如果数据量非常巨大,更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件。
- 随着业务的不断变化,之前建立的索引可能不能满足查询需求,需要消耗我们的时间去更新索引。
创建索引
1)创建索引
创建索引,最左边的列最关键,主键会自动创建索引
create index 索引名 on 表名称 (column_name,[column_name...]);
create index loc_index on dept(loc);
2)修改表结构,添加普通索引
- 普通索引:一个索引只包括一个列,一个表可以有多个列
alter table dept add index loc_index(loc)
3)创建主键索引
- 主键索引:只有一个主键索引
//随表一起建索引,设定为主键后数据库会自动建立索引,innodb为聚簇索引
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
//单独建主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
//删除建主键索引
ALTER TABLE customer drop PRIMARY KEY;
//修改建主键索引
//必须先删除掉(drop)原索引,再新建(add)索引
4)创建唯一索引
- 唯一索引:索引列的值必须唯一,但允许有空值;主键会自动创建唯一索引
alter table dept add unique(loc) #创建唯一索引--索引列的值必须唯一
//随表一起创建:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
//单独建唯一索引:
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
5)创建复合索引
alter table dept add index fuhe_index(dname,loc);
//随表一起建索引
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
//单独建索引:
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
6)创建复合唯一索引
- 复合索引:一个索引同时包括多列
alter table dept add unique fuhe_index(dname,loc);
查看索引
show index from 表名;
删除索引
alter table 表名称 drop index 索引名;
alter table dept drop index fuhe_index;
MySQL 提供多种索引类型供选择:
- 全文索引:MySQL5.X 版本只有 MyISAM 存储引擎支持 FULLTEXT,并且只限于CHAR、VARCHAR 和 TEXT 类型的列上创建。
MySQL 的索引方法:
- HASH
- BTREE (MySQL 中多数索引都以 BTREE)
索引的使用原则:
- 不过度索引
- 索引条件列(where 后面最频繁的条件比较适宜索引)
- 索引散列值,过于集中的值不要索引,例如:给性别"男"、"女"加索引,意义不大
适合创建索引的情况
- 主键自动建立唯一索引;
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题, 组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不适合创建索引的情况
- 表记录太少
- 经常增删改的表或者字段
- Where 条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
索引扫描类型
- ALL 全表扫描,没有优化,最慢的方式。
- index 索引全扫描,其次慢的方式。
- range 索引范围扫描,常用语<,<=,>=,between等操作。
- ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中。
- eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询。
- const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询,system是const的特殊情况。
- null MySQL不访问任何表或索引,直接返回结果。
最左特性
explain
select * from dept where loc='二区' #使用了loc索引
explain
select * from dept where dname='研发部'#使用了dname索引
explain
select * from dept where dname='研发部' and loc='二区' #使用了dname索引
当我们创建一个联合索引(复合索引)的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则,也称为最左特性。
8. 表关联 association
概念
表table代表了生活中一个主体,如部门表dept,员工表emp。表关联则代表了表之间的关系,如:部门和员工,商品和商品分类,老师和学生,教室和学生。
同时,也要知道,表并不都有关系,它们形成自己的小圈子。如商品和商品详情一圈,部门和员工一圈,出圈就可能没关系了,如商品和员工无关,商品和学生无关。
下面我们讨论表的关系分为四种:
- 一对一 one to one QQ和QQ邮箱,员工和员工编号
- 一对多 one to many 最常见,部门和员工,用户和订单
- 多对一 many to one 一对多反过来,员工和部门,订单和用户
- 多对多 many to many 老师和学生,老师和课程
表设计
- 外键:由子表出发向主表拖动鼠标,到达主表后松手,PD会自动添加外键字段
- 讲师表和课程表:一对多,两张表。关联关系体现:子表存储主表的主键,称外键
- 课程表和学生表:多对多,三张表。关联关系体现:子表无法存储主表的多条关联信息,只能再创建一张表来存储其信息
- 中间表:存储两张表各自的主键,某一张表的主键无法标识记录的唯一性,两个一起才可以标识唯一,这种主键为多个字段的称为复合主键
创建数据库
创建表
表设计特点:
- 表都以s结束,标识复数
- 字段多以表的首字母作为开头,在多表联查时,方便标识出是哪个表的字段
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2020 */
/*==============================================================*/
drop table if exists courses;
drop table if exists scores;
drop table if exists students;
drop table if exists teachers;
/*==============================================================*/
/* Table: courses */
/*==============================================================*/
create table courses
(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);
/*==============================================================*/
/* Table: scores */
/*==============================================================*/
create table scores
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);
/*==============================================================*/
/* Table: students */
/*==============================================================*/
create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);
/*==============================================================*/
/* Table: teachers */
/*==============================================================*/
create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);
alter table courses add constraint FK_Reference_3 foreign key (tno)
references teachers (tno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_1 foreign key (sno)
references students (sno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_2 foreign key (cno)
references courses (cno) on delete restrict on update restrict;
插入测试数据
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
多表联查 join
笛卡尔积 Cartesian product
多表查询是指基于两个和两个以上的表的查询。在实际应用中,查询单个表可能不能满足你的需求,如显示员工表emp中不只显示deptno,还要显示部门名称,而部门名称dname在dept表中。
#把两个表的数据都拼接起来
SELECT * FROM dept,emp
上面这种查询两个表的方式称为:笛卡尔积(Cartesian product),又称直积。一般笛卡尔积没有实际的业务意义,但多表查询都是先生成笛卡尔积,再进行数据的筛选过滤。
这点很值得注意,实际开发中尽量少用多表联查,其根本原因就在这里,查询过程中,现在内存中构建一个大大的结果集,然后再进行数据的过滤。那这个构建过程,和所使用的内存资源,包括过滤时的判断,都是既耗费资源,又浪费时间。
这就是阿里规范中禁止3张表以上的联查的原因。
三种连接 join
- 内连接 inner join
- 左(外)连接 left join
- 右(外)连接 right join
inner join、left join、right join的区别?
- INNER JOIN两边都对应有记录的才展示,其他去掉
- LEFT JOIN左边表中的数据都出现,右边没有数据以NULL填充
- RIGHT JOIN右边表中的数据都出现,左边没有数据以NULL填充