数据库命令介绍及用例

一、前言

数据库时按照数据结构来组织、存储和管理数据的仓库。数据库有多种类型，从简单的存储各种数据的表格到能够进行海量数据存储的大型数据库系统，这些应用都能看到数据库的身影。数据库所使用的语言通常被称为SQL语句，是一种特殊的编程语言，用于存取、查询数据或更新管理关系数据库系统。Linux系统支持安装及搭建数据库，常用的为：mysql-server(centos6)和mariadb-server（centos7），接着我们就来在Linux系统上了解学习下数据库的命令。

二、SQL

数据库的SQL语句可分为四种类型：DDL、DML、DCL、TCL。最近学习了前三种类型语句的使用，下面我们来看看这三种类型的语句的常见使用。

1、DDL

DDL，Data Definition Language 即数据库定义语言，用于定义数据库结构，其主要命令包括CREATE、ALTER、DROP等等。

  • CREATE：用于在数据库中创建对象，包括创建数据库，创建数据表、创建索引等等，其语法为：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)

其中create_defination包括:

字段：col_name  data_type
键：
PRIMARY KEY (col1, col2, ...)  ：主键，唯一地标识表中的某一条记录。
UNIQUE KEY  (col1, col2,...)：确保在非主键列中不输入重复的值。
FOREIGN KEY (column)：指向另一个表中的 PRIMARY KEY，用于关联数据表。
AUTO_INCREMENT：自增主键
索引：KEY|INDEX  [index_name]  (col1, col2,...)

而table_options则可以设置为：

ENGINE [=] engine_name

其常用用法有：

#创建一个名为charlie的数据库
MariaDB [(none)]> CREATE DATABASE charlie;

#创建一个名为students的数据表
MariaDB [charlie]> CREATE TABLE students (
    -> name VARCHAR(40),
    -> id INT(10),
    -> Address VARCHAR(40),
    -> Class INT(10),
    -> primary key(name)
    -> );

#复制表结构；
MariaDB [Market]> CREATE TABLE customers_copy LIKE customers_info;

#复制表数据；
CREATE TABLE TBL8 SELECT HOST,USER,PASSWORD FROM MYSQL.USER;                        
CREATE TABLE tbl_name () select from
  • ALTER：用于修改数据库结构，常用于在已有的表中添加、修改或删除列属性，其语法为：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

其中alter_specification内容包括:

添加字段：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]
删除字段：DROP  [COLUMN] col_name 
修改字段：
CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name] 
MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
                                
添加键：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,...)
删除键：
    主键：DROP PRIMARY KEY
    外键：DROP FOREIGN KEY fk_symbol
                                
添加索引：ADD {INDEX|KEY} [index_name]  (col1, col2,...)
删除索引：DROP {INDEX|KEY}  index_name
表选项：ENGINE [=] engine_name

常用用法有：

#在students表添加索引name和id
MariaDB [charlie]> ALTER TABLE students ADD INDEX(name,id);

#删除students表的Class字段
MariaDB [charlie]> ALTER TABLE students DROP Class;

#修改students表中的id字段为无符号的非空整型字符
MariaDB [charlie]> ALTER TABLE students MODIFY id int(10) UNSIGNED NOT NULL;
  • DROP：用于从数据库中删除对象，如删除数据库、删除数据表、删除索引等，其语法格式为：

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
DROP INDEX index_name ON tbl_name

常用用法为：

#删除数据表
MariaDB [charlie]> DROP TABLE students;

#删除数据库
MariaDB [mysql]> DROP DATABASE charlie;
2、DML

DML，Data Mainpulation Language 即数据库操作语言，用于处理数据库中的数据，主要命令有SELECT、INSERT、UPDATE、DELETE，对应着数据库的插入、删除、查询、更新几个功能。

  • SELECT
    其语法为：

1）SELECT * FROM tbl_name[,tbl_name_2]; 返回指定表的所有数据；慎用返回多个表的数据；
2）SELECT col1, col2, ... FROM tbl_name; 显示时，字段可以显示为别名，如col_name AS col_alias
3）SELECT col1, ... FROM tbl_name WHERE clause;
WHERE clause：用于指明挑选条件；如，age > 30;
(4) SELECT col1, ... FROM tbl_name [WHERE clause] ORDER BY col_name, col_name2, ... [ASC|DESC];
ASC: 升序排序；
DESC： 降序排序；

