MySQL(一) 

SQL语句三大主线

DDL:(Data Definition Languages,数据定义语言),主要用来创建,删除,修改库和表的结构.

DML:(Data Manipulation Language,数据操作语言),主要用于添加,删除,更新,查询数据库的记录.

DCL:(Data Control Language,数据控制语言),主要用于定义数据库,表,字段,用户的访问权限和安全级别.

SELECT是SQL语言的基础,最重要.
所以很多时候会把查询单独称为DQL(数据查询语言).

1.运算符

1.1比较运算符

用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真返回1,结果为假返回0,其他情况返回NULL

  • 符号类的
!=或<>	不等于
=		等于
<=>		 安全等于
-- 字符串存在隐式转换,如果转换数值不成功,则看做0
-- 如果两边都是字符串,则按照ANSI的比较规则进行比较

-- 安全等于和等于的区别
安全等于   两边都为NULL时,返回值为1,只有一边为NULL时,返回值为0
等于		其中一边或两边都为NULL时,返回值为NULL

>		大于 
>=		大于等于 
<		小于 
<=		小于等于
  • 关键字类的
为空运算符		 IS NULL/ISNULL
不为空运算符		IS NOT NULL
最小值运算符		LEAST
最大值运算符		GREATEST
在某个区间之内	    BETWEEN...AND... 	-- 包含两个点
属于运算符		 IN((....)) 		  -- 多选1
不属于运算符		NOT IN((...))	     -- 多选1
模糊匹配运算符		LIKE 占位符		 -- _匹配单个字符,%匹配多个字符

1.2逻辑运算符

逻辑与		AND或&&	-- 两边都为真才是真
逻辑或		OR或||	-- 两边有一个为真就为真
逻辑非		NOT或!	-- 取反
逻辑异或    XOR		-- 满足左边但不满足右边,满足右边但不满足左边

1.3位运算符

&	按位与
|	按位或
^	按位异或
~	按位取反
>>	按位右移
<<	按位左移

1.4运算符优先级

image-20221029171142092

越往下优先级越高

2.DQL

2.1基本查询

-- 查询多个字段
SELECT 字段1,字段2,字段3.... FROM 表名;
-- 例
SELECT name,age FROM student;

-- 查询全部字段
SELECT * FROM 表名;
SELECT * FROM employees;

-- 设置别名
SELECT 字段1 [as别名1],..... FROM 表名;
-- 例
SELECT age AS "newAge" FROM student;
-- AS可以省略,双引号可以不写(别名出现空格时要写)

-- 去重
SELECT DISTINCT 字段列表 FROM 表名;
-- 例
select distinct age from employees;

空值

  • null不等于0,'',null'
  • 空值参与运算,结果一定为null

着重号

当表名(字段名)和SQL定义的关键字重复了,就要使用着重号引起来

2.2条件查询

-- where关键字
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-- 例,查询学生表年龄等于18的学生
select name,age from student where age=18;

2.3排序查询

-- ORDER BY关键字
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
-- 排序方式
asc		升序(默认)
desc	降序
-- 例
select employee_id,last_name,salary from employees order by salary desc;

二级排序
select employee_id,last_name,salary from employees order by salary desc,
employee_id;
-- 当salary相等时,按照employee_id升序排序

2.4分页查询

-- LIMIT关键字
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
-- 例
select * from employees limit 0,20;
  • 起始索引
    • 从第几条数据开始(0开始)
  • 查询记录数
    • 每页展示多少条数据
公式:(页-1)*数量,数量;
-- 例,第二页的信息,每页15条
(2-1)*15,15
结果就是15,15

-- offset关键字
select * from employees limit 2,20;
两条语句相等
select * from employees limit 20 offset 2;

2.5多表查询

等值连接vs非等值连接

-- 等值连接示例
-- 带等号的连接都称为等值连接
select employee_id,department_name 
from employees,departments 
where employees.department_id=departments.department_id;

-- 非等值连接示例
select last_name,salary,grade_level
from employees,job_grades
where employees.salary
between job_grades.lowest_sal and job_grades.highest_sal;

自连接vs非自连接

