连接MySQL数据库:
mysql -h hostname -u username -p
,其中mysql为登录命令,-h后的参数代表服务器主机地址,-u后的参数代表登录数据库的用户名称,-p后的参数代表登录密码。
设置数据库密码:
set password for 用户名@localhost = password('新密码');
查看数据库:
SHOW DATABASES;
SQL语句不区分大小写,但最好大写。
创建数据库:
CREATE DATABASE database_name;
删除数据库:
DROP DATABASE database_name;
SHOW CREATE DATABASE声明查看数据库的定义,可以发现数据库test已经不存在了。
MySQL储存引擎:
数据库储存引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、更新和删除数据操作,MySQL的核心就是储存引擎。
MySQL提供了不同的储存引擎,包括处理事务安全表的引擎和储存非事务安全表的引擎。MySQL中不需要整个服务器中使用同一种储存引擎,可以根据情况对每一个表使用不同的存储引擎。使用SHOW ENGINES;
可以查看MySQL支持的所有的引擎类型。想要格式化查看可使用SHOW ENGINES\G;
MySQL5.5.5版本之前默认的储存引擎是MyISAM,5.5.5版本之后使用的是InnoDB。Support表示该数据库是否可用,YES表示可以使用,NO表示不可用,DEFAULT表示默认存储引擎。
MySQL提供了那么多的储存引擎,那我们应该如何选择呢?如果要提供提交、回滚和崩溃恢复能力的事务安全(ACID兼容)能力,并要求实现并发控制,可以使用InnoDB;如果数据表主要用来插入和查询记录,则MyISAM可以提供较高的处理效率;如果只是临时存放数据,数据量不大,且不需要较高的数据安全性,可以选择Memory引擎,Memory引擎将数据存储在内存中,但mysqld守护进程崩溃时数据会丢失;如果只有INSERT和SELECT操作,可以选择Archive引擎,Archive引擎支持高并发的插入操作,但本事并不是事务安全的。
查看数据表使用的存储引擎:SHOW CREATE TABLE table_name\G;
修改数据表的储存引擎:ALTER TABLE table_name ENGINE=储存引擎
不同的MySQL版本使用的默认储存引擎不同,可以使用SELECT @@default_storage_engine;
查看数据库使用的默认储存引擎
创建数据表:
CREATE TABLE <表名>(
字段名1 数据类型 [列级别约束条件][默认值],
字段名2 数据类型 [列级别约束条件][默认值],
......
[表级别约束条件]
);
查看数据表基本结构:
DESCRIBE/DESC 表名;
修改表名:
ALTER TABLE <旧表名> RENAME <新表名>;
删除数据表:
DROP TABLE [IF EXISTS] 表1,表2,...;
加上IF EXISTS可用于删除表前判断表是否存在,不存在sql语句也可以继续执行,只是会发出警告。
主键约束:
主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空。主键能够唯一的标识表中的一条记录,可以结合外键来定义数据表之间的关系,并且加快数据库查询的条件。主键分为单字段主键和多字段联合主键。
单字段主键有两种创建方式:
(1)、定义列的同时指定主键:字段名 数据类型 PRIMARY KEY [默认值]
(2)、定义完所有列后指定主键:CONSTRAINT <约束名> PRIMARY KEY [字段名]
为已存在的表添加主键约束:
ALTER TABLE <表名> ADD PRIMARY KEY(字段名);
多字段联合主键:
主键由多个字段联合组成,语法规则PRIMARY KEY [字段1,字段2,...];
外键约束:
外键用于在两个表之间建立连接,可以是一列或者多列,一个表可以有一个或多个外键,一个表的外键可以为空值,若不是空值,则每一个外键值必须等于另一个表中的主键的某一个值。外键的作用是保持数据的一致性、完整性,一个表设置了外键后,则另一个表中的相关联字段不可删除,想要删除必须先删除外键约束。
创建外键语法:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1[字段名2,...] REFERENCES <主表名> 主键列1[,主键列2,...]
例如创建手机号码表关联用户表主键:
为已存在的表添加外键约束:
ALTER TABLE <外键所在的表名> ADD CONSTRAINT <外键名> FOREIGN KEY(外键所在的列名) REFERENCES <对应主键所在表名(主键所在列名)>;
注意:外键约束不能跨引擎使用!
非空约束:
非空约束(Not Null Constraint)指字段的值不能为空,语法如下:
字段名 数据类型 not null
为已存在的表添加非空约束:
ALTER TABLE <表名> MODIFY <字段名> <字段类型> NOT NULL;
唯一性约束:
唯一性约束(Unique Constraint)要求该列唯一,允许为空,但只能出现一个空值。唯一性约束可以确保一列或者几列不出现重复值。
唯一性约束同样有两种创建方式,可以定义完列后直接创建:字段名 数据类型 UNIQUE
,也可以定义完列后指定唯一约束:[CONSTRAINT <约束名>] UNIQUE(<字段名>)
。
为已存在的表添加唯一性约束:
ALTER TABLE <表名> ADD UNIQUE(字段1,字段2,...);
UNIQUE和PRIMARY KEY区别:一个表中可以有多个字段声明为UNIQUE,但只能有一个PRIMARY KEY声明;声明为PRIMARY KEY 的列不允许有空值,声明为UNIQUE的字段可以有空值。
默认约束:
默认约束(Default Constraint)指定某列的默认值,语法规则如下:
字段名 数据类型 DEFAULT 默认值
为已存在的表添加默认约束:
ALTER TABLE <表名> ALTER <字段名> SET DEFAULT <默认值>;
主键自增长:
设置主键后一般会希望更新数据时主键可以自动增加,可以通过为表主键添加AUTO_INCREMENT关键字来实现,MySQL中AUTO_INCREMENT默认初始值为1,每增加一条记录,字段自动加1,一个表只能有一个字段使用AUTO_INCREMENT约束,且该字段必须为主键的一部分。AUTO_INCREMENT只能约束整型类型(TINYINT、SMALLIN、INT、BIGINT)的字段,语法规则:
字段名 数据类型 AUTO_INCREMENT
为已存在的表添加主键自增长:
ALTER TABLE <表名> MODIFY <主键字段名> <字段类型> AUTO_INCREMENT;
修改字段数据类型:
ALTER TABLE <表名> MODIFY <字段名> < 数据类型>;
例如修改userinfo中gender字段的数据类型为CHAR:
修改字段名:
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
添加字段:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名];
FIRST为可选参数,表示将新创建的字段设置为表的第一个字段,AFTER表示将新创建的字段添加到指定字段后面。
删除字段:
ALTER TABLE <表名> DROP <字段名>;
修改字段的排列位置:
ALTER TABLE <表名> MODIFY <字段1><数据类型> FIRST|AFTER <字段2>;
删除被其他表关联的主表:
需要先删除关联子表的外键约束,再删除主表。
查看关联子表的外键约束:SHOW CREATE TABLE <表名>\G;
可以看到外键约束名为phone_ibfk_1,删除外键约束:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
现在就可以删除主表了: