MySQL 基础知识记录
数据库语句分类:
DDL : 对数据库数据表的操作
CREATE : 创建数据库/表格
DROP: 删除索引、表和数据库
ALTER : 在已有的表中添加、修改或删除列
DML:对表中内容的操作
SELECT... FROM...: 查找
INSERT... INTO...: 插入
DELETE... FROM...: 删除
UPDATE... set... : 更新
DCL:数据控制语句
COMMIT : 将sql执行结果写入数据库
SAVEPOINT: 保存时间点
ROLLBACK:撤销SQL语句执行结果
DML:
SELECT操作:
查询操作,不影响源数据库,仅修改显示
1.SELECT [列名],[列名] FROM [表名];
2.SELECT * FROM [表名];
3.SELECT [列名], AS [别名] FROM [表名];
4.SELECT [表名].[列名] FROM [表名];
5.SELECT [表别名].[列名] FROM [表名] AS [别名]
6.SELECT [列名1],[列名2],[列名1 - 列名2] AS [利润] FROM [表名]
7.CONCAT 将字段文本拼接
有NULL结果为NULL
SELECT CONCAT(列名,列名, 常量 ,字符串 ... ) FROM [表名]
8.CONCAT_WS 将字段文本拼接
有NULL结果为忽略NULL字段
SELECT CONCAT_WS("拼接符", 列名,列名, 常量 ,字符串 ... ) FROM [表名]
9.DISTINCT 去除内容相同的行
SELECT DISTINCT [列名] FROM [表名];
10.ORDER BY
SELECT * FROM [列名] ORDER BY [列名] ASC/DESC 升序降序
中文需要转换为GBK格式 CONVERT([列名] USING GBK)
SELECT * FROM [列名] ORDER BY [列名] ASC/DESC,[列名] ASC/DESC
11.WHERE... AND/ OR
条件查找 > = < != ....
SELECT * FROM [表名] WHERE[列名] = 值;
SELECT * FROM [表名] WHERE条件1 AND 条件2 ... ;
IFNULL([列名],值) 如果列的值为NULL 使用自定义值判断
SELECT * FROM [表名] WHEREIFNULL([列名],0) > 10;
BETWEEN ... AND... :在值范围内
SELECT * FROM [表名] WHERE [列名] BETWEEN [值1] AND [值2];
12.通配符 LIKE
a. 下划线( _ ) :单个、任何字符
b.百分号( % ):任意数目、任何字符
SELECT * FROM [表名] WHERE [列名] LIKE "%菜";
数据中本身存在% 或者_ 使用\ 转义字符 将通配符转成普通字符
13.数据过滤
IN(值1,值2...) :取满足条件的
SELECT * FROM [表名] WHERE [列名] IN(值1,值2);
NOT 条件 :取反
数据类型:
数值型:
1.INTEGER:整数型、整数
INTEGER,INTEGER(10) 显示长度10位
2.DEMICAL:定点型固定小数位数
DEMICAL(总长度,小数点后位数)
DEMICAL(2,2) : 0.16 , DEMICAL(4,2) 四舍五入
3.FLOAT:浮点型 存储不固定位数小数
FLOAT/DOUBLE , 可以明确小数位数 五舍六入
文本型:
1.char 定义字符串 CHAR(10) 不足自动补空位 长度确定
2.varchar 可变长字符 varchar , varchar(10) 长度不固定 省空间
日期型:
1.DATE:日期型 Y-m-d
"2022-3-29"
2.TIME: 时间型 H:i:s
"18:00:00"
3DATETIME:日期型 Y-m-d H:i:s
"2022-3-29 18:00:00"
函数处理:
数值型:
绝对值函数 ABS ABS(-3) = 3
平方根 SQRT SQRT(4) = 2
指数函数 EXP EXP(4) = e^4
四舍五入 ROUND ROUNT(1.234,2) = 1.23
圆周率 PI PI() = π
文本型:
字符长度 CHAR_LENGTH CHAR_LENGTH('数据') = 2
字节数 LENGTH LENGTH('数据') = 6 UTF-8中文占3 GBK 占2
去除左边/右边空值 LTRIM/RTRIM LTRIM(" a") = "a"
大写/小写字母转换 UPPER/LOWER UPPER("a") = "A"
日期型:
获取当前日期时间 NOW NOW() = systime
获取当前日期 CURDATE CURDATE() = '20220329'
获取当前时间 CURTIME CURTIME() = '18:00:00'
时间增加 DATE_ADD DATE_ADD( NOW() , INTERVAL 1 YEAR)
时间减少 DATE_SUB INTERVAL 1 MONTG / DAY
聚合函数:
1.计数函数
a.COUNT
count(*)/(1) 计算出总共的行数
count([列名]) 计算出该列的非NULL数量
count(DISTINCT [列名]) 对行去重计算
SELECT COUNT(DISTINCT [列名]) FROM [表名]
b.SUM([列名])
只对数值型列作用,计算整列的值的和
c.AVG([列名])
计算平均数 只对数值型作用 忽略NULL
SELECT AVG(i) FROM [表名];
d.MAX([列名]) 计算最大值
e.MIN([列名]) 计算最小值
2.分组数据
GRUOP BY[列名];
按照给定的列名进行分类,再使用聚合函数计算
HAVING :
在分组之后再次进行筛选
SELECT `name`,SUM(i) FROM test GROUP BY `name` HAVING SUM(i) > 3;
SELECT 重要顺序:
书写顺序
SELECT... FROM... WHERE... GROUP BY... HAVING... ORDER BY...
执行顺序
FROM... WHERE... GROUP BY... HAVING... SELECT... ORDER BY...
多表查询:
1.SELECT [表名].[列名]...
FROM [表1],[表2]...
WHERE 链接条件 如 a.id = b.id
2.SELECT [表名].[列名]...
FROM [表1]
INNER JOIN [表2]
ON 链接条件 如 a.id = b.id
3.外部联接
SELECT [表名].[列名]...
FROM [表1]
LEFT/RIGHT/FULL OUTER JOIN [表2]
ON 链接条件 如 a.id = b.id
4.UNION
将两个查询联合共同列
SELECT * FROM [表名]
UNION
SELECT * FROM [表名]
INSERT:
1.INSERT INTO [表名] VALUES(插入值) 值和表内容对应 可以使用NULL
2.INSERT INTO [表名] (字段名) VALUES(插入值) 值和字段名对应
3.INSERT INTO[表名] SELECT * FROM [表名] WHERE 条件; 将查询的值插入表
UPADTE :
1.UPDATE [表名] SET [列名] = [值],[列名] = [值] ; 更新了整列
2.UPDATE [表名] SET [列名] = [值],[列名] = [值] WHERE 条件; 按照条件修改
DELETE:
DELETE FROM [表名] WHERE 条件;
DELETE FROM [表名]; 会清空表
TRUNCATE TABLE [表名] 清空表
DDL:
1.CREAT
CREATE TABLE [表名]
(
字段名 数据类型 属性,
字段名 数据类型 属性,
);
CREATE TABLE test
(
user_name VARCHAR(255) NOT NULL,
user_pwd VARCHAR(255) DEFAULT 'admin',
);
CREATE TABLE [表名] 复制表
AS
SELECT * FROM [表名]
2.ALTER TABLE
ALTER TABLE [表名] +
+ ADD [字段名] [数据类型] [属性] 添加字段
+ DROP [字段名],[字段名] 删除字段
+ DROP COLUMN [字段名] 删除字段
+ MODIFY COLUMN [字段名] [数据类型] [属性] 修改字段
3.DROP TABLE
DROP TABLE [表名]
RENAME TABLE [表名1] to [新表名1]...
视图:
1.创建一个视图
CREATE VIEW [视图名] as [目标二维表];
CREATE VIEW [视图名]
AS
SELECT [列名] FROM [表名]
2.删除视图
DROP VIEW [视图名]
存储过程:
1.创建存储过程
CREATE PROCEDURE pr_1(IN `@id` INT)
BEGIN
SELECT * FROM test WHERE id = `@id`;
END
可不要参数 ,IN 为参数输入 ,OUT为参数输出
2.删除存储过程
DROP PROCEDURE pr_1
3.调用存储过程 --类似于函数的调用
CALL pr_1(3)
DCL:
1.COMMIT
将SQL语句的执行结果写入数据库表中
2.ROLLBACK
撤销SQL语句的执行结果
3.SAVEPOINT
时间点的备份
4.COMMIT后数据包保存,无法回退,
mySQL 需要设置AUTO COMMIT = 0;取消自动提交
Begin开始 使用ROLLBACK回退到Begin的状态
SACEPOINT [时间点名称] 可以保存状态,
ROLLBACK [时间点] 可以回退到指定的时间点
RELEASE SAVEPOINT [时间点] 删除时间点,释放空间
SQL高级特性:
PRIMARY KEY 主键
FOREIGN KEY 外键
UNIQUE 唯一约束
CHECK 检查约束
DEFAULT 默认约束 默认值
索引:
CREATE INDEX [索引名] ON [表名]([列名])