mysql基础知识

1,047 阅读3分钟

建库

  1. 创建名为yusheng的数据库 create database yusheng;

  2. 确认数据库是否创建成功 show databases;

  3. 使用某个数据库 use yusheng;

查库

  1. 查看数据库版本
select version();
  1. 查看数据库名字
select database();
  1. 查看当前用户
select user();
  1. 查看数据库状态
show status\G
  1. 查看数据库配置的变量
show variables\G

建表

  1. 创建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;
  1. 查看student表是否创建成功 show tables;
  2. 查看某个表的字段属性 desc student;
  3. 查看建表语句 show create table student;
  4. 查看表的字段属性 show columns from student;
  5. 创建临时表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;
  1. 创建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分析)

落表

  1. 在student表中插入1条数据
insert into student(name,birthday) values('yusheng','2019-8-31');
  1. 在student表中一次插入2条数据
insert into student(name,birthday) value('cliff','2019-8-31'),('ford','2019-8-31');

查表

  1. 查询student表所有数据(全表查询)
select * from student;
  1. 查找名字以li开头的所有学生(模糊查询)
select * from student where name like 'li%';
  1. 查找名字叫lisi的学生(条件查询)
select * from student where name = 'lisi';
  1. 查找生日分别是2019-8-31和2019-9-1的学生(联合查询)
select * from student where birthday = '2019-8-31' union select * from student where birthday = '2019-9-1';
  1. 查找名字以rd结尾的学生(正则查询)
select * from student where name regexp 'rd$';
  1. 按照生日统计学生的人数(分组查询)
select birthday,count(*) from student group by birthday;
  1. 同时找出所有学生的学生信息与该学生的班级信息(联表查询)
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;(内联)

修表

  1. 修改指定id学生的生日
update student set birthday = '2019-9-1' where id = 2;
  1. 给student表添加字段sex enum('man','woman','unknow') default 'unknow'
alter table student add column sex enum('man','woman','unknow') not 'unknow';
  1. 删除student表的性别字段
alter table student drop column sex;
  1. 修改student表address字段为varchar(128)
alter table student modify column address varchar(128);
  1. 修改student表sex字段的默认值为woman
alter table student alter sex set default 'woman';
  1. 修改表名
alter table student1 rename to student;
  1. 调整student表列顺序
alter table student modify birthday date default null after sex;
  1. 修改存储引擎:修改为myisam
alter table student engine=myisam;
  1. 给班级名称添加唯一约束
alter table class add unique name_constraint(name);
  1. 添加检查约束,学生人数大于等于40
alter table class add constraint student_num_constraint check(student_num >= 40);
  1. 删除学生人数大于等于40的约束
alter table class drop check student_num_constraint;
  1. 给班主任名字添加索引
create index class_teacher_index on class(class_teacher);

出表

  1. 删除id为1的学生
delete from student where id = 1;

删表

drop table student;