SQL语句

已于 2023-02-13 14:17:01 修改
阅读量2k 收藏 2
点赞数
分类专栏： java后端开发 文章标签： sql 数据库 mysql
于 2022-03-15 22:14:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51416683/article/details/123502950
版权

文章目录

SQL通用语法

  1. SQL语句可以当行书写或多行书写，以分号结尾
  2. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
  3. 注释：
    单行注释：-- 注释内容或 #注释内容（MySQL特有）
    多行注释：/* 注释*/

SQL分类

  1. DDL：操作数据库，表等
  2. DML：对表中的数据进行增删改操作
  3. DQL：对表中数据进行查询
  4. DCL：对数据库进行权限控制

DDL–操作数据库

  1. 查询

SHOW DATABASES;

  1. 创建
    创建数据库

    CREATE DATABASE 数据库名称;

    创建数据库（判断，如果不存在则创建）

    CREATE DATABASE IF NOT EXISTS 数据库名称；

  2. 删除
    删除数据库

    DROP DATABASE;

    删除数据库（判断，如果存在则删除）

    DROP DATABASE IF EXISES 数据库名称;

  3. 使用数据库
    查看当前使用的数据库

    SELECT DATABASE();

    使用数据库

    USE 数据库名称;

DDL–操作表

  1. 查询表
    查询当前数据库下的所有表名称：

    SHOW TABLES;

    查询表结构：

    DESC 表名称;

  2. 创建表

    CREATE TABLE 表名（
    字段名1 数据类型1，
    字段名2 数据类型2，
    …
    字段名n 数据类型n
    );
    注意：最后一行末尾，不能加逗号

    MySQL支持多种数据类型，可分为三类：
    在这里插入图片描述

  3. 删除表
    删除表

    DROP TABLE 表名;

    删除表时判断表是否存在

    DROP TABLE IF EXISTS 表名;

  4. 修改表
    修改表名

    ALTER TABLE 表名 RENAME TO 新表名;

    添加一列

    ALTER TABLE 表名 ADD 列名 数据类型;

    修改数据类型

    ALTER TABLE 表名 MODIFY 列名 数据类型;

    修改列名和数据类型

    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

    删除列

    ALTER TABLE 表名 DROP 列名;

DML

  1. 添加
    1.1 给指定的列添加数据

    INSERT INTO 表名（列名1，列名2，…） VALUES(值1，值2，…);

    1.2 给全部列添加数据

    INSERT INTO 表名 VALUES(值1，值2…);

    1.3 批量添加

    INSERT INTO 表名（列名1，列名2，…）VALUES(值1，值2，…)，（值1，值2，…），（值1，值2，…）…;
    INSERT INTO 表名 VALUES（值1，值2，…），（值1，值2，…）…;

  2. 修改数据

    UPDATE 表名 SET 列名1=值1，列名2=值2，…[WHERE 条件];
    注意：修改语句中如果不加条件，则将所有数据都修改！

  3. 删除数据

DELETE FROM 表名 [WHERE 条件];

DQL

1. 查询语法

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

2. 基础查询

2.1 查询多个字段
>SELECT 字段列表 FROM表名;
查询所有列的数据，列名列表可以使用*来代替，不要使用！！

2.2 去除重复记录
>SELECT DISTINCT 字段列表 FROM 表名;

2.3 起别名
>AS: AS也可以省略‘
eg:SELECT math AS 数学成绩 FROM stu;

3. 条件查询(WHERE)

3.1 条件查询语法

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

3.2条件
在这里插入图片描述

----查询年龄在20-30岁的信息
SELECT * FROM stu WHERE age>=20 && age<=30;
SELECT * FROM stu WHERE age>=20 AND age<=30;
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

----查询生日在2001-1-1至2003-1-1之间的信息
SELECT * FROM stu WHERE birthday>=2001-1-1 && birthday<=2003-1-1;
SELECT * FROM stu WHERE birthday>=2001-1-1 AND birthday<=2003-1-1;
SELECT * FROM stu WHERE birthday BETWEEN 2001-1-1 AND 2003-1-1;

