SQL学习总结

数据库SQL学习

**数据库意义：数据存储与管理 **

1.使用SQL 语句建表的基本格式及基础运用

1.建表的基本格式

CREATE TABLE [IF NOT EXISTS] `表名`(
   `字段名` 列类型[属性] [索引] [注释],
   `字段名` 列类型[属性] [索引] [注释],
   `字段名` 列类型[属性] [索引] [注释],
   `字段名` 列类型[属性] [索引] [注释],
   `字段名` 列类型[属性] [索引] [注释],
   .......
   `字段名` 列类型[属性] [索引] [注释]
)[表类型] [字符集设置][注释]

2.表名的修改及删除

-- 修改表名 alter table 旧表名 rename as 新表名
ALTER TABLE student2 RENAME AS teacher;
 
-- 增加表的字段 alter table 表名 add 字段名 列属性
ALTER TABLE teacher ADD age INT(11);

-- 修改表的字段 （重命名、修改约束)
-- alter table 表名 modify 字段名 列属性[]
ALTER TABLE teacher MODIFY age VARCHAR(11)   -- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性[]
ALTER TABLE teacher CHANGE age age1 INT(1)   -- 字段重命名

-- 删除表的字段
ALTER TABLE teacher DROP age1

-- 删除表
DROP TABLE IF EXISTS teacher

2.dml语言学习

dml语言：

• insert

• update

• delete

  USE school;
  SHOW DATABASES;
  DESC student;
  
  -- 插入数据 --
  INSERT INTO `school`.`student`(`id`,`name`,`age`) VALUES (22,'baobao','22');
  
  -- 修改数据 -- 
  UPDATE `school`.`student` SET `name` = '' WHERE id = 1;
  
  -- 删除数据 --
  DELETE FROM `school`.`student` WHERE id = 22;
  
  -- 清空一个表 -- 
  TRUNCATE `school`.`student`;
  

3.查询语句DQL语句学习

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
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;

-- 创建年级表
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 auto_increment = 6 default charset = utf8;

-- 创建科目表
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 auto_increment = 19 default charset = utf8;

-- 创建成绩表
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;

-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
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);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
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);

查询语句

-- 查询所有的字段 --
SELECT *FROM `student`
SELECT *FROM `grade`

-- 查询指定的字段 --
SELECT `sex`,`studentname` FROM student;

-- 别名，给结果起一个名字 --
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student;

-- 函数 --
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM student;

-- 去重 --
SELECT *FROM result;
SELECT DISTINCT `subjectno` FROM SUBJECT;  

4.联表查询

