Jdbc笔记 —— SQL基础与数据库约束

ゞ浴缸里的玫瑰 2023-03-14 10:56 9阅读 0赞

### 文章目录 ###

*   *   *  SQL
         *   *  SQL通用语法
             *  SQL分类
             *   *  DDL
                 *  DML
                 *  DQL
                 *   *  基础查询
                     *  条件查询
                     *  排序查询
                     *  聚合函数
                     *  分组查询
                     *  分页查询
                 *  DCL
         *  约束
         *   *  非空约束
             *  唯一约束
             *  主键约束
             *  外键约束

### SQL ###

定义了一种操作所有关系型数据库的标准规则

每一种关系型数据库操作的方式存在一些差异，称为“方言”（Dialect）

比如MySQL特有的LIMIT关键字

#### SQL通用语法 ####

1.  以分号结尾
2.  SQL不区分大小写，建议关键字用大写
3.  注释
    
    1.  `-- 这是注释` ：单行注释
    2.  `# 注释` ：mysql特有的单行注释
    3.  `/* 多行注释 */`

#### SQL分类 ####

1.  DDL：用来操作**数据库**和**表**
2.  DML：用来操作表中的**记录**，增删改
3.  DQL：用来操作表中的**记录**，查询
4.  DCL：用来做用户管理和权限控制

##### DDL #####

1.  操作数据库：CRUD
    
    1.  Create：创建
        
         *  `create database test;`
         *  `create database if not exists test;`
         *  `create database test2 character set gbk;`
    2.  Retrieve：查询
        
        1.  `show databases;`
            
            可以查看所有的数据库
        2.  `show create database test2;`
            
            查看test2库的创建语句。**可以看到test2库使用的字符集**
            
            ![在这里插入图片描述][20200517195937498.png]
    3.  Update：修改
        
        可以修改库的字符集
        
        `alter database 库名称 character set utf8;`
    4.  Delete：删除
        
        删除数据库
        
        `drop database 库名称;`
        
        `drop database if exists 库名称;`
    5.  使用数据库（进入数据库）
        
        进入某个数据库后，才能操作这个库中的表
        
        1.  查询当前正在使用的数据库
            
            `select database();`
        2.  使用某个数据库
            
            `use 库名称;`
2.  操作表
    
    1.  Create：创建
        
        1.  语法：
            
                -- 创建表
                create table 表名称(
                	列名1 数据类型1，
                    列名2 数据类型2，
                    ...
                    列名n 数据类型n
                );
                
                -- 复制表
                create table 表名1 like 表名2;
        2.  列的常见数据类型
            
            1.  **int**
            2.  **double**
                
                `score double(5,2);` 这个小数一共5位，小数点后保留2位（最大值是999.99）
            3.  **date**
                
                日期，只包含年月日，格式为
                
                yyyy-MM-dd
            4.  **datetime**
                
                日期，包含年月日时分秒，格式为
                
                yyyy-MM-dd HH:mm:ss
            5.  **timestamp**
                
                时间戳类型。如果不给这个列赋值，mysql会自动取当前系统的时间戳给该列赋值
            6.  **varchar**
                
                `name varchar(255);` 指定name这一列最大为255个字符
    2.  Retrieve：查询
        
        1.  查询某个库中所有表
            
            `show tables;`
        2.  查看某个表的结构
            
            `desc 表名称;`
    3.  Update：修改
        
        1.  改表名
            
            `alter table 表名称 rename to 新的表名称;`
        2.  改表的字符集
            
            `alter table 表名称 character set utf8;`
        3.  添加列
            
            `alter table 表名 add 列名 列类型;`
        4.  修改列名称，列类型
            
            `alter table 表名 change 列名 新列名 新类型;`
            
            `alter table 表名 modify 列名 新类型;`
        5.  删除列
            
            `alter table drop 列名;`
    4.  Delete：删除
        
        1.  `drop table 表名称;`
        2.  `drop table if exists 表名称;`

##### DML #####

