sql server sql 修改视图 sql如何修改视图数据

Sql知识总结

• 一、基础操作

• 1.库的CRUD

• ①库的创建
• ②.库的修改
• ③.库的查询
• ④.库的删除

• 2.表的CRUD

• ①表的新建
• ②表的修改:表属性、列、索引
• ③表的查看
• ④表的删除

• 3.视图的CRUD

• ①创建视图标准写法
• ②查询视图
• ③视图的更新
• ④删除视图

一、基础操作

1.库的CRUD

①库的创建

创建库的标准语法如下：

CREATE {DATABASE|SCHEMA} if not EXISTS supplier
	DEFAULT CHARACTER SET [=] utf8mb4
| [DEFAULT] COLLATE [=]collation_name
| DEFAULT ENCRYPTION [=] {'Y'| 'N'}

上面是官方文档的标准写法，稍作解读：大括号带|表示使用大括号内任何一个关键字都可以但是也只能使用其中一个关键字，不使用大括号的|表示这些关键字可以并列存在也可以只存在一个。中括号则表示可有可无的信息。
创建数据库举例：

DROP DATABASE if EXISTS supplier;
CREATE SCHEMA if not EXISTS supplier
	DEFAULT CHARACTER SET = utf8mb4
	DEFAULT COLLATE = utf8mb4_0900_ai_ci 
	DEFAULT ENCRYPTION = 'N'

建库语句解读：
其实建库最多的参数也就这些了，character 是声明字符集的，msyql8.0以后默认是utf8mb4，collate是声明排序规则的，排序规则与字符集一般有对应关系，某一个字符集被声明的情况下，只能声明collate为该字符集允许的排序规则(光是utf8mb4对应的排序规则就有几十种选择)，在mysql8.0以后默认的排序规则就是utf8mb4_0900_ai_ci了，那这个排序规则有什么用呢？平时的用处可能更多的体现在char、varchar、text等类型字段的排序上，其实还有另外一个重要的点，那就是若是你在做数据迁移时，若是两边库的排序规则不一样，那么你是无法迁移成功的。至于ENCRYPTION ，则是用于对数据库进行加密的，一般不会这么用，这么用会使得整库数据进行加密然后数据的操作效率大大降低，这对于数据量大的系统是无法忍受的。
注：utf8mb4_0900_ai_ci 是mysql8.0以后才增加的排序规则，5.7系列是不认识的

DROP DATABASE if EXISTS supplier; -- 该库若是存在则先删除改库，一般无需这么写
CREATE SCHEMA if not EXISTS supplier -- SCHEMA 、DATABASE 都是建库的意思没有区别
	DEFAULT CHARACTER SET = utf8mb4  -- 为整库设置字符集，mysql8.0开始，默认值就是utf8mb4
	DEFAULT COLLATE = utf8mb4_0900_ai_ci  -- 为整库设置排序规则，msyql8.0开始，字符集是utf8mb4情况下，排序规则默认就是utf8mb4_0900_ai_ci (排序规则与字符集有对应关系)
	DEFAULT ENCRYPTION = 'N' --

②.库的修改

下面是修改库的标准语法：

ALTER {DATABASE | SCHEMA} [ db_name]
	[DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=]collation_name
  | DEFAULT ENCRYPTION [=] {'Y'| 'N'}

上面sql应该无需多说什么了，只是将create修改成了alter，和创建库的操作没啥区别都是这几个属性，唯一可能让人诧异的是db_name竟然是可以省略的，其实若是省略了db_name，那么这条alter语句就会更改默认的数据库。那默认的是数据是什么呢，就是当前执行sql时，sql所在得库。
库的修改的示例sql如下：

alter DATABASE 
default character set = utf8mb4 
default collate = utfmb4_0900_ai_ci 
default encryption = 'Y' -- 这个是一般还是N

③.库的查询

一些库层级的查询操作：

show databases; -- 查询全部库的名称(权限允许范围内的)
show create database mysql; -- 查询库的创建语句，这里可以看到库创建的关键信息
show character set; -- 查询mysql支持的所有字符集（附带有每个字符集默认的排序规则）
show collation; -- 查询msyql支持的所有排序规则（附带有排序规则与字符集的对应关系）
use supplier1; -- 进入supplier1数据库

④.库的删除

标准写法如下：

DROP {DATABASE | SCHEMA} [IF EXISTS]db_name

删除示例如下：

DROP DATABASE IF EXISTS supplier1; -- 若是库supplier1存在则删除

2.表的CRUD

这块是比较核心的内容，因为我们的数据都是要存储在表里的，所以这块的设计尤为重要，可以说是重中之重了。

①表的新建

官方写法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [（create_definition，...）]
    [ table_options]
    [ partition_options]
    [IGNORE | 更换]
    [如] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    {LIKE old_tbl_name| （LIKE old_tbl_name）}

上面是两个官方写法，没啥好说的，一个是全是新建的表，一个是基于现有表建立新表（复制原表的所有属性），其中带有temporary的表只会在当前会话中存在。上面的sql可以分为以下几部分：

表名:声明表名
临时表:即使用TEMPORARY关键字创建表，这种表只会存在于当前会话
表克隆和复制：使用like old_table 即可，会复制原表的所有属性和索引
列数据类型和属性：
索引，外键和CHECK约束
表格选项
表分区

