MySQL数据库操作与SQL语句详解-优快云博客

CREATE TABLE 表名(

字段1名类型 [默认值，约束条件],

字段2名类型 [默认值，约束条件],

....

)

CREATE DATABASE mydb; 先创建一个数据库mydb

USE mydb; 切换到mydb数据库

下面创建表时，都是创建到mydb这个库中

CREATE TABLE user(

id INT, 在MySQL中整数类型为int

username VARCHAR(30), VARCHAR为变长字符串，指定长度为字节，30表示该

password VARCHAR(30), 字符串转换为字节后最多存30字节(UTF8编码中文10字)

age INT(3) int若指定长度为整数的位数

)

查看表结构

DESC 表名

DESC user;

查看创建表的详细信息

SHOW CREATE TABLE 表名

SHOW CREATE TABLE user;

查看当前数据库中已创建的表

SHOW TABLES;

修改表名

RENAME TABLE 原表名 TO 新表名

将user表改名为userinfo

RENAME TABLE user TO userinfo

删除表

DROP TABLE 表名

DROP TABLE userinfo;

修改表

CREATE TABLE hero(

name VARCHAR(30),

age INT(3)

)

添加字段

在表的末尾追加新的字段

ALTER TABLE 表名 ADD 字段名类型

向表中追加性别字段

ALTER TABLE hero ADD gender VARCHAR(10);

在表的最开始插入新字段

ALTER TABLE 表名 ADD 字段名类型 FIRST

ALTER TABLE hero ADD id INT FIRST;

在表中插入字段

将指定的字段放在表中某字段的后面

ALTER TABLE 表名 ADD 字段名类型 AFTER 表中某字段

在名字后面追加密码字段

ALTER TABLE hero ADD pwd VARCHAR(30) AFTER name

删除字段

ALTER TABLE 表名 DROP 字段名

删除hero表中的pwd字段

ALTER TABLE hero DROP pwd;

修改字段

ALTER TABLE 表名 CHANGE 原字段名新字段名类型

将hero表中年龄字段的长度改为5

ALTER TABLE hero CHANGE age age INT(5)

将hero表中gender字段改名为pwd并且类型为VARCHAR(30)

ALTER TABLE hero CHANGE gender pwd VARCHAR(30)

注意事项：

修改字段时，如果表中已经包含数据：

当修改字段类型的长度时，应当只增不减，由于原字段某记录的值长度缩小了会导致修改失败。

当修改字段类型时，尽量不进行。例如将name字段改为int型。"张三"怎么改成int??

DML数据操作语句

对表中记录进行操作的语言

关键字：INSERT，UPDATE，DELETE

准备一张表

CREATE TABLE person(

name VARCHAR(30),

age INT(3)

)

插入数据(INSERT语句)

INSERT INTO 表名 [(字段1,字段2,.....)] VALUES (字段值1,字段值2,.....)

INSERT INTO person (name ,age) VALUES ('张三',18)

INSERT INTO person (age,name) VALUES (20,'李四')

注：数据库中，字符串得字面量使用单引号，并且字符串得内容是区分大小写的

插入数据时指定的字段的顺序可以与表不同，但是VALUES后面指定的值必须与指定的字段顺序、类型、个数一致。

插入默认值

当插入数据时，某个字段不指定时，则会插入默认值，当表中该字段没有明确指定默认值是，字段默认值为NULL

INSERT INTO person (name) VALUES ('王五')

没有指定age，则该条记录插入后age字段的值为null

默认值的指定

注：这属于DDL语句的范畴，使用DEFAULT指定默认值

创建表时

CREATE TABLE person(

name VARCHAR(30) DEFAULT '无名氏',

age INT(3)

)

修改表时

ALTER TABLE person CHANGE age age INT(3) DEFAULT 18;

修改后，age字段的默认值为18

INSERT INTO person (name) VALUES ('赵六');

插入数据后，赵六的年龄为18(默认值)

全列插入

当不指定字段名时，则为全列插入，此时VALUES后面指定的值得顺序，类型，个数必须与表结构一致。

INSERT INTO person VALUES ('钱七',25)

年龄插入默认值:使用DEFAULT关键字代替值

INSERT INTO person VALUES ('孙八',DEFAULT)

插入NULL值:使用NULL关键字代替值