模糊查询 _单个字符 %多个字符
查询姓“马”的信息
SELECT * FROM stu WHERE name LIKE ‘马%’;
查询姓名第二个字为“花”的信息
SELECT * FROM stu WHERE name LIKE ‘_花%’;
查询姓名中有“德”的信息
SELECT * FROM stu WHERE name LIKE ‘%德%’;

4. 排序查询（ORDER BY）

4.1 排序查询语法
>SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1]，排序字段名2  [排序方式2]...;

4.2 排序方式
> ASD:升序排列
DESC:降序排列
> 注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

5. 聚合函数

5.1 概念
将一列数据作为一个整体，进行纵向计算
5.2 聚合函数分类

函数名功能补充
count(列名)统计数量（一般选用不为null的列）取值：1.主键 2. *
max(列名)最大值
min(l列名)最小值当值为ull时不参与
sum(列名)求和
avg(列名)平均值

5.3 聚合函数语法

SELECT 聚合函数名（列名） FROM 表;

null值不参与所有的聚合运算

6. 分组查询(GROUP BY)

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
WHERE和HAVING的区别：

  1. 执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤
  2. 可判断的条件不一样；where不能对聚合函数进行判断，having可以

7. 分页查询（LIMIT）

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数；
起始索引：从零开始
计算公式：起始索引=（当前页码-1） * 每页显示的条目数

8.执行顺序

  1. from 确定查询的是哪一张表的数据
  2. where 指定查询的条件
  3. group by ，having 指定分组条件及分组之后的条件
  4. select 确定执行返回的字段
  5. order by ,limit

DCL

DCL英文全称Data ControlLanguage（数据控制管理），用来管理数据库，用户，控制数据库的访问权限

管理用户

  1. 查询用户

USE mysql ;
SELECT * FROM user;

  1. 创建用户

CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’；

//创建用户可以从任意主机访问数据库，则
create user ‘用户名’@‘% identified by '密码’;

  1. 修改用户密码

ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_naive_password BY ‘新密码’；

  1. 删除用户

DROP user ‘用户名’@主机名；

权限控制

在这里插入图片描述

  1. 查询权限

SHOW GRANTS FOR ‘用户’@‘主机名’；

  1. 授予权限

GRANTS 权限列表 ON 数据库.表名 TO ‘用户’@‘主机名’；

  1. 撤销权限

REVOKE 权限列表 ON 数据库.表名 FROM ‘用户’@‘主机名’；

注意：多个权限直接，使用逗号分隔，授权时，数据库名和表名都可以使用*通配符代表所有

函数

函数是指一段可以直接被另一段程序调用的程序或代码

字符串函数

在这里插入图片描述

例题
由于业务变更，企业员工的员工工号，统一为5位数，目前不足5位数的全部在前面补零。
UPDATE emp set workno = LPAD(workno,5,'0');

数值函数

在这里插入图片描述

例题
通过数据库的函数，生成一个六位数的随机验证码
//如果生成的是0.0123431..  则取整后，12343不足6位，所以补零
SELECT lpad(round(rand()*1000000,0),6,'0');

日期函数

在这里插入图片描述

例题
查询所有员工入职天数，并根据入职天数降序排序
SELECT name,datadiff(curdate(),entrydate) AS days ORDER BY days desc;

流程函数

流程函数是一类很常用的函数，可以在SQL语句中实现条件筛选，从而提高语句的效率
在这里插入图片描述

例题
1. 查询emp表中的员工姓名和工作地址（北京/上海-->一线城市；其他---->二线城市）
SELECT name,
	(CASE workaddress  WHEN '北京' THEN '一线城市' WHEN '上海'  THEN '一线城市' ELSE '二线城市' END)AS '工作地址'
	FROM emp;
2. 统计班级各个学生的成绩，提示的规则如下：
	>=85，展示优秀，
	>=60，展示及格
	其余展示不及格
	SELECT 
		name,
		CASE WHEN  achievement >= 85 THEN '优秀'  WHEN  achievement >= 60 THEN '及格' ELSE '不及格' 
		FROM score;

