目录
一、视图
1.什么是视图
1) 视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。
2) 数据库中存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
3) 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
2.视图的作用
1) 使操作简便化。
2) 增加数据的安全性。
3) 提高表的逻辑独立性。
3.基本语法
CREATE VIEW 视图名 AS SELECT 语句;
二、索引
1.什么是索引
索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。
2.索引的优缺点
优点：提高查询数据的速度。
缺点：创建和维护索引的时间增加了，同时占用硬盘空间。
3.索引分类
1) 普通索引：是最基本的索引，它没有任何限制；
2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+
4.创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])
5.修改索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])
6.删除索引
DROP INDEX 索引名 ON 表名
案例：
1) 普通索引案例
create index 索引名 on 表名(字段名)
2) 唯一索引案例
create unique index 索引名 on 表名(字段名)
3) 主键索引案例
create table 表名(
id int not null auto_increment primary key,
...
)
4) 组合索引案例
create index 索引名 on 表名(字段名1,字段名2,...)
补充说明：
批量导入：LOAD DATA INFILE（推荐）
可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。
1) select * from 表名 into outfile '/文件名.sql';
2) load data infile '/文件名.sql' into table 表名(列名1,...);
https://blog.csdn.net/weixin_44595372/article/details/88723191
导入导出
1.使用工具，类似Sqlyog、Navicat等导入导出数据。
2.使用mysqldump导入导出
2.1 导出
2.1.1 导出表数据和表结构
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)#/usr/local/mysql/bin mysqldump -uroot -pabc >abc.sql
敲回车之后提示输出密码2.1.2 只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql#mysqldump -uroot -p -d abc > abc.sql
注：导出的数据在mysql的bin目录下2.2 导入
注意：首先建立空数据库
mysql>create database abc;
2.2.1 方法一
mysql>use abc; #选择数据库
mysql>set names utf8; #设置数据库编码
mysql>source /home/abc/abc.sql; #导入数据2.2.2 方法二
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql
3.LOAD DATA INFILE可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。
1) select * from 表名 into outfile '/文件名.sql';
2) load data infile '/文件名.sql' into table 表名(列名1,...);
练习题
SELECT * from t_teacher;
SELECT * FROM t_course;
SELECT * FROM t_score;
SELECT * FROM t_student;
# 语法：CREATE VIEW 视图名 as SELECT 语句;
CREATE VIEW v_student_score as
SELECT
s.*,
sc.score,
c.*,
t.tname
FROM
t_teacher t,t_course c,t_score sc,t_student s
where
t.tid=c.tid and
c.cid=sc.cid and
sc.sid=s.sid;
SELECT * from v_student_score;
CREATE TABLE `t_log` (
`id` varchar(32) NOT NULL COMMENT '唯一标识',
`ip` varchar(15) NOT NULL COMMENT 'IP地址',
`userid` varchar(32) NOT NULL COMMENT '用户ID',
`moduleid` varchar(32) NOT NULL COMMENT '模块ID',
`content` varchar(500) NOT NULL COMMENT '日志内容',
`createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`url` varchar(100) DEFAULT NULL COMMENT '请求URL地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT * from `t_log`
-- 1.什么是索引
--
-- 索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
-- 类似于图书的目录，方便快速定位，寻找指定的内容。
--
-- 2.索引的优缺点
--
-- 优点：提高查询数据的速度。
-- 缺点：创建和维护索引的时间增加了，同时占用硬盘空间。
--
-- 3.索引分类
--
-- 1) 普通索引：是最基本的索引，它没有任何限制；
--
-- 2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
CREATE UNIQUE index idx_moduleid on t_log(moduleid);
-- 3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
SELECT * from t_log where id='00d3f9ffca154dd9aa280916ac538019';
-- 4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
CREATE INSERT idx_userid_moduleid_createdate on t_log(userid,moduleid,createdate);
SELECT * from t_log where userid='';
EXPLAIN SELECT * from t_log where userid='00d3f9ffca154dd9aa280916ac538019' and moduleid='100301';
SELECT * from t_log where userid='' and moduleid='' and createdate='';
SELECT * from t_log where moduleid='';
--
-- 5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+
--
-- 4.创建索引
--
-- CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])
CREATE INDEX idx_moduleid on t_log(moduleid);
-- 5.修改索引
--
-- ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])
--
-- 6.删除索引
DROP INDEX idx_moduleid on t_log;
-- DROP INDEX 索引名 ON 表名
-- t_user(userid,username,password,realname,rolename,createdate)
-- t_order(oid,total,phone,address,creator,auditor,person)
--
-- t_user
-- 1,zs,1234,张三,用户,2022-01-01
-- 2,ls,1234,李四,审批员,....
-- 3,ww,1234,王五,配送员,....
--
-- t_order
-- 1,90,1777777787,长沙,1,null,null
-- 2,120,1777777787,长沙,1,2,null
-- 3,190,1777777787,长沙,1,2,3
--
-- 结果：
-- 1,90,1777777787,长沙,张三,null,null
-- 2,120,1777777787,长沙,张三,李四,null
-- 3,190,1777777787,长沙,张三,李四,王五
-- 方法一:
SELECT
sid,
sum(CASE WHEN cid='01' THEN score ELSE 0 END) '语文',
sum(CASE WHEN cid='02' THEN score ELSE 0 END) '数学',
sum(CASE WHEN cid='03' THEN score ELSE 0 END) '英语'
FROM t_score
GROUP BY sid;
select
oid,total,phone,address,
sum(CASE WHEN creator=1 THEN '张三' ELSE null END) 'creator',
sum(CASE WHEN auditor=2 THEN '李四' ELSE null END) 'auditor',
sum(CASE WHEN person=3 THEN '王五' ELSE null END) 'person'
from t_order
GROUP BY oid;
-- 方法二:
select oid,total,phone,
(select realname from t_user where userid = creator),
(select realname from t_user where userid = auditor),
(select realname from t_user where userid = person)
from t_order t1 left join t_user
-- 技术点：外连接
--
-- SELECT t_order from
-- (SELECT sid,score from t_score where cid='01') t1 LEFT JOIN
-- (SELECT sid,score from t_score where cid='02') t2 on t1.sid=t2.sid
--
-- 方法三：
SELECT
oid,total,phone,
u1.realname as '创建人',
u1.realname as '审批人',
u1.realname as '配送员'
from
t_order o
left join t_user u1 on o.creator=u1.userid
left join t_user u1 on o.auditor=u1.userid
left join t_user u1 on o.person=u1.userid