数据库考点之SQL语句（数据库、表、索引、视图）

最新推荐文章于 2024-05-13 22:13:23 发布

guangod

最新推荐文章于 2024-05-13 22:13:23 发布

阅读量1.7k

点赞数

分类专栏：自学考试

本文链接：https://blog.csdn.net/guangod/article/details/108655426

版权

自学考试专栏收录该内容

223 篇文章 82 订阅

订阅专栏

如题：2019年10月

分析：

从答案和题干，删除表，首先想到的就是drop table.

alter table：更改表的结构。

delete table:没有此用法，sql中只有delete from tbl_name;并且也只是删除表中的数据。

revoke table:也没有此用法，revoke是删除某用户权限。

从这里也可以看出，虽然SQL命令从书中看，有的非常长，不好记忆！但其实是“SQL都是为解决具体问题而产生的，这个应用的情形，一定要脑补出来，才能记得住”。

以后遇到了这样的语法题，一定要深挖些“为解决何种问题”。这样每遇到一个解释一个，时间长了，就会熟记SQL。反正，就那么多命令。

详见P148页，答案:选B,如下：

注：除了数据库的设计思想外，就是语言了。掌握SQL也是很实用的。

什么是SQL呢？

Structured Query Language的缩写，意思是结构化查询语言，是一种在数据库管理系统（Relational ：关系 Database Management System, RDBMS）中查询数据，或通过RDBMS对数据库中的数据进行更改的语言。

常见的数据库：

Oracle Database：甲骨文公司的RDBMS
SQL Server ：微软公司的RDBMS
DB2：IBM 公司的RDBMS
PostgreSQL：开源的RDBMS
MySQL ：开源的RDBMS

SQL语句执行过程：

用户在客户端通过SQL语言，将需要的数据和对数据进行的操作的请求发送给RDBMS，RDBMS 根据该语句的内容返回所请求的数据，或者对存储在数据库中的数据进行更新。

如同linux的shell命令一样中间是隔了一层操作系统的，正是由于Rdbms的存在才使得数据实现集中高效的处理和操作，其实是将数据与操作分离，还是计算机分层的思想。

根据对RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类。

●DDL（Data Definition Language，数据定义语言）

用来定义和管理数据对象，包括：数据库、数据表、函数、视图、索引、触发器等。DDL 包含以下几种指令。

CREATE：创建数据库和表等对象

DROP：删除数据库和表等对象

ALTER：修改数据库和表等对象的结构

●DML（Data Manipulation Language，数据操纵语言）

用来操作数据库对象中所包含的数据，增、删、改、查。DML 包含以下几种指令。

SELECT：查询表中的数据，这部分非常重要，也是使用最为频繁的语句，详见：《SQL考点之SQL查询》

INSERT：向表中插入新数据

UPDATE：更新表中的数据

DELETE：删除表中的数据

●DCL（Data Control Language，数据控制语言）

用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

COMMIT：确认对数据库中的数据进行的变更

ROLLBACK：取消对数据库中的数据进行的变更

GRANT：赋予用户操作权限

REVOKE：取消用户的操作权限

Ⅰ. DDL（Data Definition Language，数据定义语言）

1、创建数据库(CREATE)

语法：

如：

CREATE DATABASE shop;

切换数据库语法：use db_name;

修改数据库：alter {database | schema}[db_name];

如：

alter database mysql_test
default character set gb2312
default collate gb2312_chinese_ci;

删除数据库：drop {database | schema}[if exists]db_name;

查看数据库：show {database | schema}[like 'pattern' | where expt];

注：like 关键字用于匹配指定的数据库名称，where从句用于指定数据库名称查询范围的条件。

2、创建表(CREATE)

基本语法：

注：temporary:临时，表示创建的是临时表，不被永久存储。当断开数据库时，会自动的删除。

如：

