MySQL学习

一，什么是MySQL,市面上常见的数据库有哪些？

      MySQL是目前最受欢迎开源的SQL数据库管理系统，数据库是长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据"仓库"

     作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余...

    1、MySQL是开源免费的。
    2、MySQL服务器是可靠的、易于使用的、快速的。
    3、MySQL服务器工作在客户/服务器或嵌入系统中。
    4、MySQL软件很多。
    5、MySQL是一个关系数据库管理系统。

      常见的数据库有mysql 、SQL Server、Oracle、Sybase、DB2等

二，如何创建一个数据库？

    安装MySQL,具体步骤见https://www.cnblogs.com/hellokuangshen/p/10242958.html  然后下载SQLyog，或者直接在cmd里用代码实现

    用管理员权限打开cmd，输入net start mysql 打开MySQL，然后打开SQLyog在询问里输入CREATE DATABASE [IF NOT EXISTS] 数据库名;创建表 CREATE TABLE [IF NOT EXISTS] `表名`（`字段名1` 列类型 [属性][索引][注释]  ）[表类型][表字符集][注释}；

三，对数据库进行增删改查

1. 增 ：INSERT INTO 表名[(字段1，字段2，字段3....)]

             VALUES(`值1`,`值2`,`值3`)

• 字段或值之间用英文逗号隔开.
• '字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
• 可同时插入多条数据 , values 后用英文逗号隔开.

2. 删：DELETE FROM 表名[WHERE condition];

         注意：condition为筛选条件，如不指定则删除该表的所有列数据

           TRUNCATE命令  TRUNCATE [TABLE] table_name;

           用于完全清空表数据，但表结构，索引，约束等不变；

           相同：都能删除数据，不删除表结构，但TRUNCATE速度更快

           不同：使用TRUNCATE TABLE重新设置       AUTO_INCREMENT计数器        使用TRUNCATE  TABLE不会对事务有影响

3.改：UPDATE(修改数据)

#语法：UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

#注意：column_name 为要改的数据列
value为修改后的数据
condition为筛选 条件，如果不指定的话，则修改该表的所有列是数据
where条件子句可以理解为，有条件的从表中筛选数据

4.查

4.1  SELECT语句（数据查询语言）

 在数据库管理中使用最频繁最高的语句，也是数据库语言中最核心，最重要的语句

SELECT [ALL | DISTINCT]#distinct去除重复项，默认为all
[left | right | inner join table_name2]  #连接查询
[WHERE ...]  #指定结果满足的条件
[GROUP BY ...]  #指定结果按照哪几个字段来分组
[HAVING]  #过滤分组的记录必须满足的次要条件
[ORDER BY ...]  #指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];# 指定查询的记录从哪条至哪条

查询表中所有的数据列结果，采用“*”符号，比如

select*from student

但是这样效率低，不推荐使用

#查询所有的学生信息
select * from student
#查询指定列（学号，姓名）
select studentno,studentname from student;

4.2  as字句作用

可给数据列取一个新名字

可给表取一个新名字

可把经计算或总结的结果用一个新名称来代替

#这里是为列取别名（当然as关键字可以省略）
select studentno as  学号,studentname as 姓名 from student;
#使用as也可以为表取名
select studentno as 学号,studnetname as 姓名 from student as s;


#使用as,为查询结果取一个新名字
#cancat()函数拼接字符串
select  concat('姓名：',studentname) as 新姓名 from student;

4.3  distinctg关键字的使用

作用去掉select查询返回的记录结果中重复的记录（返回所有列的值都相同），只返回一条

#查看哪些同学参加了考试（学号）---去除重复项
select * from result;#查看考试成绩
select studentno from result;#查看哪些同学参加了考试
select distinct studentno from result;
#distinct去除重复项，默认是all

select version();#查询版本号
select 100*3-1 as 计算结果；#计算表达式
#学员考试成绩集体提分一分查看
select studentno,studentresult+1 as '提分后' from result；

避免sql返回结果中包含 ’.’  ‘,’  ‘*’ 和括号等干扰开发程序语言

4.4  where条件语句

用于检索数据表中符合条件的记录

例如

