八、SQL结构化查询语言

8.1 SQL 介绍

1. SQL是访问和操作数据库的标准语言

2. SQL 能做什么？

• SQL可以对数据库执行查询
• SQL可以从数据库中检索数据
• SQL可以给数据库表插入记录
• SQL可以从数据库表中删除记录
• SQL可以创建新的数据
• SQL可以在数据库中创建新表
• SQL可以在数据库创建存储过程
• SQL可以在数据库中创建视图
• SQL可以设置表、过程和视图的权限

3. 数据的元操作

CRUD操作

• C:create 创建数据
• R:retrieve/read 获取数据/读取数据
• U:update/alter 更新数据/修改数据
• D:delete/drop 删除数据

8.2 数据库的CRUD操作

• 查看所有数据库

show databases;

• 创建数据库---C操作

create database 数据库名 charset 字符集;

• 切换数据库---R操作

use 数据库名;

• 查看当前所选数据库---R

select database();

• 修改数据库---U

alter database 数据库名 [default] character set 字符集 [default] collate 字符序;

示例：

alter database db_test default character set gb2312 default collate gb2312_chinese_ci;

• 查看数据库编码

show create database 数据库名;

• 查看mysql支持的所有编码类型

show character set;

• 删除数据库---D操作

drop database 数据库名;

8.3 表命令

• 查看所有表

show tables;

• 创建表

前提：切换数据库，需要明确在哪个数据库中新建表

CREATE TABLE table_name (  -- table_name 表名   特别说明：数据库名、表名都应该有一意义
        column1_name data_type constraints, -- 列名   类型   属性
        column2_name data_type constraints,
        ....
    )ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 存储引擎   字符集

• 创建一个用户表

CREATE TABLE users ( -- 在DB数据库下新建一个user表
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL, -- null值 表示是一个不确定的值
        birth_date DATE,
        phone VARCHAR(11) NOT NULL UNIQUE
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

主键：primary key  在一个表中至少应该有这样一个字段，这个字段所对应的值一不能有重复，二不能为空，这个字段所对应的值能够起到唯一标识表中每一条记录的作用。

上面的语句创建了一个名为users的表，其中包含5个字段id、name、birth_date和phone。注意，每个字段后面都有一个数据类型声明，表示该字段将存储何种类型的数据，例如：整数、字符串、日期等。

8.4 数据类型介绍

整数型

整形

1.tinyint 示例:性别 男1女0 数据类型选择tinyint

2.decimal 示例:价格 与财务有关的字段设置为该类型

3.mediumint 示例: 博客访问数设置为该类型

4.int 示例:员工的编号、学生学号设置为该类型

5.bigint 示例:网站访问数设置为该类型

浮点型

小数

1.float 示例:float(5，2)范围(-999.99，999.99)桌子的长度、物理测理的度量值

2.double 示例:误差比float低 科学计算、物理测量的值

3.decimal 示例:decimal(7,2) M=7(包含整数部分和小数部分，小数点也要算作1位) D=2 小数点后数字的位数 如某一商品的价格是￥1234.56

文本型和日期型

• 字符串

char(50)宽度是固定若占不完，空格补齐 查询效率高，浪费存储空间

varchar(50)宽度是自动，用多少宽度分配多少宽度，查询效率低，节约空间

text 长文本类型 示例：简历 text类型

• 日期

8.5约束和引擎

MySQL支持的常用约束

• 主键：学生表中的学号、身份证号；课程表中的课程号；在一个表中至少应该有这样一个字段，这个字段所对应的值 一不能有重复，二不能有空间，它能够起到唯一标识表中记录的作用。
• not null 不能空值 示例：某个商品的价格是null,只能说这个商品价格尚未确定，不是免费的
• unique：满足主键条件的字段 设置为nuique的字段，此字段所对应的值是不能有重复
• foreign key外键：在一个表中某一个字段它不是当前这个表的主关键字，但是它确是另一个表的主关键，我们在当前这个表，把此字段称为是当前表的外部关键字。

存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

存储引擎：就是我们对数据进行增、删、改、查或CRUD操作的一种技术方式。

show engines;

• 查看表结构

desc 表名;

• 查看表的创建语句

show create table '表名';

8.6更新约束

• 先创建一个表，除了主键，不加其他约束。

CREATE TABLE users1(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    brith_date DATE,
    phone VARCHAR(11)
)ENGINE=InnoDB DEFNULT CHARSET=utf8;

• 给手机号添加唯一约束

ALTER TABLE users1 ADD UNIQUE (phone);

• 删除唯一约束

ALTER TABLE users1 DROP INDEX phone;

• 给名字添加非空约束

ALTER TABLE users1 modify name VARCHAR(50) not null;

• 删除非空约束

ALTER TABLE users1 CHANGE COLUMN'name' 'name' VARCHAR(50) NULL;

• 给生日添加默认约束

ALTER TABLE users1 ALTER birth_date SET DEFAULT '1992-05-11';

• 制除默认约束

ALTER TABLE users1 ALTER birth_date DROP DEFAULT;

8.7插入数据

语法：

insert into 表名（字段名1，字段名2，....) values(值1，值2，....);

• 缺省插入

示例：

给user表新增一条记录？

insert into user(name,birthday,phone,age) values('老王','1954-10-5','13012345，55);

• 全列插入

给user表再新增一条记录

insert into user values(1000,'老张','1964-10-7',13112345678,60); 
insert into user values(0,'老李','1974-10-7',13212345678,50);

说明：

给表中所有字段插入记录的时候，id自动增长型主键的值从0开始。

不同：

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

查询数据

语法：

select *from 表名; -- 查看表记录

8.8float精度测试

验证float、double、decimal误差

-- 新建测试表 tbl_test
create table tbl_test(
    id int not null primary key auto_increment,     
    price1 float(9,3)not null,
    price2 double(9,3)not null,
    price3 decimal(9,3)not null
)engine=innodb default charset=utf8;