mysql基础看这一篇就够了

mysql在项目中用的很多了，大都是简单的增删改查，这里想做一个总结，主要是一些基础知识和一些项目中可能会遇到的坑。

这篇文章主要包括一下几个部分：

1. mysql的数据类型
2. DDL常见操作
3. DML常见操作
4. select查询，以及踩坑
5. 排序与分页以及踩坑
6. 分组查询
7. 函数库
8. 连接查询
9. 子查询
10. NULL问题

1、mysql的数据类型：

mysql的基本数据类型主要包括下面四类：

• 整数类型
  • bit、bool、tinyint、smallint、mediumint、int、bigint
• 浮点类型
  • float、double、decimal
• 字符类型
  • char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
• 日期类型
  • Date、DateTime、TimeStamp、Time、Year

整数类型和浮点类型

 日期类型：

字符类型：

类型（n）:

在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法个人感觉在开发过程中没有什么用途，不过还是来说一下，int(N)我们只需要记住两点：

• 无论N等于多少，int永远占4个字节

• N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。

浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

mysql> create table test5(a float(5,2),b double(5,2),c decimal(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 values (1,1,1),(2.1,2.1,2.1),(3.123,3.123,3.123),(4.125,4.125,4.125),(5.115,5.115,5.115),(6.126,6.126,6.126),(7.116,7.116,7.116),(8.1151,8.1151,8.1151),(9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),(11.12501,11.12501,11.12501);
Query OK, 7 rows affected, 5 warnings (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 5

mysql> select * from test5;
+-------+-------+-------+
| a     | b     | c     |
+-------+-------+-------+
|  1.00 |  1.00 |  1.00 |
|  2.10 |  2.10 |  2.10 |
|  3.12 |  3.12 |  3.12 |
|  4.12 |  4.12 |  4.13 |
|  5.12 |  5.12 |  5.12 |
|  6.13 |  6.13 |  6.13 |
|  7.12 |  7.12 |  7.12 |
|  8.12 |  8.12 |  8.12 |
|  9.13 |  9.13 |  9.13 |
| 10.12 | 10.12 | 10.12 |
| 11.13 | 11.13 | 11.13 |
+-------+-------+-------+
11 rows in set (0.00 sec)

 

结果说明（注意看）：

c是decimal类型，认真看一下输入和输出，发现decimal采用的是四舍五入

认真看一下a和b的输入和输出，尽然不是四舍五入，一脸闷逼，float和double采用的是四舍六入五成双

decimal插入的数据超过精度之后会触发警告。

什么是四舍六入五成双？

就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉

 mysql类型和Java类型的对应关系：

---

2、DDL常见操作

DDL：Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。

如：建库、删库、建表、修改表、删除表、对列的增删改等等。

建库通用语法：

drop database if exists 旧库名;
create database 新库名;

创建表：

create table 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

注意：

1. 在同一张表中，字段名不能相同

2. 宽度和约束条件为可选参数，字段名和类型是必须的

3. 最后一个字段后不能加逗号

4. 类型是用来限制 字段 必须以何种数据类型来存储记录

5. 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)

6. 类型后写的 约束条件 是在类型之外的 额外添加的约束

约束说明：

not null：标识该字段不能为空

default value：为该字段设置默认值，默认值为value

primary key：标识该字段为该表的主键，可以唯一的标识记录，插入重复的会报错

两种写法，如下：

方式1：跟在列后

create table test3(
    ->   a int not null comment '字段a' primary key
    -> );

方式2：在所有列定义之后定义，如下：

 create table test4(
    ->   a int not null comment '字段a',
    ->   b int not null default 0 comment '字段b',
    ->   primary key(a)
    -> );

方式3支持多字段，多个之间用逗号隔开，语法：primary key(字段1,字段2,字段n)，示例

create table test7(
    ->    a int not null comment '字段a',
    ->    b int not null comment '字段b',
    ->   PRIMARY KEY (a,b)
    ->  );

外键：foreign key：为表中的字段设置外键

语法：foreign key(当前表的列名) references 引用的外键表(外键表中字段名称)

注意几点：

• 两张表中需要建立外键关系的字段类型需要一致

• 要设置外键的字段不能为主键

• 被引用的字段需要为主键

• 被插入的值在外键表必须存在

unique key(uq)：标识该字段的值是唯一的

支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。

定义有2种方式。

方式1：跟在字段后

 create table test8(
    ->    a int not null comment '字段a' unique key
    ->  );

方式2：所有列定义之后定义

create table test9(
    ->    a int not null comment '字段a',
    ->   unique key(a)
    ->  );

方式3:  支持多字段，多个之间用逗号隔开，语法： uniqe key(字段1,字段2,字段n)

create table test10(
    ->   a int not null comment '字段a',
    ->   b int not null comment '字段b',
    ->   unique key(a,b)
    -> );

3 DML常见操作：

DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的select熟称CRUD（增删改查）

这里说一下delete语句吧

delete [别名] from 表名 [[as] 别名] [where条件];

注意：

如果无别名的时候，表名就是别名

如果有别名，delete后面必须写别名

如果没有别名，delete后面的别名可以省略不写。

使用truncate删除

语法

truncate 表名;

 

drop，truncate，delete区别

• drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉，以后要新增数据是不可能的，除非新增一个表。

  drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

  如果要删除表定义及其数据，请使用 drop table 语句。

• truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据而已。

  注意：truncate不能删除具体行数据，要删就要把整个表清空了。

• delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

  truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

  truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

  对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

  delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

  truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

  如果有自增列，truncate方式删除之后，自增列的值会被初始化，delete方式要分情况（如果数据库被重启了，自增列值也会被初始化，数据库未被重启，则不变）

• 如果要删除表定义及其数据，请使用 drop table 语句

• 安全性：小心使用 drop 和 truncate，尤其没有备份的时候，否则哭都来不及

• 删除速度，一般来说: drop> truncate > delete

 4、select查询

基本语法：

select 列 from 表名

1、查询常量

select 常量值1,常量值2,常量值3;

2、查询表达式

select 1+2,3*10,10/3;

3、查询函数

 select mod(10,4),isnull(null),ifnull(null,'第一个参数为空返回这个值');

4、查询指定的字段

select 字段1,字段2,字段3 from 表名;