CREATE TABLE Product
(product_id     CHAR(4)      NOT NULL,
 product_name   VARCHAR(100) NOT NULL,
 product_type   VARCHAR(32)  NOT NULL,
 sale_price     INTEGER      ,
 purchase_price INTEGER      ,
 regist_date    DATE         ,
 PRIMARY KEY (product_id));

每一列的数据类型（后述）是必须要指定的，数据类型包括：

INTEGER 整数型
NUMERIC ( 全体位数, 小数位数)
CHAR 定长字符串
VARCHAR 可变长字符串
DATE 日期型

删除表(DROP)

语法：

注：restrict:限制 cascade:级联

DROP TABLE Product;

表定义的更新(ALTER)

在表中增加一列(ADD COLUMN)

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
#比如书上的例子
alter table mysql_test.customers
add column cast_city char(10) not null default 'wuhan' after cust_sex;

也可以使用 add primary key、add foreign key 、add index为表添加主键，外键，索引。

同时修改列名称或数据类型（change [column])

将数据库mysql中表customers的cust_sex重命名为sex,且将数据类型更改为char(1),允许其为NULL,默认值为‘M’。

alter table mysql_test.customers
change column cust_sex sex char(1) NULL default 'M';

修改列的数据类型（modify [column]),可通过 ‘first' 'after'修改指定列在表中的位置。

将cust_name数据类型由之前char(50)改成char(20),并将此列设置为第一列

alter table mysql.custormers
modify column cust_name char(20) first;

修改或删除列的默认值添加子句(alter [column])

将custormers的cust_city列的默认值改为字符常量‘bejing’

alter table mysql.custormers
alter column cust_city set default 'beijing';

在表中删除一列(DROP COLUMN)

ALTER TABLE Product DROP COLUMN product_name_pinyin;

也可以使用 drop primary key、drop foreign key 、drop index为表删除主键，外键，索引。

变更表名(RENAME)或添加(rename [to])子句

alter table mysql_test.custormers
rename to mysql_test.backup_custormers;

或

RENAME TABLE Poduct to Product;

查看表：

A、查看表名称(show tables)

语法：

show [full] tables [{from | in}db_name][like 'pattern' | where expr];

B、查看表结构(show columns)或describe

show [full] columns {form | in} tb_name [{from | in} db_name] [like 'pattern' | where expr];

或

{describe | desc} tb_name [col_name |wild]; --mysql的show columns from的快捷方式

3、创建索引：

索引（Index）是帮助MySQL高效获取数据的数据结构。
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引。　　　　

索引的使用原则

A、主键自动建立唯一索引
B、频繁作为查询条件的字段应该创建索引
C、查询中与其他表关联的字段，外键关系建立索引
D、频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
E、WHERE条件里用不到的字段不创建索引
F、单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
G、查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
H、查询中统计或者分组字段
不适合使用索引的场合：
A、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引。
B、数据量小的表最好不要使用索引，由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
C、在不同值少的列上不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。在一个不同值较多的列可以建立索引。

索引的分类

单列索引

单列索引只包含单个列，但一个表中可以有多个单列索引。
A、普通索引
普通索引允许在定义索引的列中插入重复值和空值。
B、唯一索引
索引列中的值必须是唯一的，但是允许为空值。
C、主键索引
主键索引是一种特殊的唯一索引，不允许有空值。

复合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了组合的多个字段的左边字段时，索引才会被使用，使用复合索引时遵循最左前缀集合。

全文索引

全文索引，只有MyISAM存储引擎支持，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。在数据量较大时候，先将数据放入一个没有全文索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种：GEOMETRY、POINT、LINESTRING、POLYGON。
　在创建空间索引时，使用SPATIAL关键字。
　空间索引必须使用MyISAM存储引擎，并且空间索引的字段必须为非空。

创建表时创建索引的语法：

