MySQL学习笔记（数据类型, DDL, DML, DQL, DCL）

1、前言

MySQL对于缩进的要求很宽松，随意换行，加空格、缩进等都无所谓。再window系统下对还不区分大小写。
每段代码都以分号为结束标志。
注释语法和Python一样，都是用#。
一些基本逻辑符号兼容Python和C的形式。比如”或“可以用" or “，也可以用” || "。

2、数据类型

2.1、数值类型

• tinyint: Occupy 1 byte;
• smallint: Occupy 2 bytes;
• mediumint: Occupy 3 bytes;
• int: Occupy 4 bytes;
• bigint: Occupy 8 bytes;
• float: Occupy 4 bytes;
• double: Occupy 8 bytes;
• decimal(M, D)：取决于M和D。D是总长度，M是小数位个数。eg: decimal(4, 2)的范围是(-99.99, 99.99)。

MySQL里面定义一个变量的类型和许多程序语言都不一样，它是将变量类型放在变量后边的。

name int;

而如果想定义一个无符号的变量类型，也得把unsigned放在最后，如下：

name int unsigned;

2.2、字符串类型

• char：定长字符串，最大$2^8-1$ bytes
• varchar：变长字符串，最大$2^{16}-1$ bytes
• tinyblob: 二进制字符串，最大$2^8-1$ bytes
• tinytext: 文本字符串，最大$2^8-1$ bytes
• blob: 二进制字符串，最大$2^{16}-1$ bytes
• text: 文本字符串，最大$2^{16}-1$ bytes
• mediumblob: 中长二进制字符串，最大$2^{24}-1$ bytes
• mediumtext: 中长文本字符串，最大$2^{24}-1$ bytes
• longblob: 长二进制符串，最大$2^{24}-1$ bytes
• longtext: 长文本字符串，最大$2^{32}-1$ bytes

char和varchar的使用方法如下，其中char(10)表示无论name多长，都会占用10个字节，而varchar(10)则表示name所占的空间又自己决定，但最多不超过10个字节。
注：一个英文字母占1个字节，一个汉字占2个字节。

name char(10);
name varchar(10)

blob主要用于存放一些音频，图片文件之类的。text则主要存放一些长文本数据，比如论文等文本。

2.3、日期类型

• date：占3 bytes。格式为yyyy-mm-dd。eg：2024-04-06
• time：占3 bytes。格式为hh: mm: ss。eg：14:10:59
• year：占1 bytes。格式为yyyy。eg：2024
• datetime：占8 bytes。格式为yyyy-mm-dd hh:mm:ss。eg：2024-04-06 14:10:59
• timestamp：占4 bytes。从1970-01-01开始计时，以秒的形式记录。可以防止夸时区产生的时间问题。

在MySQL里面，可以输入

select now();					#获得当前年月日+时间
select curtime();				#获得当前时间

然后用year，date，time等去过滤我们想要的信息。

select year(now());
select date(now());
select time(now());

3、DDL

DDL(Database Definition Language)：是MySQL中对于数据库、表、字段进行定义的语言。常用的语句如下。

总览数据库/表

show databases;
show tables;

切换数据库

use <database_name>;

查看表内容

desc <table_name>;

创建数据库/表

create database [if not exists] <database_name> [charset = (utf8/utf8mb4)];

creat table [if not exists] <table_name>(
feild_name, type, [constrained condition] [comment 'annotation'],
...
);

删除数据库/表

drop database <database_name>;
drop table <table_name>;

添加字段

alter table <table_name> add <filed_name type> [comment 'annotation'] ;

删除字段

alter table <table_name> drop <field_name>;

表的重命名

rename table <table_name1> to <table_name2>;

修改字段名（以及对应的数据类型）

alter table <table_name> Change <original_field_name> <object_field_name type>;

4、DML

DML(Data Manipulation Language)主要用于字段中的数据进行增、删、改的操作。

往字段里写入具体内容

insert into <table_name(field_name)> values(<concrete_values>);

