mysql数据表基本操作_MYSQL-数据库及数据表基本操作

1.数据库操作

创建数据库：

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name

如 CREATE DATABASE t1

[IF NOT EXISTS]:如果库已经存在，就不会返回错误

显示数据库编码方式：

SHOW CREATE DATABASE t1

指定编码方式：

CREATE DATABASE IF NOT EXIST t2 CHARACTER SET=gbk

修改数据库：

ALTER {DATABASE|SCHEMA} db_name

如ALTER DATABASE t2 CHARACTER SET=utf8

查看当前服务器下的数据库列表：

SHOW {DATABASE|SCHEMAS}

如 SHOW DATABASES

查看当前打开的数据库

SELECT DATABASE();

删除数据库：

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name

2.数据表

USE 数据库名称

创建数据表：

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]

查看数据表结构：

SHOW COLUMNS FROMtb_name

插入记录：

INSERT [INTO] tb_name [(col_name,…)] VALUES(val,…)

如：

INSERT tb1 VALUES(‘Tom‘,25,7863.25);//必须匹配

INSERT tb1 (username,salary) VALUES(‘Tom‘,7863.25);

记录查找：

SELECT expr,… FROM tb_name

如：

SELECT *FROM tb1;//*为字段过滤

空值NULL与非空NOT NULL：

如：

CREATE TABLE tb2(

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NULL

);

自动编号：

AUTO_INCREMENT：必须与主键组合使用，默认情况下，起始值为1，每次增量为1

主键：

PRIMARY KEY：自动为NOT NULL 不一定和AUTO_INCREMENT一起使用 值唯一不许重复

CREATE TABLE tb3(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL

);

CREATE TABLE tb4(

id SMALLINT UNSIGNED PRIMARY KEY,

username VARCHAR(20) NOT NULL

);

唯一约束：

UNIQUE KEY：可以保证记录的唯一性，字段可以为NULL。每张数据表可以存在多个唯一约束

CREATE TABLE tb5(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE KEY,

age TINYINT UNSIGNED

);

默认约束：

DEFAULT：插入记录时如果没有明确为其赋值，则自动赋予默认值

CREATE TABLE tb5(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE KEY,

sex ENUM(‘1‘,‘2‘,‘3‘) DEFAULT ‘3‘

);

外键约束：

FOREIGN KEY p：保持数据一致性和完整性，实现一对一或一对多关系

要求：

注：子表是具有外键列的表，子表参照的表叫父表

编辑数据表的默认存储引擎(配置)：

default-set-engine=INNODB

CREATE TABLE provinces( //父表

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

pname VARCHAR(20) NOT NULL

);

CREATE TABLE users( //子带

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL,

pid SMALLINT UNSIGNED,//注意数据类型必须一致

FOREIGN KEY(pid) REFERENCES provinces (id)

);

如：

FOREIGN KEY (pid) PREFERENCES provinces (id) ON DELETE CASCADE,

INSERT provinces(pname) VALUES(‘A‘);

INSERT provinces(pname) VALUES(‘B‘);

INSERT provinces(pname) VALUES(‘C‘);

INSERT users1(username,pid) VALUES(‘Tom‘,1);

INSERT users1(username,pid) VALUES(‘Jerry‘,2);

INSERT users1(username,pid) VALUES(‘Lucy‘,3);

当删除provinces中的id=3的数据C时，相应的users1中的Lucy也会被删掉，更新的话同样如此；

实际开发中，我们很少使用物理的外键约束，而是使用逻辑约束；物理的外键约束只有innoDB这种存储引擎才会支持，MYISAM这种引擎就不支持物理的外键约束。反过来说，当我们使用到的引擎为MYISAM时，只能使用逻辑外键(即两个表的设计的逻辑关系)。

在实际开发中，用列级约束比较多，表级约束很少用，除此之外，在所有的约束中，并不是说每种约束都存在着表级或列级约束，其中，NOT NULL 非空约束，DEFAULT约束这两种约束就不存在表级约束，它们只有列级约束，而对于其他的三种，像主键，唯一，外键，它们都可以存在表级和列级约束。

3.修改数据表

添加单列：

ALTER TABLE tb_name ADD [column] col_name column_definition [FIRST|AFTER col_name];

添加多列：

ALTER TABLE tb_name ADD (col_name data_type ,...);

删除单列：

ALTER TABLE tb_name DROP col_name;

删除多列：

ALTER TABLE tb_name DROP col_name，DROP ....;

复合增减列：

ALTER TABLE tb_name ADD col_name data_type [FIRST|AFTER col_name]，DROP col_name;

ALTER TABLE tb_name ADD (col_name data_type ,...),DROP col_name,DROP ...;

添加主键约束

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY(index_col_name);

添加唯一约束

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY](index_col_name,...);

添加外键约束

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY(index_col_name,...) REFERENCES db_name(col_name);

添加或删除默认约束

ALTER TABLE tb_name ADD col_name {SET DEFAULT literal | DROP DEFAULT};

删除主键约束

ALTER TABLE tb_name DROP PRIMARY KEY;

删除唯一约束

ALTER TABLE tb_name DROP {INDEX | KEY} index;;//用命令SHOW INDEXES FROM tb_name (表名称)\G;查看唯一约束的列名称。另外这里的index也可以用来直接删除索引

删除外键约束

ALTER TABLE tb_name DROP FOREIGN KEY fk_name;//用SHOW CREATE TABLE users2;查看外键约束的名称，由CONSTRAINT [symbol]定义

修改列定义

ALTER TABLE tb_name MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;//修改id字段的数据类型和在表users2中所处的位置

修改列名称

ALTER TABLE tb_name CHANGE pid(旧的字段名称) p_id(新的字段名称) TINYINT UNSIGNED NOT NULL;//修改pid字段，改为p_id字段

数据表更名

ALTER TABLE tb_name1 RENAME tb_name2;//把表名users2改为新的users3