语句(2023)"/>
详解SQL常用语法语句(2023)
文章目录
- 1. 什么是sql语句？
- 1.1 DDL(数据定义语言)用法
- 1.2 DML(数据操纵语言)
- 1.3 DQL(数据查询语言)
- 1.4 DCL(数据控制语言)和TCL(事务控制语言)
- 1.5 子查询
- 1.6 连接操作
- 2. 什么是数据库对象？
- 3. 如何创建表？
- 3.1 数值类型
- 3.2 字符类型
- 3.3 时间日期类型
1. 什么是sql语句？
sql : sql 语句被称为 结构化查询语句，是在 关系型 数据库（采用关系模型来组织数据的数据库，使用行和列的形式存储数据）里进行相关操作的标准化语言
sql可以根据作用分为以下几种类型：
DDL ：数据定义语言
DML ：数据操纵语言
DQL ：数据查询语言
DCL ：数据控制语言
TCL ：事务控制语言
1.1 DDL(数据定义语言)用法
定义 : DDL主要对数据库对象进行操作，主要由create(创建), alter(修改), drop(删除)等关键字组成。
| 操作 | 语法 | 示例 |
|---|---|---|
| 创建数据库 | create database 数据库名称 | 创建一个名称为test的数据库：create database test |
| 删除数据库 | drop database 数据库名称 | 删除数据库名为test的数据库：drop database test |
| 查询所有存在的数据库 | show databases | 查询当前存在的所有数据库：show databases |
| 切换数据库 | use 数据库名称 | 切换数据库test：use test |
| 查询当前所处的数据库名 | select database() | 查询当前所处的数据库名： select database() |
| 查询建表语句 | show create table 表名 | 查询建表语句: show create table student5 |
| 查询表结构 | desc 表名 | 查询表student5 结构： desc student5 |
| 查询当前数据库下的所有表 | show tables | 查询当前数据库下的所有表：show tables |
| 重命名表名 | alter table 旧表名 rename as 新表名 | alter table studeht5 rename as student_info |
| 在已有表中添加新列 | alter table 表名 add 列名 列的类型 | alter table student_info add address varchar (100) |
| 对已有表中的列类型进行修改 | alter table 表名 modify 要修改的列名 修改后列的类型 | alter table student_info modify name varchar(500) |
| 删除已有表中的列 | alter table 表名 drop 要删除的列名 | alter table student_info drop address |
| 删除数据库中的表 | drop table if exists 表名 | drop table if exists student1 |
| 只复制表结构不复制表中的数据 | create table 新表名 like 已有表名 | create table student_info_new like student_info |
| 既复制表结构也复制表中的数据 | create table 新表名 as 查询sql | create table student_info_new as select * in student_info |
1.2 DML(数据操纵语言)
定义: DML语言是对表中的数据进行增删改的操作，DML不要和DDL搞混, DDL主要针对的是表的增删改和表中的对于列的操作。DML是对于表中数据的操作。
1.2.1 insert操作
insert操作主要用于表中数据的添加
--创建员工表
create table emp (
emp_id varchar(20) not null primary key comment '员工id',
emp_name varchar(20) not null comment '员工姓名',
sex char (5) not null comment '性别',
age int not null comment '年龄',
department_name varchar(10) comment '所属部门名称',
salary int not null comment '薪水'
);使用insert语句添加记录
1 插入一行记录语法
-- insert into 表名(列名1,列名2...列名n) values(数据值1,数据值2...数据值n);insert into emp(emp_id,emp_name,sex,age,department_name,salary) values('1','小明','男',26,'工程部',5000);insert into emp(emp_id,emp_name,sex,age,salary) values('2','小红','女',22,1500);-- 如下的insert语句缺失不为空的列值(age)插入 所以无法执行
insert into emp(emp_id,emp_name,sex,age) values('3','小黑','男',30);2 插入多行记录语法-- insert into表名 values(数值1,数值2...数值m) ,(数值1,数值2...数值m) ,(数值1,数值2...数值m);注意:插入的数据顺序要和建表语句中的列顺序一致,如果每列都进行数据插入,那么可以忽略列名的书写insert into emp values
('3','小黑','男',50,'后勤部',4000),
('4','小兰','女',30,'后勤部', 3000);1.2.2 update操作
update语句是对表中的数据进行值的修改
-- update语句语法
-- update 表名 set 要修改值的列名1=要修改的值1,要修改值的列名2=要修改的值2.. .[where 条件语句]-- where语句虽然是可选的, 但在绝大多数情况下都需要添加, 用来定位要修改的具体的记录
update emp set emp_name='大明' where emp_id='1';
update emp set emp_name='大明' where sex='男';1.2.3 delete操作
delete语句是对表中的数据进行删除
-- delete语句语法
-- delete from 表名 [where 条件语句]
delete from emp where emp_id='4';如果需要对整表进行数据全部删除时,推荐使用truncate语句
-- truncate table表名;truncate table emp;
delete from 表名 和 truncate table 表名都能清空表中的数据， 但二者的执行效率不一致delete from 表名 是一行一行的将表中的记录进行删除
truncate table 表名 是先将表进行删除，然后再重建该表1.3 DQL(数据查询语言)
定义: DQL语句是用来查询表中数据的语句 , DQL语句会将查询到的结构返回给客户端,返回的结果是一张虚拟表
-- 最完整的查询语句
select要查询的列名1,要查询的列名2,...要查询的列名3
from 表名
where 条件语句
group by 分组列名
having 筛选条件:分组后可以通过筛选条件进行数据的过滤
order by 排序规则
limit 显示指定的数量
1.3.1 简单查询
--1 查询常量值
select 100;--2 查询字符串
select '数据库';--3 查询函数
select version();--4 查询表中的所有列
select * from emp ;--5 查询表中指定的列
select emp_name ,age from emp where emp_id='1';1.3.2 条件查询
条件查询就是通过 where语句的变化，对于查询进行变化 , where语句会使用到运算符, 常用的运算符: = , != , <> , >= , < , <=
| 运算符 | 说明 |
|---|---|
| between…and… | 数据介于两者之间(全闭合) |
| in | 数据是集合中的一种 |
| is null | 数据为空 |
| not | 取反 |
| and | 和 |
| or | 或 |
1.3.2.1 <>
-- <>代表不等于的意思, !=也是不等于的意思, 推荐使用<>因为性能更好-- 查询员工表中性别不为男的记录
select * from emp where sex <> "男"
1.3.2.2 and
--查询员工表中性别为男所属部门为工程部的记录
select * from emp where sex='男' and department_name='工程部';
1.3.2.3 or
--查询员工表中薪水大于4000或者性别为女的记录
select * from emp where salary>4000 or sex='女';1.3.2.4 in
--查询员工表中员工编号为1,3,5的记录
select * from emp where emp_id in( '1 ','3', '5 ', '10');
1.3.2.5 between…and…
--查询员工表中年龄范围是22-50的记录
select * from emp where age between 22 and 50;
等价于
select * from emp where age>=22 and age<=50;
1.3.2.6 is null
--查询员工表中部门为nu11的记录
select * from emp where department_name is null;
1.3.2.7 not
--查询员工表中部门不为nu11的记录
select * from emp where department_name is not null;--查询员工表中员工编号不为1,3,5的记录
select * from emp where emp_id not in('1','3','5 ');1.4 DCL(数据控制语言)和TCL(事务控制语言)
链接地址:
1.5 子查询
1.5.1 定义
select语句中嵌套select语句，被嵌套的select语句是子查询。从同一个表中先计算出结果, 然后再使用该结果进行数据操f作。
1.5.2子查询的基本使用
- 子查询(内查询)在主查询(外查询)之前一次执行完成
- 子查询的结果集被外查询所使用
- 子查询的注意点:
- 子查询要包裹在括号里
- 子查询要放置在比较条件中的操作符的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
1.5.3子查询的分类
- 按照子查询的结果集返回一条记录还是多条记录,将子查询分为单行子查询和多行子查询
- 按照子查询是否被执行多次，将子查询分为相关子查询和不相关子查询
相关子查询: 如果子查询需要执行多次，即采用循环的方式, 先从外查询开始，每次都进行子查询，然后再将子查询结果集返回给外查询这种执行方式就是相关子查询
不相关子查询:子查询的执行只执行一次便返回给外查询那么这种执行方式就是不相关子查询
1.5.4 单行子查询
单行子查询中使用的操作符:=,>,>=,<,<=,<>
-- 查询员工表中工资大于员工编号为4员工工资的员工信息
select*
from emp
where salary > (select salary from emp where emp_id='4');-- 查询员工表中与小黑同部门的其他员工记录
select
*
from
(select*
from emp
where department_name = (select department_name from emp where emp_name='小黑')) and
where emp_name<>'小黑';select*
from emp
where department_name = (select department_name from emp where emp_name='小黑' ) and emp_name <> '小黑';-- 查询员工表中工资最少的员工记录
select * from emp where salary = (select min(salary) as min_salary from emp);
在having语句中进行子查询
-- 查询员工表中其他部门最低工资小于财务部门最低工资的部门名称和其最低的工资
selectdepartment_name,min(salary)
from emp
where department_name<>'财务部'
group by department_name
having min(salary) < (select min(salary) from emp where department_name='财务部');
1.5.5 多行子查询
多行子查询的操作符: in
-- 查询员工表获取每个部门中薪水最高的员工信息
select*
from emp
where salary in (selectmax(salary) as max_salaryfrom empgroup by department_name
);1.6 连接操作
含义:在数据库操作中，很多场景都需要使用多张表的数据进行关联操作，那么对多张表的关联操作被称为表的连接操作
连接操作有如下几种
- 外连接
- 内连接
- 交叉连接
首先创建测试表
-- 书籍表
create table if not exists book (
book_id varchar(20) not null primary key,
book_name varchar(50) not null,
price double not null,
author_id varchar(20)not null comment'作者id',
press_id varchar(20) not null comment '出版社id' ,
publish_date date comment '出版日期'
);-- 作者表
create table if not exists author(author_id varchar(20) not null primary key comment '作者id ',author_name varchar(50) not null,author_desc varchar(100)
);-- 出版社表create table if not exists press(press_id varchar(20) not null primary key comment '出版社id',press_name varchar(50) not null,press_desc varchar(100)
);插入相关数据
-- 向作者表插入数据
insert into author(author_id ,author_name , author_desc) values
( 'a001','贾平凹','贾平凹(ji5 ping wa),本名贾平娃,1952年2月21日出生于陕西省商洛市丹风县棣花镇,中国当代作家,中国作家协会副主席');insert into author(author_id,author_name, author_desc) values
( 'a002','刘慈欣','刘慈欣,1963年6月出生于北京,祖籍河南省信阳市罗山县,山西阳泉人');insert into author(author_id,author_name, author_desc) values
('a003','鲁迅','鲁迅（1881年9月25日-1936年10月19日)，原名周樟寿，后改名周树人，字豫山，后改字豫才，浙江绍兴人。');-- 向出版社表插入数据
insert into press(press_id,press_name ,press_desc) values('p001','作家出版社','');insert into press(press_id,press_name,press_desc) values('p002','重庆出版社','');insert into press(press_id,press_name ,press_desc) values('poo3','中国友谊出版公司','');insert into press (press_id ,press_name , press_desc) values('p004','湖南文艺出版社','');insert into press (press_id ,press_name ,press_desc) values ('p100','南海出版公司','');-- 向书籍表插入数据
insert into book(book_id,book_name,price, author_id,press_id,publish_date) values
('12516206', '废都', 29, 'a001' , 'p001', '2018-08-01');insert into book(book_id,book_name ,price , author_id,press_id,publish_date) values
('11757834','三体全集',46.5 , 'a002' , 'p002' , ' 2008-01-01');insert into book(book_id,book_name ,price, author_id,press_id,publish_date) values
('12135337','白夜行',29.8, 'a100' , 'p100', '2017-07-01');1.6.1 外连接
外连接可以细分为三种:
- 左外连接
- 右外连接
- 完全外连接
1.6.1.1 左外连接
含义: 无论左表在右表有无匹配都返回左表中的数据缺失的右表数据为null
-- 查询图书表中的记录并展示作者姓名和简介
selectt1.*,t2.author_name,t2.author_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
left join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id;-- 查询图书表中的记录 并展示作者姓名和简介 以及出版社名称和简介
selectt1.*,t2.author_name,t2.author_desc,t3.press_name,t3.press_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
left join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id
left join
(selectpress_id,press_name,press_descfrom press
) t3
on t1.press_id=t3.press_id;1.6.1.2 右外连接
含义:无论右表在左表中是否有匹配 都返回右表的数据 缺失的左表数据为null
-- 查询作者表中的数据 并查询对应的书籍信息
selectt1.*,t2.author_name,t2.author_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
right join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id;
1.6.1.3 完全外连接
含义:无论左右表是否有匹配 都返回两表的数据 缺失的数据为null
--查询图书表中的记录并展示作者姓名和简介
selectt1.*,t2.author_name,t2.author_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
left join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id
union all -- 联合
selectt1.*,t2.author_name,t2.author_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
right join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id;1.6.2 内连接
含义: 内连接相比外连接，内连接只获取两表相匹配的记录，即两表的交集
-- 查询图书表中的记录 并展示作者姓名和简介
selectt1.*,t2.author_name,t2.author_desc
from
(
selectbook_id,book_name,price,author_id,press_id,publish_date
from book) t1
inner join
(
selectauthor_id,author_name,author_descfrom author
) t2
on t1.author_id=t2.author_id;1.6.3 交叉连接
含义:交叉连接就是求两表的简卡尔积
什么是笛卡尔积?
假设有两个集合A=（a,b), B=（0,1,2),两个集合的笛卡尔积是{{a,0},{a,1},{a.2},{b,0},{b,1}，{b,2}}
--书籍表交叉连接作者表
select * from book cross join author;四个默认存在的数据库：
| 默认数据库名称 | 存放内容 |
|---|---|
| information_schema | 存放的数据库服务端的基本信息数据 |
| mysql | 存放的是用户信息，时区信息，表信息，函数，事件，存储过程等 |
| performance_schema | 存放的是数据库的性能信息 |
| sys | 存放的是数据库系统信息 |
注： 以上的四个数据库不要轻易修改或者删除其中的数据，一般的业务操作需要新建数据库，并在新建数据库中完成
2. 什么是数据库对象？
数据库对象是数据库的组成部分，常见的有以下几种：
| 组成 | 描述 |
|---|---|
| 表（table） | 数据库中的表与我们日产使用的表格相似，表是由行（row）和列（column）组成的。列由同类的信息组成，每一列又被称为一个字段，每列的标题称为字段名。行中包括多列，一行数据称为一个或者一条记录，一行记录表示一定有意义的信息的组合。一个数据库表由一条或者多条记录组成，没有记录的表称为空表。每个表通常都有一个主关键字，用于确认数据的唯一。 |
| 索引（index） | 索引是根据指定的数据库表中的列建立起来的顺序。索引提供了快速访问数据的途径，索引可以监督表中的数据，使其索引所指向的列中的数据不重复。 |
| 视图（view） | 视图看上去和表似乎一模一样，也是具有命名的字段和行数据。但实际上视图是一个虚拟的表，在数据库中并不实际存储。视图是由查而数据库中的表而产生的，视图限制了用户能够看到并修改的数据，即视图是用来控制用户对表中数据的访问，并能够简化数据的显示，即通过视图只是示那些需要被展示的数据。 |
3. 如何创建表？
在创建表前需要了解表中列的类型类型如下：
3.1 数值类型
| 数值类型 | 说明 |
|---|---|
| tinyint | 迷你整型使用1个字节存储数据 |
| smallint | 小整型，使用2个字节存储数据 |
| mediumint | 中整型，使用3个字节存储数据 |
| int | 标准整型，使用4个字节存储数据(常用类型) |
| bigint | 大整型，使用8个字节存储数据 |
| float | 单精度浮点类型，使用4个字节存储数据(常用类型) |
| double | 双精度浮点类型，使用8个字节存储数据(常用类型) |
| decimal | 字符串形式的浮点数(主要用来做金融计算的) |
3.2 字符类型
| 字符类型 | 说明 |
|---|---|
| char | char类型是定长字符串（在定义结构的时候已经确认了最终数据存储的长度)定义方式: char(L):L表示length,即可以存储的长度,单位是字符最大长度为255 |
| varchar | varchar类型是变长字符串（在定义结构时，按照最大的存储空间进行分配，但在实际使用中，会根据具体存储的数据来确定）定义方式: varchar(L):L表示Length，理论长度为65536（2的16次方）但会多出1-2个字节来确认实际的存储长度（常用类型) |
| text | 文本字符串，如果存储的字符串数据量非常大通常超过255个字符就应该使用文本字符串 |
3.3 时间日期类型
| 时间日期类型 | 说明 |
|---|---|
| date | 数据库中的格式为YYYY-MM-DD日期类型 |
| time | 数据库中的格式为HH:mm:ss 时间类型 |
| datetime | 数据库中的格式为YYYY-MM-DD HH:mm:ss 日期时间类型(常用类型) |
| timestamp | 时间戳类型计算方式是从1970年0时0分到现在时间中的毫秒数(常用类型) |
| year | 年份类型 |
创建表示例：
（1）最简单的建表语句
##create是建表语句中的关键字
##建表语句语法:##create table表名(
##列名1(字段名1)列1的类型，##列名2(字段名2)列2的类型-##) ;
##not null代表该列无法存储null值create table student1(
id int not null,
name char (20) not null
);（2） 带主键的建表语句
## primary key叫做主键约束，主键约束可以是表中的一列或者是列的组合，
## 被主键约束的列能够在表中进行唯一的标识。这样的一列或者列的组合称为
## 表的主键，通过主键约束可以强制表的实体完整性。
create table student2(
id int not null primary key,
name char (20)
);（3）带复合主键的建表语句
## primary key (列名1，列名2...) 复合主键create table student3 (
name char (20) not null,
age int not null,
primary key (name,age)
);（4）带默认值的建表语句
## default是默认值的关键字create table student4 (
id int not null primary key,
name char (20)not null,
age int not null,
sex char (10) default '男'
);（5）完整版建表语句
## use test;
## auto increment:用于主键的自增序列,从1开始增长。如果将已插入的数据进行删除，
## 再插入新数据时，自增序列不会发生调整。##自增序列有如下的特点
## 1 auto_increment是数据列的一种属性，只适用于整型类型的列
## 2 auto_increment的数据列必须有唯一索引以避免序号重复,即该列为主键或者是主键中的一部分
## 3 auto_increment的数据列必须具有not null的属性
## 4当表被全表删除时，auto increment会从1开始重新编号create table if not exists test.student5 (
id int not null auto_increment comment '学号',
name varchar(20) not null default '匿名' comment '姓名',
password varchar (30) not null default '123456' comment '密码',
sex varchar(5) not null comment '性别',
birthday date default null comment '出生日期',
primary key(id)
)
engine = innodb
default charset = utf8
comment = '学生表'
;
其他拓展：
- 数据库初始化命令
mysqld --initialize --user=root --console
- 设置用户密码
set password = password(‘修改的密码’)
- 登入MySQL数据库
mysql -u用户名 -p密码
如：mysql -uroot -proot123, 或者
mysql -u用户名 -p ，再回车输入密码
- 开启MySQL服务
net start mysql
- 关闭MySQL服务
net stop mysql
- 查看MySQL版本
mysql --version
更多推荐
详解SQL常用语法语句(2023)
发布评论