MySQL数据库--增删改查

• SQL是一种数据库查询和设计语言
• 使得数据库可以通过命令行的方式而非属性化界面的方式对表进行增删改查等操作
• SQL是一门独立的语言，在开发语言中可以嵌入SQL语言对数据库进行操作

 

SQL语句

DDL语句（数据库定义语句）：create创建库；alter修改表；drop删除库表

DML语言（数据操作语句）：insert增；delete删；update改；select查（对数据的操作）

 

insert(新增)

可以对表进行新增记录

列名插入：

insert into tablename(column_name,column_name,...,column_name) values (value1,value2,...,valuen);

直接插入：

insert into tablename values (value1,value2,...,valuen);

多条插入：

insert into tablename values(value1,value2,...,valuen),(value1,value2,...valuen),(value1,value2,...valuen);

 

delete(删除)

可以对表的数据进行删除

清空表：

delete from tablename；

删除满足某种条件的数据：

delete from tablename where 条件

*delete和drop的区别：delete仅限于把表中的数据给删除，表还在留着呢

and条件是同时满足的；or多个条件满足其中一个

删除数据注意外键的影响

 

update(修改)

update tablename set 字段=值

update tablename set 字段=值,字段=值

update tablename set 字段=值,字段=值 where 条件

---

-- 给student表，插入一条数据

INSERT INTO student(stu_id,NAME,age,professional) VALUES (1,'张三',18,'信息管理');

INSERT INTO student VALUES (2,'小美',20,'计算机专业');

INSERT INTO student VALUES (3,'留情五',21,'家里蹲'),(4,'吴多友',34,'激励顿');

-- 删除满足条件的删除

DELETE FROM student WHERE professional='激励顿' OR age=18;

DELETE FROM student;

-- 删除tempstudent表中sno=5的记录

DELETE FROM tempstudent WHERE sno=5;

-- 修改,student表中的professional的值为javasript

UPDATE student set professional='javasript';

UPDATE student SET professional='python' WHERE age=18;

UPDATE student SET professional='java',age=56 WHERE stu_id=4;

---

select(查询)

要求：可以使用SQL语句进行表的查询；会使用别名查询；会使用条件查询；掌握MySQL常用函数

定义：所谓查询，就是对已经存在于数据库中的数据按特定的组合、条件或者次序进行检索。查询功能是数据库最基本也是最重要的功能。

基本语法如下：

select select_list from table_list  [where search_conditions]

[group by group_by_list]

[having search_conditions]

[order by order_list [asc | desc] ]

 

 

select子句 -> select 列名 -> 要查询的是什么

from子句 -> from 表名 -> 告诉我们从哪来查

针对查询语句的题目，做题思路！

一定要先确定表，然后再看要查询的内容，最后确定查询条件

 

查询多个列：select 列名1,列名2,列名3 from 表名

 

去重查询

如果用户希望在查询结果中删除重复行，就可以在select子句中使用distinct关键字

（可以用来查看一个字段所有出现的值）

语法：select distinct 列名称 from 表名称

 

别名查询

在列的表达式中给出别名

针对你查询出来的结果给她一个新的标题，（对表中的类名没有影响）

语法：select 列名1 '别名1',列名2 '别名2' from 表名;

语法：select 列名1 as '别名1',列名2 as '别名2' from 表名;

 

在对数据进行查询过程中，经常需要对查询到的数据进行再次计算。

 

select id_code,name,num+10 '成绩' from chengji;

 

---

 

where子句(选择查询)

一般查询都不是针对权标所有行的查询，只是从整个表中选出满足指定条件的内容，这就要用到where子句。

以下是选择行的基本语法

 

select secelt_list

from table_list

where search_conditions

 

比较运算符

=等于  <小于  >大于  <=小于等于  >=大于等于  <>不等于  !=不等于

逻辑运算符

and 并   or 或

and 链接多个运算符，这些条件必须同时满足的

or   链接多个条件，这些条件只需要满足其中一个

 

范围查询

范围查询返回介于两个字指定值之间的所有值，可分为包括范围和排除范围两种类型。用between，not betwen

between  and：包括between和and两边的值（闭区间）

格式：select 列名 from tablename where 列名not between 开始值 and 结束值

 

列表搜索条件

in关键字使用户可以选择与列表中的任意值匹配的行。

格式：select 列名 from tablename where 列名 in (值1,值2,值N...);

 

不在这个列表范围之内：not in -->不要列表中的内容。

格式：select 列名 from tablename where 列名 not in (值1,值2,值N...);

 

搜索条件中的字符匹配符(模糊查询)

like关键字搜索与指点模式匹配的字符串、日期或者时间值。模式包含要搜索的字符串，字符串中可包含2中匹配符的任意组合。

%    表示0个或多个字符

_     表示一个字符

格式：select 列名 from tablename where 列名 like '字符模式';

 

只说like是用来匹配某个字符，加入不想要某个字符模式怎么办？

扩充：not like

 

涉及空值的查询

空值（NULL）在数据库中表示“不确定的值”

例如：学生选修课程后还没有考试时，这些学生有选课记录，但没有考试记录，因此考试成绩为空值。

