学习MYSQL的笔记分享(一)——数据的创建与使用

Valky
阅读26 喜欢0 教程 更新2019-11-26

什么是数据? 

存储在某种介质上能够识别的物理符号。如文字,图像,声音,视频等。 

什么是数据库?

保存在磁盘外存介质上的数据集合,能被各类用户所共享,数据冗余低,按一定的数据模型组织、描述和存储,较高的数据独立性和易扩展性。

什么是数据库管理系统?

用户与操作系统之间的数据管理软件。如ACCESS,MTSQL等,是数据库系统的核心。 功能如下: - 数据定义 - 数据操纵 - 数据库的运行管理 - 数据库的建立与维护 - 提供方便、有效存取数据库信息的接口和工具

什么是数据库系统?

引入数据库后的计算机系统,是一个集合体,包括:应用系统、数据库,数据库管理系统,应用开发工具,数据库管理员和用户。 特点: - 数据结构化 - 数据独立性好 - 数据共享性好 - 数据冗余度小 - 数据由DBMS统一管理和控制

三级模式结构:

模式:是对数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共视图,也叫逻辑模式或概念模式。 外模式:也叫子模式或用户模式,是对数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据逻辑表示,一个数据库可以有多个外模式 内模式:也叫存储模式,是对数据库中的数据物理结构和存储方式的描述,是数据在数据库内部的表现形式。一个数据库只有一个内模式。 三者间的关系:模式是内模式的逻辑表示,内模式是模式的物理实现,外模式是模式的部分抽取。

外模式/模式映像:

定义各个外模式与概念模式之间的映像关系,体现数据的逻辑独立性。

模式/内模式映像:

定义数据库全局逻辑结构与存储结构之间的对应关系,体现数据的物理独立性。

数据库系统的运行与应用结构

客户/服务器结构 C/S 分为单机方式和网络方式。 浏览器/服务器结构 B/S 将客户端分为“表示层”和“处理层”,“表示层”通常为浏览器。

数据模型 物理层(物理数据模型) 逻辑层(数据类型:层次类型、网状类型、关系模型、面向对象模型) 概念层(概念模型:实体-联系模型) 实体:客观存在并互相区别的事物 属性:实体的特征 实体型:属性的集体表示一种实体的类型 实体集:性质相同的同类实体的集合 码:唯一标识实体的属性集 域:属性的取值范围 联系:实体内部的联系和实体之间的联系 - 一对一 - 一对多 - 多对多


关系模型-用二维表表示 

关系:一个“表”就是一个关系,即一个二维表。 元组:一个二维表中,水平方向的行称为元组,每一个行是一个元组。 属性:二维表中垂直方向的列称为属性。 域:属性的取值范围。 分量:元组中的一个属性值 关系模式:对关系的描述,表示为”关系名"(属性1、2...n),MYSQL中的表:"表名"(字段1、2...n)。 表:由行和列组成。 行:表中的一条记录。 列:表中的一个字段。 主键:其值能够唯一标识一个元组的属性或属性的组合,也叫主码或主关键字。 外键:表中的一个字段不是本表的主关键字,而是另外一个表的主关键字,这个字段就称为外关键字。 数据类型:所允许的数据类型。

为了减少数据库中的数据冗余,增强数据的易操作性,消除数据插入、删除异常等现象,关系模型要求关系必须规范化。 第一范式(1NF),无重复的列,某属性不能有多个值或者不能有重复的属性。 第二范式(2NF),满足1NF,没有部分函数依赖,属性完全依赖于主键,每个实例或行必须可以唯一区分。 第三范式(3NF),满足2NF。没有传递函数依赖,依赖于其它非主属性,即要求一个数据库表中不包含其它表中已包含的非主关键字信息。 BNCF,符合3NF,表中不存在主键列对主键的部分函数依赖和传递函数依赖。 数据库设计 六个阶段: - 需求分析 - 概念结构设计 将用户需求抽象为信息结构,是整个数据库设计的关键,通常使用E-R图来描述现实世界的概念模型。

逻辑结构设计 把E-R图转换为关系模型,对其性能进行优化,逻辑模式规范化及调整、现实,根据需要设计一个符合局部用户需要的用户外模式。 - 物理结构设计 确定数据库的物理结构、对物理结构进行评价 - 数据库实施 - 数据库运行与维护 MYSQL系统特性 环境:LAMP、WAMP 安装配置数据库 MYSQL语言组成 数据定义语言(DDL),对数据库及数据库中的各种对象进行创建、删除、修改等操作:create、alter、drop 数据操纵语言(DML),操纵数据库中各种对象,主要是检索和修改数据:select、insert、update、delete 数据控制语言(DCL),用于安全管理:grant、revoke MYSQL扩展增加的语言要素 常量 变量:用户变量前添加一个@,系统变量前添加两个@ 运算符:算数运算符、位运算符 表达式 函数 数据库的创建与使用


CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name [DEFAULT]CHARACTER SET [=] chartes_name | [DEFAULT] COLLATE [=] chartes_name

1.创建一个名为mysql_test的数据库
CREATE database mysql_test;
2.选择数据库
USE db_name;
3.修改数据库 修改数据库mysql_test默认字符集为gb2312,校对规则为gb2312_chinese_ci
mysql>ALTER database 
->DEFAULT CHARACTER SET gb2312
->DEFAULT COLLATE gb2312_chinese_ci;
UPDATE DATA DIRECTORY NAME
4.删除数据库
DROP DATABASE IF EXISTS mytest;
注意:information_schema和mysql两个系统数据库是系统自动创建,勿删。 5.查看数据库
SHOW DATABASES;//列出当前可查看数据库
SHOW DATABASES LIKE "my%"; //列出一my开头的数据库

创建和操纵表 1.创建表 a.表创建基础 TABLE_name:表名需符合规则 表创建定义(create_definition),每列定义以列名开始跟列的数据类型以及可选参数,在圆括号里写出完整的字段,字段名间用逗号隔开,一;结束。 TEMPORARY:临时表 IF NOT EXISTS子句:建表前判断,避免出现表名与已经存在无法再建的错误 b.常用数据类型:INT、DATE、DATETIME、CHAR、 VARCHAR、TEXT

use mysql_test;
create tablecustomers(
        cust_id INT NOT NULL AUTO_INCREMENT,
        cust_name char(50) NOT NULL,
        cust_sex chhar(1) NOT NULL DEFAULT 0,
        cust_address char(50) NULL,
        cust_contact char(50) NULL,
        PRIMARY KEY(cust_id)        //主键
)ENGINE=InnoDB;   //使用InnoDB作为数据库引擎

CREATE DATABASE db_test;
USE db_test;
CREATE TABLE content(
    content_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject varchar(200),
    words varchar(1000),
    username varchar(50),
    face varchar(50),
    email varchar(50),
    createtime datetime
);

2.更新表 增加新列ADD语句,向数据库mysql_test的表customers中添加一列名为cust_city要求不能为空,默认值为whhan,且位于cust_sex列之后(在数据库中直接写表名)

ALTER TABLE mysql_test customers ADD COLUMN cust_city char(10) NOT NULL DEFAULT "wuhan" AFTER cust_sex;
ALTER TABLE mysql_test.customer CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT "M";
//数据库mysql_test的表customers中的cust_sex更改为sex,且数据类型更改为char(1),允许其为,默认值为"M"
ALTER TABLE mysql_test.customers ALTER COLUMN cust_city SET DEFAULT "BEIJING";
//修改或删除表中的默认值
ALTER TABLE mysql_test.customer MODIFY COLUMN cust_name char(20) FIRST; //设置成表的第一列
ALTER TABLE mysql_test.customer MODIFY COLUMN cust_name char(20) AFTER cust_email; //设置到cust_email之后
ALTER TABLE  mysql_cust.customers DROP COLUMN cust_contact; //删除列的同时删除列的所有内容
3.重命名表
ALTER TABLE mysql_test.customers RENAME TO mysql_test.backup_customers;
RENAME TABLE mysql_test.backup_customers TO mysql_test.customers;
//表的重命名
4.复制表
 CREATE TABLE mysql_test.customers_copy LIKE mysql_test.customers;
 //LIKE关键字只创建与原表结构相同的表,不复制表内容;AS(select_statement)可复制表内容,但索引和完整性约束不复制
5.删除表
DROP TABLE mysql_test.customers_copy;
6.查看表 a.显示表
SHOW TABLES;
b.显示表的结构
SHOW COLUMN {FROM|IN}tbl_name [{FROM|IN} db_name] 
DESC tbl_name
DESC mysql_test.customers;//查看数据库中表的结构