CREATE TABLE table_name[col_name data type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[asc|desc]

如：

在mysql_test,新建一个卖家信息表，seller,卖家ID和售卖产品类型作为主键，并在当月销量上创建索引。

create table seller
(
    seller_id int not null auto_increment,
    seller_name char(50) not null,
    seller_address char(50) null,
    seller_cotace char(50) null,
    product_type int(5) null,
    sales int null,
    primary key(seller_id,product_type),
    index index_seller(sales)
);

向已有表添加索引的语法：

ALTER TABLE tablename ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [indexname] (col_name)[ASC|DESC];
CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] indexname ON tablename(col_name[length])[ASC|DESC];

unique|fulltext为可选参数，分别表示唯一索引、全文索引
index和key为同义词，两者作用相同，用来指定创建索引
col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
asc或desc指定升序或降序的索引值存储
在创建索引时如果不指定索引名，默认使用字段名作为索引名。

如：

在seller表姓名列上添加一个非唯一索引，取名为index_seller_name.

alter table mysql.seller
add index index_seller_name(seller_name);

普通索引创建：

直接创建索引
CREATE INDEX index_name ON tablename[column[length][asc | desc]]

注：length用于指定使用列的前n个字符来创建索引。asc | desc 用于指定索引是按升序还是降序排列
修改表结构
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
创建表时指定索引

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
INDEX index_name(col_name)
);

唯一索引的创建

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tablename(column(length))
修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
创建表时直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
UNIQUE INDEX index_name(col_name)
);

主键索引的创建

修改表结构
ALTER TABLE table_name ADD PRIMARY KEY(col_name)
创建表时直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
PRIMARY KEY(col_name)
);

复合索引的创建

直接创建复合索引
CREATE INDEX indexName ON tablename(col_name1,col_name2)
修改表结构
ALTER TABLE table_name ADD INDEX indexName(col_name1,col_name2)
创建表时直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
INDEX index_name(col_name1,col_name2)
);

全文索引的创建

直接创建全文索引
CREATE FULLTEXT INDEX indexName ON tablename(col_name)
修改表结构
ALTER TABLE table_name ADD FULLTEXT INDEX indexName(col_name)
创建表时直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
FULLTEXT INDEX index_name(col_name)
);

在使用全文索引时，需要借助MATCH AGAINST操作，而不是一般的WHERE语句加LIKE。全文索引的限制比较多，比如只能使用MyISAM存储引擎，比如只能在CHAR、VARCHAR、TEXT上设置全文索引。比如搜索的关键字默认至少要4个字符，比如搜索的关键字太短就会被忽略掉。
SELECT * FROM tablename WHERE MATCH(col_name) AGAINST('pattern');
col_name为全文索引列，'pattern'为匹配的字符串

索引的删除

DROP INDEX [indexName] ON tablename; 
ALTER TABLE tablename DROP INDEX indexname;

索引信息的查看

show {index | keys | indexs}
{from | in} tbl_name
[{from | in}db_name]
[where expr]

4、创建视图

视图是由SELECT查询语句所定义的一个虚拟表，是查看数据的一种非常有效的方式。视图包含一系列带有名称的数据列和数据行，但视图中的数据并不真实存在于数据库中，视图返回的是结果集。

三级模式中，外模式对应到数据库中的概念就是视图。视图对重构数据库提供了一定程度的逻辑独立性，使用户可以将注意力集中在所关心地数据上。

创建视图(CREATE VIEW)

语法：

create view view_name [(column_list)]
as select_statement
[with[cascaded | local] check option]

注：

view_name:视图名称在数据库中必须是唯一的。

column_list:为视图中的每个列指定明确的名称，缺省则使用基本表与源视图一样的列名。

with check option:用于指定在可更新视图上所进行的修改都要符合select_statement所指定的限制条件

[cascaded | local],修改时，决定检测范围。cascaded:对所有相关视图进行检查。local只对定义视图进行检查。

如：创建学生信息的视图：

create view studentview
as select studentID, sname, sex from TStudent;

再如书上：创建视图 customers_view，要求显示所有男性信息，并保证今后对该视图修改都符合性别为男性。

