MySQL 命令初识 #yyds干货盘点#

MySQL命令初识

登录MySQL

mysql -u root -p 3306

mysql -u root -p 3306 -h 127.0.0.1

数据类型

1. 整型
2. 浮点型
3. 日期时间型
4. 字符型

CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET

操作数据库

USE 命令

• 打开数据库
• USE 数据库名称;
• SELECT DATABASE();

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(
  Column_name data_type,
  ...)

例如：

CREATE TABLE tb1(
  username VARCHAR(20),
  age TINYINT UNSIGNED,
  salary FLOAT(8,2) UNSIGNED
);

查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] ,例如：

SHOW TABLES FROM mysql;

查看数据表结构（列）

SHOW COLUMNS FROM tbl_name

例如： SHOW COLUMNS FROM tb1;

记录的插入与查找（行）

• 插入记录

INSERT [INTO] tbl_name [(col_name, ...)] VALUES(val,...)

例如：

INSERT tb1 VALUES('Tom', 25, 7777.88)
INSERT tb1 (username, salary) VALUES ('Jerry', 6666.66)

• 记录查找 SELECT

SELECT expr,... FROM tbl_name

SELECT * FROM tb1;

空值与非空

• NULL,字段值可以为空
• NOT NULL，字段值禁止为空

CREATE TABLE tb2(
  username VARCHAR(20) NOT NULL,
  Age TINYINT UNSIGNED NULL);

自动编号与主键约束

AUTO_INCREMENT 自增

• 自动编号，且必须与主键组合使用
• 默认情况下，其实值为1，每次的增加为1

CREATE TABLE tb3(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL
);

PRIMARY KEY 主键约束

• 每张表只能存在一个主键,主键的字段允许赋值
• 主键保证记录的唯一性
• 主键自动为NOT NULL
• 不一定需要和自动编号一起使用

INSERT tb3 (username) VALUES('Tome');
INSERT tb3 (username) VALUES('Jack');
INSERT tb3 (username) VALUES('Rose');

唯一约束

• 可以保证记录的唯一性
• 字段可以为空值(NULL)
• 每张数据表可以存在多个唯一约束

CREATE TABLE tb4(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  age TINYINT UNSIGNED
);

SELECT COLUMNS FROM tb4;
INSERT tb4(username, age) VALUES('Tom', 22); 

默认约束

DEFAULT 默认值

• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

CREATE TABLE tb6(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  sex ENUM('1', '2', '3') DEFAULT '3'
);
SHOW COLUMNS FROM tb6;
INSERT tb6(username) VALUES('Jack');

约束

不同标准划分，结果不同

1. 约束保证数据的完整性和一致性
2. 根据参照数目的多少，约束分为表级约束和列级约束（约束的字段的多少，1列为列级）
3. 根据功能划分，约束类型包括：

      1. NOT NULL 非空约束

  2. PRIMARY KEY 主键约束

  3. UNIQUE KEY 唯一约束

  4. DEFAULT 默认约束

  5. FOREIGN KEY 外键约束

外键约束

FOREIGN KEY

• 保证数据一致性，完整性。
• 实现一对一或一对多关系

1. 父表和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表
2. 数据表的存储引擎只能为InnoDB
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MYSQL将自动创建索引。

MySQL配置文件:Default-storage-engine=INNODB

CREATE TABLE province(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  province VARCHAR(20) NOT NULL
);
SHOW CREATE TABLE province;
CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(10) NOT NULL,
  -- pid BIGINT,
  -- pid SMALLINT,
   pid SMALLINT UNSIGNED,
  FOREIGN KEY (pid) REFERENCES province(id)
);
SHOW INDEXES FROM province\G;
SHOW INDEXES FROM users\G;

外键约束的参照操作

1. CASCADE：从父表删除或更新且自动删除或更新子表中的行
2. SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表没有指定NOT NULL
3. RESTRICT：拒绝对父表的删除或更新操作
4. NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同

物理外键，一般实现逻辑外键

表级约束与列级约束

