sql status上添加索引 sql语句加索引

转载

mob64ca13fc220d 2024-07-30 14:57:31

文章标签 sql status上添加索引 SQL 存储过程字段 文章分类 数据仓库大数据

sql status上添加索引 sql语句加索引_sql status上添加索引_02

1. sql创建数据库，创建表，维护列

2. sql基本的增删改查

3. 索引

4. 视图

5. 存储过程

6. 触发器

PART

sql创建数据库，创建表

SQL语句创建一个数据库

create database 语句用于创建数据库；
语法：create database database_name;
实例：create database my_db;(创建名为“my_db”的数据库)

SQL语句创建一个表

create table语句用于创建数据库中的表；

语法：create table 表名称(列名称1 数据类型，列名称2 数据类型，列名称3 数据类型，....);

SQL中最常用的数据类型：

integer(size),int(size),smallint(size),tinyint(size)--仅容纳整数。在括号内规定数字的最大位数。decimal(size,d),numeric(size,d)--容纳带有小数的数字。“size”规定数字的最大位数。“d”规定小数点右侧的最大位数。

char(size)--容纳固定长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。date(yyyymmdd)--容纳日期。

简单实例：

create table persons (
     id_p int,
     lastName varchar(255),
     firstName varchar(255),
     address varchar(255),
     city varchar(255)
)
实例：
drop table if exists t_menu_relation;
create table t_menu_relation
(
    ID int(10) not null AUTO_INCREMENT,
    menu_id int(10) comment '菜单表主键',
    content varchar(100) comment '菜单链接说明',
    background_link varchar(100) comment '地址链接',
    status char(1) default '1' comment '1 有效 0 无效',
    create_id int(10) comment '创建人员',
    create_date     datetime comment '创建日期',
    modify_date datetime comment '修改日期',
    modify_id int(10) comment '修改人员',
    primary key (ID)
);
alter table t_menu_relation comment '菜单链接明细表';
alert table t_menu_relation add constraint FK_ref_menu_id_menu_realtion foreign key (menu_id) references t_menu （id）;

SQL语句对已有的表添加、修改或删除列

alter table语句用于在已有的表中添加、修改或删除列。

在已有表中添加列语法：

alter table table_name add column_name datatype;

在已有表中删除表中的列：

alter table table_name drop column column_name;

(注释：某些数据库系统不允许这种在数据库表中删除列的方式：drop column column_name)

改变表中列的数据类型：

alter table table_name alter column colmn_name datatype;

实例：

alter table persons add birthday date;
(在表persons中添加一个名为birthday的新列)
alter table persons alter column birthday year;
(改变persons表中birthday列的数据类型)
alter table person drop column birthday;
(删除person表中的birthday列)
PART
2

sql基本的增删改查

插入数据

插入单行
insert [into] <表名> (列名) values (列值)
例：

insert into Strdents (姓名,性别,出生日期) values ('小明','男','2000/1/1')

将现有表数据添加到一个已有表
insert into <已有的新表> (列名) select <原表列名> from <原表名>
例：

insert into tongxunlu ('姓名','地址','电子邮件')
select name,address,email from Strdents

直接拿现有表数据创建一个新表并填充
select <新建表列名> into <新建表名> from <源表名>
例：

select name,address,email into tongxunlu from strdents

使用union关键字合并数据进行插入多行
insert <表名> <列名> select <列值> tnion select <列值>
例：

insert Students (姓名,性别,出生日期)
select '小明','男','2000/1/1' union（union表示下一行）
select '小东','男','2000/1/1'

删除数据

删除满足条件的行
delete from <表名> [where <删除条件>]
例：

delete from a where name='小明'

说明：删除表a中列值为小明的行

删除整个表
truncate table <表名>

例：

truncate table tongxunlu
注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能用语有外建约束引用的表

删除table语句：

drop table语句用于删除表（表的结构、属性以及索引也会被删除）

语法：DROP TABLE 表名称；

删除database语句：

drop database 语句用于删除数据库，语法：drop database 数据库名称；

实例：drop table if exists t_articlel;

说明：数据库中如果存在表t_article则删除

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么：我们可以使用SQL TRUNCATE TABLE 语句（仅仅删除表格中的数据）

语法：truncate table 表名称；

修改数据

update <表名> set <列名=更新值> [where <更新条件>]
例：

update tongxunlu set 年龄=18 where 姓名='小明'

查询数据

精确查询

select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

查询所有数据行和列
例：

select * from a
说明：查询a表中所有行和列

查询部分行列--条件查询
例：

select i,j,k from a where f=5
说明：查询表a中f=5的所有行，并显示i,j,k３列

在查询中使用as更改列名
例：

select name as 姓名 from a where xingbie='男'
说明：查询a表中性别为男的所有行，显示name列，并将name列改名为（姓名）显示

查询空行
例：

select name from a where email is null
说明：查询表a中email为空的所有行，并显示name列；SQL语句中用is null或者is not null来判断是否为空行

在查询中使用常量
例：

select name, '唐山' as 地址 from Student
说明：查询表a，显示name列，并添加地址列，其列值都为'唐山'

查询返回限制行数(关键字：top percent)
例１：

select top 6 name from a
说明：查询表a，显示列name的前６行，top为关键字
例２：