-- 自连接
-- 当前表与自身的连接查询,自连接必须使用表别名
SELECT 字段列表 FORM 表A 别名A JOIN 表A 别名B ON 条件;
-- 自连接示例,查询员工id,员工姓名极其管理者的id和姓名
select e1.employee_id,e1.last_name,e2.employee_id,e2.last_name
from employees e1,employees e2
where e1.manager_id=e2.employee_id;

-- 自连接以外的连接都成为非自连接

内连接vs外连接

  • 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行.
    • 查询两个表的交集数据(排除null的值)
      • 隐式内连接SELECT 字段列表 FORM 表1,表2.... WHERE 条件;
  • 外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(右)表中不满足条件的行,这种连接称为左(右)外连接,没有匹配的行时,结果表中相应的列为空(NULL).
    • 左外连接:连接条件中的左边的表称为主表,右边的表称为从表.
      • 查询A表所有数据和B表交集部分数据
    • 右外连接:连接条件中的右边的表称为主表,左的表称为从表.
      • 查询B表所有数据和A表交集部分数据
    • 满(全)外连接:连接条件中的左边的表称为主表,右边的表称为从表.
      • 查询A表和B表的所有数据
  • 联合查询
    • 把多次查询的结果合并起来,形成一个新的结果集
    • 可以解决MySQL不支持全外连接的查询
-- 隐式内连接(92语法)
SELECT 字段列表 FORM 表1,表2.... WHERE 条件; 
-- 例
select employee_id,department_name
from employees,departments
where employees.department_id=departments.department_id
-- 显示内连接(99语法)
SELECT 字段列表 FORM 表1 [INNER] JOIN 表2 ON 条件; -- INNER可以省略
-- 例
select last_name,department_name
from employees join departments
on employees.department_id = departments.department_id
-- 两个以上的表时在后面继续join 表名 on 条件即可

-- 左外连接
SELECT 字段列表 FORM 表1 LEFT [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees left join departments
on employees.department_id = departments.department_id
-- 右外连接
SELECT 字段列表 FORM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees right join departments
on employees.department_id = departments.department_id
-- 满(全)外连接
SELECT 字段列表 FORM 表1 FULL [OUTER] JOIN 表2 ON 条件;-- OUTER可以省略
-- 例
select last_name,department_name
from employees full join departments
on employees.department_id = departments.department_id

左(右)外连接只在显示内连接的情况上添加一个left或者right即可
满(全)外连接在MySQL数据库的语法中不支持full这个关键字,但MySQL有一个联合查询

-- 联合查询
SELECT 字段列表 FORM 表A.... 
UNION [ALL] 
SELECT 字段列表 FORM 表B...;
-- UNION不加ALL(效率较低)
去重
-- UNION加ALL(效率较高,推荐使用)
全部信息的结果集,包括重复的

-- 例
select employee_id,department_name
from employees left join departments
on employees.department_id=departments.department_id
union all
select employee_id,department_name
from employees right join departments
on employees.department_id=departments.department_id
where employees.department_id is null ;

2.6分组查询

  • 主要用来和聚合函数联合使用

  • select出现的非聚合函数的字段在group by中也一定要出现

  • SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组过滤条件];
-- GROUP BY例,查询各个部门的工种的平均工资
select department_id,job_id,avg(salary) 
from employees 
group by department_id,job_id;
-- 结果如下

  • image-20221030103639808

  • -- HAVING例,查询各个部门的最高工资,并且只展示最高比10000高的
select department_id,max(salary)
from employees group by department_id
having max(salary)>10000;

2.7子查询

  • 单行子查询

    • 作为条件值使用= != < > <= >=等条件判断

    • SELECT 字段列表 FORM 表 WHERE 字段名 = (子查询);

  • 多行子查询

    • 作为条件值使用IN,ANY,SOME,ALL 等关键字条件判断

    • SELECT 字段列表 FORM 表 WHERE 字段名 in (子查询);

  • 表子查询

    • 作为虚拟表(常用操作符IN)

    • SELECT 字段列表 FORM (子查询) WHERE 条件;

2.8执行顺序

FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY>LIMIT

3.函数

把经常使用的代码封装起来,需要的时候再调用,这样即提高了代码效率,又提高了可维护性.在SQL中也可以使用函数对检索出来的数据进行操作,使用这些函数,可以极大地提高用户对数据库的管理效率.

