MySQL基础
MySQL基础
数据库概述
什么是数据库
数据库是持久化的一种介质,可以理解成用来存储和管理数据的仓库
持久化:
把数据保存到可掉电式存储设备中以供之后使用. 持久化的大多数时候是将内存中的数据存储在数据库中,当然也可以存储在磁盘文件,XML数据文件中
为什么使用数据库
- 可将数据持久化到硬盘
- 可存储大量数据
- 方便检索
- 保证数据的一致性,完整性
- 安全,可共享
- 通过组合分析,可以产生新数据
常见数据库场景
- Oracle: 甲骨文
- DB2: IBM
- SQL Server: 微软
- MySQL: 甲骨文
数据库相关概念
DB:
数据库(Database): 存储数据的”仓库”.它保存了一系列有组织的数据
DBMS
数据库管理系统(Database Management System).数据库是通过DBMS创建和操作的容器
SQL
结构化查询语言(Structure Query Language): 专门用来与数据库通信的语言
数据库存储数据的特点
将数据放到表中,表再放到库中
一个数据库中可以有多个表,每一个表都有一个名字,用来标识自己
表名具有唯一性
表具有一些特性,这些特性定义了数据在表中如何存储. 类似与Java中的”属性”
表中的数据是按行存储的,每一行类似于java中的”对象”
MySQL数据库的介绍
MySQL产品的介绍
MySQL是一种开放源代码的关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购.目前 MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据(Facebook,Twitter,YouTube)。阿里提出“去IOE”,更多网站也开始选择MySQL。其本意是,在阿里巴巴的IT架构中,去掉IBM的小型机、Oracle数据库、EMC存储设备,代之以自己在开源软件基础上开发的系统。传统上,一个高端大气的数据中心,IBM小型机、Oracle数据库、EMC存储设备,可以说缺一不可。
MySQL产品的优点
成本低:开放源代码,一般可以免费试用
性能高:执行很快
简单:很容易安装和使用
安装与卸载
略
SQL语句
什么是SQL
SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中,例如:MySQL、Oracle、SQL Server等。SQL标准有:
l 1986年,ANSI X3.135-1986,ISO/IEC 9075:1986,SQL-86
l 1989年,ANSI X3.135-1989,ISO/IEC 9075:1989,SQL-89
l 1992年,ANSI X3.135-1992,ISO/IEC 9075:1992,SQL-92(SQL2)
l 1999年,ISO/IEC 9075:1999,SQL:1999(SQL3)
l 2003年,ISO/IEC 9075:2003,SQL:2003
l 2008年,ISO/IEC 9075:2008,SQL:2008
l 2011年,ISO/IEC 9075:2011,SQL:2011
l 2016年,ISO/IEC 9075:2016,SQL:2016
这些标准就与JDK的版本一样,在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。
虽然SQL可以用在所有关系型数据库中,但很多数据库还都有标准之后的一些语法,我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言,其它数据库都不支持!当然,Oracle或SQL Server都有自己的方言。
SQL语法要求
SQL语句可以单行或多行书写,以分号结尾;
可以用空格和缩进来来增强语句的可读性;
关键字不区别大小写,建议使用大写;
分类
DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);
DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;
DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
DQL
DQL就是数据查询语言,数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。
语法:
SELECT
selection_list /要查询的列名称/
FROM
table_list /要查询的表名称/
WHERE
condition /行条件/
GROUP BY
grouping_columns /对结果分组/
HAVING
condition /分组后的行条件/
ORDER BY
sorting_columns /对结果分组/
LIMIT
offset_start, row_count /结果限定/
基础查询
查询学生表中的所有列
查询指定列
条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
- =、!=、<>、<、<=、>、>=;
- AND、OR、NOT
- BETWEEN…AND、IN(set)、IS NULL
模糊查询
- like
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
#案例1:查询员工信息表中姓名第一个字符为e的员工信息
#案例2:查询员工信息表中姓名第二个字符为e,第五个字符为a的员工信息
1 | SELECT * FROM emp WHERE `ename` LIKE '_e__a'; |
#案例3:查询姓名中前面字符为ernst的员工信息
#案例4:查询姓名中第二个字符为_的员工信息
#方式一:直接通过\进行转义
#方式二:通过escape关键字指定转义字符
- between and
特点:
①用于筛选某个字段或表达式是否在指定的区间范围
②等价于 使用逻辑表达式的效果,只是语法上更加简单
③两个区间值不能调换顺序
- 两个区间值包含
>=左区间 and <=右区间
#案例1:查询年薪在100000到200000的员工年薪和姓名
#案例2:查询年薪不在100740到187200的员工年薪和姓名
- in
#案例1:查询工种编号是IT_PROT或是ST_CLERK或是AD_VP的员工信息
- is null
#案例1:查询哪个员工没有奖金
#案例2:查询哪个员工有奖金
字段控制查询
- 去除重复记录
去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT:
- 查看雇员的月薪与佣金之和(IFNULL函数)
因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不是数值类型,那么会出错。
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
- 给列名添加别名
方式一:
select 列名 as 别名 from 表名;
方式二:
select 列名 别名 from 表名;
排序 order by
1)按单个字段进行升序
案例1:按工资从低到高排序
案例2:按工资从高到低排序
1 | select * from emp order by sal desc; |
2).按多个字段排序
案例1:部门编号>50的按工资从高到低排序,如果一样,再按frist_name升序
1 | SELECT * FROM emp ORDER BY sal DESC, ename; |
3).按表达式排序
案例:将姓名中包含e字符的年薪和姓名显示出来,并且按年薪进行降序
4).按别名排序
案例:将姓名中包含e字符的年薪和姓名显示出来,并且按年薪进行降序
5).按函数排序
案例:显示所有有奖金的员工姓名、奖金、姓名的长度,按姓名的长度从低到高排序
常见函数
MySQL数据库提供了很多函数包括:
- 数学函数;
ABS(x) | 返回x的绝对值 |
---|---|
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回大于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND(x) | 返回0~1的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根 |
POW(x,y) | 返回x的y次方 |
- 字符串函数;
CONCAT(S1,S2,……,Sn) | 连接S1,S2,……,Sn为一个字符串 |
---|---|
CONCAT(s, S1,S2,……,Sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上s |
CHAR_LENGTH(s) | 返回字符串s的字符数 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
INSERT(str, index , len, instr) | 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(s,n) | 返回字符串s最左边的n个字符 |
RIGHT(s,n) | 返回字符串s最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(【BOTH 】s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1,s2 |
SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符 |
日期和时间函数;
(1)DATE_ADD(datetime,INTERVAL expr type)
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数 SELECT DATE_ADD(NOW(), INTERVAL ‘1_1’ YEAR_MONTH); #需要单引号 | |
---|---|
表达式类型 | YEAR_MONTH |
YEAR | DAY_HOUR |
MONTH | DAY_MINUTE |
DAY | DAY_SECOND |
HOUR | HOUR_MINUTE |
MINUTE | HOUR_SECOND |
SECOND | MINUTE_SECOND |
2)DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
---|---|---|---|
%M | 月名表示月份(January,….) | %m | 两位数字表示月份(01,02,03。。。) |
%b | 缩写的月名(Jan.,Feb.,….) | %c | 数字表示月份(1,2,3,…) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,…) | %d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数(1,2,3,4,5…..) | ||
%H | 两位数字表示小数,24小时制(01,02..) | %h和%I | 两位数字表示小时,12小时制(01,02..) |
%k | 数字形式的小时,24小时制(1,2,3) | %l | 数字形式表示小时,12小时制(1,2,3,4….) |
%i | 两位数字表示分钟(00,01,02) | %S和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun.,Mon.,Tues.,..) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday….) | ||
%j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 | ||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
条件判断函数;流程控制函数;
系统信息函数;
加密函数;
格式化函数;