下面给出一个表建立的终极写法（包含了尽可能多的属性），以作备忘：

drop table if exists supplier_info;
create  table if not EXISTS supplier_info 
(
column1 VARCHAR(20) not null  comment '列1',
column2 varchar(20) default null comment '列2',
column3 tinyint  AUTO_INCREMENT  comment '列3', -- AUTO_INCREMENT 只能使用在整型和浮点型数据上,且innodb中每个表只有一个列可以是自增的，MyISAM可以有多列
column4 blob not null comment '列4',
column5 tinytext comment '列5',
column6 json not null comment '列6',
column7 varchar(20) not null comment '列7',
column8 integer unsigned not null comment '列8', -- 无符号整数
column9 bigint signed not null comment '列9',-- 有符号整数
primary key(column1) comment '主键索引',
unique key idx_col2(column2),
unique index idx_col3(column3) using btree, -- 索引声明既可以使用key也可以使用index
index idx_col5(column4(10)) using btree, -- BLOB/text 建立索引必须声明索引长度
key idx_col6(column5(10)) using hash, -- 这里显示声明使用hash索引不生效，还是使用的btree
index idx_col7(column7) using hash
-- key(column6) json 列不支持索引
) engine = InnoDB  character set = utf8mb4 collate = utf8mb4_0900_ai_ci comment '测试创建表' AUTO_INCREMENT = 2;  -- 为AUTO_INCREMENT设置起始行

show create table supplier_info;

上面是是一个建表的较为全面的语句，写在这里方便查看，以作备忘吧，下面是建表的两种不常用的sql，乍一看两种建表没啥区别，其实区别还是很大的，使用select只是建立在新表建立相同的列，新表不具有原表的索引等。使用like则是完全copy一份原表出来了(注意copy的只是表结构，like建表只有表结构，没有数据，与之相反使用select建表，是有数据的，但是索引什么的并不会带到新表)。
1）使用select 建表

create table if not exists supplier_select 
select *from supplier_info;

2）使用like 建表

create table if not exists supplier_like 
like supplier_info ;

②表的修改:表属性、列、索引

表的修改——————>>>>>> 表属性的修改（增、删不存在）

-- 改表名
alter table supplier_info_re rename supplier_info;
-- 改表属性 （列举两个，其他类似）
alter table supplier_info character set = utf8mb4;
alter table supplier_info collate = utf8mb4_0900_ai_ci;

表的修改——————>>>>>> 表的列的增删改
alter table T change = alter table T rename + alter talbe T modify
即：可修改字段的名称和属性 = 可修改子段名 + 可修改字段属性

-- 增加列
alter table supplier_info add column column10 varchar(20)  not null comment '列10';
-- 修改列,修改列名
alter table supplier_info rename column column10 to column10_1;
alter table supplier_info change column10_1 column10 varchar(21) null comment '列10';
-- 修改列,修改列属性
alter table supplier_info modify column10 bigint not null comment '列10';
alter table supplier_info change column10 column10 varchar(20) not null comment '列10';
-- 删除列
alter table supplier_info drop  column10;

表的修改——————>>>>>> 表索引的增删改

-- 增加索引
alter table supplier_info add primary key(column1) using btree comment '主键索引';
alter table supplier_info add unique key idx_uni_col12(column12) using hash comment '唯一索引';
alter table supplier_info add index idx_col11(column11) using btree comment '普通索引';
-- 修改索引
alter table supplier_info rename index idx_col11 to idx_col11_1;
-- 删除索引
alter table supplier_info drop primary key ;
alter table supplier_info drop index idx_col11_1;
alter table supplier_info drop index idx_uni_col12;

其他还有约束的增删改等不常用的场景，这里不做列举了

③表的查看

-- 查看所有表
show tables;
-- 查看表的创建语句
show create table supplier_info;
-- 查看表的所有索引
show index from supplier_info from supplier;
show index in supplier_info from supplier;
-- 查看表的所有列
show columns from supplier_info like 'column%';
-- 查看表的各种属性
show table status from supplier like '%supplier_info%';
show table status from supplier like '%supplier_info_like%';
-- 表模糊查找
show tables from supplier like '%supplier%';

④表的删除

drop table if exists supplier_select;

3.视图的CRUD

视图在笔者看来用处不大，视图是虚拟的，并不真实存在，我们每次使用视图其实是相当于执行了创建视图的sql，所以使用视图对于sql其实是没有优化的。感兴趣的同学可以explain … show warings 看下查询视图的真正操作，你就会发现查询视图其实就是执行的创建视图的原始sql。

①创建视图标准写法

CREATE 
[or REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | NOT Temptable}]
[DEFINER = user]
[SQL SECURITY {DEFINER | INVOKER}]
 VIEW viewName AS 
 select_statement

下面是简单的使用

create or replace definer = 'root' view  v_supplier_info as  -- 限制了操作视图的用户
select column1,column2,column3,column4 from supplier_info;

②查询视图

和普通表的使用一模一样

select *from v_supplier_info;

③视图的更新

视图的更新其实就是更新列的内容，所以官方写法和创建视图没有区别，只是将create改成了alter

alter view v_supplier_info as 
select column6,column7,CONCAT(column6,column7)as col from supplier_info;

④删除视图

drop view if exists v_supplier_info;