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 '_张%';