其常用用法包括：

#读取student表中的内容
MariaDB [test]> select * from student;
+---------+----+------+-------+
| name    | id | sex  | class |
+---------+----+------+-------+
| Alice   |  4 | F    |     1 |
| Anna    |  6 | F    |     2 |
| charlie |  1 | NULL |  NULL |
| Denny   |  7 | M    |     3 |
| jack    |  2 | NULL |  NULL |
| Mary    |  3 | F    |     6 |
| Tong    |  5 | M    |     2 |
+---------+----+------+-------+

#根据给定的条件读取数据表中的数据
MariaDB [test]> select name,id,sex,class from student where id>2;
+-------+----+------+-------+
| name  | id | sex  | class |
+-------+----+------+-------+
| Alice |  4 | F    |     1 |
| Anna  |  6 | F    |     2 |
| Denny |  7 | M    |     3 |
| Mary  |  3 | F    |     6 |
| Tong  |  5 | M    |     2 |
+-------+----+------+-------+

#将数据表中指定的字段排序输出
MariaDB [test]> select name as student_name,id from student order by id ASC;
+--------------+----+
| student_name | id |
+--------------+----+
| charlie      |  1 |
| jack         |  2 |
| Mary         |  3 |
| Alice        |  4 |
| Tong         |  5 |
| Anna         |  6 |
| Denny        |  7 |
+--------------+----+
  • INSERT
    其语法为：

INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...
其中val1的值的类型是字符型需用引号括起来，数值不能使用引号。
其常见用法如：

#往数据表student中插入name和id的值
MariaDB [test]> INSERT INTO student (name,id,sex,class) VALUE ("Denny",7,"M",3);

与INSERT类似的命令为REPLACE，用于替换表中的数据，若表中数据不存在，则插入指定的数据。

#将student表中jack的相关内容替换给定的内容
MariaDB [test]> REPLACE INTO student (name,id,sex,class) VALUE ("jack",2,"M",4);
  • DELETE
    其语法为：

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
(1) DELETE FROM tbl_name WHERE where_condition
(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]

其用法为：

#删除student表中id为1的行
MariaDB [test]> DELETE FROM student WHERE id=1;
  • UPDATE
    其语法为：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
注意：DELETE和UPDATE语句必须使用WHERE条件，不然影响范围非常大。

其常见用法为：

#将student表中name为Mary的行的class值修改为2
MariaDB [test]> UPDATE student SET class=2 WHERE name="Mary";
3、DCL

DCL，Data Control Language，即数据库控制语言，用于对数据库账号的授权、角色控制等操作。

  • GRANT
    若授权的用户不存在，GRANT可自动生成指定的授权用户，其语法为：

GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

priv_type： ALL [PRIVILEGES]
db_name.tbl_name：
*.*：所有库的所有表；
db_name.*：指定库的所有表；
db_name.tbl_name：指定库的特定表；
db_name.routine_name：指定库上的存储过程或存储函数；
[object_type]:TABLE、FUNCTION、PROCEDURE

注意：msql的用户账号由两部分组成：'USERNAME'@'HOST';其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；

常用用法：

#授权用户
MariaDB [test]> GRANT ALL ON test.* TO 'test'@'192.168.%.%' IDENTIFIED BY "magedu";
  • REVOKE
    用于撤销授权，其语法为：

REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';

常用用法：

#用于撤销指定用户的授权，需给定USER和HOST值
MariaDB [test]> REVOKE ALL ON test.* FROM 'test'@'192.168.%.%';

4、其他数据库命令

  • SHOW
    常用用法：
#显示数据库信息：
MariaDB [test]> show databases;
#显示给指定用户赋予的权限：
MariaDB [test]> show GRANTS FOR 'wpuser'@'192.168.%.%';
#查看表上的索引的信息
mysql> SHOW INDEXES FROM tbl_name;
#查看数据库支持的所有存储引擎类型：
mysql> SHOW  ENGINES;
#查看支持的所有字符集：
mysql>show character set;
#查看支持的所有排序规则：
mysql>show collation;
#显示指定的数据信息
MariaDB [(none)]> show databases like 'mysql';
#显示指定的数据表信息
MariaDB [test]> show table status like 'student'\G;
  • DESCRIBE
    用于显示数据表的列结构信息，其语法为：

{DESCRIBE | DESC} tbl_name [col_name | wild]

用法：

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
  • 刷新授权表
