MySQL 语言最全总结

1
2
3
4
5
6
7
8
9
create database person charset utf8;	-- 创建数据库
show DATABASES;	-- 查看数据库
drop database person; -- 删除数据库
use person; -- 使用数据库



delect table [表名]; -- 删除数据表，不保留id等自增数据，只保留表结构；
truncate table [表名]; -- 删除数据表，会保留id等自增数据和表结构；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
date         #-- 日期2014-09-18
time         -- 时间08:42:30
datetime     -- 日期时间2014-09-18 08:42:30
timestamp    -- 自动存储修改的时间
year         -- 年份
 
tinyint      -- 1byte (-128~127)  
smallint     -- 2byte (-32768~32767)
mediumint    -- 3byte (-8388608~8388607)
int          -- 4byte (-2147483648~2147483647)
bigint       -- 8byte (+-9.22*10的18次方)
 
float(m,d)   -- 4byte 单精度浮点数，m总个数，d小数位
double(m,d)  -- 8byte 双精度浮点数，m总个数，d小数位
decimal(m,d) -- decimal是存储为字符串的浮点数
 
char(n)      -- 固定长度，最多255个字符
varchar(n)   -- 可变长度，对多65535个字符
tinytext     -- 可变长度，最多255个字符
text         -- 可变长度，最多65535个字符
mediumtext   -- 可变长度，最多2的24次方-1个字符
longtext     -- 可变长度，最多2的32次方-1个字符

1
2
3
4
5
6
7
not null  -- 非空
unique  -- 唯一，也可以写为unique key
primary key -- 主键，默认唯一非空
foreign key references -- 外键
auto_increment -- 自增，只能用于主键自增
DEFAULT null   -- 默认值:null
COMMENT '年龄'  -- 注释：年龄

1
2
3
4
5
6
7
8
9
10
补充：添加外键的两种方法：
    方法一：创建表语句中：
      -- 给当前表的键sid添加外键到stu表中的sid，并且定义这个外键的名称为fk_stu_score1_sid
      CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)
      -- 也可以不用定义外键的名称
      FOREIGN KEY(sid) REFERENCES stu(sid)

    方法二：在创建表语句外
       -- 给表score1表的键sid添加外键到表stu表的sid，并且定义这个外键的名称为fk_sid
       ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table t_stu(
	sid int primary key auto_increment,
	sname varchar(20) unique not null,
	sage int not null	
)auto_increment = 001;


create table if not exists t_score(
	scid int primary key auto_increment,
	sid int not null,
	yuwen int,
	shuxue int,
	yingyu INT
)auto_increment=1;

-- 添加外键
alter table t_score add constraint stuscore_sid foreign key(sid) references t_stu(sid)

-- 查看表结构
desc t_stu;
desc t_score;

1
2
3
4
5
-- 删除表格
create table gaofei(id int primary key auto_increment) -- 测试用