select top 60 percent name from a
说明：查询表a，显示列name的60%，percent为关键字

查询排序（关键字：order by , asc , desc）
例：

select name from a where chengji>=60 order by name desc
说明：查询a表中chengji大于等于60的所有行，并按降序显示name列；默认为ＡＳＣ升序

模糊查询

使用like进行模糊查询

注意：like运算副只用于字符串，所以仅与char和varchar数据类型联合使用
例：

select * from a where name like '赵%'
说明：查询显示表a中，name字段第一个字为赵的记录

使用between在某个范围内进行查询
例：

select * from a where nianling between 18 and 20

说明：查询显示表a中nianling在18到20之间的记录

使用in在列举值内进行查询

例：

select name from a where address in ('北京','上海','唐山')

说明：查询表a中address值为北京或者上海或者唐山的记录，显示name字段

分组查询

使用group by进行分组查询
例：

select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名) from score (注释:这里的score是表名) group by studentID

说明：在表score中查询，按strdentID字段分组，显示strdentID字段和score字段的平均值；select语句中只允许被分组的列和为每个分组返回的一个值的表达式，例如用一个列名作为参数的聚合函数

使用having子句进行分组筛选
例：

select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名) from score (注释:这里的score是表名) group by studentID
having count(score)>1

说明：接上面例子，显示分组后count(score)>1的行，由于where只能在没有分组时使用，分组后只能使用having来限制条件。

多表联接查询

内联接:

在where子句中指定联接条件
例：

select a.name,b.chengji from a,b where a.name=b.name
说明：查询表a和表b中name字段相等的记录，并显示表a中的name字段和表b中的chengji字段

在from子句中使用join…on
例：

select a.name,b.chengji from a inner join b on (a.name=b.name)
说明：同上

外联接:

左外联接查询
例：

select s.name,c.courseID,c.score from strdents as s left outer join score as c on s.scode=c.strdentID
说明：在strdents表和score表中查询满足on条件的行，条件为score表的strdentID与strdents表中的sconde相同

右外联接查询
例：

select s.name,c.courseID,c.score from strdents as s right outer join score as c on s.scode=c.strdentID
说明：在strdents表和score表中查询满足on条件的行，条件为strdents表中的sconde与score表的strdentID相同

PART

索引

索引简介：

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

建立索引的原则：

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

创建索引的语法：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name

ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index_property: 索引属性。

UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

删除索引语法：

DROP INDEX table_name.index_name[,table_name.index_name]

说明：table_name: 索引所在的表名称。

index_name : 要删除的索引名称。

显示索引信息：

使用系统存储过程：sp_helpindex 查看指定表的索引信息。

执行代码如下：

Exec sp_helpindex book1;

PART

视图

视图简介：

视图（view）是一种虚拟的表，并不在数据库中实际存在。通俗的来说，视图就是执行select语句后返回的结果。

视图的优点：

1，简单

对于数据库的用户来说，很多时候，需要的关键信息是来自多张复杂关联表的。这时用户就不得不使用十分复杂的SQL语句进行查询，给用户造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图的用户不需要关心相应表的结构、关联条件等。对于用户来说，视图就是他们想要查询的结果集。

2，安全

使用视图的用户只能访问被允许查询的结果，使用视图可以限制用户访问一些敏感信息列。

创建视图的语法：

CREATE VIEW 自定义名 AS

例：

CREATE VIEW vw1 AS SELECT 学号,姓名,所属院系 FROM student

WHERE 课名=’软件工程’ and 所属院系=’计算机’

修改视图：

CREATE OR REPLACE VIEW 视图名 AS SELECT […] FROM […];

PART

存储过程

存储过程简介：

SQL语句需要先编译在执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户通过制定存储过程的名字以及参数(如果有入口参数的话)来执行它.

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

存储过程的创建：

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名数据类型[,[IN|OUT|INOUT] 参数名数据类型…]]) [特性 ...] 过程体

例：

DELIMITER
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
DELIMITER ;

IN: 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT: 该值可在存储过程内部被改变，并可返回

INOUT: 调用时指定，并且可被改变和返回

过程体: 过程体的开始与结束使用BEGIN与END进行标识。

IN参数例子：

DELIMITER
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END;
DELIMITER ;

#调用
SET @p_in=1;
CALL in_param(@p_in);

SELECT @p_in;

OUT参数例子：

#存储过程OUT参数
DELIMITER
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END;
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);

SELECT @p_out;

INOUT参数例子：

#存储过程INOUT参数
DELIMITER
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END;
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

存储过程的修改：

ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

ALTER {PROCEDURE | FUNCTION} 
sp_name [characteristic ...]
characteristic:{ CONTAINS SQL |
 NO SQL |READS SQL DATA |
 MODIFIES SQL DATA }| 
SQL SECURITY { DEFINER 
| INVOKER }| COMMENT 'string'

sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句；

READS SQL DATA表示子程序中包含读数据的语句；

MODIFIES SQL DATA表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；

INVOKER表示调用者可以执行。

COMMENT 'string'是注释信息。

实例：

#将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
ALTER  PROCEDURE  num_from_employee
  MODIFIES SQL DATA
  SQL SECURITY INVOKER ;
#将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。
ALTER  PROCEDURE  name_from_employee
  READS SQL DATA
  COMMENT 'FIND NAME' ;