创建数据表:create table 数据表名
1.创建表规范
create table 表名(
列名 数据类型 是否为空 自动排序/默认值 主键/外键/唯一键,
列名 数据类型 是否为空 默认值 外键/唯一键
) ENGINE=InnoDB default charset=utf8;
强制创建数据库:create database if not exists 数据库名;
数据类型不用再说了
是否为空 not null/null
- not null:表示不能为空
- null:表示可以为空
默认值 default
default XX:表示此列的数据默认为XX
自动排序 auto_increment:表示根据列插入数据的先后顺序自动排序
注意:自动排序和默认值不能同时设定在一个列上,并且自动排序必须是主键/唯一键其中一个
主键:primary key
一张表只能有一个主键,主键是唯一不重复,并且不能为null,所以一般直接把自动排序列设置为主键
唯一键:unique key
唯一键,顾名思义,唯一不重复,但可以为null,但一张表可以有多个唯一键
外键:foreign key
如果一张表中有一个非主键的字段指向了别一张表中的主键,就将该字段叫做外键。
一张表中可以有多个外键,用于将当前表以设定的该列联系其他数据表
1)创建外键
首先需要两张表,先创建一个part部门表,此时的部门表暂且认定为主表
插入一些数据得:
再创建一个user员工表,此时的员工表设定为从表,在创建员工表时,把part_id作为foreign key,以此作为部门表的联系。
注意:外键设置都设置在从表上,子表(从表)的外键必须和父表(主表)的关联主键类型一致,不然报错
插入数据,如果插入的数据不在外键约束好的数据内,则会报错:
这就是外键的功效。具体在什么时候使用呢?就是在表与表之间有联系且不能有差异时,可以使用外键
2)删除外键:先删除外键名,再删外键字段
3)删除带有外键的数据表:先删除从表,再删除主表
4)外键约束操作:
cascade创建:当父表的id某字段删除时,子表也跟着删除
mysql> create table users1(
-> id smallint unsigned primary key auto_increment,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key (pid) references provinces(id) on delete cascade #依据provinces表的外键约束方式cascade
5)约束:
主键,唯一键,外键,默认值的功效都是约束的作用
ENGINE
就是数据库引擎,mysql默认支持的数据引擎:INNODB,BERKLEY,ISAM,MYISAM,HEAP
最常用的就InnoDB和Myisam
- Myisam:支持全文索引,查询数据时老快了
- Innodb:支持事物,原子性操作
原子性操作:设置和修改数据只有两种结果,要嘛成功要嘛不成功,设置中途如果出错,所有相关数据会回滚到之前未操作的状态
default charset = utf8:创建表时设定此表的字符编码为utf8
查看当期数据表的编码等信息:show create table 表名;/show full columns from 表名;
2.范例
1)一般创建法
2)也可以设置好后在后面插入,并且主键可以设置复合键,处的id和name复合在一起同属一个主键
注意:在创建表时最后一行不加逗号,不然报错
插入数据:
注意:当设置有自动排序时,插入数据不需要再给定列名和数据,它会自动生成
3.删除数据表:drop table 数据表名
4.查看数据
1)数据表信息:show columns from 数据表名;
2)查看表数据:select × from 表名;
如果数据很多,加上‘\G’表示以网格的形态呈现索引:
5.修改表:alter table 数据表名
1).添加
1)添加列: alter table 表名 add 列名 数据类型;
2)添加主键:alter table 表名 add primary key(列名);
3)添加外键:alter table 表名 add constraint 外键名称(随意,注意顾名思义) foreign key 从表(外键字段) references 主表(主键字段)
4)修改数据库编码:alter database 库名 character set=utf8;
5)修改数据表编码:alter table 表名 charset= utf8;
2).重设
1)重设默认值:alter table 表名 alter 字段 set default 值;
2)重设列:
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名,类型
注意:当把数据类型由大类型改为小类型,有可能数据会丢失
3).删除
1)删除列:alter table 表名 drop 列名
2)删除多列:aler table 表名 drop 列名,列名,列名
3)删除外键:alter table 表名 modify 列名
4)删除默认值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5)删除的同时新增一列:注意用逗号隔开就行
6)删除主键:
注意:一个表只能有个一个主键,所以删除主键时可以不用给列名。
修改列名时,如果列名是主键约束并且自动排列,直接修改会报错:
原因在于mysql不能单独定义两个主键
正确步骤:
a.先删除自增长(此时可以改名)
b.再删除主键:
c.再重新设置列定义:
此时的unsigned表示无符号的意思,也就是非负数,只用于整型
6.更新数据:updata table 表名 set 列名 = 值
表数据操作
1.清空表数据:
- delete from 数据表名,逐条删除(速度较慢)
- truncate table 数据表名,整体删除(速度较快)
- DELETE FROM 数据表名 WHERE……,删除某一列满足条件的数据
2.对表数据增删改查:
1)增:
insert
into
表 (列名,列名...)
values
(值,值,值...)【插入单条数据】
insert
into
表 (列名,列名...)
values
(值,值,值...),(值,值,值...)【插入多条数据】
insert
into
表 (列名,列名...)
select
(列名,列名...)
from
表【把查询到的其他表数据插入】
![](https://i-blog.csdnimg.cn/blog_migrate/ccfe7acbf4dd10268dec00bb07ac28b7.png)
delete
from
表【删除/清空数据】
delete
from
表
where
id=1
and
name
=
'字段' 【删除某一段符合条件的数据】
update
表
set
列名与字段关系(如name ='test')
where
列名与字段关系(如id >4)
select
*
from
表【查询表的所有数据】
select
*
from
表
where
... 【查询满足条件的字段数据】
select
列名
as
..
from
表 ... 【查询满足条件的表中所有数据】
a、条件 where
select
*
from
表
where
id > 1
and
name
!=
'alex'
and
num = 12;
select
*
from
表
where
id
between
5
and
16;
select
*
from
表
where
id
in
(11,22,33)
select
*
from
表
where
id
not
in
(11,22,33)
select
*
from
表
where
id
in
(
select
nid
from
表)
b、通配符
*
select
*
from
表
where
name
like
'ale%'
- ale开头的所有(多个字符串)
select
*
from
表
where
name
like
'ale_'
- ale开头的所有(一个字符)
c、限制 limit
select
*
from
表 limit 5; - 前5行
select
*
from
表 limit 4,5; - 从第4行开始的5行
select
*
from
表 limit 5 offset 4 - 从第4行开始的5行
d、排序 order by
select
*
from
表
order
by
列
asc
- 根据 “列” 从小到大排列
select
*
from
表
order
by
列
desc
- 根据 “列” 从大到小排列
select
*
from
表
order
by
列1
desc
,列2
asc
- 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
e、分组
group by /having
使用having,必须保证分组条件为聚合函数或者这个字段必须出现在当前select语句中
聚合函数:max,min,average,sum,count等永远只有一个返回结果的函数
select
num
from
表
group
by
num
select
num,nid
from
表
group
by
num,nid
select
num,nid
from
表
where
nid > 10
group
by
num,nid
order
nid
desc
select
num,nid,
count
(*),
sum
(score),
max
(score),
min
(score)
from
表
group
by
num,nid
select
num
from
表
group
by
num
having
max
(id) > 10
特别的:
group
by
必须在
where
之后,
order
by
之前
f、连表
![](https://i-blog.csdnimg.cn/blog_migrate/7472b5e2b7cdbcb39df84a5fdec9a805.png)
内连接:
左外连接:
右外连接:
全连接,左右连接合集:
外连接:
自连接:同一个数据表对自身连接,但必须有一个别名区分
多表删除:
无对应关系则不显示
select
A.num, A.
name
, B.
name
from
A,B
Where
A.nid = B.nid
无对应关系则不显示
select
A.num, A.
name
, B.
name
from
A
inner
join
B
on
A.nid = B.nid
A表所有显示,如果B中无对应关系,则值为
null
select
A.num, A.
name
, B.
name
from
A
left
join
B
on
A.nid = B.nid
B表所有显示,如果B中无对应关系,则值为
null
select
A.num, A.
name
, B.
name
from
A
right
join
B
on
A.nid = B.nid
g、组合
组合,自动处理重合
select
nickname
from
A
union
select
name
from
B
组合,不处理重合
select
nickname
from
A
union
all
select
name
from
B
6.使用any,some,all,in,not in,exists,not exists关键词操作
使用any参数:
7.mysql也支持比较运算符
参考自:http://www.cnblogs.com/wupeiqi/articles/5713315.html