学习 MySQL

Mysql:

mysql 指令末尾需要带分号，否则无法执行指令

修改密码

alter user user() identified by 新密码
# 或
# 类型有 localhost、% 
set password for '用户名'@'类型' = '密码'

数据库操作

查看数据库

show databases;

查看当前数据库下的数据表

show tables;

变更当前数据库

# DATABASENAME 需要变更的数据库名
use DATABASENAME

创建数据库

# DATABASENAME 需要创建的数据库名称
create database DATABASENAME

删除数据库

# DATABASENAME 需要删除的数据库名称
drop database DATABASENAME

表操作

创建表

# TABLENAME 需要创建的数据表名称
create table TABLENAME

表格式

# 创建 表 TABLENAME
# TABLENAME 表名称 
# 括号内为 表的格式操作
create table TABLENAME (
    # COLNAME 字段名称
    # CLASS() 类型 括号内为数据长度
    # 可选的 NOT NULL 表示为 数据不为空
    # 可选的 DEFAULT '默认值' 为设置默认值
    COLNAME CLASS() 

    # 设置表的主键
    # 括号内 为 需要设置为 主键的 COLNAME
    PRIMARY KEY()
);

查看表格结构

# TABLENAME 为需要查看的表名称
desc TABLENAME

删除表

# 删除 表 TABLENAME
# TABLENAME 为 需要删除的表名称
drop table TABLENAME

重命名表

# TABLENAME 需要重命名的表名称
# NEW_TABLENAME 新表名称
alter table TABLENAME, RENAME() NEW_TABLENAME

选取表数据

# 选取 COLNAME 的数据 它们来自于表 TABLENAME

# 可以使用 * 表示选取表中的所有 字段
# COLNAME 表示 需要选取的 字段名称
# TABLENAME 表示 需要选取数据的 表
select COLNAME from TABLENAME

插入表数据

# 插入数据 VALUENAME 到 TABLENAME 中的 KEYNAME
# KEY_VALUE 为 需要插入的数据
# KEYNAME 为 需要插入的数据的字段名称
# TABLENAME 为 需要插入数据的表名称
insert into TABLENAME (KEYNAME) value (KEY_VALUE)

更新表数据

# 更新 表 TABLENAME 设置字段 COLNAME 的值变更为 NEW_VALUE 条件为 当 COLNAME 的值为 COL_VALUE 时执行
# TABLENAME 为 需要更新的表名称
# COLNAME 为 需要变更数据的字段名称
# NEW_VALUE 为 需要更新的值
# COLNAME 为 条件字段名称
# COL_VALUE 为 条件值

# where 为 条件
update TABLENAME set COLNAME='NEW_VALUE' where COLNAME=COL_VALUE

删除表数据

# 删除 来自 TABLENAME 的 COLNAME=COL_VALUE 的数据
# TABLENAME 为 需要删除数据的表名
# COLNAME 为 需要删除数据的字段名称
# COL_VALUE 为 需要删除数据的条件值
delete from TABLENAME where COLNAME=COL_VALUE

添加表结构

# 对 表 TABLENAME 添加 COLNAME 字段 数据类型为 CLASS
# TABLENAME 为 需要添加表结构的表名称
# COLNAME 为 需要添加的字段名称
# CLASS 为 需要添加的字段的数据类型
alter table TABLENAME add COLNAME CLASS

修改表字段类型

# 对 表 TABLENAME 的 COLNAME 的数据类型 修改为 CLASS
# TABLENAME 为 需要修改的表名称
# COLNAME 为 需要修改数据类型的字段名称
# CLASS 为 新的 字段数据类型
alter table TABLENAME modify COLNAME CLASS

修改表字段名称

# 修改 表 TABLENAME 变更 COLNAME 为 NEW_COLNAME 类型为 CLASS
# COLNAME 为修改的字段名
# NEW_COLNAME 为修改的新字段名
alter table TABLENAME change COLNAME NEW_COLNAME CLASS

操作符

去重

distinct
# 选取所有不重复的 COLNAME
# 如果 distinct 后还有字段，则会一同去重
# distinct 后的字段一，字段二... 同时相同才会去重
select distinct COLNAME from TABLENAME

排序

order by
# COLNAME1, COLNAME2, COLNAME3 为 需要查询的字段
# TABLENAME 为需要查询的表
# SORTCOLNAME 为需要排序的字段名
# SORTHANDLE 为排序方式
# DESC 为降序
# ASC 为升序
# mysql 中 默认升序
select COLNAME1, COLNAME2, COLNAME3 from TABLENAME where order by SORTCOLNAME SORTHANDLE
# 选取来自表 TABLENAME 的 COLNAME1， COLNAME2 的数据
# 以 COLNAME1 为主键进行降序排序
select COLNAME1, COLNAME2 from TABLENAME where order by COLNAME1 desc