INSERT INTO person VALUES ('周九',NULL)

错误示范：

INSERT INTO person VALUES ('老十')     列对应值得个数不匹配

INSERT INTO person VALUES(22,'老十')        列对应的值类型不匹配(第一列应是名字

                                                                            VARCHAR类型)

修改数据(UPDATE语句)

UPDATE 表名 SET 字段1名=字段1值[,字段2名=字段2值,.....]

[WHERE 过滤条件]

UPDATE person SET age=40

上述SQL执行后person表中每条记录的age字段值都会被改为40!

WHERE字句在UPDATE中的使用

通常修改表中数据时，要在UPDATE语句中添加WHERE字句，WHERE字句的作用是添加过滤条件用于帅选要修改的记录。

字句：在SQL中一个关键字后面搭配一些内容，这个就是一个字句

将张三的年龄改为20岁

UPDATE person SET age=20

WHERE name='张三'

上述SQL中，WHERE条件的作用是帅选出修改的记录

上述SQL中只有张三的记录符合WHERE条件，因此只有该条记录的年龄会被修改为20，而其他记录则不会被修改

数据库在修改记录时是逐条进行修改的，记录是否被修改取决于这条记录是否满足WHERE指定的条件。

将person表中年龄为40岁的改为36岁

UPDATE person SET age=36

WHERE age=36

上述SQL，person表中凡是年龄为40的记录都会被改为36

WHERE中还可以使用：>,>=,<,<=,<>

注：不等于使用"<>",而"!="不是所有数据库都支持

将person表中年龄大于30岁的人改为年龄29岁

UPDATE person SET age=29

WHERE age>30

使用表达式进行修改

为person表中每个人的年龄涨一岁

UPDATE person SET age=age+1

修改多个字段

将person表中的"李四"改名为"李老四"，年龄改为55岁

UPDATE person SET name='李老四',age=55

WHERE name='李四'

删除数据(DELETE语句)

DELETE FROM 表名 [WHERE 过滤条件]

删除名字为"李老四"的记录

DELETE FROM person

WHERE name='李老四' 删除表中满足WHERE条件的记录

删除年龄小于25岁的人

DELETE FROM person

WHERE age<25

注：在DELETE语句中不添加WHERE子句就是清空表操作

DELETE FROM person

DML数据操作语句总结

DML:数据操作语言,它是对表中数据进行操作的语言,涵盖的操作:增(INSERT),删(DELETE)，改(UPDATE)

INSERT语句用于向表中插入数据：

插入数据时，指定的字段名的顺序可以与表不相同，但是VALUES子句中指定的值要与指定的字段顺序，个数，类型都相同

插入数据时可以忽略某些字段，此时插入的记录中没有指定的字段会插入该字段的默认值

可以使用DEFAULT关键字显示的为某个字段插入默认值

可以使用NULL显示的为某个字段插入NULL值

插入数据时可以不指定任何字段，此时为全列插入，VALUES子句要求指定的值的顺序，个数必须与表一致

UPDATE语句用于修改表中数据：

修改数据时通常会指定WHERE子句，否则为全表所有记录进行修改

DELETE语句用于删除表中的数据：

删除数据时通常也要指定WHERE子句，否则是清空表操作。

DQL语言(数据查询语言)

DQL是用来检索表中数据使用的语言

执行顺序

SELECT 查询字段 6

FROM 选表 1

JOIN...ON...关联 2

WHERE 初步筛选 3

GROUP BY 分组 4

HAVING 对分组数据进行筛选 5

ORDER BY 排序 7

LIMIT 分页 8

基础查询

一条DQL中至少会包含的两个字句:SELECT字句和FROM字句

SELECT字句用于指定查询表中那些列的内容

FROM字句用于指定从那些表中查询数据

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

SELECT * FROM teacher

上述SQL用于检索teacher表中所有数据，并且在查询的结果集中列出该表中所有字段(*的作用就是列出所有字段)，但是数据库执行该SQL时首先会查询数据字典了解teacher表中共有多少个字段后才能开始执行真实的查询，这是比较浪费性能的操作，一旦查询频繁，问题会凸显，因此我们执行SQL时应当明确指出要查询的字段，哪怕是全列查询也不要使用" * "

WHERE字句

