create database xuexiao;

use xuexiao;

create table studentInfo(
    `name` varchar(10),
    sex char,
    age int,
    address varchar(20)
);

show tables;
-- 插入操作
-- 值需要按照字段的順序插入，如果插入的值和創(chuàng)建表順序相同酷窥，則字段可以省略
insert into studentInfo(`name`,sex,age,address) values ('大鳥','男',18,'河南省');
insert into studentInfo(sex,age,`name`,address) values ('男',18,'小鳥','河南省');
insert into studentInfo values('小小鳥','男',15,'河南省');

-- 修改已創(chuàng)建的表字段默認值
alter table studentInfo change sex sex char default '男';

insert into studentInfo(`name`,sex,age,address) values ('宋江','',32,'山東');
insert into studentInfo(sex,age,`name`,address) values ('女',18,'小花','河南省');
insert into studentInfo values('武松','男',25,'山東省');-- 有默認值 如果省略字段咽安，則值需要寫全

select * from studentInfo;


create table studentInfo2(
    id int auto_increment primary key,
    `name` varchar(10),
    sex char,
    age int,
    address varchar(20)
);

show tables;


insert into studentInfo2(id,`name`,sex,age,address) values (1,'宋江','男',32,'山東');
insert into studentInfo2(`name`,sex,age,address) values ('武松','男',25,'山東');
insert into studentInfo2(id,`name`,sex,age,address) values (3,'燕青','男',24,'山東');
insert into studentInfo2(`name`,sex,age,address) values ('魯智深','男',25,'山東'),
('楊志','男',23,'山東'),
('林沖','男',28,'山東'),
('石秀','男',23,'山東'),
('盧俊義','男',35,'山東');

select * from studentInfo2;


insert into studentInfo2 values (9,'李逵','男',25,'山東'),
(10,'時遷','男',23,'山東'),
(11,'吳用','男',28,'山東'),
(12,'花榮','男',23,'山東'),
(13,'潘金蓮','女',18,'山東');


-- 修改  update...set...where條件
update studentInfo2 set name = '西門慶' where name = '潘金蓮';-- 修改指定的數(shù)據(jù)
update studentInfo2 set name = '西門慶' ;-- 沒有寫條件，修改全部的數(shù)據(jù)

-- 刪除 delete from table_name where...    有日志留下蓬推，可以恢復(fù)
delete from studentInfo2 where name = '西門慶';-- 刪除符合條件的數(shù)據(jù)

delete from studentInfo;-- 刪除所有數(shù)據(jù)

-- 銷毀數(shù)據(jù)   不留下日志妆棒，速度快，無法恢復(fù)
truncate table studentInfo;

delete from studentInfo2 where name = '吳用';

insert into studentInfo2 values (11,'吳用','男',25,'山東');

update studentInfo2 set age = 28 where name = '吳用';