【Python大数据学习笔记_day12】

前提:

操作库的前提:先启动服务,并连接它

操作表的前提:先有库,并使用它

操作记录的前提:先有表

 库中表操作

表的英文单词:table

 创建表:create table [if not exists] 表名(字段1名 字段1类型 [字段约束],字段1名,字段1类型 [字段约束]...);

删除表:drop table [if exists] 表名;

修改表名:rename table 旧表名 to 新表名;

注意:修改表中的字段本质也是修改表

查看所有表:show tables;

查看表结构(字段信息):desc 表名;

查看指定表的建表语句:show create table 表名;

# 操作库的前提: 先启动服务,并连接它
# 操作表的前提: 先有库,并使用它
CREATE DATABASE IF NOT EXISTS day02;
USE day02;
# 操作记录的前提: 先有表
# 创建表: create table [if not exists] 表名(字段1名 字段1类型 [字段1约束] , 字段2名 字段2类型 [字段2约束] ...);
CREATE TABLE product
(
    pid   INT,
    pname VARCHAR(100),
    price DOUBLE
);
# if not exists: 如果表不存在就创建,存在就忽略
CREATE TABLE IF NOT EXISTS student
(
    sid   INT,
    sname VARCHAR(100),
    age   INT
);

# 删除表: drop table [if exists] 表名;
# if exists: 如果表存在就删除,不存在就忽略
drop table if exists category;
drop table if exists student;

# 修改表名: rename table 旧表名 to 新表名;
# 注意: 修改列本质也是修改表,放到后面讲解
rename table product to pdt;

# 查看所有表: show tables;
show tables;
# 查看表结构(字段信息): desc 表名;
desc pdt;
# 查看指定表的建表语句: show create table 表名;
# 注意: 此处可以查看默认存储引擎和默认编码
show CREATE TABLE pdt;

 表中字段操作

列/纵队的英文单词:column

 注意:操作字段本质就是在修改表

添加字段:alter table 表名 add [column] 字段名 字段类型 [字段约束];

删除字段:alter table 表名 drop [column] 字段名;

自改字段名和字段类型:alter table 表名 change [column] 旧字段名 新字段名 字段类型 [字段约束];

modify只修改字段类型::alter table 表名 modify [column] 字段名 字段类型 [字段约束];

查看字段信息:desc 表名;

# 注意: 操作字段本质就是在修改表
# 添加字段:  alter table 表名 add [column] 字段名 字段类型 [字段约束];
alter table pdt add  color varchar(100);
alter table pdt add column size varchar(100);
# 注意: 如果添加的字段名是关键字,报错,如果解决? 加反引号
alter table pdt add `desc` varchar(100);

# 删除字段:  alter table 表名 drop [column] 字段名;
alter table pdt drop `desc`;
alter table pdt drop column size;

# 修改字段名和字段类型:  alter table 表名 change [column] 旧字段名 新字段名 字段类型 [字段约束];
alter table pdt change  price price int;
alter table pdt change  pid id varchar(100);

# modify只修改字段类型: alter table 表名 modify [column]  字段名 字段类型 [字段约束];
alter table pdt modify id int;

# 查看字段信息: desc 表名;
desc pdt;

表中记录操作

插入数据记录: insert into 表名 (字段名...) values (具体值...) , (具体值...);   
            注意1: 具体值要和前面的字段名以及顺序一一对应上
            注意2: 如果要插入的是所有字段,那么字段名可以省略(默认代表所有列都要插入数据)
            注意3: 如果要插入多条记录,values后多条数据使用 逗号 分隔
            
修改数据记录: update 表名 set 字段名=值 [where 条件];
            注意: 如果没有加条件就是修改对应字段的所有数据

删除数据记录: delete from 表名 [where 条件];
            注意: 如果没有加条件就是删除所有数据
            
清空所有数据:
    方式1: delete from 表名;   注意:此方式有警告
    方式2: truncate [table] 表名;  注意: 此方式没有警告 

/*
插入数据记录: insert into 表名 (字段名...) values (具体值...) , (具体值...);
			注意1: 具体值要和前面的字段名以及顺序一一对应上
			注意2: 如果要插入的是所有字段,那么字段名可以省略(默认代表所有列都要插入数据)
			注意3: 如果要插入多条记录,values后多条数据使用 逗号 分隔
*/
# 需求: 现在需要创建学生表,存储多个学生的学号,姓名,年龄,班级信息
# 建表
create table if not EXISTS stu(
    id varchar(100),
    name varchar(100),
    age int,
    cls varchar(100)
);
# 注意: 表中没有设置约束,只要满足格式任意数据都能插入成功!
# 指定字段插入1条数据
insert into stu(id,name) values ('张三',18); # 注意: 插入成功,但是混乱赋值
insert into stu(name,age) values ('李四',18);
# 指定字段插入多条数据
insert into stu(name,age) values ('李四',18),('王五',28),('赵六',38);
# 不指定字段插入1条数据(本质是默认所有字段)
insert into stu values (1,'李四',18,'python大数据16期');
# 不指定字段插入多条数据(本质是默认所有字段)
insert into stu values (2,'王五',28,'python大数据16期'),(3,'赵六',38,'python大数据16期'),(2,'田七',48,'python大数据16期');