3.1单行函数

  • 单行函数可以嵌套使用

数值函数

基本函数
  • image-20221029222321806
-- 简单举例几个
select ABS(-123),PI(),RAND() from dual;
三角函数
  • image-20221029223317628

  • image-20221029223357147

指数对数
  • image-20221029224312704
进制转换
  • image-20221029224219083

字符串函数

image-20221029224610714

image-20221029225234192

image-20221029225709805

字符串中,索引是从1开始的

日期函数

获取日期和时间
  • image-20221030083632589
时间戳的转换
  • image-20221030083316868
获取月份,星期,天数等
  • image-20221030083836387
日期的操作函数
EXTRACT(type FROM date)
-- 返回指定日期中的特定部分,type指定返回的值
  • image-20221030084152391
  • image-20221030084303901
时间和秒钟转换的函数
  • image-20221030084439210
计算日期和时间的函数

  • image-20221030084522235

    type的取值

  • image-20221030084701264

  • image-20221030084752644

  • image-20221030085017635

日期的格式化与解析

  • image-20221030085518484

  • fmt参数常用的格式符

  • image-20221030085651417

  • image-20221030085828270

  • date_type和format_type参数取值

  • image-20221030090136233

  • image-20221030090303018

流程控制函数

流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择.MySQL中的流程处理函数主要包括IF(),IFNULL()和case()函数.

  • -- IF()函数
IF(value,t,f); -- 如果value为true,返回t,否则返回f
-- 例
SELECT IF(1>0,'对','错); -- 结果为对

  • -- IFNULL()函数
-- IFNULL(value1,value2); --如果value1不为空,返回value1,否则返回value2
-- 例
SELECT IFNULL(null,'Hello World'); -- 结果为Hello World

  • -- case()函数1
CASE WHEN [val1] THEN [res1] ... ELSE [default] END;
-- 如果val1为true,返回res1,...否则返回default默认值,类似于if...else if...else
-- 例
SELECT CASE 
WHEN age>18 THEN '成年' 		-- 如果age大于18就为成年
WHEN age<18 THEN '未成年' 	    -- 不大于18就为未成年
ELSE  '错误年龄' END; 		  -- 其他情况为错误年龄
-- ELSE可以不写,不写默认值为NULL

  • -- case()函数2
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END;
-- 如果expr的值等于val1,返回res1,...否则返回default默认值,类似于switch语句
-- 例
SELECT age=12 CASE 
WHEN age>18 THEN '成年' 		
WHEN age<18 THEN '未成年' 	   
ELSE  '错误年龄' END;	-- 结果为未成年
-- ELSE可以不写,不写默认值为NULL

3.2聚合函数

对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值

  • SELECT 聚合函数(字段列表) FROM 表名;
-- NULL值不参加运算

  • -- 常见的聚合函数
统计		count
最大值		max
最小值		min
平均值		avg
求和	     sum
-- 例 平均值和总值
select avg(salary),sum(salary) from employees; -- 只使用于数值类型
-- 例 最大值最小值
select max(salary),min(salary) from employees; -- 可以适用于字符串

  • -- count的作用:计算指定字段在查询结构中出现的个数
select count(salary)) from employees;

4.DDL

4.1数据库操作

  • -- 查询所有数据库
SHOW DATABASES;
-- 查询当前数据库
SELECT DATABASE();
-- 创建数据库
CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
-- 使用数据库
USE 数据库名;

  • -- 例:创建数据库
create database if not exists students;
-- 例:使用数据库
use students;
-- 例:删除数据库
drop database if exists students;

4.2数据表操作

增

  • -- 创建表
CREATE TABLE [IF NOT EXISTS] 表名(
    字段1 字段1类型[COMMEN字段1注释],
    2,3,4....
)[COMMENT 表注释];
 -- 最后一个字段没有逗号
 
 -- 添加字段
 ALTER TABLE 表名 ADD 字段名 字段类型[COMMENT 注释] [约束];

  • -- 例:创建表
create table if not exists student(
    id int,
    name varchar(15),
    age int
);
-- 例:添加字段
alter table student add gender varchar(1);

删

  • -- 删除字段
