MySQL 学习笔记:常见函数

举报
宇宙之一粟 发表于 2022/08/31 23:07:42 2022/08/31
【摘要】 MySQL初级MySQL 是一个开源的数据库管理系统(DBMS),具有规模小、运行速度快、成本低、跨平台、个人免费、可移植性等特点。MySQL 提供了丰富的编程语言接口,包括但不限于:C、C++、Java、Go、Python、PHP 等语言的 API,支持标准的 SQL 语法,支持 ODBC (开放式数据库连接)的应用程序。官方安装地址:https://dev.mysql.com/down...

MySQL初级

MySQL 是一个开源的数据库管理系统(DBMS),具有规模小、运行速度快、成本低、跨平台、个人免费、可移植性等特点。

MySQL 提供了丰富的编程语言接口,包括但不限于:C、C++、Java、Go、Python、PHP 等语言的 API,支持标准的 SQL 语法,支持 ODBC (开放式数据库连接)的应用程序。

官方安装地址:https://dev.mysql.com/downloads/mysql/

安装完成后,登录命令如下:

mysql -u root -p 3306 -h 127.0.0.1

其中:

  • mysql 为登录命令
  • -u 后面跟登录数据库的用户名称,在这里为 root
  • -p 后面跟用户登录密码
  • -h 后面的参数是服务器的主机地址,在这里为本地机器上,客户端和服务器在同一台机器上,所以输入 localhost 或者 IP 地址 127.0.0.1

数据类型

MySQL 支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。

  1. 整型: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  2. 浮点型:FLOAT 和 DOUBLE、定点小数类型 DECIMAL
  3. 日期时间型:YEAR、TIME、DATE、DATETIME、TIMESTAMP
  4. 字符型:CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM 和 SET 等
  5. 二进制型:BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB

操作数据库

USE

  • 打开数据库
  • 语法格式:USE DATABASE();, USE 数据库名称
  • 选中数据库:SELECT DATABASE();

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(

  Column_name data_type,

  ...)

例如:

CREATE TABLE tb1(

  username VARCHAR(20),

  age TINYINT UNSIGNED,

  salary FLOAT(8,2) UNSIGNED

);

查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

例如:

SHOW TABLES FROM mysql;

查看数据表结构(列)

SHOW COLUMNS FROM tbl_name

例如:

SHOW COLUMNS FROM tb1;

记录的插入与查找(行)

  • 插入记录
INSERT [INTO] tbl_name [(col_name, ...)] VALUES(val,...)

例如:

INSERT tb1 VALUES('Tom', 25, 7777.88);

INSERT tb1 (username, salary) VALUES ('Jerry', 6666.66);
  • 记录查找 SELECT
SELECT expr,... FROM tbl_name
SELECT * FROM tb1;

空值与非空

  • NULL,字段值可以为空

  • NOT NULL,字段值禁止为空

CREATE TABLE tb2(
  username VARCHAR(20) NOT NULL,
  Age TINYINT UNSIGNED NULL);

自动编号与主键约束

AUTO_INCREMENT 自增

  • 自动编号,且必须与主键组合使用
  • 默认情况下,其实值为1,每次的增加为1
CREATE TABLE tb3(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(30) NOT NULL
);

PRIMARY KEY 主键约束

  • 每张表只能存在一个主键,主键的字段允许赋值
  • 主键保证记录的唯一性
  • 主键自动为NOT NULL
  • 不一定需要和自动编号一起使用
INSERT tb3 (username) VALUES('Tome');
INSERT tb3 (username) VALUES('Jack');
INSERT tb3 (username) VALUES('Rose');

唯一约束

  • 可以保证记录的唯一性
  • 字段可以为空值(NULL)
  • 每张数据表可以存在多个唯一约束
CREATE TABLE tb4(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  age TINYINT UNSIGNED
);
SELECT COLUMNS FROM tb4;

INSERT tb4(username, age) VALUES('Tom', 22); 

默认约束

DEFAULT 默认值

  • 当插入记录时,如果没有明确为字段赋值,则自动赋予默认值
CREATE TABLE tb6(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  sex ENUM('1', '2', '3') DEFAULT '3'
);

SHOW COLUMNS FROM tb6;
INSERT tb6(username) VALUES('Jack');

约束

不同标准划分,结果不同

  1. 约束保证数据的完整性和一致性
  2. 根据参照数目的多少,约束分为表级约束和列级约束(约束的字段的多少,1列为列级)
  3. 根据功能划分,约束类型包括:
    1. NOT NULL 非空约束
    2. PRIMARY KEY 主键约束
    3. UNIQUE KEY 唯一约束
    4. DEFAULT 默认约束
    5. FOREIGN KEY 外键约束

