本文整理自网络,侵删。
目录
- 前言
- 1. 创建
- 1.1 创建数据库
- 1.2 创建表格
- 1.3 创建索引
- 1.4 为已存在的列创建主键
- 1.5 为已存在的列创建自增约束
- 2. 插入
- 3. 修改
- 3.1 修改数据记录
- 3.2 修改数据库字符集为 utf8
- 3.3 修改表字符集为 utf8
- 3.4 修改表字段字符集为 utf8
- 3.5 修改字段类型
- 3.5 修改字段默认值
- 3.6 修改字段注释
- 4. 删除
- 4.1 删除数据记录
- 4.2 删除数据库
- 4.3 删除表
- 4.4 清空表中所有数据
- 4.5 删除索引
- 5. 查询
- 5.1 语法
- 5.2 单表查询
- 5.3 多表关联查询
前言
文章沿着设计一个假想的应用 awesome_app 为主线,从零创建修改数据库,表格,字段属性,索引,字符集,默认值,自增,增删改查,多表查询,内置函数等实用 SQL 语句。收藏此文,告别零散又低效地搜索经常使用的 SQL 语句。所有 SQL 都在 MySQL 下通过验证,可留着日后回顾参考,也可跟着动手一起做,如果未安装 MySQL 可参考 《macOS 安装 mysql》 (windows 安装大同小异)。
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. 插入
语法:
- insert into table_name values (value1, value2, …)
- insert into table_name (column1, column2, …) values (value1, value2, …)
示例:新增注册用户
insert into `users` values (1, 'ken', 'http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg', curdate()) /* 指定列插入 */ insert into `users` (`name`, `avatar`) values ('bill', 'http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg')
3. 修改
3.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
3.2 修改数据库字符集为 utf8
alter database `awesome_app` default character set utf8
3.3 修改表字符集为 utf8
alter table `users` convert to character set utf8
3.4 修改表字段字符集为 utf8
alter table `users` modify `name` char(10) character set utf8
3.5 修改字段类型
alter table `users` modify `regtime` datetime not null
3.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
3.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 | 注册时间 | +---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
4. 删除
4.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 | +----+--------+----------------------------------------------------+------------+
4.2 删除数据库
drop database if exists `awesome_app`
4.3 删除表
drop table if exists `users`
4.4 清空表中所有数据
这个操作相当于先 drop table 再 create table ,因此需要有 drop 权限。
truncate table `users`
4.5 删除索引
drop index `idx_id` on `users`
5. 查询
5.1 语法
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
5.2 单表查询
5.2.1 准备数据:
insert into users (`name`, `avatar`) values ('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'), ('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'), ('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg'), ('马六', 'http://cdn.awesome_app.com/path/to/xxx/6.jpg'), ('肖七', 'http://cdn.awesome_app.com/path/to/xxx/7.jpg'), ('刘八', 'http://cdn.awesome_app.com/path/to/xxx/8.jpg'), ('杨九', 'http://cdn.awesome_app.com/path/to/xxx/9.jpg'), ('郑十', 'http://cdn.awesome_app.com/path/to/xxx/10.jpg'); /* 增加重复行 */ insert into users (`name`, `avatar`) values ('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'), ('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'), ('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg');
5.2.2 查询所有列
mysql> select * from users; +----+--------+----------------------------------------------------+---------------------+ | id | name | avatar | regtime | +----+--------+----------------------------------------------------+---------------------+ | 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 00:00:00 | | 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 00:00:00 | | 3 | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 10:58:37 | | 4 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 10:58:37 | | 5 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 10:58:37 | | 6 | 马六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg | 2019-10-13 10:58:37 | | 7 | 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg | 2019-10-13 10:58:37 | | 8 | 刘八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg | 2019-10-13 10:58:37 | | 9 | 杨九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg | 2019-10-13 10:58:37 | | 10 | 郑十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg | 2019-10-13 10:58:37 | | 11 | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 11:20:17 | | 12 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 11:20:17 | | 13 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 11:20:17 | +----+--------+----------------------------------------------------+---------------------+
5.2.3 查询指定列
mysql> select id,name from users; +----+--------+ | id | name | +----+--------+ | 1 | steven | | 2 | bill | | 3 | 张三 | | 4 | 李四 | | 5 | 王五 | | 6 | 马六 | | 7 | 肖七 | | 8 | 刘八 | | 9 | 杨九 | | 10 | 郑十 | | 11 | 张三 | | 12 | 李四 | | 13 | 王五 | +----+--------+
5.2.4 查询不重复记录
mysql> select distinct name,avatar from users; +--------+----------------------------------------------------+ | name | avatar | +--------+----------------------------------------------------+ | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | | 马六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg | | 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg | | 刘八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg | | 杨九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg | | 郑十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg | +--------+----------------------------------------------------+
5.2.5 限制查询行数
相关阅读 >>
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » 程序员最实用的 SQL 语句收藏,看完这篇就够了
相关推荐
评论
管理员已关闭评论功能...