/*
修改数据记录: update 表名 set 字段名=值 [where 条件];
			注意: 如果没有加条件就是修改对应字段的所有数据
*/
# 需求:把王五的记录中班级改为python大数据16期
update stu set cls='python大数据16期' where name = '王五';

# 需求:把所有id为null的数据记录中班级改为大数据16期
# 注意: null的比较不能使用=号,为空:  is null  不为空: is not null
update stu set cls='大数据16期' where id is null;

# 注意: update如果不加条件,会更改所有,慎用!!!
update stu set age = 18; # 注意:报黄警告1次,运行时候叹号警告1次



/*
删除数据记录: delete from 表名 [where 条件];
			注意: 如果没有加条件就是删除所有数据
清空所有数据:
    方式1: delete from 表名;   注意:此方式有警告
    方式2: truncate [table] 表名;  注意: 此方式没有警告 
*/
# 需求:删除cls为null的数据
delete from stu where cls is null;
# 需求:删除id为null的数据
delete from stu where id is null;
# 注意: delete如果不加where条件,会删除所有数据,慎用!!!
delete from stu; # 注意:报黄警告1次,,运行时候叹号警告1次,选择execute

# 为了演示truncate清空所有数据的效果,重新插入数据
insert into stu(name,age) values ('李四',18),('王五',28),('赵六',38);
# truncate清空数据
truncate  stu;

表中约束

约束作用:限制数据的插入和删除

主键约束

主键约束关键字: primary key

主键约束特点: 限制主键插入的数据不能为空,不能重复

建表的时候添加主键约束:  create table 表名(主键名 主键类型 primary key , 其他字段...);

注意: 一个表中只能有一个主键

# 演示各类约束
# 注意: 所有的约束都建议建表的时候添加
# 比如: 如果stu表中已经有重复数据,再去添加主键限制不能重复,添加失败!!!
insert into stu(id,name,age) values (1,'李四',18),(1,'王五',28),(1,'赵六',38);
alter table stu add PRIMARY KEY (id); # 报错
desc stu;
# 主键约束: 关键字: primary key  特点: 限制值非空唯一
# 添加主键约束方式1: 建表时候添加
create table stu1(
    id varchar(100) PRIMARY KEY,
    name varchar(100),
    age int,
    cls varchar(100)
);
# 查看表结构
desc stu1;
# 添加主键约束方式2: 建表后添加
create table stu2(
    id varchar(100),
    name varchar(100),
    age int,
    cls varchar(100)
);
# 添加主键约束
alter table stu2 add PRIMARY KEY (id);
# 查看表结构
desc stu2;


# 了解删除主键约束
# 删除非空约束
# 注意: 使用change直接删除主键,非空和唯一约束都没有删除
alter table stu1 change id id VARCHAR(100);
# 查看表结构
desc stu1;

# 注意: 使用drop直接删除主键,只是把唯一键删除,不能为空的约束保留
alter table stu2 drop PRIMARY KEY;
# 查看表结构
desc stu2;
# 再使用change删除非空约束
alter table stu2 change id id VARCHAR(100);
# 查看表结构
desc stu2;

# 了解主键约束的特点: 限制值不能为空,不能重复
insert into stu1(id,name) values (null,'张三'); # 报错Column 'id' cannot be null
insert into stu1(id,name) values (1,'张三'); # 成功
insert into stu1(id,name) values (1,'李四'); # 报错 Duplicate entry '1' for key 'PRIMARY'

图解

 主键自增

自增关键字: auto_increment

自增特点: 默认从1开始,每次自动加1  
注意: 如果插入数据的时候指定了id字段,可以使用null或者0占位表示自动使用自增

建表的时候添加主键自增约束:  create table 表名(主键名 主键类型 primary key auto_increment , 其他字段...);

# 主键自增
# 添加主键自增方式1: 建表后添加
# 注意: 如果要设置主键自增,字段类型需要是整数类型
# 可以同时添加主键和自增,但是前提是表中没有主键
alter table stu2 change id id int PRIMARY KEY auto_increment;
# 注意: 可以单独添加自增,但是前提是表中已经有主键
alter table stu1 change id id int auto_increment;

# 添加主键方式2: 建表时候添加
create table stu3(
    id int PRIMARY KEY auto_increment,
    name varchar(100),
    age int,
    cls varchar(100)
);
# 查看表结构
desc stu3;