drop table gaofei; -- 删除表格，删除所有信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- =========================新建测试表===========================
create table test(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment=1


-- ==================重命名====================

  1、alter table 旧表名 rename 新表名;
  //修改 数据表名
alter table test rename test1;


-- ==================修改字段数据类型====================
  2、alter table 表名 modify  字段名 新数据类型;
  //只修改数据类型
alter table test1 modify name char(20);


-- -------修改字段名和数据类型------------
  3、alter table 表名 change 旧字段名  新字段名 新的数据类型;
  //只修改字段名
  //修改字段名和数据类型
alter table test1 change name names varchar(50);


-- -------修改字段位置-------------------
  4、alter table 表名 modify 字段 数据类型 after 已存在的字段/first
  //字段修改位置
alter table test1 modify name varchar(50) after id;


-- ==================添加新字段====================
  5、alter table 表名 add  新字段名 新数据类型 （约束条件‘如：not null’）（位置‘first’）;
  //增加字段
alter table test1 add age int(20);

  6、alter table 表名 add  新字段名 新数据类型  （约束条件） after 已存在的字段；
  //将新加的字段放在某个已存在的字段后
alter table test1 add places VARCHAR(20) after id;


-- ==================删除字段===================
  7、alter table 表名 drop 字段名；
  //删除已存在的字段名
alter table test1 drop places;

1
2
3
4
5
6
7
8
9
10
11
-- t_stu
-- 对于空值或者自增的值可以用null或者当前字段名替代
insert into t_stu values(sid,'zss',21);

insert into t_stu values(null,'l5',21);

insert into t_stu(sname,sage) values('w5',21);

insert into t_stu(sname,sage) values('z7',22),('s8',22);  -- 添加多行数据，用逗号隔开

select * from t_stu;  -- 110测试

1
2
3
4
5
6
7
-- t_score
insert into t_score values(scid,1,11,12,13);
insert into t_score values(scid,5,11,16,63);
insert into t_score values(scid,3,11,82,73);
insert into t_score values(scid,4,11,92,99);

select * from t_score; -- 110测试

1
2
3
4
-- select 字段名  from 表格名 where 条件
select * from t_score; -- 110测试

select * from t_stu;  -- 110测试

1
2
3
4
5
6
7
-- 删除表格数据
create table gaofei(id int primary key auto_increment) -- 测试用

truncate table gaofei; -- 删除表格所有数据，保留表结构

delete from gaofei; -- 删除表格所有数据，保留表结构
delete from gaofei where id=1; -- 删除指定条件的表格数据

1
2
3
4
5
-- 修改
update t_stu set sage=90;  -- 修改所有

update t_stu set sage = 99,sname='donghao' where sid > 5  -- 修改给定条件的记录

1
2
3
4
5
6
7
8
9
10
-- 比较运算符 > < >= <= = (!= <> )两个不等于一样
select * from t_score;

select * from t_score where yingyu > 60; 
select * from t_score where shuxue >= 60;
select * from t_score where shuxue < 50;
select * from t_score where shuxue <= 50;
select * from t_score where shuxue = 50;
select * from t_score where shuxue != 50;
select * from t_score where shuxue <> 50;

1
2
3
4
5
6
7
-- 逻辑运算符 and:并且 or:或者 not:
select * from t_score where yingyu > 60 and shuxue > 60;

select * from t_score where yingyu > 60 or shuxue >90;

select * from t_score where not yingyu = 73;  -- 可以转换为!=的情况
select * from t_score where not (yingyu > 60 and shuxue > 60);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 模糊查询  like
-- 通配符：
--  _ :一个字符位
--  % :匹配0个或多个字符
-- escape ：转义字符，匹配上面两个使用

select * from t_stu where sname like '_aofe_';
select * from t_stu where sname like binary '%a%'; -- 区分大小写的比较字符串用binary

select * from t_stu where sname like '%g%'; -- 匹配包含a
select * from t_stu where sname like 'a%';  -- 匹配以a开头

select * from t_stu where sname like '/_%/%%' escape '/'; -- 将/作为转义字符


-- 正则匹配：rlike 和 regexp 
-- . ：匹配任意单个字符
-- + ：匹配一个或者多个字符
-- * :匹配0个或者多个字符
-- []：匹配任意一个[]内的字符
-- ^ ：匹配开头
-- $ ：匹配结尾
-- {n}：匹配字符n次
--  []:匹配范围内的一个字符
-- [^]:匹配不在范围内的一个字符
-- a|b ：匹配a或者b

select * from t_stu where sname rlike '^[gaofei]';

select * from t_stu where sname rlike 'a+';

-- select * from t_stu where sname REGEXP '\w+';   -- 貌似没有这种
select * from t_stu where sname binary REGEXP 'a+'; -- 区分大小写的比较字符串用binary



-- 上面两种方式like 和relike、regexp的异同点：
-- 相同点：
-- 三个都不区分大小写，如果需要区分，在where 后添加关键字段binary

-- 异同点：
-- 除了使用运算符不同之外，两个重要的差异： 
-- - SQL 的 LIKE 模式只用于匹配整个字符串；正则表达式 REGEXP 可以匹配字符串的任何部分。 
-- - LIKE 运算符是多字节安全的。REGEXP 运算符只能正确地处理单字节字符集，并不会考虑排序规则。

1
2
3
4
5
-- 排序 order BY
-- 升序 asc  （默认）
-- 降序 desc 
select * from t_stu order by sid; 
select * from t_stu order by sid desc;

1
2
3
4
5
6
7
8
-- distinct :查询不重复的记录
-- 注意：（1）distinct这个关键字来过滤掉多余的重复记录只保留一条，
-- 	但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。
-- 	其原因是distinct只能返回它的目标字段，而无法返回其它字段，
-- 			（2）distinct 必须放在字段的最前面，否则报错

select distinct sage from t_stu;
select distinct sage,sid from t_stu;  -- 同时按照（sage + sid）去重，只有一个不一样的记录就是不同的记录

1
2
3
4
5
6
7
8
9
10
11
-- null 和 空字符串的用法
-- null : 表示空值  is null    is not NULL
-- 空字符串：有值 
-- --注意：当比较的字段的值为null时，用is null查找，如果有值的话，就用比较运算符等去查找
select * from t_score  where shuxue is NULL;

select * from t_score where shuxue = '90';

select * from t_score where shuxue != 0 and shuxue is not null;

select * from t_score;  -- 110测试

1
2
3
4
5
6
-- 字段 between A and B  查询满足条件范围的记录
select * from t_score where shuxue between 50 and 90; 

select * from t_score where shuxue >= 50 and shuxue <= 90;

-- 这两个语句一样的效果

1
2
3
4
5
6
-- in ：包含哪些值，和逻辑or类似
select * from t_score where shuxue in (90,82,30);

select * from t_score where shuxue = 90 or shuxue = 82 or shuxue = 30;

-- 这两个语句一样的效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- ===============group by======================

-- group by 分组汇总，根据 by 指定的规则对数据进行分组
-- 注意：group by 子句中，必须要指定需要分组的列，而且必须是出现在
--      select 之后除了聚合函数之外所有的列。
-- 简言之，前面出现的后面必须出现
-- 
-- having : 把分组之后的数据进行筛选和过滤，类似于where的作用
-- 注意：聚合函数必须要写在having后面，绝对不能写在where后面！！
-- 
-- 
-- where: 在分组之前，把不符合条件的数据过滤掉，where条件中绝对不能写聚合函数！！！


-- 条件查询的顺序：
where ------>  group by  -----> having

select sname,count(1) from t_stu GROUP BY sname;

select count(1) from t_stu;

select *,count(1) from t_score GROUP BY shuxue;

-- 错误语法，where后面不能有聚合函数
-- !!!select *,count(1) from t_score where count(1)=1 GROUP BY shuxue;  


-- ===================rollup====================

	-- 在group by分组字段的基础上再进行统计数据。
select *,count(1) from t_score GROUP BY shuxue with ROLLUP;


-- ===================coalesce==================

	-- 如果需要修改rollup新生成的统计数据的名称用coalesce
    -- coalesce(a,b,c);
        参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
	-- 参考：https://www.cnblogs.com/phpper/p/9384614.html

select coalesce(shuxue,'总计') as '数学',count(1) from t_score GROUP BY shuxue with rollup;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- ==================子查询=================
/*什么是子查询?
	1、当一个查询语句嵌套在另外一个查询语句中称之为子查询。
    2、子查询的结果作为外查询的条件
	  在使用子查询时，当子查询的返回值只有
	  一个时用=，当子查询的返回值有多个时用in
      子查询能使用的操作符：
       in  
       not in 
       =  !=
	   >  <  <>
	返回1条或者多条数据都可以使用in
*/
select * from t_stu where sid = (select sid from t_score where shuxue = 92);

select * from t_stu where sid in (select sid from t_score where shuxue = 12);

select * from t_stu where sid in (select sid from t_score where shuxue in (92,12));

select * from t_stu where sid not in (select sid from t_score where shuxue in (92,12));


-- ==============嵌套子查询================
/*
		子查询包含一个子查询，
    嵌套子查询包含多个子查询。
*/

select * from t_stu where sid in (select sid from t_score where shuxue in (select shuxue from t_score where yingyu=13))

1
2
3
4
-- 子查询和delete,update,子查询可以它们的条件表达式提供结果集
delete from t_score where sid in (select sid from t_stu where sname='gaofei');

update t_stu set sname='feifei' where sid in (select sid from t_score where shuxue = 12);

1
2
3
4
5
6
7
-- 不存在数学=100数据的时候，显示所有的学生信息
select * from t_stu where not exists (select * from t_score where shuxue = 100); 


-- 比较~：
select * from t_stu where sid not in (select sid from t_score where  shuxue = 100);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- ====================等值连接========================

/*
		总结：等值查询技巧
		通常把要查询的列放在select 后面，列中间用逗号分开，把涉及到的
		表放在from后面用逗号分开并把每个表都取别名，接着把几个表的主外键
		关系放在where后面，然后用=号连接，还可以加上其他条件。
    最后如果还可以根据需要加上分组语句。
*/
select s.sid ,s.sname from t_stu s,t_score r where s.sid = r.sid and r.shuxue = 12;
select * from t_stu s,t_score r where s.sid = r.sid and r.shuxue = 12;

-- 好代码：
select e1.* from emp e1,(select d.dname '部门名称',avg(e.epay) 'avg' ,e.did from dept d,emp e where d.did=e.did group by d.dname) e2 
where e1.did=e2.did and e1.epay>e2.avg;   -- 这里建立了一个虚拟表e2,然后将他们等值连接关联起来，e1表的工资大于其所在部门的平均工资，因为部门已经得到了关联。
)

