MySQL学习笔记-基础介绍

语法格式：

use 数据库名称；
create table table_name(
	属性名 数据类型 属性约束，
	......
)

或者

create table 数据库名称.table_name(
	属性名 数据类型 属性约束，
	......
)

use Students;
//学生表
create table Student(
	Sno int primary key auto_increment,
	Sname varchar(10) not null,
	Ssex varchar(2) check(Ssex='男' or Ssex='女'),
	Sage int check(Sage between 15 and 45),
	Sdept varchar(20) default('计算机系'),
	//Sroomid int unique,
	Sroomid int,
	//如果想给字段：Sroomid上的UK约束设置一个名字，可执行constraint语句
	constraint uk_Sroomid unique(Sroomid)
);

//课程表
Create table Cource(
	Cno varchar(10) primary key,
	Cname varchar(20) not null,
	Ccredit int check(Ccredit>0),//学分
	Semester int check(Semester>0),//学期
	Period int check(Period>0)//学时
)
//学生选课表
create table StudentCourse(
	sno varchar(7),
    	foreign key(sno) references Student(sno),
	cno varchar(10),
    	foreign key(cno) references Cource(cno),
	grade int,check(grade between 0 and 100),
	primary key (sno,cno)
)

 3.5.1、修改表名
alter table old_table_name rename new_table_name;

3.5.2、增加字段
alter table table_name add 属性名 属性类型;

//在表的第一个位置增加字段
alter table table_name add 属性名 属性类型 first;
//在表的指定字段后增加字段
alter table table_name add 属性名 属性类型 after 属性名;

3.5.3、删除字段
alter table table_name drop 属性名;

3.5.4、修改字段（修改数据类型、字段名）
//修改字段的数据类型
alter table table_name modify 属性名 新数据类型;
//修改字段名
alter table table_name change 旧属性名 新属性名 旧数据类型;
//同时修改字段名和数据类型
alter table table_name change 旧属性名 新属性名 新数据类型;
//修改字段顺序
alter tabel table_name modify 属性名1 数据类型 first/afler 属性名2;

not null (约束的值不能为空)
default (设置字段的默认值)
unique key(uk) (约束字段的值唯一,不允许重复)
primary key(pk) (约束字段为表的主键，可作为该表记录的唯一标识)
auto_increment (约束字段的值自动增加)
Foreign key(fk) （约束字段的值为表的外键）

//设置主键
//如果想给Sno字段上的PK约束设置名字，可以执行constraint语句
Constraint pk_Sno prinmary key(Sno)

//多主键的情况给PK约束设置名字
Constraint pk_Sno_Sname prinmary key(Sno,Sname)

//自增列必须是primary key
alter table address address_id int primary key auto_increment;

//设置外键
语法格式：
create table table_name(
	属性名 数据类型
	constraint 外键约束名 foreign key(属性名1) references 父表名(主键-属性名2) 
)

1、查询默认存储引擎
show variables like 'default_storage_engine%';
2、修改默认存储引擎
通过mysqlworkbench或者修改数据库管理系统的配置文件/etc/my.cnf

MyISAM：由于该存储引擎不支持事务、也不支持外键，所以访问速度比较快。因此对事务完整性没有要求并以访问为主的应用适合此引擎。
InnoDB：由于该引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比MyISAM占用更多的磁盘空间。因此需要进行频繁的更新、删除操作，同时还对事务的完整性要求比较高，需要实现并发控制，此时适合该引擎。
MEMORY:该引擎使用内存来存储数据，因此数据访问速度快，但是没有安全上的保障（若数据库发送崩溃或重启，表中数据会消失），需要进行快速访问，则适合该引擎。

整形：tinyint、smallint、mediumint、int和integer、bigint
浮点型：float、double
日期和时间：date（年月日）、datetime（年月日时分秒）、timestamp（时间戳）、time（时分秒）、year（年份）
字符串：char（少量字符）、varchar（字符长度经常变换）
text：tinytext、text、mediumtext、longtext
binary: binary、varbinary
blob：tinyblob、blob、mediumblob、longblob

