目录
1. sql创建数据库,创建表,维护列
2. sql基本的增删改查
3. 索引
4. 视图
5. 存储过程
6. 触发器
PART
1
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,k3列
在查询中使用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)例1:
select top 6 name from a说明:查询表a,显示列name的前6行,top为关键字例2:
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列;默认为ASC升序
模糊查询使用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 studentIDhaving 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
3
索引
1
索引简介: 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
4
视图
1
视图简介:
视图(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
5
存储过程
1
存储过程简介:
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' ;
MySQL存储过程的删除:
DROP PROCEDURE [过程1[,过程2…]]
从MySQL的表格中删除一个或多个存储过程。
PART
6
触发器
1