create view mysql_test.customers_view
as
select * from mysql_test.customers
where cust_sex='M'
with check option;

再如：

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

注意：定义视图时不能使用ORDER BY子句

使用视图

视图的使用和普通表一样。这是对视图最常用的操作。

select * from studentview;

再如书上：在视图customers_view中查找id号为905的客户姓名及地址

select cus_name,cus_addr
from mysql_test.customers_view
where cus_id=905;

删除视图(DROP VIEW)，必须具有删除的权限

语法：

drop view [if exists]
 view_name[,view_name]...
[restrict | cascade]

restrict:限制 cascade:级联

如：

DROP VIEW ProductSum;

修改视图定义 alter view

语法与 create view一样，不再重复。

也可以先删除视图，再创建视图来实现。

如：

CREATE OR REPLACE VIEW viewname AS SELECT [...] FROM [...];

alter view studentview 
as 
select studentID as 学号, sname as 姓名, sex as 性别 from TStudent;

修改视图数据：

不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作；
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），可以进行增删改数据操作。

如果视图的基表是一张表，可以通过视图向基表插入记录，要求视图中的没有的列允许为空。
A、通过视图插入数据到表

insert into studentview(studentID, sname, sex)VALUES('01001', '孙悟空', '男');

MySQL数据库高级（三）——视图

查询插入的记录，可以看到通过视图没有的列，值为空或默认值。

B、通过视图删除表中记录
视图的基表只能有一张表，如果有多张表，将不知道从哪一张表删除。

delete from studentview where studentid='01001';

C、通过视图修改表中记录
只能修改视图中有的列。

update studentview set sname='孙悟空' where studentid='00001';

查看视图：

查看已有的视图 show create view

语法：

show create view view_name

view_name:要查看视图的名称。

查看视图的信息

describe viewname;
desc scoreview;

查看所有的表和视图

show tables;

查看视图的信息

show fields from scoreview;

子查询（一次性视图）

-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
  FROM ( SELECT product_type, COUNT(*) AS cnt_product
            FROM Product
          GROUP BY product_type ) AS ProductSum;

标量子查询

在WHERE子句中使用标量子查询

SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                     FROM Product);

注意：能够使用常数或者列名的地方，无论是SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用标量子查询。

关联子查询

SELECT product_type, product_name, sale_price
  FROM Product AS P1 
 WHERE sale_price > (SELECT AVG(sale_price)
                          FROM Product AS P2 
                      WHERE P1.product_type = P2.product_type
                        GROUP BY product_type);

这里起到关键作用的就是在子查询中添加的WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。

视图实例：

1、使用视图创建视图

创建视图的查询的表称为基表，基表可以是视图和表。

create view sview
as select studentID, sname, sex 
from studentview where studentID>990 and sex='男';

2、创建学生成绩表的视图

创建一个视图，视图包含学生学号、姓名、学科和成绩。

create view view1
as select a.StudentID,a.Sname,c.subJectName,b.mark  
from TStudent a 
join TScore b on a.StudentID=b.StudentID 
join TSubject c on b.subJectID=c.subJectID;

MySQL数据库高级（三）——视图

创建成绩视图，包含学号、姓名、计算机网络课程成绩、数据结构成绩、JAVA开发成绩。

create view scoreview
as select studentid 学号,sname 姓名,
AVG(case subjectname when '计算机网络' then mark END) 计算机网络,
AVG(case subjectname when '数据结构' then mark END) 数据结构,
AVG(case subjectname when 'JAVA开发' then mark END)  JAVA开发 from view1
group by 学号;

MySQL数据库高级（三）——视图

Ⅱ. DML（Data Manipulation Language，数据操纵语言）

只列出表增删改，查询详见《SQL考点之SQL查询、SQL支持数据类型》

1、向表中插入数据(INSERT... values)

语法：

insert [into] tbl_name [(col_name,...)]
{values | value}({expr | default},...),(...),...

