SQL语句

最新推荐文章于 2024-10-31 18:10:53 发布
最新推荐文章于 2024-10-31 18:10:53 发布
阅读量131 收藏
点赞数 1
分类专栏： MYSQL 文章标签： sql 数据库 Powered by 金山文档
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Purple_night_/article/details/128796419
版权

分类：

  1. DCL（Data Control Language):数据控制语言，用来哦定义访问权限和安全级别）

  1. DDL（Data Definition Language):数据定义语言。对于库和表的操作

  1. DML（Data Manipulate Lanaguage):数据操作语言。对数据的增删改

  1. DQL（Data Query Language):数据查询语言，对数据进行查询

  1. TCL(Transition Control Language):事务控制语言。

DCL：数据控制语言

    • 创建用户：

创建了一个用户名为jsoft,在任意ip地址上都能登录，密码是Mys123456的用户。duo

create user 'jsoft'@'%' identified by 'Mys123456';

    • 修改密码：

5.7版本需要使用password()修改密码

set password for jsoft@'%'=password('123456');

8.0版本修改密码

set password for jsoft@'%'='123456';

    • 给用户授权

create:可以创建数据库

select:可以查询数据库

delete:可以删除数据

update:可以更新数据

insert:可以插入数据

all：所有权限

语法：grant 权限1，权限2.....on 数据库.*to 用户名@IP地址

grant all on `admin`.*to `jsoft`@`%`

    • 撤销权限

revoke all on `admin` .* from `jsoft`@`%`;

    • 查看权限

show grants for `jsoft`@`%`;

    • 删除用户

drop user `jsoft`@`%`;

DDL：主要在定义或者改变表的结构。主要的命令：CREATE，ALTER,DROP等

    • 创建表：

create table 表名（

字段名1（列名） 类型（宽度） 约束条件

字段名2（列名） 类型（宽度） 约束条件，

字段名3（列名） 类型（宽度） 约束条件，

........

);

    • 常用的数据类型

（1) 整型 tinyint:-128~127

smallint:2字节

mediumint:3字节

int:4字节

bigint:8字节