区别：
1、char和binary非常类似，但是前者只能存储字符数据，后者可以储存少量二进制数据（比如图片、音乐、视频文件等）
2、char和text非常类似，但是前者只能存储字符数据，后者可以储存大量二进制数据（比如图片、音乐、视频文件等）

insert 语句格式：
//其中的1，2，...n分别对应表中的列，表中定义了几列，insert语句就应该对应几个值，数据类型得一致
insert into 表名 values (值1，值2，...值n);

insert into Students.Student values
	(null,'胡慧','女',18,'会计',302);

//查询插入结果	
select * from Students.student;

insert into Students.Student values
	(null,'胡慧','女',18,'会计',302),
    (null,'胡集','男',19,'英语',209),
    (null,'何及','女',17,'机材',509)

语法格式：
//‘表名1’表示将获取到的记录查到哪个表中，‘表名2’表示从哪个表中查询记录
//‘列名列表1’表示为哪些列赋值，不设置表示所有列，‘列名列表2’表示从表中查询到哪些列的数据
insert into 表名1 (列名列表1) select 列名列表2 from 表名2 where 条件表达式

语法格式：
update table_name set column1=value1,[column2=value2]...
	where 条件表达式

解除安全模式：set sql_safe_updates=0;
恢复安全模式：set sql_safe_updates=1;

-- 在安全模式下，where 子句中column必须是主键才能update操作
update Students.Student set Sdept='材料工程' where Sno=2;

-- 解除安全模式
set sql_safe_updates=0;

-- 非安全模式下update
-- update Students.Student set Sdept='飞行器设计' where Sname='何及';

// 多列之间用逗号隔开
update Students.Student set Sdept='飞行器设计',Sroomid=309 where Sname='何及';

update actor set last_update = '2023-8-01' where actor_id in
	(select actor_id from film_actor where film_id=1);
	
注意：
1、在一个单独的update语句中，MySQL不会对同一行做两次更新。
这是一个内置限制，可以使在更新中写入日志的数量减至最小。

2、使用 set 关键字 可以引入列的列表或各种要更新的变量名。其中 set关键字
引用的列必须明确。

3、如果子查询没有返回值，必须在子查询中引入 in、exists、any、all等关键字。 

语句格式：
delete table_or_view from table_sources where 条件语句;

1、table_or_view：是从中删除数据的表或视图的名称
2、FROM：table_sources子句为需要删除数据的表名称。它使 delete可以先从其他表查询出一个结果集，然后删除 table_sources中与该查询结果相关的数据

//删除单行数据
delete from actor where actor_id=204;
//删除多行数据
delete from actor where actor_id>200;
//删除表中所有数据
delete from actor;

语法如下：
truncate table table_name;

select [all | distinct] select_list from table_or_view_name
[where <查找条件>]
[group by <分组条件>]
[having <查询条件>]
[order by <排序方式>] [asc | desc]

括号[]的内容是可选的

语法格式：
select * from table_name;

select * from Students.Student;

语法格式：
select 列名列表 from table_name;

select Sno,Sage,Sdept from Students.Student;

select Sno '学号',Sage '年龄',Sdept '学院' from Students.Student;

select Sno as '学号',Sage as '年龄',Sdept as '学院' from Students.Student;

select distinct * from Students.Student;

语法格式：limit 初始位置,查询记录数量;
//查询表中5条记录，指定从第 3条 记录开始显示
select * from Students.Student limit 2,5;

语法格式：limit 显示记录数;
//查询表前 2条 记录
select * from Students.Student limit 2;

select * from Students.Student where Sno=2;

//查询Saddress的值以 江西 开头，以 ... 结尾的全部记录
// '%'表示任意长度的字符串，'_'只能表示单字符
select * from Students.Student where Saddress like '江西%...';

语法格式：
group by 字段名 [having 条件表达式] [with rollup]

select * from Students.Student group by Sroomid;

//使用聚合函数 count(),计算Sroomid相同的有多少列
select *,count(Sroomid) from Students.Student group by Sroomid;

//使用聚合函数 group_concat(),查询每个寝室里住了哪些学生
select Sroomid,group_concat(Sno) from Students.Student group by Sroomid;

//对Sroomid分组查询，使用having筛选Sno>2的记录
select * from Students.Student group by Sroomid having Sno>2;