-- ======================内连接=======================
-- 关键字inner join...on，只有左，右2个表相匹配的行才能在结果中显示，作用和等值连接一样。

select * from t_stu s inner join t_score r on s.sid = r.sid;


-- =====================左外连接======================
-- 关键字left join,左边的表的行全部显示，左边连接右边的值，右边的表如果没有匹配的值，显示为null

select * from t_stu s left join t_score r on s.sid = r.sid;

-- =====================右外连接=======================
-- 关键字right join, 右边的表的行全部显示，右边连接左边的，左边的表如果没有匹配的值，显示为null

select * from t_stu s right join t_score r on s.sid = r.sid;

-- =====================完全连接=======================
-- 关键字union, 用于合并两个或多个select语句的结果集。 左，右2个表的行都全部显示，如果没有匹配的值，显示为null
-- 合并的select语句的列数量、类型、顺序必须完全一样

select * from t_stu where sid in (1,2)
union
select * from t_stu where sid in (5,6)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
-- ==============================================================
-- 日期函数
select now(); -- 获取当前的日期时间
select curdate();	-- 获取当前的日期
select curtime();	-- 获取当前的时间


-- ==============================================================

  select date_add(指定日期,interval 年月日间隔数 年月日季选择);
    返回指定日期加上一个时间间隔后的日期
  
  select date_sub(指定时间,interval 年月日间隔数 年月日季选择);
    函数从日期减去指定的时间间隔。

