命令类型 | 作用 | 命令 |
基本 | 连接mysql服务器 | mysql -uroot -p |
退出mysql服务器 | exit | |
层次查看帮助 | 查询所有可供查询的分类 | ?/help contents; |
快速查看帮助 | 快速查阅某项语法 | ?/help name; |
DDL:数据定义语言 | ||
数据库 | 显示已有数据库 | show databases; |
创建数据库 | create database dbname; | |
删除数据库 | drop database dbname; | |
使用数据库 | use dbname; | |
表 | 显示已有表 | show tables; |
创建表 | create table tablename(column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, ... , column_name_n column_type_n constrains); | |
查看表定义 | desc tablename; | |
查看全面的表定义 | show create table tablename /G; | |
删除表 | drop table tablename; | |
修改表字段类型 | alter table tablename modify column_name column_type_new; | |
增加表字段 | alter table tablename add column_name_new column_type_new; | |
删除表字段 | alter table tablename drop column_name; | |
字段改名 | alter table tablename change column_name_old column_name_new column_type_new; | |
修改字段排列顺序 | add/chage/modify中都有可选项 first/after column_name 用于该表该字段的位置 | |
更改表名 | alter table talbename_old rename table_name_new; | |
DML:数据操纵语言 | ||
插入 | 插入记录 (支持插入多条记录) | insert into tablename (field1,field2,...,fieldn) values (record1_value1,value2,...,valuen), (record2_value1,value2,...,valuen), ... (recordn_value1,value2,...,valuen),; |
更新 | 更新单个表的记录 | update tablename set field1=value1,field2=value2,...,fieldn=valuen [where condition]; |
更新多个表的记录 | update tablename1 tablename2 .... tablenamen set t1.field1=expr1,t2.filed2=expr2,...,tn.fileldn=exprn [where condition]; | |
删除 | 删除单个表的记录 | delete from tablename [where condition]; |
删除多个表的记录 | delete t1,t2,...,tn from t1,t2,...,tn [where condition]; | |
查询 | 查询记录 | select * from tablename [where condition]; |
查询不重复记录 | select distinct * from tablename [where condition]; | |
排序 | select * from tablename [where condition] [order by field1 [desc|asc], field2 [desc|asc], ... , fieldn[desc|asc]]; | |
限制 (显示从offse+1开始的count条记录) | select * from tablename [where condition] [limit offset_start , row_count]; | |
聚合 having在聚合后, where在聚合前, fun_name: sum(field_name); count(field_num); max(field_name); min(field_name) | select [field1, field2, fieldn], fun_name from tablename [where condition] [group by field1, field2, ..., fieldn] [with rollup] [having where_condition]; | |
表连接 内连接:只显示互相匹配的记录; 外连接:显示所有记录; 左连接:包含所有左边表中有的记录; 右连接:包含所有右边表中有的记录; | select filed1, field2 from table1, table2 where table1.fieldx = table2.fieldx; select filed1, field2 from table1 [left/right] join table2 on table1.fieldx = table2.fieldx; | |
子查询 | in(); not in(); =; !=; exists; not exists; | |
联合 union 会将union后的结果进行一次去重; | select * from t1 union/union all select * from t2 ... union/union all select * from tn; | |
DCL:数据控制语言,DBA用来管理系统中的对象权限 |
mysql -- 基础 -- 常用命令
最新推荐文章于 2024-09-15 20:24:46 发布