//使用where筛选Sno>2的记录，然后对Sroomid分组
select * from Students.Student where Sno>2 group by Sroomid;

//报错：this is incompatible with sql_mode=only_full_group_by，这是由于MySQL触发了sql_mode的only_full_group_by条件
//该条件要求：当对一个或多个字段进行分组后，所查询的字段（即select后跟的字段名），必须是分组所依据的字段和经过聚合函数聚合后的新字段。这个逻辑是合理的之所以要分组就是要探究该组内的信息，既然是组内信息，就必须对全组数据进行统一处理，单独拎某一个数据是不合理的。

//报错
select * from Students.Student group by Sroomid;

//所查询的字段和分组所依据的字段一致,正常查询
select Sroomid from Students.Student group by Sroomid;

//使用聚合函数max(),正常查询
//聚合函数包括max、min、avg、sum、count等
select max(Sno),Sroomid from Students.Student group by Sroomid;

解决办法：
1、从sql_mode端解决问题。
(1) 临时关闭only_full_group_by模式，这种方法通过修改系统变量，重启数据库后失效。

首先查看下当前的sql_mode：

show VARIABLES LIKE 'sql_mode';

//修改后需在新的回话里验证原SQL
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

(2)永久关闭only_full_group_by模式，这种方法需要在mysql的配置文件里修改，然后重启。

Step 1 找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)

Step 2: 在上述文件内的[mysqld]后追加(sudo vim /etc/my.cnf)

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

Step 3:保存配置文件后，重启Mysql即可。

语法格式：
//order_expression 表示排序列或列的别名和表达式
order by order_expression [asc | desc]

//查询Sno在[1,10]间的记录，按Sroomid升序排序
select * from Students.Student where Sno between 1 and 10 order by Sroomid;

语法格式：
select * from table1,table2;

//查询 Student表和Cource表 每一行两两组合成新的一行的结果
select * from Students.Student,Students.Cource;

语法格式：
//列名列表:可以来自不同的表
select 列名列表 from 表1,表2 where 表1.列1 = 表2.列2;

//查询Student和StudentCourse表的记录，但是必须Student.Sno = StudentCourse.Sno
select * from Students.Student,Students.StudentCourse where Students.Student.Sno = Students.StudentCourse.Sno;

语法格式：
select 列名列表 from 表名1 inner join 表名2 on 表名1.列名1=表名2.列名2;

select 列名列表 from 表名1 cross join 表名2 on 表名1.列名1=表名2.列名2;

语法格式：
//列名列表:可以来自不同的表
//左连接，join左边的表是主表，右连接，join右边的表是主表
//以某一个表为主表，进行关联查询，不管能不能关联上，主表的数据都会保留，关联不上的以null显示
select 列名列表 from 表名1 left | right join 表名2 on 表名1.列名1=表名2.列名2;

select * from Students.Student left join Students.StudentCourse on Students.Student.Sno = Students.StudentCourse.Sno;

基本语法：
select 语句1
union | union all
select 语句2
union | union all
select 语句n
[order by order_expression]

//联合查询Student表中Sno在[1,3]和[5,7]这两个范围的记录
select * from Students.Student where Sno between 1 and 3
	union select * from Students.Student where Sno between 5 and 7

//将Student表中年龄小于19的Sname和Teachers表中的Tname合并
select Sname from Students.Student where Sage<19
	union select Tname from Teacher;

select * from Students.Student where Sno=(select max(Sno) from Students.StudentCourse where grade>90 or cno>3014);

select * from Students.Student where Sno in (select Sno from Students.StudentCourse where grade>90 or cno>3014);

语法格式：

列名 regexp '匹配方式'

创建索引可提供查询速度，但过多的索引则会占据许多磁盘空间。因此创建索引前要权衡利弊。
1、设置唯一性约束的字段，可更快通过该索引查到某条记录，比如学生表中的学号具有唯一性。
2、为经常需要排序、分组、联合操作的字段建立索引；比如经常需要order by、group by、distinct、union等操作的字段，排序会浪费很多时间，如果为其建立索引，可有效地避免排序操作。
3、经常被查询的字段，即再where子句中出现的字段。
4、尽量使用数据量少的索引；如果索引值很长，那么查询的速度会受到影响。列如对一个char(100)类型的字段进行全文检索需要的时间肯定比char(10)字段需要的时间多。
5、尽量使用前缀来索引；如果索引字段的值很长，最好使用值的前缀来索引。

