删除:
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
delete from `student`
delete from `student` where id = 1;
truncate
作用:完全清空一个数据库表,表格结构和索引约束都不会变
truncate `student`
相同点:都能删除数据,都不会删除表结构
不同:
- TRUNCATE 重新设置自增列,计数器会归零
- 不会影响事物
DQL查询重点
DQL数据查询语言
数据库中最核心的语言
狂神school.sql
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.33 : Database - school
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `school`;
/*Table structure for table `grade` */
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`GradeID` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` varchar(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `grade` */
insert into `grade`(`GradeID`,`GradeName`) values
(1,'大一'),
(2,'大二'),
(3,'大三'),
(4,'大四'),
(5,'预科班');
/*Table structure for table `result` */
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`StudentNo` int(4) NOT NULL COMMENT '学号',
`SubjectNo` int(4) NOT NULL COMMENT '课程编号',
`ExamDate` datetime NOT NULL COMMENT '考试日期',
`StudentResult` int(4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`SubjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `result` */
insert into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentNo` int(4) NOT NULL COMMENT '学号',
`LoginPwd` varchar(20) DEFAULT NULL,
`StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` tinyint(1) DEFAULT NULL COMMENT '性别,0或1',
`GradeId` int(11) DEFAULT NULL COMMENT '年级编号',
`Phone` varchar(50) NOT NULL COMMENT '联系电话,允许为空',
`Address` varchar(255) NOT NULL COMMENT '地址,允许为空',
`BornDate` datetime DEFAULT NULL COMMENT '出生时间',
`Email` varchar(50) NOT NULL COMMENT '邮箱账号允许为空',
`IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard` (`IdentityCard`),
KEY `Email` (`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert into `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-01-01 00:00:00','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-01-01 00:00:00','text111@qq.com','123456199001011233');
/*Table structure for table `subject` */
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`SubjectNo` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` varchar(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` int(4) DEFAULT NULL COMMENT '学时',
`GradeID` int(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`SubjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `subject` */
insert into `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
查询所有字段
select * from student
查询指定字段
select `StudentName` from student
别名:可以给字段和表起别名
select `Sex` As 芯 from student
- 函数
SELECT CONCAT(‘姓名:’,StudentName) AS 新名字 from student
去重:distinct
作用:select查询出来的结果重复的数据
SELECT `StudentNo` from result
-- 发现重复数据
SELECT DISTINCT `StudentNo` from result
数据库表达式
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 As -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增步长(变量)
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' from result -- 查询成绩加1分
where条件子句
检索数据中符合条件的值
select studentNo,`StudentResult` from result
where StudentResult>=90 AND StudentResult<=100
select studentNo,`StudentResult` from result
where StudentResult BETWEEN 95 AND 100
select studentNo,`StudentResult` from result
where StudentNo !=1000
模糊查询:比较运算符
-- 查询姓的同学
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 '%张%'
-- 查询1000,10002号学员
SELECT `StudentNo`,`StudentName` from `student`
where studentNo in(1000,1002,1003)
联表查询
JOIN
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * from student
SELECT * from result
/*思路
1.分析需求,分析
确定交叉点(这两个表那个数据相同)
判断的条件: 学生表的studentNo = 成绩表studentNo
*/
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result As r
WHERE s.studentNo = r.StudentNo
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student s
RIGHT JOIN result r
on s.studentNo =r.StudentNo
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student s
LEFT JOIN result r
on s.studentNo =r.StudentNo
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
leftJoin | 也会从左表中返回所有的值,即使右表中没有匹配 |
rightJoin | 从右表中返回所有的值,即使左表中没有匹配 |
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表
categoryid | categoryNmae |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryNmae |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | PS技术 |
操作:查询父类对应的子类关系
SELECT a.`categoryName` As '父栏目',
b.`categoryName` AS '子栏目'
from category as a,`category` as b
where a.`categoryid` = b.`pid`
分页和排序
排序:升序ASC,降序desc
-- ORDER BY
SELECT s.`studentNo`,`studentName`,`subjectName`,`studentResult`
from student s
INNER JOIN `result` r
on s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
on r.`SubjectNo` = sub.`subjectNo`
where SubjectName = '高等数学-1'
ORDER BY StudentResult DESC
分页limit
-- Limit 起始值,页面大小
SELECT s.`studentNo`,`studentName`,`subjectName`,`studentResult`
from student s
INNER JOIN `result` r
on s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
on r.`SubjectNo` = sub.`subjectNo`
where SubjectName = '高等数学-1'
ORDER BY StudentResult DESC
LIMIT 0,2
第一页 limit 0,5 (1-1)*5
第二页 5,5 (2-1)*5
第三页 10,5 (3-1)*5
第N页 ,5 (n-1)*pageSize
pageSize页面大小
(n-1)*pageSize 起始值
n:当前页
总页数:数据总数/页面 大小
子查询
where值是固定的
本质:在where语句中嵌套一个子查询语句
函数
常用函数(其实不常用)
select ABS(-2)
SELECT CEILING(9.4)
SELECT FLOOR(9.4)
SELECT RAND() -- 返回0~1之间的随机数
SELECT SIGN(10)
SELECT CHAR_LENGTH('dadsa')
聚合函数(常用)
-- 都能够统计表中的数据(想查询一个表中有多少个记录,就会用count)
SELECT COUNT(`StudentName`) from student;-- count,会忽略所有的null值
SELECT count(*) FROM student;-- 不忽略null值 本质:计算行数
SELECT count(1) FROM student;-- 不忽略null值 本质:计算行数
MD5
不可逆
事务
要么都成功,要么都失败
事务原则:ACID
原子性:要么都成功,要么都失败
一致性:事务前后的数据完整性要保证一致
持久性:事务一旦提交则不可逆,被持久化到数据库中
隔离性:事务的隔离性是多个用户并发访问数据库
隔离导致一些问题:
脏读:
指一个事务读取了另外一个事务未提交的数据
不可重复读:
在一个事务内读取表中某一行数据,多次读取结果不同。
虚读(幻读):
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
索引
索引:帮助MySQL高效获取数据的数据结构。
索引的分类
主键索引:唯一标识,主键不可重复,只能有一个列作为主键
唯一索引:避免重复的列出现,唯一索引可以重复,多个列都可以标识位唯一索引
常规索引:默认的,index,key关键字设置
全文索引:在特定的数据库引擎下才有,快速定位数据。
测试索引
-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用户', i), '112@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显。
mysql导出
三大范式
第一范式:保证每一列不可再分
第二范式:
前提:满足第一范式
每张表只描述一件事情
第三范式:
前提:满足第一、第二范式
第三范式需要确保数据表中每一列数据都和主键直接相关,而不能间接相关
JDBC
package com.gu.lession;
import java.sql.*;
/**
* @Author: ganeshys
* @Date: 2021/7/4 15:13
*/
//JDBC程序
public class JdbcFristDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2,用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "root";
//3连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL对象
Statement statement =connection.createStatement();
//5执行SQL的对象,可能存在结果,查看返回的结果
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
}
//6释放链接
resultSet.close();
statement.close();
connection.close();
}
}