库和表的操作

发表于

库和表的操作

库的操作

  • 创建数据库
1
create database 库名；

小tips：当创建数据库时没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_general_ci

字符集：utf8 为可变字节，从1到4个字节，3个字节代表中文

  • 创建使用utf8字符集的数据库，并带校验规则
1
create database 库名 charset=utf8 collate utf8_general_ci；

字符集和校验规则

  • 查看系统默认的字符集以及校验规则
1
2
show variables like 'character_set_database';
show variables like 'collation_database';
  • 查看数据库支持的字符集和字符校验规则
1
2
show charset;
show collation;

校验规则对数据库的影响

  • 不区分大小写：校验规则按照存表顺序

    1
    2
    校验规则使用utf8_general_ci [不区分大小写]
    create database 库名 collate utf8_general_ci;

  • 区分大小写：校验规则按照ASCII来排序

    1
    2
    校验规则使用utf8_bin [区分大小写]
    create database 库名 collate utf8_bin;

操纵数据库

  • 查看数据库是如何被创建出来的
1
show create database 库名;

案例：

1
2
3
4
5
6
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

说明：

  1. /!40100 DEFAULT CHARACTER SET utf8 / 这句话不是注释，它的意义是mysql当前版本大于4.01版本的话就会执行该语句，若小于则将其当做为注释。
  2. 数据库名字的反引号，是为了防止使用的数据库名刚好是关键字。

#####数据库修改

语法:

1
2
3
4
5
6
ALTER DATABASE db_name 
[alter_spacification [,alter_spacification]...]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name 
[DEFAULT] COLLATE collation_name

说明：

  • 对数据库的修改主要指是修改数据库的字符集和校验规则

    1
    2
    将数据库字符集改为 gbk
    alter database 库名 charset=gbk；
数据库删除

语法：

1
DROP DATABASE [IF EXISTS] db_ name;

删除库后的结果

  • 数据库内部看不到对应的数据库
  • 对应的数据库文件夹被删除，级联删除，里面的数据全部被删除

注意：不要随意删除数据库

恢复与备份
查看链接情况

语法：

1
show processlist;

实例：

1
2
3
4
5
6
mysql> show processlist;
+----+------+-----------------+-------+---------+------+-------+------------------+
| Id | User | Host            | db    | Command | Time | State | Info             |
+----+------+-----------------+-------+---------+------+-------+------------------+
|  1 | root | localhost:44862 | test2 | Query   |    0 | init  | show processlist |
+----+------+-----------------+-------+---------+------+-------+------------------+
  • 通过查看链接情况，可以得知有哪些用户链接到我们的数据库，如果查出某个用户不是你正常登录的，就有可能你的数据库被入侵了。如果发现自己的数据库比较慢时，可以用该指令来查看数据库连接情况。

表的操作

创建表

语法：

1
2
3
4
5
CREATE TABLE table_name (
    field1 datatype,    
    field2 datatype,    
    field3 datatype 
) character set 字符集 collate 校验规则 engine 存储引擎；

说明：

  • field 标识列名
  • datatype 标识列的数据类型
  • character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
  • collate 校验规则，如果没有指定校验规则，则以所载数据库的校验规则为准
  • engine 存储引擎，如果没有指定存储引擎，则默认使用Innodb存储引擎

创建表案例

1
2
3
4
5
6
create  table  users (
    id int,   
    name varchar(20) comment '用户名', 
    password char(32) comment '密码是32位的md5值', 
    birthday date comment '生日' 
) character set utf8 engine MyISAM；

说明：

  • 使用不同的存储引擎，创建的表的文件不一样

    例如使用MyISAM存储引擎创建表，在数据目录中就会有三个不同文件，分别是：

    • .frm：表结构
    • .MYD：表数据
    • .MYI：表引索

  • Innodb存储引擎中 .idb：表数据，相比于MyISAM存储引擎少了表引索

查看表结构

语法：

1
desc 表名；

实例：

1
2
3
4
5
6
7
mysql> desc person;          引索类型
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
字段名称 字段类型       是否允许为空  默认值     扩充

向表中插入数据：

1
insert into 表名 values(1,'a','b');

修改表

语法：

1
2
3
4
5
6
7
8
9
10
添加字段
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
修改字段
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);
删除字段
ALTER TABLE tablename DROP (column)
修改表明
ALTER TABLE 表明 rename to 新表明；
修改字段名
ALTER TAbLE 表明 change 字段名 新字段名 varchar(10);  --新字段需要完整定义

插入新字段后，对原来表中数据没有影响：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> alter table student add age int(3);
Query OK, 0 rows affected (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | int(11)    | YES  |     | NULL    |       |
| name   | varchar(8) | YES  |     | NULL    |       |
| gender | varchar(6) | YES  |     | NULL    |       |
| age    | int(3)     | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+

注意：删除字段是要小心，删除字段会将其对应的列数据全部删除

删除表

语法：

1
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

实例：

1
drop table 表名;

清空表的内容

语法：

1
truncate table 表名称;