在DQL中，WHERE字句是用于过滤需要查询的记录。只有满足过滤条件的记录才会被列在查询结果集中。

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

WHERE 条件字句

使用AND和OR连接多个条件

AND的优先级是高于OR的,可以使用()去提高OR的优先级

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

WHERE 条件1 AND[OR] 条件2

IN(列表):等于列表中的其中之一

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

WHERE 字段 IN (' ',' '...)

NOT IN(列表):不再列表中，不能等于列表中的任何一项

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

WHERE 字段 NOT IN (' ',' '...)

BETWEEN...AND...在一个范围内

SELECT 字段1[,字段2,字段3...(也可以时函数或表达式)]

FROM 表1[,表2,表3...(子查询或视图)]

WHERE 字段 BETWEEN 上限 AND 下限

DISRINCT去重，去除结果集中指定字段值重复的记录

DISTINCT关键字必须紧跟在SELECT关键字之后

DISTINCT后面可以指定多个字段，当指定了多个字段时效果为这些字段值得组合相同的记录会被去重

SELECT DISTINCT 字段1[,字段2,...]
FROM 表名
...

LIKE模糊查询

LIKE中有两个通配符:

%:百分号表示有任意个字符(0-多个)

_:下划线表示1个字符

LIKE '%x%':字符中含有x

LIKE '_x%':字符串中第二个字符是x

LIKE 'x%':字符串中第一个字符是x

LIKE '%x':字符串中最后一个字符是x

LIKE '%x_y':字符串中倒数第三字符是x并且最后一个字符是y

NULL值判断

判断NULL值要使用: IS NULL

判断不为NULL值要使用:IS NOT NULL

不能使用=和<>判断NULL值

ORDER BY字句

ORDER BY字句是用来对查询结果集排序的

ORDER BY字段名[ASC]:将结果集按照指定的字段值从小到大排序

ORDER BY 字段名 DESC:将结果集按照指定的字段值从大到小排序

ORDER BY在不指定排序方式时，默认为升序

ORDER BY也可以按照多字段排序:ORDER BY 字段1 ASC|DESC,字段2 ASC|DESC,...

按照多字段排序时，排序存在优先级，首先按照第一个字段排序，再按照第二个字段排序。

排序的规则:当按照第一个字段排序后，第一个字段值相同的记录再按照第二个字段的值排序，以此类推

ORDER BY只能是DQL语句中的最后一个字句(LIMIT 算是ORDER BY的附属)

LIMIT分页查询

当一条SQL语句查询出的结果集记录数过多时，会消耗大量的系统资源，因此通常都会采取分段分批的方式

在MySQL中使用LIMIT来完成，在ORACLE中使用伪列ROWNUM实现

ORDER BY ...

LIMIT N,M

N:表示跳过结果集中N条记录

M:表示从跳过的N条记录后查询M条记录

在分页中常用的参数:

页数:查看第几页数据

每页显示的条目数

计算公式:

N的公式(页数-1)*每页显示的条目数

M就是每页显示的条目数

注：当ORDER BY字句指定的字段值多行记录相同时，结果集中这些记录的顺序可能是不同的，因此LIMIT分页会出现重复的数据或丢失数据的现象。

解决办法，添加其他字段(该字段值最好不重复，例如id字段)进行排序确保顺序

DQL中可以使用函数或表达式进行查询

在SELECT字句中使用表达式

SELECT name,salary,salary*12
FROM teacher

在SELECT字句中使用函数

IFNULL函数:IFNULL(arg1,arg2)

当arg1不为NULL时函数返回arg1的值

当arg1不为NULL时函数返回arg2的值

IFNULL函数的作用就是将一个NULL值替换为一个非NULL值

内部逻辑示意

IFNULL(arg1,arg2){

if(arg1!=null){

return arg1;

}else{

return arg2;

}

}

在WHERE字句中使用表达式

SELECT name,salary,salary*12
FROM teacher
WHERE salary*12<60000

在WHERE子句中使用函数

SELECT name,comm
FROM teacher
WHERE IFNULL(comm,0)<3000

别名

在SELECT字句中为函数或表达式取别名，增强可读性。

别名也可以在FROM字句中为表取别名，便于关联查询使用。

字段名别名

SELECT salary*12 salaries
FROM teacher

字段名 AS 别名

SELECT salary*12 AS salary
FROM teacher

