必会单词

create 创建
insert 插入
primary key 主键
alter 修改
change 改变/修改
rename 重命名
modify 修改
character 字符
engine 引擎
table 数据表
database 数据库
foreign key 外键
update 更新
delete 删除
drop 扔掉/删除
show 展示
unique 唯一

数据库四门基本功课:`增删改查`

创建数据库

创建数据库的语法

create database db_name character set character_name

db_name :表示所要创建的数据库的名称。
character_name :表示数据库的字符集。

『示例』创建一个名为 bookshop 的数据库,并设置其字符集为gbk。

create database bookshop character set gbk;

结果:

mysql> create database bookshop character set gbk;
query ok, 1 row affected (0.00 sec)

操作数据库

查看数据库

成功创建数据库后,可以使用show命令查看 mysql 服务器中的所有数据库信息

show  databases;

『示例』show命令査看 mysql 服务器中的所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bookshop           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

选择数据库

虽然成功创建了数据库,但并不表示当前就在操作操作库 bookshop 。可以使用 use 语句选择一个数据库。

use db_name;

例如,选择名称为 bookshop 的数据库,设置其为当前默认的数据库,命令执行结果如下所示

mysql> use bookshop;
Database changed

修改数据库

数据库创建后,如果需要修改数据库的参数,可以使用 alter database 命令。语法如下:

alter database db_name character set character_name;

『示例』将数据库 bookshop所用的字符集修改成 gb2312。

alter database bookshop character set gb2312;

删除数据库

删除数据库的操作可以使用 drop database 语句,语法如下:

drop database db_name;

例如,通过 drop database 语句删除名称为 bookshop 的数据库,如下所示。

mysql> drop database bookshop;
query ok, 0 rows affected (0.03 sec)

数据库存储引擎

数据库存储引擎是数据库底层软件组件,数据库管理系统( dbms ) 使用数据引擎进行创建、查询、更新和删除数据操作。
目前, mysql的存储引擎至少10种,使用 mysql命令“ show engines; ” 即可查看mysql服务实例支持的存储引擎。

如下所示。

innodb存储引擎

支持自动增长列。
支持外键完整性约束。
存储格式

myisam存储引擎

myisam存储引擎的文件类型有三个。
- .frm 文件存储表定义。
- 数据文件的扩展名为 .myd (mydata)。
- 索引文件的扩展名是 .myi (myindex)。
基于 myisam 存储引擎的表支持3种不同的存储格式, 分别是
- 静态型
- 动态型
- 压缩型

memory存储引擎

memory表的大小是受到限制的。
memory不支持varchar、blob、和text数据类型,因为这种表类型按固定长度的记录格式存储

如何选择存储引擎

掌握数据表的基础知识

表的定义

表是包含数据库中所有数据的数据库对象。
在表中,数据成二维行列格式,每一行代表一个唯一的记录,每一列代表一个域。

列名

列名是用来访问表中具体域的标识符,列名必须遵循下列规则:

列名是可以含有从1到128的asc码字符,它的组成包括字母、下划线、符号以及数字
不要给列名命名为与sql关键字相同的名字,比如 select, in, desc等。
列名应该反映数据的属性。

数据类型

数值类型

整数数据类型
- 整数数据类型包括 bigint、int、 mediumint、 smallint和 tinyint
小数数据类型
- 小数数据类型包括 decimal、 numeric两类。从功能上说两者完全等价,两者的唯一区别在于 decimal不能用于带有 indentity 关键字的列
- 声明小数型数据的格式是 numeric | decimal(p[,s]),其中p 为精度,s为小数位数,s的默认值为0
浮点数据类型
- 浮点型也称近似类型。这种类型不能提供精确表示数据的精度,使用这种类型来存储某些数值时,有可能会损失些精度。

字符串类型

常用的字符串类型主要包括char、 varchar、 binary、 varbinary 等类型。

日期时间类型

在 mysql中,日期时间类型包括 datetime、time、timestamp、date等。