判断某个值是否为NUlL值，不能使用普通的运算比较符。

判断取值为空的语句格式为：列名 is null

判断取值不为空的语句格式为：列名 is not null

格式：select 列名 from tablename where 列名 is null;

 

---

 

聚合函数

• sum([distinct] <列名>)：计算列值总和
• avg([distinct] <列名>)：计算列值平均值（默认保留4位小数）
• max([distinct] <列名>)：求列值最大值
• min([distinct] <列名>)：求列值最小值
• count(*)：统计表中元祖的个数
• count([distinct] <列名>)：统计本列列值的个数

除了count(*)之外，其他的聚合函数在计算时，都是把空值排除在外的！！！

 

行数限定

select 列名 from tablename where 条件 limit [start], nums;

start：从第几行开始，可选，不写的话从0开始；

nums：总共要查询几行；

 

数据分组

group by 语句用于结合合计函数，根据一个或多个列对结果集进行分组：

格式：select 列名,聚合函数(列名) from table_name group by column_name;

 

having 通常和group by子句一起使用。相当于一个用于组的where子句，制定组的搜索条件。

having子句可以包含聚合函数，但是where不可以。

having后面的条件一般都是聚合函数当做条件。

格式：select 列名from tablename group by 列名 having 条件;

 

排序

• order by 语句用于对指定的结果集进行排序；
• order by 语句默认升序；
• 降序排序可以使用desc关键字；
• 查询成绩表并对成绩进行排序。

有时是按照两个列来排序的！！！

格式：select 列名 from tablename order by 类名 [desc | asc];

 

-- 给student表，插入一条数据

INSERT INTO student(stu_id,NAME,age,professional) VALUES (1,'张三',18,'信息管理');

INSERT INTO student VALUES (2,'小美',20,'计算机专业');

INSERT INTO student VALUES (3,'留情五',21,'家里蹲'),(4,'吴多友',34,'激励顿');

-- 删除满足条件的删除

DELETE FROM student WHERE professional='激励顿' OR age=18;

DELETE FROM student;

-- 删除tempstudent表中sno=5的记录

DELETE FROM tempstudent WHERE sno=5;

-- 修改,student表中的professional的值为javasript

UPDATE student set professional='javasript';

UPDATE student SET professional='python' WHERE age=18;

UPDATE student SET professional='java',age=56 WHERE stu_id=4;

-- student表中，每个学生的年龄在原有基础上增加3岁。

UPDATE student SET age=age+10;

DESC student;

-- 查询全部

select * FROM student;

-- 指定列名

SELECT stu_id,name,professional from student;

-- 查询中删除重复行(查看所有学生都来自哪些专业？)

SELECT DISTINCT professional from student;

-- 别名查询

select professional '专业',`name` '姓名' from student;

DESC chengji;

SELECT * FROM chengji;

-- 对查询的结果进行计算

SELECT id_code,NAME,num+10 '新成绩' FROM chengji;

-- 选择查询

SELECT * FROM student WHERE age<>30;

-- 选择查询 and逻辑查询

SELECT * FROM student WHERE age<>30 and professional='python';

-- 选择查询 OR逻辑查询

SELECT * FROM student WHERE age=30 OR professional='python';

-- 范围搜索条件between and (这边闭区间)

SELECT * FROM student WHERE age BETWEEN 30 AND 60;

-- 列表搜索条件

SELECT * FROM student WHERE professional in ('python','math');

SELECT * FROM student WHERE professional='python' OR professional='math';

-- not in  列表搜索条件

SELECT stu_id,`name`,age FROM student WHERE professional NOT in ('python','math');

-- 搜索条件中的字符匹配符

--  从student表中查询name有’小‘的记录

SELECT * FROM student WHERE `name` LIKE '小_';

SELECT * FROM student WHERE `name` NOT LIKE '_小%';

-- 涉及空值的查询is NULL

SELECT * FROM student WHERE professional is not NULL ;

-- 聚合函数

SELECT sum(age) as '总年龄' FROM student;

select count(*) AS '总数', avg(age) AS '平均年龄',max(age) as '最大年龄',min(age) as '最低年龄' FROM student;

-- 限定行数

select * FROM student LIMIT 5;

SELECT * FROM student LIMIT 2,6;

-- GROUP BY 数据分组

SELECT count(*) AS '总数',avg(age) AS '平均年龄',professional FROM student GROUP BY professional;

-- HAVING 的使用

SELECT count(*) AS '总数',avg(age) AS '平均年龄',professional FROM student GROUP BY professional HAVING avg(age)>30;

-- ORDER BY 排序

SELECT * FROM student ORDER BY age desc,stu_id DESC;

 

---

MySQL中函数主要分为四大类：

字符串函数

• length    可以返回字符串的字节长度
• char_length    可以返回字符串的字符长度
• MID    可以从某个位置获取某个长度的字符（不是字节）MID(sname,n,m)-->sname要截取的内容，n开始的位置，m要截取的长度

MySQL数据库中，一个汉字占用三个字节！一个字母占用一个字节！

 

数学函数