字段名 [AS] '别名'

SELECT salary*12 AS 'salary'
FROM teacher

字段名 [AS] "别名"

SELECT salary*12 AS "salary"
FROM teacher

别名如果是SQL关键字或者包含空格时，要使用引号

SELECT salary*12 from 语法错误，因为会将from当成关键字使用
FROM teacher

正确写法:
SELECT salary*12 'from'
FROM teacher

SELECT salary*12 annu sal 数据库会理解为annu为别名，sal就不能被识别了
FROM teacher

正确写法
SELECT salary*12 'annu sal'
FROM teacher

聚合函数

聚合函数又称为:分组函数，多行函数。对结果集中记录进行统计的

聚合函数可以将多条记录中同一字段的值进行统计并最终得到一条结果

聚合函数：

MIN:求指定字段的最小值

MAX:求最大值

AVG:求平均值

SUM:求和

COUNT:统计记录的行数

聚合函数是忽略NULL值的

MIN,MAX,AVG,SUM都是对值得统计，COUNT是对记录数的统计

GROUP BY字句

GROUP BY字句是仅用于配合聚合函数的，它可以对结果集中指定字段值相同的记录进行分组，在组内使用聚合函数统计结果。

如果SELECT字句中没有聚合函数，就不会使用GROUP BY

在SELECT子句中出现了聚合函数，那么不在聚合函数中的其他都应当出现在GROUP BY子句中。

按照单字段分组

SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id 在查询出的结果集中，subject_id字段值相同的记录会被分为一组

按照多字段分组

GROUP BY字句后可以指定多个字段，将结果集中这些字段值组合相同的记录看作一组

SELECT COUNT(*),class_id,job
FROM student
GROUP BY class_id,job 在结果集中只有class_id和job字段值都相同的记录会被划分

为一组

按照聚合函数排序

SELECT AVG(salary) avg,subject_id
FROM teacher
GROUP BY subject_id
ORDER BY avg

HAVING字句

HAVING字句紧跟在GROUP BY字句之后，用于过滤分组。

HAVING子句中可以使用聚合函数作为过滤条件

注：WHERE字句中不能使用聚合函数

原因：WHERE与HAVING的过滤时机不同

WHERE是在检索表中数据时进行过滤的，从而将满足该过滤条件的记录产生查询结果集

HAVING是在产生结果集后根据GROUP BY字句按照指定字段对结果集分组后，添加过滤条件来筛选需要的分组

WHERE先进行过滤，用于确定结果集。HAVING后过滤，用于确定GROUP BY分组

    句执行顺序
SELECT AVG(salary),subject_id             4：对最终包流的两组数据进行统计
FROM teacher                                           1：确定数据来源的表
GROUP BY subject_id 2：确定数据的分组(按照subject_id将数据分6组)
HAVING AVG(salary)>6000                       3：需要保留的分组(仅有2组满足要求)

子查询

嵌套在其他SQL语句中的一个DQL语句被称为子查询

子查询使用场景

DQL中：基于一个查询结果集再进行二次查询

DML中：基于一个查询结果集进行数据操作

DDL中：基于一个查询结果集操作数据库对象(表，视图)

子查询分类

单行单列子查询：该子查询的查询结果集仅有一行且这一行仅有一列。该查询结果就只有一个值。

多行单列子查询：该查询结果集仅有一列，但是存在多行。

多行多列子查询：该结果集就是一个表格

单列子查询通常用于DQL语句，多列子查询通常用于DDL语句

在DQL中使用子查询

SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary FROM teacher WHERE name='张三')

使用多行单列子查询

使用多行单列子查询时，那么在WHERE中作为过滤条件判断时需要搭配使用:

如：>,>=,<,<=,=,<>此时需要使用ANY，ALL，IN等

ANY和ALL是搭配>,>=,<,<=:

>ANY:大于列表中的其中之一，大于最小的即可

<ANY:小于列表中其中之一，小于最大的即可

>ALL:大于列表中所有的，大于最大的

<ALL:小于列表中所有的，小于最小的

IN和NOT IN是代替=和<>

SELECT name,class_id
FROM student
WHERE class_id IN(SELECT class_id FROM student WHERE name IN('张三','李四'))

在DML语句中使用子查询

UPDATE teacher
SET salary = salary+500
WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='王五')