select DATE_ADD(CURDATE(),INTERVAL 1 month); -- 下个月的今天
select DATE_ADD(CURDATE(),INTERVAL 1 quarter); -- 下个季度的今天
select date_add(curdate(),interval 1 year); -- 明年的今天
select date_add(curdate(),interval 1 day); -- 明天

select DATE_sub(CURDATE(),INTERVAL 1 month); -- 上个月的今天
select DATE_sub(CURDATE(),INTERVAL 1 quarter); -- 上个季度的今天
select date_sub(curdate(),interval 1 year); -- 去年的今天
select date_sub(curdate(),interval 1 day); -- 昨天

select date_add(curdate(),interval 10 day); -- 10天后
select date_add('2018-1-1',interval 356 day); -- 2018-1-1 356天后是哪一天


-- ==============================================================
datediff()

/*
语法格式：datediff(结束时间,起始时间)
返回起始时间和结束时间之间的天数。
*/
select DATEDIFF('2019-1-4',curdate())
select datediff(curdate(),'2019-1-5')

-- !!!select datediff(CURTIME(),'00:29:10') -- 101错误，应该不常用，只用日期就行了
-- 这个具体的时间后边查查 110 查查为什么时间不行

-- ==============================================================
date() 
 /*
 语法：date(日期时间表达式)
 提取日期或时间日期表达式中的日期部分。 
*/
select date(curdate());
select date(now());
select date(curtime());  -- 为Null,因为没有date日期
select date('2018-11-18 00:34:45');

-- ==============================================================
dayofweek(date)
     /*返回date是星期几(1=星期天,2=星期一,……7=星期六) */