1、在查询中很少被使用的索引
2、拥有许多重复值的字段

1、普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等）。该类型索引可创建在任务数据类型字段上。

2、唯一索引，即创建索引时，限制索引的值必须是唯一的。通过该类型索引可更快地查询某条记录。
* 所谓自动索引，指在表里设置完整性约束时，该表会被系统自动创建索引。
* 所谓手动索引，指手动在表上创建索引。
* 当设置表中某个字段设置主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

3、全文索引，主要关联在数据类型为char、varchar和text的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
* MySQL从3.23.23开始支持全文索引，只能在存储引擎为 MyISAM 的数据库表上创建全文索引。默认情况下，全文索引的搜索执行方式不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。

4、单列索引，在表的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只有保证该索引只对应一个字段就可以。

5、多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用。

6、空间索引，使用 spatial 参数可以设置索引为空间索引。只能建立在空间数据类型上，这样可提供系统获取空间数据的效率。MySQL中空间数据类型包括geometry、point、linestring、polygon等。目前只有MyISAM 存储引擎支持空间索引，而且索引的字段不能为空值。

语法格式：
create table table_name(
    属性名 数据类型 [完整性约束条件]
	[unique | fulltext | spatial] index|key [索引名](属性名1 [(长度)] [Asc|Desc])
) 
注意：MySQL支持的存储引擎每个表至少支持16个索引，总索引长度至少为256字节。

说明：index或key指定字段为索引，索引名一般index_开头，‘属性名1’表示用来指定所关联的字段名，‘长度’指定索引的长度，ASC|DESC指定升序或降序排序。

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	index index_deptno(deptno)
);

//验证表中索引是否创建成功
show create table t_dept;

//验证表中索引是否被使用,执行结果中若possible_keys和key字段的值都是创建的索引名index_deptno,说明索引已存在，而且已经开始启用
explain
	select * from t_dept where deptno=1;

1、创建普通索引
create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	index index_deptno(deptno)
);

2、创建唯一性索引
语法格式和创建普通索引一样，在 index 前面加上unique就可以。

3、创建全文索引
create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	fulltext index index_deptno(deptno)
) engine=myisam;

4、创建多列索引
语法格式：
create table table_name(
    属性名 数据类型
	index|key [索引名](属性名1 [(长度)] [Asc|Desc],
						.....
						属性名1 [(长度)] [Asc|Desc]
	)
) 

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	index index_deptno_loc(deptno,loc)
);

5、创建空间索引
平时很少用到。

语法格式：
create [unique | fulltext | spatial] index 索引名
	on 表名(属性名 [(长度)] [Asc|Desc])

1、创建单列索引
//查询时可以只查询 dname 的前4个字符，而不需要全部查询。
create index index_deptno on t_dname(dname(4));

2、创建唯一性索引
语法格式和创建普通索引一样，在 index 前面加上unique就可以。

3、创建全文索引

语法格式和创建普通索引一样，在 index 前面加上fulltext就可以。

4、创建多列索引
create index index_deptno on t_dept(deptno, dname);

5、创建空间索引
语法格式和创建普通索引一样，在 index 前面加上spatial就可以。

语法格式：
alter table table_name
	add [unique | fulltext | spatial] index|key 索引名(属性名 [(长度)] [Asc|Desc])
	
alter table t_dept
	add index index_deptno(deptno);

语法格式：
drop index index_name
	on table_name;

语法格式：
alter table 表名 add [unique | fulltext | spatial] index
	索引名(属性名 [(长度)] [Asc|Desc])

1、集中数据。简化用户的数据查询和处理。有时需要将多表数据集中到一起方便用户的数据查询和处理。
2、简化操作。用户不必了解复杂的数据库表结构，并且表的更改不影响用户对数据库的使用。
3、实现复杂的查询需求。复杂的查询可以进行问题分解，然后将创建多个视频获取数据，最后将视图联合起来得到需要的结果。
4、简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。
5、便于数据分享。各用户不必都定义各存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。

