MySQL——数据库操作

一、建立数据库

1、创建新的数据库

create database database_name

2、删除指定数据

drop database database_name

3、建库后进入该数据库

use database_name

4、查询该数据库下多少个数据表

show tables

5、迁移数据库

（1）、查询目标数据库下面的所有表

SELECT table_name,TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA='旧数据库名称';

（2）、创建新数据库

CREATE DATABASE IF NOT EXISTS 新数据库名称 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

（3）、将每个旧表迁移到新的数据库里面

RENAME TABLE 旧数据库名称.表1 TO 新数据库名称.表1,旧数据库名称.表2 TO 新数据库名称.表2,……;

（4）、查询新数据库下面的所有表是否迁移成功

SELECT table_name,TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA='新数据库名称';

（5）、最后删除旧数据库

DROP DATABASE 旧数据库名称;

二、建立数据表

1、建表

create table table_name(column_name1 attribute,column_name2 attribut,……)

2、使用外键约束

（1）、建立外键约束语法

[CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (col_name, ...)
 REFERENCES tbl_name (col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]
reference_option:
 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

1. CONSTRAINT [symbol]]指定约束名字，不写则系统自动生成外键约束名；

2. MySQL强制外键约束的列上必须要有索引， [index_name]指定索引名，不写则系统自动生成索引名，如果已经存在可以用于支持外键约束的索引，那么指定的索引名则无效，采用已有的索引；

3. [ON DELETE reference_option]和[ON UPDATE reference_option]指定主表update和delete操作时，子表对应的数据怎么响应，其中reference_option参数有如下5种：

• 1.CASCADE，级联删除/级联更新，即主表delete或update了被其他表引用的数据，对应子表的数据也被delte或update；

• 2.SET NULL，当主表delete或update了被其他表引用的数据，对应子表的数据被设为null，注意子表的外键不能设为not null；

• 3.RESTRICT，主表不允许delete或update被其他表引用的数据；当没有指定任何[ON DELETE reference_option]和[ON UPDATE reference_option]，默认是采用RESTRICT；

• 4.NO ACTION，在MySQL中，等效于RESTRICT；

• 5.SET DEFAULT，InnoDB和NDB中无法使用该参数。

实例：

create table tab1 (id int primary key);
create table tab2 (
id int primary key,
col1 int,
foreign key (col1)
references tab1(id)
);

（2）、添加外键约束语法