select dayofweek(curdate());

select concat('今天是星期：',dayofweek(now())-1) 'dayofweek';

-- ==============================================================
dayofmonth(date) 
　   /*返回date是一月中的第几天(在1到31范围内)*/
select DAYOFMONTH(now());
select DAYOFMONTH(CURTIME()); -- 没日期结果为Null

-- ==============================================================
dayofyear(date) 
　   /*返回date是一年中的第几天(在1到366范围内)*/
select dayofyear(now());

-- ==============================================================
month(date) 
   　/*返回date中的月份数值*/ 
day(date)
     /*返回date中的天数数值*/ 
year(date)
     /*返回date的年份(范围在1000到9999)*/ 
quarter(date) 
    /*返回date是一年的第几个季度 */

select month(now());
select day(now());
select year(now());
select quarter(now());

-- ==============================================================
week(date,first)
 /*返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)*/
select week(now(),1);

-- ==============================================================
DATE_FORMAT(date,format) 
   /*根据format字符串格式化date值*/
select date_format(now(),'%y=%m=%d=%h=%m=%s');

-- ==============================================================
extract() 
   /*函数用于返回日期/时间的各个部分，比如年、季、月、日、小时、分钟等等。*/

select extract(year from now())；
select extract(month from now());
select extract(day from now());
select extract(quarter from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());

-- ==============================================================
timestampdiff() ： 计算两个日期的时间差函数
/*select timestampdiff(年月日季选择,起始时间，结束时间);*/
select timestampdiff(day,'2017-4-9','2017-8-9');
select datediff('2017-8-9','2017-4-9');

select timestampdiff(year,'2017-4-9','2018-8-9');

-- ==============================================================
last_day() 函数：返回月份中的最后一天
select last_day('2017-7-8');
select last_day(now());

-- ===========================小练习=============================
-- 今天是星期几
select dayofweek(curdate() - 1);
select dayofweek(curdate()) - 1;  -- 最好用第一种方法，这种方法周日显示的是0
-- 今天是第几季度
select quarter(curdate());
-- 本月一共有多少天
select day(last_day(curdate()));
-- 本月的第一天是星期几
select dayofweek(date_sub(curdate(),interval day(curdate())-1 day)) - 1;
-- 本周的周一是几号
select date_sub(curdate(),interval dayofweek(curdate()-1)-1 day);  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

-- ==============================================================
concat() 
/*   concat(str1,str2,…)  concat函数可以连接一个或者多个字符串
     concat_ws(x,s1,s2,...) 同concat(s1,s2,...)函数，但是每个字符串直接要加上x 
*/
select concat('a','b','c');
select concat_ws('*','a','b','c');  -- 添加了分隔符


-- ==============================================================
left（str, length）
/*
     从左开始截取字符串  
     说明：left（被截取字段，截取长度）
*/
select left('abc',2);

-- ==============================================================
right (str , length)
/*
     从右开始截取字符串  
     说明：left（被截取字段，截取长度）
*/
select right('abc',2);


-- ==============================================================
substring
截取字符串 
/*
     substring（str, pos） 从pos位置开始截取到末尾
     substring（str, pos, length） 从pos位置开始截取length位字符串
     说明：substring（被截取字段，从第几位开始截取） 
     substring（被截取字段，从第几位开始截取，截取长度）
*/
select substring('abcdefg',2);
select substring('abcdefg',2,2);

-- ==============================================================
char_length(s)  
/*
返回字符串s的字符数
*/
select char_length('abcdefg');

-- ==============================================================
insert(s1,x,len,s2) 
/*
将字符串s2替换s1的x位置开始长度为len的字符串
*/
select insert('abcdefg',2,2,'123'); -- 将2位置开始两位字符替换为123

-- ==============================================================
upper(s) 
/*
upper(s): 将字符串s的所有字母变成大写字母
*/
select upper('abcDEfgH');

-- ==============================================================
lower(s) 
/*
lower(s): 将字符串s的所有字母变成小写字母
*/
select lower('abcDEfgH');

