Learning note
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 2^{8} - 1 28−1 bytes
- varchar:变长字符串,最大 2 16 − 1 2^{16} - 1 216−1 bytes
- tinyblob: 二进制字符串,最大 2 8 − 1 2^{8} - 1 28−1 bytes
- tinytext: 文本字符串,最大 2 8 − 1 2^{8} - 1 28−1 bytes
- blob: 二进制字符串,最大 2 16 − 1 2^{16} - 1 216−1 bytes
- text: 文本字符串,最大 2 16 − 1 2^{16} - 1 216−1 bytes
- mediumblob: 中长二进制字符串,最大 2 24 − 1 2^{24} - 1 224−1 bytes
- mediumtext: 中长文本字符串,最大 2 24 − 1 2^{24} - 1 224−1 bytes
- longblob: 长二进制符串,最大 2 24 − 1 2^{24} - 1 224−1 bytes
- longtext: 长文本字符串,最大 2 32 − 1 2^{32} - 1 232−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;
可以看出,可怜老师还是第一,但由于其整容,也时常被诟病。