在DDL语句中使用子查询

可以将子查询的结果集当作表创建出来

创建一张表用于记录老师工资的统计情况(按照科目)。要记录每门课老师的最高工资，最低工资，平均工资和工资总和以及科目编号。表名:teacher_salary_info

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),subject_id
FROM teacher
GROUP BY subject_id

此时可以基于该查询创建表
CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary) max_salary,MIN(salary) min_salary,
AVG(salary) avg_salary,SUM(salary) sum_salary,
subject_id
FROM teacher
GROUP BY subject_id
注:如果子查询的SELECT子句中出现了函数或表达式时，必须指定别名(别名会组为表的字段名)

关联查询

查询结果集中的数据来自于多张表，联合多张表进行查询。

表与表中的数据会有一定的对应关系，被称为关联关系

关联关系有三种：

一对一：A表中的一条记录仅唯一对应B表中的一条记录

一对多：A表中的一条记录可以对应B表中的多条记录

多对多：A表和B表中的记录双向看待都是一对多时就是多对多

在关联查询中，非常重要的一个点就是指定连接条件，用于让数据库知道两表中数据是如何对应的.延伸出多张表之间也需要分别与之两张表的关系。总结：N张表关联，至少要有N-1个连接条件

笛卡尔积：

如果连接条件不指定，或者连接条件无效时会产生笛卡尔积，这通常是一个无意义的结果集，性能和时间消耗量惊人，要尽量避免，严重时可能导致服务器宕机。

关联查询的语法

SELECT 表中对应的字段
FROM 表A,表B,...
WHERE 连接条件连接条件必须与过滤条件同时成立
AND 过滤条件

笛卡尔积的产生

SELECT t.name,t.salary,t.subject_id,s.name
FROM teacher t,subject s

不指定连接条件时，会产生笛卡尔积
数据库会将teacher表中的每条数据与subject表中每条数据都连接一次并产生一条记录
产生的结果集中的记录数为teacher表记录数与subject表记录数的乘积

关联查询中必须使用过滤条件

连接条件必须与过滤条件同时满足

SELECT c.name,c.floor,t.name,t.salary
FROM class c,teacher t
WHERE c.teacher_id=t.id 连接条件
AND t.name='王五' 过滤条件

N张表关联查询，至少要有N-1个连接条件

1：数据来自哪些表?
teacher表，class表，student表

2:连接条件
student表与class表连接条件:s.class_id=c.id
class表与teacher表连接条件:c.teacher_id=t.id

3:过滤条件
老师的名字:t.name='老刘'

SELECT s.name,s.age,c.name,t.name
FROM teacher t,class c,student s
WHERE s.class_id=c.id               连接条件
AND c.teacher_id=t.id               连接条件
AND t.name='老刘'

关联查询中使用聚合函数

查看教语文的老师平均工资是多少?

SELECT AVG(t.salary)
FROM teacher t,subject s
WHERER t.subject_id=s.id
AND s.name='语文'

主键和外键

在多表关联查询中，连接条件通常就是两张表中某个字段值做等值判断

通常这两个字段就分别时两张表中的主键与外键

例:学生表student与班级表class关联查询时

对于班级表(class表)而言，id字段就是该表的主键:主键字段的特点:非空且唯一

对于学生表(student表)，每个学生都要记录他所在的班级信息，对此我们在student表中设计了一个字段class_id,

而该字段保存的就是class表中的id字段的值。用于使每个学生都能与对应的班级记录关联在一起。那么在学生表中这个class_id(用于记录class表主键字段的值)字段就称为是class表对应的外键。

并且在关联关系中，包含外键字段的表永远多"多"的那一方。例如:class表与student表就是"一对多"

外键在数据库中也存在外键约束

外键约束的要求:

外键字段保存的值必须是其对应的主键字段中已有的值

例如:student表与class表。 student表的class_id为外键，对应的是class表id这个主键

class表有3条记录，id分别是1,2,3。此时student表任何记录中class_id可选值仅为1,2,3,NULL。若class_id值为4则违反外键约束。

删除主键表中的某条记录时，必须保证该记录主键所对应的外键表中没有一条记录的外键保存该主键字段值。

例如：student表与class表。 student表的class_id为外键，对应的是class表id这个主键