也可以同时插入多条数据

insert into <table_name> (field_name1, field_name2, field_name3, ...)
values
    (value1A, value2A, value3A, ...),
    (value1B, value2B, value3B, ...),
    (value1C, value2C, value3C, ...),
    ...;

修改字段内容

update <table_name> set <filed_name = concrete_values> [where file_name = ...];

删除字段内容

delete from <table_name> [where field_name = ...];

或者

truncate <table_name> [where field_name = ...] 

delete和truncate的原理不同，前者只是删除表里的内容，而后者则是删除整张表之后再重新建立。

5、DQL

DQL(Data Query Language)主要用于对表中内容的查询。在实际项目当中，DQL运用的频率远高于DDL, DML, DCL，且玩的花样也是最多的。

5.1、基本查询

select
	[all | distinct]					#是否去掉重复值
	<field_name1> [as alias1],			#选择字段
	<field_name2> [as alias2],
	...
from table_name1 [as table_alias1], table_name2 [as table_alias2] ...		#选择表
[where]									#筛选条件
[group by ... [having...] ]				#对内容进行分组
[order by <field_name> [asc | desc]]	#排序
[limit]									#限制只能某列显示，或者显示多少行。

以上这些语句的执行顺序如下
1、from：先定位那张表
2、where：筛选条件
3、group by … having…：分组字段列表，分组之后满足having后面条件的数据才被显示出来
4、select：开始选择字段
5、order by：对字段进行排序
6、limit：对内容显示量进行限制

5.2、聚合查询

聚合查询时指利用以下函数对查询数据进行计算、取极值、平均值等操作。

• count()：
• sum()
• max()
• min()
• avg()

比如一个计算一个列表中所有人的岁数之和。

select sum(age) from <table_name>;

当然也可以同时对很多字段进行聚合查询

select sum(<field_name1>) sum(<field_name2>) from <table_name>;

当然也不一定非要在select后面出现，任何你需要计算的地方都可以用上聚合函数。

5.3、正则表达式

正则表达式时匹配字符串的一种规则，在MySQL中常用的匹配字符有以下这些。

• “ ^ ”：匹配字符串的首个字符，比如"^n"就可以匹配字符串"name"。
• “ $ ”：匹配字符串的最后一个字符，比如"$e"就可以匹配字符串"name"。
• “ . ”：匹配除"\n"之外的所有字符，比如".a"就可以匹配字符串"name"。
• " […] “：匹配所包含的任意一个字符，比如” [ab] “可以匹配字符串"avnidn”。
• " [^…] “：匹配未包含的合集中的字符的字符串，比如” [ ^ae ] “就可以匹配字符串"bcd”，但不能匹配字符串"abc"。
• p1|p2|p3：" | “或的意思，即只要字符串中出现p1, p2, p3其中任意一个都可以匹配成功。比如” a|b"可以匹配字符串"agent"。
• “ * ”：匹配子表达式零次或多次，比如" ab* “可以匹配字符串"a"和字符串"abs”。
• “ + ”：匹配子表达式一次或多次，比如"ab+“可以匹配字符串"abs”，但不可能匹配字符串"a"。
• {n}：匹配字符串中某个字母n次，比如"L{2}“可以匹配字符串"HELLO”。
• {n, m}：匹配字符串某字母出现次数的区间，比如”a{2, 3}“可以匹配字符串"banana"，但不能匹配"agent"。

最后，在查询语句中需要借助regexp来判别，比如需要查询一张表中谁的名字以a开头。

select * from <table_name> where name regexp "^a";

6、DCL

DCL(Data Control Language)主要是用户权限、创建、删除层面的控制语言。

查询所有用户

use mysql;					#先切换到总数据库
select * from user;

查询当前登录的用户

select current_user;

切换用户

这一步适用于在cmd操作的用户。在ide操作的可以通过ide设置。
首先得推出当前用户。

exit;

再重新登录

mysql -u '<user_name>' -p;

创建用户

