MySQL基础（二）

一、数据库操作

• 登录数据库
  mysql -h host -u username -p password
  
  输入密码，验证通过后：
  
• 创建数据库
  create database DB_NAME charset=utf8;
  
• 查看数据库
  show databases;
  
• 选择数据库
  use DB_NAME
  
• 删除数据库
  drop database DB_NAME;
  

二、数据表操作

1、创建表

create table [ if not exists ] table_name
(
col_name data_type col_constraint,
...
)

![](https://s4.51cto.com/images/blog/202009/21/dbd0dabfe51fbf80606627ec400f9a07.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

> 查看表结构
desc table_name;
![](https://s4.51cto.com/images/blog/202009/21/a2e108db98a24e1958e3e929295ff2d0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

> 查看详细表结构
show create table table_name \G
![](https://s4.51cto.com/images/blog/202009/21/1db1d6efb78ac0794bf510daa4583249.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

### 2、修改表
> 添加一列
alter table tbl_name add col_name dtype col_constraint;
修改某列
alter table tbl_name modify col_name dtype col_constraint;
删除某列
alter table tbl_name drop col_name;
修改列名
alter table tbl_name change old_col_name new_col_name dtype col_contraint;
重命名表
rename table old_tbl_name to new_tbl_name;
删除表
drop table tbl_name;

### 3、查询表
> 
查询表中所有数据
select * from table_name;
![](https://s4.51cto.com/images/blog/202009/21/207bf4d99652b4376f6f0269df078540.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

消除重复行
select distinct field_name from table_name;
![](https://s4.51cto.com/images/blog/202009/21/9a2997e45455cb30c7db49469c8446e0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

条件查询
// 查询id小于3的所有数据
![](https://s4.51cto.com/images/blog/202009/21/8508e93223654f0c7fbfc4c37864e9f2.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查询名字不是"xiaohong"的所有数据
![](https://s4.51cto.com/images/blog/202009/21/e5a853eefa0588366f21a10cb675cd84.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查看未删除的所有数据
![](https://s4.51cto.com/images/blog/202009/21/74cd0d93666dbfcf8ef62cb37c87139d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查看id大于3且性别为女
![](https://s4.51cto.com/images/blog/202009/21/329123ba44157ab68cf6cf3084146417.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查看id小于4或未删除
![](https://s4.51cto.com/images/blog/202009/21/eae432c02c9ee50f4cf50d3827d12026.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查看姓黄的所有数据
![](https://s4.51cto.com/images/blog/202009/21/bdc1626e7707b3fe6b451b9aab63d780.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// 查看姓黄且只有两个字的所有姓名
![](https://s4.51cto.com/images/blog/202009/21/67eec9033dd121054c2bcbf0eb7c9adf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

MySQL基础（二）

标签：ons   sts   条件   查询   char   基础   shadow   table   name