# 了解主键自增的特点: 每次自增1
# 如果设置了主键后,null或者0代表使用主键自增
insert into stu3(id,name) values (null,'张三'); # 成功 默认第一次编号自动生成了1
insert into stu3(id,name) values (0,'张三'); # 成功
insert into stu3(id,name) values (1,'李四'); # 报错 Duplicate entry '1' for key 'PRIMARY'
insert into stu3(name) values ('王五');


# 了解自增的删除
# 删除stu2表的自增,主键保留
alter table stu2 change id id int ;
# 删除stu1表的自增,主键保留
alter table stu1 change id id int ;

/*
结论: 如果想要删除主键和自增
1.先用change删除自增
2.再用drop删除主键中的唯一约束
3.最后用change删除非空约束
*/
# 删除stu3的主键和自增
alter table stu3 change id id int ;
alter table stu3 drop PRIMARY KEY ;
alter table stu3 change id id int ;

图解

 delete和truncate区别

delete和truncate的区别?
    共同点: 都能删除表中所有数据
    不同点:
        delete删除所有数据: 自增顺序保留,下次再插入的时候继续自增
        truncate删除所有数据: 自增顺序重置,下次再插入数据的时候从1重新开始自增

# delete删除所有数据: 自增顺序保留,下次再插入的时候继续自增
delete from stu3; -- 警告2次(慎用)
# 如果再次插入数据,自增顺序按照删除前最后值继续
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);

# truncate删除所有数据: 自增顺序重置,下次再插入数据的时候从1重新开始自增
truncate stu3; -- 没有警告(慎用)如果真要清空所有数据建议用truncate
# 如果再次插入数据,自增重新从1开始自增
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);

 非空约束

非空约束关键字: not null

非空约束特点: 限制对应数据不能为空null

建表的时候添加非空约束:  create table 表名(主键名 主键类型 primary key ,字段名 字段类型 not null , 其他字段...);

注意: 一个表中可以有多个非空约束

# 3.非空约束
# 创建表
create table stu4(
    id int not null,
    name varchar(100) not null,
    age int
);

# 查看表结构
desc stu4;

# 演示非空约束特点: 限制数据不能为空null
insert into stu4 (id, name, age) VALUES (1,'张三',18); # 成功
insert into stu4 (id, name, age) VALUES (null,null,null); # 报错,因为id和name不能为空


# 了解建表后添加和删除非空约束
# 添加非空约束
alter table stu4 change age age int not null;
# 删除非空约束
alter table stu4 change age age int;

 图解

唯一约束

唯一约束关键字: unique

唯一约束特点: 限制对应的数据不能重复

建表的时候添加唯一约束:  create table 表名(主键名 主键类型 primary key ,字段名 字段类型 unique, 其他字段...);

注意: 一个表中可以有多个唯一约束

# 4.唯一约束
# 创建表
create table stu5(
    id int not null unique ,
    name varchar(100) unique ,
    age int
);
# 查看表结构
desc stu5;
# 演示唯一约束特点: 限制数据不能重复
insert into stu5 (id, name, age) VALUES (1,'张三',18); # 成功
insert into stu5 (id, name, age) VALUES (1,'李四',28); # 报错,因为id不能重复
insert into stu5 (id, name, age) VALUES (null,'王五',null); # 报错,因为id设置唯一约束同时也设置了非空约束


# 了解建表后添加和删除唯一约束
# 添加唯一约束(自动添加唯一索引,索引名就是字段名)
alter table stu5 change age age int unique ;
# 删除唯一约束(本质就是删除唯一索引)
drop index age on stu5;

# 添加唯一索引(手动添加唯一索引,自定义索引名称)
create unique index age_index on stu5 (age);
# 删除唯一约束(本质就是删除唯一索引)
drop index age_index on stu5;
# 查看表结构
desc stu5;

 图解

 默认约束

默认约束关键字: default

默认约束特点: 可以提前给字段设置默认值

建表的时候添加默认约束: create table 表名(主键名 主键类型 primary key ,字段名 字段类型 default 默认值, 其他字段...);

注意: 一个表中可以有多个默认约束

# 5.默认约束
# 创建表
create table stu6(
    id int primary key auto_increment,
    name varchar(100) default 'admin',
    pwd varchar(100) default '123456'
);
# 查看表结构
desc stu6;

# 演示默认约束特点: 如果用户没有指定字段值,可以使用提前设置好的默认值
insert into stu6(id) values (1);
insert into stu6(name) values ('张三');
insert into stu6(name,pwd) values ('李四','abcde');


# 了解建表后添加和删除默认约束
create table stu7(
    id int primary key auto_increment,
    name varchar(100) ,
    pwd varchar(100)
);
# 添加默认约束
alter table stu7 change name name varchar(100) default 'admin';
alter table stu7 change pwd pwd varchar(100) default '123456';
# 删除默认约束
alter table stu7 change name name varchar(100);
alter table stu7 change pwd pwd varchar(100);

 图解