1.  添加数据
    
    1.  语法
        
            insert into 表名(列名1,列名2, ... , 列名n) values
            (
                值1,
                值2,
                ...
                值n
            )
            -- 列名和值要一一对应
            -- 表名后可以不定义列名，如此则默认给所有列添加值
            insert into 表名 values
            (
                值1,
                值2,
                ...
                值n
            )
            -- 除了数字类型，其他类型要用引号引起来
            -- 比如
            insert into student (name,age,score)
            values(
            	'黄大仙',
                18,
                100
            )
2.  删除数据
    
    1.  语法
        
            delete from 表名 [where 条件]
            
            -- 不加where条件时，则会删除表中全部记录
            delete from 表名;
            
            -- 若要删除所有记录，推荐使用下面的语句
            -- 会先删除表中的所有记录，然后创建一张一样的空表
            -- truncate比delete更快
            truncate table 表名;
3.  修改数据
    
    1.  语法
        
            update 表名 set 列名1 = 值1,列名2 = 值2 ...
            [where 条件]

##### DQL #####

查询表中的数据

*  语法

select 
    	字段列表
    from
    	表名列表
    where
    	条件列表
    group by
    	分组字段
    having
    	分组后的过滤条件
    order by
    	排序字段
    limit
    	分页

###### 基础查询 ######

*  去重
    
        -- 针对name字段去重
        select distinct name from user;
        
        -- 针对name和class字段去重(当2个字段都相同，才认为是重复的记录)
        select distinct name,class from user;
        
        -- 查询所有列
        select * from user;
 *  计算
    
        -- 可以用四则运算（用于数字类型）
        -- 查询商品的名称，价格，税，总价格
        select name,math,english,math + english from test;
        -- 如果参与计算的列为null，则相加后的结果也为null
        -- 可以用ifnull函数来处理,ifnull(e,0) 表示如果e为null，则使用0来替换
        select name,math,english,math + ifnull(english,0) from test;
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70]
 *  起别名
    
        select name u_name from user;
        select name AS u_name from user; -- AS可省略

###### 条件查询 ######

*  where子句后跟条件
 *  可用于where条件的运算符
    
     *  `> ， < ， <= ，>= ，= ，<> , ！=` (`<>`等价于`!=`)
     *  `BETWEEN...AND`
     *  `IN(集合)`
     *  `LIKE`
        
         *  占位符：
            
             *  `_` ：单个占位符
             *  `%`：多个占位符
         *  示例
            
                -- 查询name以马开头的记录
                select * from user where name like '马%';
                -- 查询name为2个字，且姓马的记录
                select * from user where name like '马_';
                -- 查询name包含马的记录
                select * from user where name like '%马%';
     *  `IS NULL ， IS NOT NULL` （NULL值的判断不能用`=`和`!=`）
     *  `AND， &&` 逻辑与
     *  `OR ，||` 逻辑或
     *  `not ，!` 逻辑非

###### 排序查询 ######

*  语法
    
        -- 先按照字段1排序，若字段1相同的记录，则按字段2排序
        order by 排序字段1 排序方式1 , 排序字段2 排序方式2...;
 *  排序方式
    
     *  升序：`ASC`，默认
     *  降序：`DESC`
 *  案例
    
        -- 按照分数升序排列
        select * from user order by score;
        -- 按照年龄降序排列
        select * from user order by score desc;
        -- 先按照数学成绩排列，若数学成绩相同，则按英语成绩排名
        select * from user order by math asc,english desc;

###### 聚合函数 ######

1.  概念
    
    将一列数据作为一个整体，进行纵向计算
2.  mysql的聚合函数
    
    1.  `count()`
        
        1.  `count(name)` ，若name为null的记录，不会被计算在内
        2.  解决方案：
            
            1.  选择不为空的列，比如主键`count(id)`
            2.  `count(ifnull(name,''))` 使用ifnull函数
            3.  `count(*)` ：不推荐使用，影响性能
            4.  `count(1)` 使用伪列
    2.  `max()`
    3.  `min()`
    4.  `sum()`
    5.  `avg()`
    
    注意：聚合函数的计算会排除NULL值

###### 分组查询 ######

1.  语法
    
        group by 子句