mysql>flush privileges;

三、综合案例

1、创建数据库Market，在Market中创建数据表customers，其结构如下图：


customers表结构

按要求完成以下操作：
1）创建数据库Market

MariaDB [(none)]> CREATE DATABASE Market;
Query OK, 1 row affected (0.00 sec)

2）创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束。

MariaDB [(none)]> use Market
Database changed
MariaDB [Market]> CREATE TABLE customers 
    -> (c_num INT(11) PRIMARY KEY NOT NULL UNIQUE KEY AUTO_INCREMENT,
    -> c_name VARCHAR(50),
    -> c_contact VARCHAR(50),
    -> c_city VARCHAR(50),
    -> c_birth DATETIME NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [Market]> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

3）将c_contact字段插入到c_birth字段后面；

MariaDB [Market]> ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;  #FIRST 表示放在前面，AFTER表示放在后面
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Market]> DESC customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

4）将c_name字段数据类型改为VARCHAR(70)。

MariaDB [Market]> ALTER TABLE customers MODIFY c_name VARCHAR(70);
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Market]> DESC customers;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| c_num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name  | varchar(70) | YES  |     | NULL    |                |
| c_city  | varchar(50) | YES  |     | NULL    |                |
| c_birth | datetime    | NO   |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

5）将c_contact字段名改为c_phone；

MariaDB [Market]> ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Market]> DESC customers;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| c_num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name  | varchar(50) | YES  |     | NULL    |                |
| c_city  | varchar(50) | YES  |     | NULL    |                |
| c_birth | datetime    | NO   |     | NULL    |                |
| c_phone | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

6）增加c_gender字段，数据类型为CHAR(1).

MariaDB [Market]> ALTER TABLE customers ADD c_gender CHAR(1);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Market]> DESC customers;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name   | varchar(70) | YES  |     | NULL    |                |
| c_city   | varchar(50) | YES  |     | NULL    |                |
| c_birth  | datetime    | NO   |     | NULL    |                |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

7）将表名修改为customers_info;

MariaDB [Market]> ALTER TABLE customers RENAME customers_info;
Query OK, 0 rows affected (0.00 sec)

MariaDB [Market]> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info   |
+------------------+
1 row in set (0.00 sec)

8）删除字段c_city；

MariaDB [Market]> ALTER TABLE customers_info DROP c_city;
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Market]> DESC customers_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name   | varchar(70) | YES  |     | NULL    |                |
| c_birth  | datetime    | NO   |     | NULL    |                |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

9）修改数据表的存储引擎为MyISAM。

MariaDB [Market]> ALTER TABLE customers_info ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [Market]> SHOW CREATE TABLE customers_info\G;
*************************** 1. row ***************************
       Table: customers_info
Create Table: CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(70) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_gender` char(1) DEFAULT NULL,
  PRIMARY KEY (`c_num`),
  UNIQUE KEY `c_num` (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,874评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,102评论 3赞 391
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,676评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,911评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,937评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,935评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,860评论 3赞 416
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,660评论 0赞 271
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,113评论 1赞 308
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,363评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,506评论 1赞 346
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,238评论 5赞 341
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,861评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,486评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,674评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,513评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,426评论 2赞 352

推荐阅读更多精彩内容

  • MySQL实战
    第1章 初识MySQL 启动MySQL服务 net start mysql(服务名) 关闭MySQL服务 net ...
    好记性不如烂Bettle阅读 643评论 0赞 3
  • MySQL数据库常用命令
    MySQL 数据库常用命令 1、MySQL常用命令 create database name; 创建数据库 use...
    55lover阅读 4,787评论 1赞 57
  • 2016.10.14
    一上午的课，早起吃的早餐再去上课的，也没在教室。吃完饭就去借仪器了，然后老师讲解了一下，我们自己就开始找...
    天空蓝上阅读 103评论 0赞 0
  • 第十三章 金融危机来袭 我却开单了(3)
    即开先例，来日方长.有了第一个客户迷你单的成交经验,我也进行了总结分析.是否公司主打产品竞争太激烈导致开发效果欠佳...
    敬业福阅读 342评论 0赞 1
  • 1973，泡腾片与Seven Stars
    一 长久以来我养成了一个习惯，我会把日常的一些琐事记下来，写成断断续续的流水账，发表在一些社交网站上，然后等某个百...
    周大大仙人阅读 223评论 0赞 0