class表有1条id为1的班级记录，同时student表中有三个学生记录的class_id为1.此时若删除class表中id为1的这条记录是不被允许的(原因是3个学生的外键记录着该该主键值)

多对多关系

多对多关系在表设计时，通常要添加一张关联关系表来维持两张表的多对多关系

关联关系表通常不需要主键字段，并且一定有两个字段分别是多对多关系的两张表中各自主键所对应的外键字段

t_stu_subject_score表，该表有两个字段stu_id，subject_id分别记录了student表的id和subject表的id。因此依靠这张表就可以实现student与subject表的多对多关系

查看'张三'都学了哪门课程以及成绩

SELECT s.name,su.name,tss.score
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id
AND su.id=tss.subject_id
AND s.name='张三'

内连接

内连接与关联查询效果一致，特点：将连接条件单独定义在ON子句中，不在WHERE中与过滤条件混淆。结构看起来更清晰

SELECT 字句

FROM 表1

JOIN 表2 ON 连接条件(表2与表1的连接条件)

[JOIN 表3 ON 连接条件(表3与表2或表1的连接条件)]

...

查看1年级1班所有同学的信息

关联查询:
SELECT s.name,s.age,c.name
FROM class c,student s
WHERE c.id=s.class_id 连接条件
AND c.name='1年级1班' 过滤条件

内连接:
SELECT s.name,s.age,c.name
FROM class c
JOIN student s ON c.id=s.class_id
WHERE c.name='1年级1班'

外连接

外连接可以将关联查询中不满足连接条件的记录也查询出来

外连接分为：

左外连接：以JOIN左侧的表作为驱动表，该表中满足连接条件记录会被显示出来，当该表中存在不满足连接条件的记录时，在结果集中该记录来自JOIN右侧表中的字段值全部为NULL

右外联机：与左外连接相反

查看所有的班级，以及该班级对应的班主任，没有班主任的也要将班级列出来

班级表中有3个班级的teacher_id为99，但是teacher表中没有id为99的老师，因此这三条记录是不满足连接条件的，因此结果集中没有体现这三个班级

左外连接，以class表为主，该表中不满足连接条件时，结果集中该记录中来此teacher表的字段为NULL
SELECT c.name,t.name
FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id

在数据库中UNION关键字可以对结果集取并集(两个结果集的字段数，类型，顺序要完全一致)

SELECT c.name,t.name
FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id
UNION
SELECT c.name,t.name
FROM class c RIGHT JOIN teacher t ON c.teacher_id=t.id

自连接

在同一张表中的一条记录可以对应多条记录

自连接设计解决：通常属性的一组数据之间存在上下级关系的树状结构

查看每个老师以及他的领导信息

SELECT t.name,m.name
FROM teacher t
JOIN teacher m ON t.manager=m.id

数据类型

不同的数据库，数据类型不完全一致

数字类型

整数类型

INT(m)

BIGINT(m)

m表示整数的位数

INT(5):整数最大位数为5，如果插入一条记录该字段值为整数18，实际保存：00018

浮点类型DOUBLE(m,n)

m:数字的位数

n:小数的位数

m包含n

DOUBLE(5,2):数字总共5位，其中包含2位小数，可保存的最大值：999.99

如果保存数字时小数的精度超过运行范围时，会进行四舍五入

person表中有一个字段为salary是一个DOUBLE类型(7,4)

INSERT INTO person (salary) VALUES (555,125672)

插入数据后，该记录中salary字段最终保存的值：555,,1257

如果插入最大值还需要四舍五入时会报错

INSERT INTO person (salary) VALUES(999.999987)

字符类型

定长字符：CHAR(n)

n表示长度，单位是字符

name CHAR(10)：name可以保存10个字符

最大值为255，最多可以保存255个字符

定长字符串，无论表中该字段实际保存的字符个数，在磁盘空间中开辟的一定是最大字符所占用的空间。当达不到指定长度时会在后面补充若干个空格。

优点：由于每天记录占用的磁盘空间是固定的，因此查询速度更快

缺点：由于每条记录该字段占用的磁盘空间是固定的，那么这会导致磁盘空间的浪费

变长字符：VARCHAR(n)

n表示长度，但是为字节

最大值为65535

实际占用磁盘空间为用多少占多少

优点：磁盘空间没有浪费

