视图
视图是建立在一个或者多个表上面通过子查询建立的投影,是一个虚拟表,其内容由查询定义。同真实的表一样,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。
视图本身是不包含任何数据的,视图的数据全部来自基表,基表的数据更改则视图数据更改,视图的数据修改其实是在修改基表数据。对视图的操作与对表的操作一样,可以对其进行查询、修改(有一定的限制)、删除。
视图可以进行多视图的连接查询
创建视图:
Create view view名 (各列别名)AS 查询语句
视图如果有别名,通过别名进行列的操作
删除视图:
Drop view view名
显示创建视图语句
Show create view view名
查看视图内容:
Select *
FROM information_schema.views
索引
分类:主键索引 外键索引 全文索引 普通索引
创建索引:
1. create index idx_名 ON emp(empno,ename);
2. alter table emp add index(empno,ename);
3. create table emp(empno int,ename char(10),index(empno,ename));
查看索引 show index from 表名
删除索引 drop index index名 on 表名
全文检索 搜索引擎为MyISAM 支持频率在50%以下的单词检索,不区分大小写
CREATE TABLE `tt1` (
`id` int(11) DEFAULT NULL,
`title` text,
`content` text,
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
SELECT *
FROM tt1
WHERE match(title,content) against ('mysql');
存储过程与函数
存储函数与存储过程的区别:
存储函数不能拥有输出参数;必须添加return;
存储函数可直接调用,且不需使用call语句,而存储过程的调用必须使用call语句;
存储函数中必须包含一条return语句,而这条特殊的SQL语句不允许包含于存储过程。
过程的返回可以是out或者直接是select
备注:delimiter作用就是对整个小段语句做一个简单的封装。确定一个提交的段落,不然mysql遇到‘;’就提交
存储过程
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
函数
mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURN S CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE FUNCTION test.hello\G
mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
练习:
查找对应姓名雇员的奖金,如果奖金高于500,就显示‘表现优秀,继续努力’
存储过程
CREATE PROCEDURE find2(idNAME VARCHAR(20))
BEGIN
SELECT
EMP.ENAME,
if(EMP.COMM > 500, '表现优秀,继续努力', COMM) AS '奖金'
FROM EMP
WHERE idNAME = EMP.ENAME;
END;
CALL find2('李兰');
函数
DELIMITER //
CREATE FUNCTION find3(idNAME VARCHAR(20))
RETURNS CHAR(100)
BEGIN
DECLARE FUN CHAR(100);
DECLARE money DOUBLE;
SELECT EMP.COMM
INTO money
FROM EMP
WHERE idNAME = EMP.ENAME;
IF (money >= 500)
THEN SET FUN = 'good';
END IF;
RETURN (FUN);
END;//
DELIMITER ;
SELECT find3('李兰');
补充:流程控制语句IF, CASE, LOOP, WHILE, ITERATE, LEAVE
查询每个学生学号、 学生姓名、 课程名称、 成绩 (成绩大于60时的显示及格,小于60时的显示不及格)
SELECT stu.SID, stu.SName,subject.SubName,
select_subject.Grade < 60 AS '不及格',
select_subject.Grade > 60 AS '及格'
FROM stu,select_subject,subject
WHERE stu.SID = select\_subject.StuID and select_subject.SubID= subject.SubID;
IF
SELECT stu.SID, stu.SName, subject.SubName,if( select_subject.Grade < 60,'不及格','及格')
AS '成绩'
FROM stu,select_subject,`subject`
WHERE stu.SID = select_subject.StuID and select_subject.SubID= subject.SubID;
CASE
SELECT stu.SID, stu.SName, subject.SubName,
CASE select_subject.Grade < 60
WHEN 1 THEN'不及格'
WHEN 0 THEN '及格'
ELSE '成绩为空'END
AS '成绩'
FROM stu,select_subject,subject
WHERE stu.SID = select_subject.StuID AND select_subject.SubID= subject.SubID;
ELT
Select stu.SID, stu.SName, `subject`.SubName,
ELT((select_subject.Grade < 60)+1,'及格','不及格') '成绩'
from stu,select_subject,subject
WHERE stu.SID = select_subject.StuID and select_subject.SubID= subject.SubID
LOOP, ITERATE
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END
REPEAT
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
mysql> CALL dorepeat(1000)//
mysql> SELECT @x//
WHILE
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END
– 建立存储函数:根据给定的cust_id返回客户所在的州名(缩写),若库中无给定的cust_id,则返回“不存在该客户”。
delimiter //
create function fn_search(c_id int)
returns varchar(50) -- 定义返回的数据类型,与函数部分中的数据类型需统一,如函数中的“不存在该客户”为6个字符,如果这里设置为char(5),则无法输出该结果表示对于相同的输入值,返回值也相同
begin
declare state char(2); -- 声明一个变量state,作为输出的州变量
select cust_state from customers where cust_id=c_id into state;
if state is null then
return(select ’不存在该客户’); --注意这里return不用加s
else
return(select state);
end if;
end;
//
-- 执行存储函数
->select fn_search(10001);