2.  案例
    
        -- 按照性别分组，分别查询男，女同学的平均分
        select sex,avg(score) from student group by sex;
        
        -- 按照性别分组，分别查询男，女同学平均分，分数大于70分才参与计算
        -- 分组之前，进行条件限定，使用where
        select sex,avg(score) from student where score > 70 group by sex;
        
        -- 按照性别分组，分别查询男，女同学平均分，分数大于70分才参与计算，并且最后只显示女同学的记录
        -- 分组之后，进行条件过滤，使用having
        select sex,avg(score) from student where score > 70 group by sex having sex = '女';
3.  注意
    
    分组之后，select子句中的字段，必须是分组字段，或者聚合函数。若写其他的某个字段A，虽然不会报错，但查出来会是第一条记录的A字段，没有意义。
4.  where 和 having 区别
    
    1.  where在分组之前进行限定，若记录不满足where条件，则不参与分组；having在分组之后进行限定，若不满足having条件，则不会被查询出来
    2.  where之后不可以跟聚合函数，having之后可以跟聚合函数
        
            -- 按班级分组，统计每个班的平均分，每个班上分数大于70的同学参与统计，并且只展示人数大于30人的班级
            select class,avg(score) from student where score > 70 group by class having count(id) > 30;

###### 分页查询 ######

1.  语法
    
        limit 偏移,记录数
2.  案例
    
        -- 从0开始查，查10个同学，即以每页10条数据，查第一页
        select * from student limit 0,10;
        
        -- 查第2页
        select * from student limit 10,10;
        
        -- 查第3页
        select * from student limit 20,10;
        
        -- 公式
        -- 开始的索引(偏移) = （页码 - 1）* 每页显示条数
3.  注意
    
    1.  分页操作是一种方言(Dialect)，不属于SQL标准，不同的数据库可能有不同的语法
    2.  limit语句是MySQL特有的，Oracle，PostgreSQL中的分页操作，可能就不是limit了

##### DCL #####

用来管理用户，做权限控制的

1.  管理用户
    
    1.  添加用户
        
            create user '用户名'@'主机名' identified by '密码'; -- 案例 -- zhangsan只能本地访问 create user 'zhangsan'@'localhost' identified by '123'; -- % 表示可以在任意主机用该用户来登录mysql create user 'lisi'@'%' identified by '123'
    2.  删除用户
        
            drop user '用户名'@'主机名'
    3.  修改用户
        
            update user set password = password('新密码') where user = '用户名';
            
            set password for '用户名'@'主机名' = password('新密码'); -- mysql 8 用如下方法 update user set authentication_string = '' where user = 'root'; alter user 'root'@'localhost' identified by 'root';
    4.  查询用户
        
            -- 切换到mysql库
            -- 查询user表
    
    =>> 如果忘记了root用户密码
    
    1.  先停掉mysql服务
        
            net stop mysql
    2.  使用无验证方式启动mysql服务
        
            # mysql 5
            mysqld --skip-grant-tables
            
            # 进入mysql后
            use mysql
            update user set password= password('root') where user = 'root';
    3.  杀掉mysqld进程
    4.  重启mysql服务
2.  权限管理
    
    1.  查询权限
        
            show grants for '用户名'@'主机名'
    2.  授予权限
        
            grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' -- 给root用户授予所有权限 GRANT ALL ON *.* TO 'root'@'localhost'; GRANT SELECT,DELETE,UPDATE ON test.student TO 'lisi'@'%';
    3.  撤销权限
        
            revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; -- 撤销zhangsan的update权限 REVOKE UPDATE ON test.student FROM 'lisi'@'%';

### 约束 ###

1.  概念：对表中的数据进行限定，以保证数据的正确性
2.  分类
    
    1.  主键约束：`primary key`
    2.  非空约束：`not null`
    3.  唯一约束：`unique`
    4.  外键约束：`foreign key`

#### 非空约束 ####

1.  创建约束
    
        -- 创建表时指定
        create table student(
        	name varchar(20) not null
        )
        
        -- 创建表后，进行添加
        alter table student modify name varchar(20) not null;
2.  删除约束
    
        alter table student modify name varchar(20);

#### 唯一约束 ####

1.  创建约束
    
        -- 创建表时指定
        create table student(
        	name varchar(20) not null,
            id_no varchar(20) unique
        );
        -- 注意，有唯一约束的列，可以有多个NULL值
        alter table student modify id_no varchar(20) unique;
        -- 创建表后，进行添加
