mysql 斯文克斯_MySQL操作语句总结

1，DDL (Data Definition Language 数据库定义语句)

操作对象：数据库和表

注意事项：DDM的表操作语句，在InnoDB存储引擎中，会引起表级锁，在高并发情况下，会引起问题。

典型的操作动作

1.1 创建数据库​​​

CREATE DATABASE [IF NOT EXISTS] `db_name` [CHARACTER SET "CHAR_NAME"] [COLLATE "COLL_NAME"];

1.2 删除数据库

DROP DATABASE [IF EXISTS] `db_name`;

1.3 使用某数据库

USE `db_name`;

1.4  查看当前数据库的所有表名称

SHOW TABLES;

1.5 创建一张表  (注意：以下示例的数据类型未经测试，可能存在问题)

CREATE TABLE `table_name` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT "主键ID",

`tiny_int_value` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT "微小整数值型",

`small_int_value` smallint(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT "小整数值型",

`medium_int_value` mediumint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "中整数值型",

`int_value` int(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT "整数值型",

`big_int_value` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "极大整数值型",

`decimal_value` decimal(10, 2)UNSIGNED NOT NULL DEFAULT 0.00 COMMENT "小数点值型",

`float_value` float(10, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT "单精度浮点数值",

`double_value` float(10, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT "双精度浮点数值",

`char_str` char(256) NOT NULL DEFAULT "" COMMENT "定长字符型",

`varchar_str` varchar(65535) NOT NULL DEFAULT "" COMMENT "不定长字符型",

`tiny_blob_binary` tinyblob NOT NULL DEFAULT "" COMMENT "不超过255个字符的二进制字符串",

`blob_binary` blob NOT NULL DEFAULT "" COMMENT "不超过65535个字符的二进制形式的长文本数据",

`medium_blob_binary` mediumblob NOT NULL DEFAULT "" COMMENT "不超过16777215个字符的二进制形式中等长度文本数据",

`long_blob_binary` longblob NOT NULL DEFAULT "" COMMENT "不超过4294967295个字符的二进制形式极大文本数据",

`tiny_text` tinytext NOT NULL DEFAULT "" COMMENT "不超过255个字符的短文本字符串",

`text` blob NOT NULL DEFAULT "" COMMENT "不超过65535个字符的长文本数据",

`medium_text` mediumtext NOT NULL DEFAULT "" COMMENT "不超过16777215个字符的中等长度文本数据",

`long_text` longblob NOT NULL DEFAULT "" COMMENT "不超过4294967295个字符的极大文本数据",

`date` date NOT NULL DEFAULT "1000-01-01" COMMENT "日期值",

`time` time NOT NULL DEFAULT "25:00:00" COMMENT "时间值",

`year` year NOT NULL DEFAULT "1901" COMMENT "年份值",

`date_time` datetime NOT NULL DEFAULT "1000-01-01 00:00:00" COMMENT "混合日期和时间值",

`time_stamp` timestamp NOT NULL DEFAULT "1970-01-01 00:00:00" COMMENT "混合日期和时间值，时间戳"

PRIMARY KEY (`id`),

UNIQUE KEY `idx_iv` (`int_value`),

KEY `idx_bi` (`big_int_value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT="示例表";

1.6 查看某张表的创建语句

SHOW CREATE TABLE `table_name`;

1.7 描述某张表结构

DESC `table_name`;

1.8 修改表结构

ALTER TABLE `table_name` ...

1.8.1 增加字段

ALTER TABLE `table_name` ADD COLUMN `column_name` tinyint(1) UNSIGNED NOT NULL DEFALUT 0 COMMENT "新增字段" AFTER `exist_column`;

1.8.2 删除字段

ALTER TABLE `table_name` DROP COLUMN `column_name`;

1.8.3 修改字段

1.8.3.1 修改字段名称

ALTER TABLE `table_name` CHANGE `old_column_name` `new_column_name`;

1.8.3.1 修改字段描述

ALTER TABLE `table_name` MODIFY COLUMN `column_name` tinyint(1) UNSIGNED NOT NULL DEFALUT 0 COMMENT "修改字段";

1.8.4 增加索引

如果是创建一张新表去存放已有的数据，可以暂不创建索引，先把数据插入进去之后，再通过新增索引的方式去实现。如果在创建表的时候就创建索引，则在数据插入时，存储引擎需要不断地去维护索引，耗费写入性能。

1.8.4.1 增加普通索引

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_name`[, `coumn_name_two`, ...]);

或者

ALTER TABLE `table_name` ADD KEY `index_name` (`column_name`[, `coumn_name_two`, ...]);

1.8.4.2 增加唯一索引

ALTER TABLE `table_name` ADD UNIQUE KEY `unique_index_name` (`column_name`[, `coumn_name_two`, ...]);

1.8.4.3 声明主键索引 (注意：只在当前表未显示声明主键的情况下才生效)

ALTER TABLE `table_name` ADD PRIMARY KEY (`column_name`);

1.8.4.4 增加全文索引

ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name` (`column_name`[, `coumn_name_two`, ...]);

注意： MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

1.8.5 删除索引

1.8.5.1 删除普通索引和唯一索引

DROP INDEX `index_name` ON TABLE `table_name`;

ALTER TABLE `table_name` DROP INDEX `index_name`;

1.8.5.2 删除主键索引

ALTER TABLE `table_name` DROP PRIMARY KEY;

1.8.6 修改索引

没有直接删除索引的语句，可以通过先删除旧索引，再创建新索引的方式进行。

1.9 删除某张表

DROP TABLE [IF EXISTS] `table_name`;

2.0 清空某张表的数据

TRUNCATE TABLE [IF EXISTS] `table_name`;

DELETE 是一条一条删除记录的，配合事务(transaction)和回滚(rollback)可以找回数据，且自增(auto_increment)不会被重置。

TRUNCATE是隐式提交，会直接删除整个表，再重新创建一个一模一样的新表，自增(auto_increment)会被重置，原表数据无法找回。

2，DML ( Data Manipulation Language 数据操控语言)

操作对象：数据表及其记录

注意事项：当我们对表记录进行Insert、Delete和Update操作的时候，一定要注意，有没有其他的DDL语句在进行操作，及其影响。以及锁，事务等方面。

典型的操作动作

2.1 插入一条记录

INSERT INTO `table_name` (`column_1`, `column_2`,`column_3`) VALUES (1, 2, 3) [,(11, 22, 33),(111, 222, 333)];

1.2 删除一条或多条记录

DELETE FROM `table_name` [where query];

1.3 更新一条或多条记录

UPDATE `table_name` SET `column_name_1` = value_1 [, `column_name_2` = value_2] [WHERE `column_name_3` = value_3 [AND `column_name_4` = value_4]];

3, DQL (Data Query Language 数据查询语言)

操作对象：数据表

注意事项：共享锁，排他锁，意向锁，事务隔离等级等方面。

1.1 典型的操作动作

SELECT FROM `table_name` [WHERE `column_name_1` = value_1 [AND `column_name_2` = value_2]];

4，DCL(Data Control Language 数据控制语言)

操作对象：数据库服务层的连接校验，权限验证，以及存储引擎的事务

4.1 用户管理

需要当前用户为root权限才可操作，一般由DBA同学进行操作，开发人员一般不操作。

4.1.1 新增用户

CREATE USER "user_name"@"ip/host_name/%" IDENTIFIED BY "pass_word";

其中ip/host_name 表示创建的用户使用的IP地址，可以设置为localhost(代表本机)，"%"表示允许该用户通过任意IP地址登录。

4.1.2 删除用户

DROP USER "user_name"@"ip/host_name";

4.1.3 修改密码

UPDATE USER SET PASSWORD = PASSWORD("new_pass_word") WHERE USER = "user_name";

4.1.4 查询用户

USE myql; //切换到mysql数据库

SELECT * FROM USER; //查询user表

4.1.5 忘记root密码，如何修改

4.1.5.1 在开启MySQL服务的适时候，使用 --skip-grant-tables 参数跳过权限检查启动MySQL

mysqld_safe --skip-grant-tables

161209 00:01:29 mysqld_safe Logging to "/data/mysql/mytest_3306/data/error.log".

161209 00:01:29 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306/data

4.1.5.2 开始改密码

不过这里有个地方要注意，如果直接用 SET 的方式改密码，会报错，因为使用了 --skip-grant-table，所以没有用

root@localhost:mysql.sock  00:02:17 [(none)]>SET PASSWORD FOR "root"@"localhost" = PASSWORD("123456");

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

所以只能直接改mysql.user权限表

root@localhost:mysql.sock  00:05:52 [(none)]>USE mysql

Database changed

root@localhost:mysql.sock  00:06:10 [mysql]> UPDATE user SET password = password ("123456") where user = "root";

Query OK, 0 rows affected (0.05 sec)

Rows matched: 1  Changed: 0  Warnings: 0

修改完毕，关闭服务。然后正常启动mysql服务，再用修改后的密码登陆

[root@c80k2 Desktop]# mysqladmin shutdown

[root@c80k2 Desktop]# mysqld_safe

[root@c80k2 Desktop]# mysql -uroot -p123456

4.2 权限管理

4.2.1 授予权限

GRANT SELECT[,INSERT,DELETE,UPDATE]/ALL ON `database_name`.`table_name_1`/* TO "user_name"@"ip/host_name";

ALL表示所有权限，*表示该数据库下的所有表。

4.2.2 收回权限

REVOKE SELECT[,INSERT,DELETE,UPDATE] ON "database_name"."table_name_1/*" FROM "user_name"@"ip/host_name/%";