数据库之mysql篇(3)—— mysql创建/修改数据表/操作表数据

2018-03-01 07:46:52来源:cnblogs.com作者:yangva人点击

分享

创建数据表: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 表【把查询到的其他表数据插入】 2)删:delete from 表【删除/清空数据】delete from 表 where id=1 and name'字段' 【删除某一段符合条件的数据】 3)改:update 表 set 列名与字段关系(如name ='test') where 列名与字段关系(如id >4)
 4)查:select from 表【查询表的所有数据】select from 表 where ... 【查询满足条件的字段数据】select 列名 as .. from 表 ... 【查询满足条件的表中所有数据】 5)其他: a、条件  where    select from 表 where id > 1 and name != 'alex' and num = 12;     select from 表 where id between 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 limit参数是从0开始计数    select from 表 limit 5;            - 前5行    select from 表 limit 4,5;          - 从第4行开始的5行    select from 表 limit 5 offset 4    - 从第4行开始的5行 d、排序  order by asc  小到大desc  大到小     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、连表       无对应关系则不显示    select A.num, A.name, B.name    from A,B    Where A.nid = B.nid     无对应关系则不显示    select A.num, A.name, B.name    from inner join B    on A.nid = B.nid     A表所有显示,如果B中无对应关系,则值为null    select A.num, A.name, B.name    from left join B    on A.nid = B.nid     B表所有显示,如果B中无对应关系,则值为null    select A.num, A.name, B.name    from 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

 

微信扫一扫

第七城市微信公众平台