1. 库
1.1 增/创建
- 语句:
create database db_name;
- 使用
新增 "mlg"数据库
mysql> create database mlg;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mlg |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 扩展
注:每创建一个数据库,就相当于在数据库的数据存储路径下新创建了一个文件夹
1)ps -ef | grep
确定mysql服务的数据存储路径为/var/lib/mysql/
;
2)cd /var/lib/mysql/
切换到该路径下
3)ls
查看发现该路径下多了一个mlg
文件夹
1.2 查
- 语句
show databases;
- 使用
见 1.1 内容
1.3 用
- 语句
use db_name;
- 使用
mysql> use mlg;
Database changed
mysql>
- 扩展
注:使用一个库,就相当于切换到该数据库所对应的文件夹下
1.4 删
- 语句
drop database db_name;
- 使用
删除mlg数据库
mysql> drop database mlg;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
- 扩展
注:删除一个库,就相当于删除该数据库所对应的文件夹
2. 表
2.1 增
- 语句
create table table_name(字段名1 类型(长度) 约束,字段名2 类型(长度) 约束,...);
数据类型 与 约束 相关内容可查看以下博客:
mysql 数据类型与约束
- 使用
mysql> use mlg;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table score(id int(8),name char(20),num int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_mlg |
+---------------+
| score |
+---------------+
1 row in set (0.00 sec)
mysql>
2.2 查
- 语句
# 简单查看当前数据库下有哪些表
show tables;
# 查看某表的结构详情(直观):
desc table_name;
# 查看某表详情(带编码和存储引擎信息)
show create table table_name;
- 使用
mysql> show tables;
+---------------+
| Tables_in_mlg |
+---------------+
| score |
+---------------+
1 row in set (0.00 sec)
mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(8) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table score;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| score | CREATE TABLE `score` (
`id` int(8) DEFAULT NULL,
`name` char(20) DEFAULT NULL,
`num` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
- 扩展1
若通过 show create table score; 得到发现该表的编码不为utf8(如DEFAULT CHARSET=latin1
),则可能是默认编码没有更改导致;
1)可参考如下文章修改数据库的配置文件(永久性更改编码格式),然后删掉新建的表和库,重新创建即可
mysql的安装与初始操作(Linux版本)
2)也可通过以下方法在数据库中临时修改编码格式(重启mysql后会失效),建议在安装数据库的时候就在配置文件里指定数据库使用的编码格式
set character_set_server='utf8'
3)也可通过以下方法在建表的时候就指明编码格式。建议还是通过修改配置文件的方法
create table table_name(字段名1 类型(长度) 约束,字段名2 类型(长度) 约束,...) charset = utf8;
-
扩展2
到mlg数据库对应到目录下查看
cd /var/lib/mysql/mlg
然后 执行ll -ht
,结果如下
其中 scre.idb 存储的是inodb模式的数据;而score.frm存储的是框架与表结构信息 -
扩展3
查看各类搜索引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
transactions:事务
row-level locking:行级别锁
foreign keys:外键
以上三个概念,可查看其他对应的博客文章
2.3 改
即修改表结构
- 语句
alert
关键字 | 作用 |
---|---|
rename | 修改表名 |
add | 新增字段 |
change | 修改字段名称/数据类型/长度约束,并移动字段位置 |
drop | 删除一列 |
modify | 在不修改字段位置和名称的情况下,修改某字段的数据类型或者长度约束 |
alter table 表名 ...
# 具体用法如下
# rename: 修改表名
alter table 表名 rename 新表名;
# add: 新增字段
alter table 表名 add 字段名 数据类型 长度约束;
# change: 移动字段位置到第一列/修改字段名及其属性
alter table 表名 change 字段名 新字段名 数据类型 长度约束 first; # 把原字段变为新字段(新字段名及字段属性也是新定义的,可以与之前相同也可以不同),并且移动到第一列
# 移动字段位置到指定列
alter table 表名 change 字段名 新字段名 数据类型 长度约束 after 某字段名; #移动到“某字段名”后面
# drop: 删除一列
alter table 表名 drop 字段名;
# modify: 修改列的数据类型或者长度约束
alter 表名 表名 modify 字段名 数据类型 长度约束;
- 使用
1)rename 修该表名
mysql> show tables;
+---------------+
| Tables_in_mlg |
+---------------+
| score |
+---------------+
1 row in set (0.00 sec)
mysql> alter table score rename score_mlg;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_mlg |
+---------------+
| score_mlg |
+---------------+
1 row in set (0.00 sec)
mysql>
2)add 新增字段
mysql> desc score_mlg;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(8) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table score_mlg add id_auto int unique auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc score_mlg;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id | int(8) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
| id_auto | int(11) | NO | PRI | NULL | auto_increment |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
3)change 修改字段
a)字段名及字段属性均不变移动到第一列
mysql> alter table score_mlg change id_auto id_auto int unique auto_increment first;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc score_mlg;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id_auto | int(11) | NO | PRI | NULL | auto_increment |
| id | int(8) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
b)修改字段名及其属性
eg:修改id_auto字段为uid,且将其数据类型更改为tinyint
mysql> alter table score_mlg change id_auto uid tinyint unique auto_increment first;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc score_mlg;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| uid | tinyint(4) | NO | PRI | NULL | auto_increment |
| id | int(8) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
c)移动到指定位置
eg:将id字段移动到num字段后面
mysql> alter table score_mlg change id id int(8) after num;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc score_mlg;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| uid | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
| id | int(8) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
4)drop 删除字段/一列
删除uid这一列
mysql> alter table score_mlg drop uid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc score_mlg;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
| id | int(8) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
5)modify 修改列
mysql> alter table score_mlg modify name varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc score_mlg;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| num | int(4) | YES | | NULL | |
| id | int(8) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
2.4 删
- 语句
drop table table_name;
- 使用
mysql> show tables;
+---------------+
| Tables_in_mlg |
+---------------+
| score |
+---------------+
1 row in set (0.00 sec)
mysql> drop table score;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql>