数据库的基本操作

DBMS(数据库管理系统)

• 什么是DBMS？
  • 数据库的管理软件 可视化软件
  • 例如 mysql---->关系型数据库管理系统！
• 各自的作用是什么？
  • 数据库应用系统(应用软件)连接数据库管理系统，从而使用数据库
  • 数据库管理系统用于管理数据库
  • 数据库用于存储数据

Mysql的下载与安装(mysql5.7+版本)

• 建议使用安装包下载(免安装)
  • 推荐浏览博客
  • 百度网盘链接：https://pan.baidu.com/s/1tbJN8_dXJ0VZ-AT57kfVaw
    提取码：4f1r
• 也可以下载exe文件 再进行安装(需要安装)
  • 推荐浏览博客
  • 百度网盘链接：https://pan.baidu.com/s/19iJJ80hv1GP30_pZzReZIg
    提取码：r8b2

Navicat的下载与安装(破解版)

• 百度网盘链接：https://pan.baidu.com/s/1WlRobU6NBZ9s0kUFfMA-vA
  提取码：xas4
• 推荐浏览博客
• 使用方法

数据库基本的命令行操作

mysql -uroot -p123qwe --连接数据库
update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';  --修改密码
flush privileges;  --刷新权限
show databases; --显示所有数据库
use dbname；--打开某个数据库(切换数据库)
show tables; --显示数据库中所有的表
describe user; --显示user表的列信息
create database name; --创建数据库
exit; --退出Mysql

SQL语言的五个部分

• DDL 数据定义语言
  • 主要用针对是数据库对象（表、索引、视图、触发器、存储过程、函数、表空间等）进行创建、修改和删除操作
  • CREATE ALTER DROP
• DML 数据操作语言
  • 主要用于对数据库中的数据进行增加、修改和删除的操作
  • INSERT UPDATE DELETE
• DQL 数据查询语言
  • 主要用于数据的查询
  • 使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
• DCL 数据控制语言
  • 用来授予或回收访问数据库的权限
  • GRANT REVOKE
• TCL 事务控制语言
  • 用于数据库的事务管理
  • START TRANSACTION、COMMIT、ROLLBACK、SET TRANSACTION

DDL

• 数据库的创建与销毁

create database if not exists websto; --创建数据库
drop database if exists websto; --销毁数据库

• 数据类型

整数:int  / bigint浮点数:float(10,2): 长度是10,小数点后的位数是2double(16,4): 长度为16,小数点后的位数是4decimal(m,n): 金额等数据时必须使用decimal字符串类型:必须指明长度char(n): 固定长度varchar(n): 可变长度日期类型:date: 日期time: 时间datetime: 日期和时间timestamp: 时间戳其他类型:text: 文本类型blob: 二进制数据类型enum() / set()  / json()

• 表的创建，修改和销毁

1.创建表
create table student(sid int,sname varchar(20),age int,gender varchar(10)
);
desc student; --查看表的状态
2.修改结构(添加字段 销毁字段 修改字段)
①添加字段
alter table student add email varchar(20);  --追加字段
alter table student add phone char(11) FIRST;  --添加到首列
alter table student add card char(18) after sid;  --添加到指定位置
②销毁字段
alter table student drop card;
③修改字段
alter table student change email semail varchar(20); --修改字段名称
alter table student modify sname varchar(50);  --修改字段的类型和长度
alter table student modify semail varchar(20) after sname;  --修改字段位置
3.销毁表
drop table student;
4.修改表的名称
rename table student to stu;
5.清空表(只清空数据,不改变结构)
truncate table stu;

• 数据的完整性

1.实体完整性--->保证记录准确 不重复
①主键约束  字段值唯一且不为空(primary key)
1>创建表的同时添加约束
create table student(sid int primary key,sname varchar(20)
);
2>表已经存在
alter table stu add constraint PK_ID primary key stu(sid);
--每张表必须有主键  但是有且只能有一个
②唯一约束  字段值唯一
1>创建表的同时添加约束
create table student(card char(18) unique
);
2>表已经存在
alter table stu add constraint UQ_CARD unique stu(card);
③主键自增
auto_increment: 默认从1开始每次增加1
通常设置在主键上
2.域完整性--->保证字段准确 不重复
--类型约束
not null:非空约束 必须有值
default: 默认值
3.引用完整性  
--外键约束
alter table emp add constraint FK_DEPTNO foreign key(deptno) references dept(deptno);

DML

• 添加数据

1.给所有字段添加值
insert into person values(5,'李白',24,'155');
2.部分字段添加值，值的顺序和前面字段的声明顺序必须一致
insert into person(id,age,phone) values(1,27,'158');
3.添加三条记录
insert into person(id,age,phone) values(2,27,'157'),(3,25,'159'),(4,26,'156');
4.表复制
①创建一个和person结构相同的表person1
create table person1 select *from person where 0=1;
②插入person表所有数据到person1
insert into person1 select *from person where 1=1;
--where 0=1,这个条件始终为false,结果不会返回任何数据，只有表结构，可用于快速建表
--where 1=1,这个条件始终为true,结果返回表中所有数据

• 修改数据

--将电话号码为159的姓名修改成杜甫
update person set sname=‘杜甫’ where phone=‘159’;

• 删除数据

1.删除id=2的数据
insert into person(id,sname,age) values(2,‘杜甫’,26);
delete from person where id=2;
-----------------------------------------------------------------
2.delete、drop和truncate的区别
--删除整个表数据(一条一条的删) 不改变结构 会保留自增序列 不影响事务 --->可以进行条件删除
delete from person1;
--删除整个表的结构以及所有数据
drop table person1;
--直接清除所有数据(一次性删除) 不改变结构 不会保留自增序列 会结束事务 --->只能进行全表删除
truncate table person1;
在速度上，一般来说，drop> truncate > delete

