SQL语句 | 青训营笔记

椰香薄饼
72 阅读8分钟

SQL语句

1. SQL语句通用语法

  1. SQL语句可以单行或多行书写，以分号结尾

  2. SQL语句不区分大小写

  3. SQL分类

    DDL(Data Definition Language) 数据定义语言，用于定义数据库对象：数据库，表，列等

    DML(Data Manipulation Language) 数据操作语言，用于对数据库中表的数据进行增删改

    DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录（数据）

    DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建数据

2.DDL

  • 浏览已创建的数据库

    show database;

  • 创建数据库（如果重复则不创建）

    create database if not exists test;

  • 删除数据库（如果存在则删除与上面一致）

    drop database test;

  • 使用数据库

    use test;

  • 查看当前使用的数据库

    select database();

  • 查询表

    • 查询当前数据库下所有表的名称

      show tables;

    • 查询表结构

      desc 表名称;

  • 创建表

    create table 表明
(
	字段名1 数据类型1,
	字段名2 数据类型2,
	字段名3 数据类型3			
);

  • 删除表

    drop table 表名称;

image-20221115001025219.png

  • 数据类型

    分类数据类型大小描述
    数值类型TINYINT1 byte小整数型
    数值类型SMALLINT2大整数型
    数值类型MEDIUMINT3
    数值类型INT/INTEGER4
    数值类型BIGINT
    数值类型FLOAT
    数值类型DOUBLEscore double(总长度，小数点后保留的位数)
    数值类型DECIMAL小数值
    日期和时间类型DATE日期值
    TIME时间值或者持续时间
    YEAR年份
    DATETIME混合日期和时间
    TIMESTRAP混合日期和时间值，时间戳
    字符串CHAR定长字符串name char(10) 10个字符空间 存储性能高 浪费空间
    VARCHAR变长字符串name varchar(10) 2个内存空间 存储性能低 节省空间
    TINYBLOB不超过255字符的二进制字符串
    TINYTEXT
    BLOB
    TEXT
    MEDIUMBLOB
    MEDIUMTEXT
    LONGBLOB
    LONGTEXT
    create table Student(
	id int,
	name varchar(10),
	gender char(1),
	birthday date,
	score double(5,2),
	email varchar(64),
	tel varchar(15),
	status tinyint
);

  • 修改表

    1. 修改表名

      ALTER TABLE 表明 RENAME TO 新表名;

    2. 添加一列

      ALTER TABLE 表名 ADD 列名 数据类型;

    3. 修改数据类型

      ALTER TABLE 表名 MODIFY 列名 新数据类型;

    4. 修改列名和数据类型

      ALTER TABLE 表名 MODIFY 列名 新列名 新数据类型;

    5. 删除列

      ALTER TABLE 表名 DROP 列名;

3.DML

  1. 添加数据

    • 给指定列添加数据

      INSERT INTO 表名(列名1，列名2，...) VALUES (值1,值2,...);

    • 给全部列添加数据

      INSERT INTO 表名 VALUES (值1，值2,...);

    • 批量添加数据

      INSERT INTO 表名(列名1,列名2) VALUES (值1，值2,...),(值1,值2,...)...;
INSERT INTO 表名 VALUES (值1，值2,...),(值1,值2,...)...;

  2. 修改数据

    • 修改表数据

      UPDATE 表名 SET 列名1=值1，列名2=值2，...[where 条件];

  3. 删除数据

    • 删除表数据

      delete from 表名 where 条件;

4.DQL

1. 查询语法
SELECT 字段列表
FROM   字段列表	
WHERE  字段列表
GROUP BY 字段列表
HAVING 字段列表
ORDER BY 字段列表
LIMIT  字段列表
2. 基础查询
select 列名 from 表名;
select distinct 列名 from 表名;	-- 去除重复记录
select name as 姓名 , math , english from stu;	-- 查询特定列，as 给列取别名
3. 条件查询
select age from stu where age between 20 and 30; -- 查询20-30岁的人	(between ... and ...)
select age from stu where age <> 18; 			 -- 查询不等于18岁的人(<>)
select age from stu where english is null;		 -- 查询英语成绩为null

-- 模糊查询 like
/*
	通配符：
	1. _:代表单个任意字符
	2. %:代表任意个数字符
*/
select name from stu where name like '马%';
4. 排序查询
select age from stu order by age;					-- 查询年龄，按照年龄升序排列
select math from stu order by math desc;			-- 查询数学成绩，降序排列
select math from stu order by math desc,english esc;-- 先降序查数学(desc)，在升序查英语(asc)
5. 分组查询

  1. 聚合函数

    对一列数据作为一个整体进行一个纵向的计算

    count(列名)		-- 统计数量(一般不选用null)
