SQL 语言基本使用指南(DDL、DML、DQL、DCL、约束、多表)

简介: SQL 语言基本使用指南(DDL、DML、DQL、DCL、约束、多表)

SQL语言

概述与作用

概述:结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。

作用:通过Sql语言可以对数据库管理系统中的数据库,表,表中的数据进行增删改查。


sql分类和语法

分类:

  1. DDL(Data Definition Language)数据定义语言

    用来操作数据库和表

    关键字:create,drop,alter等

  2. DML(Data Manipulation Language)数据操作语言

    用来对数据库中表的数据进行增删改

    关键字:insert,delete, update等

  3. DQL(Data Query Language)数据查询语言

    用来对数据库中表的数据进行查询

    关键字:select,from,where等

  4. DCL(Data Control Language)数据控制语言(了解)

    用来定义数据库的访问权限和安全级别,及创建用户。

    关键字:grant, revoke等

  5. TCL(Transaction Control Language) 事务控制语言

    用于控制数据库的事务操作

    关键字: commit,rollback等


sql语法:

  • SQL语句可以单行或多行书写,以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • QL关键字本身不区分大小写。表,列等的名称具有与数据库相关的区分大小写
  • 可以使用 -- 或 # 或 /**/ 的方式完成注释


DDL(数据定义语言)

操作数据库

1-创建数据库

-- 创建数据库。默认:utf-8    
create database 数据库名;
-- 判断是否存在并创建数据库。存在:不创建;不存在:创建。默认:utf-8
create database if not exists 数据库名;
-- 创建数据库指定字符集。一般不用
create database 数据库名 character set 字符集(gbk);

2-查询数据库

-- 查询所有数据库
show databases;

-- 查看某个数据库的定义信息
show create database 数据库名称;

3-删除数据库

-- 删除数据库
drop database 数据库名;

4-数据库的其他操作

-- 查看当前使用的数据库
select database();

-- 切换数据库。注意:如果想要操作哪个库(创建表),就得先进入到该数据库中
use 数据库名;


操作表

注意:如果想要操作哪个库(创建表),就得先进入到该数据库中:use 数据库名;

1-创建表(重要)

-- 如果表存在就删除该表
drop table IF EXISTS 表名;
-- 创建数据库表,同时定义表列属性。    -- ps:表名会有关键字  解决:换名。    
create table 表名(
    字段名1 字段类型1(长度),        -- ps:只有字符串类型需要手动加长度,其它类型都不需要加长度(默认有)
    字段名2 字段类型2(长度)   
    .....
)

-- 创建空表
create table 表名;

-- 示例: 创建一个学生表(id name sex)
create table stu(
    id int,
    name varchar(10),
    sex varchar(2),
    price double,
    time date                    
) 

-- 数据类型
java的数据类型      mysql数据库的数据类型
    int                    int
    float                float
    double                double
    char/string            varchar

    date                datetime  YYYY-MM-DD HH:MM:SS  
                        date      yyyy-MM-dd
                        time      HH:MM:SS

2-查看表

-- 查看某个数据库中的所有表
show tables;

-- 查看表结构
desc 表名称;

-- 查询建表语句
show create table 表名;

-- 快速创建表结构。特点:2张表的结构一样
create table 新表名 like 旧表名;

3-修改表结构

-- 添加表列
alter table 表名 add 列名 类型;
-- 删除表列
alter table 表名 drop 列名;

-- 修改列名
alter table 表名 change 旧名称 新名称 类型;
-- 修改列类型
alter table 表名 modify 列名 新类型

-- 修改表名称
rename table 表名 to 新表名;

4-删除表

-- 直接删除表
drop table 表名;
-- 当指定表名存在时删除该表。常用在初始化数据库建表前
drop table if exists 表名; 


DML(数据操作语言)

1-插入记录

-- 添加部分字段
insert into 表名 (字段名1, 字段名2, ...) values(值1, 值2, ...);

-- 按列定义顺序添加全部字段
insert into 表名 values (值1, 值2, 值3);  

