mysql基本语句

古城微笑少年丶 2021-09-30 00:42 434阅读 0赞

*  **`数据库`**

创建库
    create database uplooking; 删除库 drop database uplooking; 修改库 alter database uplooking character set = utf8 alter database uplooking collate=utf8_general_ci

*  **`表`**

创建表
    create table home (id int not null primary key auto_increment, name varchar(250) not null, class varchar(250) not null); 查看表结构 desc home; 修改表 添加字段：alter table home add gender enum('f','m'); 删除字段：alter table home drop gender; 修改字段： alter table home change name username varchar(100) after id; alter table home modify username varchar(100) first; 删除表 drop table home;

##### 前言：`insert, delete, update, select` #####

--------------------

*  **`insert`**

insert into home (class,username) values ('ops', '运维开发'), ('opsdev', '运维开发'), ('开发', 'java开发');

*  **`update`**

update home set class = '开发部门' where id = 1;

*  **`delete`**

delete from home where class = '开发';

*  **`select`**

查询表上的所有的数据
    select * from home; 查询部分数据 select id,class from home; # 还可以取个别名 select id as num,class from home; 使用where子句过滤 # 可以使用的算数运算符：>, < , >=, <=, ==, != # 可以使用连接词：and , or select * from home where id >= 2; select * from home where id <= 2 and id >1; select * from home where id between 1 and 2; # 可以使用like做模糊匹配（%：表示任意长度的字符，_：表示任意单个字符） select * from home where class like 'ops%'; # 可以使用null对值进行判断 select * from home where id is not null; select * from home where id is null; 使用order指定排序（默认是asc,升序排列） select * from home order by id desc;

##### 前言：`grant revoke` #####

--------------------

*  **`grant`**

1：先创建用户，再授权
    create user yhy@'172.16.19.%' identified by '123456'; grant all on *.* to yhy@'172.16.19.%'; flush privileges; 2：创建用户的同时给用户授权 grant all on *.* to yhy@'172.16.19.%' identified by '123456'; flush privileges; 3：给用户授予某些权限 show grants for yhy@'172.16.19.%';

*  **`revoke`**

revoke select ON *.* from yhy@'172.16.19.%'; flush privileges;

*  **`查看用户的权限`**

show grants for yhy@'172.16.19.%';

*  **`删除用户`**

delete from mysql.user where user = "yhy"; flush privileges;

> **` `**

转载于:https://www.cnblogs.com/liu1026/p/7467643.html