MySQL 库、表的操作与使用

许锅锅

目录

• 数据库的编码集与校验集
  
• 表的基本结构
  
• 库的操作(DDL)
  
  
  
  • 创建数据库
    
    
    
    • 带字符集创建
      
    • 带校验集创建
      
    
    
  • 查看数据库
    
    
    
    • 查看自己正在使用的是哪一个数据库
      
    
    
  • 显示创建语句
    
  • 删除数据库
    
  • 使用数据库
    
  • 查看当前使用的数据库
    
  • 数据库备份与还原
    
    
    
    • 备份
      
    • 还原
      
    
    
  • 查看数据库连接数
    
  
  
• 表的操作(DDL)
  
  
  
  • 建表
    
  • 查看数据库中的表
    
  • 查看表结构
    
  • 查看表的创建信息
    
  • 修改表结构
    
  • 修改表名
    
  • 删除表
    
  
  

数据库的编码集与校验集

数据库编码集:数据库未来存储数据采用的编码集

编码集,即一套表示数据的规则,写入数据时,根据采用的字符集,写入对应规则的数据.
一般来说怎么存就要怎么取,即统一的编码集

数据库校验集:检测数据库存取比较时,是否使用同样的编码

取数相关操作需要加载到内存中,此时才会对数据进行校验

• 查看系统默认字符集以及校验规则
  

1. show variables like 'character_set_database';         ##字符集
2. show variables like 'collation_database';                                ##校验规则
3. show variables like 'character_set_%';                                        ##查看所有字符集相关
4. show variables like 'collation_%';                                                        ##查看所有校验规则相关

复制代码

• 查看数据库支持的字符集
  

1. show charset;

复制代码

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集或配置文件中的字符集：
例如配置文件my.cnf中设置了utf8，校验规则 是：utf8_ general_ ci
一般来说,使用了对应的校验码后,mysql会自动同步更新成对应的字符集,无需手动设置.(如果对应不上的话,则可以查表show charset自己修改正确)
表的基本结构

​                        列fields
行records
库的操作(DDL)

创建数据库

语法

1. CREATE DATABASE [IF NOT EXISTS] 数据库名 [create_specification [create_specification] ...]

复制代码

中括号[]围起来的表示可选选项

说明：

• 大写的表示关键字
  
• [] 是可选项
  
• CHARACTER SET: 指定数据库采用的字符集
  
• COLLATE: 指定数据库字符集的校验规则
  

带字符集创建

显式指明字符集时,会覆盖掉默认的字符集(就近原则)
未来在建表时,表的配置也同数据库的配置;

1. create database 数据库名 charset=utf8;
2. 或
3. create database 数据库名 charset utf8;                                ##省略=号
4. 或
5. create database 数据库名 character set=utf8;
6. 或
7. create database 数据库名 character set utf8;        ##省略=号

复制代码

带校验集创建

1. create database 数据库名 charset=utf8 collate utf8_general_ci;

复制代码

utf8_general_ci:不区分大小写
utf8_bin:区分大小写

注意:一般不支持指定存储引擎创建
验证:

1. mysql> create database test_db charset=utf8 collate=utf8_general_ci;
2. ## 切换至linux
3. [root@gz mysql]# cd test_db/
4. [root@gz test_db]# ls
5. db.opt
6. [root@gz test_db]# cat db.opt
7. default-character-set=utf8
8. default-collation=utf8_general_ci

复制代码

说明数据库默认只配置编码集和校验集
查看数据库

1. show databases;

复制代码

查看自己正在使用的是哪一个数据库

1. select database();

复制代码

显示创建语句

1. show create database 数据库名；

复制代码

结果

1. mysql> show create database helloworld;
2. +------------+-----------------------------------------------------------------------+
3. | Database   | Create Database                                                       |
4. +------------+-----------------------------------------------------------------------+
5. | helloworld | CREATE DATABASE `helloworld` /*!40100 DEFAULT CHARACTER SET utf8 */ |
6. +------------+-----------------------------------------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

说明：

• MySQL 建议我们关键字使用大写，但是不是必须的。
  
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
  
• /*!40100  default.... */这个不是注释，表示 如果当前mysql版本大于4.01版本，就执行这句话
  

删除数据库

1. DROP DATABASE [IF EXISTS] 数据库名;

复制代码

对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

使用数据库

语法

1. use 数据库名;

复制代码

use就像cd命令一样,需要使用某个目录(数据库)时,先cd进入这个目录 -- 库的行为与linux行为对应上

查看当前使用的数据库

1. select database();

复制代码

数据库备份与还原

备份

• 备份整个数据库
  

1. mysqldump  -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径(一般是.sql文件,相对路径)

复制代码

• 备份数据库其中的表
  

1. mysqldump -u root -p 数据库名 表名1 表名2  > ../mytest.sql                ## 注意,不带-B了

复制代码

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据
库，再使用source来还原。
-B选项会备份数据库创建语法

• 备份多个数据库
  

