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

2022-10-23 13:36:31

创建数据表：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 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

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、连表