数据库——MySQL（DML、DQL）

一：DML语言

数据库意义：数据存储，数据管理
DML语言：数据操作语言

插入

-- 插入语句
-- insert into 表名(  [ 字段1,字段2,字段3,...] ) values (值1),(值2),(值3),(...);

insert into `grade`(`gradename`) values('大一');
insert into `grade`(`gradename`) values
('大二'),
('大三'),
('大四');

values后面 每一个括号就代表一个对象,多个对象间用,号分隔

由于主键自增，我们可以省略，(如果我们不写表的字段，他就会一一匹配)

insert into `grade` values ('5','大五');

注意事项

1：字段与字段之间使用英文逗号分隔
2：字段是可以省略的，但是后面的值必须一一对应，不能少(即使主键自增也必须写)
3：可以同时插入多条数据，values后面的值，需用 英文逗号分隔

修改

update 修改的对象 set 旧值 = 新值

修改学生姓名 带条件查询

update `student` set `name`='潮汕奴仔' where id = 1;

如果不指定条件的话会修改表中所有的数据

update `student` set `name`='潮汕奴仔';

修改学生的多个属性，逗号分隔

update `student` set `pwd`='666666',`address`='汕头' where id = 1;

语法：

update 表名 set column_name = value,[ column_name = value,… ] where [ 条件 ];
条件：where 子句 运算符

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不相等	5<>6	true
>	大于	2>1	true
<	小于	2<1	false
>=	大于等于	3>=2	true
<=	小于等于	3<=5	true
between … and …	在某个范围内	[2,5]	从2到5,闭区间
and	&&	1=1 && 2=1	false
or	||	1=1 && 2=1	true

注意：

colnum_name 是数据库的列， 尽量带上``
条件，筛选的条件，如果没有指定，则会修改所有的列
value，是一个具体的值，也可以是一个变量
多个设置的属性之间，使用英文逗号分隔

删除

语法： delete from 表名 [ where 条件 ]

delete from `student` where id = 3;

注意 ：应该避免这样写! 这样子删库跑路太低级了！

delete from `student`;

正确的删库跑路应该是这样子的!

truncate `student`;

作用：完全清空一个数据库表，表的结构和索引约束不会变！

delete 和 truncate的区别

相同点: 都能删除数据，都不会删除表结构
不同点：truncate 会重新设置 自增列 计数器会归零 且不会影响事务

关于delete删除的问题，重启数据库，现象

InnoDB ：自增列会从1开始，因为数据是存在内存中的，断电即失
MYISAM : 继续从上一个自增量开始，因为是存在文件中的，不会丢失

二：DQL语言

( Data Query language 数据查询语言 )
所有的查询操作都使用它
简单的查询，复杂的查询它都可以做
数据库中最核心的语言，最重要的语言
使用频率最高的语言

指定查询字段

-- 查询全部的学生 select 字段 from 表
select * from student;

-- 查询指定字段
select `StudentNo`,`StudentName` from student;

-- 别名，给结果起一个名字 as  也可以给字段起别名、给表起别名
select `StudentNo` as 学号, `StudentName` as 学生姓名 from student as student

-- 函数 Concat (a,b)
select concat('姓名：',StudentName) as 新名字 from student;

有的时候，列名字不是那么见名知意 ，我们可以起别名

去重 distinct

作用：去除select查询出来的结果中重复数据，重复的数据只显示一条

select * from result;
select `studentNo` from result;
select distinct `studentno` from result; -- 发现重复数据 去重

数据库的列(表达式)

select version()	-- 查询系统版本（函数）
select 1+1 as 计算结果 -- 用来计算(表达式)
select @@auto_increment_increment -- 查询自增的步长 （变量）
-- 考试成绩全部加一分
select `studentno`,`studentresult`+1 as 加分后的成绩 FROM result;

数据库中的表达式： 文本值，列，null，函数，计算表达式，系统变量…
select 表达式 from 表

where条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果 布尔值
逻辑运算符
与 and | &&
或 or | ||
非 not | !
模糊查询——比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，结果为true
is not null	a is not null	如果操作符不为null，结果为true
between	a between b and c	若a在b和c之间，则结果为true
like	a like b	SQL匹配，如果a匹配b，则结果为true
in	a in (a1,a1,a3…)	假设a在a1,a2,a3…其中的某个值，结果为true

like查询——like结合 %(代表0到任意一个字符) _代表一个字符

-- 查询姓陈的同学
select * from student where `studentname` like '陈%';
-- 查询姓陈的同学 名字只有俩字
select * from student where `studentname` like '陈_';
-- 查询姓陈的同学 名字是仨字的
select * from student where `studentname` like '陈__';
-- 查询名字中有广的同学
select * from student where `studentname` like '%广%';

in

-- in 具体的一个或者多个值
--查询学号为 2 和3 的学员信息
select * from student where `studentno` in (2,3);

null not null

-- null  not NULL
-- 查询出生日期为空的学生
select * from student where `borndate` is null;

-- 查询出生日期不为空的同学
select * from student where `borndate` is not null;