• round的基本用法是实现四舍五入。
• least 求取最小的数字；least(1,2,3,4,5)后面跟的是数字
• greatest求取最大的数字；greatest(1,2,3,4,5)后面跟的是数字

 

日期时间函数

• now    可求数据库服务器的当前日期时间
• current_DATE    可求数据库服务器的当前日期
• current_TIME    可求数据库服务器的当前时间
• TO_DAYS    将日期转化成总天数
• DAYOFYEAR    可以求得该年已过的天数
• WEEK    可以返回当前的时日是第几周

 

控制流函数

if有三个参数，第一个参数为空，输出第三个参数，否则熟读第二个参数

select if(布尔表达式,'参数二','参数三');

select if(null,'参数二','参数三');

ifnull有两个参数，第一个参数为空输出第二个参数，否则输出第一个

select ifnull(null,'你好');

select ifnull('你好','你不好');

 

---

表连接

内部连接：

前提：有时候需要的数据不止在一个表中，需要多个表格做结合的查询，就可以用表连接实现！

表和表之间建立关联的列，要求列名可以不一样，但是数据类型和内容必须相同！

内部连接：内部连接是比较常用的一种数据连接查询方式。

只会显示两个表中匹配到的内容

 

格式一：select select_lists from 表名1,表名2 where 表名1.列=表名2.列

格式二：select select_lists from 表名1 [inner] join 表名2 on 表名1.列=表名2.列

 

on的后面只放表连接的条件，其他条件需要用where进行链接！！！！

 

多表查询首先要在各个表之间进行连接。

用户在进行基本连接操作时，可以遵循以下基本原则。

 

select 子句列表中，每个目标列钱都要加上基表名称

from  子句应包含所有使用的基表

where 子句应该定义一个连接

 

inner join on又划分为两个分类：

1、等值连接。指的是条件中只包含等号“=”，没有其他符号在里面

2、非等值连接。值得是条件中，除了等号之外，还有其他的符号，像> ,< ,等

 

 

两个表连接：

1、where 进行表连接

select 表名.列名

from 表名1,表名2,

where 表名1.列名1=表名2.列名1 [and 条件1 and 条件2]

[group by 列名]

[having 分组的条件]

[order by 列名 desc | asc]

 

2、on进行表连接

select 表名.列名

from 表名1 inner join 表名2

on 表名1.列名1=表名2.列名2

 

 

多表连接：

1、where 进行表连接

select 表名.列名

from 表名1,表名2,表名3

where 表名1.列名1=表名2.列名1 and 表名2.列名1=表名3.列名1

[group by 列名]

[having 分组的条件]

[order by 列名 desc | asc]

 

2、on进行表连接

select 表名.列名

from 表名1 inner join 表名2

on 表名1.列名1=表名2.列名2 inner join 表3

on 表名2.列名1=表名3.列名1

 

外连接：

外部连接会返回from子句中提到的至少一个表或者视图中的所有行。

外部连接分为左部外连接、右部外连接。

• 左外部连接对连接条件中左边的表不加限制；就是会返回左外部表中所有行
• 右外部连接对连接条件中右边的表不加限制；就是会返回右外部表中所有行

 

在from之后，先写的是左表，后写的是右表

 

使用关键字left outer join对两个表进行连接，左外连接的查询结果中包含指定左表的所有行，而不仅仅是连接所匹配的行。如果左表的某行在右表中没有找到匹配的行，则结果集中的右表的相对应的位置为NULL。

 

select 表名.列名

from 表名1 left outer join 表名2

on 表名1.列=表名2.列

 

子查询：

子查询在其他查询结果的基础上提供了一种有效的方式来表示where子句的条件。

子查询是可以嵌套在select、insert、update、delect语句中。

子查询的select查询总是使用圆括号括起来。

 

注意事项：

where后面的条件要什么，我的子查询就查什么；

什么情况下优先考虑子查询？

1. 题上给出的条件没有明确的提示结果
2. where后面要用到聚合函数当条件

聚合函数不能直接写，就可以借助查询语句把聚合函数的结果给查出来就行了。

 

格式：select 列名 from 表名 where 列名 in (查询语句);

 

1、嵌套子查询

在MySQL中子查询是可以嵌套使用的，并且可以在一个查询中嵌套任意多个子查询，即一个子查询中还可以包含另一个子查询，这种查询方式成为嵌套子查询。

格式：

select 列名 from 表名 where 列名 in (select 列名 from 表名 where in (select 列名 from 表名 where 列名 条件));

select 列名 from 表名 where in (select 列名 from 表名 where 列名 条件));

 

2、相关子查询

子查询的结果有且只有一个！

 

使用insert和select语句添加数据

在inert语句中使用select子句可以将一个或多个表、试图中的值添加到另一个表中。使用select子句还可以同时插入多行。

语法格式为：insert  [into] tablename select [column_list] from table_name;

 

使用update 和select语句（子查询）结合

将一个表的查询结果作为另一个表的查询条件去执行。

update table_name set 列名 where 列名 in (select子句)

 

案例：

update sc

set grade=grade+5

where sno in (select sno from student where sdept='计算机系');

 

 

使用delete和select语句（子查询）结合

detele from table_name where 列名 in (select子句)