enum类型和set类型
- 所谓枚举类型enum,就是指定数据只能取指定范围内的值。
- 对于可以选取多个值的字段,可以选择set类型,例如,“爱好”字段就可以选择set类型,因为可能有多种爱好
text类型和blob类型
- text类型存储只能存储字符数据。而blob类型可以用于存储二进制数据。
- text类型包括 tinytext、text、 mediumtext和 longtext
长度
- 给列定义的大小部分指的是该列能接受多少个字符,比如char允许用户只输入一个字符,而另一些则不允许这些做,所以建议使列值尽可能小,显示列越小,表所占的空间也就越少。
- 但是还有一个问题就是如果减少列的大小, mysql将用截断数据以满足新的大小尺寸,所以很可能丢失有价值的信息数据。

管理网上书城系统数据库

表的设计步骤

表的命名方式。
表中每一列的名称、数据类型及其长度。
表中的列是否允许空值, 是否唯一, 是否要进行默认设置或添加用户定义约束。
表间的关系, 即确定哪些列是主键, 哪些是外键

使用sql语句创建数据表

语法

create table tb_name(
    column name1 datatype[列级别约束条件],
    column name2 datatype[列级别约束条件]
    ...
    [表级别约束条件]
);

创建如下数据表的话

实际代码

create table customers(
    cid char(6) not null primary key,
    ctruename varchar(50),
    cpassword varchar (50),
    csex char (2),
    caddress varchar(50),
    cmobile varchar(11),
    cemail varchar(50),
    cregisterdate datetime
)engine=innodb;

数据完整性约束

数据完整性概述

什么是数据完整性?

存在你数据库里的数据, 就要符合你数据库的规矩

在 mysql中可以通过 primary key约束、foreign key约束、 unique、空值约束和默认值约束等来实施数据完整性

实体完整性(每行数据都不一样)
域完整性(数据大小在字段允许的范围内)
引用完整性约束(外键和对应主键的值要一致)
自定义完整性(自己定义的一些规则)

主健约束

主键(primary key)约束是使用最为频繁的约束它的作用是唯一标记一条数据

所以:

主键不能为null
主键不能重复
用过的主键, 即便删除来这条记录, 最好也不要再用
主键不能修改

总结起来就是四不

不为空, 不相同, 不重用, 不改动

单字段主键

『示例』顾客信息表 customers中需要以“顾客编号”作为顾客的唯一标识,在创建数据表 customers时,为cid列设置primary key约束,由于单列组成主键,故该主键可以定义为列级主键。

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null,
    caddress varchar (50) null,
    cmobile varchar (11) not null,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

多字段主键

『示例』如果在订单详情表中不设置编号,则可以将表中“订单编号 + 商品编号”作为订单详情的唯一标识,在创建数据表 orderdetails时, 为oid和gid的组合设置 primary key约束。

create table orderdetails(
    oid char(14) not null,
    gid char(6) not null,
    odprice float not null,
    odnumber int not null,
    primary key (oid, gid)
);

外健约束

外键( foreign key )约束标识表之间的关系,用于强制参照完整性,为表中一列或者多列数据提供参照完整性。

简单说, 外键是连接另一个表的锚点

创建外键的基本语法如下:

[constraint<外键名>] foreign key 列名1 [, 列名2, ...] references <主键表> 主键列1 [主键列2...]

看着挺唬人, 其实很简单, 看个例子

『示例』在创建数据表订单表 orders时,表中的cid(客户编号)引用了客户表 customers中的客户编号,需要建立 orders表和 customers 表之间的关系。其中cid为关联列, customers 表为主键表, orders表为外键表。

create table orders(
    oid char(14) not null primary key,
    cid char(6) not null,
    odate datetime not null,
    osum float not null,
    ostatus char(1) not null,
    foreign key(cid) references customers(cid)
)engine=innodb;

唯一约束

『示例』为了保证客户信息表表 customers中的手机号不重复,在创建数据表 customers时,为 cmobile设置 unique约束

-- 如果存在就删除, 防止重复建表而报错
drop table if exists customers; 

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null,
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

默认值约束

『示例』在创建客户信息表 customers时,为用户性别csex 字段设置为默认值“男”。

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null default '男',
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