7.对数据结构进一步说明 a.关于空值(NULL),不可与数字0或字符类型的空字符混为一谈,表的关键字不允许为空值,任意两个空值不相等。 b.关于列表的标志(IDENTITY)属性,每个表只能有一个列设置为标志属性,不允许为空值,可作为键值。 c.关于列的类型的隐含改变 MYSQL中使用SQL语句插入、删除和修改表数据的方法 一、插入数据 1.使用INSERT语句插入表数据

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE][INTO] tbl_name [(col_name,...)]
{VALUES|VALUE}({expr|DEFAULT},...),(...)...
[ON DUPLICATE KEY UPDATE col_name=expr[,col_name=expr]...]

tbl_name:表名 col_name:列名 IDENTITY属性列 默认值列--指关键字DEFAULT 没有默认值,允许空值列--指关键字NULL TIMESTAMP--自动赋值 AUTO_INCREMENT列--表中其他列被赋值自动生成 VALUES或VALUE子句:数据顺序必须与列的顺序相对应 exprs: 一个常量、变量或表达式,NULL,数值类型要与数值类型一致,列为字符型,需用单引号括起来 DEFAULT:已经明确指定了默认值,可以通过它引用

INSERT INTO customers(cust_address,cust_name,cust_sex)
VALUES("武汉市","李四",DEFAULT);

INSERT INTO (cust_address,cust_name,cust_sex)
VALUES("武汉市","李四",DEFAULT),("北京市","F","张三");
//INSERT有多组数据时,每组数据用一对圆括号括起来 
SET
INSERT [INTO] tbl_name SET col_name={expr|default},...
SET子句:给表中某列指定对应列值,未指定列,列值会被指定为该列的默认值
INSERT INTO customers
SET cust_name="李四",cust_address="武汉市",cust_sex=DEFAULT;
SELECT
INSERT [INTO] tbl_name SELECT...
SELECT句:将一个或多个表的数据取出,并插入到另一个表中,字段数、字段的数据类型必须一致
//customers有备份表customers_copy,以INSERT...SELECT语句的形式将customers_copy中
//的数据合并到表customers_copy中的数据合并到表customers
INSERT INTO customers(cust_id,cust_name,cust_sex,cust_address,cust_contact)
SELECT cust_id,cust_name,cust_sex,cust_address,cust_contact
FROM customers_copy;
//注意:列名可以不相同;如果主键值重复,插入数据时省略这个列,检索出的数据行数为0是合法的

2.使用REPLACE语句插入表数据 待插入表存在有PRIMARY KEY 或UNIQUE KEY,如插入数据与原址冲突,将无法插入,可用REPLACE语句替换

REPLACE [INTO] tbl)_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...
REPLACE INTO customers (cust_id,cust_name,sex,cust_address,cust_contact)
VALUES (901,"王五","M","guangzhou","yuexiu")

二、删除表数据 1.使用DELETE语句从单个表中删除数据

DELETE FROM tbl_name [(col_name,...)][WHERE where_condition]
//删除数据库mysql_test的表customers中客户名为‘王五’的客户信息
DELETE FROM customers WHERE cust_name="王五";
//删除数据库中的表customers的所有数据
DELETE FORM customers;

2.使用DELETE语句从多个表中删除数据 假设数据库中的三个表tbl1,tbl2,tbl3,均含有id列,先要求删除表tb1中的id值等于tbl2的id值的所有行,以及表tbl2中的id值等于表tbl3的id值的所有行

//tip1:
DELETE tbl1,tbl2 FROM tbl1,tbl2,tbl3 WHERE tbl1.id=tbl2.id and tbl2.id=tbl3.id;
//tip2:
DELETE  FROM tbl1,tbl USING tbl1,tbl22,tbl3 WHERE tbl1.id=tbl2.id and tbl2.id=tbl3.id;

3.使用TRUNCATE语句删除表的数据 如果删除所有行,使用TRUNCATE语句,执行速度更快。

TRUNCATE [TABLE] tbl_name

注意: - 数据删除后无法恢复; - 使用语句后,AUTO_INCREMENT被充值为初值; - 对于参与了索引和视图的表,不能使用,应使用DELETE语句; - 比DELETE使用的系统和事务日志资源少 实操题目: 使用DELETE语句将数据库db_test的表content中留言人的姓名为"MYSQL初学者"的留言信息删除

DELETE FROM db_test.content WHERE username="MYSQL初学者";
三、修改表数据 1.使用UPDATE语句修改单个表 //使用UPDATE语句经数据库mysql_test的表customers中的姓名为“张三”的客户的地址更新为“武汉市”
UPDATE customers SET cust_address="武汉市" WHERE cust_name="张三";
2.使用UPDATE语句修改多个表
UPDATE tbl1,tbl2 SET tbl1.name="qq",tbl2.name="ww" WHERE tbl1.id=tbl2.id