1. 练习
#创建表并加约束
create table category(
categoryId int primary key,
categoryName varchar(20) not null
)
create table good(
goodId int primary key auto_increment,
goodName varchar(10) not null,
price decimal(5,2),
produceDate date not null,
address varchar(20) default '郑州',
categoryId int,
constraint foreign_key foreign key (categoryId) references category(categoryId)
)
#插入数据
insert into category(categoryId,categoryName)
value
(1001,'食品'),
(1002,'日用'),
(1003,'洗漱')
insert into good(goodName,price,produceDate,categoryId)
value
('面',10.5,'2022-7-13',1001),
('米',15.5,'2022-7-14',1001),
('馒头',1.5,'2022-7-13',1001),
('扫帚',5.5,'2022-7-15',1002),
('盆',10.5,'2022-6-13',1003),
('肥皂',6.5,'2022-7-10',1003)
#查询
SELECT * from good;
select categoryId,categoryName from category2. 数据定义语言(DDL)
? ? ? ? 主要是用来修改数据结构用的，增加/减少/修改 字段(属性)? 添加各种约束
#添加新的字段
alter table 表名 add 列名<类型>;
#添加新的字段 位置在第一列
alter table 表名 add 列名<类型> first;
#将列名1添加到列名2的后面
alter table 表名 add 列名1<类型> after 列名2;
#修改字段名和类型
alter table 表名 change column? <旧列名> <新列名> <新列类型>;
#修改列名的类型
alter table 表名 modify <列名><类型>;
#删除列
alter table 表名 drop <列名>;
#给表重命名
alter table 表名 rename <新表名>
#设置字符集
alter table 表名 character set <字符集名>;
#移动列的位置? 将列名a移动到列名b的后边
alter table 表名 modify <列名a>(列类型)? after <列名b>;?
#给id添加自增约束
alter table 表名 modify id int(5) auto_increment;
#修改表中的字段不为空/或者为空
alter table 表名 modify 字段名 not null/null;
#添加表的字段自增主键? ?修改则将add 改成 modify
alter table 表名 add column 字段名? 类型? auto_increment not null,add primary key;
#添加唯一约束
alter table 表名 add constraint 约束名(uk_字段名) unique(字段名);
#删除表的字段
alter table 表名 drop column 字段;
#删除自增主键约束
alter table 表名 modify id int;
#删除表的主键约束，要删除自增约束才可以删除主键约束
alter table 表名 drop primary key;
#添加外键约束??
alter table 表名 add constraint 外键名? foregin key(引用id) references 被引用表名(被引用id)?
#添加主键约束
alter table 表名 add constraint 约束名 primary key (主键列名)
#删除外键约束
alter table 表名? drop foregin key 外键名
------可以通过show create table 表名? ?来查看外键名
3. 增删改语句
3.1 insert 语句
insert [into] 表名[(colimn1.column2,column3,...)]
value/values (value1,value2,value3,...);
????????????????可以省略列名，但需要按顺序插入所有列，值和列要一一对应。
?#一次录入多条数据
insert [into] 表名[(colimn1.column2,column3,...)]
value/values (value1,value2,value3,...),
value/values (value1,value2,value3,...),
value/values (value1,value2,value3,...)
#在innodb存储引擎下，会自增断层
insert? [into]? (table_name)??[(colimn1.column2,column3,...)]
select? value1,value2,...? union
select? value1,value2,...??
3.2 update 语句
update table_name
set
? ? ? ? column1={ },column2 = { },...
where <表达式>??
?3.3 delete 删除语句
delete from table_name where <表达式>
?删除表中的数据：
delete : 根据条件删除，逐行删除，自增值不会重置；
truncate：整表删除，无法设定条件，效率高，自增列会重新计数，无法恢复数据；无法直接删除有外键引用的表。
#删除数据
#以数据行为单位进行逐行删除，删除速度较慢
#安全，可以记录日志，需要时可以通过日志进行恢复
delete from 表 [where 条件]
#truncate 截断、截取
#直接删除整张表中的数据，相当于先把物理表删除再创建一个新表
#执行效率高，但是无法设定条件
#自增列会重新计数
truncate table 表名
?3.4 级联
涉及到有关主外键关联的数据表：
①删除数据时：先删除外键表，再删主键表
②录入数据时：先录入主键，再录入外键
③更新数据；
级联：当更新（删除）主键表中的数据时，外键表中引用的数据会自动更新(删除)
cascade：删除主键时，自动删除外键表中引用的数据，修改时自动更新外键表中引用的数据。
# on delete CASCADE : 开启 删除 级联
# on update cascade: 开启 更新 级联
alter table emp
add constraint fk_deptno foreign key (deptno)
references dept(deptno)
on delete cascade on update cascade;#级联设置
4. 查询
4.1 语法：
select? 列名1,列名2
from 表名
#可选
where 条件表达式
group by 列名
having 条件表达式
order by 列名
limit? ?,?? 分页
?说明：
①查询表的列时，可直接写列名，顺序自定义
select `name`,age,address,sex from student;②可以使用 select * 查询所有列，查询效率低
select * from student;③列和表都可以起别名
select s.`name`,s.age+3 from student s;④查询中支持做运算? ?👆
?4.2 去重查询
? ?去除查询结果中重复的数据
select distinct? 列名 from 表名
4.3 条件查询where语句
SQL语句中的条件表达式：查询条件，判断条件，修改条件
条件运算符：= > < >= 和 !=
算术运算符：+ - * / %（mod) 不支持：++? --? 复合赋值 += -=
逻辑运算符：and(&&) , not(!) , or(||)
#查询价格大于5元的商品
select goodsName 商品名,price 价格 from goods where price > 5;
#查询价格大于10元的并且产地是广州的商品
select goodsName 商品名,price 价格,address 产地 from goods where price > 10 && address = '广州';
#查询商品类型是食品，或者价格小于5元的商品
select
g.goodsName,g.price,c.categoryName
from goods g,category c
where
(g.categoryId = c.categoryId and c.categoryName = '食品')
or (g.price < 5 and g.categoryId = c.categoryId);
#查询价格不小于5的商品
select
goodsName,price
from goods
where price >=5;
#查询产地不是广州的
select
*
from goods
where address != '广州';
#带条件修改
#1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期
update goods g,category c
set g.price = price +1,g.produceDate = now()
#select *
#from goods g,category c
where
g.address = '郑州' and g.categoryId = c.categoryId and c.categoryName = '食品';
#2. 将价格小于5的并且是饮品的商品产地改为新乡
update goods g,category c
set g.address = '新乡'
#select *
#from goods g,category c
where
g.price <5 and g.categoryId = c.categoryId and c.categoryName = '饮品';?#范围查询：between and
#查询商品编号是2-9之间的数据
SELECT
*
from goods
where goodsId between 2 and 9 #包含开始和结束
#where goodsId>=2 and goodsId<=9#范围查询：in 的使用（用于不连续的条件）
#查询商品是面包和鸡蛋的记录
SELECT
*
from goods
where goodsName in ('面包','鸡蛋')
#where goodsName = '面包' or goodsName = '鸡蛋'4.4 模糊查询
4.4.1 like
与'%'结合使用：匹配任意长度任意内容
与'_'结合使用：匹配一个长度任意内容
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据,三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
%：表示任意 0 个或多个字符,可匹配任意类型和长度的字符,有些情况下若是中文,需使用两 个百分号（%%）表示；
_：表示任意单个字符，匹配单个任意字符，它常用来限制表达式的字符长度语句。
4.4.2 正则通配符
正则模式REGEXP操作符
“.” ????????匹配任意单个的字符
“[ ]” ???????匹配在[]内的任意一个字符
[^] ????????匹配不在[]内的任意一个字符
“ * ” ??????匹配零个或多个在它前面的字符
“+” ????????匹配+前面的字符1次或多次
“{n}” ??????匹配前面的字符至少n次
“^” ????????匹配文本的开始字符
“$” ????????匹配文本的结尾字符
#商品名字带有‘面’的商品
select *
from goods
where goodsName like '%面%'
#商品名字以‘面’字开头且只有两个字的商品
select *
from goods
where goodsName like '面_'
#用正则表达式查询，以面开头的记录
select
*
from goods
where goodsName REGEXP '^面'4.5 排序操作
order by 列名
asc(默认) 升序?
desc 降序
#按照商品编号降序呈现
select
*
from goods
order by goodsId desc
#查询商品，按价格从小到达排序
SELECT
*
from goods
order by price asc
#多字段排序:按照价格升序，如果价格相同，生产日期降序排序
SELECT
*
from goods
order by price,produceDate desc4.6 分页查询limit(结合排序使用)
????????每次我们直接 select * from **?会将数据全部查询出来，如果一张表数据量比较大，此时 容易爆炸，使用分页操作可以只查一部分的数据。
#查询前三条记录
SELECT
*
from goods
limit 3 #起始位置0可以省略
#获取4，5，6数据
SELECT
*
from goods
limit 3,3
#按照价格升序排序进行分页
select * from goods
order by price asc
limit 1,3