ALTER TABLE tbl_name
 ADD [CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (col_name, ...)
 REFERENCES tbl_name (col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]

（3）、删除外键约束语法

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

注意：

1.外键上无法插入主表没有的数据，也无法更新为主表没有的数据；

2.外键上必须要有索引，主表上对应的列最好也创建索引，因为当子表外键插入数据时，也是需要到主表对应的列上去检查数据是否存在，有索引可以提高效率；

3.如果一个表被其他表的外键引用，则这个表无法被删除；如果通过set foreign_key_checks=0强制删除主表，则子表无法插入任何数据；

4.外键的字段类型，字符集必须要跟引用的字段一致，unsigned属性也要一致，否则无法创建外键约束；

5.被引用的列上必须要有索引，否则无法创建外键约束。

3、使用主键约束

（1）、单字段主键

语法：

CREATE TABLE <数据表>
(
	<字段名> <数据类型> PRIMARY KEY  -- 在定义字段时指定主键
    <字段名> <数据类型> PRIMARY KEY(<字段名>)  -- 在定义完字段后指定主键
);

实例：

CREATE TABLE table_example1
(
  # 在定义字段时，指定主键
	id INT(10) PRIMARY KEY,
  # 在定义完字段后指定主键
    id INT(10),
    PRIMARY KEY(id),
);

（2）、联合主键

联合主键表示该数据的主键有多个字段组成，一般在单字段主键无法满足需求时使用。

注意：联合主键只能在定义完所有的字段后指定，不能直接在字段明后指定主键(也无法指定)

语法：

CREATE TABLE <数据表>
(
	<字段1> <数据类型>,
	<字段2> <数据类型>,
	PRIMARY KEY(<字段1>,<字段2>)
);

实例：

# 创建数据表table_example3，主键约束为id,name的联合主键
CREATE TABLE table_example3
(
	id INT(10),
	name VARCHAR(20),
	PRIMARY KEY(id,name)
);

（3）、修改表属性时添加主键约束

如在建表时未添加主键约束，可通过修改表属性 添加主键约束

此时，待设为主键的字段不允许有NULL、重复值

语法：

ALTER TABLE <数据表> ADD PRIMARY KEY(<字段名>);  -- 修改表属性时添加主键约束

实例：

# 将字段id设为无主键表的主键约束
# 1.新建无主键约束的数据表
CREATE TABLE table_example4
(
	id INT(10),
	name VARCHAR(20)
);

修改表table_example4，将id设为主键

ALTER TABLE table_example4 ADD PRIMARY KEY(id);

（4）、删除主键约束

ALTER TABLE <数据表> DROP PRIMARY KEY(<字段名>);
ALTER TABLE <数据表> DROP PRIMARY KEY;  -- 一张表中只有一个主键，所以删除主键约束可以不用指定主键名，两行代码效果一样

4、使用非空约束

非空约束强制列不能为 NULL 值。插入或更新字段值的时候，必须为该字段指定一个非空的数据，否则会出现插入或更新失败。

语法：

create table 表名(
    列名 类型 not null,
    ....
);

5、使用唯一性约束

（1）、添加唯一性约束

alter table tableName add unique(column_name)

（2）、查看唯一性约束

show keys from tableName;

（3）、删除唯一性约束

然后可以使用DROP INDEX：
DROP INDEX index_name ON tbl_name
或ALTER TABLE语法：
ALTER TABLE tbl_name DROP INDEX index_name

6、使用默认值约束

“默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。MySQL 默认值约束用来指定某列的默认值。  例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

（1）、添加默认值

<字段名> <数据类型> DEFAULT <默认值>;

（2）、修改默认值

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

（3）、删除默认值

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

7、设置表的属性值自动增加

在MySQL 中 AUTO_INCREMENT 的初始值是1，每新增一条记录，字段值自动加1。一个表只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为主键的一部分。 AUTO_INCREMENT 约束的字段可以是任何整数类型。

字段名 数据类型 AUTO_INCREMENT

8、查看数据表结构

（1）、查看表基本结构语句

DESC  数据表；       

（2）、查看表详细结构语句

SHOW CREATE TABLE 表名\G ;         

注意：如果不加  "\G"  参数，显示的结果可能比较混乱，加上之后，显示的结果更加直接。

三、修改表结构

1、表中添加列

ALTER TABLE table_name ADD column_name datatype;

2、删除表中的列

ALTER TABLE table_name DROP COLUMN column_name;

3、改变表中列的数据类型

ALTER TABLE table_name modify COLUMN column_name datatype;

4、修改字段名

ALTER TABLE table_name CHANGES columns_old columns_new columns_type;

5、修改字段长度

ALTER TABLE table_name MODIFY COLUMN column_name  new_columntype;

6、删除字段

ALTER TABLE database_name DROP column_name;

7、修改表名

ALTER TABLE new_tablename RENAME TO old_tablename;

8、重命名表

RENAME TABLE table_Oldname TO table_Newname;

9、复制表

CREATE TABLE newtable_name LIKE table_name;

10、复制表中的内容

CREATE TABLE newtable_name SELECT*FROM table_name;

11、删除表

（1）、删除没有被关联的表

drop table 表名；

（2）、删除被其他表关联的表

将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

五、插入数据

1、插入完整数据

（1）、使用INSERT INTO……VALUES语句插入   

INSERT INTO Table_nmae VALUES(field1、field2、field3、……);

（2）、使用INSERT……SET语句插入

INSERT INTO tavke_name SET column_name = value,column_name2 = value2,……;

2、插入数据记录的一部分

INSERT INTO table_name values(column_name1 attribute);

3、插入查询结果

INSERT INTO data_name.insert_name SELECT field= value FROM query_table;