use test;

# 创建表
create table student(
    id int primary key,
    name varchar(100),
    age int(2) default 10);

# 展示该数据库有多少个表
show tables;

# 显示表结构
describe student;

# 删除表
drop table student;

# 给表添加一个字段
alter table student add column sex varchar(2) default 1;

# 给表添加多个字段
alter table student add a int,add b int,add c int;

# 给表添加多个字段，并且默认值
alter table student add x int default 120,add y int default 121,add z int default 122;

# 修改数据库字段类型
alter table student modify column sex char(4);

# 修改字段名称
alter table student change column a aa int default 9797;

# 删除表的一到多个字段
alter table student drop column aa;
alter table student drop column b, drop column c;


# 修改表名
alter table student rename to stu;

# 改回来
alter table stu rename to student;

# 查询表的所有结构
select * from student;

# 插入数据
# 1、单条全字段插入
insert into student values(1, '张三', 19, 'boy', 11, 12, 13);

# 2、单条指定插入
insert into student (id, name, sex) values (2, '哈哈', 'boy');

alter table stdent drop column x,drop column y,drop column z;

alter table student drop column sex;

# 3、批量插入
insert into student 
values
(4, '王五',  13),
(5, '王6', 14),
(6, '王7', 12);

# 修改数据
alter table student add column class varchar(10);

select * from student;

# 把表的当前所有行的class字段的值改成’二班‘
update student set class = '二班';

# 改表的某一行的某个字段
update student set age = 15 where id = 4;

# 按条件修改某一行的某几个字段
update student set age = 1, name = '一', class = '一班' where id = 1;

# where条件可匹配多行
update student set age = 2 where class = '二班';

# 删除数据
# 1、按条件删除
delete from student where id = 6;

# 2、整体删除，删除表当前的所有行(清空表)
delete from student;

# 在修改、查询和删除时，都可以按条件（where...)



alter table student drop  column class;

insert into student values 
(1, '张三', 18),
(2, '李四', 19),
(3, '王五', 18);

alter table student add column class varchar(10);

update student set class = '二班';

# 查询指定列
select name, class from student;

# 字段别名（多表联合查询时可能会用到）
select name '姓名', class '班级' from student;

# 合并列查询，例如想把班级和姓名放在一个列中查询，展示的时候是（二班——张三）
select concat(class, '-', name) '班级-姓名' from student;

alter table student add servlet int, add jsp int;


# 合并查询两个成绩的分数
select name  '姓名', (servlet + jsp) '总成绩' from student;


select name, age, (servlet + jsp) '总成绩', class, '光明小学' from student


select '常量' from student;


# 去重(字段单独使用)
select distinct age from student;





# 条件查询
select * from student where name = '张三'

# 逻辑判断
# >
select name, age, class from student where age > 18;

# between and
select name, age, class from student where age between 15 and 20;

# 查询二班的年龄小于20岁的学生(可以多个and)
select * from student where class = '二班' and age < 20;


select * from student;

insert into student (id, name) values (8, '');

# 判空条件 is null 、 is not null 、=、<>
# 为空 
select * from student where name = '';

# 不为空 <>, 这里包含不等于null和'';
select * from student where name <> '';

# 为null，但不为''
select * from student where name is null;

# 只要不为null
select * from student where name is not null;


# like模糊判断
# 查询姓张的学生
# _代表匹配一个字符，%匹配零到多个字符
select * from student where name like '%张%';

insert into student values (10, '大小张伟', 50, '二班', 9, 9);


select * from student where name like '_张%';