非空约束

非空值约束限制一列或多个列的值不能为空(null) 空表示未定义或未知的值。
在默认情况下,所有列都接受空值, 若要某列不接受空值, 则可以在该列上设置not null约束。
null值既不等价于数值型数据0, 也不等价于字符型数据中的空串, 只是表明字段值是未知的

使用sql语句操作数据表

修改数据表

如需在表中添加列，请使用下面的语法:

alter table table_name add column_name datatype;

『示例』在客户表中新增一个客户昵称字段, 变长11位

alter table customers add cnickname varchar(11);

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

alter table table_name drop column column_name

『示例』删除用户昵称字段

alter table customers drop cnickname

要改变表中列的数据类型，请使用下面的语法：

alter table table_name modify column column_name datatype

『示例』假设已经在数据库 bookshop中创建了表customers。要把字段 csex 的数据类型改为int类型。

use bookshop;
alter table customers modify csex int not null;

『示例』假设已经在数据库 bookshop中创建了表 customers。添加一个新的字段 state,类型为cha(1),不允许为空,将字段 address列删除。

use bookshop;
alter table customers add state char() not null, drop column address

修改表名

用 rename table 语句来更改表的名字。其语法形式如下

rename table tb_name to new_tb_name

『示例』假设数据库 bookshop 中存在 customers 表,将 customers 表重命名为 customers2 表。

use bookshop;
rename table customers to customers2;

删除数据表

删除表是指删除数据库中已存在的表。在 mysql 中通过 drop table 语句来删除表。语法如下:

drop table tb_name;

『示例』假设存在 customers,删除 bookshop数据库中 customers 数据表。

use bookshop;
drop table customers;

管理数据表数据

插入记录

为表的所有字段插入数据

使用基本的 insert语句插入数据要求指定表名称和插入到新记录中的值。基本语法格式为
```
insert into tb_name (col_list) values (val_list);
insert into tb_name values (val_list);
```
『示例』向 bookshop数据库的表 customers插入如下的数据。

 ```msyql
 insert into customers
      ( cid, 
        truename, 
        cpassword, 
        csex, 
        address, 
        cmobile, 
        cemai, 
        cregisterdate )
 values
      ( 'c0011', 
        '李 平', 
        '123456', 
        '女', 
        '广东中山市', 
        '1351543876', 
        'liping163.com', 
        '2009-9-6' );
 ```

为表的指定字段插入数据

『示例』新顾客信息录入,顾客信息地址 address和电子邮箱cemail尚缺,只能将该顾客的部分信息如表所示添加到 customers表中。

 ```sql
 insert into customers
( cid, truename, password, csex, cmobile, cregisterdate )
 values
( c0012, 张先明, 23456, 男, 13513452312, 2009-10-6 );
 ```

同时插入多条记录

『示例』如果想一次性插入多条, 可以这样...

use bookshop;
insert into customers values
    (
       'c0013', 
       '韩志国', 
       '123456', 
       '男', 
       null, 
       '13512134256', 
       'hanzo', 
       'al63com', 
       '2010-1-16'
       ), (
       'c00141', 
       '张小明', 
       '123456', 
       '男', 
       null, 
       '13613434256', 
       'zma163.com', 
       '2010-2-24');

修改记录

修改表中的全部数据

『示例』修改商品信息表 goods中的数据,将全部书籍的价钱九折出售。

update goods;
set price = price * 0.9;

根据条件修改表中的数据

『示例』修改商品信息表gods中的数据,将书籍存量小于100本的图书再增加50本。完成语句如下所示。

update goods;
set number = number + 50 where number <= 100;

删除记录

根据条件删除表中的数据

『示例』商品编号为“010001”的商品已售完,并且以后不考虑再进货,需要在商品信息表中清除该商品的信息。完成语句如下所示

delete from goods where gid=010001;

删除表中的全部数据

『示例』删除商品信息表中的所有信息。完成语句如下所示。

delete from goods;

切记, update和delete如果没有where条件, 后果会很严重...

快速跳转