MySQL基础笔记（自用，勿怪）
MySQL
DDL：操作数据库和表
DML：对数据进行增删改
DQL：对数据进行查询
DCL：对数据库进行权限管理
数据库增删改查
-
create database if not exists db1;# 如果数据库不存在才创建
-
drop database if exists db1;# 如果数据库存在才删除
-
use db1;# 使用数据库
-
select DATABASE();# 查询当前数据库
-
show tables;# 查询当前数据库下所有表名称，首先要使用数据库
-
desc tb1;# 查看表的结构，字段和类型等
创建表
每个字段后面一定要加","号，并且最后一个后面不能加
CREATE TABLE tb_user(
id int,
username VARCHAR(20),
password VARCHAR(20)
);
所有的数据类型
数据类型基本释义
- DECIMAL：通过字符串形式表示小数，提高小数精度
- DATE：年月日
- TIME：时分秒
- YEAR：年份
- DATETIME：年月日时分秒
- TIMESTAMP：年月日时分秒，用时间戳表示，默认当前时间，最大表示到2038年（即2147483647），不推荐
数据类型使用方法
-
DOUBLE(总长度,小数点后保留的位数);# 总长度是小数点前和后的字符串总长度，不是内存位数
-
CHAR(字符长度);# 超出后报错
-
VARCHAR(字符长度);# 按实际大小存储
-
MySQL5.0以后字符串长度表示字符数而不是字节数
| 数据类型 | 大小 | 描述 |
|---|---|---|
| TINYINT | 1 byte | 小整数值 |
| SMALLINT | 2 bytes | 大整数值 |
| MEDIUMINT | 3 bytes | 大整数值 |
| INT或INTEGER | 4 bytes | 大整数值 |
| BIGINT | 8 bytes | 极大整数值 |
| FLOAT | 4 bytes | 单精度浮点数值 |
| DOUBLE | 8 bytes | 双精度浮点数值 |
| DECIMAL | 小数值 | |
| DATE | 3 | 日期值 |
| TIME | 3 | 时间值或持续时间 |
| YEAR | 1 | 年份值 |
| DATETIME | 8 | 混合日期和时间值 |
| TIMESTAMP | 4 | 混合日期和时间值,时间戳 |
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
修改表/删除表
alter table tablename add (column datatype [default value][null/not null],….);# 添加字段的语法
alter table tablename modify (column datatype [default value][null/not null],….);$# 修改字段的语法
alter table tablename drop (column);# 删除字段的语法
- DROP TABLE [IF EXISTS] tb1;# 判断是否存在删除表
- ALTER TABLE 表名 RENAME TO 新表名;# 修改表名
- ALTER TABLE 表名 ADD 列名 数据类型 [FIREST/AFTER .../BEGIN ...];# 添加一列
- ALTER TABLE 表名 MODIFY 列名 新数据类型;# 修改数据类型
- ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;# 修改表名和数据类型
- ALTER TABLE 表名 DROP 列名;# 删除字段
数据增删改查
添加数据
1.给指定列添加数据
- INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...);
2.给全部列添加数据
- INSERT INTO 表名 VALUES(值1,值2,...);
3.批量添加数据
- INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
- INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
修改数据
1.修改表数据
- UPDATE 表名 SET 列名1=值1,列名2=值2,... [WHERE 条件];# 不加条件默认修改所有记录
查询数据
基本语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 分页限定
基础查询
1.查询多个字段
- SELECT 字段1,... FROM 表名;
- SELECT * FROM 表名;# 查询所有数据，不建议这样用
2.去除重复记录
- SELECT DISTINCT 字段列表 FROM 表名;# 只删除回显时重复数据
3.起别名
- AS：AS也可以省略;
别名使用，修改回显时的字段名
select name,math as 数学成绩,english as 英语成绩 from tb1;
| 符号 | 功能 |
|---|---|
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| <>或!= | 不等于 |
| BETWEEN ... AND ... | 在某个范围之内(都包含) |
| IN(...) | 多选一 |
| LIKE占位符 | 模糊查询 _单个任意字符 %多个任意字符 |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| AND 或 && | 并且 |
| OR 或 || | 或者 |
| NOT或! | 非 或 不是 |
条件查询
注意1："或"运算的两种语法，多条件可用"多选一"模式
select * from tb1 where age = 18 or age = 20 or age = 22;
select * from tb1 where age in (18,20,22);
注意2：查询字段值为null的记录，不能用 = 或 != 比较，需要用 IS 或 IS NOT
注意3：通配符的使用
1.查询姓'马'的学员信息
- select * from tb1 where name like '马%';
2.查询第二个字是'花'的学员信息
- select * from tb1 where name like '_花';
3.查询名字中包含'德'的学员信息
- select * from stu where name like '%德%';
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] ...;
排序方式：
ASC：升序排列(默认值)
DESC：降序排列
注意:如果有多个排序条件，当前边的条件值一样时,才会根据第二条件进行排序
分组查询
聚合函数
以列为单位进行纵向计算：
SELECT 聚合函数名(列名) FROM 表;
- count(*)在新版MySQL中会自动选择字段查询
| 函数名 | 功能 |
|---|---|
| count(列名) | 统计数量(一般选用不为null的列) |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
注意：所有的聚合函数，null值都不参与运算
分组查询
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意：分组之后，查询的字段为聚合函数和参与分组字段,查询其他字段无任何意义
where和having区别:
-
执行时机不一样: where 是分组之前进行限定，不满足where条件，则不参与分组,而having是分组之后对结果进行过滤。
-
可判断的条件不一样: where 不能对聚合函数进行判断，having 可以。
执行顺序: where > 聚合函数 > having
查询示例
1.查询男同学和女同学各自的数学平均分
- select sex,avg(math) from stu group by sex;
2.查询男同学和女同学各自的数学平均分，以及各自人数
- select sex,avg(math),count(*) from stu group by sex;
3.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
- select sex,avg(math),count(*) from stu where math > 70 group by sex;
4.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
- select sex,avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;
分页查询
基本语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
- 起始索引：从0开始，如果查询的条目数超过表中记录总数，不会报错
- 计算公式：起始索引=(当前页码-1) * 每页显示数量
1.每页显示3条数据，查询第2页数据
- select * from stu limit 3【(2-1)*3】,3;
2.tips:
- 分页查询limit是MySQL数据库的方言
- Oracle分页查询使用rownumber
- SQL Server分页查询使用top
