网站 seo 设置,恢复正常百度,东莞做网站排名优化推广,学计算机的出路一 数据库基础知识
先谈发音
MySQL如何发音#xff1f;在国内MySQL发音有很多种#xff0c;Oracle官方文档说 他们念作 My sequal[si:kwəl]。 数据库基本概念 数据 数据#xff08;Data#xff09;是指对客观事物进行描述并可以鉴别的符号#xff0c;这 些符号是可识别…一 数据库基础知识
先谈发音
MySQL如何发音在国内MySQL发音有很多种Oracle官方文档说 他们念作 My sequal[si:kwəl]。 数据库基本概念 数据 数据Data是指对客观事物进行描述并可以鉴别的符号这 些符号是可识别的、抽象的。它不仅指狭义上的数字而是有多 种表现形式字母、文字、文本、图形、音频、视频等。 数据库 数据库是数据管理的有效技术是由一批数据构成的有序集合 这些数据被存放在结构化的数据表里。数据表之间相互关联反 映客观事物间的本质联系。 数据库管理系统 数据库应用程序Database Application SystemDBAS是在 数据库管理系统基础上使用数据库管理系统的语法开发的直 接面对最终用户的应用程序。 数据库管理员 数据库管理员Database AdministratorDBA是指对数据库 管理系统进行操作的人员其主要负责数据库的运营和维护。
数据库分类 关系型数据库
关系型数据库最典型的数据结构是表由二维表及其之间的联系所组成的一个数据组织。可以采用结构化查询语言SQL对数据库进行操作。
优点
易于维护都是使用表结构格式一致使用方便SQL语言通用可用于复杂查询复杂操作支持SQL可用于一个表以及多个表之间非常复杂的查询。
缺点
读写性能比较差尤其是海量数据的高效率读写固定的表结构灵活度稍欠高并发读写需求传统关系型数据库来说硬盘I/O是一个很大的瓶颈。
非关系型数据库
非关系型数据库也称之为NoSQL数据库是一种数据结构化存储方法的集合可以是文档或者键值对等。
优点
格式灵活存储数据的格式可以是key,value形式、文档形式、图片形式等等文档形式、图片形 式等等使用灵活应用场景广泛而关系型数据库则只支持基础类型。速度快nosql可以使用硬盘或者随机存储器作为载体而关系型数据库只能使用硬盘高扩展性成本低nosql数据库部署简单基本都是开源软件。
缺点
不提供sql支持学习和使用成本较高无事务处理数据结构相对复杂复杂查询方面稍欠。
二 MySQL基础知识
MySQL简介
MySQL 是一个关系型数据库管理系统 由瑞典 MySQL AB 公司开 发 目前属于 Oracle 公司。MySQL 是一种关系型数据库管理系统关系型数据库将数据保存在不同的表中而不是将所有数据放 在一个大仓库内这样就增加了速度并提高了灵活性。
MySQL特点
MySQL 是开源的。
MySQL 支持大型系统的数据库。可以处理拥有上千万条记录的大型 数据库。 MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以运行于多个系统上并且支持多种语言。这些编程语言 包括 C 、C、 Python 、Java 、Perl 、PHP 等。
MySQL 存储数据量较大32 位系统表文件最大可支持 4GB 64 位系统支持最大的 表文件为 8TB。
MySQL 是可以定制的采用了 GPL 协议你可以修改源码来开发 自己的 MySQL 系 统。
MySQL分类 MySQL分为社区版 社区版是完全开源免费的社区版也支持多种数据类型和标准的 SQL查询语言能够对数据进行各种查询、增加、删除、修改等 操作所以一般情况下社区版就可以满足开发需求了。 企业版 企业版是收费的。
即使在开发中需要用到一些付费的附加功能 价格相对于昂贵的 Oracle、DB2等也是有很大优势的。对数据库可靠性要求比较高的企业可以选择企业版。
三 下载MySQL
MySQL官网地址mysql.com 四 MySQL的安装与卸载
MySQL安装 MSQL卸载 五 连接MySQL 通过MySQL自带客户端工具 可通过MySQL自带的客户端工具链接MySQL。 通过客户端工具可对MySQL进行操作。 通过MySQL客户端工具的快捷方式连接MySQL Navicat工具的使用 六 SQL语言 SQL语言简介
结构化查询语言(Structured Query Language)简称 SQL(发音 sequal[si:kwəl])是一种数据库查询和程序设计语言用于存取数据以及查询、更新和管理关系数据库系统。
SQL 能做什么
SQL 面向数据库执行查询SQL 可在数据库中插入新的记录SQL 可更新数据库中的数据SQL 可从数据库删除记录SQL 可创建新数据库SQL 可在数据库中创建新表SQL 可在数据库中创建存储过程SQL 可在数据库中创建视图SQL 可以设置表、存储过程和视图的权限
SQL 标准
SQL 是 1986 年 10 月由美国国家标准局ANSI通过的数据库语 言美国标准接着国际标准化组织ISO颁布了 SQL 正式国际 标准。1989 年 4 月ISO 提出了具有完整性特征的 SQL89 标准 1992 年 11 月又公布了 SQL92 标准在此标准中把数据库分为 21 三个级别基本集、标准集和完全集。在 1999 年推出 99 版标准。 最新版本为 SQL2016 版。比较有代表性的几个版本SQL86、 SQL92、SQL99。
SQL语言分类 数据查询语言DQLData Query Language其语句也称为 “数据检索语句”用以从表中获得数据确定数据怎样在应用程序给出。关键字 SELECT 是 DQL也是所有 SQL用得最多的动词。 SELECT FROM WHERE ORDER BY HAVING 数据操作语言DMLData Manipulation Language其语句包括动词 INSERTUPDATE 和 DELETE。它们分别用于添加 修改和删除表中的行。 INSERT添加数据 UPDATE更新数据 DELETE删除数据 数据定义语言DDLData Definition Language定义数据库对象语言其语句包括动词 CREATE 和 DROP 等。 CREATE创建数据库对象 ALTER修改数据库对象 DROP删除数据库对象 数据控制语言DCLData Control Language它的语句通过 GRANT 或 REVOKE 获得许可确定用户对数据库对象的访问。 GRANT授予用户某种权限 REVOKE回收授予的某种权限 事务控制语言TCL Transaction Control Language它的语句能确保被 DML 语句影响的表的所有行及时得以更新。 COMMIT提交事务 ROLLBACK回滚事务 SAVEPOINT设置回滚点 注意 数据操纵语言DMLinsert、update、delete针对表中的数据 而数据定义语言DDLcreate、alter、drop针对数据库对象比如数据库database、表table、索引index、视图view、 存储过程procedure、触发器trigger SQL语言语法
SQL语句不区分大小写关键字建议大写。SQL语句可以单行或多行书写以分号结尾。
七 创建与删除数据库
创建数据库 使用DDL语句创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码;示例 创建一个test 的数据库并查看该数据库以及该数据库的编 码。
创建数据库
create database test default character set utf8;查看数据库
show databases;查看数据库编码
select
schema_name,default_character_set_name
from information_schema.schemata
where schema_name test;使用Navicat创建数据库
示例 创建一个test2 的数据库。 删除数据库 使用DDL语言删除数据库
DROP DATABASE 数据库名称;示例 删除 test 数据库
drop database test;使用Navicat删除数据库
示例 删除test2数据库 选择数据库
在创建表时需要先选择数据库。
USE 数据库名;示例 创建一个名称为 bjsxt 的数据库编码为 utf8。
create database bjsxt default character set utf8;选择该数据库。
USE bjsxt;八 MySQL中的数据类型 整数类型
MySQL数据类型含义有符号tinyint(m)1个字节 范围(-128~127)smallint(m)2个字节 范围(-32768~32767)mediumint(m)3个字节 范围(-8388608~8388607)int(m)4个字节 范围(-2147483648~2147483647)bigint(m)8个字节 范围(±9.22*10的18次方)
数值类型中的长度 m 是指显示长度并不表示存储长度只有字段指定 zerofill 时有用
例如 int(3) 如果实际值是 2 如果列指定了 zerofill 查询结果就是 002 左边用 0 来 填充
浮点类型
MySQL数据类型含义float(m,d)单精度浮点型 8位精度(4字节) m总个数d小数位double(m,d)双精度浮点型 16位精度(8字节) m总个数d小数位
字符类型
MySQL数据类型含义char(n)固定长度最多255个字符tinytext可变长度最多255个字符varchar(n)可变长度最多65535个字符text可变长度最多65535个字符mediumtext可变长度最多2的24次方-1个字符longtext可变长度最多2的32次方-1个字符 char和varchar char长度固定 即每条数据占用等长字节空间适合用在身份证号码、手机号码等定长。varchar可变长度可以设置最大长度适合用在长度可变的属性。text不设置长度 当不知道属性的最大长度时适合用text。 按照查询速度 char最快 varchar次之text最慢。 字符串型使用建议 经常变化的字段用varchar知道固定长度的用char尽量用varchar超过255字符的只能用varchar或者text能用varchar的地方不用text 日期类型
MySQL数据类型含义date日期 YYYY-MM-DDtime时间 HH:MM:SSdatetime日期时间 YYYY-MM-DD HH:MM:SStimestamp时间戳YYYYMMDD HHMMSS
二进制数据(BLOB)
BLOB和TEXT存储方式不同TEXT以文本方式存储英文存储区分大小写而Blob是以二进制方式 存储不分大小写。BLOB存储的数据只能整体读出。TEXT可以指定字符集BLOB不用指定字符集。
九 创建表与删除表
创建表 使用DDL语句创建表
CREATE TABLE 表名(列名 类型,列名 类型......);示例 创建一个 employees 表包含雇员 ID 雇员名字雇员薪水。
create table employees
(employee_id int,employee_name varchar(10),salary float(8,2));查看已创建的表。
show tables;使用Navicat创建表
示例 创建employees2表。 删除表 使用DDL语句删除表
DROP TABLE 表名;示例 删除 employees 表。
drop table employees;使用Navicat删除表
示例 删除employees2表 十 修改表
修改表名 使用DDL语句修改表
ALTER TABLE 旧表名 RENAME 新表名;示例一 创建一个 employees 表包含雇员 ID 雇员名字雇员薪水。
create table employees(employee_id int,employee_name varchar(10),salaryfloat(8,2)
);示例二 将 employees 表名修改为 emp。
alter table employees rename emp;使用Navicat修改表名
选择表按F2。 修改列名 使用DDL语句修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;示例 将 emp 表中的 employee_name 修改为 name。
alter table emp change column employee_name name varchar(20);使用Navicat修改列名 修改列类型 使用DDL语句修改列类型
ALTER TABLE 表名 MODIFY 列名 新类型;示例 将 emp 表中的 name 的长度指定为 40。
alter table emp modify name varchar(40);使用Navicat修改列类型 添加新列 使用DDL语句添加新列
ALTER TABLE 表名 ADD COLUMN 新列名 类型;示例 在 emp 表中添加佣金列列名为 commission_pct。
alter table emp add column commission_pct float(4,2);使用Navicat添加新列 删除指定列 使用DDL语句删除指定的列
ALTER TABLE 表名 DROP COLUMN 列名;示例 删除 emp 表中的 commission_pct。
alter table emp drop column commission_pct;使用Navicat删除指定的列 十一 MySQL中的约束 约束概述
数据库约束是对表中的数据进行进一步的限制保证数据的正确性、有效性和完整性。 主键约束(Primary Key) PK 主键约束是使用最频繁的约束。在设计数据表时一般情况下都会要求表中设置一个主键。 主键是表的一个特殊字段该字段能唯一标识该表中的每条信息。例如学生信息表中的学号是唯一的。 外键约束(Foreign Key) FK 外键约束经常和主键约束一起使用用来确保数据的一致性。 唯一性约束(Unique) 唯一约束与主键约束有一个相似的地方就是它们都能够确保列的唯一性。与主键约束不同的是唯一约束在一个表中可以有多个并且设置唯一约束的列是允许有空值的。 非空约束(Not Null) 非空约束用来约束表中的字段不能为空。 检查约束(Check) 检查约束也叫用户自定义约束是用来检查数据表中字段值是否有效的一个手段但目前 MySQL 数据库不支持检查约束。
添加主键约束(Primary Key)
单一主键 使用一个列作为主键列当该列的值有重复时则违反唯一约束。联合主键 使用多个列作为主键列当多个列的值都相同时则违反唯一约束。
修改表添加主键约束
使用DDL语句添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)示例 将 emp 表中的 employee_id 修改为主键。
alter table emp add primary key(employee_id);主键自增长
MySQL 中的自动增长类型要求
一个表中只能有一个列为自动增长。自动增长的列的类型必须是整数类型。自动增长只能添加到具备主键约束与唯一性约束的列上。删除主键约束或唯一性约束如果该列拥有自动增长能力则需要先去掉自动增长然后在删除约束。
alter table 表名 modify 主键 类型 auto_increment;示例 将 emp 表中的 employee_id 主键修改为自增。
alter table emp modify employee_id int auto_increment;使用Navicat添加主键约束 删除主键
使用DDL语句删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;注意 删除主键时如果主键列具备自动增长能力需要先去掉自动增长然后在删除主键。 示例 删除emp表中的 employee_id 主键约束。
去掉自动增长
alter table emp modify employee_id int;删除主键
alter table emp drop primary key;使用Navicat删除主键 添加外键约束(Foreign Key) 修改表添加外键约束
使用DDL语句添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY( 列 名 ) REFERENCES 参照的表名 (参照的列名);示例一 创建 departments 表包含 department_id 、 department_name location_id。
create table departments(department_id int,department_name varchar(30),location_id int
);示例二 修改departments表向department_id列添加主键约束与自动递增。
alter table departments add primary key(department_id);
alter table departments modify department_id int auto_increment;示例三 修改 emp 表添加 dept_id 列。 alter table emp add column dept_id int;示例四 向 emp 表中的 dept_id 列添加外键约束。
alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);使用Navicat添加外键约束 删除外键约束
使用DDL语句删除外键约束。
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;示例 删除 dept_id 的外键约束。
alter table emp drop foreign key emp_fk;使用Navicat删除外键约束 添加唯一性约束(Unique)
修改表添加唯一性约束
使用DDL语句添加唯一性约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);示例 向 emp 表中的 name 添加唯一约束。
alter table emp add constraint emp_uk unique(name);使用Navicat添加唯一性约束 删除唯一性约束
使用DDL语句删除唯一性约束。
ALTER TABLE 表名 DROP KEY 约束名;示例 删除 name 的唯一约束。
alter table emp drop key emp_uk;使用Navicat删除唯一性约束。 添加非空约束(Not Null)
修改表添加非空约束
使用DDL语句添加非空约束。
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;示例 向 emp 表中的 salary 添加非空约束。
alter table emp modify salary float(8,2) not NULL;使用Navicat添加非空约束。 删除非空约束
使用DDL语句删除非空约束。
ALTER TABLE 表名 MODIFY 列名 类型 NULL;示例 删除emp表中salary 的非空约束。
alter table emp modify salary float(8,2) NULL;使用Navicat删除非空约束。 创建表时添加约束
查询表中的约束信息
SHOW KEYS FROM 表名;示例 创建 depts 表包含 department_id 该列为主键且自动增长 department_name 列不允许重复location_id 列不允含有空值。
create table depts(department_id int primary key auto_increment,department_name varchar(30) unique,location_id int not null
);十二 MySQL中DML操作 添加数据(INSERT)
选择插入 INSERT INTO 表名(列名 1 列名 2 列名3.....) VALUES(值 1 值 2 值 3......);
示例 向 departments 表中添加一条数据部门名称为 market 工 作地点 ID 为 1。
insert into departments(department_name,location_id) values(market, 1);完全插入
INSERT INTO 表名 VALUES(值 1 值 2 值3......);注意 如果主键是自动增长需要使用 default 或者 null 或者 0 占位。 示例一 向 departments 表中添加一条数据部门名称为 development 工作地点 ID 为 2 。使用 default 占位。
insert into departments values(default,development,2);示例二 向 departments 表中添加一条数据部门名称为human 工作地 点 ID 为 3 。使用 null 占 位。
insert into departments values(null,human,3);示例三 向 departments 表中添加一条数据部门名称为 teaching 工作 地点 ID 为 4 。使用 0 占 位。
insert into departments values(0,teaching,4);默认值处理(DEFAULT)
在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插 数据时并未指定该列的值那么 MySQL 会将默认值添加到该列 中。
创建表时指定列的默认值
CREATE TABLE 表名(列名 类型 default 默认值,......
);示例 创建 emp3 表该表包含 emp_id 主键且自动增长包含 name 包含 address 该列默认值为”未知”。
create table emp3(emp_id int primary key auto_increment,name varchar(10),address varchar(50) default Unknown
);修改表添加新列并指定默认值
ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;示例 修改 emp3 表添加job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;插入数据时的默认值处理
如果在插入数据时并未指定该列的值那么MySQL 会将默认值添加到该列中。如果是完全项插入需要使用 default 来占位。
示例 向 emp3 表中添加数据要求 address 列与job_id 列使用默认值作为该列的值。
insert into emp3(name) values(admin);
insert into emp3 values(default,oldlu,default,default);更新数据(UPDATE)
UPDATE 表名 SET 列名值列名值 WHERE 条件;注意 更新语句中一定要给定更新条件否则表中的所有数据都会被更新。 示例 更新 emp3 表中的 id 为 1 的数据添加 address 为 BeiJing。
update emp3 set address BeiJing where emp_id 1;删除数据(DELETE)
DELETE删除数据
DELETE FROM 表名 WHERE 条件;注意 在DELETE语句中如果没有给定删除条件则会删除表中的所有数据。 示例 删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id 1;TRUNCATE清空表
TRUNCATE TABLE 表名;示例 删除 emp3 表中的所有数据。 truncate table emp3;清空表时DELETE与 TRUNCATE 区别
truncate 是整体删除(速度较快) delete 是逐条删除(速度较慢)truncate 不写服务器 logdelete 写服务器 log也就是 truncate 效率比 delete 高的原因truncate 是会重置自增值相当于自增列会被置为初始值又重新从 1 开始记录而不是接着原来的值。而 delete 删除以后 自增值仍然会继续累加。
十三 MySQL查询数据
SELECT基本查询
SELECT语句的功能 SELECT 语句从数据库中返回信息。使用一个 SELECT 语句可以做下面的事
列选择能够使用 SELECT 语句的列选择功能选择表中的列这些列是想要用查询返回的。当查询时能够返回列中的数据。行选择能够使用 SELECT 语句的行选择功能选择表中的行这些行是想要用查询返回的。能够使用不同的标准限制看见的行。连接能够使用 SELECT 语句的连接功能来集合数据这些数据被存储在不同的表中在它们之间可以创建连接查询出我们所关心的数据。
SELECT基本语法 基本 SELECT 语句
在最简单的形式中SELECT 语句必须包含下面的内容
一个 SELECT 子句指定被显示的列一个 FROM 子句指定表该表包含 SELECT 子句中的字段列表
在语法中
语句含义SELECT是一个或多个字段的列表*选择所有的列DISTINCT禁止重复column | expression选择指定的字段或表达式alias给所选择的列不同的标题FROM table指定包含列的表
添加测试数据
将data.sql文件通过Navicat导入到MySQL中itbz数据库中。该文件包含了该文章中所使用的案例表。 查询中的列选择
选择所有列 用跟在 SELECT 关键字后面的星号 (*)你能够显示表中数据的所有列。
示例 查询 departments 表中的所有数据。
select * from departments;选择指定列 能够用 SELECT 语句来显示表的指定列指定列名之间用逗号分隔。
示例 查询 departments 表中所有部门名称。
select department_name from departments;查询中的算术表达式 需要修改数据显示方式如执行计算或者作假定推测这些都可能用到算 术表达式。一个算术表达式可以包含列名、固定的数字值和算术运算符。
使用算术运算符 示例 查询雇员的年薪并显示他们的雇员ID名字。
select employees_id,last_name, 12*salary from employees;运算符的优先级 如果算术表达式包含有一个以上的运算乘法和除法先计算。如果 在一个表达式中的运算符优先级相同计算从左到右进行。可以用圆括号强制其中的表达式先计算。
示例一 计算 employees 表中的员工全年薪水加 100 以后的薪水是多少 并显示他们的员工ID与名字。
select employees_id,last_name, 12*salary100 from employees;示例二 计算 employees 表中的员工薪水加 100 以后的全年薪水是多少 并显示他们的员工ID与名字。
select employees_id,last_name, 12*(salary100) from employees;MySQL中定义空值 如果一行中的某个列缺少数据值该值被置为 null 或者说包含一个空。
空是一个难以获得的、未分配的、未知的或不适用的值。空和 0 或者空格不相同。 0 是一个数字而空格是一个字符。
算术表达式中的空值 示例 计算年薪包含佣金。
select 12*salary*commission_pct from employees;MySQL中的别名
使用列别名 SELECT 列名 AS 列别名 FROM 表名 WHERE 条件;示例 查询 employees 表将雇员 last_name 列定义别名为 name。
select last_name as name from employees;
select last_name name from employees;使用表别名
SELECT 表别名.列名 FROM 表名 as 表别名 WHERE 条件;示例 查询 employees 表为表定义别名为emp将雇员 last_name 列定 义别名为 name。
select emp.last_name name from employees emp;MySQL中去除重复 除去相同的行 SELECT DISTINCT 列名 FROM 表名;示例 查询 employees 表显示唯一的部门 ID。
select distinct department_id from employees;查询中的行选择 用 WHERE 子句限制从查询返回的行。一个 WHERE 子句包含一个 必须满足的条件WHERE 子句紧跟着 FROM 子句。如果条件是 true返回满足条件的行。
在语法中
WHERE 限制查询满足条件的行
condition 由列名、表达式、常数和比较操作组成
SELECT * | 投影列 FROM 表名 WHERE 选择条件;示例 查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。
select department_name,location_id from departments where department_id 4;MySQL中的比较条件 符号 ! 也能够表示不等于条件。
示例一 查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪 水。
select last_name,salary from employees where salary 3000;示例二 查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。
select last_name,salary from employees where salary5000;其他比较条件 使用BETWEEN条件 可以用 BETWEEN 范围条件显示基于一个值范围的行。指定的范围包含一个下限和一个上限。
示例 查询 employees 表薪水在 3000-8000 之间的雇员ID、名字与薪水。
select employee_id,last_name,salary from employees where salary between 3000 and 8000;使用IN条件 示例 查询 employees 表找出薪水是 5000,6000,8000 的雇员ID、名字 与薪水。
select employee_id,last_name,salary from employees where salary in(5000,6000,8000);使用LIKE条件 示例 查询 employees 中雇员名字第二个字母是 e 的雇员名字。
select last_name from employees where last_name like _e%;使用NULL条件 NULL 条件包括 IS NULL 条件和 IS NOT NULL 条件。
IS NULL 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此你不能用 因为 null 不能等于或不等于任何值
示例一 找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。
select employee_id,last_name,commission_pct from employees where commission_pct is null;示例二 找出 employees 表中那些有佣金的雇员ID、名字与佣金。
select employee_id,last_name,commission_pct from employees where commission_pct is not
null;逻辑条件 逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果或者逆转一个单个条件的结果。当所有条件的结果为真时返回行
SQL 的三个逻辑运算符是 AND OR NOT
可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。
示例一 查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员 名字与薪水。
select last_name,salary from employees where
salary 8000 and last_name like %e%;示例二 查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员 名字与薪水。
select last_name,salary from employees where
salary 8000 or last_name like %e%;示例三 查询 employees 表中雇员名字中不包含 u 的雇员的名字。
select last_name from employees where
last_name not like %u%;优先规则 在图片的例子中有两个条件
第一个条件是 job_id 是 AD_PRES 并且薪水高于 15,000。 第二个条件是 job_id 是 SA_REP。 在图片中的例子有两个条件 第一个条件是 job_id 是 AD_PRES 或者 SA_REP 。 第二个条件是薪水高于$15,000
使用 ORDER BY 排序 在一个不明确的查询结果中排序返回的行。ORDER BY 子句用于排序。如果使用了 ORDER BY 子句它必须位于 SQL 语句的最后。
SELECT 语句的执行顺序如下
FROM 子句WHERE 子句SELECT 子句ORDER BY 子句
示例一 查询 employees 表中的所有雇员显示他们的ID、名字与薪水并按薪水升序排序。
select employee_id,last_name,salary from
employees order by salary;
select employee_id,last_name,salary from
employees order by salary asc;示例二 查询 employees 表中的所有雇员显示他们的ID与名字并按雇员名字降序排序。
select employee_id,last_name from employees
order by last_name desc;使用别名排序 示例 显示雇员ID名字。计算雇员的年薪年薪列别名为annsal并对 该列进行升序排序
select employee_id,last_name ,12*salary annsal from employees order by annsal;多列排序 示例 以升叙排序显示 DEPARTMENT_ID 列同时以降序排序显示 SALARY 列。
select department_id,salary from employees
order by department_id asc ,salary desc;十四 SQL函数
函数介绍 函数是 SQL 的一个非常强有力的特性函数能够用于下面的目的
执行数据计算修改单个数据项操纵输出进行行分组格式化显示的日期和数字转换列数据类型
SQL 函数有输入参数并且总有一个返回值。
函数分类 单行函数
单行函数仅对单个行进行运算并且每行返回一个结果。
常见的函数类型
字符数字日期转换
多行函数
多行函数能够操纵成组的行每个行组给出一个结果这些函数也被称为组函数。
单行函数 单行函数分类 字符函数 大小写处理函数
函数描述实例LOWER(s)|LCASE(s)将字符串 s 转换为小写将字符串 OLDLU转换为小写 SELECT LOWER(“OLDLU”); – oldluUPPER(s)|UCASE(s)将字符串s转换为大写将字符串 oldlu转换为大写 SELECT UPPER(“oldlu”); – OLDLU
示例 显示雇员 Davies 的雇员号、姓名和部门号将姓名转换为大写。
select employee_id,UPPER(last_name),department_id
from employees where last_name davies;字符处理函数
函数描述实例LENGTH(s)返回字符串 s 的长度返回字符串oldlu的字符数 SELECT LENGTH(“oldlu”); --5;CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合 并为一个字符串合并多个字符串 SELECT CONCAT(sxt , teacher , “oldlu”); --sxt teacher oldlu;LPAD(s1,len,s2)在字符串 s1 的开始处填充字 符串 s2使字符串长度达到 len将字符串 x 填充到 oldlu字符串的开始处 SELECT LPAD(‘oldlu’,8,‘x’); – xxxoldluLTRIM(s)去掉字符串 s 开始处的空格去掉字符串 oldlu开始处的空格 SELECT LTRIM( oldlu) ;-- oldluREPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中 的字符串 s1将字符串 oldlu 中的字符 o 替换为字符 O SELECT REPLACE(‘oldlu’,‘o’,‘O’); --OldluREVERSE(s)将字符串s的顺序反过来将字符串 abc 的顺序反过来 SELECT REVERSE(‘abc’); – cbaRPAD(s1,len,s2)在字符串 s1 的结尾处添加字 符串 s2使字符串的长度达 到 len将字符串 xx填充到 oldlu字符串的结尾处 SELECT RPAD(‘oldlu’,8,‘x’); – oldluxxxRTRIM(s)去掉字符串 s 结尾处的空格去掉字符串 oldlu 的末尾空格 SELECT RTRIM(oldlu ); – oldluSUBSTR(s, start, length)从字符串 s 的 start 位置截取 长度为 length 的子字符串从字符串 OLDLU中的第 2 个位置截取 3个 字符 SELECT SUBSTR(“OLDLU”, 2, 3); – LDLSUBSTRING(s, start, length)从字符串 s 的 start 位置截取 长度为 length 的子字符串从字符串 OLDLU中的第 2 个位置截取 3个 字符 SELECT SUBSTRING(“OLDLU”, 2, 3); – LDLTRIM(s)去掉字符串 s 开始和结尾处的 空格去掉字符串 oldlu 的首尾空格 SELECT TRIM(’ oldlu );–oldlu
示例 显示所有工作岗位名称从第 4 个字符位置开始包含字符串 REP的 雇员的ID信息将雇员的姓和名连接显示在一起还显示雇员名的长度以及名字中字母 a 的位置。
SELECT employee_id, CONCAT(last_namefirst_name) NAME,job_id, LENGTH(last_name),INSTR(last_name,a) Contains a? FROM employees WHERE
SUBSTR(job_id, 4) REP;MySQL中INSTR函数用于在指定的字符串中查找特定的子字符串并返回其在原字符串中首次出现的位置。其基本语法如下 INSTR(string, substring)数字函数
函数名描述实例ABS(x)返回 x 的绝对值返回 -1 的绝对值 SELECT ABS(-1) – 返回1ACOS(x)求 x 的反余弦值(参数是弧度)SELECT ACOS(0.25);ASIN(x)求反正弦值(参数是弧度)SELECT ASIN(0.25);ATAN(x)求反正切值(参数是弧度)SELECT ATAN(2.5);ATAN2(n, m)求反正切值(参数是弧度)SELECT ATAN2(-0.8, 2);AVG(expression)返回一个表达式的平均值 expression 是一个字段返回 Products 表中Price 字段的平均值 SELECT AVG(Price) AS AveragePrice FROM Products;CEIL(x)返回大于或等于 x 的最小整数SELECT CEIL(1.5) – 返回2CEILING(x)返回大于或等于 x 的最小整数SELECT CEILING(1.5); – 返回2COS(x)求余弦值(参数是弧度)SELECT COS(2);COT(x)求余切值(参数是弧度)SELECT COT(6);COUNT(expression)返回查询的记录总数 expression 参数是一个字段或 者 * 号返回 Products 表中 products 字段总共有多少条记录 SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;DEGREES(x)将弧度转换为角度SELECT DEGREES(3.1415926535898) – 180n DIV m整除n 为被除数m 为除数计算 10 除于 5 SELECT 10 DIV 5; – 2EXP(x)返回 e 的 x 次方计算 e 的三次方 SELECT EXP(3) – 20.085536923188FLOOR(x)返回小于或等于 x 的最大整数小于或等于 1.5 的整数 SELECT FLOOR(1.5) – 返回1GREATEST(expr1, expr2, expr3, …)返回列表中的最大值返回以下数字列表中的最大值 SELECT GREATEST(3, 12, 34, 8, 25); – 34 返回以下字符串列表中的最大值 SELECT GREATEST(“Google”, “Runoob”, “Apple”); – RunoobLEAST(expr1, expr2, expr3, …)返回列表中的最小值返回以下数字列表中的最小值 SELECT LEAST(3, 12, 34, 8, 25); – 3 返回以下字符串列表中的最小值 SELECT LEAST(“Google”, “Runoob”, “Apple”); – AppleLN返回数字的自然对数以 e 为 底。返回 2 的自然对数 SELECT LN(2); – 0.6931471805599453LOG(x) 或 LOG(base, x)返回自然对数(以 e 为底的对 数)如果带有 base 参数则 base 为指定带底数。SELECT LOG(20.085536923188) – 3 SELECT LOG(2, 4); – 2LOG10(x)返回以 10 为底的对数SELECT LOG10(100) – 2LOG2(x)返回以 2 为底的对数返回以 2 为底 6 的对数 SELECT LOG2(6); – 2.584962500721156MAX(expression)返回字段 expression 中的最 大值返回数据表 Products 中字段 Price 的最大值 SELECT MAX(Price) AS LargestPrice FROM Products;MIN(expression)返回字段 expression 中的最 小值返回数据表 Products 中字段 Price 的最小值 SELECT MIN(Price) AS MinPrice FROM Products;MOD(x,y)返回 x 除以 y 以后的余数5 除于 2 的余数 SELECT MOD(5,2) – 1PI()返回圆周率(3.141593SELECT PI() --3.141593POW(x,y)返回 x 的 y 次方2 的 3 次方 SELECT POW(2,3) – 8POWER(x,y)返回 x 的 y 次方2 的 3 次方 SELECT POWER(2,3) – 8RADIANS(x)将角度转换为弧度180 度转换为弧度 SELECT RADIANS(180) – 3.1415926535898RAND()返回 0 到 1 的随机数SELECT RAND() --0.93099315644334ROUND(x)返回离 x 最近的整数SELECT ROUND(1.23456) --1SIGN(x)返回 x 的符号x 是负数、 0、正数分别返回 -1、0 和 1SELECT SIGN(-10) – (-1)SIN(x)求正弦值(参数是弧度)SELECT SIN(RADIANS(30)) – 0.5SQRT(x)返回x的平方根25 的平方根 SELECT SQRT(25) – 5SUM(expression)返回指定字段的总和计算 OrderDetails 表中字段 Quantity 的总和 SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;TAN(x)求正切值(参数是弧度)SELECT TAN(1.75); – -5.52037992250933TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值与 ROUND 最大的区 别是不会进行四舍五入SELECT TRUNCATE(1.23456,3) – 1.234
ROUND(column|expression, n) 函数
ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个 参数是 0 或者缺少值被四舍五入为整数。如果第二个参数是 2值 被四舍五入为两位小数。如果第二个参数是–2值被四舍五入到小数点左边两位。
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1);TRUNCATE(column|expression,n) 函数
TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少值被截断为整数。如果第二个参数是 2值被截断为两 位小数。如果第二个参数是–2值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。
SELECT TRUNCATE(45.923,2);使用MOD(m,n) 函数
MOD
函数找出m 除以n的余数。
示例 所有job_id是SA_REP的雇员的名字薪水以及薪水被5000除后的余数。
SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id SA_REP;日期函数
在MySQL中允许直接使用字符串表示日期但是要求字符串的日期 格式必须为‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;
函数名描述实例CURDATE()返回当前日期SELECT CURDATE(); - 2018-09-19CURTIME()返回当前时间SELECT CURTIME(); - 19:59:02CURRENT_DATE()返回当前日期SELECT CURRENT_DATE(); - 2018-09-19CURRENT_TIME()返回当前时间SELECT CURRENT_TIME(); - 19:59:02DATE()从日期或日期时间表达式中提取日期值SELECT DATE(“2017-06-15”); - 2017-06-15DATEDIFF(d1,d2)计算日期 d1-d2 之间相隔的天数SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) - -32DAY(d)返回日期值 d 的日期部分SELECT DAY(“2017-06-15”); - 15DAYNAME(d)返回日期 d 是星期几如 Monday,TuesdaySELECT DAYNAME(‘2011-11-11 11:11:11’) - FridayDAYOFMONTH(d)计算日期 d 是本月的第几天SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) - 11DAYOFWEEK(d)日期 d 今天是星期几1 星期日2 星期 一以此类推SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) -6DAYOFYEAR(d)计算日期 d 是本年的第几天SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) - 315HOUR(t)返回 t 中的小时值SELECT HOUR(‘1:2:3’) - 1LAST_DAY(d)返回给给定日期的那一月份的最后一天SELECT LAST_DAY(“2017-06-20”); - 2017-06-30MONTHNAME(d)返回日期当中的月份名称如 NovemberSELECT MONTHNAME(‘2011-11-11 11:11:11’) - NovemberMONTH(d)返回日期d中的月份值1 到 12SELECT MONTH(‘2011-11-11 11:11:11’) -11NOW()返回当前日期和时间SELECT NOW() - 2018-09-19 20:57:43SECOND(t)返回 t 中的秒钟值SELECT SECOND(‘1:2:3’) - 3SYSDATE()返回当前日期和时间SELECT SYSDATE() - 2018-09-19 20:57:43TIMEDIFF(time1, time2)计算时间差值SELECT TIMEDIFF(“13:10:11”, “13:10:10”); - 00:00:01TO_DAYS(d)计算日期 d 距离 0000 年 1 月 1 日的天数SELECT TO_DAYS(‘0001-01-01 01:01:01’) - 366WEEK(d)计算日期 d 是本年的第几个星期范围是 0 到 53SELECT WEEK(‘2011-11-11 11:11:11’) - 45WEEKDAY(d)日期 d 是星期几0 表示星期一1 表示星 期二SELECT WEEKDAY(“2017-06-15”); - 3WEEKOFYEAR(d)计算日期 d 是本年的第几个星期范围是 0 到 53SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’) - 45YEAR(d)返回年份SELECT YEAR(“2017-06-15”); - 2017
示例一
向 employees 表中添加一条数据雇员ID300名字kevin emailkevinsxt.cn 入职时间2049-5-1 8:30:30工作部 门‘IT_PROG’。
insert into
employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(300,kevin,kevinsxt.cn,2049-5-1 8:30:30,IT_PROG);示例二
显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作 时间以周计算用当前日期 (SYSDATE) 减去雇员的受顾日期再除以 7。
SELECT last_name, (SYSDATE()-hire_date)/7 AS WEEKS
FROM employees WHERE department_id 90;转换函数 隐式数据类型转换
隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。 如可以将标准格式的字串日期自动转换为日期类型。
MySQL字符串日期格式为‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;
显示数据类型转换
显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。 如
DATE_FORMAT(date,format) 将日期转换成字符串;STR_TO_DATE(str,format) 将字符串转换成日期; 示例一
向 employees 表中添加一条数据雇员ID400名字oldlu emailoldlusxt.cn 入职时间2049 年 5 月 5 日工作部 门‘IT_PROG’。
insert into
employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(400,oldlu,oldlusxt.cn,STR_TO_DATE(2049 年 5 月 5 日,%Y 年%m 月%d日),IT_PROG);示例二
查询 employees 表中雇员名字为 King 的雇员的入职日期要求显 示格式为 yyyy 年 MM 月 dd 日。
select DATE_FORMAT(hire_date,%Y 年%m 月%d 日) from employees where last_name King;通用函数
函数名描述实例IF(expr,v1,v2)如果表达式 expr 成立返回结果 v1否 则返回结果 v2。SELECT IF(1 0,‘正 确’,‘错误’) -正确IFNULL(v1,v2)如果 v1 的值不为 NULL则返回 v1否则返 回 v2。SELECT IFNULL(null,‘Hello Word’) -Hello WordISNULL(expression)判断表达式是否为 NULLSELECT ISNULL(NULL); -1NULLIF(expr1, expr2)比较两个参数是否相同如果参数 expr1 与 expr2 相等 返回 NULL否则返回 expr1SELECT NULLIF(25, 25); -COALESCE(expr1, expr2, …, expr_n)返回参数中的第一个非空表达式从左向 右SELECT COALESCE(NULL, NULL, NULL, ‘bjsxt.com’, NULL, ‘google.com’); - bjsxt.comCASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result ENDCASE 表示函数开始END 表示函数结束。 如果 condition1 成立则返回 result1, 如果 condition2 成立则返回 result2当全部不成立则返回 result而当有一个成立之后 后面的就不执行了。SELECT CASE ‘oldlu’ WHEN ‘oldlu’ THEN ‘OLDLU’ WHEN ‘admin’ THEN ‘ADMIN’ ELSE ‘kevin’ END;
示例一
查询部门编号是50或者80的员工信息包含他们的名字、薪水、佣 金。在income列中如果有佣金则显示‘SALCOMM’无佣金则显 示’SAL’。
SELECT last_name, salary, commission_pct, if(ISNULL(commission_pct),SAL,SALCOMM) income
FROM employees
WHERE department_id IN (50, 80);示例二
计算雇员的年报酬你需要用 12 乘以月薪再加上它的佣金 (等于年薪乘以佣金百分比)。
SELECT last_name, salary,
IFNULL(commission_pct, 0), (salary*12) (salary*12*IFNULL(commission_pct, 0)) AN_SAL
FROM employees;示例三
查询员工表显示他们的名字、名字的长度该列名为expr1姓 氏、姓氏的长度该列名为expr2。在result列中如果名字与姓氏的 长度相同则显示空如果不相同则显示名字长度。
SELECT first_name, LENGTH(first_name) expr1,last_name, LENGTH(last_name) expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)) result
FROM employees;示例四
查询员工表显示他们的名字如果 COMMISSION_PCT 值是非 空显示它。如果COMMISSION_PCT 值是空则显示 SALARY 。 如果 COMMISSION_PCT 和SALARY 值都是空那么显示 10。在结 果中对佣金列升序排序。
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;示例五
查询员工表如果 JOB_ID 是 IT_PROG薪水增加 10%如果 JOB_ID 是 ST_CLERK薪水增加 15%如果 JOB_ID 是 SA_REP 薪水增加 20%。对于所有其他的工作角色不增加薪水。
SELECT last_name, job_id, salary,CASE job_id WHEN IT_PROG THEN 1.10*salaryWHEN ST_CLERK THEN 1.15*salaryWHEN SA_REP THEN 1.20*salaryELSE salary END REVISED_SALARY
FROM employees;
