学完MySQL后发现关于表的操作非常多,所以就做了一个关于表的操作总结。
目录
1.创建表
MySQL可以忽略大小写
CREATE TABLE 表名(
属性名 数据类型,
属性名 数据类型,
.
.
.
属性名 数据类型
);
在创建表时建议加上if not exists,因为只要创建表名相同的表时,mysql就会报错。
-- 如果不存在表animal就创建,否则不创建
create table if not exists animal(
id int
);
2.修改表及数据
1.增加表的数据
1.1增加单条数据不指定字段
注意需要与列名对应
insert into 表名 values(数据,数据,..,数据);
1.2增加单条数据指定字段
insert into 表名 (字段1, 字段3, 字段3) values(value1, value2, value3)
1.3增加多条数据
insert into 表名(字段1, 字段2, 字段3....) values (value1, value2,value3.....),(value1, value2, value3....);
举例
create table animal(
id int ,
name varchar(999)
);
insert into animal values (1,"狗");
insert into animal(id ,name)values (2,"猫");
insert into animal(id,name) values(3,"牛"),(4,"羊")
结果
在这之前我已经增加了一条数据,所以会有两条狗
1.4增加字段
ALTER TABLE 表名 ADD 字段名 数据类型;
2.修改表的数据
2.1修改表里所有数据
update 表名 set 字段名1=值1,字段名2=值2...,字段名n=值n;
2.2修改指定数据
update 表名 set name="狗" where id=1;
2.3修改字段名
alter table 表名 change 字段名 字段新名称 字段的类型;
举例
//*create table animal(
id int ,
name varchar(999)
);
insert into animal values (1,"狗");
insert into animal(id ,name)values (2,"猫");
insert into animal(id,name) values(3,"牛"),(4,"羊")
*//
update animal set name="鸡" where id=1;
alter table animal change name newname varchar(999);
结果
2.4修改表名
alter Table 表名 rename to 新表名;
3.删除表及数据
3.1删除表的数据
//*truncate的删除原理是重新创建一个表(不包含数据),然后将原来的表删除。
数据不可恢复,该命令也不能添加查询条件*//
truncate table 表名
-- 从表中删除特定的行(删除表中id为1的记录)
DELETE FROM 表名 WHERE id=1;
-- 从表中删除所有的行
DELETE FROM 表名;
-- 删除时不仅删除了表中的数据,还删除了表结构。如果我们要删除数据库或数据表,一般都会使用drop来删除。
drop table 表名
总结
1.不再需要该表时,用 drop;
2.需要保留该表,但要删除所有记录时, 用 truncate;
3.删除部分记录时,用delete
3.2删除字段
alter table 表名 drop column 字段;
4.约束条件
约束条件可以让表的数据更加准确,约束条件在创建表时可以使用, 也可以修改表的时候添加约束条件。上面的内容都没有添加约束条件,主要想让大家了解原始的指令。
约束条件分类
4.1 not null
非空约束,保证字段的值不能为空
-- 设置字段name的名字不能为空
name VARCHAR(10) NOT NULL;
4.2 defaul
默认约束,保证字段总会有值
-- 默认age的值为18
age INT DEFAULT 18;
4.3 unique
-- 保证唯一性但是可以为空,比如学号
stu_number INT UNIQUE;
4.4 check
检查性约束, MySQL 8.0.15 以及之前的版本中,虽然 CREATE TABLE 语句允许CHECK (expr)
形式的检查约束语法,但实际上解析之后会忽略该子句.
-- 性别只能为男或女
sex CHAR(1) CHECK(sex='男' OR sex='女');
4.5 primary key
主建约束,同时保证唯一性和非空
id INT PRIMARY KEY;
4.6 AUTO_INCREMENT
默认情况下,AUTO_INCREMENT 的初始值是 1,每新增一条记录,字段值自动加 1,每个表只能有一个自动增长的列,并且这个列必须被定义为键。
字段名 数据类型 AUTO_INCREMENT
4.7 foreign key
外键约束,用于限制两个表的关系,保证从表该字段的值来自于主表相关联的字段的值,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。
注意:
- 从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致
- 外键中列的数目必须和主表的主键中列的数目相同。
在创建表时增加外键
CONSTRAINT 外键名
FOREIGN KEY(子表字段) REFERENCES 主表(主表字段)
在修改表时增加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名
FOREIGN KEY(字段名) REFERENCES 主表名 (字段名);
举例,在创建表时增加外键
create table if not exists class(
c_id int,
c_name varchar (99) primary key
);
create table if not exists student(
s_id int,
s_name varchar(99),
CONSTRAINT fk_stu
FOREIGN KEY(s_name) REFERENCES class(c_name)
);
在表 student上添加了名称为 fk_stu 的外键约束,外键名称为 s_name,其依赖于表 class 的主键c_name
举例, 在修改表时增加外键
create table if not exists class(
c_id int primary key ,
c_name varchar (99)
);
create table if not exists student(
s_id int,
s_name varchar(99)
);
ALTER TABLE student ADD CONSTRAINT fk_id
FOREIGN KEY(s_id) REFERENCES class (c_id);
在表 student上添加了名称为 fk_id 的外键约束,外键名称为 s_id,其依赖于表 class 的主键c_id.
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
5.查询表(重点)
表的查询有很多种,有单表的,有多表的,有带条件的,也有不带条件的,还有和函数一起使用的。所以无论是条件查询还是单表查询,我只将它分为单表和多表。
聚合函数
- 聚合函数具有自动滤空的功能,若某一个值为NULL,那么会自动将其过滤使其不参与运算。
- 只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。
一些常用的聚合函数如下
聚合函数 一般和 group by 一起 使用 ,但是也能单独 使用 (前提 sql只返回一个 字段 的值)。 1.和group by配合 使用 , 查询 分组后同一个 字段 多条数据的聚合值。 2.单独 使用 时,规定只能返回一个 字段 的值
1.count()
-- 计算表中的行数或计算指定列其值不为空的数据个数
-- 查询该表中有多少学生
select count(*) from student;
2.max()
-- 计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算
-- 查询该学生表中年纪最大的学生
select max(age) from student;
3.min()
-- 计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算
-- 查询该学生表中年纪最小的学生
select name,min(age) from student group by name;
4.sum()
-- 计算指定列的数值和,如果指定列类型不是数值类型则计算结果为0
-- 查询该学生表中年纪的总和
select sum(age) from student;
5.avg()
-- 计算指定列的平均值,如果指定列类型不是数值类型则计算结果为0
-- 查询该学生表中年纪的平均数
select avg(age) from student;
5.1 单表的简单查询
5.1.1查询当前数据库所有表
show tables;
5.1.2查询表的字段
desc 表名;
5.1.3 查询表的所有数据
select * from 表名;
5.1.4 查看指定字段的数据
select 字段1,字段2 ... from 表名;
5.1.5查询过滤重复的数据
select distinct 字段 from 表名;
5.2 单表的常见条件查询
5.2.1 关系运算符查询
-- 查询年龄在17岁及以上的学生
select * from student where age>=17;
5.2.2 IN关键字查询
IN关键字用于判断某个字段的值是否在指定集合中
-- 查询id为1和100的学生
select * from student where id in (1,100);
-- 查询id不是100的学生
select * from student where id not in (100);
5.2.3 between and 查询
-- 查询年龄在10到15的学生
select * from student where age between 10 and 15;
5.2.4 空值查询
-- 查询名字不为空的学生
select * from student where name is not null;
5.2.5 AND关键字查询
-- 查询年龄大于16岁且性别为男的学生
select * from student where age>16 and gender='男';
5.2.6 OR关键字查询
-- 查询年龄大于16或者性别为男的学生
select * from student where age>16 or gender='男';
5.2.7 LIKE关键字查询
LIKE关键字可以判断两个字符串是否相匹配
-- 查询名字与ikun相同的学生
select * from student where name like 'ikun';
5.2.6 ORDER BY对查询结果排序
参数 ASC表示按照升序排序,DESC表示按照降序排序;
-- 查询学生并按照年龄降序
select * from student order by age desc;
-- 查询学生并按照年龄升序
select * from student order by age asc;
5.2.7 LIMIT限制查询结果的数量
-- 查询年龄最小的3个学生
select * from student order by age asc limit 3;
5.2.8 GROUP BY--分组查询
-- 找到name这一列,将它单独拿出来,如果有重名的忽略
SELECT name FROM student GROUP BY name;
建议看看这篇文章:关于group by的用法 原理-CSDN博客
5.2.9 取别名
别名可以代替表和字段的原名参与查询操作。
-- 为表名取别名
select * from student as newstudent;
-- 为字段取别名
select name as '名字' from student;
5.3 多表查询
先看看别的吧,以后有时间啊在总结