2.  删除约束
    
        -- 不能用上面删除非空约束的方式来删
        -- 唯一约束实际上是唯一索引，所以用drop index
        alter table drop index id_no;

#### 主键约束 ####

1.  概念：主键约束 = 非空约束 + 唯一约束。
    
    一张表只能有一个字段作为主键
2.  创建约束
    
        -- 创建表时，添加
        create table student(
        	id int primary key
        );
        -- 创建表后，添加
        alter table student modify id int primary key;
3.  删除约束
    
        alter table student drop primary key;
4.  自动增长
    
    1.  如果某一列是数值类型，可以使用`auto_increment`来完成值的自动增长
    2.  一般会和主键一起使用
    3.  自动增长时，只会根据上一条插入的记录
    
        -- 添加自动增长
        -- 创建表时，添加
        create table student(
            id int primary key auto_increment,
            name varchar(20)
        )
        --创建表后，添加
        alter table student modify id int auto_increment;
        -- 删除自动增长
        alter table student modify id int;
    
        insert into student (name) values ('yogurt');
        
        insert into student values (NULL,'amber');
        
        insert into student values(10,'tomcat');
        
        insert into student values(NULL,'wireshark');
    
    结果：
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 1]

#### 外键约束 ####

1.  概念：多表之间进行关联，所使用的字段.。可以用于约束多个表之间的关系，从而保证数据的正确性。
2.  创建约束
    
        -- 创建表时，添加
        create table 表名(
        	....
            外键列 int 
            constraint 外键名 foreign key (外键列) references 关联表名(关联表的列)
        )
        -- 创建表后，添加
        alter table student add constraint idx_dep_id foreign key(dep_id) references department(id);
        
        -- 案例
        -- 院系表
        create table department(
        	id int primary key auto_increment,
            name varchar(255) not null,
            professor varchar(255)
        )
        -- 学生表
        create table student(
        	id int primary key auto_increment,
            name varchar(255) not null,
            dep_id int,   -- 院系id，作为关联院系表的外键
            constraint idx_dep_id foreign key (dep_id) references department(id)
        )
        
        -- 创建完表后，执行下面语句，会报错，因为院系表中没有id为1的记录
        insert into student values (NULL,'yogurt',1);
        /* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yogurt`.`student`, CONSTRAINT `idx_dep_id` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)) */
        -- 先往department表中添加一条记录
        insert into department values (NULL,'Computer Science','Mitnick');
        -- 然后再执行上面的sql
        insert into student values (NULL,'yogurt',1);
        -- 就能成功插入了
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 2]
3.  删除约束
    
        -- 案例
        alter table student drop foreign key idx_dep_id;
4.  **外键约束的级联操作**
    
    包括了**级联更新**和**级联删除**
    
    1.  **级联更新**
        
            -- 设置级联更新
            alter table student add constraint idx_dep_id foreign key(dep_id) references department(id) ON UPDATE CASCADE;
            
            -- 这样，只要在department表中对数据进行修改，比如某个院系的教授换了，则会自动更新student表中与该院系关联的学生
        
        案例：
        
        操作前
        
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 3]
        
            -- 对院系表进行修改
            update department set id = 5 where id = 1;
        
        操作后
        
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 4]
    2.  **级联删除**
        
            -- 若删除院系表中某一院系的记录，则在student表中与该院系相关的学生信息，也会被全部删除
            -- 一般不会使用级联删除，会不太安全
            
            
            -- 设置级联删除
            alter table student add constraint idx_dep_id foreign key(dep_id) references department(id) ON DELETE CASCADE;
    3.  **注意**：对级联操作的使用要非常谨慎

[20200517195937498.png]: /images/20230312/a5769902e40848299b4d2ceb29e2ee3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70]: /images/20230312/322d89aca3f0429eb5767a13d89f725b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 1]: /images/20230312/1f02b15aa31e4411b1310fab6ea9dfa0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 2]: /images/20230312/57de1fb6e99a4cf795c92f3b2fcfabe3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 3]: /images/20230312/2b5dff16fe2d4876a5966fedd1574aff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 4]: /images/20230312/2faefc7211d34deda1fa1d33ede0d3cd.png