数据库的一些基本语句

总结一下数据库的一些基本语句及用法。

使用数据库

use myuser;

创建用户

create user myuser identified by '123456';

修改用户

alter user myuser identified by '123457';

删除用户

drop user myuser;

授权

grant select on test1.test to myuser;

收回授权

revoke select on test1.test from myuser;

创建表

create table mytable
(
     number int primary key,#主键约束
     name   varchar(45) unique,#唯一约束
     age    datetime default now(),#默认约束
     sex    enum('男’,'女')
);

加一列

alter table myable add column address varchar(45);

改类型

alter table mytable modify address int;

删列

alter table mytable drop column address;

删表

drop table mytable;

查询

select number from mytable;
select number,name from mytable;
select * from mytable;

增加

insert into mytable values(1,'test','2022-1-1','男');

修改

update mytable set sex ='男';

删除

delete from mytable;

显示一列

select* from mytable where number =1;

显示非一列

select* from mytable where number !=1;
select* from mytable where number <>1;

显示集合

select* from mytable where number in(1,4,7);

排序函数：asc升序 desc降序

select * from mytable order by age asc,number desc;

模糊查询

‘%‘为其余内容，‘_‘’为任意内容。

select *from mytable where name like 't%';
select *from mytable where name like '%t';
select *from mytable where name like '%t%';
select *from mytable where name like '_e%';