缺点：由于每条记录的占用空间不固定，因此查询速度相对较慢

变长字符：TEXT(n)

n表示长度，单位是字符

最大值为65535

日期类型

DATE类型：保存年月日

TIME类型：保存时分秒

DATETIME：保存年月日时分秒

TIMESTAMP：时间戳类型，记录UTC时间。自1970年元旦到改时间之间经过的毫秒

准备一张表

CREATE TABLE userinfo(

int INT,

username VARCHAR(30),

gender CHAR(1)

birth DATETIME,

salary DOUBLE(7,2)

)

插入日期类型时,如果日期类型为DATETIME型,可以用字符串格式表示:"yyyy-MM-dd hh:mm:ss"

MM表示2位数字的月，mm表示2位数字的分，HH表示24小时制的时，hh表示12小时制的时

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES(1,'张三','男','2000-2-2 12:12:12',5555.55)

插入日期时，如果类型为DATETIME，是可以忽略时分秒的

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES(2,'李四','男','2002-5-16',8000.96)

插入日期时，如果类型为DATETIME,不可以忽略年月日

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES(3,'王五','女','19:23:56',9500.15)

约束条件

可以对表添加约束条件，这样一来只有满足约束的操作才会被运行，否则被拒绝

主键约束(PRIMARY KEY)

主键约束在一张表中只能被施加在一个字段上

主键约束的特点：该字段的值在整张表中要求每条记录都必须是非空且唯一的

通常情况下一张表中第一个字段为主键字段，名字一般使用"id"

CREATE TABLE user1(

id INT PRIMARY KEY

name VARCHAR(30)

age INT(3)

)

INSERT INTO user1(id,name,age) VALUES(1,'张三',15)

INSERT INTO user1(id,name,age) VALUES(2,'李四',20)

主键字段不能插入重复的值

INSERT INTO user1(id,name,age) VALUES(1,'王五',36)

不能将NULL值插入主键字段

INSERT INTO user1(id,name,age) VALUES(NULL,'王五',36)

INSERT INTO user1(name,age) VALUES('王五',36)

修改表中现有记录时，也不能修改重复的值或NULL给主键字段

UPDATE user1

SET id=1

WHERE name='李四'

具体主键约束的字段通常可以搭配自增使用AUTO_INCREMENT

创建表时，可以在添加主键约束的同时添加自增

CREATE TABLE user2(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(30),

age INT(3)

)

修改表时，也可以为主键字段添加自增

ALTER TABLE user1 CHANGE id id INT PRIMARY KEY

如果id字段已经具有了主键约束，那么可以单独修改添加自增
ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT

当主键字段具有自增时，插入数据可以忽略主键字段

INSERT INTO user2(name,age) VALUES('张三',22);
INSERT INTO user2(name,age) VALUES('李四',16);

当主键字段具有自增时，可以显示的为主键插入NULL值，此时主键仍然自增(不推荐)

INSERT INTO user2(id,name,age) VALUES(NULL,'王五',45);

非空约束

被非空约束修饰的字段，在任何时候该字段的值都不允许为空

创建表时可以为某个字段指定非空约束

CREATE TABLE user3(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(30) NOT NULL, name字段被施加非空约束

age INT(3)

)

修改表时为某字段施加非空约束

ALTER TABLE user2 CHANGE name name VARCHAR(30) NOT NULL

不要将NULL值插入具有非空约束的字段上

INSERT INTO user3(name,age) VALUES(NULL,16)

也不能将具有非空约束的字段修改为NULL值

INSERT INTO user3(name,age) VALUES('张三',22);

UPDATE user3 SET name=NULL

WHERE name='张三'

插入数据时，不能忽略具有非空约束的字段

INSERT INTO user3(age) VALUES(22)

唯一性约束

具有唯一性约束的字段，该字段的值整张表中不允许有重复的

创建一张表时可以为某个字段添加唯一性约束

CREATE TABLE user4(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(30) UNIQUE,

age INT(3)

)

可以在修改表示为某个字段添加唯一性约束

ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE

注:如果user3中name字段有非空约束时，上述SQL添加唯一性约束后会取消非空约束

ALTER TABLE user3 CHANGE name name VARCHAR(30) NOT NULL UNIQUE

插入数据时，不能将重复的值插入具有唯一性约束的字段