#满足条件的查询（where）
select studentno,studentresult from result;
#查询考试成绩在95~100之间的
select studentno,studentresult
from result
where studentresult>=95 and studentresult<=100;
#其中and也可以写成&&
select studentno,studentresult
from result
where studentresult>=95 && studentresult<=100;
#还可以这样表示
select studentno,studentresult
from result
where studentresult between 95 and 100;
#除了1000号同学，要其他同学的成绩
select studentno,studentresult
from result
where studentno!=1000;


#使用 not
select studentno,studentresult
from result
where not studentno=1000;

4.5  模糊查询

         比较操作符

操作符名称	语法	描述
is null	a is null	若操作符为null，则结果为真
is not null	a is not null	若操作符不为null，则结果为真
between	a between b and c	若a在b与c之间，则结果为真
like	a like b	m模式匹配，若a匹配b,则结果为真
in	a in (a1,a2,a3…)	若a等于a1,a2，a3中的某一个，则结果为真

相同数据类型的数据之间才能进比较

#模糊查询 like , in , null , between and 
# LIKE
#like结合使用的通配符 : % (代表0到任意个字符) ; _ 代表一个字符
#查询姓李的同学的学号及姓名
SELECT studentno,studentname FROM student
WHERE studentname LIKE '李%';

#查询姓李的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '李_';

#查询姓李的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '李__';

#查询姓名中含有 文 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%文%';

#查询姓名中含有特殊字符的需要使用转义符号 '\'
#自定义转义符关键字:  ESCAPE ':'

#=============================================
# IN
#查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

#查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

#=============================================
# NULL 空
#查询出生日期没有填写的同学
#不能直接写=NULL , 这是代表错误的 , 用 is null 
SELECT studentname FROM student
WHERE BornDate IS NULL;

#查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

#查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.6  连接查询

   三种连接查询对比

名称	描述
INNER JOIN	查询两个表中的结果集中的交集，如果表中有至少一个匹配，则返回行
LEFT JOIN	以左表为基准，右边表来一一匹配，匹配不上的，返回左表的记录，右表以null填充，即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	以右表为基准，左边表来一一匹配，匹配不上的，返回右表的记录，左表以null填充，即使左表中没有匹配，也从右表中返回所有的行

 

#查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student  result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
#内连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

# 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

# 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

# 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

#查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

#思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

4.7  排序（ORDER BY）

解释：order by 语句用于根据指定的列对结果集进行排序，默认按照asc升序记录进行排序，desc进行降序排序

#查询：学习数据库结构的同学的所有考试结果（学号，学生姓名，科目名称，成绩）
#按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno

4.8  分页

# 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

4.9 子查询

嵌套查询可以由多个子查询组成，求解的过程是最里面的优先查询，一次向外。

#查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
#方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

#方法二:使用连接查询+子查询
#分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

#在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '高等数学-2'
)

#方法三:使用子查询
#分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
    )
)

五：MySQL常用函数

# 数学函数 (这里只列出一些常用的)
SELECT ABS(-8);  /*绝对值*/
SELECT CEILING(9.4);  /*向上取整*/
SELECT FLOOR(9.4);  /*向下取整*/
SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

#字符串函数
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5);  /*从左边截取*/
SELECT RIGHT('hello,world',5);  /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转*/


#日期和时间函数
SELECT CURRENT_DATE();   /*获取当前日期*/
SELECT CURDATE();   /*获取当前日期*/
SELECT NOW();   /*获取当前日期和时间*/
SELECT LOCALTIME();   /*获取当前日期和时间*/
SELECT SYSDATE();   /*获取当前日期和时间*/

/*获取年月日,时分秒*/
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());


#系统信息函数
SELECT VERSION();  /*版本*/
SELECT USER();  /*用户*/


# 查询姓李的同学,改成吴
SELECT REPLACE(studentname,'李','吴') AS 新名字
FROM student WHERE studentname LIKE '李%';

#聚合函数
#COUNT()：返回满足SELECT条件的记录总和数，如SELECT  COUNT(*)
#SUM()：返回数字字段或表达式列作统计，返回一列的总和
#AVG()：通常为数值字段或表达列作统计，返回一列的平均值
#MAX()：可以为数值字段、字符字段或表达式列作统计，返回最大的值
#MIN()：可以为数值字段、字符字段或表达式列作统计，返回最小的值

SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;