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
YEARDAY_HOUR
MONTHDAY_MINUTE
DAYDAY_SECOND
HOURHOUR_MINUTE
MINUTEHOUR_SECOND
SECONDMINUTE_SECOND

​ 2)DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)

%Y4位数字表示年份%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为周中第一天
%T24小时制%r12小时制
%pAM或PM%%表示%
  • 条件判断函数;流程控制函数;

  • 系统信息函数;

  • 加密函数;

  • 格式化函数;