约束

概述

  1. 概念：约束是作用域表中字段上的规则，用于限制存储在表中·的数据
  2. 目的：保证数据库中数据的正确，有效性和完整性
  3. 分类：约束是作用于表中字段上的，可以创建表/修改表时添加约束
    在这里插入图片描述

约束演示

在这里插入图片描述

CREATE TABLE user(
	id int PRIMART KEY AUTO_INCREMENT COMMENT '主键'，
	name varchar(10) NOT NULL UNIQUE COMMENT '姓名'，
	age int CHECK (age>0 && age<=120) COMMENT '年龄',
	status char(1) DEFAULT '1' COMMENT '状态'，
	gender char(1) COMMENT '性别'
) COMMENT '用户表';

外键约束

  • 概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
    目前这两张表，在数据库层面并未建立外键连接，虽然逻辑上外键层理，但是在物理层面上不可以，所以无法保证数据的一致性和完整性
    在这里插入图片描述
  • 语法
    • 添加外键

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

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

    • 删除外键

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

  • 删除/更新行为
    在这里插入图片描述

ALTER TABLE 表名 ADD CONSTRANT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段) ON UPDATE CASCADE ON DELETE SET NULL;

多表查询

多表关系

  • 一对多(多对一)
    • 案例：部门与员工的关系
    • 关系：一个部门对应多个员工，一个员工对应一个部门
    • 实现：在多的一方建立外键，指向一的一方的主键
  • 多对多
    • 案例：学生和课程的关系
    • 关系：一个学生可以选修多门课程，一门课程可以被多名学生选择
    • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键
  • 一对一
    • 案例：用户和用户详情的关系
    • 关系：一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
    • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的

多表查询概述

  • 概述：指从多张表中查询数据
  • 笛卡尔积：笛卡尔积指的是在数学上中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
  • 多表查询的分类
    • 连接查询
      • 内连接：相当于查询A，B交集部分数据
      • 外连接：
        • 左外连接：查询左表所有数据，以及两张表交集部分数据
        • 右外连接：查询右表所有数据，一级两张表交际部分数据
      • 自连接：当前表与自身的连接查询，自连接必须使用表别名
    • 子查询

内连接

内连接查询的是两张表交集的部分

  • 隐式内连接:

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

  • 显示内连接

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

例题：
查询每一个员工的姓名及关联的部门的名称
隐式内连接实现：
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
SELECT e.name ,dept.name  FROM emp e,dept WHERE e.dept_id = dept.id;
显式连接实现：
SELECT e.name , d.name FROM emp e INNER JOIN dept d  ON e.dept_id=d.id;

外连接

  • 左外连接：相当于查询表1（左表）的所有数据，包含表1和表2交集的部分数据

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

  • 右外连接：相当于查询表2（右表）的所有数据，包含表1和表2交集的部分数据

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

1. 查询emp表中的所有数据，和对应的部门信息（左外连接）
	SELECT * from emp e LEFT OUTER JOIN dept d ON e.dept_id =d.id;
2. 查询dept表中的所有数据，和对应的员工信息（右外连接）
	SELECT * from emp e RIGHT OUTER JOIN dept d ON e.dept_id=d.id;

自连接

语法：

SELECT 字段列表 FROM 表名 别名A JOIN 表名A 别名*ON 条件…;

自连接查询可以是内连接查询，也可以是外连接查询

例题
1. 查询所有员工 及其领导的名字
	SELECT * FROM emp a,emp b WHERE a.managerid = b.id;
2. 查询所有员工emp 及所有领导名字emp ,如果员工没有领导，也需查询出来
	SELECT * FROM emp a LEFT OUTER JOIN emp b ON a.managerid = b.id;

联合查询-union，union all

对于union查询，就是把多次查询结果合并起来，形成一个新的查询结果集

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

注意：联合查询的多张表的列数必须保持一致，字段类型也需保持一致

