目录
数据库的编码集与校验集
数据库编码集:数据库未来存储数据采用的编码集
编码集,即一套表示数据的规则,写入数据时,根据采用的字符集,写入对应规则的数据.
一般来说怎么存就要怎么取,即统一的编码集
数据库校验集:检测数据库存取比较时,是否使用同样的编码
取数相关操作需要加载到内存中,此时才会对数据进行校验
- 查看系统默认字符集以及校验规则
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 表名;