MySQL表的操作

• MySQL基础一
  上一节主要了解了MySQL数据库的相关命令和数据库中存储的数据类型, 这一节主要是MySQL中对于表的操作。
  数据表(或称表): 是数据库的重要储存部分, 是一张二维表格由行和列组成,行称呼为记录,列称之为字段。

在创建表之前 需要打开数据库,使用USE db_name;命令打开数据库。这样就能在db_name下创建表了。

创建表的命令:

CREATE TABLE [IF NOT EXISTS] t_name(
column_name data_type,
...
);

查看当前打开的数据库 SELECT DATABASE();

我们先创建一张工资表,用来练手:

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

解释一下上面的意思 CREATE TABLE 命令为创建表格, UNSIGNED为无符号位，即正数。FLOAT(8,2)表示8位数保留小数点后2位。

ps: 今天听后台说起，如果是涉及到钱的话 可以设置类型为int 然后单位为分。具体的咱也没有学习到就先不谈了~

那么既然创建好了数据表, 如何查看数据表呢?

查看数据表命令
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
查看数据表的命令是否影响当前使用的数据库呢，可以使用
SELECT DATABASE();查看当前使用的数据库。

表有了 怎么查看数据表中的各个字段呢，也就是表结构如何查看。
查看数据表结构:
SHOW COLUMNS FROM table_name;

工资表结构

好了,既然表已经有了接下来就该如何存储数据了， 在mysql中可以使用insert 插入一条数据(记录)。
插入命令:
INSERT [INTO] table_name [(column1, column2...)] VALUES(value1, value2, ...)
从命令中可以看出 字段可以省略,那如果字段省略之后, 值一定要跟表中的数据一一对应,否则会发生错误。
插入数据(记录)之后,可以使用SELECT命令进行查找数据。SELECT 命令在数据库的使用中 非常普遍。
简单查找命令
SELECT expr,... FROM table_name;

表约束

空值与非空值

• NULL, 字段值可以为空
• NOT NULL， 字段值禁止为空
  该约束不能同时出现在同一字段中, 字段默认为NULL 我们可以使用一个小例子来进行测试

1. 创建t_null表

CREATE TABLE IF NOT EXISTS t_null(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);

2. 插入正常数据

INSERT t_null VALUES('Tom', NULL);

正常

3. 插入非法数据

INSERT t_null VALUES(NULL, 12);

错误

自增约束

AUTOINCREMENT

• 自动编号, 并且必须与主键组合使用
• 默认情况下 起始为1, 每次的增量为1

主键

PRIMARY KEY

• 每张表只能存一个主键
• 主键保证记录的唯一性
• 主键自动为NOT NULL
• 自增约束必须组合主键使用, 但是主键不一定要组合自增使用

可以使用例子证明:

1. 创建表格 (主键自增)

CREATE TABLE IF NOT EXISTS t_primary(
t_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL
);

2. 插入数据

INSERT t_primary (username) VALUES('c');
INSERT t_primary (username) VALUES('oc');

返回结果

3. 查看数据

SELECT * FROM t_primary;

查询所有字段结果

ps: 这里的*号为过滤的字段 如果为* 则表示显示全部的字段

显示10的那里是因为我为主键t_id赋值为10, 因为是自增所以下面为11 ， 12， 13..

唯一约束

UNQUE KEY

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

还是以一个例子来解释:

1. 创建表格

CREATE TABLE IF NOT EXISTS t_unique(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED UNQUE KEY
);

2. 插入数据

INSERT t_unique (username, age) VALUES('tom', 20);
INSERT t_unique (username, age) VALUES('tom', 20);

3. 错误

重复错误

默认约束

DEFAULT

• 当插入记录时，如果没有明确赋值,则自动赋予默认值
  例子:

1. 创建表格

CREATE TABLE IF NOT EXISTS t_default(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCRMENT,
username VARCHAR(20) NOT NULL,
sex ENUM('1', '2', '3') DEFAULT '3'
);

2. 插入数据

INSERT t_default (username) values ('tom');

3. 查看表中数据

SELECT * FROM t_default;

4. 数据返回结果

数据返回结果

外键约束

• 父表和字表必须使用相同的存储引擎，而且禁止使用临时表
• 数据表的存储引擎只能是INNODB
• 外键列和参照列必须具有相同的数据类型，其中数字类型的长度或者是否有符号位必须相同，而字符的长度则不同.
• 外键列和参照列必须创建索引，如果外键列不存在索引的话,MySQL将自动创建.
  关于最后一条, 如果参照列没有所以MySQL不会创建外键列的索引
  例子:

1. 创建城市表

CREATE TABLE IF NOT EXISTS t_province(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
p_name VARCAHR(20) NOT NULL
);

2. 创建用户表

CREATE TABLE IF NOT EXISTS t_users(
id SMALLINT UNSIGNED PRIMARY KEY AUT_INCREMENT,
username VARCHAR(20) NOT NULL,
p_id SMALLINT UNSIGN,
FOREIGN KEY (p_id) REFERENCES t_province (id)
);

如外键开头所说 如果参照列创建了索引,那么 MySQL会自动为外键列创建索引。虽然我们没有明显的为t_province表的id创建索引,但是id为主键, 主键会默认创建索引。所以t_users表中的p_id字段也会有索引。那么如何证明呢。 可以使用SHOW INDEXES FROM table_name命令查看表中的索引, 当然为了好看还可以使用 \G 让索引成网格状显示。
查看t_province表中的索引

SHOW INDEXES FROM t_province\G;

t_province中的索引
查看t_users表中的索引

SHOW INDEXES FROM t_users\G;

t_users中的索引

外键约束的参照操作

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

以上参照操作都是指 在创建好外键列的时候, 在更新表的时候, 字表是否也进行相应的操作
例子:

1. 创建车库表

CREATE TABLE IF NOT EXISTS t_garage(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_CREMENT,
garage_name VARCHAR(20) NOT NULL
);

2. 创建车表

CREATE TABLE IF NOT EXISTS t_cars(
id SMALLINT UNSINGED PRIMARY KEY AUTO_CREMENT,
car_name VARCHAR(20) NOT NULL,
garage_id SMALLINT UNSIGNED,
FOREIGN KEY (garage_id) REFERENCES t_garage (id) ON DELETE CASCADE
);

因为车库表为父表,车表为字表 所以现在要父表中插入数据,再在子表中插入数据.

3. t_garage插入数据

INSERT t_garage (garage_name) VALUES('A');
INSERT t_garage (garage_name) VALUES('B');
INSERT t_garage (garage_name) VALUES('C');

使用SELECT * FROM t_garage;查看表中数据

t_garage表中数据

4. t_cars插入数据

INSERT t_cars (car_name, garage_id) VALUES ('红旗', 3);
INSERT t_cars (car_name, garage_id) VALUES ('models', 1);
INSERT t_cars (car_name, garage_id) VALUES ('吉利', 3);

使用SELECT * FROM t_cars;查看表中数据

t_cars中数据

5. 删除父表中的记录

DELETE FROM t_garage where id = 3;

查看t_garage表中的数据 SELECT * FROM t_garage;

t_garage中的数据
查看t_cars表中的数据SELECT * FROM t_cars; t_cars中的数据

由此可以看出 只要是父表中删除了数据,字表中的记录也会被删除。
由于在实际项目中不常使用参照操作，所以也不过多进行了~

表级约束与列级约束

• 对于一个数据列建立的约束,称之为列级约束
• 对多个数据列建立的约束, 称之为表级约束
• 列级约束可以在列定义时声明,也可以在列定义后声明
• 表级约束只能在列定义后声明

修改数据表

• 添加单列

ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name]

• 添加多列

ALTER TABLE table_name ADD [COLUMN] (column_name, column_definition, ...)

添加多列的时候不能指定位置关系, 只能在字段的后方。

• 删除列

ALTER TABLE table_name DROP [COLUMN] column_name

• 删除多列

ALTER TABLE t_name DROP [COLUMN] c_name, DROP c_name

用一个例子演示一下:

1. 创建表格

CREATE TABLE IF NOT EXISTS t_action(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL
);

2. 添加salary字段

ALTER TABLE t_action ADD salary FLOAT(8,2) UNSIGNED;

3. 查看表结构

SHOW CLOUMNS FROM t_action;

表结构

3. 添加age字段

ALTER TABLE t_action ADD age SMALLINT UNSIGNED AFTER username;

4. 查看表结构

SHOW COLUMNS FROM t_action;

表结构

可以看到使用AFTER把age字段添加到了username字段的后方.

5. 删除salary字段

ALTER TABLE t_action DROP salary;

6. 查看表结构

SHOW COLUMNS FROM t_action;

表结构

7. 删除username 添加 area_name

ALTER TABLE t_action DROP username, ADD area_name VARCHAR(20) NOT NULL;

8. 查看表结构

SHOW COLUMNS FROM t_action;

表结构

由此可见如果想进行多个字段的添加和删除只需要用,隔开即可。

• 添加主键

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

用一个例子说明:

1. 创建一个t_user_test表

CREATE TABLE IF NOT EXISTS t_user_test(
id SMALLINT UNSIGNED,
username VARCHAR(20) NOT NULL
);

2. 为id添加主键约束

ALTER TABLE t_user_test ADD CONSTRAINT PK_user_test PRIMARY KEY (id);

CONSTRAINT 为设置别名，目前还不知道

• 添加唯一约束

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

• 添加外键约束

ALTER TABLE tb_name ADD [CONSTRINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition

• 添加/删除默认约束

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

• 删除主键约束

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 MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

修改列定义需要注意的是 如果修改的数据类型是由大往小了更改 那么会造成部分数据丢失。

• 修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

• 数据表更名
  方法一

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

方法二

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

方法二可以更换多个表的名称

插入 insert

第一种方法

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

举个🌰:

1. 创建't_user'表

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

ps: 这里声明age字段时如果没有设置NOT NULL设置默认约束的时候 会出现语法错误

2. 插入数据

• 简单插入

INSERT t_user VALUES(NULL, 'Tom', '123', 13, 1);

插入结果

• 插入(函数)

INSERT t_user VALUES (DEFAULT,'Join', md5('123456'), 14, 2);

插入结果

• 插入(表达式)

INSERT t_user VALUES (DEFAULT, 'left', '123456', 5*6-5, 1);

插入结果

• 插入(多条)

INSERT t_user VALUES (DEFAULT,'right', md5('123456'), 20, 1), (NULL, 'big', '123456', 21, 3);

插入结果

由此可见 mysql即支持表达式还支持函数, 对于自增字段可以使用'DEFAULT'或者NULL默认值。对于多条插入可以以','隔开。

第二种方法

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

第二种方法较第一种方法来说,此方法可以使用子查询, 但是只能插入一条数据。

• 插入t_user表数据

INSERT t_user SET  username='haha',password='123456';

因为id 和 age 都有默认值, sex允许为空 所以可以不赋值。

插入结果

第三种方法

INSERT [INTO] tbl_name [(column_name, ..)] SELECT ...

该方法可以把查询出的数据,插入到数据表中.

更新

• 更新记录(单表)
  UPDATE [LOW_PRIORITY] [IGNORE] tbl_refrenece SET col_name1={expr1|DEFAULT} [, col_name2={expr2}|DEFAULT]... [WHERE where_condition]

UPDATE t_user SET age = age+10, sex = 0;

删除

• 删除记录(单表)

DELETE FROM t_user where id = 6;

查询

• where 查询表达式

• GROUP BY
  查询结果分组

[GROUP BY {col_name|position} [ASC|DESC], ...]

ASC为升序 为默认
DESC为降序
🌰

1. 创建t_users表格

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

users表结构

2. 插入数据

INSERT t_users VALUES (DEFAULT, 'tom', '123456', 20,1), (DEFAULT, 'alex', '123456', 20, 1), (DEFAULT, 'tornado', '123', 19, 1), (DEFAULT, 'fdfd', '123', 10, 0);

插入结果

3. 以sex分组

SELECT sex FROM t_users GROUP BY sex;

分组结果

• HAVING分组条件
  分组可以对全部记录做分组 也可以指定部分记录作为分组。
  🌰

1. 插入基础数据

INSERT t_users VALUES (DEFAULT,'pp', '123456', 10, 0),
 (DEFAULT,'dd', '123456', 26, 0);

2. group by

SELECT sex,age FROM t_users GROUP BY sex,age having age >10;

• order by排序

[ORDER BY {col_name|expr|position} [ASCC|DESC], ...]

🌰

1. 按照id降序排列

SELECT *  FROM t_users ORDER BY id DESC;

排序结果

2. 按照age 降序如果相同按照id升序

SELECT * FROM t_users ORDER BY age DESC , id;

排序结果

限制查询LIMIT

[LIMIT{[offset,] row_cout|row_cout OFFSET offset}]

还是使用t_users来做例子

1. 查询两条数据

SELECT * FROM t_users LIMIT 2;

2. 从第三条数据开始 查询两条

SELECT * FROM t_users LIMIT 2,2;

因为数据库中记录的索引从0开始 所以索引为2的记录是第三条数据

3. limit只按照查询结果来计算索引 不管id为大还是小
   =========================================
   暂存有空做修改