例题
将年龄大于50岁的员工和工资高于5000的员工查询出来
SELECT * FROM emp WHERE age>50
UNION
SELECT * FROM emp WHERE salsry>5000;
//union all 将两次查询的结果集直接进行合并
//union 将两次结果集合并后的结果去重显示

子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称为子查询

SELET * FROM t1 WHERE column1 = (SELECT column FROM t2);
//子查询的外部语句可以是在INSERT/UPDATE/DELETE/SELECT的任何一个

  • 根据子查询结果的不同，分为
    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询（子查询结果为多行多列）
  • 根据子查询出现的位置分为：WHERE之后，FROM之后，SELECT之后

标量子查询

--标量子查询
1. 查询“销售部”的所有员工
	a. 查询“销售部”部门id
	b. 根据销售部部门Id查询员工
	SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='销售部');
2. 查询在‘方东白’入职之后的员工信息
 a. 查询‘方东白’的入职时间
 b. 根据该时间查询员工
  SELECT * FROM emp WHERE entrydata > (SELECT entrydata FROM emp WHERE name='方东白');

列子查询

子查询的返回结果时一列（可以是多行的），这种子查询称为列子查询
常用操作符：IN，NOT IN，ANY，SOME，ALL
在这里插入图片描述

--列子查询
1. 查询“销售部”和“市场部”的所有员工信息
	a. 查询“销售部”和“市场部”的部门id
	b. 根据部门id，查询员工
	SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name ='销售部' OR name ='市场部'));
2. 查询比财务部所有人工资都高的员工信息
	a. 查询所有财务部人员工资
	b.比财务部所有人工资都高的员工信息
	SELECT * FROM emp WHERE salary > all (SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='财务部'));
3. 查询比研发部其中任意一人工资高的员工信息
	a. 查询所有研发人员工资
	b.比研发部所有人工资都高的员工信息
	SELECT * FROM emp WHERE salary > any (SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用操作符：=，<>，IN，NOT IN

--行子查询
 查询与‘张无忌’的薪资及直属领导相同的员工信息；
	a. 查询张无忌的薪资及直属领导
	b. 查询与‘张无忌’的薪资及直属领导相同的员工信息
	SELECT * FROM emp WHERE (salary,managerid)=(SELECT salary,managerid FROM emp WHERE name = '张无忌')；

表子查询

子查询的返回结果时多行多列，这种子查询称为表子查询
常用操作符:IN

1. 查询与’鹿杖客‘，‘宋远桥’的职位和薪资相同的员工信息
	a. ’鹿杖客‘，‘宋远桥’的职位和薪资
	b. 查询与’鹿杖客‘，‘宋远桥’的职位和薪资相同的员工信息
	SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name = '鹿杖客' OR name ='宋远桥');
2. 查询入职日期时'2006-01-01'之后的员工信息及其部门信息
	a. 入职日期是“2006-01-01"之后的员工
	b. 查询这部分员工，对应的部门信息
	SELECT * FROM （SELECT  * FROM emp WHERE entrydate >'2006-01-01'）e LEFT OUTER JOIN dept d ON e.dept_id = d.id;

多表查询案例

