老学员干货分享|数据库命令总结-IT知识库

老学员干货分享|数据库命令总结-IT知识库

咨询热线

0731-82115773

精选文章 > IT知识库 > 老学员干货分享|数据库命令总结

老学员干货分享|数据库命令总结

时间:2020-11-13  发布:新梦想培训  来源:新梦想培训

表的操作

/* 表的操作 */ ------------------

-- 创建表

    create [temporary] table[ if not exists] [库名.]表名 ( 表的结构定义 )[ 表选项]

        每个字段必须有数据类型

        最后一个字段后不能有逗号

        temporary 临时表,会话结束时表自动消失

        对于字段的定义:

            字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY]KEY] [COMMENT 'string']

-- 表选项

    -- 字符集

        CHARSET = charset_name

        如果表没有设定,则使用数据库字符集

    -- 存储引擎

        ENGINE = engine_name    

        表在管理数据时采用的不同的数据结构,结构不同会导致处理方式、提供的特性操作等不同

        常见的引擎:InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive

        不同的引擎在保存表的结构和数据时采用不同的方式

        MyISAM表文件含义:.frm表定义,.MYD表数据,.MYI表索引

        InnoDB表文件含义:.frm表定义,表空间数据和日志文件

        SHOW ENGINES -- 显示存储引擎的状态信息

        SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息

    -- 数据文件目录

        DATA DIRECTORY = '目录'

    -- 索引文件目录

        INDEX DIRECTORY = '目录'

    -- 表注释

        COMMENT = 'string'

    -- 分区选项

        PARTITION BY ... (详细见手册)

-- 查看所有表

    SHOW TABLES[ LIKE 'pattern']

    SHOW TABLES FROM 表名

-- 查看表机构

    SHOW CREATE TABLE 表名    (信息更详细)

    DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']

    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

-- 修改表

    -- 修改表本身的选项

        ALTER TABLE 表名 表的选项

        EG:    ALTER TABLE 表名 ENGINE=MYISAM;

    -- 对表进行重命名

        RENAME TABLE 原表名 TO 新表名

        RENAME TABLE 原表名 TO 库名.表名    (可将表移动到另一个数据库)

        -- RENAME可以交换两个表名

    -- 修改表的字段机构

        ALTER TABLE 表名 操作名

        -- 操作名

            ADD[ COLUMN] 字段名        -- 增加字段

                AFTER 字段名            -- 表示增加在该字段名后面

                FIRST                -- 表示增加在第一个

            ADD PRIMARY KEY(字段名)    -- 创建主键

            ADD UNIQUE [索引名] (字段名)-- 创建唯一索引

            ADD INDEX [索引名] (字段名)    -- 创建普通索引

            ADD 

            DROP[ COLUMN] 字段名        -- 删除字段

            MODIFY[ COLUMN] 字段名 字段属性        -- 支持对字段属性进行修改,不能修改字段名(所有原有属性也需写上)

            CHANGE[ COLUMN] 原字段名 新字段名 字段属性        -- 支持对字段名修改

            DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)

            DROP INDEX 索引名    -- 删除索引

            DROP FOREIGN KEY 外键    -- 删除外键

 -- 删除表


    DROP TABLE[ IF EXISTS] 表名 ...

-- 清空表数据

    TRUNCATE [TABLE] 表名

-- 复制表结构

    CREATE TABLE 表名 LIKE 要复制的表名

-- 复制表结构和数据

    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

-- 检查表是否有错误

    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

-- 优化表

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- 修复表

    REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

-- 分析表

    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...


数据操作


-- 增

    INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]

        -- 如果要插入的值列表包含所有字段并且顺序一致,则可以省略字段列表。

        -- 可同时插入多条数据记录!

        REPLACE 与 INSERT 完全一样,可互换。

    INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]

-- 查

    SELECT 字段列表 FROM 表名[ 其他子句]

        -- 可来自多个表的多个字段

        -- 其他子句可以不使用

        -- 字段列表可以用*代替,表示所有字段

-- 删

    DELETE FROM 表名[ 删除条件子句]

        没有条件子句,则会删除全部

-- 改

    UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]


Select

select [all|distinct] select_expr from -> where -> group by [合计函数] -> having -> order by -> limit

 

a. select_expr

    -- 可以用 * 表示所有字段。

        select * from tb;

    -- 可以使用表达式(计算公式、函数调用、字段也是个表达式)

        select stu, 29+25, now() from tb;

    -- 可以为每个列使用别名。适用于简化列标识,避免多个列标识符重复。

        - 使用 as 关键字,也可省略 as.

        select stu+10 as add10 from tb;

 

b. from 子句

    用于标识查询来源。

    -- 可以为表起别名。使用as关键字。

        select * from tb1 as tt, tb2 as bb;

    -- from子句后,可以同时出现多个表。

        -- 多个表会横向叠加到一起,而数据会形成一个笛卡尔积。

        select * from tb1, tb2;

 

c. where 子句

    -- 从from获得的数据源中进行筛选。

    -- 整型1表示真,0表示假。

    -- 表达式由运算符和运算数组成。

        -- 运算数:变量(字段)、值、函数返回值

        -- 运算符:

            =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,

            in (not) null, (not) like, (not) in, (not)between and, is (not), and, or, not, xor

            is/is not 加上ture/false/unknown,检验某个值的真假

            <=>与<>功能相同,<=>可用于null比较

 

