SQL学习笔记
DDL语句--操作数据表
/*
快捷键:
insert键 在插入 和 替换模式之间切换
ctrl + 字母z 撤销上一步操作
tab 往后缩进(默认4个空格)
shift + tab 往前缩进(默认4个空格)
ctrl + alt + 字母L 格式化(对其)代码
*/
# 场景3: DDL操作 数据表
# 1. 创建day02数据库
create database day02;
# 2. 切换到day02数据库.
use day02;
# 3. 查询指定数据库下, 所有的数据表.
show tables;# 扩展:
# 计算机存储数据(编码): '中' => 十进制的整数, 例如: 55653 => 转成二进制, 例如: 0b00110110 => 存储到计算机中.
# 计算机读取数据(解码): 读取二进制 => 转成十进制 => 找到对应的字符 => 显示
# 问题: '中' 这个字符 应该对应哪个数字呢? 码表说了算, 即: 码表就是维护 字符 和 数字之间的关系的.
# 结论: GBK主要针对于国内, UTF-8是国际通用码表, 也叫万国码.# 4. 查看指定表的信息.
# 4.1 查看数据表的码表等信息(没什么用, 了解)
show create table student;# 4.2 查看具体表的信息.
desc student; -- 查看表结构信息.
# 5. 创建数据表.
/*
格式:
create table 表名 (
字段名1 数据类型 [约束],
字段名2 数据类型 [约束],
字段名3 数据类型 [约束]
);
格式解释:
字段名: 方便我们快速查找某列数据的, 一般要做到: 见名知意.
数据类型: 标记着这一列能放什么形式的数据, 例如: 整数, 小数, 时间格式, 字符串等等...
常用的数据类型(目前先了解, 稍后解释):
整数: int,
字符串: varchar(长度), 长度的意思是, 这里可以存多少个字符. '张', '三', '0'等都是1个字符, '张三' 就是字符串.
约束: 这里的[]表示可选项, 它(约束)是基于数据类型之外, 加强的规范和规则.
*/
# 需求: 创建学生表, 字段有: 学生编号, 姓名, 性别, 详细信息(describe)
create table if not exists student
(
id int not null, # 字段名:id, 整数类型, 约束: 非空约束
name varchar(20), # 字段名: name, 字符串类型, 无约束.
gender varchar(10), # 字段名: gender, 字符串类型, 无约束
`desc` varchar(20) # 细节: 如果非得要用关键字做字段名, 记得用``包裹该词, 这样它就是1个普通的字符串了.
);# 6. 修改数据表, 即: 修改表名, 格式为: rename table 旧表名 to 新表名;
rename table student to stu;# 7. 删除数据表. 格式: drop table 数据表名;
drop table stu;
数据类型和约束介绍
数据类型
用来限定该列值的类型的, 即: 能存什么样的数据, 不能存什么样的数据.
常用的数据类型有:
整数: int
字符串: varchar(长度)
日期: datetime
枚举: enum
约束
就是在数据类型的基础上, 额外增加的规则.
常用的约束:
单表约束
主键约束: primary key, 默认: 非空, 唯一. 一般要结合自增(auto_increment)一起使用.
非空约束: not null
唯一约束: unique
默认约束: default
多表约束
外键约束
foreign key, 有外键列的表叫: 外表, 有主键列的表叫主表.
特点: 外表的外键列不能出现主表的主键列没有的数据.
结论
数据类型和约束保证了表中数据的准确性和完整性.
DDL语句--操作字段
# ------------------------------------------- 以下内容是DDL操作 字段 -------------------------------------------
# 场景3: DDL操作 字段
# 1. 查看(某表的)所有的字段信息.
desc student;# 2. 添加列. 添加地址列, address varchar(30) 非空约束
# 格式: alter table 表名 add 字段名 数据类型 [约束];
alter table student add address varchar(30) not null;# 3. 修改列.
# 3.1 修改address列的 数据类型为 int
# 格式: alter table 表名 modify 字段名 数据类型 [约束];
alter table student modify address int; -- 细节: 因为没写非空约束, 所以约束没了.# 3.2 修改address列的名字为 addr, 类型为 date, 非空约束.
# 格式: alter table 表名 change 旧字段名 新的字段名 数据类型 [约束];
alter table student change address addr date not null;# 4. 删除列.
# 格式: alter table 表名 drop 字段名;
alter table student drop addr; -- 删除addr列
alter table student drop `desc`; -- 删除desc列# 简化记忆格式:
# alter table 表名 add/modify/change/drop 字段名 [新的字段名] 数据类型 约束;
DML--操作表数据
增
# DML语句, 主要是对表数据进行更新操作的, 更新操作 = 增, 删, 改.
# 快捷键: alt + shift + ↑/↓ 代码的上下移动
# 快捷键: shift + 回车 快速向下插入一行
# ----------------------------------以下是DML语句之 添加表数据 ----------------------------------
# 1. 切库.
use day02;
# 2. 查看数据表.
show tables;
# 3. 查询表数据, 这个先了解, 稍后详解.
# 格式: select * from 表名;
select * from student; # 查询表中所有的数据.# 查看学生表的字段信息.
desc student;# 4. 添加表数据, 方式1: 不指定字段, 添加一条.
# 格式: insert into 表名 value(值1, 值2, 值3...); 要求: 因为没有指定字段, 默认是全列名, 所以必须给每一列值赋值.
# 细节1: 值的个数 和 类型, 必须和 列的 个数和类型保持一致.
# 细节2: 数字可以直接写, 其它类型用引号包裹, 单双引号均可, 建议: 单引号.
insert into student value(1, '乔峰', '男');# 5. 添加表数据, 方式2: 指定字段, 添加一条.
# 格式: insert into 表名(列名1, 列名2...) value(值1, 值2...);
insert into student(id, gender) value(2, '女');-- 报错, 因为id列我们设置了非空约束, 因为只写了gender列, 所以只会给该列添加值, 其它的列会默认用null填充.
insert into student(id, gender) value(null, '女'); # 报错.# 6. 添加表数据, 方式3: 不指定字段, 添加多条.
# 格式: insert into 表名 value(值1, 值2, 值3...), (值1, 值2, 值3...), (值1, 值2, 值3...);
insert into student value(1, '虚竹', '男'), (3, '段誉', '男');# 7. 添加表数据, 方式4: 指定字段, 添加多条.
# 格式: insert into 表名(列名1, 列名2...) value(值1, 值2...), (值1, 值2...), (值1, 值2...);
insert into student(id, gender) values(4, '保密'), (5, '萨瓦迪卡');
总结(细节)
如果不指定字段, 则默认是全列名.
无论是否指定字段, value后边写的值的个数和类型, 必须和字段的个数和类型保持一致
改
一定一定要写where条件, 否则是修改所有.
# ---------------------以下是DML语句之 修改表数据 ----------------------
# 非常非常非常重要的内容, 一个老屌丝的含泪忠告, 当你在进行删除或者修改操作的时候, 一定一定一定要加where条件.
# 格式: update 表名 set 列名1=值1, 列名2=值2 where 条件;
# 需求: 修改id为2的数据, 姓名为: 许文杰, 男
update student set name='许文杰', gender='男' where id=2;# 这个动作看看就行了, 很危险, 别碰. 因为没有写where, 会一次性修改表中所有的数据.
update student set name='许文杰', gender='男';select * from student;
删
一定一定要写where条件, 否则是删除所有.
# ---------------------以下是DML语句之 删除表数据 ------------------------------
# 格式: delete from 表名 where 条件;
# 细节: 不能值删除某个字段的值, 是以 行为单位进行删除的.
delete from student where id=1; # 删除id=1的数据.
delete from student where id>=3; # 删除id 大于3的数据.# 这个动作看看就行了, 很危险, 别碰. 因为没有写where, 会一次性修改表中所有的数据.
delete from student;
SQL约束--增强
主键约束
/*
约束:
概述/作用:
是基于数据类型之上, 对某列的数据做要求的, 保证数据的完整性和安全性.
分类:
单表约束:
主键约束 primary key
特点:
非空, 唯一, 一张表中, 主键列最多只能有1列.
一般要结合 自增(自动增长, auto_increment)一起使用.
非空约束
not null
唯一约束
unique
默认约束
default
多表约束:
主外键约束, foreign key
*/
# 1. 创建员工表 employee, 字段: 员工id(主键), 姓名, 性别, 工资, 部门
# 为了防止建表的时候, 建错库, 要么记得use 切库, 要么写: 数据库名.数据表名
create table if not exists day02.employee(
id int primary key , # 员工id(主键)
name varchar(20), # 姓名
gender varchar(10), # 性别
salary decimal(5, 3), # 工资
dept varchar(10) # 部门
);# 2. 设置id列为主键列.
# 2.1 方式1: 创建表的时候, 直接设置主键.
# 参考上述代码.# 2.2 方式2: 创建表之后, 设置主键.
# 格式: alter table 表名 add primary key(主键列);
alter table day02.employee add primary key(id);
alter table day02.employee add primary key(name); # 报错, 一张表主键只能有1个.# 3. 往员工表中添加表数据.
insert into employee value(1, '乔峰', '男', 66.123, '丐帮');
# insert into employee value(1, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键必须唯一.
# insert into employee value(null, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键不能为空.
insert into employee value(2, '洪七公', '男', 33.33, '丐帮');
insert into employee value(4, '黄蓉', '女', 55.33, '丐帮');# 4. 查询结果.
select * from day02.employee;# 5. 查看表结构.
desc day02.employee;-- 6. 删除主键.
alter table day02.employee drop primary key ;
alter table day02.employee modify id int;-- 7. 实际开发中, 主键约束一般要结合自动增长(auto_increment)一起使用, 因为主键仅仅是1个标记, 标记着这条数据的唯一性, 不参与业务.
drop table day02.employee;create table if not exists day02.employee(
id int primary key auto_increment, # 员工id(主键)
name varchar(20), # 姓名
gender varchar(10), # 性别
salary decimal(5, 3), # 工资
dept varchar(10) # 部门
);# 查询表数据
select * from day02.employee;# 有了自增后, 主键添加数据的方式如下, 只要传入null即可, 至于具体的数字是谁, 程序(计算机)会自动生成.
insert into employee value(1, '乔峰', '男', 66.123, '丐帮');
insert into employee value(null, '洪七公', '男', 33.33, '丐帮'); # 不报错, 有自增, 主键会自动生成.insert into employee value(1, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键必须唯一.
# 细节: 此时删除了id 为 5的数据, 之后再填加数据, 主键从几开始.
delete from day02.employee where id = 5;# 扩展: delete from 和 truncate table 之间的区别是什么?
# delete from 会清除表数据, 不会重置主键id. 属于DML语句, 一般可以结合事务一起使用.
delete from day02.employee;# truncate table 会清除表数据, 会重置主键id, 相当于把表摧毁了, 然后创建一张和该表一模一样的表. 属于DDL语句.
truncate table day02.employee; -- 这个table可以省略.
约束总结
/*
约束:
概述/作用:
是基于数据类型之上, 对某列的数据做要求的, 保证数据的完整性和安全性.
分类:
单表约束:
主键约束 primary key
特点:
非空, 唯一, 一张表中, 主键列最多只能有1列.
一般要结合 自增(自动增长, auto_increment)一起使用.
非空约束
not null
唯一约束
unique
默认约束
default
多表约束:
主外键约束, foreign key
*/
# 1. 创建员工表 employee, 字段: 员工id(主键), 姓名, 性别, 工资, 部门
# 为了防止建表的时候, 建错库, 要么记得use 切库, 要么写: 数据库名.数据表名
create table if not exists day02.employee(
id int primary key , # 员工id(主键)
name varchar(20), # 姓名
gender varchar(10), # 性别
salary decimal(5, 3), # 工资
dept varchar(10) # 部门
);# 2. 设置id列为主键列.
# 2.1 方式1: 创建表的时候, 直接设置主键.
# 参考上述代码.# 2.2 方式2: 创建表之后, 设置主键.
# 格式: alter table 表名 add primary key(主键列);
alter table day02.employee add primary key(id);
alter table day02.employee add primary key(name); # 报错, 一张表主键只能有1个.# 3. 往员工表中添加表数据.
insert into employee value(1, '乔峰', '男', 66.123, '丐帮');
# insert into employee value(1, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键必须唯一.
# insert into employee value(null, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键不能为空.
insert into employee value(2, '洪七公', '男', 33.33, '丐帮');
insert into employee value(4, '黄蓉', '女', 55.33, '丐帮');# 4. 查询结果.
select * from day02.employee;# 5. 查看表结构.
desc day02.employee;-- 6. 删除主键.
alter table day02.employee drop primary key ;
alter table day02.employee modify id int;-- 7. 实际开发中, 主键约束一般要结合自动增长(auto_increment)一起使用, 因为主键仅仅是1个标记, 标记着这条数据的唯一性, 不参与业务.
drop table day02.employee;create table if not exists day02.employee(
id int primary key auto_increment, # 员工id(主键)
name varchar(20), # 姓名
gender varchar(10), # 性别
salary decimal(5, 3), # 工资
dept varchar(10) # 部门
);# 查询表数据
select * from day02.employee;# 有了自增后, 主键添加数据的方式如下, 只要传入null即可, 至于具体的数字是谁, 程序(计算机)会自动生成.
insert into employee value(1, '乔峰', '男', 66.123, '丐帮');
insert into employee value(null, '洪七公', '男', 33.33, '丐帮'); # 不报错, 有自增, 主键会自动生成.insert into employee value(1, '洪七公', '男', 33.33, '丐帮'); # 报错, 主键必须唯一.
# 细节: 此时删除了id 为 5的数据, 之后再填加数据, 主键从几开始.
delete from day02.employee where id = 5;# 扩展: delete from 和 truncate table 之间的区别是什么?
# delete from 会清除表数据, 不会重置主键id. 属于DML语句, 一般可以结合事务一起使用.
delete from day02.employee;# truncate table 会清除表数据, 会重置主键id, 相当于把表摧毁了, 然后创建一张和该表一模一样的表. 属于DDL语句.
truncate table day02.employee; -- 这个table可以省略.# ----------------------------------------- 单表约束总结 -----------------------------------------
# 创建英雄表hero, 字段: id, 姓名, 技能, hp血量, mp蓝量
create table hero(
id int primary key auto_increment, # 主键, 自增
name varchar(20) unique , # 姓名, 唯一约束
skill varchar(20) not null unique, # 技能, 伪主键(即: 非空 + 唯一)
hp int default 100, # 血量, 默认是100
mp int default 10 # 蓝量, 默认是10
);# 添加数据.
insert into hero(id, name, skill) value(null, '典韦', '疯魔'); # 测试 默认约束.
insert into hero value(null, '典韦', '吸血', 6666, 20); # 报错, 姓名要求唯一.
insert into hero value(null, '典韦2', '疯魔', 6666, 20); # 报错, 技能要求唯一.
insert into hero value(null, '典韦2', null, 6666, 20); # 报错, 技能要求不能为空.insert into hero value(null, '吕布', '天魔乱舞', 10000, 0);
# 查询表数据.
select * from hero;
DQL语句
简单查询
# DQL语句主要是针对于 表数据 进行查询操作的.# 0. 准备数据, 即, 直接执行如下的内容即可, 这样就有表以及数据了.use day02;# 创建表CREATE TABLE product # 商品表(pid INT PRIMARY KEY, # 商品id, 主键.pname VARCHAR(20), # 商品名price DOUBLE, # 价格, 小数category_id VARCHAR(32) # 商品所属的类别id);# 插入记录INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,null);INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');# ---------------------------------------- 以下是具体的查询动作 简单查询 ----------------------------------------# 1. 查询数据表中所有的数据, 格式: select * from 表名;select pid, pname, price, category_id from product; # 查询所有的列select * from product; # 如果查询的是所有的列, 则: 可以用*替代所有的列名.# 2. 获取所有商品的名称和价格select pname,price from product;# 3. 获取所有商品的名称和电商价(电商价 = 价格 + 10), 数值列可以直接运算.select pname,price + 10 from product;# 4. 给列起别名, 注意: 别名只是为了查询方便做的, 表中真实的列名并没有被修改.# 格式: as 别名, 且 as 可以省略不写.select pname as '商品名称',price + 10 as '我是修改后的价格' from product;select pname '商品名称',price + 10 '我是修改后的价格' from product; -- as 可以省略不写
条件查询
# ---------------------------------------- 以下是具体的查询动作 条件查询 ----------------------------------------
/*
DQL中的条件查询指的是 where条件, 即: 写了where之后, 只会筛选出我们要的数据.格式:
select * from 表名 where 条件;where后边可以写:
1. 比较运算符
2. 逻辑运算符
3. 模糊查询
4. 范围查询
5. 空判断
*/
# 需求0: 查询所有.
select * from product;# 需求1. 比较运算符, 查询id是偶数的数据.
# 比较运算符: >, >=, <, <=, =, !=
select * from product where pid % 2 = 0;# 查询id不为3的数据.
select * from product where pid != 3;# 需求2. 逻辑运算符
# and(逻辑与, 并且的意思, 要求条件都要满足), or(逻辑或, 或者的意思, 只要满足任意1个条件即可), not(取反的意思), 这三个运算符都要和 真(true), 假(false) 一起使用.
# 查询售价在 1000以上 且 分配id是c001的商品.
select * from product where price > 1000 and category_id = 'c001';# 查询分类id是c001 或者 c003的商品信息.
select * from product where category_id = 'c001' or category_id = 'c003';# 需求3. 模糊查询, 格式: like '内容', _ 表示1个占位符, %表示多个占位符
# 查找以 香开头的商品名.
select * from product where pname like '香_'; # _表示1个占位符, 所以这里只能匹配 香后边跟1个字的.
select * from product where pname like '香%'; # %表示多个占位符, 所以这里是 以香开头, 后边是啥无所谓.
select * from product where pname like '%香%'; # 只要包含香字即可.
select * from product where pname like '%霸'; # 以 霸 结尾的.# 需求4. 范围查询, between ... and ...
# 固定区间: 查找价格区间在 200 ~ 5000之间的商品信息.
select * from product where price >= 200 and price <= 5000; # 比较运算符.
select * from product where price between 200 and 5000; # 范围查询, 包左也包右.# 不固定区间, 查找pid为3, 5, 6的数据.
select * from product where pid = 3 or pid = 5 or pid = 6; # 比较运算符.
select * from product where pid in (3, 5, 6); # 范围查询.
select * from product where pid not in (3, 5, 6); # 范围查询.
# 需求5. 空判断, is null, is not null
# 查找 分类id 为null的数据.
select * from product where category_id is null;# 查找 分类id 不为null的数据.
select * from product where category_id is not null;
