MySql基础--增删改查

1.什么是数据库？

2.数据库有什么作用？

3.MySQL--Table（表）

3.1表中的数据类型

3.2表中的约束

3.3表的创建、删除、修改

4.MySQL基础--增删改查

4.1增添数据、修改数据、删除数据

 4.2查询数据

 4.2.1条件查询

4.2.2分组查询

4.2.3关联查询

4.2.4子查询

---

1.什么是数据库？

数据库顾名思义就是我们存储数据的一个仓库，数据库分为了关系型数据库如(MySQL、Oracle、SQL Server)等、非关系型数据库如(MongoDB、Cassandra、Redis)等、内存数据库（Redis、Memcached）等、图数据库(Neo4j、ArangoDB)等。总的来说，不同类型的数据库适用于不同的数据存储和查询需求，根据项目的要求选择适合的数据库类型即可。本章我们主要将Mysql数据库。

2.数据库有什么作用？

数据库可以存储大量的数据，并且提供了一种结构化的方式来保存数据，是数据可以被轻松的存取，更新，管理等。我们在使用java编程语言时，可以通过导入Mysql包注册JDBC驱动连接到数据库，在java中就可以用sql语句对数据库进行增删改查，保存到数据库中的数据也不会因为关机而消失。在数据库中的数据也可以备份，数据也更加安全。

当我们在做一个项目之前，需要想到用户的需求，然后就要先构思数据库怎么写，防止数据库的冗余等各种问题。有了实际的后端前端的内容才有用处，不然就是空壳。

3.MySQL--Table（表）

3.1表中的数据类型

在学习增删改查之前我们首先要了解表，因为增删改查是在表的基础上完成的。再创建一个表之前我们要知道表中的数据类型，整数型由字节从小到大（1、2、3、4、8）来排序TINYINT、SAMLLINT、MEDIUMINT、INT、BIGINT。浮点类型为DECIMAL。字符类型有TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT也是逐渐增大的较大的字符类型可以存储文章、博客等长度可变。char()和varchar()也是存储字符类型，char是一种固定长度的字符数据类型，长度在开始时定义，字符长度不够则会用空格补满，varchar是一种可变的字符串类型，长度也要开始时定义，比char更节省存储空间。还有date、datetime等数据类型。

3.2表中的约束

在数据中还有一些对数据的约束条件，如主键(PRIMARY KEY):在表中代表唯一的一条记录，不能为空，不能重复。NOT NULL数据不能为空、UNIQUE唯一性约束(不能重复如学号)、CHECK()检查约束设置条件比如age INT(3) Check(age>18)。comment“注释” 

3.3表的创建、删除、修改

CREATE TABLE 表名（列名  数据类型  【约束】【默认值】 【注释】......）

例如:

创建数据库时一定要创建编码这里我用的是utf-8

删除表:  DROP TABLE [if exists]  表名

修改表：RENAME TABLE  旧表名  TO  新表名

复制表结构：CREATE TABLE 新表名 LIKE 被复制表名

4.MySQL基础--增删改查

4.1增添数据、修改数据、删除数据

增添数据：

方式 1: INSERT INTO 表名 ( 列 1, 列 2……, 列 n) VALUES( 值 1, 值 2….., 值 n);//这种方式最常用

方式 2: INSERT INTO 表名 set 列名 1= 值 1,.. 列名 n= 值 n;//一半给某

方式 3: INSERT INTO 表名 ( 列 1, 列 2……, 列 n) VALUES( 值 1, 值 2….., 值 n),( 值 1, 值 2….., 值 n);

方式 4:INSERT INTO 表名 ( 列 1, 列 2……, 列 n) 查询语句 ( 查询的列数与插入列数匹配 )；

如图所示的插入四种方式用法，最后一个插入相当于把创建的test_user表中的数据插入到t_user中去（列要一致） 

更新数据：UPDATE 表名  SET 列名=‘新值’  WHERE（条件）id=2;如果不写条件则会把列全修改

删除数据：DELETE FROM 表名 WHERE（条件）注意主键id自增删除后不会重新排序！

TRUNCATE TABLE 表名;清空整张表

 4.2查询数据

语法：SELECT 查询列表 FROM 表名

特定列查询 :select column1,column2 from table

全部列查询 : select * from table

算数运算符 :+ - * /

排除重复行 : select distinct column1,column2 from table （是对查询结果中所有列相同则自动删除）

查询函数： select 函数 ; / 例如 version()

函数分为单行函数和多行函数

单行函数：会对查询中的每一行数据进行处理

单行函数中的字符处理

length() ：获取参数值的字节个数

char_length() 获取参数值的字符个数

concat(str1,str2,.....) ：拼接字符串

upper()/lower() ：将字符串变成大写 / 小写

substring(str,pos,length) ：截取字符串 位置从1开始（java中从零开始）

instr(str, 指定字符 ) ：返回子串第一次出现的索引，如果找不到返回 0

trim(str) ：去掉字符串前后的空格或子串 ,trim( 指定子串 from 字符串 )