d. group by 子句, 分组子句

    group by 字段/别名 [排序方式]

    分组后会进行排序。升序:ASC,降序:DESC

    

    以下[合计函数]需配合 group by 使用:

    count 返回不同的非NULL值数目    count(*)、count(字段)

    sum 求和

    max 求最大值

    min 求最小值

    avg 求平均值

    group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。

 

e. having 子句,条件子句

    与 where 功能、用法相同,执行时机不同。

    where 在开始时执行检测数据,对原数据进行过滤。

    having 对筛选出的结果再次进行过滤。

    having 字段必须是查询出来的,where 字段必须是数据表存在的。

    where 不可以使用字段的别名,having 可以。因为执行WHERE代码时,可能尚未确定列值。

    where 不可以使用合计函数。一般需用合计函数才会用 having

    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。

 

f. order by 子句,排序子句

    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...

    升序:ASC,降序:DESC

    支持多个字段的排序。

 

g. limit 子句,限制结果数量子句

    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合,按照记录出现的顺序,索引从0开始。

    limit 起始位置, 获取条数

    省略第一个参数,表示从索引0开始。limit 获取条数

 

h. distinct, all 选项

    distinct 去除重复记录

    默认为 all, 全部记录


视图

什么是视图:

    视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

    视图具有表结构文件,但不存在数据文件。

    对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。

    视图是存储在数据库中的查询的sql语句,它主要出于两种原因:安全原因,视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。

 

-- 创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)] AS select_statement

    - 视图名必须唯一,同时不能与表重名。

    - 视图可以使用select语句查询到的列名,也可以自己指定相应的列名。

    - 可以指定视图执行的算法,通过ALGORITHM指定。

    - column_list如果存在,则数目必须等于SELECT语句检索的列数

 

-- 查看结构

    SHOW CREATE VIEW view_name

 

-- 删除视图

    - 删除视图后,数据依然存在。

    - 可同时删除多个视图。

    DROP VIEW [IF EXISTS] view_name ...

 

-- 修改视图结构

    - 一般不修改视图,因为不是所有的更新视图都会映射到表上。

    ALTER VIEW view_name [(column_list)] AS select_statement

 

-- 视图作用

    1. 简化业务逻辑

    2. 对客户端隐藏真实的表结构

 

-- 视图算法(ALGORITHM)

    MERGE        合并

        将视图的查询语句,与外部查询需要先合并再执行!

    TEMPTABLE    临时表

        将视图执行完毕后,形成临时表,再做外层查询!

    UNDEFINED    未定义(默认),指的是MySQL自主去选择相应的算法。


锁表

表锁定只用于防止其它客户端进行不正当地读取和写入

MyISAM 支持表锁,InnoDB 支持行锁

-- 锁定

    LOCK TABLES tbl_name [AS alias]

-- 解锁

    UNLOCK TABLES

 

--// 存储函数,自定义函数 ----------

-- 新建

    CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型

        函数体

 

    - 函数名,应该合法的标识符,并且不应该与已有的关键字冲突。

    - 一个函数应该属于某个数据库,可以使用db_name.funciton_name的形式执行当前函数所属数据库,否则为当前数据库。

    - 参数部分,由"参数名"和"参数类型"组成。多个参数用逗号隔开。

    - 函数体由多条可用的mysql语句,流程控制,变量声明等语句构成。

    - 多条语句应该使用 begin...end 语句块包含。

    - 一定要有 return 返回值语句。

 

-- 删除

    DROP FUNCTION [IF EXISTS] function_name;

 

-- 查看

    SHOW FUNCTION STATUS LIKE 'partten'

    SHOW CREATE FUNCTION function_name;

 

-- 修改

    ALTER FUNCTION function_name 函数选项


存储过程,自定义功能

-- 定义

存储存储过程 是一段代码(过程),存储在数据库中的sql组成。

一个存储过程通常用于完成一段业务逻辑,例如报名,交班费,订单入库等。

而一个函数通常专注与某个功能,视为其他程序服务的,需要在其他语句中调用函数才可以,而存储过程不能被其他调用,是自己执行 通过call执行。

 

-- 创建

CREATE PROCEDURE sp_name (参数列表)

    过程体

 

参数列表:不同于函数的参数列表,需要指明参数类型

IN,表示输入型

OUT,表示输出型

INOUT,表示混合型

 

注意,没有返回值。


/* 存储过程 */ ------------------

存储过程是一段可执行性代码的集合。相比函数,更偏向于业务逻辑。

调用:CALL 过程名

-- 注意

- 没有返回值。

- 只能单独调用,不可夹杂在其他语句中

 

-- 参数

IN|OUT|INOUT 参数名 数据类型

IN        输入:在调用过程中,将数据输入到过程体内部的参数

OUT        输出:在调用过程中,将过程体处理完的结果返回到客户端

INOUT    输入输出:既可输入,也可输出

 

-- 语法

CREATE PROCEDURE 过程名 (参数列表)

BEGIN

    过程体

END


任何值得到达的地方,都没有捷径。

新梦想11月25全日制软测班正在火热报名中

名额有限,火速报名!

详情咨询:QQ2714875285

报名热线:15874117912

免费试听:新梦想软件测试公众号菜单栏“免费学”处。

报名地点:湖南省长沙市天心区五一大道五一新干线。


大咖面授 免费试听

老学员干货分享|数据库命令总结-IT知识库
技术支持 英铭科技