语法格式：
create [algorithm = {undefined | merge | temptable}]
view view_name as
select column_name(s) from table_name
[with [caccaded | local] check option ]

各个参数的含义如下：
（1）algorithm：可选参数，表示视图选择的算法。
（2）undefined：MySQL 将自动选择所要使用的算法。
（3）merge：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应的部分。
（4）temptable：将视图的结果存入临时表，然后使用临时表执行语句。
（5）view_name：指创建视图的名称，可包含其属性列表。
（6）column_name(s)：指查询的字段，也就是视图的列名。
（7）table_name：指从哪几个表中获取数据
（8）with check option：可选参数，表示更新视图时要保证在视图的权限范围内。
（9）cascaded：在更新视图时满足所有相关视图和表的条件才进行更新
（10）local：在更新视图时满足该视图本身定义的条件即可更新。

注意：如果给定了 or replace 子句，该语句还能替换已有的视图，select_statement 它给出了视图的定义，该语句可从基表或其他视图进行选择。

系统提供的默认格式：
create view view_name
	as 查询语句;

//创建视图student_view	
create view student_view as select * from Students.Student;

//查看视图的结构
desc student_view;

//查询student_view视图数据
select * from student_view;

语法格式：
show table status like '视图名';

show table status like 'student_view';

语法格式：
show create view 视图名;

show create view student_view;

语法格式：
create or replace [algorithm = {undefined | merge | temptable}]
view view_name [(属性清单)] as
select column_name(s) from table_name
[with [caccaded | local] check option ];

各个参数的含义如下：
（1）属性清单：可选，指定了视图中各个属性的名词，在默认情况下与 select 语句中查询的属性相同。
（2）create or replace：可以替换成 alter 修改视图。只能修改已存在视图，不会创建。

//修改视图，视图有sno,sage,sroomid
create or replace view student_view(sno,sage,sroomid) as 
select Students.Student.Sno,Students.Student.Sage,Students.Student.Sroomid 
from Students.Student;

update student_view set sroomid=302 where sno=4;

//因为向视图插入数据会使对应表也插入数据，但原表一条记录中有的列不允许为空，所以无法插入。
insert into student_view values(10,19,305);

delete from student_view where sno=2;

语法格式：
//使用if exists防止因不存在的视图而出错，给定了该子句时，将为每个不存在的视图生成NOTE.
drop view [if exists] view_name [,view_name1...]

drop view if exists student_view;

语法1：

case 
　　when e1
　　then v1
　　when e2
　　then e2
　　...
　　else vn
end
　　case表示函数开始，end表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

select case 
　　when 1 > 0
　　then '1 > 0'
　　when 2 > 0
　　then '2 > 0'
　　else '3 > 0'
　　end
->1 > 0

语法2：

case expr 
　　when e1 then v1
　　when e1 then v1
　　...
　　else vn
end
　　如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。

select case 1 
　　when 1 then '我是1'
　　when 2 then '我是2'
else '你是谁'

select inet_aton('192.168.0.1')
    ->3232235521
    
select inet_ntoa(3232235521)
    ->192.168.0.1

select charset('abc')
    ->utf-8    

select charset(convert('abc' using gbk))
    ->gbk

select cast('342' as unsigned integer) + 1
    ->343
//mysql能默认转换
select '123' + 1
    ->124

select cast(now() as date)
　　->2023-11-30

select password('123')

select md5('432')

select encode('123','fdf')
   
select decode(';vx','fdf')
   

//加密
select TO_BASE64(AES_ENCRYPT('暖乎乎123','323241'));

//解密
//结果是blob类型，显示不了
select aes_decrypt(FROM_BASE64('uSWupDerTa8oLm0wijcmiA=='),'323241');

//可以 使用 convert 设置 字符集为utf-8,就可以正常显示
//这种转换只适用于存储文本类型数据的 blob 列
SELECT convert(aes_decrypt(FROM_BASE64('uSWupDerTa8oLm0wijcmiA=='),'323241') using utf8) as text_data