-- ==============================================================
trim(s) ： 
/*
去掉字符串s开始和结尾处的空格
并不会去除末尾的换行符，所以如果想去除换行符那么：
	解决方法：
		UPDATE tablename SET field = REPLACE(REPLACE(field, CHAR(10), ''), CHAR(13), '');
		char(10):  换行符
		char(13):  回车符

	MySQL的trim函数没办法去掉回车和换行，只能去掉多余的空格，可以用MySQL的replace函数，解决掉这个问题
	缺点：会把字符串中间出现的换行也会替换掉

--100  疑问
	解决：
		UPDATE `tran` 
		SET `status` = '1' 
		WHERE trim( trim( 
		BOTH 'rn' 
		FROM content ) ) = '我爱你'

		用了两个trim，这样的好处是不会替换内容中间的换行和回车，
		只会处理头尾的空格换行回车，相当于php中trim函数的作用了。 

*/
select trim(' abc ');

reverse(s) ： 
/*
将字符串s的顺序反过来
*/
select reverse('abcdefg');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
abs(1) -- 取绝对值
ceil(x) -- 向上取整
floor(x) -- 向下取整
rand() -- 返回0-1的随机数，包含0不包含1
pi() -- 返回圆周率（3.141593）
round(x,y) -- 保留x小数点后y位的值，但截断时要进行四舍五入
truncate(x,y) -- 返回数值x保留到小数点后y位的值（与round最大的区别是不会进行四舍五入）
pow(x,y) 或 power(x,y) -- 返回x的y次方
sqrt(x) -- 返回x的平方根
mod(x,y) -- 返回x除以y以后的余数

select sqrt(4);
select mod(5.5,3);
select mod(5,2);
select pow(2,4);
select power(2,4);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/*
  创建索引
  语法：create index 索引名称 on 表名(字段名);
        alter table 表名 add index 索引名称(字段名);
*/
-- 案例
create index suoyin on t_stu(sid);

/*删除索引 
  语法： drop index 索引名称 on 表名；
  */
drop index suoyin on t_stu;
/*查看索引*/
show index from tblname;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
视图
(
/*
什么是视图 
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。
视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。
这样，用户可以不用看到整个数据库中的数据，而之关心对自己有用的数据。
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
视图的作用 ：
1.使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
2.增加数据的安全性，通过视图，用户只能查询和修改指定的数据。
3.提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。
创建视图时不能使用临时表。
即使表被删除，视图定义仍将保留。
定义视图的查询不能包含 以下语句：
order by
compute 子句
compute by 子句
into 关键字 
*/

/*
  创建视图
  语法：create view 视图名称 as 查询语句
*/
create view view_a as select s.sid,sname,shuxue,yingyu from t_stu s,t_score r where s.sid=r.sid;


/*查询视图
  语法：select * from 视图名称;
*/
select * from view_a;

/*删除视图 
  语法：drop view 视图名称;*/
drop view view_a;

/*向视图中添加数据
  语法：insert into 视图名称(字段1，字段2，...) values (值1，值2，....);
  不能添加数据到多个表,因为添加语句涉及到了多个基表
	添加数据的时候，如果涉及的基表除了插入字段外还有非空没有默认值字段，那么也不会成功
*/
insert into view_a(shuxue) values(66);
SELECT * FROM T_SCORE;

/*向视图中修改数据
  可以修改单表数据,不能修改多表数据
  update 视图名称 set 字段1=新值1 where 字段2=值2;
*/
update view_a set shuxue = 100 where sid = 1;
select * from view_a;

/*
  with check option 的使用
  语法：create view 视图名称 as 查询语句  with check option;
  --在创建视图时，可以使用with check option选项，
  --其作用是限定向视图添加或修改数据时，添加或修改的数据必须遵照创建视图时where之后的条件

		1.对于update,有with check option，要保证update后，数据要被视图查询出来 
		2.对于delete,有无with check option都一样 
		4.对于insert,有with check option，要保证insert后，数据要被视图查询出来 
		5.对于没有where 子句的视图，使用with check option是多余的
*/
create view view_b as select s.sid,s.sname,r.shuxue,r.yingyu from t_stu s,t_score r with check option;
drop view view_b;


/*修改视图定义
  语法： alter view 视图名称 as 查询语句
*/
alter view view_b as select * from t_stu with check option;
select * from view_b;