在使用中，可以设置成无符号。tinyint(无符号），0~255，unsigned

(2)浮点型 float(m,d):4字节 单精度浮点型

double(m,d):8字节，双精度浮点型

decimal(m,d):对应我们java的BigDecimal m总长度 d 小数位数

float(5,3)

插入的数据是：123.45678。最后得到的结果是99.999

插入的数据是：12.34567。最后得到的结果是12.346

在使用浮点型的时候，注意陷阱，要以插入数据库中的实际结果为准。

（3）字符串类型 char：固定长度，最多255个字符。

varchar：可变长度，最大容量65535个字符

tinytext：可变长度，最多255个字节。

mediumtext:可变长度，16MB

longtext：可变长度，4GB

varchar和text区别？

text:不能设置默认值，有溢出存储。

varchar:可以设置默认值

（4）日期和时间类型

date:日期 2023-01-30

time:时间，08：49：30

datetime:日期时间，2023-01-30 08：49：30

timestamp:时间戳。毫秒数。自动存储记录的修改时间。（mysql5.7以后）

    • 建表约束

(1)NOT NULL:非空约束

(2)UNIQUE：唯一约束，不能重复

(3)PRIMARY KEY:主键约束，自带非空，唯一索引

(4)DEFAULT:默认值

(5)FOREIGN KEY:外键约束

创建一张author表

create table author(
`aut_id` INT,
`aut_name` varchar(50) NOT NULL,#非空约束
`gender` char(1) default `男`
`country` varchar(50),
`birthday` datetime,
primary key(aut_id,aut_name);

创建一张book表

create table `book`(
`id` int primary key auto_increment,
`name` varchar(50) not null,
`bar_code` varchar(30) not null unique,
`aut_id` int not null --作者的编号
）

主键约束：（主键）

主键分为单字段主键和多字段联合主键

注意：

  1. 每个表只能定义一个主键

  1. 主键值必须唯一，不能为NULL，表中不能存在有相同主键值的两行数据。

  1. 一个字段名只能联合主键字段表中出现一次。

  1. 联合主键用要慎重

create table author(
`aut_id` INT,
`aut_name` varchar(50) NOT NULL,#非空约束
`gender` char(1) default `男`
`country` varchar(50),
`birthday` datetime
);
create table author(
`aut_id` INT,
`aut_name` varchar(50) NOT NULL,#非空约束
`gender` char(1) default `男`
`country` varchar(50),
`birthday` datetime
primary key(aut_id)
);

外键约束：FOREIGN KEY

外键约束维护的是表与表之间的关系。规定了当前列的数据必须来自于一张其他表的某一列中的值。

逻辑外键：在查询的时候，通过SQL的多表联查，临时使用的一种语法。

物理外键：在查询的时候，通过SQL添加的外键，可以看见的外键，约束能力很强。

create table `book`(
`id` int primary key auto_increment,
`name` varchar(50) not null,
`bar_code` varchar(30) not null unique,
`aut_id` int not null,
foreign key (aut_id) references author(aut_id)
);

  1. 对标的修改操作

切换库：use 库名；

显示当前库中的所有表：show tables;

查看表结构：desc 表名；

添加列：

alter table author add(
hobby varchar(20),
address varchar(50)
);

4.修改表：

    • 修改表的数据类型

alter table author modify address varchar(100);

    • 修改列名

alter table author change address addr varchar(60);

    • 删除列

alter table author drop addr;

    • 修改表名

alter table author rename  `authors`;

删除表：

drop table if exists `author`;

DML(数据操作语言）（重要）

用来对表记录进行增删改

    • 插入数据

insert into author(
aut_name,gender,country,birthday)values
('金庸`,`男`,`中国`,`1924-3-10`);
#批量插入
insert into author
values
(null,`韩寒`,`男`,`中国`,`1982-9-23`),
(null,`海明威`,`男`,`美国`,`1982-9-23`);

    • 修改数据

（1）修改某列的全部值

update `author` set `country`=`中国`;

（2）修改某一条数据的某列的值

UPDATE  `author` set `country`=`英语` where aut_id=2;

where关键字：=，！=，<>,<,>,<=,>=,between...and,in(...),is null, not,or,and...

where aut_id>1;
where aut_id in(1,3,5);
where aut_id between 1 and 4;
where aut_id>1 and aut_name=`xxx`;
where aut_name is null;
where aut_name is not null;

    • 删除数据

delete from author where aut_id=4;

全部删除：

delete from author;

truncate:截断表，删除表中的全部数据

truncate table author;

面试题：数据库中表的数据的删除有几种方式？

  • drop

  • truncate

  • delete

面试题：truncate和delete的去别的？

  • 主键的问题

  • truncate效率高，可以理解为把表删除了，又重新创建了这个表。

  • truncate实际上是一个DDL,不能回滚（事务）

  • truncate和delete都不会对表结构约束索引不会改变。

DQL数据查询语言

搭建环境：

create table student(
id int primary key auto_increment,
`name` varchar(10),
age int(5) not null,
gender varchar(2)
)
create table course(
id int primary key auto_increment,
`name`varchar(20),
t_id int
)
create table scores(
s_id int,
score int,
c_id int,
primary key(s_id,c_id)
)

    • 基础查询

    • 基础查询:

查询某一张表的所有数据。（实际上不建议写*）

select * from student;
#查询指定的的列
select id,name from student;
#去重
select distinct gender from student;

    • 列运算

select id,name,age*10 from student;

注意：

null和任意数字做运算，结果都是NULL

字符串和数字做计算额，如果能转成数字，则会转成数字继续计算。如果无法转换成数字，把字符串当作0处理。

    • 别名

select id as '编号',name as '姓名',age as '年龄' from student;
select id  '编号',name '姓名',age  '年龄' from student;

    • 条件筛选

select * from student where id=3;
select * from student where id in(1,3,7);
select * from student where id>5;
select * from student where id between 3 and 7;
select * from student where id between 3 and 7 or age>50;

    • 模糊查询

select * from student where name like "马_";
select * from student where name like "马%“；

    • 排序（默认是升序）

asc:升序

desc:降序

select * from student order by age;
select * from student order by age asc;
select * from student order by age desc;

    • 聚合函数（统计）

  1. count:查询满足记录条件的记录数

select count(1) from student where gender="女”；

如果列值为空，不会进行统计

  1. max:查询满足条件的记录中的最大值

select max(age) from student;

  1. min:查询满足田间的记录的最小值

select min(age) from student;

  1. sum:求和（忽略null值）

select sum(salary) from employee;

  1. avg:平均数

select avg(age) from student;

    • 分组查询

分组查询就是将原有的数据进行分组统计。group by

select gender,avg(age) from student group by gender;
select gender,avg(age) from student where gender='男' group by gender;
#先筛选人，再分组
select gender,avg(age) from student where age>50 group by gender;
#先分组，再筛选人
select gender,avg(age) from student group by gender having avg(age)>55;

面试题：

having和where的区别？、

（1）作用的位置

（2）作用的范围

（3）作用的效果

分页查询（不同的数据库产品，语法不一样）

limit关键字

select * from student limit 0,4

select * from student where gender='男' order by age desc limit 0,4;

limit字句分页查询永远在最后。

oracle:rownum

sqlserver:top

多表查询

一：笛卡尔积

如果我们的查询条件相对比较复杂，需要涉及多张表，如果是两张无关的表联合查询，列出所有可能的结果，两张表的数据的积，就是我们数据库层面的笛卡尔积。

在开发中，禁止出现笛卡尔积。

二：多表查询SQL

SQL92语法：

#查询每科课程对应的老师。
select c.name as `cname`,t.name as `tname` from course c,teacher t where c.t_id=t.id;

注意：

  1. 如果不需要查询所有列，在select后就用表名.列名的形式来查询想要的列。

  1. 表名是可以起别名，一旦表名起了别名，就必须使用别名，不能用原来的表名。

  1. 需要使用到where关键字来进行条件的关联。

SQL99语法：

内连接：

在我们刚才的sql中，使用逗号分隔两张表进行查询。

select c.id,t.name,c.name from teacher t inner join course c 
on t.id=c.t_id where c.id=1;

实际上就是根据条件，找到表A和表B的数据的交集。

外连接（常用）：

内连接和外连接的区别：

  • 对于内连接中的两张表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。

  • 对于外连接中的两张表，即使【驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，可以分为【左外连接】和【右外连接】。

左外连接

select c.id,c.·name·,t.·name· from course c left outer join teacher t on c.t_id=t.id;
--等价于
select c.id,c.name,t.name from course c left  join teacher t on c.t_id=t.id;

左表中的英语这门课是没有与之匹配的老师，但是英语课程也显示出来了，同时给他匹配了一个对应的老师的值是null。

右外连接

select c.id,c.·name·,t.·name· from course c right outer join teacher t on c.t_id=t.id;
--等价于
select c.id,c.name,t.name from course c right  join teacher t on c.t_id=t.id;

右表中的孙子这个老师是没有与之匹配的课程，但是孙子也显示出来了，同时给他匹配了一个对应的课程的值是null。

全连接

select * from teacher t full  outer join course c on c.t_id=t.id;

虽然我们的mysql不支持上面的写法，我们可以采用联合查询的方法来实现这种效果。

select * from teacher t left join course c on t.id=c.t_id;
union
select * from teacher t right join course c on t.id=c.t_id;

三：子查询

  • 标量子查询：结果集只有一行一列

  • 列子查询：结果集只有多行一列

  • 行子查询：结果集只有一行多列

  • 表子查询：结果为多行多列

where/having型子查询

查询比马化腾大的所有学生

SELECT * from  student where age>(select age from student where name='马化腾')

查询有一门学科分数大于90分的学生的信息

select * from student where id in(select s_id from scores where  score>90)

查询男生且年龄最大的学生的信息

SELECT * from student WHERE age=(select max(age) from student GROUP BY gender HAVING gender='男');

总结：

  • where型子查询，如果where列=内层sql，则内层sql返回的必须是单行单列。

  • where型子查询，如果where列in（内层sql),内层sql返回必须是单列，可以多行。

from型子查询

查询数学成绩排名前五的学生，正序排列。

select * from (
    select s.id,s.gender,s.name,sc.score,c.name as cname
        from student s
left join scores sc on sc.s_id=s.id left join course c on c.id=sc.c_id where c.name='数学' order by sc.score desc limit 0,3
)t where t.gender='男' order by t.score asc;

select型子查询

查询每个老师代课的数量。

select t.id,t.name,count(*) '代课的数量'
from teacher t 
left join course c 
on t.id=c.t_id
group by t.id,t.`name`;
--子查询
select t.id,t.name,(
 select count(*) from course c where c.t_id=t.id
 ) as '代课的数量' from teacher t;

exists型子查询

表示判断子查询是否有返回值（true/false),有则有返回值，没有则返回false

 select * from teacher t where EXISTS(
  SELECT * from course c where c.t_id=t.id
    )

MYSQL常用函数

聚合函数

  • count：统计

  • min：最小值

  • max：最大值

  • sum：求和

  • avg：平均值

数值型函数

  • CEILING:向上取整

  • FLOOR:向下取整

  • ROUND:四舍五入

  • PI:圆周率

  • RAND：0-1的随机数

  • TRUNCATE（X,D）：截断 X小数 D想要几位小数。

字符串函数

日期和时间函数

获取时间和日期

  • 获取日期

select CURDATE();

select CURRENT_DATE;

  • 获取时间

select CURTIME();

selectt CURRENT_TIME;

  • 获取时间和日期

select NOW();

select SYSDATE();

  • 时间戳和日期转换

获取时间戳

select UNIX_TIMESTAMP();

时间戳的转换

select FROM_UNIXTIME(UNIX_TIMESTAMP());

  • 根据日期获取年月日

select MONTH(SYSDATE());

select MONTHNAME(SYSDATE());

select DAYNAME(SYSDATE());

select DAYOFWEEK(SYSDATE());

  • 时间日期计算函数

select CURDATE(),CURRENT_DATE+5;

select DATE_ADD("2023-1-31",INTERVAL 1 DAY);

计算时间间隔

select DATEDIFF("2023-1-31“，”2023-1-31");

日期的格式化

select DATE_FORMAT(SYSDATE(),"%W %M %D %Y")

select DATE_FORMAT(SYSDATE(),"%W %M %D %Y %p %h:%i")

加密函数

select MD5("123456");

流程控制函数

  • select IF(expr1,expr2,expr3)

expr1表达式是真，返回2，如果是假，返回3

select IF(1>2,2,3)

  • select IFNULL("abc","hahaha");

如果第一个函数为null,则取第二个参数的值。

  • select NULLIF(expr1,expr2)

如果expr1=expr2,返回null，否则返回expr1

备注：还有其他的流程控制函数，比如：

case....when...otherwise;

when...then...else;

紫夜✘
关注
专栏目录
使用SQL语句创建数据库与创建表
@初阶牛的博客
11-28 5万+
数据库基础知识讲解,包括创建数据库,修改数据库属性,数据库的分离与附加,创建数据表等操作.
SQL常用语句大全
龙木南的博客
11-01 8万+
SQL常用语句大全
python自动生成SQL语句自动化
一键难忘的博客
04-23 6528
除了使用现有的库和工具外，我们还可以根据项目需求自定义SQL语句生成器，以满足特定的数据操作需求。通过自定义生成器，我们可以灵活地控制生成的SQL语句结构和内容，以适应不同的场景和要求。
sql语句大全
weixin_37990128的博客
10-19 4万+
数据库操作 查看所有数据库 show databases; 1 查看当前使用的数据库 select database(); 1 创建数据库 create databases 数据库名 charset=utf8; 1 5.删除数据库 drop database 数据库名 1 6 .使用数据句库 use database 数据库名 1 7.查看数据库中所有表 show tables; 1 表的操作 1.查看表结构 desc 表名 1 2.创建表结构的语法 create table
《SQL Server基础——SQL语句》
Dustpolaris的博客
06-14 1万+
SQL Server基础——SQL语句 一、创建和删除数据库； 二、创建数据表； 三、创建视图； 四、约束语句； 五、修改语句； 六、终局之战； 七、查询语句； 八、分类汇总； 九、连接查询； 十、特殊查询；
数据库常用SQL语句
helloworld0415的博客
05-27 8627
数据库常用SQL语句
sql语句中union的用法
热门推荐
weixin_42383680的博客
08-22 11万+
union联合的结果集不会有重复值，如果要有重复值，则使用union all union会自动压缩多个结果集合中重复的结果，使结果不会有重复行，union all 会将所有的结果共全部显示出来，不管是不是重复。 union：会对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。 union all：对两个结果集进行并集操作，包括重复行，不会对结果进行排序。 1.sql Union用法 select 字段1 from 表名1 Union select 字段2 from 表名2.....
预处理SQL语句
weixin_46834417的博客
08-19 3126
所谓预处理指的就是将SQL语句中的关键字(如 SELECT…FROM…)与数据(如字段名，数据表名)分离,使得针对不同数据的相同SQL语句的执行开销更小，同时又可防止SQL注入的攻击 **传统方式的SQL语句,**在执行时每条SQL都需要经过分析丶编译和优化的步骤。 预处理方式则是利用客户端与服务器的二进制协议,预先编译一次客户端发送的SQL语句模板,然后再根据客户端发送给服务器相应数量的变量进行执行操作，并旦针对一条SQL语句模板可以执行多次,还无需考虑数据中含有未转义的SQI引号和分隔符字符. 预处理语
SQL语句执行顺序【全】
weixin_52938172的博客
10-10 1万+
SQL语句执行顺序
sql——使用SQL生成SQL语句
m0_43400852的博客
09-18 2580
一、几个SQL*PLUS命令 1、set echo on/off set echo on在执行的时候，看到你的SQL语句。set echo off不想在执行的时候看到SQL语句，只想看到输出的结果。 2、set feedback on/off feedback就是查询所输入的行数，例如：如果运行的select语句返回30行数据，则feedback运行显示：30 row select。 set feedback on会显示对行数的计数，set feed...
动态拼接sql语句工具类,拼接where后面语句
05-25
动态拼接sql语句工具类,拼接where后面语句 配合原生jdbc仿动态sql注入 if (ObjectUtil.isNotEmpty(maxLat)&&ObjectUtil.isNotEmpty(minLat)){ sqlParamList.add(new SqlParam("lat",minLat, SqlOpEnum.GE)); ...
sql语句说明sql语句说明sql语句说明.zip
05-11
sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明...
sqlgenerator:使用JavaSQL语句生成器
07-09
使用JavaSQL语句生成器 以下示例生成 SQL 语句。 实体类 - 这表示数据库中的一个表。 字段类 - 这表示表中的一个字段。 每个字段都属于一个实体。 联接类 - 这表示任意两个表之间的联接。 EntityLookup 类 - 该...
C#中验证sql语句是否正确(不执行语句)
12-25
SET PARSEONLY检查每个 Transact-SQL 语句的语法并返回任何错误消息，但不编译和执行语句。SET PARSEONLY { ON | OFF }当 SET PARSEONLY 为 ON 时，SQL Server 只分析语句。当 SET PARSEONLY 为 OFF 时，SQL Server ...
Access数据库-SQL语句大全.mdb
03-28
Access数据库-SQL语句资料.mdb
【Hive sql面试题】找出连续活跃3天及以上的用户
lzhlizihang的博客
10-31 47
Hive sql面试题：找出连续活跃3天及以上的用户，包括解题思路以及答案 sql 代码
pgsql的行级权限设置
qq_43826626的博客
10-31 345
行级安全性（RLS）是一个特性，允许您根据执行查询的用户来限制查询返回的行。RLS 允许您，根据当前用户和由策略定义的特定条件，来控制对表中各个行的访问。此外要注意的是，此表设置完行级权限后，对应角色才会进入设置的规则，没设置过的角色没有权限访问，可以设置一个默认database_admin角色，每次给上true的条件，表管理者没影响。
详解SQL单表查询
最新发布
Weiki的博客
10-31 610
SQL单表查询
SQL Server 可观测最佳实践
观测云的博客
10-28 778
本实践介绍如何通过观测云监控自建的 SQL Server，帮助我们及时识别数据库性能瓶颈和潜在问题，确保数据库高效运行和稳定性。
SQL语句审计与功能详解
"SQL语句详解——语句级审计" SQL（Structured Query Language）是关系数据库的标准语言，它的历史可以追溯到1974年，由Boyce和Chamberlin提出，并在IBM的System R上实现。随着时间的发展，SQL经历了多个标准化版本...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值