MySQL入门之表和数据的操作

数据表的操作

创建数据表时，要先 use 数据库名；来确定要操作的数据库。

创建数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 (字段名字段类型[字段属性]...)[表选项]；

表选项：DEFAULT CHARSET=utf8 表示默认表的编码格式为utf8.

创建一个数据库名为user的表，id为主键自增非空。

CREATE TABLE user(
	id INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) COMMENT '名字',
    sex VARCHAR(20) COMMENT '性别',
    age INT(11) COMMENT '年龄',
    salary FLOAT COMMENT '薪水'
);

not null(非空)、primary key（设置为主键）、auto_increment（自动增加）、commit（备注）

CHAR和VARCHAR的区别：

• char是固定分配空间，例：char(12)将分配12个字节空间，假如实际内存所占为2个字节，则会自动用空格符填充剩下的10个字节
• varchar是动态分配空间，例：varchar(12)，如果实际存入的数据只有2个字节，则会自动只分配2个字节存储空间

因此，varchar会比char更节省内存空间的使用

约束条件	说明
PRIMARY KEY	主键约束，用于唯一标识对应的记录
POREIGN KEY	外键约束，涉及到多表查询
NOT NULL	非空约束，用于指定某个字段不能为空值
UNIQUE	唯一性约束，用于保证数据表中字段的唯一性
DEFAULT	默认值约束，用于设置字段的默认约束

单字段主键

<字段名> <数据类型> PRIMARY KEY

多字段主键

PRIMARY KEY (字段名1,字段名2,字段名n)

CREATE TABLE SC (
	s_id INT,
    c_id INT,
    grade FLOAT,
    PRIMARY KEY (s_id,c_id)
);

每个数据表中可以有多个主键，但只能有一个主键约束

修改表中的某项数据

UPDATE <表名> SET <字段名=要修改后的数据> [WHERE 筛选的内容]

例:

update t_pm set city_name = '广州' where id = 1;

查看表的结构

DESC 表名;

查看数据表

1. 使用SHOW TABLE 查看已有的表

SHOW TABLES [LIKE匹配模式];

例：查看当前数据库里所有的含every字段的表

SHOW TABLES LIKE'%every%';

2. 使用SHOW TABLE FROM 查看数据表的相关信息

SHOW TABLE STATUS [FROM 数据库名] [LIKE 匹配模式]

“\G”是MySQL客户端可以使用的结束符中的一种，用于将显示结果纵向排列，适合字段非常多的情况。

字段名称	描述
Name	数据表的名称
Engine	数据表的存储引擎
Version	数据表的结构文件（如lib_user_temp.frm）版本号
Row_format	记录的存储格式，Dynamic表示动态
Data_length	数据文件的长度（MyISAM存储引擎）或为集群索引分配的内存（InnoDB存储引擎），均以字节为单位
Create_time	数据表的创建时间
Collation	数据表的校对集

3. 使用SHOW CREATE TABLE 查看数据表

SHOW CREATE TABLE <表名>;

4. 使用DESCRIBE 语句查看数据表

DESCRIBE <表名>;

或简写为：

DESC <表名>;

修改数据表

1. 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

ALTER TABLE <旧表名> RENAME [TO] <新表名> [,<旧表名2> TO <新表名2>];

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新字段数据类型>;

修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

修改字段位置

ALTER TABLE <表名> MODIFY <字段1> <字段1数据类型> [FIRST | AFTER 字段2];

修改前表的结构：

修改后表的结构：

ALTER TABLE STUDENTS MODIFY age INT ALTER name; 

新增字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件][FIRST | AFTER 已存在的字段名]

同时增加多条字段

ALTER TABLE <表名> ADD (新字段1 新字段1数据类型,新字段2 新字段2数据类型,……);

默认情况下，新添加的字段在表的末尾。

删除字段

ALTER TABLE <表名> DROP <字段名>;

为表添加约束条件

ALTER TABLE <表名> ADD [约束条件] <字段名>[,字段名2];

删除约束条件

ALTER TABLE <表名> DROP [约束条件];

若表中有多个主键约束，是不可以单独删除的

删除数据表

DROP TABLE <数据表1> [,数据表2,……];

索引

添加、更新与删除数据

添加数据

添加一条记录

1. 指定字段名

INSERT INTO <表名> (字段名1,字段名2,...) VALUES (值1,值2,...);

2. 不指定字段名

INSERT INTO <表名> VALUES (值1,值2,...);

不指定字段名，添加的值的顺序必须和字段在表中定义的顺序相同

添加多条记录

INSERT INTO <表名> (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),...,(值1,值2,...);

INSERT INTO <表名> VALUES (值1,值2,...),(值1,值2,...),...,(值1,值2,...);

通过SET添加数据

INSERT INTO <表名> SET <字段名1> = <值1> [,<字段名1> = <值2>,...];

更新数据

UPDATE <表名> SET <字段名1> = <值1>[,<字段名2> = <值2>,...] [WHERE <条件表达式>];

删除数据

delete

DELETE FROM <表名> [WHERE <条件表达式>]

truncate

TRUNCATE TABLE <表名>;

1. delete可接WHERE 来选择是否全删和部分删除数据，truncate 则是直接全部删除
2. delete通常被认为是DML 语句，而truncate则通常被认为是DDL语句
3. 若都选择全删数据，并且表中有自增字段，那么truncate 会重新从1开始自增，而delete则会从被删之前最大值加1开始自增