外键约束

FOREIGN KEY

  • 保证数据一致性,完整性。

  • 实现一对一或一对多关系

  1. 父表和子表(具有外键列的表)必须使用相同的存储引擎,而且禁止使用临时表
  2. 数据表的存储引擎只能为InnoDB
  3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话,MYSQL将自动创建索引。

MySQL配置文件:Default-storage-engine=INNODB

CREATE TABLE province(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  province VARCHAR(20) NOT NULL
);

SHOW CREATE TABLE province;

CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(10) NOT NULL,
  -- pid BIGINT,
  -- pid SMALLINT,
   pid SMALLINT UNSIGNED,
  FOREIGN KEY (pid) REFERENCES province(id)
);

SHOW INDEXES FROM province\G;
SHOW INDEXES FROM users\G;

外键约束的参照操作

  1. CASCADE:从父表删除或更新且自动删除或更新子表中的行
  2. SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表没有指定NOT NULL
  3. RESTRICT:拒绝对父表的删除或更新操作
  4. NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同

物理外键,一般实现逻辑外键

表级约束与列级约束

  • 对一个数据列建立的约束,称为列级约束(用的更多)

  • 对多个数据列建立的约束,称为表级约束

  • 列级约束既可以在列定义时声明,也可以在列定义后声明

  • 表级约束只能在列定义后声明

  • 其中NOT NULL、DEFAULT不存在表级约束

check 约束,不起作用

修改数据表

添加单列

ALTER TABLE tbl_name ADD[COLUMN] col_name column_definition[FIRST | AFTER col_name]

例如:

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;


ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;


ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;

# 

添加多列

ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,...)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

例如:

ALTER TABLE user1 DROP truname;

ALTER TABLE user1 DROP password, DROP age;

修改数据表–添加约束

  1. 添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT] [symbol] PRIMARY KEY [index_type] (index_col_name,...)
CREATE TABLE users2(
  username VARCHAR(10) NOT NULL,
  pid SMALLINT(5) UNSIGNED
);

ALTER TABLE user2 ADD id SMALLINT UNSIGNED;

SHOW COLUMNS FROM users2;

ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
SHOW COLUMNS FROM users2;
  1. 添加唯一约束,可以由多个,而主键约束只能有一个
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name, ...)

ALTER TABLE users2 ADD UNIQUE (username);

  1. 添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference definition

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCE province(id);

  1. 添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

例如:

ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;

ALTER TABLE user2 ALTER age SET DEFAULT 15;

ALTER TABLE user2 ALTER age DROP DEFAULT;

删除约束

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列定义

ALTER TABLE tbl_name MODIFTY[COLUMN] col_name column_definition [FIRST|AFTER col_name]

修改列名称

ALTER TABLE tbl_name CHANGE[COLUMN] old_col_name new_col_name_definition [FIRST|ALTER col_name]

例如:

ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

数据表更名

-- 方法一:ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

-- 方法二:RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2]...
ALTER TABLE users2 RENAME users3;

RENAME TABLE users3 TO users2;

总结:

  • 约束
    • 功能分:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY
    • 数据列的数目分:表级约束,列级约束
  • 修改数据表
    • 针对字段的操作:添加/删除字段,修改列定义,修改列名称等
    • 针对约束的操作;添加/删除各种约束
    • 针对数据表的操作:数据表更名(量种方式)

插入记录

INSERT

第一种方法:

INSERT [INTO] tbl_name [(col_name, ...)] {VALUES|VALUE} ({expr|DEFAULT},...,(...),...

先创建一个users表:

CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL,
  password VARCHAR(32) NOT NULL,
  age TINYINT UNSIGNED NOT NULL DEFAULT 10,
  sex BOOLEAN
);

然后依次插入记录:

INSERT users VALUEs(NULL, 'Tom', '123', 25, 1)

INSERT users VALUEs(DEFAULT, 'Jerry', '123', 18, 1)

expr可以赋予表达式、函数

INSERT users VALUES(NULL, 'Tom', '123', 25), (DEFAULT, 'Jerry', md5('123'), 18, 1);

第二种方法:

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

说明:与第一种方式的区别在于,此方法可以使用子查询(SubQuery),而且每次只能插入一条记录

第三种方法:

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

查询结果写入指定的数据表

UPDATE

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT} [, col_name2={expr2|[DEFAULT]}]...[WHERE where_condition]

UPDATE users set age = age + 5;

DELETE

单表删除

DELETE FROM tbl_name [WHERE where_condition]

SELECT

查找记录