max(列名)			-- 最大值
min(列名)			-- 最小值
sum(列名)			-- 求和
avg(列名)			-- 平均值

    select 聚合函数名(列名) from 表名;

  2. 分组查询

    select 字段列表 from 表名 where 分组前条件限定 group by 分组字段名 having 分组后条件过滤;

    select avg(math) from stu group by gender;

    -- 查询男女同学各自数学平均分，以及各自人数，要求：分数低于70的不参与分组
select sex, avg(math), count(*) from stu where math > 70 group by sex;
-- 查询男女同学各自数学平均分，以及各自人数，要求：分数低于70的不参与分组，分组后人数大于2个
select sex, avg(math), count(*) from stu where math > 70 group by sex having ;

  3. 分页查询

    select 字段列表 from 表名 limit 起始索引,查询条目数	-- 起始索引从0开始
-- 计算公式：起始索引 = (当前页码 - 1) * 每页显示的页数

5. 约束

create table emp (
	id int primary KEY auto_increment,	-- primary key 主键且自增长(auto-increment)[当列是数字类型且 唯一约束]
	ename varchar(50) not NULL UNIQUE,	-- not null 非空 unique 唯一（唯一约束）
	joindate DATE not null,				
	salary double(7,2) not null,
	bonus double(7,2) DEFAULT 0			-- 没有奖金默认为0，默认约束
	
);

-- 主键约束，非空且唯一
insert INTO emp(id,ename,joindate,salary,bonus) VALUES (null,'张三','1999-11-11',10000,5000); -- 会报错，主键不能为空

  • 外键约束：让两个表的数据建立连接，保证数据的一致性和完整性

    -- 创建表时添加外键约束
create table 表名 (
	列名 数据类型,
	...
	[Constraint] [外键名称] foreign key(外键列名) references 主表(主表列名) 
);

-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键名称字段) references 主表名称(主表列名称)

-- 删除约束
alter table 表名 drop foreign key 外键名称;

6. 数据库简介

  • 软件研发步骤

image-20221120151752814.png

  • 数据库设计步骤

    ① 需求分析(数据是啥？数据具有哪些属性？数据和属性的特点？)

    ② 逻辑分析(ER图【Entity - Relationship】)

    ③ 物理设计

    ④ 维护设计

1. 表关系之一对多

实现方式：在多的一方建立外键，指向一的一方的主键

2. 表关系之多对多

实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

image-20221120153550557.png

3. 表关系之一对一

实现方式：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一(UNIQUE)

7. 多表查询

  • 笛卡尔积：取A,B集合的所有组合情况
  • 多表查询：从多张表查询数据
    1. 连接查询
      • 内连接：查询A，B交集的数据
      • 外连接：
        • 左外连接：相当于查询A表所有数据和交集部分数据
        • 右外连接：相当于查询B表所有数据和交集部分数据
    2. 子查询
内连接

只显示根据条件匹配相同的字段值a_id,b_id的行数据

img

  1. 内连接查询语法

    -- 隐式内连接,给表起别名（t1,t2）
select 字段列表 from 表1，表2... where 条件;
select emp.name , emp.gender from emp t1 , dept t2 where emp.dep_id = dept.did;

-- 显式内连接
select * from emp inner join dept on emp.dep_id = dept.id;
外连接

  1. 外连接查询语法

    -- 左外连接（查询emp表所有数据和对应的部门信息）
select * from emp left join dept on emp.dep_id = dept.id;
-- 右外连接（查询dept表所有数据和对应的员工信息）
select * from emp left join dept on emp.dep_id = dept.id;

右外连接（左外同理）

请添加图片描述

子查询

  • 查询之中嵌套查询

  • 根据查询结果不同，作用不同

    1. 单行单列(可以使用= / != / > / <等进行条件判断)

      select 字段列表 from 表 where 字段名 = (子查询);
select * from emp where dep_id = (select did from dept where dname = '财务部');

    2. 多行单列(使用in等关键字进行条件判断)

      select 字段列表 from 表 where 字段名 in (子查询);

    3. 多行多列(虚拟表)

      select 字段列表 from 表 where 条件;

8. 事务

数据库的事务是一种机制，是一个操作序列，包含一组数据库操作命令

事务将所有的命令作为一个整体一起向系统提交或撤销操作请求，即这组数据库命令要不同时成功，要么同时失败

-- 开启事务
start transaction;
begin;

-- 提交事务
commit;

-- 回滚事务
rollback;

  1. 事务的四大特征

    Atomicity (原子性)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败

    Consistancy (一致性)：事务完成时，必须是所有数据都保持统一状态

    Isolation (隔离性)：多个事务之间，操作的可见性

    Durablity (持久性)：事务一旦提交或者回滚，他对数据库里的数据的改变就是永久地

avatar
文章
阅读
粉丝