1. mysqldump -u root -p -B 数据库名1  数据库名2 ... > 数据库存放路径

复制代码

还原

• 还原
  

1. mysql> source 绝对路径

复制代码

查看数据库连接数

1. show processlist;

复制代码

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | test | Sleep   | 1386 |       | NULL             |
|  3 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
表的操作(DDL)

建表

语法

1. CREATE TABLE  user1 (
2.   id int(11) COMMENT '用户id',
3.   name varchar(32) COMMENT '用户名',
4.   password char(32) COMMENT '定长密码',
5.   birthday date COMMENT '生日'
6. ) ENGINE=MyISAM CHARSET=utf8;

复制代码

1. CREATE TABLE if not exists `user2` (
2.   `id` int(11) DEFAULT NULL COMMENT '用户id',
3.   `name` varchar(32) DEFAULT NULL COMMENT '用户名',
4.   `password` char(32) DEFAULT NULL COMMENT '定长密码',
5.   `birthday` date DEFAULT NULL COMMENT '生日'
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

• comment:建表语句注释
  

建表可以指定存储引擎,编码集,校验集
数据库是仓库(目录),表是货物(具体文件)

与linux对应操作

1. [root@gz d1]# ll
2. total 128
3. -rw-r----- 1 mysql mysql    61 Sep  4 11:14 db.opt                //数据库默认配置
5. -rw-r----- 1 mysql mysql  8697 Sep  4 11:19 user1.frm        //frm:表结构
6. -rw-r----- 1 mysql mysql     0 Sep  4 11:19 user1.MYD        //D:data        MYISAM的数据文件
7. -rw-r----- 1 mysql mysql  1024 Sep  4 11:19 user1.MYI        //I:index MYISAM的索引文件(数据与索引分开)
8. -rw-r----- 1 mysql mysql  8697 Sep  4 11:17 user2.frm        //frm:表结构
9. -rw-r----- 1 mysql mysql 98304 Sep  4 11:17 user2.ibd        //d:data         InnoDB的数据文件(数据和索引一起)

复制代码

查看数据库中的表

1. show tables;

复制代码

查看表结构

1. desc 表名;                        ##description:描述

复制代码

显示结构:
| 字段 | 字段数据类型 | 是否允许为空 | 键/索引类型 | 缺省值 | 扩充 |
示例

1. mysql> desc user1;
2. +----------+-------------+------+-----+---------+-------+
3. | Field    | Type        | Null | Key | Default | Extra |
4. +----------+-------------+------+-----+---------+-------+
5. | id       | int(11)     | YES  |     | NULL    |       |
6. | name     | varchar(32) | YES  |     | NULL    |       |
7. | password | char(32)    | YES  |     | NULL    |       |
8. | birthday | date        | YES  |     | NULL    |       |
9. +----------+-------------+------+-----+---------+-------+

复制代码

查看表的创建信息

1. show create table 表名;

复制代码

不一定和用户当时写的SQL一一对应,用户写的SQL会在MYSQL服务器词法语法分析器中重新解析、优化后执行,更加标准;因此返回的结果是标准SQL语句.

修改表结构

删除字段(列)只需要字段名,增加和修改操作还需要属性

1. 增加列
2. ALTER TABLE  tablename ADD (column datatype 字段属性...);
4. 修改列属性
5. ALTER TABLE  tablename MODIfy (column datatype 字段属性...);
7. 删除列
8. ALTER TABLE  tablename DROP (column);
10. 修改列名(相当于删除后添加,但是不会删除数据)
11. ALTER TABLE  tablename 列名 change 新列名 (字段属性等完整定义...)

复制代码

举例

• 添加一列用于存放图片路径
  1. alter table user1 add image_path varchar(128) comment '图片路径' after birthday;        ## 默认插入到最末;  after 列名 : 表示在哪列之后,

  复制代码
• 删除一列
  1. alter table user1 drop image_path;                                                # 只需要列名,该列所有数据也会被干掉

  复制代码
• 修改某列的属性
  1. alter table user1 modify name varchar(60);                 # 直接在字段后面加上属性,会覆盖掉所有旧属性

  复制代码
  1. mysql> show create table user1 \G;
  2. *************************** 1. row ***************************
  3.        Table: user1
  4. Create Table: CREATE TABLE `user1` (
  5.   `id` int(11) DEFAULT NULL COMMENT '用户id',
  6.   `name` varchar(60) DEFAULT NULL,                                                                                                ### 覆盖式修改
  7.   `password` char(32) DEFAULT NULL COMMENT '定长密码',
  8.   `birthday` date DEFAULT NULL COMMENT '生日',
  9.   `image_path` varchar(128) DEFAULT NULL COMMENT '图片路径'
  10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  11. 1 row in set (0.00 sec)

  复制代码

修改表名

1. alter table 表名 rename to 新表名;                                ## to可以省略

复制代码

或

1. rename table 表名 to 新表名;

复制代码

删除表

1. drop table 表名;

复制代码

来源:https://www.cnblogs.com/DSCL-ing/p/18425442
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！