• 对一个数据列建立的约束，称为列级约束（用的更多）
• 对多个数据列建立的约束，称为表级约束
• 列级约束既可以在列定义时声明，也可以在列定义后声明
• 表级约束只能在列定义后声明
• 其中NOT NULL、DEFAULT不存在表级约束

check 约束，不起作用

修改数据表

添加单列

ALTER TABLE tbl_name ADD[COLUMN] col_name column_definition[FIRST | AFTER col_name]

例如：

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;

添加多列

ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,...)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

例如：

ALTER TABLE user1 DROP truname;
ALTER TABLE user1 DROP password, DROP age;

修改数据表--添加约束

1. 添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT] [symbol] PRIMARY KEY [index_type] (index_col_name,...)

CREATE TABLE users2(
  username VARCHAR(10) NOT NULL,
  pid SMALLINT(5) UNSIGNED
);
ALTER TABLE user2 ADD id SMALLINT UNSIGNED;
SHOW COLUMNS FROM users2;
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
SHOW COLUMNS FROM users2;

2. 添加唯一约束，可以由多个，而主键约束只能有一个

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name, ...)

ALTER TABLE users2 ADD UNIQUE (username);

3. 添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference definition

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCE province(id);

4. 添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

例如：

ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;
ALTER TABLE user2 ALTER age SET DEFAULT 15;
ALTER TABLE user2 ALTER age DROP DEFAULT;

删除约束

删除主键约束 ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束 ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

删除外键约束 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列定义

ALTER TABLE tbl_name MODIFTY[COLUMN] col_name column_definition [FIRST|AFTER col_name]

修改列名称

ALTER TABLE tbl_name CHANGE[COLUMN] old_col_name new_col_name_definition [FIRST|ALTER col_name]

例如：ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

数据表更名

-- 方法一：
 ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
-- 方法二：
 RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2]...

ALTER TABLE users2 RENAME users3; RENAME TABLE users3 TO users2;

总结：

• 约束
• 功能分：NOT NULL，PRIMARY KEY， UNIQUE KEY， DEFAULT，FOREIGN KEY
• 数据列的数目分：表级约束，列级约束
• 修改数据表
• 针对字段的操作：添加/删除字段，修改列定义，修改列名称
• 针对约束的操作；添加/删除各种约束
• 针对数据表的操作：数据表更名（量种方式）

插入记录

INSERT语句

第一种方法：

INSERT [INTO] tbl_name [(col_name, ...)] {VALUES|VALUE} ({expr|DEFAULT},...,(...),...

先创建一个users表：

CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL,
  password VARCHAR(32) NOT NULL,
  age TINYINT UNSIGNED NOT NULL DEFAULT 10,
  sex BOOLEAN
);

然后依次插入记录：

INSERT users VALUEs(NULL, 'Tom', '123', 25, 1)

INSERT users VALUEs(DEFAULT, 'Jerry', '123', 18, 1)

expr可以赋予表达式、函数

INSERT users VALUES(NULL, 'Tom', '123', 25), (DEFAULT, 'Jerry', md5('123'), 18, 1);

第二种方法：

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery），而且每次只能插入一条记录

第三种方法:

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...查询结果写入指定的数据表

UPDATE

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT} [, col_name2={expr2|[DEFAULT]}]...[WHERE where_condition]

UPDATE users set age = age + 5;

DELETE

单表删除DELETE FROM tbl_name [WHERE where_condition]

SELECT

查找记录

SELECT select_expr[,select_expr ...]
[
  FROM table_references
  [WHERE where_condition]
  [GROUP BY {col_name|position} [ASC|DESC],...]
  [HAVING where_condition]
  [ORDER BY {col_name|expr|positon}[ASC|DESC],...]
  [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

例如：SELECT VERSION(); SELECT NOW(); SELECT 3+5； 查询表达式：

• 每个表达式表示想要的一列，必须有至少一个
• 多个列之间以英文逗号隔开
• 星号(*)表示所有列。table_name.*可以表示命名表的所有列。
• 查询表达式可以使用[AS] alias_name为其赋予别名
• 别名可以用于GROUP BY，ORDER BY或HAVING子句。

总结

这里只是将部分常用的MySQL 命令做了一些小的罗列，方便后续开发的时候做一些备忘和查阅。