mysql进入数据库表_数据库（Mysql表操作）

一、mysql存储引擎

1.1 常用引擎：

第一种方式: (Myisam: 是5.5之前默认的存储引擎)

数据存在硬盘上,存三个文件,表结构,数据,和搜索目录

既不支持事务、也不支持外键、不支持行级锁

只支持表锁

对于只读操作比较多的情况 查询速度相对快

第二种方式: (Innodb: 是5.6之后的默认存储引擎)

数据存在硬盘上,存两个文件,表结构,(数据和搜索目录)

支持事务

支持行级锁

支持外键

第三种方式: (Memory)

数据存在内存中,存一个文件,表结构(在硬盘上)

数据容易丢失,但读写速度都快

1.2 存储引擎相关sql语句：

查看当前的默认存储引擎:

mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎

mysql> show engines \G;

View Code

1.3 在建表时指定引擎类型：

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;

mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

也可以使用alter table语句，修改一个已经存在的表的存储引擎。

mysql> alter table ai engine = innodb;

View Code

1.4 配置文件中指定(my.ini)：

#my.ini文件

[mysqld]

default-storage-engine=INNODB

View Code

二、创建表与查询结构

2.1 创建表的语法结构：

#语法：

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);#注意：

1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

View Code

2.2 查看表结构：

describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构

show create table语法。除了可以看到表定义之外，还可以看到engine(存储引擎)和charset(字符集)等信息。(\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。)

#查看表结构：

mysql> describe staff_info; #方式一

mysql> desc staff_info; #方式二

mysql> show create table staff_info\G; #方式三

View Code

三、mysql中的数据类型

3.1 常用数据类型：

mysql中的基础数据类型:

数值类型:

int-->大整数值(4字节)

float-->单精度(4字节)

字符串类型:

char--> 定长字符串(0-255字节)

varchar--> 变长字符串(0-65535字节)

时间类型:

datetime--> 年月日时分秒(YYYY-MM-DD HH:MM:SS)

set和enum类型:

enum-->单选(一次选取一个值)

set--> 多选(一次选择多个值)

View Code

3.2 示例：

#整型与浮点数示例：

i系列：

create table i1(id1 int,id2 tinyint,id3 int unsigned);#tinyint(小整数值:1字节)

create table i2(id1 int(2),id2 int(11)); #对int类型的长度进行的约束无效

浮点数系列 f系列：

create table f1(f float(5,2),d double(5,2),d2 decimal(5,2));

create table f2(f float,d double,d2 decimal);

create table f3(d double,d2 decimal(65,30));#总结：#float精确到小数点后5位#double能多精确一些位数,但仍然存在不精确的情况#decimal默认是整数,但是通过设置,最多可以表示到小数点后30位

整型与浮点数示例

#日期：

#year(类型：now(),2019) # now()表示当前时间

#date(类型：now(),20191010 ,'2019-01-01')

#time(类型：now(),121212,'12:12:12')

#datetime(类型：now(),20191010121212,'2019-01-01 12:12:12')

#timestamp(建议少用：时间戳时间(4字节)快结束了)

#时间示例：

#datetime 能表示的时间范围大 可以为空,没有默认值

#timestamp 能表示的时间范围小 不能为空,默认值是当前时间

create table time1(y year,d date,t time);

insert into time1 values (now(),now(),now());#2018-09-21 | 14:51:51 | 2018-09-21 14:51:51

insert into time1 values (null,null,null); #NULL | NULL | NULL

create table time2(dt datetime,ts timestamp);

insert into time2 values(null, null);#NULL | 2019-04-23 18:15:04

create table time2(dt datetime default current_timestamp,ts timestamp);#人为设置datetime类型的默认值是当前时间

日期类型示例

#字符串:#char 能表示的长度小,浪费存储空间,读写效率快

#定长字符串

#在显示的时候会去掉所有空格显示,对用户的视觉造成欺骗#varchar 能表示的长度大,节省存储空间,读写效率慢

#变长字符串

#varchar(5) 'ab'-->'ab2' 'abc'-->'abc3' 'abcde'-->'abcde5' # 存储样式2表示ab为2个长度

#示例：

create table s1(c char(4),v varchar(4));

insert into s1 values ('ab','ab'); #在检索的时候char数据类型会去掉空格

select length(v),length(c) from s1; #来看看对查询结果计算的长度

select concat(v,'+'),concat(c,'+') from s1; #给结果拼上一个加号会更清楚

insert into s1 values ('abcd','abcd'); #当存储的长度超出定义的长度，会截断

字符串类型示例

#enum和set:

#枚举,单选,且自动剔除不存在的选项

#enum('male','female')

#集合,多选,自动剔除不存在的选项,自动去重

#set('洗脚','洗头','抽烟','喝酒','烫头')

#示例：

create table es(name char(10),sex enum('male','female'),hobby set('洗脚','洗头','抽烟','喝酒','烫头'));

insert into es values('Lisa','male','烫头,抽烟,洗脚,按摩');

insert into es values('Annie','人妖','烫头'); #不存在的选项则无法添加

insert into es values('Andy','male','抽烟,喝酒,喝酒,喝酒') #自动去重

enum和set示例

四、mysql表的完整性约束

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

#字段类型：#设置整形无符号 int unsigned#设置默认值 default#是否可以为空 not null#是否唯一 unique#自增 auto_increment#主键 primary key#外建 foreign key

View Code

示例：

#not null类型示例：#表结构 : id,name,phone,sex

create table stu1(

id int,

name char(12) not null, #不能为NULL

phone char(11),

sex enum('male','female')

);

not null类型示例

#not null + default 类型示例：

create table stu2(

id int,

name char(12) notnull,

phone char(11),

sex enum('male','female') not null default 'male')

not null + default 类型示例

#唯一(unique)示例:#unique只是约束在char数据类型内不能重复,但是不能约束null

id name ident

create table stu3(

id int,

name char(12),

ident char(18) unique

)

唯一(unique)示例

#联合唯一 (unique)示例：#一台机器上跑着多少个服务#把每一个正在运行的应用程序的信息都统计下来

#ip + port#192.168.16.13 mysql 3306#192.168.16.13 kugou 8080#192.168.16.13 flask 5000#192.168.16.15 mysql 3306#192.168.16.16 mysql 3306

create table service(

id int,

ip char(15),

name char(15),

port int(5),

unique(ip,port)#联合唯一

)

联合唯一 (unique)示例

#auto_increment 自增的条件(这一列必须是数字,这一列必须是uniuqe)示例：#userinfo#1,alex,'alex3714'

create table userinfo(

id int unique auto_increment,

name char(12),

password char(32)

)

auto_increment 自增的条件示例

#not null 非空 + unique 唯一 == primary key示例：#登录时候的用户名 一定是唯一的

create table userinfo3(

id int unique,

username char(18) notnull unique,

password char(32),

ident char(18) notnull unique

)

create table pri1(

id1 int uniquenotnull,

id3 int uniquenotnull

)#一张表中只能有一个主键 : 主键从约束的角度上来说 就是非空且唯一的#只不过,非空+唯一可以设置多个字段,但是主键只能给一个表中的一个字段设置

auto_increment= notnull

create table userinfo2(

id int unique auto_increment,

username char(18) notnull unique,

password char(32),

ident char(18) notnull unique

)

not null 非空 + unique 唯一 示例

#主键 primary key :在约束中就是非空 + 唯一 示例：

#一般情况下,我们给id字段设置为主键,不允许一张表不设置主键

create table pri2(

id1 int primary key,

id3 int uniquenotnull

)

create table pri3(

id1 int primary key,

id3 int primary key

)#报错 一张表只能有一个主键

主键 primary key示例

#联合主键 : 约束多个字段各自不能为空,并且联合唯一 示例：

create table pri4(

id1 int,

num int,

primary key(id1,num)

);

联合主键 示例

#外键#创建两张表#

#表2 班级表 cid class_name

create table clas(

cid int primary key,

class_name char(20)

)#表1 学生表 id name class_id

create table stu(

id int primary key ,

name char(18),

class_id int,

foreign Key(class_id) references clas(cid)#设置外键

)#总结：#有外键之后所有的新增和删除都会受到外表的约束#比如#如果新增了一个学生所在的班级不存在,那么不能写入学生#如果删除一个还有学生指向的班级,也不能删除,也不能修改外键指向的键

外键示例

#级联更新 级联删除 示例：

create table stu4(

id int primary key ,

name char(18),

class_id int,

foreign Key(class_id) references clas(cid)

on update cascade on delete cascade#设置联合更新和删除(关联表操作)

)

级联更新 级联删除 示例

五、 修改表结构

5.1 修改表的语法结构：

语法：1. 修改表名

ALTER TABLE 表名

RENAME 新表名;2. 增加字段

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…],

ADD 字段名 数据类型 [完整性约束条件…];3. 删除字段

ALTER TABLE 表名

DROP 字段名;4. 修改字段

ALTER TABLE 表名

MODIFY 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];5.修改字段排列顺序/在增加的时候指定字段位置

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] FIRST;

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

ALTER TABLE 表名

CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;

ALTER TABLE 表名

MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

View Code

5.2 示例

#表重命名

mysql>alter table staff_info rename staff;#删除sex列

mysql>alter table staff drop sex;#添加列

mysql> alter table staff add sex enum('male','female');#修改id的宽度

mysql> alter table staff modify id int(4);#修改name列的字段名

mysql> alter table staff change name sname varchar(20);#修改sex列的位置

mysql> alter table staff modify sex enum('male','female') after sname;#创建自增id主键

mysql> alter table staff modify id int(4) primary key auto_increment;#删除主键，可以看到删除一个自增主键会报错

mysql>alter table staff drop primary key;#需要先去掉主键的自增约束，然后再删除主键约束

mysql> alter table staff modify id int(11);#添加联合主键

mysql>alter table staff add primary key (sname,age);#删除主键

mysql>alter table staff drop primary key;#添加主键id

mysql>alter table staff add primary key (id);#为主键添加自增属性

mysql> alter table staff modify id int(4) auto_increment;

DROP TABLE 表名;#删除表

表修改(alter)示例

create table t(id int unique,name char(10) notnull);#去掉null约束

alter table t modify name char(10) null;#添加null约束

alter table t modify name char(10) notnull;#去掉unique约束

alter table t drop index id;#添加unique约束

alter table t modify id int unique;

alter处理null和unique约束

alter操作非空和唯一

1、首先创建一个数据表table_test：

create table table_test(

`id` varchar(100) NOT NULL,

`name` varchar(100) NOT NULL,

PRIMARY KEY (`name`)

);2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。

先删除主键

alter table table_test drop primary key;

然后再增加主键

alter table table_test add primary key(id);

注:在增加主键之前,必须先把反复的id删除掉。

alter操作主键

创建press表

CREATE TABLE `press` (

`id` int(11) NOT NULL,

`name` char(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ；

创建book表

CREATE TABLE `book` (

`id` int(11) DEFAULT NULL,

`bk_name` char(12) DEFAULT NULL,

`press_id` int(11) NOT NULL,

) ；

为book表添加外键

alter table book add constraint fk_id foreign key(press_id) references press(id);#constraint fk_id (指定外键名，可忽略不写)

删除外键

alter table book drop foreign key fk_id;#fk_id 外键名(可查看： show create table 表名)

为表添加外键

5.3 修改编码类型

#修改字段编码类型

alter table 表名 modify 字段名 字段类型 character set "utf-8"alter table tab1 modify name varchar(30) character set "utf-8"

#修改表编码类型

alter table 表名 default character set "utf-8"alter table tab2 default character set"utf-8"

#修改库编码类型

alter database 库名 default character set "utf-8"alter database db1 default character set"utf-8"

六、多表结构的创建与分析(一对多，多对多，一对一)

6.1 建立表之间 的关系：

#一对多或称为多对一

三张表：出版社，作者信息，书

一对多(或多对一)：一个出版社可以出版多本书

关联方式：foreign key

一对多

=====================多对一=====================create table press(

id int primary key auto_increment,

name varchar(20)

);

create table book(

id int primary key auto_increment,

name varchar(20),

press_id intnotnull,

foreign key(press_id) references press(id)

on delete cascade

on update cascade

);

insert into press(name) values

('北京工业地雷出版社'),

('人民音乐不好听出版社'),

('知识产权没有用出版社')

;

insert into book(name,press_id) values

('九阳神功',1),

('九阴真经',2),

('九阴白骨爪',2),

('独孤九剑',3),

('降龙十巴掌',2),

('葵花宝典',3)

;

一对多示例

#多对多

三张表：出版社，作者信息，书

多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

关联方式：foreign key+一张新的表

多对多

=====================多对多=====================create table author(

id int primary key auto_increment,

name varchar(20)

);#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了

create table author2book(

id intnotnull unique auto_increment,

author_id intnotnull,

book_id intnotnull,

constraint fk_author foreign key(author_id) references author(id)

on delete cascade

on update cascade,

constraint fk_book foreign key(book_id) references book(id)

on delete cascade

on update cascade,

primary key(author_id,book_id)

);#插入四个作者，id依次排开

insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下

egon:

九阳神功

九阴真经

九阴白骨爪

独孤九剑

降龙十巴掌

葵花宝典

alex:

九阳神功

葵花宝典

yuanhao:

独孤九剑

降龙十巴掌

葵花宝典

wpq:

九阳神功

insert into author2book(author_id,book_id) values

(1,1),

(1,2),

(1,3),

(1,4),

(1,5),

(1,6),

(2,1),

(2,6),

(3,4),

(3,5),

(3,6),

(4,1)

;

多对多示例

#一对一

两张表：学生表和客户表

一对一：一个学生是一个客户

关联方式：foreign key+unique

一对一

create table customer(->id int primary key auto_increment,-> name varchar(20) notnull,-> qq varchar(10) notnull,-> phone char(16) notnull->);

create table student(->id int primary key auto_increment,-> class_name varchar(20) notnull,-> customer_id int unique, #该字段一定要是唯一的

-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique

->on delete cascade->on update cascade->);#增加客户

mysql>insert into customer(name,qq,phone) values-> ('韩蕾','31811231',13811341220),-> ('杨澜','123123123',15213146809),-> ('翁惠天','283818181',1867141331),-> ('杨宗河','283818181',1851143312),-> ('袁承明','888818181',1861243314),-> ('袁清','112312312',18811431230)

mysql> #增加学生

mysql>insert into student(class_name,customer_id) values-> ('脱产1班',3),-> ('周末1期',4),-> ('周末1期',5)-> ;

一对一示例

七、设置数据的严格模式

7.1 查看数据库当前模式：

select @@sql_mode

7.2 设置数据严格模式(临时):

#server重启后失效：

set sql_mode="STRICT_TRANS_TABLES"

7.3 not null 不生效：

设置严格模式：

不支持对not null字段插入null值

不支持对自增长字段插入”值

不支持text字段有默认值

直接在mysql中生效(重启失效):

mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效)：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"