Mysql数据库管理（你想要的都在这里！！）

一、MySQL数据库管理

1.1、库和表

数据库–>数据表–>行（记录）：用来描述一个对象的信息

列（字段）：用来描述对象的一个属性

1.2、常用的数据类型

int	整型
float	单精度浮点 4字节32位
double	双精度浮点 8字节64位
char	固定长度的字符类型
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位

二、查看数据库结构

2.1、查看当前服务器中的数据库

show databases;#大小写不区分，分号“;”表示结束

2.2、查看数据库中包含的表

use mysql;
show tables;

2.3、查看表的结构（字段）

use 数据库名;
describe [数据库名.]表名;
可缩写成：desc 表名;
或者
desc 数据库名.数据表名;

三、SQL语句

作用：SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能

3.1、SQL语句分类：

语句	代表的意思
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等
DML	数据操纵语言，用于对表中的数据进行管理
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限

四、创建及删除数据库和表

4.1、创建新的数据库

create database 数据库名;

4.2、创建新的表

create table 表名 (字段1 数据类型,字段2 数据类型,...);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

例：

create database db;
use db;
create table test1 (id int(4) not null,name char(10),age int(3),primary key(id)) ;

4.3、删除指定的数据表

drop table  [数据库名.]表名;
#如不用USE进入库中，则需加上数据库名

4.4、删除指定的数据库

drop database 数据库名;

五、管理表中的数据记录

5.1、向数据表中插入新的数据记录

语法说明：

insert into 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);
insert into 表名 values (字段1的值,字段2的值,...);

例：

insert into test1 (id,name,score,passwd) values (1,'zhangsan','70.1',password('111111'));
#PASSWORD('111111')：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示。
insert into test1 values(2,'lisi',90.1,'22222222');　　

5.2、查询数据记录

select  字段名1,字段名2[,...] from 表名 [where 条件表达式];

例：

SELECT * FROM test;
SELECT ``id``,name,score FROM ab WHERE ``id``=2;` `
select` `name from ab\G 
``#以列表方式竖向显示``select` `* from ab limit 2;  ``#只显示头2行``select` `* from ab limit 2,3; ``#显示第2行后的前3行

5.2.1 查询全部数据

语法说明：

select * from test1;

5.2.2 查询指定数据

语法说明：

select id,name from test1 where id=2;

5.2.3 以列表方式竖向显示

select * from test1;

5.2.4 显示指定行内容

语法说明：

select * from test1 limit 2;
select * from test1 limit 2,2;
select * from test1 limit 3,2;
#limit 2表示显示前两行
#limit 2,2表示显示第2行后面的两行，也就是3，4行
#limit 3,2表示显示第3行后面的两行，也就是4，5行

5.3、修改、更新数据表中的数据记录

语法说明：

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];

例：

update test1 set score='100.0' where id=6;

5.4、在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式]; 

例：

delete from test1 where id=6;

六、修改表名和表结构

6.1、修改表名

rename table 旧表名 to 新表名;
或者
alter table 旧表名 rename 新表名;

例：

rename table test1 to test;
或者
alter table test rename test1;

6.2、扩展表结构（增加字段）

alter table 表名 add address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

例：

alter table test1 add address varchar(50) default '地址不详';

6.3、修改字段（列）名，添加唯一键

语法说明：

alter table 表名 change 旧列名 新列名  数据类型 [unique key] ;

例：

alter table test1 change name username varchar(10) unique key ;#change可修改字段名、数据类型、约束等所有项

6.4、删除字段

语法说明：

alter table 表名 drop 字段;

例：

alter table test1 drop address;

6.5、使用 if 判断 创建表并测试自增和填充

案例

use db;
create table if not exists info (
id int(4) zerofill primary key auto_increment,  #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));　　

分析：

#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL

测试：创建数据测试自动补齐和自增长

insert into info (id,name,cardid,hobby) values(1,'张三',18,'篮球');

注意：不过如果中间有一行记录被删掉的话，可以只定id添加一行记录

七、数据表高级操作

7.1．克隆表，将数据表的数据记录生成到新的表中

方法一：先复制表再导入数据

语法说明：

#只能克隆出他的表结构，里面的数据记录无法克隆下来

#创建一个和旧表的结构相同的表
create table 新表名 like 旧表名;
#查看表中的记录
select * from 新表名 ;
#查看表的结构
desc 新表名 ;
#将旧表中的记录插入到新表中
insert into 新表名 select * from 旧表名;
select * from 新表名;

方法二：复制表的同时导入数据

语法说明：

create table 新表名 (select * from 旧表名);
select * from 新表名;

7.2．清空表，删除表内的所有数据

方法一：delete删除

#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

select * from stu4;
delete from stu4;
select * from stu4;
insert into stu4 (name,sex) values ('老三','男');
select * from stu4;

方法二:truncate删除

说明：

#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

select * from 表名;
truncate table 表名;
#测试 truncate与delete的不同之处，在于如果id是自增的话，用delete删除记录后再添加记录从之前的最大id下一位算起，而
insert into TEST03 (name,cardid) values ('wangwu','33333');
select * from TEST03;

7.3、创建临时表

说明：

#临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。
语法说明：

例：

create temporary table test3 (id int(4) zerofill primary key auto_increment,name char(10) not null,age int(3));
insert into test3 values (1,'张三',19);
create table test4(select * from test3);

7.4、创建外键约束，保证数据的完整性和一致性

说明：

主键表和外键表的理解:
(1)以公共关键字作主键的表为主键表(父表、主表)
(2)以公共关键字作外键的表为外键表(从表、外表)

注意：与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。

创建外键约束的操作步骤：

7.4.1 创建主键表和从表

#创建主表live，定义各字段属性
create table live(id int,city char(10));
desc live;
#创建从表det
create table det(id int,name char(10),sex char(3),age int(3));
desc det;

7.4.2 为主键表添加一个主键约束。主键名建议以“PK_”开头。

alter table live add constraint PK_id primary key(id);
desc live;

7.4.3 为从表添加外键，外键名建议以“FK_”开头，并与主键表建立关联

说明：

#为 det表添加外键，并将 det表的id 字段和 live表的 id 字段建立外键关联。外键名建议以“FK_”开头。

语法说明：

alter table det add constraint FK_id foreign key(id) references live (id);

7.4.4 插入新的数据记录时，要先主表再从表(不按这个顺序的话无法添加数据)

#先主表添加数据
insert into  live values(1,'南京');
#从表添加数据
insert into det values (1,'张三','男',23);

注意：主表添加数据后从表才能添加数据，主表只有一条数据，从表无法添加第两条

7.4.5 删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表

#先删除从表的数据
delete from det where id=3;
#再删除主表的数据
delete from live where id=3;

7.4.6 查看和解除外键约束以及删除外键

show create table det;
#删除外键约束
alter table det drop foreign key FK_id;
#删除外键
alter table det drop  key FK_id;

7.4.7 外键的定义:

如果同一个属性字段x在表一中 是主键，而在表二中不是主键，则字段x称为表二的外键

7.4.8 MySQL中6种常见的约束

约束	说明
primary key	主键约束
foreign key	外键约束
not null	非空约束
unique [key|index]	唯一性约束
default	默认值约束
auto_increment	自增约束

总结：

• 主从表中添加数据的顺序：先再主表中添加，然后再在从表中添加，且外键的字段必须在主表中存在，从表中的记录不能超过主表中的记录数
• 删除数据的顺序时先删除从表中的记录，然后再删除主表中记录
• 没按照上面的执行的话，也没多大问题，就是会报错