环境
本文基于环境 CentOS 7.6 X86_64
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@localhost ~]# uname -a
Linux localhost.localdomain 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Mysql 安装
CentOS 7.6 默认安装的是 mariadb ,如果没有安装,可以使用 yum 直接安装即可。
sudo yum install -y mariadb-server #安装数据库
systemctl enable mariadb #设置开机启动
systemctl start mariadb #开启数据库
ps -ef | grep mysql #查看数据库是否开启
/usr/bin/mysql < test.sql #将test.sql导入数据库
Mysql 常用高频语句
注意:
- 数据库命令行的最后是以
;
结尾的; - SQL 语句大小写是等价的;
- 下例子中均是在mariadb 自带的 test 数据库进行的操作。因此操作提示符中 MariaDB [
test
] 均显示为test
。
#登陆数据库,root账户登陆数据库
[root@localhost ~]# mysql -uroot -p
#显示数据库列表:
MariaDB [(none)] show databases;
#连接数据库、切换数据库
MariaDB [(none)] use <YourDatabaseName>;
#切换数据库
MariaDB [(mysql)] use <YourNewDatabaseName>;
#显示当前数据表项:
MariaDB [test]> show tables;
#显示表中的记录:
MariaDB [test]> select * from <YourTableName>
#显示数据表的结构:
MariaDB [test]> describe <YourTableName>;
MariaDB [test]> desc <YourTableName>; #可以简写
#建库 YourDatabaseName
MariaDB [test]> create database <YourDatabaseName>;
#建表 YourTableName
MariaDB [test]> create table <YourTableName> (columns sets);
#删YourDatabaseName库和删YourTableName表:
MariaDB [test]> drop database <YourDatabaseName;>
MariaDB [test]> drop table <YourTableName>;
#将表中记录清空:
MariaDB [test]> delete from <YourTableName>; (内容清空,自增id不会被清掉,自增id会保留)
库的基本操作
#创建数据库:
MariaDB [(none)] create database <YourDatabaseName>;
#连接数据库:
MariaDB [(none)] use <YourDatabaseName>;
#查看当前使用的数据库:
MariaDB [test]> select database();
#显示当前数据库包含的表项:
MariaDB [test]> show tables;
#删除数据库:
MariaDB [test]> drop database <YourDatabaseName>;
举例子:
#1、查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.09 sec)
#2、连接数据库
MariaDB [(none)]> use test;
Database changed
#3、查看当前使用的数据库
MariaDB [test]> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
#4、显示当前数据库包含的表项
MariaDB [test]> show tables;
Empty set (0.00 sec)
表的基本操作
建表
#创建表:
MariaDB [test]> create table <YourTableName> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);
举例子:
假设要创建下面一张表,名称为 birds:
#创建一个表 birds:
MariaDB [(none)]> create table if not exists `birds` (
bird_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
scientific_name VARCHAR(255) UNIQUE,
common_name VARCHAR(50),
family_id INT,
description TEXT);
其他举例:
MariaDB [(none)]> create table YourTableName(
id int(4) not null(不能为空) primary key(主键) auto_increment(自增长),
name varchar(25) not null,
age int (4) not null default'0'); (default'0' 设置默认值为0)
获取表结构
#获取表结构信息
MariaDB [(none)]> desc <YourTableName>; #更常用,推荐;
MariaDB [(none)]> show columns from <fromYourTableName>;
举例子:
#查询表结构
MariaDB [test]> desc birds;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| bird_id | int(11) | NO | PRI | NULL | auto_increment |
| scientific_name | varchar(255) | YES | UNI | NULL | |
| common_name | varchar(50) | YES | | NULL | |
| family_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
查询表中的数据
#查询表中所有内容
MariaDB [test]> select * from <YourTableName>;
查询指定行数据举例子:
#1、查看表中前 3 行数据
MariaDB [test]> select * from birds limit 0,3;
+---------+----------------------+---------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+----------------------+---------------------+-----------+-------------+
| 1 | Charadrius vociferus | Killdeer | NULL | NULL |
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
+---------+----------------------+---------------------+-----------+-------------+
3 rows in set (0.00 sec)
#2、或者使用 order by 方式
MariaDB [test]> select * from birds order by bird_id limit 0,3; (order by bird_id :是以bird_id 排序)
+---------+----------------------+---------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+----------------------+---------------------+-----------+-------------+
| 1 | Charadrius vociferus | Killdeer | NULL | NULL |
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
+---------+----------------------+---------------------+-----------+-------------+
3 rows in set (0.00 sec)
插入数据
#表结构信息插入有效信息
MariaDB [test]> insert into <YourTableName> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]
举例子:
#例子birds:
#1、先查询是否为空
MariaDB [test]> select * from birds;
Empty set (0.00 sec)
#2、向表中添加数据
MariaDB [test]> insert into birds (scientific_name, common_name)
VALUES ('Charadrius vociferus', 'Killdeer'),
('Gavia immer', 'Great Northern Loon'),
('Aix sponsa', 'Wood Duck'),
('Chordeiles minor', 'Common Nighthawk'),
('Sitta carolinensis', 'White-breasted Nuthatch'),
('Apteryx mantelli', 'North Island Brown Kiwi');
#3、再次查询是否添加成功
MariaDB [test]> select * from birds;
+---------+----------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+----------------------+--------------------------+-----------+-------------+
| 1 | Charadrius vociferus | Killdeer | NULL | NULL |
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Apteryx mantelli | North Island Brown Kiwi | NULL | NULL |
+---------+----------------------+--------------------------+-----------+-------------+
6 rows in set (0.00 sec)
删除表中数据
#删除表中数据:
MariaDB [(none)]> delete from <YourTableName> where 表达式
举例子:删除表 birds 中 bird_id 为 1 的记录
#1、首先查询现有数据:
MariaDB [test]> select * from birds;
+---------+----------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+----------------------+--------------------------+-----------+-------------+
| 1 | Charadrius vociferus | Killdeer | NULL | NULL |
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Apteryx mantelli | North Island Brown Kiwi | NULL | NULL |
+---------+----------------------+--------------------------+-----------+-------------+
6 rows in set (0.00 sec)
#2、删除表中bird_id为 1 的数据
MariaDB [test]> delete from birds where bird_id=1;
Query OK, 1 row affected (0.00 sec)
#3、再次查询修改的结果
MariaDB [test]> select * from birds;
+---------+--------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+--------------------+--------------------------+-----------+-------------+
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Apteryx mantelli | North Island Brown Kiwi | NULL | NULL |
+---------+--------------------+--------------------------+-----------+-------------+
5 rows in set (0.00 sec)
修改表中数据
#修改表中数据:
MariaDB [(none)]> update <YourTableName> set 字段=新值,... where 条件
举例子:修改表中 bird_id=6 的数据
#1、首先查询现有数据(bird_id=1已经删除过):
MariaDB [test]> select * from birds;
+---------+--------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+--------------------+--------------------------+-----------+-------------+
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Apteryx mantelli | North Island Brown Kiwi | NULL | NULL |
+---------+--------------------+--------------------------+-----------+-------------+
5 rows in set (0.01 sec)
#2、修改表中 bird_id 为 6 的数据
MariaDB [test]> update birds set scientific_name='Richard Phillips Feynman' where bird_id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#3、再次查询修改的结果
MariaDB [test]> select * from birds;
+---------+--------------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+--------------------------+--------------------------+-----------+-------------+
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Richard Phillips Feynman | North Island Brown Kiwi | NULL | NULL |
+---------+--------------------------+--------------------------+-----------+-------------+
5 rows in set (0.00 sec)
修改表中字段
#增加字段 add
MariaDB [(none)]> alter table <YourTableName> add column 字段 类型 其他;
#删除字段 drop
MariaDB [(none)]> alter table <YourTableName> drop column 字段;
举例子:
#1、查询表中现有结构
MariaDB [test]> select * from birds;
+---------+--------------------------+--------------------------+-----------+-------------+
| bird_id | scientific_name | common_name | family_id | description |
+---------+--------------------------+--------------------------+-----------+-------------+
| 2 | Gavia immer | Great Northern Loon | NULL | NULL |
| 3 | Aix sponsa | Wood Duck | NULL | NULL |
| 4 | Chordeiles minor | Common Nighthawk | NULL | NULL |
| 5 | Sitta carolinensis | White-breasted Nuthatch | NULL | NULL |
| 6 | Richard Phillips Feynman | North Island Brown Kiwi | NULL | NULL |
+---------+--------------------------+--------------------------+-----------+-------------+
5 rows in set (0.00 sec)
#2、在表 birds 中添加了一个字段 wing_id, 类型为 char(2), 默认值为 0
MariaDB [test]> alter table birds add column wing_id CHAR(2) after family_id;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
#3、在表 birds 中删除 common_name 字段
MariaDB [test]> alter table birds drop column common_name;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
#4、再次检查表中增加字段结果
MariaDB [test]> select * from birds;
+---------+--------------------------+-----------+---------+-------------+
| bird_id | scientific_name | family_id | wing_id | description |
+---------+--------------------------+-----------+---------+-------------+
| 2 | Gavia immer | NULL | NULL | NULL |
| 3 | Aix sponsa | NULL | NULL | NULL |
| 4 | Chordeiles minor | NULL | NULL | NULL |
| 5 | Sitta carolinensis | NULL | NULL | NULL |
| 6 | Richard Phillips Feynman | NULL | NULL | NULL |
+---------+--------------------------+-----------+---------+-------------+
5 rows in set (0.01 sec)
#5、再次检查现有表结构
MariaDB [test]> desc birds;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| bird_id | int(11) | NO | PRI | NULL | auto_increment |
| scientific_name | varchar(255) | YES | UNI | NULL | |
| family_id | int(11) | YES | | NULL | |
| wing_id | char(2) | YES | | NULL | |
| description | text | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
更改表名
#更改表名:
MariaDB [(none)]> rename table <YourOldTableName> to <YourNewTableName> ;
举例子:
#举例子:将表 birds 名字更改为 myTestBirds
#1、检查当前表的名字是 birds:
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| birds |
+----------------+
1 row in set (0.00 sec)
#2、修改当前表的名字为 myTestBirds:
MariaDB [(none)]> rename table birds to myTestBirds;
#3、再次检查修改后结果
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| myTestBirds |
+----------------+
1 row in set (0.00 sec)
删除表
#删除表
MariaDB [(none)]> drop table <YourTableName>
举例子:
#1、查看现有表,前面修改过名字为 myTestBirds
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| myTestBirds |
+----------------+
1 row in set (0.00 sec)
#2、删除表名为 myTestBirds 的表
MariaDB [test]> drop table myTestBirds;
Query OK, 0 rows affected (0.00 sec)
#3、再次检查修改后结果
MariaDB [test]> show tables;
Empty set (0.00 sec)
Reference
《Learning MySQL and MariaDB》