# mysql 常用sql

mysql 的常用 sql 命令就是一般的增、删、查、改

## 数据库

**创建**

```sql
> create database db1 default charset utf8;
# 创建db1数据库并指定字符集utf8

> create database if not exists db1 default charset utf8 collate utf8_general_ci;
# if not exists 如果数据库不存在就创建
# collate utf8_general_ci : mysql的字符序遵从命名惯例。以_ci(表示大小写不敏感)，以_cs(表示大小写敏感)，以_bin(表示用编码值进行比较)
```

**删除**

```sql
> drop database db1;

> drop database if exists db1;
```

## 数据表

**创建**

```sql
> create table if not exists users (
    -> userID int(11) not null auto_increment,
    -> username varchar(64) default null,
    -> nickname varchar(32) unique default null,
    -> age int(11) default null,
    -> description varchar(256) default null,
    -> primary key (userID)
    -> ) engine=MyISAM;
```

命令解析：

* create table tbname (...) ：创建表unique 
* userID int(11) not null auto\_increment ：指定字段，`int(11)` 字段类型，`not null` 不为空，`auto_increment` 自增长，`unique` : 唯一字段 
* primary key (userID) ：指定主键
* engine=MyISAM ：表引擎

**查看表结构**

```sql
> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| userID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| username    | varchar(64)  | YES  |     | NULL    |                |
| nickname    | varchar(32)  | YES  |     | NULL    |                |
| age         | int(11)      | YES  |     | NULL    |                |
| description | varchar(256) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
```

**修改**

```sql
alter table test rename test1; # 修改表名

alter table test add column name varchar(10); # 添加表列

alter table test drop column name; # 删除表列

alter table test modify address varchar(10); # 修改表列类型
alter table test change address address varchar(40); 

alter table test change column address address1 varchar(30); # 修改表列名
```

**删除**

```sql
> drop table users;
> drop table if exists users;
```

## table的增删查改

**插入数据**

```sql
> insert into users(userID, username, nickname, age, description) 
    -> values(1, 'John', 'John', 22, 'a singer');

# 多行插入
> insert into users(userID, username, nickname, age, description) 
    -> values(2, 'xiaoming', '小明', 25, '我叫小明'), 
    -> (3, 'li', '李', 32, 'Hello World');
```

**修改数据**

```sql
> update users set age=24 where username='John';

> update users
    -> set
    -> age=24, username='John-' # 修改的内容
    -> where userID=1;          # 修改的条件
```

**查询数据**

```sql
> select * from users;
+--------+----------+----------+------+--------------+
| userID | username | nickname | age  | description  |
+--------+----------+----------+------+--------------+
|      1 | John-    | John     |   24 | a singer     |
|      2 | xiaoming | 小明     |   25 | 我叫小明     |
|      3 | li       | 李       |   32 | Hello World  |
+--------+----------+----------+------+--------------+

> select username, age from users; # 指定查询字段
+----------+------+
| username | age  |
+----------+------+
| John-    |   24 |
| xiaoming |   25 |
| li       |   32 |
+----------+------+

> select username, age from users\G; # 指定查询字段, 列显示

> select * from users where username like '%xiao%'; # 使用like匹配
```

**删除数据**

```sql
> delete from users where userID=3;

> truncate table dbname.table ; #清空某库下的某表
```