mysql基础知识考试_MySQL基础知识

数据库概述

数据库指的就是数据‘仓库’，专门存储数据的。

数组，集合，文件....

数据库存放的数据便于管理

数据库存储数据的特点

将数据放到表中，表再放到库中

一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。

表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。

表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”

表中的数据是按行存储的，每一行类似于java中的“对象”。

常见的数据库

Mysql、Oracle、DB2、SQL Server

MySQL的安装

Mac 下安装brew install mysql

#启动服务

mysql.server start

#停止服务

mysql.server stop

#重启服务

mysql.server restart

sql语法规范

A.不区分大小写

B.语句结束使用;

C.注释可以使用单行或者多行注释

单行注释：

-- 注释的内容

注释的内容

多行注释：

/*

注释的内容

*/

D.语句可以分行编写，但是关键字不能拆开编写

SQL语句的分类

定义

分类

DML Data Manipulation Language

数据操作语言

DDL Data Definition Language

数据定义语言

DCL Data Control Language

数据控制语言

DQL Data Query Language

数据查询语言

DQL语言

基础查询

语法：

select 查询列表 from 表名;

特点：

1.查询的结果为一个虚拟表，并没有真实存在

2.查询列表可以是常量、字段、表达式、函数以及上述的组合

条件查询

语法：

select 查询列表 from 表名

where 条件;

条件的形式：

关系表达式

通过关系运算符组成的表达式：关系表达式

sql中的关系运算符：> < >= <= = != <>

逻辑表达式

通过逻辑运算符组成的表达式：逻辑表达式

sql中的逻辑运算符：and、or 、not 【&& || ！】

模糊查询

like、between and、in、is null

排序查询

语法：

select 查询列表

from 表名

where 条件

order by 排序列表 asc|desc;

特点：

asc代表升序，desc代表降序。默认是升序

查询列表可以是 单个字段、多个字段、函数、表达式、别名或上述的组合

常见函数-单行函数

函数：类似于“方法”，都是为了完成特定的功能，将内部实现细节隐藏，对外暴露函数名

关心的是：函数名和函数功能

函数调用的语法：

select 函数名(参数列表);

字符函数

length 获取字节个数

replace 替换所有

lower 变小写

upper 变大写

substr 截取子串

lpad 左填充

rpad 右填充

trim 去前后空格或指定字符

instr 获取子串第一次出现的索引

concat 拼接字符串

数学函数

ceil 向上取整

floor 向下取整

rand 随机数

abs 绝对值

mod 取余

round 四舍五入

truncate 截断

日期函数

now 获取当前日期+时间

year/month/day 获取年、月、日

date_format 格式日期，返回格式好的字符串

DATE_FORMAT(NOW(),'%Y年%m月%d日 %H小时%i分钟%s秒')

str_to_date 将字符串解析成日期

curtime 获取当前时间，不包含日期

curdate 获取当前日期，不包含时间

注意：mysql默认使用的时区不是东八区

查询mysql的时区：show variables like '%time_zone%';

可以修改mysql.ini配置mysql默认时区：

设置mysql时区

default-time-zone='+08:00'

修改完配置文件需要重启mysql服务才会生效

流程控制函数

if函数

case结构

示例1：查询员工的姓名和旧工资、显示的新工资

如果部门编号>100,则工资显示为3倍

如果部门编号>80,则工资显示为2倍

如果部门编号>50,则工资显示为原工资

否则，显示为1/2倍

SELECT last_name,salary 旧工资,

CASE

WHEN department_id>100 THEN salary*3

WHEN department_id>80 THEN salary*2

WHEN department_id>50 THEN salary

ELSE salary/2

END 新工资

FROM employees;

示例2：案例：显示姓名、工种编号、旧工资、新工资，如果工种为IT_PROG，则工资显示成5倍

SELECT job_id,last_name,salary,

CASE job_id

WHEN 'IT_PROG' THEN salary*5

WHEN 'ST_CLERK' THEN salary*3

WHEN 'AD_VP' THEN salary

ELSE salary/2

END 新工资

FROM employees;

分组函数

调用语法：select 函数名(实参列表);

分组函数和单行函数的区别：

单行函数：将一个数据进行处理，返回一个值

分组函数：将虚拟表看做一个组，处理一组数据，返回一个值

常见的分组函数：

字段

含义

sum(字段)

求该字段的所有值的和

avg(字段)

求该字段的平均值

max(字段)

求最大值

min(字段)

求最小值

count(字段)

计算该字段中非空的值的个数

特点

分组函数可以搭配筛选条件使用

分组函数的参数可以为字段或表达式

分组函数都忽略null值

分组函数支持的类型

max、min、count支持任意类型

sum、avg仅仅支持数值型

count的使用

count(*)或count(常量值) ：统计结果集中的行数

count(distinct 字段)：实现去重后的统计

和分组函数一同查询的字段不能是任意字段，可以是group by后面的字段！

分组查询

语法：

select 分组函数,分组的字段 -- ⑤

from 表名 -- ①

where 分组前的筛选条件-- ②

group by 分组的字段,... -- ③

having 分组后的筛选条件 -- ④

order by 排序列表 --⑥

特点：

筛选可以分为分组前筛选和分组后筛选

筛选

针对的数据

使用的关键字

位置

分组前筛选