别名

as
# 选取所有来自表 TABLENAME 的 COLNAME 数据，并为其赋予别名 NEW_COLNAME
select COLNAME as NEW_COLNAME from TABLENAME
# 选取所有来自表 TABLENAME 的 COLNAME 数据，并为其赋予别名 NEW_COLNAME
# 并在条件中使用 赋予的别名 NEW_COLNAME 进行筛选
select COLNAME as NEW_COLNAME from TABLENAME where NEW_COLNAME=VALUE_NAME

约束

对表的字段添加约束

# 对 表 TABLENAME 添加 约束 键 KEYNAME1 和 主键 KEYNAME2
# TABLENAME 为 需要添加约束的表名称
# KEYNAME1 为 需要添加的约束的名称 便于删除
# KEYNAME2 为 需要设置为主键的 字段
alter table TABLENAME add constraint KEYNAME1 primary key(KEYNAME2)

常用字段约束

# KEYNAME 为 需要设置的字段名

# 非空约束 字段不允许为空
not null
# 默认约束 赋予某字段默认值
default
# 唯一约束 设置该字段值唯一，允许为空，但只能有一个空值
unique key(KEYNAME)
# 主键约束 可唯一标识该表记录
primary key(KEYNAME)
# 外键约束 用于在两表之间建立关系 需指定引用主表哪一字段
foreign key(KEYNAME)
# 自动增长 设置该字段为自增字段，默认每条自增1
auto_increment
# 检查约束 example 表达式，在添加值时检查
# 如 check <id > 0 and id < 1000>
# 即 id 值 必须大于 0 且 小于 1000
check <example>

创建表时设置约束

create table TABLENAME (
    id int(4) auto_increment comment '唯一id',
    NAME char(10) not null default '昵称' comment '注释'
    degree decimal(4, 1) comment '成绩',

    # CONSTRAINTNAME 为 设置的约束名
    # 可通过 CONSTRAINTNAME 修改约束

    # 设置主键
    constraint CONSTRAINTNAME primary key(id), 
    # 设置范围为 0 到 100
    constraint CONSTRAINTNAME check(degree >= 0 and degree <= 100)
    # 设置 外键为 TABLENAME 表的 主键
    # 外键与主键的数据类型必须相同
    # TABLENAME 必须设置主键
    constraint CONSTRAINTNAME foreign key (id) references TABLENAME(primaryKEYNAME)
)

外键约束

# CONSTRAINTNAME 约束名
# KEYNAME1 指定的外键字段名
# TABLENAME 主表名
# KEYNAME2 主表的主键名

# 外键与主键的类型必须一致
# 主表必须拥有主键
# 主键不允许含空值
constraint CONSTRAINTNAME foreign key(KEYNAME1) references TABLENAME(KEYNAME2)

条件查询

与或非

# 与
# 选择所有来自表 TABLENAME 的 COLNAME=COL_VALUE 和 COLNAME=COL_VALUE 的数据
select * from TABLENAME where COLNAME=COL_VALUE and COLNAME=COL__VALUE

# 或
# 选择所有来自表 TABLENAME 的 COLNAME=COL_VALUE 或 COLNAME=COL_VALUE
select * from TABLENAME where COLNAME=COL_VALUE or COLNAME=COL_VALUE

# 非
# 选择所有来自表 TABLENAME 的 COLNAME 不为 COL_VALUE
select * from TABLENAME where NOT COLNAME=COL_VALUE

选取范围

# 选取所有来自表 TABLENNAME 的 COLNAME 在 MIN_VALUE 和 MAX_VALUE 范围内的数据
select * from TABLENAME where COLNAME between MIN_VALUE and MAX_VALUE

模糊查询

# 选取所有来自表 TABLENAME 的 COLNAME 的值包含 value,以 value 开头 的数据
# 通配符 % 表示任意多个字符
# 通配符 _ 表示任意一个字符
select * from TABLENAME where COLNAME like 'value%'

# 选取所有来自表 TABLENAME 的 COLNAME 的值不包含 value,以 value 开头 的数据
select * from TABLENAME where COLNAME not like 'value%'

正则表达式

# 选取所有来自表 TABLENAME 的 COLNAME 符合正则 regexp 的数据
select * from TABLENAME where COLNAME regexp 'REGEXP'

列表查询

