楠枫渡

Thinking && coding

24
文章
13
分类
28
标签

MySQL基础知识总结

一、mysql数据库查看相关信息的语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 查看数据库的版本号
SELECT VERSION();
-- 产看当前用户
SELECT USER(),CURRENT_USER();
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 查看当前日期
SELECT CURDATE(),CURRENT_DATE();
-- 查看当前的时间
SELECT CURTIME(),CURRENT_TIME();
-- 查看当前的时间戳
SELECT NOW(),CURRENT_TIMESTAMP();
-- 查看mysql支持的所有的字符编码
SHOW CHARSET;
-- 查看以s开头的字符编码
SHOW CHARSET LIKE 's%'; 
-- 查看变量名为char开头的变量
SHOW VARIABLES LIKE 'char%';
-- 查看数据库支持的所有引擎
SHOW ENGINES;
-- 查看所有的数据库
SHOW DATABASES;
-- 查看所有的表
SHOW TABLES;
-- 查看标准建库语句
SHOW CREATE DATABASE tableName;
-- 查看标准的建表语句
SHOW CREATE TABLE tableName;
-- 查看表的所有字段信息
SHOW FULL COLUMNS FROM tableName;
-- 查看表结构[DESCRIBE的缩写:DESC
DESC tableName;

二、数据库相关操作

1
2
3
4
5
6
7
8
9
10
11
-- 创建数据库，名字可以是关键字.主要作用:增强兼容性
CREATE DATABASE databaseName;
CREATE DATABASE databaseName;
CREATE DATABASE IF NOT EXISTS databaseName DEFAULT CHARSET=utf8;
CREATE DATABASE databaseName /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE databaseName /*!40100 DEFAULT CHARACTER SET gbk */;
CREATE DATABASE databaseName /*!40100 DEFAULT CHARACTER SET gb2312 */;
-- 选择数据库
USE databaseName;
-- 删除数据库
DROP DATABASE IF EXISTS databaseName;

三、数据表相关操纵

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 标准建表语句
CREATE TABLE IF NOT EXISTS tableName(
    列名，类型，修饰，约束
    id INT(10) UNSIGNED ZEROFILL PRIMARY KEY AUTO_INCREMENT
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 快速建表，以表名为tableName的表为模板建立相同结构相同约束的表，无数据
CREATE TABLE tableName2 LIKE tableName;
-- 快速建表 以tableName查询的数据新建一个表，没有约束
CREATE TABLE aa SELECT * FROM tableName;
-- 删除一个表
DROP TABLE IF EXISTS tableName;
-- 删除多个表
DROP TABLE IF EXISTS tableName1,tableName2,tableName3;
-- 修改表名称
RENAME TABLE tableName1 TO tableName2;
ALTER TABLE tableName1 RENAME tableName2;
ALTER TABLE tableName1 RENAME TO tableName2;
-- 修改表的列名和类型
ALTER TABLE tableName CHANGE columnName columnName2 VARCHAR(20);
-- 删除主键(前提是主键没有自增字段，若主键自增先去除auto_increment)
ALTER TABLE tableName DROP PRIMARY KEY;
-- 添加主键
ALTER TABLE tableName ADD CONSTRAINT pk PRIMARY KEY(columnName);
-- 新增字段
ALTER TABLE tableName ADD COLUMN columnName;
ALTER TABLE tableName ADD COLUMN columnName1 AFTER columnName2;
ALTER TABLE tableName ADD COLUMN columnName FIRST;
-- 删除字段
ALTER TABLE tableName DROP COLUMN columnName;
-- 添加外键约束(字段必须存在，且类型和修饰相同)
ALTER TABLE tableName ADD CONSTRAINT fk FOREIGN KEY (columnName) REFERENCES tec(columnName);
-- 删除外键关联时，相关的内容设为空
ALTER TABLE tableName ADD CONSTRAINT fk FOREIGN KEY (columnName) REFERENCES tec(columnName) ON DELETE SET NULL;
-- 删除外键关联时，删除相关的数据
ALTER TABLE tableName ADD CONSTRAINT fk FOREIGN KEY (columnName) REFERENCES tec(columnName) ON DELETE CASCADE;
-- 更新外键关联时，更新相关的内容
ALTER TABLE tableName ADD CONSTRAINT fk FOREIGN KEY (columnName) REFERENCES tec(columnName) ON UPDATE CASCADE;
-- 删除设空，修改更新
ALTER TABLE tableName ADD CONSTRAINT fk FOREIGN KEY (columnName) REFERENCES tec(columnName) ON DELETE SET NULL ON UPDATE CASCADE;

/****
上面的ON DELETE SET NULL：外键设为空
上面的ON DELETE/UPDATE CASCADE：外键级联
注意：使用外键约束时，数据表的存储引擎为INNODB
****/
-- 删除外键
ALTER TABLE tableName DROP FOREIGN KEY fk;
-- 截断表内数据，重新插入主键从开始自增
TRUNCATE tableName

四、索引相关操作

1
2
3
4
5
6
7
-- 一般情况下，建立表时如果建立pk(主键)、fk(外键)、uk(唯一约束键) 数据库系统会自动的建立索引，索引的作用：数据量很大时可以提高检索速度
-- 创建索引
CREATE INDEX indexName ON tableName(columnName DESC);
ALTER TABLE tableName ADD INDEX indexName(columnName DESC);
-- 删除索引
DROP INDEX indexName ON tableName;
ALTER TABLE tableName DROP INDEX indexName;

五、MySQL的常用数据类型

数值类型

  • BIT                      // 位类型(只能存0和1)
  • TINYINT              // 短整型 范围为有符号数：-128~127，无符号数：0~255
  • SMALLINT          // 小整型 范围为有符号数：-32768~32767，无符号数：0~65535
  • MEDIUMINT       // 中整型 范围为有符号数：-8388608~8388607，无符号数：0~16777215
  • INT                     // 整型 范围为有符号数：-2147483648~2147483647，无符号数：0~4294967295
  • BIGINT               // 大整型 范围为有符号数：-9223372036854775808~9223372036854775807，无符号数：0~18446744073709551615

精度小数位类型

  • NUMERIC(p,s)    // p代表数值总长度，s表示保留的小数位，若p=s，则只能存小于1的小树
  • DECIMAL(p,s)    // p代表数值总长度，s表示保留的小数位，若p=s，则只能存小于1的小树

字符类型

  • CHAR(n)            // 定长字符串，n<=255，可以保存n个字符
  • VARCHAR(n)     // 变长字符串,n<=65535
  • TEXT                 // 文本类型，最大存储65535个字符
  • MEDIUMTEXT  // 中等文本类型，可以存储16777215个字符
  • LONGTEXT      // 长文本类型最大可以存4GB
  • ENUM              // 枚举类型取其中一个

二进制文件类型

  • LONGBLOB  //主要存储二进制文件，最大可以是4G

日期时间类型

  • DATE             // 日期类型 只能保存 年月日 非法的为:0000-00-00
  • DATETIME    // 日期时间类型 保存年月日 时分秒
  • TIME             // 时间类型 保存 18:30:35 时分秒
  • TIMESTAMP //时间戳类型

六、约束关键字

  • NOT NULL        // 非空约束
  • DEFAULT          // 默认值约束，若指定值，则不再采用默认值
  • ENUM(1,2)        // 枚举，只能去里面的值或null，不能使其他值
  • PRIMARY KEY // 主键，在一个表内主键有且只能有一个，不能重复
  • FOREIGN KEY // 外键约束,两个表存储引擎采用InnoDB，并且一个表内的外键是另一个表的主键或唯一约束键
  • UNIQUE KEY  // 唯一约束