DQL

• 关键字ALL和DISTINCT

1.ALL(默认) 所有的、全部
SELECT ALL stuName,gradeId FROM student;  --查询出所有数据 不去重
2.DISTINCT 直接的、明显的
SELECT DISTINCT stuName,gender,gradeId FROM student;  --查询出所有数据 去重

• where查询条件

1.整体理解
SELECT * FROM student WHERE stuName = '张三';
--select 是用来筛选列的
--where 是用来筛选行的
2.逻辑运算符的使用
SELECT * FROM student WHERE stuName = '张三' AND gender = '女';
SELECT * FROM student WHERE gender = '女' OR gradeId = 3;
SELECT * FROM student WHERE NOT gender = '女';
3.比较运算符的使用
SELECT * FROM student WHERE gradeId > 1;
SELECT stuName FROM student WHERE address IS NULL; --查询地址为空 不能用=
SELECT stuName FROM student WHERE address IS NOT NULL;
4.between...and..的使用
SELECT stuName FROM student WHERE  gradeId >= 2 AND gradeId <= 3; 等价于 SELECT stuName FROM student WHERE  gradeId BETWEEN 2 AND 3;
5.IN 的使用
SELECT * FROM student WHERE gradeId = 1 OR gradeId = 3; 等价于 SELECT * FROM student WHERE gradeId IN (1,3);
6.模糊查询
-- % 表示任意单个/多个字符
-- _ 表示任意单个字符
SELECT * FROM student WHERE stuName LIKE '张%';  --查询姓为张的学生信息

• 联表查询

1.内连接可以理解为交集(常用)
SELECTstu.stuName,g.gradeName
FROMstudent stu
INNER JOIN grade g
ON stu.gradeId = g.gradeId; --关联条件
2.采用左连接 以左表为主 LEFT JOIN前面的就是左表
SELECTstu.stuName,g.gradeName
FROMstudent stu
LEFT JOIN grade g
ON stu.gradeId = g.gradeId;
3.自连接  一张表拆分成两张表来看
SELECTc1.categoryName,c2.categoryName
FROMcategory c1,category c2
WHEREc1.pid = c2.id

• 分页和排序

1.排序 ASC--->升序 DESC--->降序
--需求：查询出学生姓名、课程名称、成绩 并且按照成绩进行降序排序
SELECTstu.stuName,sub.subjectName,r.result
FROMresult r,student stu,`subject` sub
WHEREr.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY r.result DESC;
--需求：在上方基础进行改动 要求按照成绩进行降序 且如果成绩相同按照时间降序
SELECTstu.stuName,sub.subjectName,r.result,r.examDate
FROMresult r,student stu,`subject` sub
WHEREr.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BYr.result DESC , r.examDate DESC;
2.分页
--第N页:limit (pageNo-1)*pageSzie,pageSzie   解释:[pageNo:页码,pageSize:单页面显示条数]
--查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
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='JAVA第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10  --0表示从第0个数据开始，10表示每页10条数据

• 子查询 ---->嵌套的查询

--查询课程为 高等数学-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
-- 方法二:使用联表查询+子查询
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'
)
-- 方法三:使用子查询
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常用函数

1.常用的字符串函数
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*字符串长度*/
SELECT CONCAT('我','爱','程序');  /*合并字符串*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串 开始和长度 索引从1开始*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
--查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
2.常用的数学函数
SELECT ABS(-8);  /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4);   /*向下取整*/
SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
SELECT ROUND(20.5);  /*四舍五入*/
3.日期和时间函数
--获取当前时间
SELECT NOW();
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
--日期转换
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
--计算时间差额
SELECT DATEDIFF(NOW(),'2019-05-25');
4.常用的聚合/统计函数
SELECT MAX(gradeId) FROM grade; --求年级编号最大的
SELECT AVG(result) FROM result WHERE subjectId = 1; --求平均分
SELECT SUM(result) FROM result WHERE subjectId = 1; --求和
SELECT COUNT(stuId) FROM student; --统计字段出现次数  不统计字段为null的记录
SELECT COUNT(1) FROM student; --计算学生总人数  包含字段为null的记录
5.md5数据加密函数
--对pwd这一列数据进行加密
update testmd5 set pwd = md5(pwd);
--单独对某个用户(如kuangshen)的密码加密
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
--插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
--查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

• 分组查询

1.计算各个年级的学生人数
SELECT gradeId,COUNT(1) FROM student GROUP BY gradeId;
2.如果我希望查看 各个年级的总人数 且要求查看对应的学生姓名列表
SELECT gradeId,COUNT(1),GROUP_CONCAT(stuName) FROM student GROUP BY gradeId;
--GROUP_CONCAT()函数作用:将同一分组中该字段的值拼接到一块
3.计算各个年级各个性别的人数
SELECT gradeId,gender,COUNT(1) FROM student GROUP BY gradeId,gender;
4.查询年级的学生人数大于1人的年级名称
SELECTstu.gradeId,count(1),g.gradeName
FROMstudent stu,grade g
WHEREstu.gradeId = g.gradeId
GROUP BYgradeId
HAVINGcount(1) > 1;
5.HAVING和WHERE的区别？
相同点 都可以用来进行条件判断 筛选行数
不同点位置不同： WHERE是在分组之前 HAVING是在分组之后条件筛选不同： WHERE是给FROM查询到的数据进行条件筛选 而HAVING是对分组后的数据进行筛选