MySQL 学习笔记:常见函数
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 支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。
- 整型: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
- 浮点型:FLOAT 和 DOUBLE、定点小数类型 DECIMAL
- 日期时间型:YEAR、TIME、DATE、DATETIME、TIMESTAMP
- 字符型:CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM 和 SET 等
- 二进制型: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列为列级)
- 根据功能划分,约束类型包括:
- NOT NULL 非空约束
- PRIMARY KEY 主键约束
- UNIQUE KEY 唯一约束
- DEFAULT 默认约束
- FOREIGN KEY 外键约束
外键约束
FOREIGN KEY
-
保证数据一致性,完整性。
-
实现一对一或一对多关系
- 父表和子表(具有外键列的表)必须使用相同的存储引擎,而且禁止使用临时表
- 数据表的存储引擎只能为InnoDB
- 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
- 外键列和参照列必须创建索引。如果外键列不存在索引的话,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;
外键约束的参照操作
- CASCADE:从父表删除或更新且自动删除或更新子表中的行
- SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表没有指定NOT NULL
- RESTRICT:拒绝对父表的删除或更新操作
- 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;
修改数据表–添加约束
- 添加主键约束
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;
- 添加唯一约束,可以由多个,而主键约束只能有一个
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name, ...)
ALTER TABLE users2 ADD UNIQUE (username);
- 添加外键约束
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);
- 添加/删除默认约束
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 ;
- 点赞
- 收藏
- 关注作者
评论(0)