lpad(str,length, 填充字符 ) ：用指定的字符实现左填充将 str 填充为指定长度

rpad(str,length, 填充字符 ) ：用指定的字符实现右填充将 str 填充为指定长度

replace(str,old,new) ：替换，替换所有的子串

单行函数中的逻辑处理

(case when 条件 then  ' 结果1'  else  '结果2'  end)as 别名 ; 可以有多个when

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回

原本的值

if函数：if else的 效果 if(条件,结果1,结果2)

单行函数中的数学函数

round( 数值 ) ：四舍五入

ceil( 数值 ) ：向上取整，返回 >= 该参数的最小整数

floor( 数值 ) ：向下取整，返回 <= 该参数的最大整数

truncate( 数值 , 保留小数的位数 ) ：截断，小数点后截断到几位

mod( 被除数 , 除数 ) ：取余，被除数为正，则为正；被除数为负，则为负

rand() ：获取随机数，返回 0-1 之间的小数

单行函数中的日期函数

now() ：返回当前系统日期 + 时间( 年月日时分秒 )

curdate() ：返回当前系统日期，不包含时间（ 年月日 ）

curtime() ：返回当前时间，不包含日期( 时分秒 )

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR( 日期列 ),MONTH( 日期 列 ),DAY( 日期 列 ) ,HOUR( 日期 列 ) ,MINUTE( 日期 列 )

SECOND( 日期 列 )

str_to_date( 字符串格式日期 , 格式 ) ：将 日期格式 的字符转换成指定格式的日期 例如select str_to_date('2003-3-3','%Y-%m-%d') from 表

date_format( 日期列 , 格式 ) ：将日期转换成字符串

datediff(big,small) ：返回两个日期相差的天数

日期格式图

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类： sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 计数

（非空）如果在某一行中的某一个数据（height）为空，则count（height）就不会计这个数据

1.sum ， avg 一般用于处理数值型 max，min，count可以处理任何类型

2. 以上分组函数都忽略 null 值

3.count 函数的 一般使用count（*）用作统计行数

4. 和分组函数一同查询的字段要求是 group by 后的字段

 4.2.1条件查询

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。

语法 :select < 结果 > from < 表名 > where < 条件 >

比较

=, != 或 <>, >, <, >=, <=

逻辑运算

and 与

or 或

not 非

LIKE ：是否匹配于一个模式 一般和 通配符搭配 使用，可以判断字符型数值

或数值型. 如库里 则库%

通配符： % 任意多个字符

between and 两者之间, 包含临界值【】 ；

in 判断某字段的值是否属于in列表中的某一项

IS NULL（为空的）或 IS NOT NULL（不为空的）

UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，

mysql 会把所有的记录返回，且效率高于union 。（ 两个语句查询的列数必须相等 ）

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、 多个字段（height  asc,reg_time asc）

数量限制(分页功能)

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5 ; （0，1，2，3，4）起始位置和查询个数

4.2.2分组查询

分组在一些统计场景中，按照某一个条件进行区分。统计时，按照性别、班级、年纪、部门等区分

语法：

select 分组函数，列（要求出现在 group by 的后面）

from 表

[where 筛选条件 ]

group by 分组的列表

[having 分组后的筛选 ]

[order by 子句 ]

4.2.3关联查询

外键：引用另外一个数据表的某条记录。

外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键

（foreign key）建立起来的。

create table student(

id int not null auto_increment primary key,

num int,

name varchar(10)

majorid int,

CONSTRAINT 约束名 foreign key(majorid ) references major(id)

);

约束名规则:

例：FK_ForeignTable_PrimaryTable_On_ForeignColumn

多对多:学生选课

内连接 (inner join)

把满足了条件的两张表中的交集数据查询出来

语法： Select 结果 from 表 1 ，表 2 where 表 1.column1 = 表 2.column2

如图所示

解释:如图两种查询方式，第一种查询方式就是内关联查询，在student s是对student起别名省略了as，这样的方式可以避免在查询name时不知道是哪个表中的name而引起报错，第二种查询方式是先对两个表进行了笛卡尔积，再从笛卡尔积后的表中筛选出s.majorid=m.id的条件，效率更慢。 

多表关联

  左外连接 (left join)

select 结果 from

表 1 left join 表 2 on

表 1.column1 = 表 2.column2

解释:内连接如果外键为空则这一行不会被查询出来，左外连接即使外键为空也会被查询出来。

多表关联

 右 外连接 (left join)

select 结果 from

表 1 right join 表 2 on

表 1.column1 = 表 2.column2

解释:外键的中有值及时其他列都为空也可以被查询出来。

多表关联 GROUP_CONCAT(C.NAME)分组连接字符串

4.2.4子查询

含义：出现在其他语句中的 select 语句，称为子查询或内查询；外部的查询语句，称为主查询或

外查询 .

分类：

按子查询出现的位置：

from 后面：支持表子查询

where ：支持标量子查询，列子查询

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

表子查询（结果集一般为多行多列）