视图
USE myschool;
DROP VIEW IF EXISTS `view_student_result`;
删除视图`
CREATE VIEW view_student_result`
AS
创建视图
……
SELECT * FROM `view_student_result`;
查看视图
创建视图
CREATE VIEW vw_python5 AS
SELECT studentname,sex,phone,studentresult,examdate FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`;
查看视图
SELECT * FROM vw_python5;
视图可以使用多个表
视图也可以嵌套
CREATE VIEW vw_neinei AS
SELECT * FROM views;查看所有的视图
SELECT * FROM views\G;\G全局
#查看学生各科目考试成绩平均分
CREATE VIEW vw_result AS
SELECT studentname,subjectname,studentresult FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`
INNER JOIN subjects ON results.`subjectno`=subjects.`subjectno`
GROUP BY studentname,subjectname;
索引
相当于目录的概念
提高检索速度
改善数据库性能
B—树索引
哈希索引
索引类型
普通索引;唯一索引unique(允许有一个空值);主键索引(非空唯一);复合索引;全文索引fulltext(用于大的文字类型);空间索引spatial(例如地图搜附近,空间数据类型的列建立的索引)
查询时减少使用*
show index from 查看索引
在cmd
数据备份(数据库故障;突然断电;病毒入侵;误操作导致数据丢失)
mysqldump命令
mysqldump [options(选项参数)] –u username() –h host –ppassword
dbname[tbname1[,tbname2……]]>filename.sql
mysqldump -uroot -hlocalhost -p123456 school student2 > G:\MYSQL\schools.sql;备份命令
mysql(DOS命令)
恢复数据库mysql -uroot -p123456 school student2 > G:\MYSQL\schools.sql;
source filename;在mysql中恢复
导出
SELECT * FROM `result`
WHERE `subjectNo` =
(SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';
导入
LOAD DATA INFILE 'd:/back/result_Java.txt' INTO TABLE result;
USE myschool;
DROP VIEW IF EXISTS `view_student_result`;
删除视图`
CREATE VIEW view_student_result`
AS
创建视图
……
SELECT * FROM `view_student_result`;
查看视图
创建视图
CREATE VIEW vw_python5 AS
SELECT studentname,sex,phone,studentresult,examdate FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`;
查看视图
SELECT * FROM vw_python5;
视图可以使用多个表
视图也可以嵌套
CREATE VIEW vw_neinei AS
select studentname,studentresult from vw_python5;
SELECT * FROM views;查看所有的视图
SELECT * FROM views\G;\G全局
#查看学生各科目考试成绩平均分
CREATE VIEW vw_result AS
SELECT studentname,subjectname,studentresult FROM student2
INNER JOIN results ON student2.`studentNo`=results.`studentno`
INNER JOIN subjects ON results.`subjectno`=subjects.`subjectno`
GROUP BY studentname,subjectname;
索引
相当于目录的概念
提高检索速度
改善数据库性能
B—树索引
哈希索引
索引类型
普通索引;唯一索引unique(允许有一个空值);主键索引(非空唯一);复合索引;全文索引fulltext(用于大的文字类型);空间索引spatial(例如地图搜附近,空间数据类型的列建立的索引)
查询时减少使用*
show index from 查看索引
在cmd
数据备份(数据库故障;突然断电;病毒入侵;误操作导致数据丢失)
mysqldump命令
mysqldump [options(选项参数)] –u username() –h host –ppassword
dbname[tbname1[,tbname2……]]>filename.sql
mysqldump -uroot -hlocalhost -p123456 school student2 > G:\MYSQL\schools.sql;备份命令
mysql(DOS命令)
恢复数据库mysql -uroot -p123456 school student2 > G:\MYSQL\schools.sql;
source filename;在mysql中恢复
导出
SELECT * FROM `result`
WHERE `subjectNo` =
(SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';
导入
LOAD DATA INFILE 'd:/back/result_Java.txt' INTO TABLE result;