数据库有哪些比较实用的SQL语句

这篇文章主要介绍“新手学习数据库有哪些比较实用的SQL语句”，有一些人在新手学习数据库有哪些比较实用的SQL语句的问题上存在疑惑，接下来小编就给大家来介绍一下相关的内容，希望对大家解答有帮助，有这个方面学习需要的朋友就继续往下看吧。

文章沿着设计一个假想的应用 awesome_app 为主线，从零创建修改数据库，表格，字段属性，索引，字符集，默认值，自增，增删改查，多表查询，内置函数等实用 SQL 语句。收藏此文，告别零散又低效地搜索经常使用的 SQL 语句。

1. 创建
1.1 创建数据库
语法：create database db_name

示例：创建应用数据库 awesome_app

create database `awesome_app`
1.2 创建表格
语法：create table table_name ( … columns )

示例：创建用户表 users

create table `users`
(
 `id` int,
 `name` char(10),
 `avatar` varchar(300),
 `regtime` date
)
1.3 创建索引
语法：create index index_name on table_name (column_name)

示例：为用户 id 创建索引 idx_id

create index `idx_id` on `users` (`id`)
/* 创建唯一索引 */
create unique index `idx_id` on `users` (`id`)
1.4 为已存在的列创建主键
更常用的方式是在创建表语句所有列定义的后面添加一行 primary key (column_name)。

语法：alter table table_name add primary key (column_name)

示例：将用户 id 设为主键

alter table users add primary key (`id`)
1.5 为已存在的列创建自增约束
更常用的方式是在创建表语句中添加自增列 id int not null auto_increment。

alter table `users` modify `id` int not null auto_increment

2. 修改
2.1 修改数据记录
语法：
update table_name set column=new_value where condition
update table_name set column1=new_value1,column2=new_value2,… wherecondition
示例：
update `users` set `regtime`=curdate() where `regtime` is null
/* 一次修改多列 */
update `users` set `name`='steven',`avatar`='http://cdn.awesome_app.com/path/to/xxx/steven.jpg' where `id`=1

2.2 修改数据库字符集为 utf8
alter database `awesome_app` default character set utf8

2.3 修改表字符集为 utf8
alter table `users` convert to character set utf8

2.4 修改表字段字符集为 utf8
alter table `users` modify `name` char(10) character set utf8

2.5 修改字段类型
alter table `users` modify `regtime` datetime not null

2.5 修改字段默认值
alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/* 设置默认为当前时间 current_timestamp，需要重新定义整个列 */
alter table `users` modify `regtime` datetime not null default current_timestamp

2.6 修改字段注释
alter table `users` modify `id` int not null auto_increment comment '用户ID';
alter table `users` modify `name` char(10) comment '用户名';
alter table `users` modify `avatar` varchar(300) comment '用户头像';
alter table `users` modify `regtime` datetime not null default current_timestamp comment '注册时间';
修改后，查看改动后的列：

mysql> show full columns from users;
+———+————–+—————–+——+—–+——————-+—————-+———————————+————–+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+———+————–+—————–+——+—–+——————-+—————-+———————————+————–+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 用户ID |
| name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户名 |
| avatar | varchar(300) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户头像 |
| regtime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | 注册时间 |
+———+————–+—————–+——+—–+——————-+—————-+———————————+————–+

3. 删除

3.1 删除数据记录
语法：delete from table_name where condition
示例：删除用户名未填写的用户
# 先增加一条用户名为空的用户
mysql> insert into `users` (`regtime`) values (curdate());
mysql> select * from users;
+—-+——–+—————————————————-+————+
| id | name | avatar | regtime |
+—-+——–+—————————————————-+————+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
| 3 | NULL | NULL | 2019-10-12 |
+—-+——–+—————————————————-+————+
# 删除用户名为空的行
mysql> delete from `users` where `name` is null;
mysql> select * from users;
+—-+——–+—————————————————-+————+
| id | name | avatar | regtime |
+—-+——–+—————————————————-+————+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
+—-+——–+—————————————————-+————+

3.2 删除数据库
drop database if exists `awesome_app`

3.3 删除表
drop table if exists `users`

3.4 清空表中所有数据
这个操作相当于先 drop table 再 create table ，因此需要有 drop 权限。
truncate table `users`

3.5 删除索引
drop index `idx_id` on `users`