注意事项:

  1. 值与字段必须对应,个数相同,类型相同
  2. 值的数据大小必须在字段的长度范围内
  3. 除了数值类型外,其它的字段类型的值必须使用引号引起。
  4. 如果要插入空值,可以不写字段,或者手动插入null

2-蠕虫复制

-- 将表2中数据复制插入到表1中。前提:表结构得一致
insert into 表名1 select * from 表名2;

3-修改记录

-- 根据条件修改数据。特点:按条件改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 where 字段名=值 ;
-- 不带条件修改数据。特点:表中相应字段全改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 ;

4-删除记录

-- 根据条件删除数据。特点:按条件删
delete from 表名 where 字段名=值; 
-- 不带条件删除数据。 特点:全删
delete from 表名;  
-- truncate删除表记录。
truncate table 表名;  

truncate和delete的区别:

  • delete是将表中的数据一条一条删除。truncate是将整个表摧毁,重新创建一个新的表,新的表结构和原来表结构一模一样
  • delete删除的数据能够找回。truncate删除的数据找不回来了


DQL(数据查询语言)

查询顺序、条件查询、运算查询

select....from...where..group by...having...order by...limit a,b    

简单查询

-- 查询所有列
select * from 表名;

-- 查询指定列
select 字段名1,字段名2... from 表名; 

-- 别名查询
select 字段名1 as 别名, 字段名2 as 别名... from 表名 as 表别名; 

-- 清除重复值
select distinct 字段名 from 表名;

-- 查询结果参与运算。参数运算的字段必须为数值型
select 列名1 + 固定值 from 表名;

条件查询:

select * from 表名 where 条件(各种运算符); 

运算查询:

>   大于 
<   小于 
<=  小于等于 
>=  大于等于 
=   等于 
<> 、 !=  不等于

and(&&)     多个条件同时满足 
or(||)         多个条件其中一个满足 
not(!)         不满足


范围查询、模糊查询、排序查询

范围查询:

between 值1 and 值2
in(值1, 值2, 值3, ...)

模糊查询 like

like
    %  : 模糊多位 通配符
    _  : 模糊一位 通配符

is null
is not null

排序查询 order by 字段

order by 字段名 [desc | asc] , 字段名 [desc | asc] ;        -- asc(默认): 升序,desc: 降序


聚合函数查询

count() : 计数

sum(字段) : 求和
max(字段) : 求最大值
min(字段) : 求最小值
avg(字段) : 求平均值

ifnull(字段, 备用值) : 第一个参数若不为NULL,则返回该值;若为NULL,则返回备用值,


count(1)、count(*)与count(列名)的区别

执行效果:

  1. count(*) 包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
  2. count(1) 包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
  3. count(列名) 只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。

执行效率:

  1. 如果有主键,则 count(主键) 的执行效率是最优的
  2. 列名为主键,count(列名) 会比 count(1) 快
  3. 列名不为主键,count(1) 会比 count(列名) 快
  4. 如果表多个列并且有主键或索引,则 count(*) 系统会自动优化走主键或者索引
  5. 如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(* )


分组查询、分页查询

分组查询

group by 分组字段 [having 条件]

-- 示例:查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示总人数大于2的数据
select sex, COUNT(*) from student where age>25 group by sex having COUNT(*)>2;

having与where的区别

  • having是在分组后对数据进行过滤;where是在分组前对数据进行过滤
  • having后面可以使用聚合函数;where后面不可以使用聚合函数


分页查询

limit 跳过条数,查询条数;


DCL(数据控制语言)

-- 创建用户
-- 命令格式:
CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码';    -- 默认没有任何权限 什么都做不了
-- 示例:
CREATE USER 'tom'@'localhost' IDENTIFIED BY '123';

-- 给用户分配权限
-- 命令格式:
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
-- 示例:
GRANT ALL ON db3.* TO 'tom'@'localhost';

-- 查看权限
-- 命令格式:
SHOW GRANTS FOR '用户名'@'主机名';
-- 示例:
SHOW GRANTS FOR 'tom'@'localhost'

