文章目录
sql_mode SQL模式
规范SQL语句的书写方式，让数据库在存、取时满足生活中的常识和科学逻辑，让数据变的有意思
查看约束
select @@sql_mode;
比如说：除数不能为0，一年只有1-12月 1-31日
数据库属性
库（databases）
库名字
库属性：字符集（charset）、排序规则（collation）
表（tables）
表名
表属性： 存储引擎类型、字符集、排序规则
列名
列属性：数据类型、约束、其他属性
数据行
字符集（charset）及校对规则（collation）
字符集
mysql的密码本
show charset;
utf8 : 一个字符占3个字节
utf8mb4 (建议): 一个字符占4个字节， 支持emoji
8.0之前，默认字符集 latin1
8.0之后是utf8mb4
create database test charset utf8mb;
show create databses test;
校对规则
主要影响的是数据的排序方式
每种字符集，有多种校对规则（排序规则 ）
show collation;
数据类型
数字类型：整数、小数
tinity 1B = 8bit
int 4B
bigint
| 类型 | 大小（字节） | 范围 |
|---|---|---|
| TINYINT | 1 | （-128,127） |
| SMALLINT | 2 | （-32768,32767） |
| INT（INTEGER） | 4 | |
| BIGINT | 8 |
| 类型 | 大小（字节） | 范围 |
|---|---|---|
| FLOAT | 4 | |
| DOUBLE | 8 |
字符串类型
char(长度)：定长字符串类型 255字符
varchar(长度)：变长字符串类型 65535字符
举例：
char(10)：最多存10个字符，如果存储的字符不够10个，自动用空格填充剩余空间
varchar(10)：最多存10个字符，按需分配空间
| 类型 | 大小（字节） | 用途 |
|---|---|---|
| CHAR | 0-255 | 定长字符串 |
| TEXT | 0-65535 | 长文本 |
| BLOG | 0-65535 | 二进制形式的长文本 |
| VARCHAR | 0-65535 | 变长字符串 |
注意：
（1）varchar类型存储数据时，会判断字符的长度，然后合理分配空间，而char 类型不会判断，直接分配空间，推荐使用varchar
（2）varchar 类型，除了会存储字符串之外，还会额外使用1-2字节存储字符长度
（3）应用场景，字符串固定长度使用 char，不固定使用varchar
（4）括号中的数字问题
括号中，设置的是字符的个数，与字符类型无关
但是，不同种类的字符，占用的存储空间是不一样的
对于英文和数字，每个字符占一个字节长度
对于中文，占用空间大小、要考虑字符集
utf8（3个字节）、utf8mb4（4个字节）
总长度不能超过数据类型的最大长度
时间类型
DATETIME （8字节）
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP （4字节）
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响
| 类型 | 大小（字节） | 格式 |
|---|---|---|
| DATE | 3 | YYYY-MM-DD |
| TIME | 3 | HH:MM:SS |
| YEAR | 1 | YYYY |
| DATETIME | 8 | YYYY-MM-DD HH:MM:SS |
二进制类型
json
{
key: value
}
约束
NOT NULL/NULL：非空，用于该字段的值不能为空
DEFAULT：默认，用于保证该字段具有默认值
PRIMARY KEY：主键，用于保证该字段的只具有唯一性，且不为空
UNIQUE：唯一值，用于保证该字段的值具有唯一性，可以为空
CHECK：检查约束（MySql中不支持，但也不报错）
FOREIGN KEY：外键约束
分类
- 列级约束：上面的约束都可以，但外键约束没有效果
- 表级约束：除了非空、默认，其它的都可以
其它属性
表属性
- engine ： 存储引擎设置，默认是innodb存储引擎
- charset ： utf8mb4
- comment : 注释
列属性
- default: 默认值，与 not null 一起使用
- auto_increment： 数字列自增长，与主键一起使用
- comment： 列的注释
SQL种类
DDL：数据定义语言，主要是对库或表进行创建、删除、修改的操作
DCL：数据控制语言，grant（授权）、revoke（收权）
DML：数据操作语言，对表中的数据进行增、删、改、查
DQL（属于DML）：数据查询语言，查看表数据和元数据
一个很不错的网站传送门
DDL
库的定义
1、建库
CREATE DATABASE test CHARSET utf8mb4;
2、删库
DROP DATABASE test;
3、修改库
-- 修改字符集
ALTER DATABASE test CHARSET <字符集编码>;
-- 修改检验规则
ALTER DATABASE test COLLATE <校对规则>;
4、查看库
show databases;
SHOW CREATE DATABASE test;
表的定义
1、建表
CREATE TABLE <表名> (
<列名><数据类型> [列级完整性约束条件],
<列名><数据类型> [列级完整性约束条件],
<表级完整性约束条件>
);
-- ----------------------------
-- Table structure for Students
-- ----------------------------
CREATE Table Students (
student_id INT(4) NOT NULL PRIMARY KEY,
student_name VARCHAR(6) NOT NULL,
student_gender CHAR(1) NOT NULL,
student_class CHAR(10) NOT NULL,
student_birthday date NOT NULL,
student_tel VARCHAR(20)
);
-- ----------------------------
-- Table structure for Courses
-- ----------------------------
CREATE Table Courses (
course_id VARCHAR(6) NOT NULL PRIMARY KEY,
course_name VARCHAR(20) NOT NULL,
course_credit FLOAT NOT NULL,
coures_time INT NOT NULL,
course_teacher VARCHAR(5),
) CHARSET = utf8;
-- ----------------------------
-- Table structure for Homeworks
-- ----------------------------
CREATE Table Homeworks (
course_id VARCHAR(6) NOT NULL,
student_id CHAR(4) NOT NULL,
homework1 INT(3),
homework2 INT(3),
homework3 INT(3),
constraint homework_id PRIMARY KEY(course_id, student_id),
CONSTRAINT fk_CouHome FOREIGN KEY(course_id) REFERENCES Courses(course_id),
CONSTRAINT fk_StuHome FOREIGN KEY(student_id) REFERENCES Students(student_id)
);
2、删表
DROP TABLE <表名>
3、修改
-- 修改表名
ALTER TABLE <旧表名> RENAME TO <新表名>;
-- 修改字符集
ALTER TABLE test CHARSET = utf8mb4;
-- 修改数据类型
ALTER TABLE test MODIFY s_name CHAR(20) NOT NULL COMMIT '学生姓名';
-- 添加列（末尾添加，前面的话最后面加 FIRST）
ALTER TABLE student ADD COLUMN s_name CHAR(11) NOT NULL COMMIT '学生姓名';
4、查看
use test;
show create table student;
desc student;
DML
1、插入数据
-- 格式
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
-- ----------------------------
-- Records of Students
-- ----------------------------
INSERT INTO Students VALUES
('0433', '张艳', '女', '生物04', '1986-9-13', null),
('0496', '李越', '男', '电子04', '1984-2-13', '1381290××××'),
('0529', '赵欣', '男', '会计05', '1984-1-27', '1350222××××'),
('0531', '张志国', '男', '生物08', '1986-9-10', '1331256××××'),
('0538', '于兰兰', '女', '生物05', '1984-2-20', '1331200××××'),
('0591', '王丽丽', '女', '电子05', '1984-3-20', '1332080××××'),
('0592', '王海强', '男', '电子05', '1986-11-1', null);
-- ----------------------------
-- Records of Courses
-- ----------------------------
INSERT INTO Courses VALUES
('K001', '计算机图形学', 2.5, 40, '胡晶晶'),
('K002', '计算机应用基础', 3, 48, '任泉'),
('K006', '数据结构', 4, 64, '马跃先'),
('M001', '政治经济学', 4, 64, '孔繁新'),
('S001', '高等数学', 3, 48, '赵晓尘');
-- ----------------------------
-- Records of Homeworks
-- ----------------------------
INSERT INTO Homeworks VALUES
('K001', '0433', 60, 75, 75),
('K001', '0529', 70, 70, 60),
('K001', '0531', 70, 80, 80),
('K001', '0591', 80, 90, 90),
('K002', '0496', 80, 80, 90),
('K002', '0529', 70, 70, 85),
('K002', '0531', 80, 80, 80),
('K002', '0538', 65, 75, 85),
('K002', '0592', 75, 85, 90),
('K006', '0531', 80, 80, 90),
('K006', '0591', 80, 80, 80),
('M001', '0496', 70, 70, 80),
('M001', '0591', 65, 75, 75),
('S001', '0531', 80, 80, 80),
('S001', '0538', 60, null, 80);
2、修改数据
UPDATE student SET name = '张三', age = 20 WHERE id = 4;
3、删除数据
-- 删除课程id为4的课程
DELETE FROM tb_courses WHERE course_id=4;
-- 伪删除： update 替代 delete ，添加状态列，1带表存在，0代表删除
ALTER TABLE student ADD COLUMN status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态列：0表示删除，1表示存在';
UPDATE student SET status = 0 WHERE id = 4;
SELECT * FROM student WHERE status = 1;
上面的都是删除一条数据
全表删除
drop table t1
删除表中所有的数据（包括元数据，表的定义等），删除整个表段（rm ibd ）,属于物理性质，会释放磁盘空间
truncate table t1
保留表结构，清空表段中的数据页。属于物理删除，会释放磁盘空间
delete from t1
逐行删除数据，会保留表结构，属于逻辑性质删除。只是标记删除，不会立即释放磁盘空间
这个操作会产生碎片，可以通过日志反转来恢复，即就是 insert
DQL
常见的运算符
算数运算符
加、减、乘、除、取余
逻辑运算符
- 与（AND、&&）
- 或（OR、||）
- 非（NOT、!）
比较运算符
= 和 <=> 的区别
= ：无法比较 NULL，若比较，则返回的结果为 NULL
<=>：可以比较 NULL，一个为 NULL，返回0，两边都为 NULL，返回0
运算符的优先级
show
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like '%xx%' 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in '' 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in '' 查看中继日志的事件信息
show status like '' 查看数据库整体状态信息
select 单独使用
查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
select 函数()
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
select 单表查询
通用的语法
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit
select + from + where
-- 查询city表中，所有中国的城市信息。
select * from city where countrycode = 'CHN';
-- 查询人口数小于100人城市信息
select * from city where population < 100;
-- 查询中国,人口数超过500w的所有城市信息
select * from city where countrycode = 'CHN' and population > 5000000;
-- 查询中国或美国的城市信息。
select * from city where countrycode = 'CHN' or countrycode = 'USA' ;
select * from city where countrycode in ('CHN','USA');
-- 查询人口数为100w-200w城市信息
select * from city where population >= 1000000 and population <= 2000000
# 或者：
select * from city where population between 1000000 and 2000000 ;
-- 查询中国或美国，人口数大于500w的城市
select * from city where (countrycode = 'CNH' or countrycode = 'USA' ) and population > 5000000;
select * from world.city where countrycode in ('CHN','USA') and population >= 5000000;
select + like 模糊查询
%：>= 0个字符
_：一个字符
select * from city where name like 'xi%';
select + from + group by + 聚合函数
聚合函数
count() 统计数量
sum() 求和
avg() 平均数
max() 最大值
min() 最小值
group_concat() 列转行
group by 分组原理
（1）按照分组条件进行排序
（2）进行分组列的去重
（3）聚合函数将其他列的结果进行聚合
-- 统计每个国家的城市个数。
select countrycode,COUNT(name) from city group by countrycode;
-- 统计每个国家的总人口数
select CountryCode as '国家',count(name) as '城市总数',sum(population) as '人口总数'
from city group by CountryCode;
having
和 where 差不多，但是执行顺序在 where 的后面，一般是在 group by 后面
-- 显示总人口超过1亿人的国家
select countrycode,sum(population)
from city
group by countrycode
having sum(population)>100000000;
order by 排序
- desc 降序
- asc 升序
limit 分页
- limit M,N : 跳过M行，显示N行
- limit N offset M : 跳过M行，显示N行
-- 显示6-10名
select * from city where countrycode = 'CHN' order by population desc limit 5,5
-- 后五名
select * from city where countrycode='chn'
order by population asc limit 5;
union 和 union all
作用
多个结果集合并查询的功能
区别
union all 不做去重复
union 会做去重操作
-- 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
-- 可改写为
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
select 多表查询
内连接 inner join
获取两个表中字段匹配关系的记录
left join（左连接）
获取左表所有记录，即使右表没有对应匹配的记录
right join （右连接）
获取右表所有记录，即使左表没有对应匹配的记录
举例
-- 查询老师教授的课程名称
select teacher.tname,course.cname from teacher join course on teacher.tno=course.tno;
-- 统计每个学员学习几门课
select concat(student.sname,'_',student.sno) as '学生姓名',count(sc.cno) as '课程总数'
from student join sc on student.sno=sc.sno group by student.sno,student.sname;
-- 统计每个学员，学习课程的门数和课程名
select student.sname as '学员',count(sc.cno) as '课程总数',group_concat(course.cname) as '课程名'
from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
group by student.sno,student.sname;
-- 每位老师教的学生数量和学生名列表
SELECT teacher.tname,COUNT(*),GROUP_CONCAT(student.sname)
FROM teacher
JOIN course ON teacher.tno=course.tno
JOIN sc ON course.cno=sc.cno
JOIN student ON sc.sno=student.sno
GROUP BY teacher.tno;
-- 每位老师教所教课程的平均分
select teacher.tname as '教师',course.cname as '课程名称',avg(sc.score) as '平均分'
from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno group by teacher.tno,course.cno;
扫一扫
1393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