alter TABLE 表名 DROP 字段名;
-- 删除表
DROP TABLE[IF EXISTS] 表名;
-- 删除指定表并且重新创建该表(清除数据)
TRUNCATE TABLE 表名;

  • -- 例:删除字段
alter table students drop employee_id;
-- 例:删除表
drop table student;
-- 例:清除数据
truncate table student;

改

  • -- 修改数据类型
alter TABLE 表名 MODIFY 字段名 新数据类型;
-- 将字段移动
alter table 表名 modify 要移动的字段 after 移动在哪;
-- 修改字段名和字段类型
ATLER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型 [COMMENT 注释] [约束];
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

  • -- 例:修改数据类型
alter table students modify first_name varchar(19);
-- 例:修改字段名和字段类型
alter table students change first_name one_name varchar(20);
-- 例:修改表名
alter table students rename to student;

查

  • -- 查询当前数据库所有表
SHOW TABLES;
-- 查询表结构
DESC 表名;
-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

  • -- 例:查看表结构
desc student;
-- 例:查看指定表的建表语句
show create table student;

复制

  • -- 复制表数据,例
create table studnet 
[AS] 
select id,name,age from employee;
-- 此时student表就会有id,name,age的数据(数据类型和约束等于employee表一致)
-- 相当于把查询的结果复制到新的表当中

  • -- 复制表结构(不包括数据,只要结构),例
create table studnet 
[AS]
select id,name,age from employee where 这里给一个不可能的条件,例如1=0;

5.DML

增

  • 单个添加

  • -- 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2....) VALUES(值1,值2....);
-- 例
insert into detp01(name) values ('李四');

-- 给全部指定添加数据
INSERT INTO 表名 VALUES(值1,值2......);
-- 例
insert into detp01 values (1,'张三');

  • 批量添加

  • -- 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2....) VALUES(值1,值2....),(值1,值2....),(值1,值2....);
-- 例
insert into detp01(id,name) values (2,'李四'),(3,'王五');

-- 给全部指定添加数据
INSERT INTO 表名 VALUES(值1,值2......),(值1,值2....),(值1,值2....);
-- 例
insert into detp01 values (2,'李四'),(3,'王五');

  • -- 将查询的结果插入到表中
insert into 表名(字段名1,字段名2,字段名3..) 查询语句
-- 字段名要一一对应

删

  • DELETE FROM 表名 [WHERE 条件];
-- 例
delete from detp01 where id=4;

改

  • UPDATE 表名 
SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
-- 例
update detp01 set id=4 where id is null;

6.TCL

COMMIT:提交数据,一旦执行commit,则数据就被永久的保存在数据库中了,意味着无法回滚数据.

ROLLBACK:回滚数据,一旦执行rollback,则可以实现数据回滚,回滚到最近的一次commit之后.

-- 使用
直接COMMIT;
或者ROLLBACK;即可
  • delete from和truncate table的使用选择
    • 虽然truncate table效率高,但是有可能造成事故,所以建议用delete
    • 如果truncate table不加where子句时,效率与delete相同
DDL和DML的说明
1.DDL的操作一旦执行,就不可回滚,set autocommit=false对DDL无效(DDL自带COMMIT)
2.DML的操作默认情况下,一旦执行,也不可回滚
	2.1在执行DML之前执行了set autocommit=false,则执行的DML操作可以实现回滚

7.数据类型

7.1数值类型

整数

类型 字节 有符号数取值范围 无符号数取值范围
TINYINT 1 -128~127 0~255
SMALLINT 2 -32768~32767 0~65535
MEDIUMINT 3 -8388608~8388607 0~16777215
INT/INTEGER 4 -2147483648~2147483647 0~4294967295
BIGINT 8 -9223372036854775808~9223372036854775807 0~18446744073709551615

浮点数

类型 字节 有符号数取值范围 无符号数取值范围
FLOAT 4 超级大 超级大
DOUBLE 8 超级大 超级大
  • 浮点性用有符号类型,有误跟无符号类型的占用空间一样大
  • 浮点数是不准确的,要避免使用=号来判断两个数是否相等.
  • 同时,在对精度要求较高的项目中,不用使用浮点数,要使用定点数↓↓↓

定点数