-- 撤销权限
-- 命令格式:
REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';
-- 示例:
REVOKE ALL ON db3.* FROM 'tom'@'localhost';

-- 删除用户
-- 命令格式:
DROP USER '用户名'@'主机名';
-- 示例:
DROP USER 'tom'@'localhost';

-- 给root用户修改密码
-- 特点:不需要登录 直接操作命令即可
-- 格式:
mysqladmin -uroot -p password 新密码

-- 给普通用户修改密码
-- 特点:需要root用户先登录 再使用命令去修改普通用户的密码
-- 格式:
set password for '用户名'@'主机名' = password('新密码');
-- 示例:
set password for 'aaa'@'localhost' = password('root');


数据库约束

约束:对表中的数据可以进行进一步的限制,来保证数据的唯一性,正确性和完整性

约束种类:

  • PRIMARY KEY :主键约束。代表该字段的数据不能为空且不可重复
  • NOT NULL :非空。代表该字段的数据不能为空
  • UNIQUE :唯一。代表该字段的数据不能重复


主键约束:primary key

一个表中都得需要主键约束,用来标注一条记录的唯一性

特征:

  • 主键字段值唯一不可重复
  • 主键字段值不能包含NULL值
  • 一个表中只能有一个主键,但主键可以是多个字段 (联合主键)

MySQL实现方式

添加主键

  • 方式一:在创建表时,添加约束

    格式:

    create table 表名 ( 
        字段名称1 字段类型 primary key,     
        字段名称2 字段类型 约束 
    );
  • 方式二:在创建表时,结尾内添加约束

    格式1:单一主键

    create table 表名 (
        字段名称1 字段类型,     
        字段名称2 字段类型,     
        primary key(字段名称1)  
    );

    格式2:联合主键

    create table 表名 (
        字段名称1 字段类型,     
        字段名称2 字段类型,     
        primary key(字段名称1,字段名称2)  
    );

删除主键

alter table 表名 drop PRIMARY KEY;

主键自增

auto_increment        -- 默认地AUTO_INCREMENT 的开始值是1

-- 修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;


唯一约束:unique

特征:被修饰的字段唯一,不可重复

注意:一个表中可以有多个被unique修饰的字段,但对null不起作用

实现方式

-- 创建表时在字段后添加 
create table 表名 ( 
    字段名称1 字段类型 unique, 
    字段名称2 字段类型 约束 
);


非空约束:not null

特征:被修饰的字段不可为空

实现方式:直接在字段后面添加即可

create table 表名 ( 
    字段名称1 字段类型 not null default '默认值',             -- default:默认值
    字段名称2 字段类型 unique not null default '默认值',     -- 一个字段上可以同时出现唯一约束和非空约束的
    字段名称3 字段类型 约束 
);


多表

多表的设计与实现

有三种:

  • 一对一

    实现:

    1、让双方的主键作为外键一一对应

    2 、在任意一方创建一个字段当成是外键指向另一方的主键,但是这个外键必须唯一

  • 一对多

    称一的一方为主表,称多的一方为从表

    建立原则:只要是一对多,就在从表中(多的一方)创建一个字段为外键,然后让这个外键指向主表的(一的一方)主键

  • 多对多

    建立原则:需要在外部创建一张中间表。这个中间表至少需要2个字段,然后让这2个字段分别作为外键只向各自表的主键


多表约束(外键约束)

外键约束的作用:能够保证数据的完整性和有效性

特征:从表中如果关联了主表的数据则强制主表的数据不能删除,保证数据的完整性和有效性


外键的性能问题:

  • 数据库需要维护外键的内部管理;
  • 外键等于把数据的一致性事务实现,全部交给数据库服务器完成
  • 有了外键,当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,而不得不消耗资源
  • 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况


外键的使用策略:

  • 在大型系统中(性能要求不高,安全要求高),使用外键;

    在大型系统中(性能要求高,安全自己控制),不用外键;

    小系统随便,一般不用外键。

  • 用外键要适当,不能过分追求
  • 不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后每个应用通过这个层来访问数据库。