-- 联表查询 --
SELECT *FROM student;
SELECT *FROM result;

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno` = r.`studentno`

-- right join --
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s     -- as 可写可不写 --
RIGHT JOIN result r
ON s.`studentno` = r.`studentno` 
-- inner join 可以用where  但是left right只能用on

leftjoin和rightjoin的区别：如果要查的行里面有属性值为空，则leftjoin会显示，但是rightjoin不会侠士出来
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dIEKoA3M-1613914753585)(C:\Users\23737\AppData\Roaming\Typora\typora-user-images\image-20210221145001408.png)]

自连接（了解）

*自己的表和自己的表连接 *

5.分页和排序

limit 和 order by(通过哪个字段排序)

1.排序：升序asc 降序desc

-- 分页和排序
-- 降序
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s     -- as 可写可不写 --
RIGHT JOIN result r
ON s.`studentno` = r.`studentno` 
ORDER BY studentresult DESC;

-- 升序
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s     -- as 可写可不写 --
RIGHT JOIN result r
ON s.`studentno` = r.`studentno` 
ORDER BY studentresult asc;

2.分页

-- 语法：limit 当前页，页面大小
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s     -- as 可写可不写 --
RIGHT JOIN result r
ON s.`studentno` = r.`studentno` 
ORDER BY studentresult DESC;
LIMIT 1,2;

6.mysql函数

1.常用函数

-- ==============常用函数=======================

-- 数学函数
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.1)  -- 向下取整
SELECT RAND() -- 返回0~1的随机数

SELECT SIGN(-10)  -- 判断一个数的符号 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('字符串长度') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我喜欢编程',1,1,'你是不是')   --  插入替换  从某个位置开始替换某个长度
SELECT LOWER('AAAA') -- 转小写
SELECT UPPER('aaaa') -- 转大写
SELECT INSTR('222222122222','1')  --  返回第一次出现的字符索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',3,6) -- 截取 开始位置到结束位置
SELECT REVERSE('两极反转') -- 反转字符

-- 查询姓周的同学 名字 绉
SELECT REPLACE(studentname,'周','绉') FROM student WHERE studentname LIKE '周%'

-- 时间和日期函数(常用)
SELECT CURRENT_DATE() -- 获取当前日期
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 SYSTEM_USER()
SELECT USER()
SELECT VERSION()

2.聚合函数

SELECT COUNT(studentname) FROM student -- count(指定列) 会忽略所有的null值
SELECT COUNT(*) FROM student  -- count(*)   不会忽略所有的null值  本质计算行数
SELECT COUNT(1) FROM result   -- count(1)   不会忽略所有的null值  本质计算行数

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

7.索引

1.索引分类

主键索引只能有一个，但是唯一索引可以有多个

• 主键索引 PRIMARY KEY : 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 UNIQUE KEY ： 避免重复的列，多个列都可以标识为唯一索引
• 常规索引 KEY ： 默认的索引，index或者key关键字
• 全文索引 FULLText ： 在特定的数据库引擎下才有

2.索引的使用

在创建表的时候给字段添加索引

-- 显示索引
SHOW INDEX FROM student;

-- 增加一个索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX (`studentname`);

-- 分析sql的执行状况
EXPLAIN SELECT *FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentname) AGAINST('张');

8.三大范式

1.第一范式

原子性： 保证每一列不可再分

2.第二范式

前提：满足第一范式

每张表只描述一件事情

3.第三范式

前提： 满足第一范式和第二范式

第三范式需要满足确保数据表中的每一列数据和主键直接相关，而不能间接相关

9.JDBC

1.数据库驱动

package com.kuang.less01;

import netscape.javascript.JSUtil;

import java.sql.*;

//我的第一个JDBC程序
public class JDBCfirstDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法 加载驱动

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true 支持中文编码 设定字符串为UTF-8 使用安全连接
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username="root";
        String password="123456";

        //3.连接成功 数据库对象   connection代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4.执行sql的对象  statement执行sql的对象
        Statement statement = connection.createStatement();


        //5.执行sql 可能存在结果 查看返回结果

        String sql="SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集 结果集中封装了我们全部的查询出来的结构

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("===================================================");
        }

        //6.释放链接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结
加载驱动
连接数据库DriverManager.getConnection
获取执行sql对象 onnection.createStatement()
获得返回的结果集
释放链接

2.PreparedStatement接口

增

//增
public class lesson01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String usename = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, usename, password);

        String sql = "INSERT INTO `grade`(`gradeid`,`gradename`) VALUES (?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,22);
        preparedStatement.setString(2,"研究生");
        System.out.println("successfull");

        preparedStatement.executeUpdate();
        preparedStatement.close();;
        connection.close();
    }
}

删

//删
class lesson02{
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String usename = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, usename, password);

        String sql = "DELETE FROM `grade` WHERE `gradeid`=22";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("successfull");

        preparedStatement.executeUpdate();
        preparedStatement.close();;
        connection.close();
    }
}

改

//改
class lesson03{
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String usename = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, usename, password);

        String sql = "SELECT `studentname`,`address` FROM `school`.`student`";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //检测
        int i = preparedStatement.executeUpdate();
        if(i > 0){
            System.out.println("successfull");
        }

        preparedStatement.executeUpdate();
        preparedStatement.close();;
        connection.close();
    }
}

查

//查
class lesson04{
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String usename = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, usename, password);

        String sql = "SELECT `studentname`,`address` FROM `school`.`student`";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            System.out.println("id: "+resultSet.getObject("studentname"));
            System.out.println("address: "+resultSet.getObject("address"));
            System.out.println("-----------------------");
        }

        preparedStatement.executeUpdate();
        preparedStatement.close();;
        connection.close();
    }
}