2.5修改删除表
- 修改表名 alter table 旧表名 rename as 新表名
alter table user rename as user1 - 增加表字段 alter table 表名 add 字段名 字段属性(列属性)
alter table user1 add age int(3) - 修改表的字段(修改约束) alter table 表名 modify 字段名 字段属性
alter table user1 modify age varchar(3) - 修改表的字段(重命名)alter table 表名 change 旧字段名 新字段名 属性
alter table user1 change age age1 int(3)
change和modify的区别
change用于字段重命名,不能用于修改字段类型和约束
modify用于修改字段类型和约束,不能修改字段名 - 删除表字段 alter table 表名 drop 字段名
alter table user1 drop age1 - 删除表drop table [if exists] 表名
drop table if exists user1
创建和删除操作加上判断可以有效的避免报错
sql关键字大小写不敏感
字段名使用反引号包住 ``
3.MySQL数据管理
3.1 建立外键关联
方式一
CREATE TABLE grade(
gradeId INT(3) COMMENT ‘年级id’,
gradeName VARCHAR(6) COMMENT ‘年级名称’,
PRIMARY KEY (gradeId
)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS student(
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
NAME VARCHAR(30) NOT NULL DEFAULT ‘未知’ COMMENT ‘姓名’,
gradeId INT(3) COMMENT ‘年级’,
PRIMARY KEY(id
),
KEY FK_gradeId
(gradeId
),
CONSTRAINT FK_gradeId
FOREIGN KEY (gradeId
) REFERENCES grade
(gradeId
)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
方式二
CREATE TABLE grade(
gradeId INT(3) COMMENT ‘年级id’,
gradeName VARCHAR(6) COMMENT ‘年级名称’,
PRIMARY KEY (gradeId
)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS student(
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
NAME VARCHAR(30) NOT NULL DEFAULT ‘未知’ COMMENT ‘姓名’,
gradeId INT(3) COMMENT ‘年级’,
PRIMARY KEY(id
)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
ALTER TABLE student ADD CONSTRAINT FK_gradeId
FOREIGN KEY(gradeId) REFERENCES grade(gradeId)
alter table 表名 add constraint 约束名 foreign key(作为外键的字段) references 关联的表(关联的字段)
以上操作都是物理外键,数据库级别的外键,不建议使用(避免数据过多造成困扰)
最佳实践
数据库就是单纯的表,只用来保存数据,没有外键
想要使用多张表数据时,使用外键时,通过程序实现
3.2 DML语言
数据库意义:数据存储,数据管理
DML语言:数据操作语言
insert 添加
update 修改
delete 删除
3.3 insert
insert into 表名 ([字段1,字段2,字段3,…]) values(‘值1’,‘值2’,‘值3’,…)
insert into grade(gradeId,gradeName) values(‘1’,‘大一’)
插入多条数据
insert into 表名([字段1,字段2,字段3,…]) values(‘值1’,‘值2’,‘值3’,…),(‘值1’,‘值2’,‘值3’,…)
INSERT INTO grade(gradeId,gradeName) VALUES(‘2’,‘大二’),(‘3’,‘大三’)
字段和字段之间使用英文逗号隔开
字段可以省略不写,但是后面的值必须和字段一一对应
3.4修改
update 表名 set 字段名 = 具体值 where 条件
update grade set gradeName =‘一年级’ where gradeId=1
条件:where 子句(字段 运算符 匹配值)
运算符
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 3=1 | false |
<>或!= | 不等于 | 3<>1 | true |
> | 大于 | ||
< | 小于 | ||
>= | |||
<= | |||
between … and … | 在某个范围内 | [1,3] | |
and | && | 3>1 and 4>5 | false |
or | 3>1 or 4>5 | true |
使用条件筛选数据,如果没有条件则将所有的数据进行修改
多个修改的字段之间使用英文逗号隔开
3.5删除
delete 命令
delete from 表名 [where 条件]
删除符合条件的数据
delete from grade where gradeId=1
删除表中所有的数据,尽量避免这样用
delete from grade
truncate命令
作用:完全清空一个数据库表,表的结构和索引约束不会改变
truncate grade
delete 和truncate的区别
相同点:都能删除数据,但不会删除表结构
不同点:truncate是将表删除再重新创建,delete则不是,因而truncate重新设置自增列,计数器会归零
truncate不会影响事务
4.DQL查询数据
4.1DQL数据库查询语言
所有的查询操作都使用select
简单查询与复杂查询都能实现
数据库中最核心最重要的语句
使用频率最高的语句
4.2查询指定字段
select 字段 from 表名
查询表中所有的数据
select * from grade
查询指定字段
SELECT gradeName FROM grade
使用as给查询的字段,或表起别名
SELECT gradeName AS 年级 FROM grade AS 年级表
函数,concat(a,b) 连接函数,改变查询结果的展示
SELECT CONCAT(‘年级:’,gradeName) FROM grade
去重 distinct
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
SELECT DISTINCT gradeName FROM grade
数据库中的列,表达式
查询系统版本
select version()
用于计算
SELECT 3*4+1 AS 结果
查询自增步长
SELECT @@auto_increment_increment
展示数据+1显示
SELECT gradeId+1 FROM grade
4.3where 查询子句
作用:检索数据库中符合条件的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and 或&& | a&&b 或 a and b | 逻辑与,两个都为真则结果为真 |
or | a or b | 逻辑或,其中一个为真则结果为真 |
not 或! | not a 或 !a | 逻辑非,a为真则假,a为假则真 |
推荐使用英文单词的写法
select * from grade where gradeId>1 &&gradeId<3
select * from grade where not gradeId=2
select * from grade where gradeId!=2
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 若a为空,则结果为真 |
is not null | a is not null | 若a不为空,则结果为真 |
between … and … | a between b and c | 若a在b和c之间,则结果为真 |
like | a like %b%_ | sql匹配,若a匹配b,则结果为真,%表示任意个字符,_表示一个字符 |
in | a in (a1,a2,a3,…) | 若a在a1,a2或a3的某一个值中,则结果为真 |
查询姓王的同学
select * from student where studentName like 王%
查询姓王的同学,且在姓后只有一个字
select * from student where studentName like 王_
查询名字中带佳的同学
select * from student where studentName like %佳%
4.4联表查询
数据查询思路
1.分析需求,分析需查询的字段来自哪些表(连接查询)
2.确定使用哪种连接查询
3.确定交叉点,判断条件
操作 | 描述 |
---|---|
inner join 或 join | 如果表中有匹配的数据则返回 |
left join | 会从左表返回所有数据,右表如果不匹配,则右表数据显示为null |
right join | 会从右表返回所有数据,左表如果不匹配,则左表数据显示为null |
SELECT business_username,audio_title,audio_src FROM audio a JOIN business b ON a.business_id=b.business_id
SELECT business_username,audio_title,audio_src FROM audio a LEFT JOIN business b ON a.business_id=b.business_id
SELECT business_username,audio_title,audio_src FROM audio a RIGHT JOIN business b ON a.business_id=b.business_id
自连接
表本身相连,将一张表视为两张表
select a.name as ‘父节点’,b.name as ‘子节点’ from info a,
info b where a.id=b.pid
4.5分页和排序
排序: 升序 asc 降序 desc
select name,course,scoreNum from student s , score sc
where s.id=sc.id order by scoreNum desc
分页:设置显示5条数据
使用分页得目的:缓解数据库压力,给人更好得体验
语法:limit 起始数据位置,显示条数
select name,course,scoreNum from student s , score sc
where s.id=sc.id order by scoreNum desc
limit 0,5
子查询:在查询中嵌套查询
查询参加了考试的学生姓名
select distinct name from student
where id in (select id from score)
order by scoreNum desc
limit 0,5