MySQL数据库的基础sql语句 表和库的定义

库的定义

1.  show databases  查看MySQL数据库所有的库 

2. create database qiaoning charset utf8; 创建qiaoning数据库 字符集位utf8

3. show create database qiaoning 查看创建数据库qiaoning所定义的信息

4.  alter database qiaoning charset utf8mb4;   修改qiaoning数据库的字符集为utf8mb4;

5. drop  database qiaoning  删除qiaoning的数据库

6. use qiaoning;  进入到qiaoning的数据库中

表的定义；

1. 创建表：

create table qiaoning (
sid int not null primary key comment '学号',
sname varchar(64)  not null comment '姓名',
sage enum ('m','n')  notn ull default 'm' comment '性别'
)engine=innodb charset=utf8mb4 comment '测试专用';

2. 查看表

show tables;
show create table qiaoning;
desc qiaoning;

3. 删除表

drop table qiaoning；

4. 修改表属性

alter table qiaoning rename qiaoning;
alter table qiaoning charset utf8;
alter table qiaoning add wangtao char(11)  not null comment '王涛';
alter table qiaoning add linux char(12)  not null comment 'linux' after sname;
alter table qiaoning add windows char(13) not null comment 'windows' first;
alter table qiaoning change windows unix  tinyint not null default 0 comment 'unix';
alter table qiaoning modify unix varchar(11) not null comment 'unix';
alter table qiaoning drop unix;
alter table qiaoning drop wangtao;
alter table qiaoning drop linux;

5. update delete insert

insert into qiaoning (sid,sname,sage) values (1,'qiaoning','n'),(2,'wangtao','m');
insert into qianing values (1,'qiaoning','n'),(2,'wangtao','m');

update qiaoning set sname='school' where sname='qiaoning';

delete from qiaoning where sname='qiaoning';
delete from qiaoning where sname like '%qiao%';

6. select

select * from qiaoning;
select sid,sname,sage from qiaoning;
select sid,sage from qiaoning;
select * from qiaoning where sname='qiaoning';
select * from qiaoning where sname like 'qiao';
select * from qiaoning where sname='qiaoning' and sname='wangtao';
select * from qiaoning where sname='qiaoning' or sname='wangtao';
select  * from qiaoning where sname in ('qiaoning','wangtao');