常用的数据类型：
| 类型 | 含义 |
|---|---|
| tinyint(n) | 1个字节，范围(-128~127) |
| smallint(n) | 2个字节，范围(-32768~32767) |
| mediumint(n) | 3个字节，范围(-8388608~8388607) |
| int(n) | 4个字节(32个比特位)，整数型，范围(-2147483648~2147483647) |
| bigint(n) | 8个字节，整数型，范围(+-9.22*10的18次方) |
| float(m,d) | 单精度浮点，8位精度，4字节32位。m数字总个数，d小数位 |
| double(m,d) | 双精度浮点，16位精度，8字节64位 。m总个数，d小数位 |
| char | 固定长度的字符类型 |
| varchar | 可变长度的字符类型 |
| text | 文本 |
| image | 图片 |
| decimal(5,2) | 5个有效长度数字，小数点后面有2位（例如123.56） |
具体说明：
1、int(N)
int(N)中的N不是限制字段取值范围的，int的取值范围是固定的（0至4294967295）或（-2147483648至2147483647）。N这个值是为了zerofill在字段中的值不够时补零的。
int默认是signed（有符号），取值范围（-2147483648至2147483647）。如果加了unsigned（ 无符号）参数那么取值范围就为（0至4294967295）。
2、float(m,d)
设一个字段定义为float(6,3)，表示6个有效长度数字，小数点后面有3位。如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位。如果插入数12.123456，存储的是12.123，如果插入12.12，存储的是12.120。
整数部分最大是3位，如果插入1234.56，会插入失败。
3、char与varchar
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：
| 值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
|---|---|---|---|---|
| '' | ' ' | 4个字节 | '' | 1个字节 |
| 'ab' | 'ab ' | 4个字节 | 'ab ' | 3个字节 |
| 'abcd' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
| 'abcdefgh' | 'abcd' | 4个字节 | 'abcd' | 5个字节 |
字节大小：
- char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节。
- varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节。
优劣比较：
- varchar比char节省磁盘空间。
- 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片。
数据库对象和命名：
数据库的组件(对象)：
- 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。
命名规则：
- 必须以字母开头，后续可以包括字母，数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字，如table、select、show、databases。
- 数据库名、表名、用户名区分大小写
SQL语言分类
1、DDL：数据定义语言（Data Definition Language），用于创建数据库对象，如库、表、索引等。
例如：CREATE，DROP，ALTER 等。
2、DML：数据操纵语言（Data Manipulation Language），用于对表中的数据进行管理。
例如： SELECT、UPDATE、INSERT、DELETE 等。
3、DQL：数据查询语言（ Data Query Languag ），用于从数据表中查找符合条件的数据记录。
例如： SELECT
4、DCL：数据控制语言（Data Control Language），用于设置或者更改数据库用户或角色权限
例如： GRANT，REVOKE
5、TCL：事务控制语言（Transaction Control Language），用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。
例如： COMMIT，ROLLBACK，SAVEPOINT
SQL语句基础
SHOW DATABASES; # 查看所有数据库
USE nsd2021; # 切换指定数据库
SHOW TABLES; # 查看当前库中所有的表
DESC departments; # 查看表结构
mysql> SELECT DATABASE(); # 查看当前所处的数据库
mysql> SELECT USER(); # 查看当前登陆用户
mysql> SELECT VERSION(); # 查看版本
DDL
数据库操作
create database 数据库名;新增数据库
drop database 数据库名；删除数据库
数据表操作
新建表
create table 表名(
字段名 类型(长度) 约束,
字段名 类型(长度) 约束
);
约束：
主键约束（primary key） PK
自增长约束（auto_increment）
非空约束（not null）
唯一性约束（unique）
默认值约束（default）
零填充约束（zerofill）
外键约束（foreign key）FK
复制表： create table 表名 like 被复制的表名;
删除表
drop table 表名; 删除指定的数据表
修改表结构
alter table 旧表名 rename 新表名 #修改表名
alter table 表名 change 旧字段 新字段 数据类型; #修改列名
alter table 表名 add 字段 数据类型; #添加列
alter table 表名 drop 字段名; #删除列
alter table 表 modify [COLUMN] 列名 类型#修改列的类型或约束
ALTER TABLE t1 add primary key (stuid); 增加主键
ALTER TABLE t1 drop primary key 删除主键
与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。
#为从表设置外键
alter table stu add foreign table (key) references table(key)
删除外键约束字段先删除外键约束，再删除外键名
删除主键表时必须先删除其他与之关联的表
表复制
create table 待创建的表名 like 已有表名 #仅复制表结构
create table 待创建的表名（select 字段, ... from 已有表名 ）#复制表结构及数据
注意：会丢失部分约束关系
查看表
show tables; 查看所有表
desc 表名; 查看表结构
DML
插入数据
insert into 表名(列名1,列名2,...列名n) values
(值1,值2,...值n)，
...
(值1,值2,...值n);
insert into 表名 values #直接赋值，每个人字段都要赋值
(值1,值2,...值n),
...
(值1,值2,...值n);
删除数据
#格式：
delete from 表名; 删除所有数据 保留原来最大数据id
truncate table 表名; 清空所有数据，
TRUNCATE不能回滚，DELETE可以
delete from 表名 [where 条件表达式]; 删除指定行数据
#示例：删除表中id为4的数据
delete from info where id=4
修改、更新数据
update 表名 set 列名1=列值1，列名2=列值2... where 列名=值
查看数据
select 字段名1,字段名2[,...] from 表名 [where 条件表达式];
select * from 表; #查看所有数据
select * from 表 \G; #示例3：以列表方式竖向显示
select * from 表 limit 2;#只显示头2行
select * from 表名 limit 2,3; #显示第2行后的前3行（即显示第3~5行）
进阶语句
distinct，不显示重复的数据记录。
SELECT DISTINCT "字段" FROM "表名";
SELECT "字段" FROM "表名" WHERE "条件1" AND "条件2";
SELECT "字段" FROM "表名" WHERE "条件1" OR "条件2";
SELECT "字段" FROM "表名" WHERE "字段" LIKE ' ' ;
%：百分号表示零个、一一个或多个字符 _：划线表示单个字符
SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC|DESC] ;
#ASC是按照升序进行排序的，是默认的排序方式。
#DESC是按降序方式进行排序。
- 一般对数值字段进行排序。
- 如果对字符类型的字段进行排序，则会按首字母排序
数学函数
| 数学函数 | 作用 |
|---|---|
| abs(x) | 返回x的绝对值 |
| rand() | 返回0到1的随机数 |
| mod(x, y) | 返回x除以y以后的余数 |
| power(x, y) | 返回x的y次方 |
| round(x) | 返回离x最近的整数 |
| round(x, y) | 保留x的y位小数四舍五入后的值 |
| sqrt(x) | 返回x的平方根 |
| truncate(x, y) | 返回数字x截断为y位小数的值 #不四舍五入 |
| ceil(x) | 返回大于或等于x的最小整数 |
| floor(x) | 返回小于或等于x的最大整数 |
| greatest(x1,x2,...) | 返回集合中最大的值 |
| least(x1,x2,...) | 返回集合中最小的值 |
聚合函数
| 聚合函数 | 含义 |
|---|---|
| avg() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
- count(列名)：只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行。
- count(*) ：包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL。
字符串函数
| 字符串函数 | 作用 |
|---|---|
| trim() | 返回去除指定格式的值 |
| concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
| substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同 |
| substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 |
| length(x) | 返回字符串 x 的长度 |
| replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
| upper(x) | 将字符串 x 的所有字母变成大写字母 |
| lower(x) | 将字符串 x 的所有字母变成小写字母 |
| left(x,y) | 返回字符串 x 的前 y 个字符 |
| right(x,y) | 返回字符串 x 的后 y 个字符 |
| repeat(x,y) | 将字符串 x 重复 y 次 |
| space(x) | 返回 x 个空格 |
| strcmp(x,y) | 比较 x 和 y，返回的值可以为-1,0,1 |
| reverse(x) | 将字符串 x 反转 |
进阶语句
分组
SELECT 字段1,函数(字段2) FROM 表名 GROUP BY 字段1;
SELECT 字段1,SUM(字段2) FROM "表格名" GROUP BY 字段1 HAVING(函数条件) ;
- HAVING语句的存在弥补了WHERE 关键字不能与聚合函数联合使用的不足。
别名
SELECT 字段1,字段2 AS 字段2的别名 from 表名; #AS可以省略不写
嵌套
SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] #外查询
(SELECT "字段1" FROM "表格2" WHERE "条件") ; #内查询
update students set age= (select avg(age) from students) where stuid=25;
select * from 表 where a.字段= where b.字段 连接有相同列的表
竖向合并
[select 语句1] UNION [select 语句2];
纵向合并 字段数一样 子段类型要要一致 字段顺序最好要一样 合并后去重
[select 语句1] UNION ALL [SELECT 语句2];
结果不去重
连接查询
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录。
A 表全要 ， B表只要相同的部分
right join（右连接）返回包括右表中的所有记录和左表中联结字段相等的记录。
inner join(内连接)：只返回两个表中联结字段相等的行。
A 表 B表 取交集
第一张表的内容 inner join 第二张表 on 条件
方法一: select * from location A inner join store_info B on A.store_name=B.store_name;
方法二: select * from location A, store_info B where A.store_name=B.store_name;