MySQL中，是通过用户名和主机名来唯一定位一个用户的。如果想要求该用户只能在本机访问，那主机名便设置为localhost，反之若想在任意主机访问，则用通配符“ % ”即可。

create user '<user_name>'@'<host_name>' identified by '<password>';

修改用户密码

alter user '<user_name>'@'<host_name>' identified with mysql_native_password by '<new_password>';

删除用户

drop user '<user_name>'@'<host_name>';

查询权限

常用的权限有select, insert, update, delete, alter, drop, create等。如果想要一次性指定所有权限，可以用all。

show grants for '<user_name>'@'<host_name>';

授予权限

对某个数据库中的某个表进行授权，如果要所有数据库和所有表，也可以all privileges代替之。

grant <permission_name> on <database_name.table_name> to '<user_name>'@'<host_name>';

多个权限也可以用逗号隔开。

撤销权限

revoke <permission_name> on <database_name.table_name> from  '<user_name>'@'<host_name>';

7、综合练习

程序目的：

• 新建一个只适用于当前主机的用户，密码为123。授权其可对数据库进行读写操作。
• 创建一个名为teacher_schema的数据库，并在其中再创建一张名为teacher的表，里面存放各位老师的名字、身高、岁数、出生日期、三围等情况。
• 进行各种花式查询。

第一步：登录root用户，并且创建一个叫teacher_schema的数据库。

mysql -u root -p;
create database if not exists teacher_schema charset = utf8mb4;

第二步：创建一个叫yuquan的用户。

use mysql;
create user 'yuquan'@'localhost' identified by '123';
grant all privileges on teacher_schema.* to 'yuquan'@'localhost';

第三步：退出root用户，重新以yuquan这个用户登录

exit;
mysql -u yuquan -p;

第四步：在teacher_schema中创建一张名为teacher_table的表，其中存放四个字段。

use teacher_schema;
create table if not exists teacher_table(
    name varchar(30),
    age tinyint unsigned,
    height char(5),
    birthday date,
    cup char(3)
);

第五步：开始插入各位老师的名字。

insert into teacher_table values
    ("铃村爱里", 30, '152cm', '1993-09-24', '32D'),
    ("凉森玲梦", 26, '160cm', '1997-12-03', '34D'),
    ("河合明日菜", 26, '158cm', '1998-03-21', '35H'),
    ("楪可怜", 23, '148cm', '2001-03-28', '31H'),
    ("高桥圣子", 30, '161cm', '1993-05-13', '34G'),
    ("伊藤舞雪", 26, '160cm', '1997-11-30', '35F'),
    ("神宫寺奈绪", 27, '160cm', '1997-02-15', '34D'),
    ("三上悠亚", 30, '159cm', '1993-08-16', '33F'),
    ("葵司", 33, '163cm', '1990-08-14', '35E'),
    ("初川南", 29, '156cm', '1995-01-19', '34D'),
    ("明里䌷", 26, '155cm', '1998-03-31', '31B'),
    ("三宫春", 25, '152cm', '1998-05-04', '35G'),
    ("美谷朱里", 26, '166cm', '1997-04-15', '33E'),
    ("天海翼", 36, '160cm', '1988-03-08', '33E'),
    ("水卜樱", 26, '152cm', '1997-11-30', '31G');

最后，进行各种查询练习。
1、查询整个列表。

select * from teacher_table;

2、查询共多少老师。

select count(*) from teacher_table;			# print 15

3、按岁数降序查询，且这查询30岁以下的。

select name, age from teacher_table where age < 30 order by age desc;

可以看出，可怜老师岁数最小。

4、查询各位老师的cup，按字母降序查询，如果字母一样，则按数字升序。

select name, cup, age from teacher_table
order by
    substring(cup, regexp_instr(cup, '[A-Za-z]')) desc,
    cast(substring(cup, 1, regexp_instr(cup, '[A-Za-z]') - 1) as unsigned) asc;

可以看出，可怜老师还是第一，但由于其整容，也时常被诟病。