类型 字节 含义
DECIMAL(M,D),DEC,NUMERIC M+2字节 有效范围由M和D决定

  • 使用DECIMAL(M,D)的方式表示高精度小数,其中,M被称为精度,D被称为标度.

    • M的有效范围:0<=M<=65

    • D的有效范围:0<=D<=30

    • -- 例
DECIMAL(5,2)
表示该列取值范围是-999.99~999.99

  • decimal的最大取值范围和double一样,但是有效的数据范围由MD决定的.

7.2日期类型

类型 字节 日期格式 最小值 最大值 作用
YEAR 1 YYYY或YY 1901 2155 年
TIME 3 HH:MM:SS -838:59:59 838:59:59 年月日
DATE 3 YYYY-MM-DD 1000-01-01 9999-12-03 时分秒
DATETIME 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 年月日时分秒
TIMESTAMP 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC 2038-01-19 03:14:07UTF 带时区的年月日时分秒 
-- 总结:规范写即可,不要花里胡哨的

7.3字符串类型

文本字符串

文本字符串类型 字节 值的长度 长度范围
char(M) M个字节 M 0<=M<=255
Varchar(M) M+1个字节 M 0<=M<=65535
TinyText L L+2个字节 0<=L<=255
Text L L+2个字节 0<=L<=65535
MediumText L L+3个字节 0<=L<=16777215
LongText L L+4个字节 0<=L<=4294967295
ENUM L 1或2个字节 1<=L<=65535
SET L 1,2,3,4或8个字节 0<=L<=64
  • char
    • char类型一般需要预先定义字符串长度.如不知道,则默认为1个字符
    • 如果保存时,数据的实际长度比char类型声明的长度小,则会在右侧填充空格以达到指定的长度,当MySQL检索char类型的数据时,char类型的字段会去除尾部的空格
    • 定义char类型字段时,声明的字段长度即为char类型字段所占的存储空间的字节数
  • varchar
    • varchar类型定义时,必须指定长度,否则报错
    • 检索varchar类型的字段数据时,会保留数据尾部的数据,varchar类型的字段所占用的存储空间为字符串实际长度+1个字节
类型 特点 空间上 时间上 使用场景
char 固定长度 浪费存储空间 效率高 存储不大,速度要求高
varchar 可变长度 节省存储空间 效率低 非char的情况

  • enum

    • -- 例
create table student(
    season enum('春','夏','秋','冬')
);
-- 插入数据
insert into student values ('春');
insert into student values (1);

    • 可以添加null值(没有限制非空的情况下)

    • 也可以使用索引进行调用(从1开始)

    • 一次只能添加一个值(忽略大小写)

  • set

    • 跟enum的区别就是set可以一次添加多个值

二进制字符串

主要用于存储一些二进制数据,如:图片,音频,视频等.

  • binary与varbinary
    • 对应的是char和varchar类型,用的不多
  • BLOB类型(用的不多)
二进制字符串类型 字节 值的长度 长度范围
TinyBlob L+1个字节 L 0<=L<=255
Blob L+2个字节 L 0<=L<=65535(相当于64kb)
MediumBLOB L+3个字节 L 0<=L<=16777215(相当于16MB)
LongBlob L+4个字节 L 0<=L<=4294967295(相当于4GB)

7.4JSON类型

json是一种轻量级的数据交换格式.简洁和清晰的层次结构使得json称为理想的数据交换语言.它易于人阅读和编写,同时也易于机器解析和生成.并有效地提升网络传输效率.json可以将JavaScript对象中表示一组数据转换为字符串,然后就可以在网络或者程序直接轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式.

  • -- 创建
create table test(
    js json
);
-- 插入数据
insert into test(js) values ('{json对象值: 属性值,......}');

7.5使用建议

整数	INT
小数	DECIMAL(M,D)
日期	DATETIME
非负数的字段,必须使用unsigned

posted @   22-10-21  阅读(21)  评论(0)  编辑  收藏  举报
相关博文：
·  MySQL(三)
·  MySQL(二)
·  MySQL
·  MySQL（一）
·  SQL基础语句
阅读排行：
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 字符编码：从基础到乱码解决
· 提示词工程——AI应用必不可少的技术
点击右上角即可分享
微信分享提示
AI IDE Trae