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

2021-12-14 07:25:46

MySQL命令初识

登录MySQL

mysql -u root -p 3306

mysql -u root -p 3306 -h 127.0.0.1

数据类型

整型
浮点型
日期时间型
字符型

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列为列级）
根据功能划分，约束类型包括：

1. NOT NULL 非空约束

2. PRIMARY KEY 主键约束

3. UNIQUE KEY 唯一约束

4. DEFAULT 默认约束

5. FOREIGN KEY 外键约束

外键约束

FOREIGN KEY

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

父表和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表
数据表的存储引擎只能为InnoDB
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，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;

外键约束的参照操作

CASCADE：从父表删除或更新且自动删除或更新子表中的行
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表没有指定NOT NULL
RESTRICT：拒绝对父表的删除或更新操作
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;

修改数据表--添加约束

添加主键约束

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;

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

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

ALTER TABLE users2 ADD UNIQUE (username);

添加外键约束

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);

添加/删除默认约束

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子句。

码农公寓