MySQL-基础知识备忘
基础知识备忘
[主要参考]
1. http://www.runoob.com/mysql/mysql-tutorial.html
2. https://www.cnblogs.com/kissdodog/p/4174421.html
管理
创建用户
- mysql -u root -p
- use mysql
- grant grant on *.* to ‘username‘@’localhost’ identified by ‘password’ with grant option;
其中:
grant : 授权的权限列表,如select/insert/update/drop等
username : 需要创建的用户名
password : 用户密码
给用户赋值所有权限 :
grant all privileges on *.* to ‘username‘@’localhost’ identified by ‘password‘; - FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表
管理命令
- use database_name
- show databases
- show tables
- show columns from table_name
- show index from table_name
- show table status like [from db_name]
数据库
- 创建数据库
mysqladmin -u root -p create database_name - 删除数据库
mysqladmin -u root -p drop database_name - 选择数据库
mysql -u root -p
use database_name
数据类型
数值
- tinyint
- smallint
- mediumint
- int/integer
- bigint
- float
- double
- decimal
日期/时间
- date 日期值
- time 时间值或持续时间
- year 年份
- datetime 混合日期和时间值
- timestamp 混合日期和时间值,时间戳
字符
- char 定长字符串(0-255字节)
- varchar 变长字符串(0-65535字节)
- tinyblob
- tinytext
- blob 二进制形式的长文本数据
- text 长文本数据
- mediumblob
- mediumtext
- longblob
- longtext
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
建表、删表
建表
DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL,
`auther` varchar(40) DEFAULT NULL,
`submission_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
删表
DROP TABLE table_name;
查询数据
语法
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
where子句
select/delete/update查询条件
操作符列表
- =
- <> , !=
- <
- >
- <=
- >=
- AND
- OR
update
语法
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
当我们需要将字段中的特定字符串批量修改为其他字符串时,可已使用以下操作:
UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')
[WHERE Clause]
delete
语法
DELETE FROM table_name [WHERE Clause]
like
用于条件匹配
- %:任意字符
- _:单个字符
- NOT LIKE:非
union
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
其中,
- DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选,返回所有结果集,包含重复数据。
- 两个select的expression数量必须保持一致,内容可以不同。
排序
语法
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
其中,
- 可以使用任何字段作为排序条件
- 可以设定多个排序字段
- ASC升序,DESC降序,默认升序
分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
group by 语法
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
with rollup
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…),也就是一次汇总。
coalesce
select coalesce(a,b,c);
参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。类似地有ifnull函数。
连接
inner join
内连接、等值连接,可以直接用join。
left join
左连接,获取左边表所有记录,即使右表没有对应匹配的记录。
right join
右连接,获取右边表所有记录,即使左表没有对应匹配的记录。
NULL值处理
为了处理NULL值情况,MySQL提供了三大运算符:
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符(不同于=运算符),当比较的的两个值为 NULL 时返回 true。
关于 NULL的条件比较运算是比较特殊的。不能使用 = NULL 或 != NULL 在列中查找 NULL值。在MySQL中,NULL 值与任何其它值的比较(即使是 NULL)永远返回 false,即 NULL = NULL 返回false。MySQL中处理NULL使用 IS NULL 和 IS NOT NULL 运算符。
正则表达式
REGEXP操作符支持正则表达式
事务
- 在MySQL中只有使用了Innodbs数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行
- 事务用来管理 insert,update,delete 语句
一般来说,事务是必须满足4个条件(ACID):
- 原子性(Atomicity):一组事务,要么成功;要么撤回。
- 稳定性(Consistency) :有非法数据(外键约束之类),事务撤回。
- 隔离性(Isolation):事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 可靠性(Durability):软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
事务控制语句
- BEGIN或START TRANSACTION;显式地开启一个事务;
- COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
- ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
- RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
- ROLLBACK TO identifier;把事务回滚到标记点;
- SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MySQL事务处理方法
- 方法一
用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
- 方法二
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
ALTER
修改数据库表名或者表字段时,使用alter。
删除、添加或修改表字段
- 删除
alter table table_name drop column_name;
- 添加
alter table table_name add column_name column_type comment 'comment content' [first]|[after column_name];
使用first/after制定新增列的位置
- 修改字段位置
alter table table_name modify column_name column_type first|after column_name2;
字段类型及名称
使用modify或change子句
alter table table_name modify column_name column_type;
alter table table_name modify old_column new_column new_type;
Null值和默认值
修改字段时可以指定是否包含或是否设置默认值。如果不设置则MySQL会自动设置字段默认值为NULL。
mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;
修改字段默认值
添加字段默认值
alter table table_name alter column_name set default column_value;
删除字段默认值
alter table table_name alter column_name drop default;
修改表名
alter table old_table_name rename to new_table_name;
修改存储引擎
alter table table_name engine=myisam;
删除外键约束
alter table table_name drop foreign key key_name;
索引
普通索引
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX index_name ON table_name(column_name(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构(添加索引)
ALTER table table_name ADD INDEX index_name(column_name)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
column_name VARCHAR(16) NOT NULL,
INDEX [index_name] (column_name(length))
);
删除索引的语法
DROP INDEX [index_name] ON table_name;
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
alter命令添加、删除索引
有四种方式来添加数据表的索引:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
alter命令添加、删除主键
主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键:
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。
显示索引信息
SHOW INDEX FROM table_name;
临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。
创建临时表
使用关键字temporary即可。
删除临时表
同普通表,drop即可。
复制表
如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等。
方法一
- 查询原始表的创建语句
SHOW CREATE TABLE table_name
- 更改表名执行以上语句
- 插入数据
insert into clone_table_name(...)
select ... from table_name;
方法二
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTabl;
方法三
create table new_table_name select * from table_name;
这种方法不推荐,因为会丢失部分信息,比如索引、字符集等。
其他
可以只拷贝部分字段,可以只拷贝部分数据,可以修改新建表的列名。
元数据
查询结果信息:
- SELECT, UPDATE 或 DELETE语句影响的记录数。
- 数据库和数据表的信息: 包含了数据库及数据表的结构信息。
- MySQL服务器信息: 包含了数据库服务器的当前状态,版本号等。
命令 描述
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量
序列
auto_increment
一个表中只能有一个,而且必须是在主键/索引列上
重置auto_increment
慎用!删除列后重新添加列
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
设置auto_increment初始值
创建表时
CREATE TABLE table_name
(
... //表列
)engine=innodb auto_increment=100 charset=utf8;
建表后
ALTER TABLE t AUTO_INCREMENT = 100;
重复数据
防止出现
可以在表中设置primary key或者unique来避免重复数据。
INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。
REPLACE INTO如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。
统计
SELECT COUNT(*) as c, column_name//可能重复的列
FROM table_name
GROUP BY column_name
HAVING c > 1;
过滤
可以使用distinct或group by 来过滤
删除
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;
-> GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
或者添加主键或唯一索引
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
导出数据
select … into outfile ‘xxx’
使用select … into outfile ‘xxx’导出数据到文本文件xxx上。
LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT … INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。
mysqldump
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
方法一(指定目录):
mysqldump -u username -p --tab=directory database_name table_name
如:
mysqldump -u jt -p --tab=/User/jiangtao/tmp test student
该命令会在目标目录下生成两个文件,一个sql文件为建表SQL,一个txt文件为表的数据文件。
方法二(指定文件):
mysqldump -u username -p database_name [table_name] > filename
如:
mysqldump -u jt -p test student > dumpfile.txt
该命令会生成一个文件,里面包含建表语句和insert语句。如果不指定table_name则导出所有表。
导入数据
load data
与select … into outfile ‘xxx’对应
LOAD DATA LOCAL INFILE 'xxx' INTO TABLE table_name;
如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件在服务器上按路径读取文件。可以明确地在LOAD DATA语句中指出列值的分隔符和行尾标记,但是默认标记是定位符和换行符。
LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的,如果数据文件中的列与插入表中的列不一致,则需要指定列的顺序。
mysqlimport
mysqlimport -u username -p --local database_name file_name.txt
该命令与load data命令功能一样,只能导入表数据,不要被import这个命令名称给忽悠了,他必须要求导入表已经存在!
- 要求表已经存在!
- 导入文件名file_name必须与目标数据库表名一致!
数据库备份与恢复
备份
备份指定表或指定数据库下所有表: mysqldump -u username -p database_name [table_name ...] > dumpfile.sql 备份指定数据库: mysqldump -u username -p --databases database_name [...] > dumpfile.sql 备份所有数据库: mysqldump -u username -p --all-databases > dumpfile.sql
恢复
mysql -u username -p [database_name] < dumpfile.sql