# 选取所有来自表 TABLENAME 的 COLNAME 值被 item1， item2，item3 任意一项所匹配的数据
select * from TABLENAME where COLNAME in(item1, item2, item3)
# 选取所有来自表 TABLENAME 的 COLNAME 值不被 item1， item2，item3 任意一项所匹配的数据
select * from TABLENAME where COLNAME not in(item1, item2, item3)

空值查询

# 选取所有来自表 TABLENAME 的 COLNAME 值是空的数据
select * from TABLENAME where COLNAME is null
# 选取所有来自表 TABLENAME 的 COLNAME 值不是空的数据
select * from TABLENAME where COLNAME is not null

限制行数

# 选取所有来自表 TABLENAME 的所有数据
# 限制返回的行数为 ROWNUMBER
# 偏移 OFFSETNUMBER 即 从 第 OFFSETNUMBER 条数据 开始获取
select * from TABLENAME limit OFFSETNUMBER ROWNUMBER

条件运算

= 等于 不能用于过滤 null
<=> 安全等于 可以过滤 null
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 或 != 都表示不等于

统计函数

分组:

# 选择所有来自 TABLENAME 的数据
# 以 COLNAME 为分组依据
select * from TABLENAME group by COLNAME

过滤

# 选择来自 TABLENAME 的所有 COLNAME 列数据
# 以 COLNAME 为分组依据进行分组
# 过滤掉 COLNAME < 100 的数据
# 只显示 COLNAME > 100 的数据
select COLNAME from TABLENAME group by COLNAME having COLNAME > 100

聚合函数

要求获取最大值，平均值，最小值等聚合数据时使用

求平均值

# 选取所有来自表 TABLENAME 的 COLNAME 数据
# 将其平均值显示在 SHOWCOLNAME 下面
# 忽略 null 值
# 可以使用 distinct 进行去重操作 avg(distinct COLNAME)
select avg(COLNAME) SHOWCOLNAME from TABLENAME

# 选取所有来自表 TABLENAME 的 GROUPNAME 的数据 和 COLNAME 的平均值
# 并以 GROUPNAME 为分组依据进行分组
select GROUPNAME, avg(COLNAME) SHOWCOLNAME from TABLENAME group by GROUPNAME

# 选取所有来自表 TABLENAME 的 GROUPNAME 的数据 和 COLNAME 的平均值
# 并以 GROUPNAME 为分组依据进行分组
# 过滤掉 平均值大于 50 的数据
select GROUPNAME, avg(COLNAME) SHOWCOLNAME from TABLENAME 
group by GROUPNAME having avg(COLNAME) > 50

计数

# 选取所有来自表 TABLENAME 的数据
# 将其条目数量显示在 SHOWCOLNAME 下
# as 和 SHOWCOLNAME 为可选的
# 若不加则条目数量显示在 count(*) 列下
# count(*) 为选择所有 包含 空值
# count(all COLNAME) 选取所有值非空的 COLNAME
# count(distinct COLNAME) 选取所有唯一非空值的 COLNAME
select count(*) as SHOWCOLNAME from TABLENAME

求和

# 选取所有来自表 TABLENAME 的 COLNAME 和 COLNAME1与COLNAME2 的和
# 以 COLNAME 为分组依据进行分组
# 将 COLNAME1 和 COLNAME2 的和 显示在 总和 列下
# sum(distinct COLNAME) 去重求和
# sun 自动忽略空值
# 如果没有匹配的行 则 sum 返回 null 而非 0
select COLNAME sum(COLNAME1, COLNAME2) as 总和 from TABLENAME group by COLNAME

最大值

# 选取 TABLENAME 中 COLNAME 的最大值
# 并显示在 最大值 列下
select max(COLNAME) as 最大值 from TABLENAME

最小值

# 选取 TABLENAME 中 COLNAME 的最小值
# 并显示在 最小值 列下
select min(COLNAME) as 最小值 from TABLENAME

字符串函数

MySQL 支持各种字符集，可使用 `show character set` 获取 MySQL数据库服务器所支持的所有字符集*

拼接字符串

# concat 需要至少一个字符串参数
# concat 会将传入字符串拼接成字符串
# concat 会在连接之前将所有参数转为字符串类型，如果任意参数为 null， 则返回 null

# 选取字符串，默认显示在 concat('a', '1') 列下
select concat('a', '1');
# 选取字符串， 显示在赋予的别名 COLNAME 列下
select concat('a', '1') as COLNAME;
# 选取表 TABLENAME 的 NAME， AGE 两列数据 并拼接输出
select concat('名叫：', NAME, '今年', AGE, '岁') from TABLENAME

使用分隔符拼接字符串

# 使用 concat_ws 可以使用分隔符拼接字符串
# concat_ws 第一个参数为 后续参数的分隔符
# concat_ws 会忽略第一个参数以外的 其它 null 参数
# 如果 concat_ws 的第一个参数为 null 则 返回 null
# 选取表 TABLENAME 的 YEAR_VALUE MONTH_VALUE DAY_VALUE 三列数据以 - 为分割符
# 显示在 日期 列下
select concat_ws('-', YEAR_VALUE, MONTH_VALUE, DAY_VALUE) as 日期 from TABLENAME;

获取字符串长度

获取以字节为单位的字符串长度

length(str)

获取以字符为单位的字符串长度

char_length(str)

```mysql
# 将 字符串 转换为 ucs2 字符集
# ucs-2 Unicode 指定一个字符存储为 2 个字节
# 分别使用 char_length 和 length 获取 @s 的长度
set @s = convert('字符串', using ucs2);
# @s 以 2 个字节存储每个字符，因此
# char_length(@s) 获取的值为 3
# length(@s) 获取的值为 6
select char_length(@s), length(@s);

获取字符串左侧指定位置

# left(str, length)
# str 为 需要提取出子字符串的父字符串
# length 为指定返回字符数
# 如果 两个参数任意一个为 null 则 返回 null
# 如果 length 为 0 或 为负 则返回空字符串
# 如果 length 大于 str 的长度 则 返回整个 str
select left('123456789', 5) # 12345

替换字符串

# replace(str, old_string, new_string)
# replace 允许用新的字符串代替列中的字符串
# str 为 父字符串
# old_string 为 被替换的字符串
# new_string 为 替换的字符串

# 有一个 replace 的语句用于 插入或更新数据，需要注意

# 选取表 TABLENAME 的 NAME 如果 值为 null 则使用 查无此人 代替
select replace(NAME，'null', '查无此人') from TABLENAME

从指定位置返回指定长度字符串

# substring(str, position)
# str 为 提取子字符串的父字符串
# position 为整数 指定子串的开始字符的位置 可以使正负整数
# 如果 position 为 0 则返回空字符串
# 如果要指定获取的子字符串长度 则 可以添加第三个参数 length
# substring(str, position, length)
select substring('__MySQL-SERVER__', 3, 12);# MySQL-SERVER

删除不必要字符

# trim({botn | leading | trailing} [remove_str] from str)
# leading 为前导字符， trailing  为尾随字符， botn 为前导和尾随字符
# 如果未指定 则默认使用 botn 即 删除前导和尾随字符
# remove_str 是删除的字符串，默认情况下，是空格，即 如不指定，仅删除空格
# str 为是删除子字符串 remove_str 的父字符串
# trim 返回字符串， 删除不需要字符
select trim('-' from '--awdawd--') # awd

# 如果数据已存在， 且要清理空格或其它字符，可在 update 语句使用 trim() 函数
update COLNAME set COLNAME=trim(COLNAME)

# 如果仅删除前导或尾随空格，可使用 ltrim 和 rtrim 函数

查找列表中的指定字符串

# find_in_set(needle, haystack)
# needle 为查找字符串
# haystack 为需要搜索的字符串列表
# 如果 任意参数为 null 则返回 null
# 如果 needle 不在 haystack 或 haystack 为空 返回 0
# 如果 needle 在 haystack 中 返回正整数

# 如果 needle 包含逗号则无法正常工作
# 如果 needle 为常量字符串，且 haystack 是类型为 set 的列
# mysql 将使用位算术优化

# 'x, y, z' 返回 0
# 'x,y,z' 返回 2
# 原因是 第一个包含了空格
select find_in_set('y', 'x,y,z') # 2
# 可使用 not 否定 返回的结果值， not 0 = true, not [1 ~ ...] = false

格式化字符串

# fomrat(number, double, locale)
# number 为需要格式化数字
# double 为保留小数位数
# locale 是可选的，用于确定 千个分隔符和分隔符之间的分组
# 如省略，则默认使用 en_US
# 有些地区是以 . 进行分割的
select format(123456.789, 2, 'zh_cn') # 123456.78

```mysql
CREATE TABLE `user_xly` (
  `phoneno` char(12) NOT NULL COMMENT '手机号码',
  `balance` int DEFAULT '0' COMMENT '余额',
  `cid` char(20) NOT NULL COMMENT '身份证',
  `name` char(10) NOT NULL COMMENT '姓名',
  `status` char(2) NOT NULL DEFAULT '2' COMMENT '手机状态',
  PRIMARY KEY (`phoneno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


select 
avg(stuscore) as '平均分', 
cno as '选课门数' 
from stumarks 
where order by avg(stuscore) 
having cno >= 1;

控制语句

#   if 语句
##  search_condition 表示条件判断语句
##  statement_list 可包括多个语句
IF search_condition THEN satement_list
ELSEIF search_condition THEN satement_list
ELSE statement_list
END IF


#   case 语句
##  search_condition 条件判断语句
##  when_value 条件判断变量 与 case_value 配合使用
CASE [case_value]
    WHEN search_condition THEN statement_list
    WHEN when_value THEN statement_list
    [WHEN search_condition THEN statement_list]
    [WHEN when_value THEN statement_list]
    ELSE statement_list
END CASE


#   loop 语句
##  同一层下 begin_label 和 end_label 必须相同
##  loop 没有停止循环语句
##  需通过 LEAVE 语句停止
[begin_label] LOOP
    statement_list
END LOOP [end_label]

#   leave 语句 break
##  离开 label 标志的循环
LEAVE label

#   iterate 语句 coutinue
##  再次循环
ITERATE label

#   repeat 语句
##  将重复执行 statement_list
##  知道 search_condition 返回 true
[begin_label] REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [end_label]

#   while 语句
##  重复执行 statement_list
##  直到 search_condition 为 true
[begin_label] WHILE search_condition DO
    statement_list
END WHILE [end_label]

存储过程

CREATE
    [DEFINER] = { user | current_user }
    PROCEDURE NAME ([proc_parameter][,...])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
        COMMENT 'string'
    |   LANGUAGE SQL
    |   { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
    |   SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

[begin_label] BEGIN
    [statement_list]
END [end_label]

#   语法
##  修改语句结束符
##  将语句结束符变更为 $$ 而非 ;
DELIMITER $$

#   声明存储过程
##  创建存储过程 NAME
##  接受参数 params 类型为 int
##  可以是字面量或变量
CREATE PROCEDURE NAME ( IN params int )
##  输出参数 params 类型为 int
##  可返回多个值，传出值只能是变量
##  传入变量参数，返回时将自动变更传入变量值
CREATE PROCEDURE NAME ( OUT params int )
##  输入输出参数 params 类型为 int
##  传出值只能是变量
##  传入变量参数，返回时将自动变更传入变量值
##  尽量少用
CREATE PROCEDURE NAME ( INOUT params int )
##  如果过程无参数
##  也需要在过程名后写上小括号
CREATE PROCEDURE NAME ()

#   开始和结束符
##  BEGIN_LABEL 和 END_LABEL 为可自定义的
##  开始和结束标签
##  可以嵌套
##  嵌套中的每条语句必须以 ; 结尾
BEGIN_LABEL BEGIN
    ...
END END_LABEL

#   变量赋值和定义
##  赋值
### 设置 @var 变量值为 1
SET @var=1
##  定义
### 声明 num 为 无符号整数 类型 值为 40000
DECLARE num int unsigned default 40000

#   创建函数
##  创建函数 NAME 接受参数 params
CREATE FUNCTION NAME(params)

#   调用存储过程
##  NAME 为存储过程名
##  PARAMS 为需要的参数
CALL NAME(PARAMS)

#   查看存储过程的详细
##  显示在 DATABASE 中 创建的存储过程时的详细
SHOW CREATE PROCEDURE DATABASE.PROCEDURENAME

#   修改存储过程
ALTER PROCEDURE

#数据库 #编程

#编程 #数据库 #软件开发 #后端

学习 MySQL

http://localhost:8080/archives/87ff57ca-533b-400a-8333-884c4cc4dd58

作者

inksha

发布于

2024年09月14日

许可协议

学习 Jest - 触发事件上一篇

学习 Nightwatch - export 语法下一篇

学习 MySQL

Mysql:

登录：

修改密码

数据库操作

查看数据库

查看当前数据库下的数据表

变更当前数据库

创建数据库

删除数据库

表操作

创建表

表格式

查看表格结构

删除表

重命名表

选取表数据

插入表数据

更新表数据

删除表数据

添加表结构

修改表字段类型

修改表字段名称

操作符

去重

排序

别名

约束

对表的字段添加约束

常用字段约束

创建表时设置约束

外键约束

条件查询

与或非

选取范围

模糊查询

正则表达式

列表查询

空值查询

限制行数

条件运算

统计函数

分组:

过滤

聚合函数

求平均值

计数

求和

最大值

最小值

字符串函数

拼接字符串

使用分隔符拼接字符串

获取字符串长度

获取以字节为单位的字符串长度

获取以字符为单位的字符串长度

获取字符串左侧指定位置

替换字符串

从指定位置返回指定长度字符串

删除不必要字符

查找列表中的指定字符串

格式化字符串

控制语句

存储过程