添加外键约束

  • 方式1:在已有表上添加外键约束

    给从表的外键字段添加外键约束

    alter table 从表 add constraint [外键别名] foreign key(外键字段) references 主表名称(主键字段)
  • 方式2:可视化图形方式

    在架构设计器中,直接指定外键拖向主键即可,简单方便快捷

删除外键

  • 方式一:

    ALTER TABLE 从表 drop foreign key 外键名称;    
  • 方式二:在架构设计器中,选中指向关系右键删除即可


多表查询

多表查询:从多张表中获取到有关系的数据。比如:查询分类信息以及分类下的所有商品

多表查询的语法分类:

  • 内连接查询:只查询多张表之间有关系的数据
  • 外连接查询
  • 子查询
  • 交叉查询:查询的是2张表的乘积(笛卡尔积),有关系的数据和没关系的数据都查出来了


内连接查询

特点:可以使用内连接去查询2张表之间有关系的数据

  • 隐式内连接:

    -- 语法格式:
    select * from 表1 表1别名,表2 where 关联条件 and 筛选条件;     -- ps:可以使用表别名简化书写
  • 显示内连接

    -- 语法格式:
    select * from 表1 [inner] join 表2  on 关联条件 where 筛选条件 and 筛选条件; 
        -- on: 后跟的是关联条件
        -- where: 后跟的是对结果的筛选条件


外连接查询

  • 左外连接:以 join 左边的表为主,查询出来的是2张表之间有关系的数据以及左边表所有的数据。

    如果右表中没有满足条件的对应数据,则填充 null

    -- 语法格式:
    select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;
  • 右外连接:以 join 右边的表为主,查询出来的是2张表之间有关系的数据以及右边表所有的数据

    如果左表中没有满足条件的对应数据,则填充null

    -- 语法格式:
    select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;


子查询

语法格式:

-- 子查询作为where条件。适用于单列单值或者单列多值
select * from 表名 where (子查询结果);
-- 子查询作为临时表。适用于多列多值
select * from 子查询结果 where 条件;
-- 子查询作为结果集字段
select *,(子查询结果) from 表名 where 条件;

示例:

  • 子查询作为where条件 示例:查询工资最高的员工是谁(结果是单列单值)

    -- 先查询最高的工资
    select MAX(salary) from emp;
    -- 再查询最高工资的员工
    select name from emp where salary=(select MAX(salary) from emp);
  • 子查询作为where条件 示例:查询工资大于5000的员工部门ID,来自于哪些部门(结果是单列多值)

    -- 先查询工资大于5000的员工部门ID
    select dept_id from emp where salary>5000;
    -- 根据部门id获取部门名称
    select name from dept where id in (select dept_id from emp where salary>5000);
  • 子查询作为临时表 示例:查询出2011-01-01以后入职的员工信息,包括部门名称(结果是多列多值)

    select l.*,d.name from dept d,(select * from emp where join_date>'2011-01-01') l
    where d.id=l.dept_id;


交叉连接

select * from 表1,表2;

会产生2张表的乘积数据,简称笛卡尔积数据,有关系的数据和没关系的数据都查出来了


相关文章
|
2月前
|
SQL 关系型数据库 数据库
|
4月前
|
SQL 数据库
零基础学习数据库SQL语句之操作表中数据的DML语句
零基础学习数据库SQL语句之操作表中数据的DML语句
44 0
零基础学习数据库SQL语句之操作表中数据的DML语句
|
4月前
|
SQL 流计算
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(4)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
|
4月前
|
SQL Java 流计算
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】(3)
Flink(十三)【Flink SQL(上)SqlClient、DDL、查询】
|
4月前
|
SQL 关系型数据库 MySQL
零基础学习数据库SQL语句之定义数据库对象的DDL语句
零基础学习数据库SQL语句之定义数据库对象的DDL语句
41 0
|
8天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
68 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
48 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
232 0
下一篇
无影云桌面