wilihelmi
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据分析系列学习之My SQL（二）SQL语言操作1
Sherrylanss的博客
05-18 249
一、操作及概念理解 首先我们明白了为什么在《数据分析系列学习之My SQL（一）》中除了下载My SQL还要下载Navicat。这两个软件在操作中如何相辅相成协助我们提高查询办公效率的？ 那么我们先定义来个简单的定义： My SQL属于SERVICE服务端，Navicat属于Client端。 基本操作步骤：通过Client端去操作MySQL数据库。【其中MySQL数据库主要承担了数据存储与管理功能】，因此只有通过Client端的操作才能按照我们的需求去提取和调用数据来达到我们想要达到的目的。简而..
SQL语句示例
09-11
SQL语句示例
MSSQL表存在则删除
LinoY93的博客
09-23 468
【代码】MSSQL表存在则删除。
SQL语句语法
03-21
SQL语句语法，合适需要入门的同学学习，很基础的内容，需要可以下载。
sql语句中用问号代替参数
08-02
sql语句中用问号代替参数
表存在则删除，不存在则创建（一句sql实现）
Flashing-C的博客
08-15 1万+
通过一句sql实现： 1、表存在则删除；2、表不存在则创建； mysql: drop table if exists `test`; create table if not exists `test` (`id` integer not null, `name` varchar(10), primary key(`id`)); sqlserver: if ex
如果存在表则删除表然后创建Mysql
热门推荐
孤竹的博客
12-20 7万+
如果存在表则删除表然后创建Mysql drop table if exists suspects; create table suspects( id int PRIMARY KEY not null AUTO_INCREMENT, xm VARCHAR(20), xb VARCHAR(20), gmsfhm VARCHAR(100), csrq DATE, whcd VARC
Sql删除
weixin_47247228的博客
04-20 3290
DROP TABLE 语句从数据库中删除一个或多个表。 DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name; 在上面语法中， 首先， database_name 指定要删除的表的名称。 其次，指定创建表的数据库的名称以及表所属的模式的名称。数据库名称是可选的。 如果不指 定， DROP TABLE 语句将删除当前数据库中的表。 第三，使用 IF EXISTS 子句仅在表存在时才删除表。 自SQL Server 2016 13.x起，
判断SQL Server中是否存在某张表，删掉重新创建
weixin_43783990的博客
05-24 652
重新创建表
如果存在这个表，则删除这个表的各种sql写法
雨水霂
08-07 4550
mysql：  drop table if exists address_book sql2005: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[表名]') AND type in (N'U')) DROP TABLE [dbo].[表名] sql2000:
泛微系统SQL语句大全
12-29
泛微系统中常见的主要基于SQL server相关的SQL语句大全， 其中包括组织架构相关SQL，流程相关SQL以及其他自定义需求； 效率排名，等SQL总数将近百；
基本SQL语句
12-15
数据库最基层的SQL语句，新手必备，绝对靠谱，不懂可以留言
sql server 2008 r2 创建表前，先判断是否存在，如果存在先删除
Happy_Girl2012的专栏
03-11 1056
if  exists(select * from sysobjects where name = 'student' and type = 'U') drop table student; create table student(     id int identity(1,1) primary key not null,     name nvarchar(10) not null,
oracle（PL/SQL）表操作：创建表时检查数据库是否存在该表，若存在删除再创建
阿发的专栏
04-15 9133
Technorati 标签: 在PL/SQL中创建表之前判断表是否存在 如果有就删除表的执行语句的书写   由于才开始使用PL/SQL来进行Oracle的客户端操作，由于以前使用sql 2005 来执行“创建表之前判断表是否存在 如果有就删除表，再创建”的操作语句非常简单： 1: if exists ( 2: select * from sysobjects 3
sql脚本创建表【如已经有表，先删除，后创建】
believe的专栏
08-10 5923
if  exists(select * from sysobjects where  type = 'U' and name = 'tablename') drop table tablename; 查看所有的数据库 SELECT * FROM sysdatabases;
MySQL第10章_创建和管理表
LeungKiChen
11-25 441
MySQL第10章_创建和管理表 #第10章_创建和管理表 SELECT * FROM `order`; #1. 创建和管理数据库 #1.1 如何创建数据库 #方式1： CREATE DATABASE mytest1; # 创建的此数据库使用的是默认的字符集 #查看创建数据库的结构 SHOW CREATE DATABASE mytest1; #方式2：显式了指名了要创建的数据库的字符集 CREATE DATABASE mytest2 CHARACTER SET 'gbk'; # SHOW.
父子级分类统计分类下数量sql
最新发布
pscool的博客
05-19 72
【代码】父子级分类统计分类下数量sql。
mybatis动态sql语句
03-31
MyBatis动态SQL语句是指在编写SQL语句时可以根据不同的条件动态生成不同的SQL语句。 MyBatis提供了以下几种方式来实现动态SQL语句： 1. if元素：通过if元素可以根据条件判断来生成不同的SQL语句片段。例如： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

wilihelmi

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值