mysql操作练习DML

sql语句 

create database liu;
show databases ;
use vueblog;
show table status ;

drop database  if exists school2;
create database school;
create table if not exists student(
  stu_no int(4)not null comment '学号',
  login_pwd varchar(20)comment '登录密码',
  stu_name varchar(20) comment '名字',
  born_date datetime comment '出生日期',
  identity_card varchar(18),
  sex char(3)
);

use school;
show tables;

create table if not exists student2(
  stu_no int(4)not null comment '学号',
  login_pwd varchar(20)comment '登录密码',
  stu_name varchar(20) comment '名字',
  sex tinyint(1) comment '性别 取值 01',
  gradeId int(11) comment '年级编号',
  phone varchar(50)comment '联系电话',
  address varchar(255)comment '地址',
  born_date datetime comment '出生日期',
  email char(10) comment 'email',
  identity_card varchar(18)
);
alter table student2 add score double comment '分数';
alter table student2 drop score;
alter table student2 modify email int null comment 'email';
alter table student2 change score score2 varchar(10)comment '分数';
alter table student2 drop column score2;
#查看当前有哪些引擎
show engines;

insert  into  student2(stu_no)values ('1002');
insert  into  student2 values (1002,'aa','1',1,1111,'111',now(),'111','11111','111');
create table if not exists student3
(
    stu_no    int(4) not null comment '学号',
    login_pwd varchar(20) comment '登录密码',
    stu_name  varchar(20) comment '名字'
);

insert  into  student3 values (1002,'aa','asa');

create table if not exists student(
  phone varchar(50)comment '联系电话',
  address varchar(255)comment '地址',
  born_date datetime comment '出生日期',
  email char(10) comment 'email',
  identity_card varchar(18)
);



insert  into  student values ('12121','dsada',now(),'523@','3205');
insert into student2(stu_no)values ('1003'),('1004'),('1005');
update student2 set sex=2,phone='111111';
update student2 set stu_no='12121' where stu_no='1002';
update student2 set  stu_name='bsasab' where  stu_no>=1003 && stu_no<=1003;

use school;
show tables ;
desc student2;
rename table student2 to student3;
show databases ;
alter table student3 add ss double comment 'ss';
alter table student3 modify email int null comment 'email';
drop  table  if exists  new_stu;
create table student2 as select * from  school.student3;
create table stu5 like school.student3;

delete from school.student3 where school.student3.stu_no=1003;

delete from stu5;
create database mydb1;
create database mydb2 character set utf8;
alter database mydb2 character set gbk collate gbk_chinese_ci;

create database mydb3;
drop database mydb3;
show databases;

show variables like '%character%';
show variables like 'collation%';
create table if not exists student(
  phone varchar(50)comment '联系电话',
  address varchar(255)comment '地址',
  born_date datetime comment '出生日期',
  email char(10) comment 'email',
  identity_card varchar(18)
);
desc information_schema.schemata;
use mydb1;
show variables like '%character%';
use mydb2;
create database employee;
use employee;
create table if not exists employee(
    id int null comment 'id',
    name char(5),
    gender char(5),
    birthday datetime,
    entry_date datetime,
    job char(5),
    salary double,
    resume text
);

create table if not exists employee2(
    id int auto_increment comment 'id',
    name char(5)not null,
    gender char(5),
    birthday datetime,
    entry_date datetime,
    job char(5),
    salary double,
    resume text,
         primary key (id)
);

drop table employee2;

alter table employee add column image varchar(5);
ALTER TABLE employee MODIFY COLUMN job VARCHAR(60);
alter table employee drop column gender;

alter  table employee rename to user;
alter table user default character set utf8;
alter  table user change column name username char(5);

show databases ;
show create table employee;
show create table employee2;
show create table user;