包含列清单

INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
VALUES ('0001', 'T恤衫','衣服', 1000, 500, '2009-09-20');

这个命令也是很常用的，一定要熟写熟记！！！！！

省略列清单

START TRANSACTION; 
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;

insert...set给表中某列指定列值

insert [into] tbl_name
set col_name={expr | default},...

insert select从其他表中复制数据

语法：

insert [into] tbl_name [(col_name...)]
select ...

INSERT INTO ProductCopy (product_id, product_name, product_type，sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;

INSERT 语句中的SELECT 语句，也可以使用WHERE 子句或者GROUP BY 子句等。

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
 GROUP BY product_type;

2、数据的删除(DELETE)

语法：

delete from tbl_name
[where where condition]
[order by ...]
[limit row_count]

清空表

DELETE FROM Product;

指定删除对象（搜索型DELETE）

DELETE FROM Product
 WHERE sale_price >= 4000;

指定删除，是很常用的。

根据另一张表的条件删除记录

DELETE a FROM table_a a join table_b b on a.column1=b.column1 [WHERE condition>] ;

删除分数小于60分的学生

delete a from TStudent a join TScore b on a.`StudentID`=b.`StudentID`
where b.mark<60

也可以使用子查询实现。
删除分数表中分数大于90的学生

delete from TStudent where studentid in (select studentid from TScore where mark&gt;90);

3、数据的更新(UPDATE)

语法：

update tbl_name
set col_name = {expr | default} [,col_name2={expr2 | default}]...
[where where_condition]
[order by ...]
[limit row_count]

注：order by:限定表中行被修改的次序

limit：限定被修改的行数。

更新整列

UPDATE Product
   SET regist_date = '2009-10-10';

指定条件的更新（搜索型UPDATE）

UPDATE Product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';

指定条件更新，是很常用的。

多列更新

UPDATE Product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

根据另一张表的条件更改记录

UPDATE table_nameA a join table_nameB b 
on a. column_name1=b. column_name1
     SET a.column_name1 = value1,
    a.column_name2=value2,……,
     a.column_namen=valuen
    WHERE b. column_name2>20

如：将有不及格的学生姓名后加*标记

update TStudent a join TScore b on a.`StudentID`=b.`StudentID`
set a.`Sname`=concat(sname,'*') where b.mark<60

同时更改两张表的列
实例：把分数低于60分的学生，加5分，并在学生姓名添加标记+

update TStudent a join TScore b on a.`StudentID`=b.`StudentID`
set a.`Sname`=concat(sname,'+'), b.mark=b.`mark`+5
 where b.mark<60;

子查询也能实现相同功能
以下语句将分数有大于98分的学生姓名后加#号标记

update TStudent set Sname=concat(sname,'#') where studentid in
(select studentid from TScore where mark>98);

Ⅲ. DCL（Data Control Language，数据控制语言）

1、创建事务(START TRANSACTION) - 提交处理(COMMIT)

START TRANSACTION;
    -- 将运动T恤的销售单价降低1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';
    -- 将T恤衫的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤衫';
COMMIT;

2、取消处理(ROLLBACK)

START TRANSACTION;
    -- 将运动T恤的销售单价降低1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';
    -- 将T恤衫的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤衫';
ROLLBACK;

guangod

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库考点之SQL语句（数据库、表、索引、视图）

如题：2019年10月注：除了数据库的设计思想外，就是语言了。掌握SQL也是很实用的。那么有没有什么技巧呢？首先，如果给我们一张表，我们是从（from）这张表开始查，然后如果需要过滤就（where）过滤，之后如果引用了聚合函数，就需要进行分组（group by）,接下来如果我们需要对这前面阶段–分组得到的结果进行二次过滤的时候，就用到（having）进行过滤，只要记住，having只服务于group by就行了，经过这么多步骤得出来的结果后我们选择(select)哪些需要显示的用什么顺（or
复制链接

扫一扫