MySQL基础知识

操作数据表

MySQL登录

net start mysql80 or net stop mysql80

mysql -uroot -p

password：1177

prompt 提示符

\D 当前日期
\d 当前数据库
\h 当前主机

\u 用户名

mysql -uroot -p --prompt \u@\h(\d)>

prompt \u@\h(\d)>

MySQL常用命令

SELECT VERSION() 显示版本
SELECT NOW(); 显示日期
SELECT USER(); 显示用户
SELECT DATABASES()；显示数据库
创建数据库test
```
CREATE DATABASE [IF NOT EXISTS]test [CHARACTER SET utf-8];
```

SHOW WARNINGS; //查看warning

SHOW CREATE DATABASE test //展示创建的数据库信息

修改数据库编码

ALTER DATABASE test CHARACTER SET = utf-8

删除数据库

DROP DATABASE [IF EXISTS] test;

创建数据表及基本操作

CREATE TABLE test1(
    username VARCHAR(20) NOT NULL,     //字段可以为空
    age TINYINT UNSIGNED NULL,        //非空
    salary FLOAT(8,2) UNSIGNED
);

查看数据表列表
```
SHOW TABLES [FROM test]
```
查看数据表结构
```
SHOW COLUMNS FROM test;
```

插入记录

//若省略[]中内容，则所有字段都要赋值
INSERT test [username,age] VALUES('Cui',24);

查找记录
```
SELECT * FROM test;
```

AUTO_INCREMENT 自动编号

自动编号，必须与主键组合使用,但主键不必定需要AUTO_INCREMENT
默认情况下，起始值为1，每次的增量为1

不需要赋值

//必须设为主键 PRIMARY KEY
CREATE TABLE test(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
);

PRIMARY KEY 主键

主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性

主键自动为NOT NULL

CREATE TABLE test(
    id SMALL UNSIGNED PRIMARY KEY,
);

UNIQUE KEY 唯一约束

唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值(NULL)

每张数据表可以存在多个唯一约束 (与主键的区别)

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

DEFAULT 默认约束

默认值

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

//sex字段没有赋值时，默认为3
CREATE TABLE test(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    sex ENUM('1','2','3') DEFAULT '3'
);

约束及修改数据表

约束

约束保证数据的一致性和完整性
约束分为表级约束和列级约束：对两个及以上字段使用为表级约束，对一个为列级约束
约束类型包括：NOT NULL(非空约束)、PRIMARY KEY (主键约束)、UNIQUE KEY(唯一约束)、DEFAULT (默认约束)、FOREIGN KEY(外键约束)

外键约束
保持数据的一致性、完整性 因此称为
实现一对一或一对多关系 关系型数据库

外键约束的要求:
父表和子表必须使用相同的存储引擎，且只能为InnoDB，禁止使用临时表
外键列和参照列必须具有相似的数据类型。数字的长度或有无符号位必须相同，字符长度可不同

外键列和参数列必须创建索引，如果外键列不存在索引，MySQL将自动创建索引。

创建一个省份数据表：

CREATE TABLE provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    //参照列
    pname VARCHAR(20) NOT NULL
);
//查看存储引擎是否为InnoDB
SHOW CREATE TABLE provinces;

创建一个用户数据表,存储用户所在省份：

CREATE TABLE user(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    //参照列
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,            //外键列
    //
    FOREIGN KEY (pid) REFERENCES provinces (id)
);

显示索引： SHOW INDEXES FROM provinces\G; \G:以网格的形式呈现

外键约束的参照操作

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

修改数据表

添加单列

//FIRST|AFTER 控制添加列的位置
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

ALTER TABLE user ADD password VARCHAR(32) NOT NULL DEFAULT AFTER username; /插入在 username 列后

添加多列

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

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

删除多列且添加一列

ALTER TABLE tbl_name DROP col_name,DROP col_name,ADD col_name col_definition;

添加约束

添加主键约束

//CONSTRAINT 给主键添加名字
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)

eg: ALTER TABLE user ADD CONSTRAINT user_id PRIMARY KEY (id);

添加唯一约束

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

eg: ALTER TABLE user ADD UNIQUE (username);

添加外键约束

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

eg: ALTER TABLE user ADD FOREIGN KEY (pid) REFERENCE provinces (id);

添加/删除默认约束
```
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}
```
eg: ALTER TABLE user ADD age TINYINT UNSIGNED NOT NULL;

ALTER TALBE user ALTER age SET DEFAULT 15;//添加默认约束

ALTER TABLE user ALTER age DROP DEFAULT;//删除默认约束

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

删除唯一约束

//一张表可以有多个唯一约束，因此删除需要唯一约束名字
ALTER TABLE tbl_name DROP {index|key} index_name

eg: ALTER TABLE user DROP INDEX username;

删除外键约束
```
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
```
eg: SHOW CREATE TABLE user;//查看外键约束的名字user_ibfk_1
ALTER TABLE user
FOREIGN KEY user_ibfk_1;
删除索引

eg:删除索引pid

ALTER TABLE user DROP INDEX pid;

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name]

eg: ALTER TABLE user MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;//修改id字段为首列

类型同样可以修改，若由大类型改为小类型，可能会造成数据丢失

修改列名称

eg: ALTER TABLE user CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

//修改列名称pid为p_id

修改数据表名称

方法1：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法2：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…