MySQL-02

2022-07-18
作者我爱小小蝶
~12.63K 字

数据类型

示意图

1657711095882

数值型

类型	空间大小[字节]	范围（有符号）	范围（无符号）	用途
TINYINT	1	(-128，127)	(0，255)	小整数值
SMALLINT	2	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8			极大整数值
FLOAT	4			单精度浮点数值
DOUBLE	8			双精度浮点数值
DECIMAL	如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

字符串

类型	空间大小[字节]	备注
char	0-255	定长字符串(需要指定长度)
varchar	0-65535	变长字符串(需要指定长度)
tinytext	0-255	短文本字符串
text	0-65535	长文本数
mediumtext	0-16777215	中等长度文本数据
longtext	0-4294967295	极大文本数据
tinyblob	0-255	不超过255个字符的二进制数据
blob	0-65535	二进制形式的长文本数据
mediumblob	0-16777215	二进制形式的中等长度文本数据
longblob	0-4294967295	二进制形式的极大文本数据

日期类型

类型	空间大小[字节]	备注
time	3	时分秒
date	3	年月日
datetime	8	年月日时分秒
timestamp	4	时间戳类型，TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间
year	1	不推荐使用

其他

类型	备注
enum	枚举类型
set	集合类型

案例

CREATE TABLE user(
  id int NOT NULL AUTO_INCREMENT,
  season enum('春','夏','秋','冬') NULL,
  hobby set('java','c','html') NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

约束

案例

create table user(
   id int primary key auto_increment,
   age tinyint unsigned check(age <= 100) -- 此处限制了age 的值必须小于等于100
)

SQL

含义

结构化查询语言(Structured Query Language)简称SQL, 是操作和检索关系型数据库的标准语言

书写规则

不区分大小写，也就是说SELECT，select，Select，执行时效果是一样的。
可以单行来书写，也可以书写多行,通过Tab和缩进的使用可以提高程序的可读性。
关键字不可以缩写、分开以及跨行书写，如SELECT不可以写成SEL或SELE CT等形式。
关键字最好使用大写，其它语法元素（如列名、表名等）小写。

准备数据

drop TABLE emp;
drop TABLE dept;
drop TABLE salgrade;
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- 下面要特别注意，%d-%m-%Y 里的 d 和 m 一定要小写
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

DQL

含义

数据查询语言

语法

1	select [distinct] {* \| 列名 \| 表达式 [别名]} from 表名;

案例

-- 查询所有的列
SELECT * FROM dept;

-- 查询指定的列,请问为什么该效率高于上面的
SELECT deptno,dname,loc FROM dept;

-- 查询指定的列
SELECT deptno, loc FROM   dept;

算术运算

可以在SELECT语句中使用算术运算符，改变输出结果

1657782991010

优先级

乘除优先于加减
相同优先权的表达式按照从左至右的顺序依次计算
括弧可以提高优先权，并使表达式的描述更为清晰

案例

-- 查看每个员工的名字和年薪(包括奖金)
SELECT ename,12*sal+ifnull(comm,0) FROM emp;

-- 使用()号改变优先级
SELECT ename, sal, 12*(sal+100) FROM emp;

NULL

空值是指一种无效的、未赋值、未知的或不可用的值。空值不同于零或者空格

注意点

任何包含空值的算术表达式运算后的结果都为空值NULL
ifnull(列名,代替值)函数来处理空值

案例

-- 查看每个员工的名字和年薪(包括奖金)
SELECT ename,12*sal+ifnull(comm,0) FROM emp;

-- 查看没有奖金的员工
SELECT *FROM emp where comm is null;

列别名

用来重新命名列的显示标题,如果SELECT语句中包含计算列，通常使用列别名来重新定义列标题。

语法

列名列别名
列名 AS 列别名

DISTINCT

在SELECT字句中使用关键字DISTINCT可消除重复行

案例

-- 查看有员工的部门编号
SELECT DISTINCT deptno FROM   emp;

-- 查询员工表中一共有哪几种岗位类型
SELECT DISTINCT job from emp;

WHERE 字句

语法

1
2
3

SELECT [DISTINCT] {*| column | expression [alias], ...}
FROM   table
[WHERE condition(s)];

通常格式为：列名比较操作符要比较的值

案例

-- 查找职位是CLERK的员工信息,注意了字符串需要添加引号
SELECT ename,job,deptno FROM emp WHERE job='CLERK';

-- 查找入职时间晚于1985-01-01,注意了日期需要添加引号
SELECT ename,hiredate,deptno  FROM emp WHERE  hiredate > '1985-01-01'

特殊比较符

案例

-- 查询薪水在1000和1500之间的员工信息
SELECT	ename, sal FROM emp WHERE	sal BETWEEN 1000 AND 1500;

-- 查询员工的经理编号在7902,7566,7788里的员工信息
SELECT	empno,ename,sal,mgr FROM emp WHERE mgr IN (7902,7566,7788);

-- 查询姓名以S开头的员工
SELECT ename FROM emp WHERE ename LIKE 'S%';

-- %与_组合使用,此处代表查询员工名字至少一位的员工信息
SELECT	ename FROM 	emp WHERE	ename LIKE '_L%';

-- 查询岗位以MAN@_开头的员工信息
SELECT	ename,job FROM emp WHERE job LIKE 'MAN@_%' ESCAPE '@';

-- 查找没有经理的员工信息
SELECT ename,mgr FROM emp WHERE mgr IS NULL;

逻辑运算符

AND
OR
NOT
- NOT IN
- NOT BETWEEN 开始 AND 结束
- NOT LIKE
- IS NOT NULL

优先级

默认优先级

NOT > AND > OR
改变优先级

适当添加()可以改变优先级同时也可以提高代码可读性

案例一

-- 查询薪水大于等于1100 并且岗位是CLERK的员工
SELECT empno,ename,job,sal FROM emp WHERE sal>=1100 AND job='CLERK';

-- 查询薪水大于等于1100 或岗位是CLERK的员工
SELECT empno,ename,job,sal FROM emp WHERE sal>=1100 OR job='CLERK';

-- 查询岗位不在'CLERK','MANAGER','ANALYST'中的员工
SELECT ename, job FROM emp WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

案例二

-- 查找岗位是SALESMAN 或 工资大于1500并且岗位是PRESIDENT 的与员工信息
SELECT ename,job,sal FROM   emp  WHERE  job='SALESMAN' OR job='PRESIDENT' AND sal>1500;

-- 查找岗位是SALESMAN或PRESIDENT的员工并且他们的薪水要大于1500
SELECT ename,job,salFROM emp WHERE (job='SALESMAN' OR job='PRESIDENT') AND sal>1500;

order by

排序

规则

即按照数字大小顺序由小到大排列。
日期升序排列相对较早的日期在前，较晚的日期在后。
字符升序排列按照字母由小到大的顺序排列。即由A-Z排列,中文升序按照字典顺序排列。
空值在升序排列中排在最前面，在降序排列中排在最后。

语法

SELECT  [DISTINCT] { * | 列名 |表达式 [别名][,...]}
FROM 表名
[WHERE  条件]
[ORDER BY  {列名|表达式|列别名|列序号} [ASC|DESC],…];

可以按照列名、表达式、列别名、结果集的列序号排序
ORDER BY 子句必须写在SELECT语句的最后
ASC 为升序,DESC为降序

案例

-- 查找员工信息并且按照入职日期升序
SELECT ename,job,deptno,hiredate FROM emp ORDER BY hiredate;

-- 根据列别名排序【根据年薪升序,不考虑奖金】
SELECT empno,ename,sal*12 annsal FROM emp ORDER BY annsal;

-- 根据部门编号升序，薪水降序
SELECT ename,deptno,sal FROM emp ORDER BY deptno,sal DESC;

limit

分页

语法

1	.... limit 跳过多少条,最大取多少条

案例

-- 查询入职日期最早的前5名员工姓名，入职日期
select *from emp  order by sal desc limit 5,5;

-- 查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期
select *from emp order by hiredate desc LIMIT 0,5;

函数

mySQL提供了很多功能强大、方便易用的函数，在进行数据库管理以及数据的查询和操作时，帮助我们提高对数据库的管理效率。

1657787243356

分类

1657787277789

单行函数

语法

函数名[(参数1，参数2,…)]

其中的参数可以是以下之一：
变量
列名
表达式

特点

单行函数对单行操作
每行返回一个结果
有可能返回值与原参数数据类型不一致
单行函数可以写在SELECT、WHERE、ORDER BY子句中
有些函数没有参数，有些函数包括一个或多个参数
函数可以嵌套

数学函数

常见

abs()

求绝对值
pi()

返回pi
sqrt(x)

开平方根
mod(x)

求余数
ceil(x)\ceiling(x)

向上取整
floor(x)

向下取整
round()

四舍五入取整
round(x,y)

四舍五入保留小数点y位取整
truncate(x,y)

截断
rand()

返回随机数？
sign(x)

返回符号为,整数返回1负数返回-1,0就返回0
pow(x,y)\power(x,y)

返回x数据求y次方后的结果

案例

select abs(-1),SQRT(9),pi(),MOD(8,3) from dual;
 
select ceil(1.1),ceil(1.5),FLOOR(1.1),FLOOR(1.9) from dual;
 
select CEILING(1.1) from dual;
 
-- round 返回整数,  +0.5 取下一个整数
select ROUND(1.1),ROUND(1.5), ROUND(-1.1),ROUND(-1.5) from dual;
 
 
select round(1.1256,2) from dual;
 
select TRUNCATE(1.23456,4) from dual; 

-- RAND() /[0.0,1.0)
select rand() from dual;

-- SIGN(x) 返回符号为  整数 1 负数-1 0 0
select sign(0),sign(-10),sign(10) from dual;

-- 求多少次方
select pow(3,3),POWER(3,3) from dual;

字符串函数

常见

char_length(x)

求字符的个数
length(x)

返回字节个数
concat(str1,str2,…)

字符串拼接,如果有一个参数是null那么全部返回都是null
concat_ws(sepe0rator,str1,str2,…)

字符串拼接,拼接的分隔符使用seperator,如果被拼接的参数是null,则会忽略
insert(str,position,len,newStr)

在str的指定位置开始取len个字符并用newStr来代替
lower(x)

转成小写
upper(x)

转成大写
left(str,len)

返回字符串str的左边开始计算共返回len个字符
right(str,len)

返回字符串str的右边开始计算共返回len个字符
lpad(str,len,newStr)

对str的左边开始用newStr填充直到填充后的字符串长度为len为止
rpad(str,len,newStr)

对str的右边开始用newStr填充直到填充后的字符串长度为len为止
ltrim(x)

去除字符串x的左边空格
rtrim(x)

去除字符串x的右边空格
trim(x)

去除字符串x左右两边的隔空
trim( delStr from str)

去除字符串str中左右两边的delStr字符串
repeat(str,times)

返回一个新字符串,该字符串由于字符串str重复times次组成
space(n)

返回n个由于空格组成的字符串
replace(str,source,target)

返回str字符串中source子串被target字符串替换的结果
strcmp(x,y)

返回字符串x和y的比较结果,如果是相等返回0,x大于y返回1,否则返回-1
substring(str,startPosition[,len])

如果len没有写,返回的是字符串str中从startPosition开始截取到末尾字符串

如果len有指定,返回的是字符串str中从stratPosition开始截取len个字符串
reverse(x)

对字符串x逆序(倒序)
ELT(position,str1,str2,…)

返回的是参数中str1,str2,…参数中第position个位置的字符串

案例

select LENGTH('中A'),CHAR_LENGTH('中A') from dual;

select CONCAT('11','22','33'),CONCAT_WS('-','11','22','33') from dual;

select insert('abc',1,1,'12') from dual; 

select insert('abc',1,3,'12') from dual;

select LOWER('ABC'),UPPER('abc'),LEFT('abcdefg',1),RIGHT('abcdefg',2) from dual;

select LPAD('abc',5,'*') from dual;  

select RPAD('abc',5,'*') from dual;  

-- 15915785369
-- *******85369

select LPAD(RIGHT('15915785369',5),11,'*') from dual;

select ltrim('    abc    '),rtrim('    abc    '),trim('    abc    ') from dual;

-- ltrim rtrim TRIM([remstr FROM] str)
select trim('i' from 'i love java,is i') from dual;
 
select REPEAT('hello',2) from dual;
 
select SPACE(10) from dual;
	
select REPLACE('java,html','java','css') from dual;
	
select STRCMP('a','b') from dual; 
	
select SUBSTRING('abcdefg' FROM 2 FOR 2) from dual;
	
SELECT SUBSTRING('abcdefg',2)
	
select REVERSE('123') from dual;
	
select ELT(2,'11','22','33') FROM DUAL;

记录