一、SQL
1、DDL-数据库操作
1.1、查询
查询所有数据库:SHOW DATABASES;
查询当前数据库:SELECT DATABASES;
1.2、创建
创建:CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET字符集] [COLLATE 排序规则];
1.3、删除
删除:DROP DATABASE [IF EXISTS] 数据库名;
1.4、使用
使用:USE 数据库名
2、DDL-表操作
2.1、创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
.......
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
注意:[......]为可选参数,最后一个字段不加逗号。
数据类型 | ||||
分类 | 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 |
数值类型 | TINYINT | 1byre | (-128,127) | (0,255) |
数值类型 | SMALLINT | 2bytes | (-32768,32767) | (0,65535) |
数值类型 | MEDIUMINI | 3bytes | (-8388608,8388607) | (0,16777215) |
数值类型 | INT或者INTEGER | 4bytes | (-2147483648,2147483647) | (0,4294967295) |
数值类型 | BIGINT | 8bytes | (-2^63,2^63-1) | (0,2^64-1) |
数值类型 | FLOAT | 4bytes | (-3.402823466E+38,3.4028234635E+38) | 0和(1.195494351E-38,3.402823466E+38) |
数值类型 | DOUBLE | 8bytes | (-1.7976931348623157E+308,1.7976931348623157E+308) | 0和(2.2250738585072014E-308,1.7976931348623157E+308) |
数值类型 | DECTMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 |
日期类型 | |||||
分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
日期类型 | DATE | 3bytes | 1000-01-01至9999-12-31 | YYYY-MM-DD | 日期值 |
日期类型 | TIME | 3bytes | -838:59:59至838:59:59 | HH:MM:SS | 时间值或持续时间 |
日期类型 | YEAR | 1byte | 1901至2155 | YYYY | 年份值 |
日期类型 | DATETIME | 8bytes | 1001-01-01 00:00:00至9999-01-01 00:00:00 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
日期类型 | TIMESIAMP | 4bytes | 1970-01-01 00:00:00至2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
字符串类型 | |||
类型 | 类型 | 大小 | 描述 |
字符串类型 | CHAR | 0-255bytes | 定长字符串 |
字符串类型 | VARCHAR | 0-65535bytes | 变长字符串 |
字符串类型 | TINYBLOB | 0-255bytes | 不超过255个字符的二进制数据 |
字符串类型 | TINYTEXT | 0-255bytes | 短文本字符串 |
字符串类型 | BLOB | 0-65535bytes | 二进制形式长文本数据 |
字符串类型 | TEXT | 0-65535bytes | 长文本数据 |
字符串类型 | MEDIUMBLOB | 0-16777215bytes | 二进制形式中等长度文本数据 |
字符串类型 | MEDIUMTEXT | 0-16777215bytes | 中等长度文本数据 |
字符串类型 | LONGBLOB | 0-4294967295bytes | 二进制形式的极大文本数据 |
字符串类型 | LONGTEXT | 0-4294967295bytes | 极大文本数据 |
2.2、查询
查询当前数据库所有表:SHOW TABLES;
查询表结构:DESC 表名;
查询指定表的建表语句:SHOW CREATE TABLE 表名;
2.3、删除
删除表:DROP TABLE [IF EXISTS] 表名;
删除指定表,并重新创建表:TRUNCATE TABLE 表名;
2.4、修改
添加字段:ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释 ] [约束];
修改数据类型:ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释] [约束];
删除字段:ALTER TABLE 表名 DROP 字段名;
修改表名:ALTER TABLE 表名 RENAME TO 新表名;
3、DML
3.1、添加数据
给指定字段添加数据:INSERT INTO 表名(字段名1,字段名2,......)VALUES(值1,值2,......);
给全部字段添加数据:INSERT INTO 表名 VALUES(值1,值2,......);
批量添加数据:
INSERT INTO 表名(字段名1,字段名2,......) VALUES(值1,值2,......),(值1,值2,......),(值1,值2,......);
INSERT INTO 表名 VALUES(值1,值2,......),(值1,值2,......),(值1,值2,......);
注意:
插入数据时,指定的字段顺序需要与值的顺序是一一对应的;
字符串和日期型数据应该包含在单引号中;
插入的数据大小,应在字段的规定范围内。
3.2、修改数据
修改数据:UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,......[WHERE 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
3.3、删除数据
删除数据:DELETE FROM 表名 [WHERE 条件];
注意:DELETE语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据;
DELETE语句不能删除某个字段的值(可以使用UPDATE)。
4、DQL
4.1、语法
基本查询:
SELECT 字段列表
FROM 表名列表
条件查询:
WHERE 条件列表
分组查询【前提(COUNT、MAX、MIN、AVG、SUM)】:
GROUP BY 分组字段列表
HAVING 分组后条件列表
排序查询:
ORDER BY 排序字段列表
分页查询:
LIMIT 分页参数
4.2、基本查询
查询多个字段:
SELECT 字段1,字段2,字段3...FROM;
SELECT * FROM 表名;
设置别名:SELECT 字段1 [AS 别名1],字段2[AS 别名2]......FROM 表名;
去除重复记录:SELECT DISTINCT 字段列表 FROM 表名;
4.3、条件查询
语法:SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
BETWEEN...AND... | 在某个范围之内(含最小值,最大值) |
IN(...) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
AND或&& | 并且(多个条件同时成立) |
OR或!! | 或者(多个条件任意一个存在) |
NOT或! | 非,不是 |
4.4、聚合函数
介绍:将一列数据作为一个整体进行纵向计算
常见聚合函数:
函数 | 功能 |
COUNT | 统计数量 |
MAX | 最大值 |
MIN | 最小值 |
AVG | 平均值 |
SUM | 求和 |
语法:SELECT 聚合函数(字段列表) FROM 表名;
注意:所有的NULL不参与聚合函数。
4.5、分组查询
语法:SELETCT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];
WHERE和HAVING区别:
执行时机不同:where是分组前过滤,不满足where条件,不参与分组,而having是分组之后对结构进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意:执行顺序where>聚合函数>having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
4.6、排序查询
语法:SELETE 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
排序方式:
ASC:升序(默认)
DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
4.7、分页查询
语法:SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数;
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL是LIMIT;
如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10;。
4.8、执行顺序
编写顺序 | 执行顺序 |
SELECT字段列表 | FORM 表名列表 |
FROM 表名列表 | WHERE 条件列表 |
WHERE 条件列表 | GROUP BY 分组字段列表 |
GROUP BY 分组字段列表 | HABING 分组后条件列表 |
HAVING 分组后条件列表 | SELECT 字段列表 |
ORDER BY排序字段列表 | ORDER BY 排序字段列表 |
LIMIT 分页参数 | LIMIT 分页参数 |
5、DCL
5.1、管理用户
查询用户:
USE mysql;
SELECT * FROM user;
创建用户:
CREAT USER '用户名'@'主机名' IDENTFIED BY '密码';
修改用户密码:
ALTER USER '用户名'@'主机名' IDENTFIED WITH mysql_native_password BY '新密码';
删除用户:
DROP USER '用户名'@'主机名';
注意:主机名可以使用%通配;
这类SQL开发人员操作的比较少,主要是DBA(database administrator 数据库管理员)使用。
5.2、权限控制
权限 | 说明 |
ALL,ALL PRIVIEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
查询权限:SHOW GRANIS FOR '用户名'@'主机名';
授予权限:GRANT 权限列表 ON 数据库 表名 TO '用户名'@'主机名';
撤销权限:REVOKE 权限列表 ON 数据库 表名 FROM '用户名'@'主机名';
注意:多个权限之间,采用逗号分隔;
授权时,数据库名和表名可以使用*进行通配,代表所有。