建库
-
创建名为yusheng的数据库
create database yusheng;
-
确认数据库是否创建成功
show databases;
-
使用某个数据库
use yusheng;
查库
- 查看数据库版本
select version();
- 查看数据库名字
select database();
- 查看当前用户
select user();
- 查看数据库状态
show status\G
- 查看数据库配置的变量
show variables\G
建表
- 创建student表
create table if not exists student(
id int unsigned primary key auto_increment,
name varchar(64) not null,
birthday date default null
)engine=innodb default charset=utf8;
- 查看student表是否创建成功
show tables;
- 查看某个表的字段属性
desc student;
- 查看建表语句
show create table student;
- 查看表的字段属性
show columns from student;
- 创建临时表teacher,只对当前连接有效,关闭连接时自动删除
create temporary table if not exists teacher(
id int(10) unsigned primary key auto_increment,
name varchar(32) not null
)engine=innodb default charset=utf8;
- 创建student表的备份表student_bak
create table student_bak like student;(只复制表结构)
insert into student_bak select * from student;(只复制数据)
create table student_copy select * from student;(复制表结构和数据,但是表结构可能不完整,结合desc student_copy分析)
落表
- 在student表中插入1条数据
insert into student(name,birthday) values('yusheng','2019-8-31');
- 在student表中一次插入2条数据
insert into student(name,birthday) value('cliff','2019-8-31'),('ford','2019-8-31');
查表
- 查询student表所有数据(全表查询)
select * from student;
- 查找名字以li开头的所有学生(模糊查询)
select * from student where name like 'li%';
- 查找名字叫lisi的学生(条件查询)
select * from student where name = 'lisi';
- 查找生日分别是2019-8-31和2019-9-1的学生(联合查询)
select * from student where birthday = '2019-8-31' union select * from student where birthday = '2019-9-1';
- 查找名字以rd结尾的学生(正则查询)
select * from student where name regexp 'rd$';
- 按照生日统计学生的人数(分组查询)
select birthday,count(*) from student group by birthday;
- 同时找出所有学生的学生信息与该学生的班级信息(联表查询)
select birthday,count(*) from student group by birthday;(笛卡尔联表)
select s.*,c.* from student s left join class c on s.class_id = c.id;(左联)
select s.*,c.* from student s right join class c on s.class_id = c.id;(右联)
select s.*,c.* from student s inner join class c on s.class_id = c.id;(内联)
修表
- 修改指定id学生的生日
update student set birthday = '2019-9-1' where id = 2;
- 给student表添加字段sex enum('man','woman','unknow') default 'unknow'
alter table student add column sex enum('man','woman','unknow') not 'unknow';
- 删除student表的性别字段
alter table student drop column sex;
- 修改student表address字段为varchar(128)
alter table student modify column address varchar(128);
- 修改student表sex字段的默认值为woman
alter table student alter sex set default 'woman';
- 修改表名
alter table student1 rename to student;
- 调整student表列顺序
alter table student modify birthday date default null after sex;
- 修改存储引擎:修改为myisam
alter table student engine=myisam;
- 给班级名称添加唯一约束
alter table class add unique name_constraint(name);
- 添加检查约束,学生人数大于等于40
alter table class add constraint student_num_constraint check(student_num >= 40);
- 删除学生人数大于等于40的约束
alter table class drop check student_num_constraint;
- 给班主任名字添加索引
create index class_teacher_index on class(class_teacher);
出表
- 删除id为1的学生
delete from student where id = 1;
删表
drop table student;