联表查询

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值， 即使右表中没有匹配(null)
right join	会从右表中返回所有的值，即使左表中没有匹配(null)

左连接就是以左表为基准
右连接就是以右表为基准

自连接

自己和自己连接
准备表

CREATE TABLE `category`( 
`categoryid` INT(3) NOT NULL COMMENT '主题id',
 `pid` INT(3) NOT NULL COMMENT '父id',
 `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
 PRIMARY KEY (`categoryid`)
 ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, "信息技术");
insert into `CATEGOrY` (`categoryid`, `pid`, `categoryname`) values (3, 1, '软件开发');
insert into `category` (`categoryid`, `PId`, `categoryname`) values (5, 1, '美术设计');
insert iNTO `category` (`categoryid`, `pid`, `categorynamE`) VAlUES (4, 3, '数据库'); 
insert into `category` (`CATEgoryid`, `pid`, `categoryname`) values (8, 2, '办公信息');
insert into `category` (`categoryid`, `pid`, `CAtegoryname`) values (6, 3, 'web开发'); 
inserT INTO `category` (`categoryid`, `pid`, `categoryname`) valueS (7, 5, 'ps技术');

查看表

-- 自连接 查询父子关系
select a.`categoryname` as '父类' ,b.`categoryname` as '子类'
from `category` as a, `category` as b
where a.categoryid = b.pid;

结果

排序

升序asc、 降序desc、 oder by 根据哪个来排

result表

现在根据 成绩降序排序

select * from result
order by studentresult desc

分页

语法：limit 起始值 ，页面的大小
注意 ：limit语句是在最后的

select * from result
order by studentresult desc
limit 0,3;

子查询

where （这个值是计算出来的）
本质：在where中嵌套一个查询语句
在查询中，另一个查询的结果可以作为条件的一部分。
经常用作判定列值是否与某个查询的结果集中的值相等

分组和过滤

 -- 查询不同课程的平均分 ，最高分，最低分
-- 核心：根据不同的课程分组
select subjectName ,avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
group by r.subjectno -- 通过什么字段来分组

再加上平均分大于80的条件进行过滤

select subjectName ,avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
group by r.subjectno -- 通过什么字段来分组
having 平均分 >80

MySQL函数

常用函数

	
-- 数学运算
select ABS(-8)	-- 绝对值
select ceiling(9.4)	-- 向上取整  结果为10
select floor(9.4)	-- 向下取整 结果为9
select rand()	-- 返回一个0到1的随机数
select sign(0) -- 判断一个数的符号 0 返回0  负数返回-1 正数返回1

-- 字符串函数
select char_length('我是潮汕奴仔') -- 字符串长度
select concat('我','来自','潮汕') -- 拼接字符串
select insert('你是潮汕奴仔',1,1,'我才') -- 替换 从某个位置开始替换某个长度 默认下标从1 开始
select lower('I am CHAOSHANNUZAI')	-- 转小写字母
select upper("i am chaoshannuzai")	-- 转大写字母
select instr('Java','v')	-- 返回第一次出现的子串的索引
select replace('I love Java','Java','you')	-- 替换出现的指定字符串
select substring('I love Java',8,4)	-- 截取指定位置的子字符串 源字符串，截取的位置，截取的长度 如果没写截取长度则默认全部截取
select reverse('java') 	-- 反转字符串

-- 查询姓李的同学 把他的姓改成理
select replace(name,'李','理') from student 
where name like '李%';

-- 时间和日期函数
select current_date() -- 获取当前日期
select curdate() -- 获取当前日期
select now()	-- 获取当前具体时间(重点)
select localtime() -- 获取本地时间
select sysdate()-- 获取系统时间

-- 系统
select system_user()	-- 获取当前系统用户
select user()	-- 获取当前系统用户
select version()	-- 获取当前版本

聚合函数

count 函数 都能统计 表中的数据

select count(birthday) from student; count(字段) 会忽略所有的null值

select count(*) from student; count(*) 不会忽略null值 本质计算行数

select count(1) from student; count(*) 不会忽略null值 本质计算行数

count(*)将返回表格中所有存在的行的总数包括值为null的行

count(字段) 与count(*)和count(1)的区别就是 它会忽略null值

count(*)和count(1)的区别就是
没有主键的时候 使用count(字段) 效率优于 count(*)

从执行结果来看：

count（1）和count（*）之间没有区别，因为count（*）count（1）都不会去过滤空值。
但count（列名）就有区别了，因为count（列名）会去过滤空值

从执行效率来说：

     他们之间根据不同情况会有些许区别，MySQL会对count（*）做优化。

（1）如果列为主键，count(列名)效率优于count(1)
（2）如果列不为主键，count(1)效率优于count(列名)
（3）如果表中存在主键，count(主键列名)效率最优
（4）如果表中只有一列，则count()效率最优
（5）如果表有多列，且不存在主键，则count(1)效率优于count()

求和函数：sum()

select sum(studentResult) as 总和 from result;

求平均数函数：avg()

select avg(studentResult) as 平均数 from result;

求最大值函数：max()

select max(studentResult) as 最高分 from result;

求最小值函数：min()

select min(studentResult) as 最低分 from result;