原始表

where

group by前面

分组后筛选

分组后的结果集

having

group by后面

分组函数做条件放在having后面！

连接查询

查询的字段或条件来自于多张表，这个时候则需要使用多表连接查询，简称连接查询

笛卡尔乘积的现象：

表1行数m行，表2行数n行，结果为m*n行

如何产生的？

没有添加连接条件

如何解决？

添加连接条件

如何添加连接条件？

sql92语法：

内连接：

等值连接

非等值连接

自连接

外连接：【支持的不太好】mysql中不支持！

左外连接

右外连接

全外连接

sql99语法：

内连接：

等值连接

非等值连接

自连接

外连接：

左外连接

右外连接

全外连接【mysql不支持】

子查询

概念

一个查询语句里面又嵌套了另一个完整的查询语句，被嵌套在里面的查询语句，称为 子查询或内查询

外面的查询语句，称为主查询或外查询

分类

按位置不同：

select后面

from后面

where或having后面 ★

按查询结果的行数不同：

单行子查询

多行子查询

exists后面

特点

放在后面的子查询的特点：

子查询放在小括号内

子查询优先于主查询执行，主查询一般会用到子查询的结果

一般放在条件的右侧

子查询一般会搭配着操作符使用

单行子查询，搭配单行操作符使用：> < >= <= = <>

多行子查询，搭配多行操作符使用：in、not in、any、all

分页查询

语法

select 查询列表

from 表

inner join 表 on 连接条件

where 筛选条件

group by 分组

having 分组后筛选

order by 排序】

limit 起始条目索引,条目数;

特点：

limit子句放在最后

起始条目索引可以省略，默认从第一条开始 ，索引为0

DDL语言

库和表的管理

创建数据库语法：

create database【 if not exists】 库名;

删除数据库语法：

drop database 【if exists】 库名;

创建表语法：

create table 【if not exists】 表名(

字段名字段类型【字段约束】,

字段名字段类型【字段约束】,

....

);

删除表语法：

drop table 【if exists】表名;

修改表语法：alter table 表名 add/drop/modify/change column 列名 字段类型；

修改表名：ALTER TABLE 旧表名 RENAME TO 新表名;

常用mysql数据类型：

选择数据类型原则：类型越简单越好，能保存数值的类型的所占空间越小越好

整型TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT

浮点型FLOAT(n,m)/DOUBLE(n,m)/DECIMAL(n,m)

n:代表整数部位+小数部位的最大长度

m:代表小数部位的最大长度

FLOAT(5,3) :-99.999~99.999

字符型CHAR(n)/VARCHAR(n)/TEXT

TEXT:用于保存较长的文本，比如备注文字、协议文字

字段

n的意思

特点

性能

CHAR(n)

能保存的字符的最大个数,可选，默认是1

固定长度的字符

较高

VARCHAR(n)

能保存的字符的最大个数，必写

可变长度的字符较低

日期型DATETIME/DATE/TIME/TIMESTAMP时间戳

TATE:只有日期，TIME：只有时间

示例

所占字节

保存日期范围

受时区和服务器版本的影响

DATETIME：日期+时间

8个字节

1900-1-1~9999-12-31

不受

TIMESTAMP:日期+时间

4个字节

1970-1-1~2038-12-31

受

常见约束

说明：用于额外限定表中的字段值，为了保证数据的完整性(准确性和可靠性)

常见的六大约束：

NOT NULL非空：该字段的值为必填项

UNIQUE 唯一：该字段的值不能重复，可以为空，一个表中可以有多个

PRIMARY KEY 主键：该字段的值不能重复，不能为空，一个表中只能有一个，一般来讲，一个表最好有一个主键

DEFAULT 默认：该字段的值如果不手动插入，也有默认值

CHECK 检查【mysql不支持】：用于检测该字段的值是否满足某个条件

FOREIGN KEY 外键：用于限定两个表的关系，从表的某列值要来自于主表的某列值

分类

表级约束：只有外键、主键、唯一

列级约束：只有非空、默认、主键、唯一

DDL语言

insert 插入

插入单行：

insert into 表名(列名1，列名2,...) values(值1,值2,...);

批量插入多行：

insert into 表名(列名1，列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);

特点：

①整型的值，不用引号

字符或日期型的值，使用引号

②插入的值必须满足对应的字段类型和约束

③字段可以调换顺序，不一定是表中字段的存储顺序，但要注意字段和值必须一一对应！

④字段和值的个数、顺序、类型必须一致

如果可以为null或default，则需要使用null或default填充

⑤如果要插入的为该表的所有列，则直接省略列名，默认所有列！

update 更新

语法：

update 表名 set 字段名 = 新值,字段名 = 新值 【where 条件】;

delete 删除

语法：delete from 表名 where 条件

truncate 删除

语法：

truncate table 表名;

delete与truncate的区别(面试题)

delete可以加WHERE条件

TRUNCATE不可以加WHERE条件

truncate效率更高

delete 可以返回受影响的行数

TRUNCATE没有返回受影响 的行数

如果使用DELETE删除带自增长列的表，删除后再插入，则自增长列从断点处开始插入

如果使用TRUNCATE删除带自增长列的表，删除后再插入，则自增长列从1开始

delete语句可以回滚,

TRUNCATE 语句不能回滚