目录
1、视图
1.1创建视图
create view 视图名 as 查询语句eg:
mysql> create view v1 as select cs_id,cs_name,cs_credit from course where cs_type='专业基础';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v1;
+----------+--------------+-----------+
| cs_id | cs_name | cs_credit |
+----------+--------------+-----------+
| 5223013 | 大学物理 | 4 |
| 5237514 | c语言 | 4 |
| 20201833 | 概率论 | 3 |
| 20202336 | 高等数学 | 6 |
+----------+--------------+---ming--------+
4 rows in set (0.00 sec)
1.2修改视图
create or replace view 视图名(属性1，属性2，属性3...) as 查询语句
或者
alter view 视图名(属性1，属性2，属性3...) as 查询语句eg1: mysql> create or replace view v1(id,name,credit) as select cs_id,cs_name,cs_credit from course where cs_type='专业基础'; Query OK, 0 rows affected (0.01 sec) mysql> select * from v1; +----------+--------------+--------+ | id | name | credit | +----------+--------------+--------+ | 5223013 | 大学物理 | 4 | | 5237514 | c语言 | 4 | | 20201833 | 概率论 | 3 | | 20202336 | 高等数学 | 6 | +----------+--------------+--------+ 4 rows in set (0.00 sec) eg2: mysql> alter view v1(id1,name1,credit1) as select cs_id,cs_name,cs_credit from course where cs_type='专业基础'; Query OK, 0 rows affected (0.01 sec) mysql> select * from v1; +----------+--------------+---------+ | id1 | name1 | credit1 | +----------+--------------+---------+ | 5223013 | 大学物理 | 4 | | 5237514 | c语言 | 4 | | 20201833 | 概率论 | 3 | | 20202336 | 高等数学 | 6 | +----------+--------------+---------+ 4 rows in set (0.00 sec)
1.3创建复杂视图
mysql> create view v2 as (select e.name,d.depName from (Employee e,department d) where e.depno=d.depno and e.sex='男') ; Query OK, 0 rows affected (0.01 sec) mysql> select * from v2; +---------+-----------------+ | name | depName | +---------+-----------------+ | 王林 | 人力资源部 | | 王芳 | 财务部 | | 张晓 | 财务部 | | 李华 | 市场部 | | 李明 | 市场部 | | 吴天 | 市场部 | | 刘备 | 经理办公室 | | 赵云 | 研发部 | +---------+-----------------+ 8 rows in set (0.00 sec)
1.4删除视图
mysql> drop view 视图名;
mysql> select * from VIEWS where TABLE_NAME='视图名';#视图示例
（1）在数据库example下创建college表。College表内容如下所示
CREATE TABLE college(
number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
major VARCHAR(20) NOT NULL COMMENT '专业',
age INT(5) COMMENT '年龄'
);
（2）在student表上创建视图college_view。视图的字段包括student_num、student_name、 student_age和department。ALGORITHM设置为MERGE类型，并且为视图加上WITH LOCAL CHECK OPTION条件
mysql> create ALGORITHM=MERGE view college_view(student_num,student_name,student_age,department) as select number,name,age,major from college with local check option;
Query OK, 0 rows affected (0.01 sec)（3）查看视图college_view的详细结构
show create view college_view \G;
（4）更新视图。向视图中插入3条记录。记录内容如下表所示
numer name major age
0901 张三 外语 20
0902 李四 计算机 22
0903 王五 计算机 19
mysql> insert into college_view values('0901','张三',20,'外语');
Query OK, 1 row affected (0.01 sec)
mysql> insert into college_view values('0902','李四',22,'计算机');
Query OK, 1 row affected (0.00 sec)
insert into college_view value ('0903','王五',19,'计算机');
Query OK, 1 row affected (0.00 sec)
（5）修改视图，使其显示专业为计算机的信息，其他条件不变
mysql> alter ALGORITHM=MERGE view college_view(student_num,student_name,student_age,department) as select number,name,age,major from college where major='计算机' with local check option;
Query OK, 0 rows affected (0.00 sec)
#也可以将 alter 换成 create or replace
mysql> select * from college_view;
+-------------+--------------+-------------+------------+
| student_num | student_name | student_age | department |
+-------------+--------------+-------------+------------+
| 902 | 李四 | 22 | 计算机 |
| 903 | 王五 | 19 | 计算机 |
+-------------+--------------+-------------+------------+
2 rows in set (0.00 sec)（6）删除视图college_view
mysql> drop view college_view;
Query OK, 0 rows affected (0.00 sec)
2、索引
优点：可以提高检索数据的速度，对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。
1.创建单列索引
CREATE INDEX idx_name ON users (name);
这将创建一个名为 idx_name 的单列索引，它将加速对 name 列的查询。
2.创建多列索引
CREATE INDEX idx_name_age ON users (name, age);
这将创建一个名为 idx_name_age 的多列索引，它将加速对 name 和 age 列的查询。
3.创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
这将创建一个名为 idx_email 的唯一索引，它将确保 email 列中的值是唯一的，从而防止重复记录。
4.创建全文索引
CREATE FULLTEXT INDEX idx_name ON users (name);
这将创建一个名为 idx_name 的全文索引，它将加速对 name 列的全文搜索查询。
5.查询索引
SHOW INDEXE FROM table_name; 示例： show index from coll5;
6.删除索引
DROP INDEX index_name ON table_name;
示例：
（1）创建索引
create [unique/fulltext/spatial/] index 索引名 on 表名 (属性1[l类型] [asc/desc])；示例：
mysql> create index index_Emp on Employee (num asc);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
（2）删除索引
mysql> drop index index_Emp on Employee;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
（3） alter方式创建索引
mysql> alter table college add index index_college (number desc);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#索引示例
1、在数据库job下创建workInfo表。创建表的同时在id字段上创建名为index_id的唯一性索引，而且以 降序的格式排列。workInfo表内容如下所示
CREATE TABLE workInfo(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
type VARCHAR(10),
address VARCHAR(50),
tel VARCHAR(20),
wage INT,
content TINYTEXT,
extra TEXT,
UNIQUE INDEX index_id(id DESC)
);2 、使用create index语句为name字段创建长度为10的索引
mysql> create index index_name on workInfo(name(10)) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 03 、使用alter table语句在type和address上创建名为index_t的索引
mysql> alter table workInfo add index index_t (type,address);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 04 、将workInfo表的存储引擎更改为MyISAM类型
mysql> alter table workInfo engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 05 、使用alter table语句在extra字段上创建名为index_ext的全文索引
mysql> alter table workInfo add fulltext index index_ext (extra);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 06 、删除workInfo表的唯一性索引index_id
mysql> drop index index_id on workInfo;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
3、存储过程
格式
delimiter $$
create procedure 过程名(参数列表)
begin
SQL语句
end $$三种参数类型
IN, OUT, INOUT：
DELIMITER $$
USE `db`$$
DROP PROCEDURE IF EXISTS `p1`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `p1`()
BEGIN
SELECT COUNT(1) FROM mysql.user;
END$$
DELIMITER ;通过上面的编写来得到一个有几个用户
mysql> call p1();
+----------+
| count(1) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#存储过程示例
eg：查询表中男生或者女生人数
编写存储过程
DELIMITER $$ CREATE PROCEDURE `db`.`p4`(sex ENUM('男','女') ) BEGIN SELECT COUNT(1) FROM student WHERE sex=stu_sex; END$$ DELIMITER ;存储过程调用
mysql> call p4('男');
+----------+
| count(1) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p4('女');
+----------+
| count(1) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
4、触发器
查看触发器有哪些 show triggers
删除触发器 drop trigger
{ BEFORE | AFTER } 触发器时机
{ INSERT | UPDATE | DELETE } 触发的事件
#触发器示例
创建两个数据库，实现一个数据库插入时另一张表也自动插入。
mysql> CREATE TABLE tab1(
-> id int primary key auto_increment,
-> name varchar(50),
-> sex enum('m','f'),
-> age int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE tab2(
-> id int primary key auto_increment,
-> name varchar(50),
-> salary double(10,2)
-> );
Query OK, 0 rows affected (0.02 sec)创建触发器
DELIMITER $$ USE `db`$$ DROP TRIGGER /*!50032 IF EXISTS */ `tab1_after_insert_trigger`$$ CREATE /*!50017 DEFINER = 'admin'@'%' */ TRIGGER `tab1_after_insert_trigger` AFTER INSERT ON `tab1` FOR EACH ROW BEGIN INSERT INTO tab2(NAME,salary) VALUES(new.name,6000); END; $$ DELIMITER ;向表tab1插入数据
mysql> insert into tab1 value(1,'john','m',12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab1;
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | john | m | 12 |
+----+------+------+------+
1 row in set (0.00 sec)
mysql> select * from tab2;
+----+------+---------+
| id | name | salary |
+----+------+---------+
| 1 | john | 6000.00 |
+----+------+---------+
1 row in set (0.01 sec)
2681
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
