MySQL 库、表的操作与使用

数据库的编码集与校验集

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

编码集,即一套表示数据的规则,写入数据时,根据采用的字符集,写入对应规则的数据.

一般来说怎么存就要怎么取,即统一的编码集

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

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

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

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

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

show charset;

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集或配置文件中的字符集：

例如配置文件my.cnf中设置了utf8，校验规则 是：utf8_ general_ ci

一般来说,使用了对应的校验码后,mysql会自动同步更新成对应的字符集,无需手动设置.(如果对应不上的话,则可以查表show charset自己修改正确)

表的基本结构

​ 列fields

行records

库的操作(DDL)

创建数据库

语法

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

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

说明：

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

带字符集创建

显式指明字符集时,会覆盖掉默认的字符集(就近原则)

未来在建表时,表的配置也同数据库的配置;

create database 数据库名 charset=utf8;
或
create database 数据库名 charset utf8;				##省略=号
或
create database 数据库名 character set=utf8;
或
create database 数据库名 character set utf8;	##省略=号

带校验集创建

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

utf8_general_ci:不区分大小写

utf8_bin:区分大小写

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

验证:

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

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

查看数据库

show databases;

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

select database();

显示创建语句

show create database 数据库名；

结果

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

说明：

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

删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

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

使用数据库

语法

use 数据库名;

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

查看当前使用的数据库

select database();

数据库备份与还原

备份

• 备份整个数据库

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

• 备份数据库其中的表

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

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

-B选项会备份数据库创建语法

• 备份多个数据库

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

还原

• 还原

mysql> source 绝对路径

查看数据库连接数

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)

建表

语法

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

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

• comment:建表语句注释

建表可以指定存储引擎,编码集,校验集

数据库是仓库(目录),表是货物(具体文件)

与linux对应操作

[root@gz d1]# ll
total 128
-rw-r----- 1 mysql mysql    61 Sep  4 11:14 db.opt		//数据库默认配置

-rw-r----- 1 mysql mysql  8697 Sep  4 11:19 user1.frm	//frm:表结构
-rw-r----- 1 mysql mysql     0 Sep  4 11:19 user1.MYD	//D:data	MYISAM的数据文件
-rw-r----- 1 mysql mysql  1024 Sep  4 11:19 user1.MYI	//I:index MYISAM的索引文件(数据与索引分开)
-rw-r----- 1 mysql mysql  8697 Sep  4 11:17 user2.frm	//frm:表结构
-rw-r----- 1 mysql mysql 98304 Sep  4 11:17 user2.ibd	//d:data 	InnoDB的数据文件(数据和索引一起)

查看数据库中的表

show tables;

查看表结构

desc 表名;			##description:描述

显示结构:

| 字段 | 字段数据类型 | 是否允许为空 | 键/索引类型 | 缺省值 | 扩充 |

示例

mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(32) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

查看表的创建信息

show create table 表名;

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

修改表结构

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

增加列
ALTER TABLE  tablename ADD (column datatype 字段属性...);

修改列属性
ALTER TABLE  tablename MODIfy (column datatype 字段属性...);

删除列
ALTER TABLE  tablename DROP (column);

修改列名(相当于删除后添加,但是不会删除数据)
ALTER TABLE  tablename 列名 change 新列名 (字段属性等完整定义...)

举例

• 添加一列用于存放图片路径

  alter table user1 add image_path varchar(128) comment '图片路径' after birthday;	## 默认插入到最末;  after 列名 : 表示在哪列之后,
  

• 删除一列

  alter table user1 drop image_path;						# 只需要列名,该列所有数据也会被干掉
  

• 修改某列的属性

  alter table user1 modify name varchar(60); 		# 直接在字段后面加上属性,会覆盖掉所有旧属性
  

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

修改表名

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

或

rename table 表名 to 新表名;

删除表

drop table 表名;