SELECT select_expr[,select_expr ...]
[
	FROM table_references
  [WHERE where_condition]
  [GROUP BY {col_name|position} [ASC|DESC],...]
  [HAVING where_condition]
  [ORDER BY {col_name|expr|positon}[ASC|DESC],...]
  [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

例如:

SELECT VERSION();

SELECT NOW();

SELECT 3+5

查询表达式:

  • 每个表达式表示想要的一列,必须有至少一个
  • 多个列之间以英文逗号隔开
  • 星号(*)表示所有列。table_name.*可以表示命名表的所有列。
  • 查询表达式可以使用[AS] alias_name为其赋予别名
  • 别名可以用于GROUP BY,ORDER BY或HAVING子句。

WHERE查询

对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。

在WHRE表达式中,可以使用MySQL支持的函数或运算符。

GROUP BY 查询结果分组

[ORDER BY {col_name|expr|positon}[ASC|DESC],...]

HAVING 部分分组

[HAVING where_condition]

ORDER BY 查询结果排序

[ORDER BY {col_name|expr|positon}[ASC|DESC],...]

LIMIT 限制查询数量

子查询与连接

子查询(Subquery)是指出现在其他SQL语句内的SELEC子句。

嵌套在查询内部,且必须始终出现在圆括号内。

子查询可以包含多个关键字或条件,如 DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。

子查询的外层查询可以是:SELECT、INSERT、UPDATE、SET 或 DO。

子查询可以返回标量、一行、一列或子查询。

使用比较运算符的子查询

=、>、< >= <= <> != <=>

语法结构

operand comparison_operate subquery

使用ANY、SOME或ALL修饰的比较运算符

operand comparison_operate ANY (subquery)

operand comparison_operate SOME (subquery)

operand comparison_operate ALL (subquery)

使用[NOT] IN 的子查询

operand comparison_operate [NOT] IN (subquery)

= ANY 运算符与IN等效

!= ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS 的子查询

如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE

使用INSERT…SELECT插入记录

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

多表更新

连接类型:

INNER JOIN 内连接,在 MySQL中,JOIN,CROSS JOIN 和 JOIN 是等价的。

LEFT[OUTER] JOIN,左外连接

RIGHT[OUTER] JOIN, 右外连接

语法结构:

table_reference {[INNER | CROSS] JOIN| {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condition_expr

多表更新之一步到位:

CREATE…SELECT 创建数据表同时将查询结果写入到数据表

连接

MySQL在SELECT语句、多表更新、多表删除语句汇总支持JOIN操作。

数据表参照

table_reference

tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用tbl_name AS alias name 或tbl_name alias name 赋予别名。

Table_subquery 可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。

内连接

使用ON关键字来设定连接条件,也可以使用WHERE来代替

通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。

显示连接公共的部分。仅显示连接条件的记录

左外连接

显示左表的全部记录及右表符合连接条件的记录

右外连接

显示右表的全部记录及左表符合连接条件的记录

多表连接

外键的逆向操作

无限级分类表设计

自身连接

同一个数据表对其自身进行连接

多表删除

函数

字符函数

CONCAT()

CONCAT_WS()

FORMAT()

LOWER()

UPPER()

LEFT()

RIGHT()

LENGTH()

LTRIM()

RTRIM()

TRIM()

SUBSTRING()

[NOT] LIKE

REPLACE()

数值运算符函数

CEIL

DIV

FLOOR

MOD

POWER

ROUND

TURNCATE

比较运算符与函数

[NOT] BETWEEN…AND…

[NOT] IN()

IS[NOT] NULL

日期时间函数

NOW()

CURDATE()

CURTIME()

DATE_ADD()

DATEIFF()

DATE_FORMAT()

信息函数

CONNECTION_ID()

DATEBASE()

LAST_INSERT_ID()

USER()
VERSION()

聚合函数

一个返回值

AVG()

COUNT()

MAX()

MIN()

SUM()

加密函数

MD5()

PASSWORD()

自定义函数

除了 MySQL 自带的函数,用户还可以自定义函数得到相应的功能。

例如,创建一个函数,返回两个整数之和:

-- 创建一个函数,返回两个整数之和
DELIMITER //
CREATE FUNCTION f1(
    n1 INT,
    n2 INT)   -- 创建函数 f1 参数可以是MySQL支持的那些类型
RETURNS INT  -- 该函数的返回值也是 int 类型
BEGIN    -- 标识函数体开始
    DECLARE num INT;    -- 定义一个int类型的变量
    SET num = n1 + n2;
    RETURN(num);

END //    -- 标识函数体结束
DELIMITER ;
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。