0
0

MySql（干货）

    写这篇博客的目的不是为了将介绍原理，而是为了Sql中的代码操作属实太多了，在这里进行一个汇总，方便查阅！！！

Sql分类
分类全称说明
DDL
Data Definintion Language数据定义语言，用来定义数据库对象DMLData Manipulation Language数据库操作语言，用来对数据库表中的数据进行增删改DQLData Query Language数据查询语言，用来查询数据库的表的记录DCLData Control Language数据库控制语言，用来创建数据库用户，控制数据库的访问权限

DDL:

数据库操作

1.查询所有数据库

SHOW DATABASES

2.查询当前数据

select database()

3.创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ] 

#如果wincast存在的话则不用创建，不存在的话则直接创建
create database if not extists wincast

#创建一个wincast数据库并指定字符集
create database wincast default charset utf8mb4;

案例：

create database if not extists itcast;

4.删除数据库desc 表名 ;

DROP DATABASE [IF EXISTS] 数据库名

5.切换数据库

use 数据库名

表操作

1.查询当前数据库中的所有表

show tables;

2.查看指定表结构

desc 表名 ;

3.**查询指定表的建表语句 **

show create table 表名 ;

4.创建表的结构

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ]

案例：

create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
) comment '用户表';

5.添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

案例：

#为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

6.修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

7.修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

案例：

#将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

8.删除字段

ALTER TABLE 表名 DROP 字段名;

案例：

#将emp表的字段username删除
ALTER TABLE emp DROP username;

9.修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例：

#将emp表的表名修改为 employee
ALTER TABLE emp RENAME TO employee;

10.删除表

DROP TABLE [ IF EXISTS ] 表名;

案例：

#如果tb_user表存在，则删除tb_user表
DROP TABLE IF EXISTS tb_user;

11.删除指定表，并重新创建表

TRUNCATE TABLE 表名;

DML

添加数据

1.给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

案例：

#给employee表所有的字段添加数据 
insert into employee(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

2.给全全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...);

案例：

#插入数据到employee表
insert into employee values(2,'2','张无忌','男',18,'123456789012345670','2005-01-
01');

3.批量的添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

案例：

#批量插入数据到employee表
insert into employee values(3,'3','韦一笑','男',38,'123456789012345670','2005-01-
01'),(4,'4','赵敏','女',18,'123456789012345670','2005-01-01');

修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

DQL

基础语法

SELECT
   字段列表
FROM
   表名列表
WHERE
  条件列表
GROUP BY
  分组字段列表
HAVING
  分组后条件列表
ORDER BY
  排序字段列表
LIMIT
  分页参数

基础查询

1.查询多个字段

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT * FROM 表名 ;

2.字段设置别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;

SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1.语法

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

2.条件

比较运算符功能>大于>=大于等于<小于<=小于等于<>或！=不等于BETWEEN...AND...在某个范围之内（含最小、最大值）IN(...)在in之后的列表中的值，多选一LIKE 占位符模糊匹配（_匹配单个字符，%匹配任意一个字符）IS NULL是NULL==等于
常用逻辑运算符
运算符功能ADD 或&&并且（多个条件同时成立）OR 或 ||或者（多个条件任意一个成立）NOT 或！非，不是

聚合函数

语法：

SELECT 聚合函数(字段列表) FROM 表名 ;

常见的聚合函数
函数功能count统计数量max最大值min最小值avg平均值sum求和

分组查询

语法

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];

# 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender ;

排序查询

语法

#ASC：升序（默认值） DESC:降序

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ; 1

#根据年龄对公司的员工进行升序
select * from emp order by age asc;
select * from emp order by age;

分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
# 查询第1页员工数据, 每页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;

执行顺序

DCL

查询用户

select * from mysql.user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

案例：

#创建用户itcast,只能够在当前主机localhost进行访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

修改用户密码

# 修改用户heima的访问密码为1234;
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

案例：

alter user 'heima'@'%' identified with mysqlnativepassword by '1234';

删除用户

DROP USER '用户名'@'主机名' ;

案例：

drop user 'itcast'@'localhost';

权限控制

权限说明ALL、ALL PRIVLEGES所有权限SELECT查询数据INSERT插入数据UPDATE修改数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表DELETE删除数据

1.查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

案例：

#查询 'heima'@'%' 用户的权限
show grants for 'heima'@'%';

2.授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

案例：

#授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限
grant all on itcast.* to 'heima'@'%';

3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

案例：

#撤销 'heima'@'%' 用户的itcast数据库的所有权限
revoke all on itcast.* from 'heima'@'%';

函数

1.字符串函数

函数功能CONCAT(S1，S2,...,Sn)字符串拼接，将S1,S2,...,Sn拼接成一个字符串LOWER(str)将字符串str全部转为小写UPPER(str)将字符串str全部转换为大写LPAD(str,n,pad)左填充，用字符串pad对str的右边进行填充，达到n个字符串长度RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度TRIM(str)去掉字符串头部和尾部的空格SUBSTRING(str，start,len)返回从字符串str从start位置起的len个长度的字符串
案例：

  • concat:字符串拼接
select concat('Hello' , ' MySQL');
  • lower:全部转小写
select lower('Hello');
  • upper:全部转大写
select upper('Hello');
  • lpad:左填充
select lpad('01', 5, '-');
  • rpad:右填充
select rpad('01', 5, '-');
  • trim:去除空格
select trim(' Hello MySQL ');
  • substring:截取子字符串
select substring('Hello MySQL',1,5);

2.数值函数

常见的数值函数
函数功能CEIL(x)向上取整FLOOR(x)向下取整MOD(x,y)返回x/y的模RAND()返回0-1内的随机数ROUND(x,y)求参数x的四舍五入的值，保留y位小数

  • ceil:向上取整
select ceil(1.1);
  • floor:向下取整
select floor(1.9);
  • mod:取模
select mod(7,4);
  • rand:获得随机数
select rand();
  • round:四舍五入
select round(2.344,2);

3.日期函数

常见的日期函数
函数功能
CURDATE()

返回当前日期

CURTIME()

返回当前时间

NOW()

返回当前日期和时间

YEAR(date)

获取指定date的年份

MONTH(date)

获取指定date的月份

DAY(date)

获取指定date的日期

DATE_ADD(date, INTERVAL expr type)

返回一个日期/时间值加上一个时间间隔expr后的时间值

DATEDIFF(date1,date2)

返回起始时间date1 和 结束时间date2之间的天数

  • curdate：当前日期
select curdate();
  • curtime：当前时间
select curtime();
  • now：当前日期和时间
select now();

  • YEAR、MONTH、DAY:当前年、月、日

select YEAR(now());
select MONTH(now());
select DAY(now());

  • date_add:增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR )

  • datediff:获取两个日期相差的天数

select datediff('2021-10-01', '2021-12-01');

流程函数

常见的流程函数

**函数 **

**功能 **

IF(value , t , f)

如果value为true，则返回t，否则返回

f

IFNULL(value1 , value2)

如果value1不为空，返回value1，否则

返回value2

CASE WHEN [ val1 ] THEN [res1] ...

ELSE [ default ] END

如果val1为true，返回res1，... 否

则返回default默认值

CASE [ expr ] WHEN [ val1 ] THEN

[res1] ... ELSE [ default ] END

如果expr的值等于val1，返回

res1，... 否则返回default默认值

  • if
select if(false, 'Ok', 'Error');
  • ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
  • case when then else end
#需求: 查询emp表的员工姓名和工作地址 (北京/上海-> 一线城市,其他-> 二线城市)
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址' from emp;

约束

约束分类

约束描述关键字非空约束限制该字段的所有数据都是唯一，不重复的NOT NULL
唯一约束

保证该字段的所有数据都是唯一、不重复的

UNIQUE

主键约束

主键是一行数据的唯一标识，要求非空且唯一

PRIMARY

KEY

默认约束

保存数据时，如果未指定该字段的值，则采用默认值

DEFAULT

检查约束(8.0.16版本

之后)

保证字段值满足某一个条件

CHECK

外键约束

用来让两张表的数据之间建立连接，保证数据的一致 性和完整性

FOREIGN

KEY

语法

1.添加外键

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;

案例：

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

2.删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

#删除emp表的外键fk_emp_dept_id。
alter table emp drop foreign key fk_emp_dept_id;

3.删除/更新行为

行为说明
NO

ACTION

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为

RESTRICT

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为

CASCADE

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

SET NULL

当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。

SET

DEFAULT

父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
  • CASCADE
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;
  • SET NULL
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null ;

多表查询

内连接

1.隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

2.显示内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

外连接

1.左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

2.右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ; 1

3.自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

4.联合查询

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

案例：

#将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;

5.子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

6.列子查询

操作符描述IN
在指定的集合范围之内，多选一

NOT IN

不在指定的集合范围之内

ANY

子查询返回列表中，有任意一个满足即可

SOME

与ANY等同，使用SOME的地方都可以使用ANY

ALL

子查询返回列表的所有值都必须满足

存储引擎

1.建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

案例：

#创建表 my_myisam , 并指定MyISAM存储引擎
create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;

索引

1.创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

2.查看索引

SHOW INDEX FROM table_name ;

3.删除索引

DROP INDEX index_name ON table_name ;
标签： mysql oracle 数据库
本文转载自: https://blog.csdn.net/dfdbb6b/article/details/132248823
版权归原作者 吃橘子的Crow 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySql（干货）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航