数据库原理--数据库

第1章SQL语句

1.1SQL概述

1.1.1SQL语句介绍

数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数据库的代码。
结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
普通话：SQL99标准，各数据库厂商都遵循的ISO标准。
方言：数据库特有的语法。

1.1.2SQL语句分类

SQL分类：
数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象，也就是创建，修改，删除：数据库，表，列等。关键字：create，alter，drop等

数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等

数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

1.1.3SQL通用语法概览

为了方面我们学习，可以先简单看一眼SQL的通用语法
SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
例如：SELECT * FROM user。
同样可以使用/**/的方式完成注释 – #
MySQL中的我们常使用的数据类型如下

详细的数据类型如下(不建议详细阅读！)
分类 类型名称 说明
整数类型 tinyInt 很小的整数
smallint 小的整数
mediumint 中等大小的整数
int(integer) 普通大小的整数
小数类型 float 单精度浮点数
double 双精度浮点数
decimal（m,d） 压缩严格的定点数 decimal(10,2)
日期类型 year YYYY 1901~2155
time HH:MM:SS -838:59:59~838:59:59
date YYYY-MM-DD 1000-01-01~9999-12-3
datetime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp YYYY-MM-DD HH:MM:SS 1970₀₁01 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型 CHAR(M) M为0~255之间的整数定长 char(10) tom
VARCHAR(M) M为0~65535之间的整数变长 varchar(10) tom
TINYBLOB 允许长度0~255字节
BLOB 允许长度0~65535字节
MEDIUMBLOB 允许长度0~167772150字节
LONGBLOB 允许长度0~4294967295字节
TINYTEXT 允许长度0~255字节
TEXT 允许长度0~65535字节
MEDIUMTEXT 允许长度0~167772150字节
LONGTEXT 允许长度0~4294967295字节
VARBINARY(M) 允许长度0~M个字节的变长字节字符串
BINARY(M) 允许长度0~M个字节的定长字节字符串

1.2DDL之数据库操作：database

DDL(Data Definition Language) 本小结描述 对数据库的创建查看删除和使用。

1.2.1创建数据库

格式:
    * create database 数据库名;
    * create database 数据库名 character set 字符集;

例如：

#创建数据库数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
CREATE DATABASE webdb_1; 
#创建数据库并指定数据库中数据的编码
CREATE DATABASE webdb_2 CHARACTER SET utf8;

1.2.2查看数据库

查看数据库MySQL服务器中的所有的数据库:
show databases;

查看某个数据库的定义的信息:
show create database 数据库名;
例如：
show create database webdb_1;

1.2.3删除数据库

drop database 数据库名称;
例如：
drop database webdb_2;

1.2.4使用数据库

查看正在使用的数据库:
select database();

其他的数据库操作命令
切换数据库：
use 数据库名;
例如：
use webdb_1;

1.3DDL之表操作：table

DDL(Data Definition Language) 本小结描述 对数据表的创建查看删除和使用。

1.3.1创建表

基本格式：

CREATE TABLE 表名
(
字段名1 数据类型(长度),
字段名2 数据类型(长度),
字段名3 数据类型(长度),
....
);

字段名 参数规定表中列的名称。
数据类型 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
长度 参数规定表中列的最大长度。
更多数据类型见附录：
示例：
现在我们想要创建一个名为 “Persons” 的表，包含五列：PersonID、LastName、FirstName、Address 和 City。
我们使用下面的 CREATE TABLE 语句：

CREATE TABLE Persons
(
PersonID INT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
);

PersonID 列的数据类型是 int，包含整数。
LastName、FirstName、Address 和 City 列的数据类型是 varchar，包含字符，且这些字段的最大长度为 255 个字符。

增强格式：

CREATE TABLE 表名
(
字段名1 数据类型(长度) [约束],
字段名2 数据类型(长度) [约束],
字段名3 数据类型(长度) [约束],
....
);

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

在 SQL 中，我们有如下约束：
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。
在后面的章节，我们会讲解相关的约束。

1.3.2查看表

查看数据库中的所有表：

格式：show tables;

查看表结构：

格式：desc 表名;
例如：desc sort;

1.3.3删除表

格式：drop table 表名;
例如：drop table category;

1.3.4修改表结构 格式：

alter table 表名 add 列名 类型(长度) [约束];
作用：修改表添加列.
例如：

#1，为分类表添加一个新的字段desc为分类描述,类型是 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);

alter table 表名 modify 列名 类型(长度) [约束];
作用：修改表修改列的类型长度及约束.
例如：

#2, 为分类表的描述字段desc进行修改，类型varchar(50) 添加约束 not null
ALTER TABLE category MODIFY desc VARCHAR(50) NOT NULL;

alter table 表名 change 旧列名 新列名 类型(长度) [约束];
作用：修改表修改列名.
例如：

#3, 为分类表的分类名称字段进行更换更换为 snamesname varchar(30)
ALTER TABLE category CHANGE desc description VARCHAR(30);

alter table 表名 drop 列名;
作用：修改表删除列.
例如：

#4, 删除分类表中snamename这列
ALTER TABLE category DROP description;

rename table 表名 to 新表名;
作用：修改表名
例如：

#5, 为分类表category改名成 category2
RENAME TABLE category TO category2;

alter table 表名 character set 字符集;
作用：修改表的字符集
例如：

#6, 为分类表 category 的编码表进行修改，修改成 gbk
ALTER TABLE category CHARACTER SET gbk;

1.4DML数据操作语言

简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新

1.4.1插入表记录：insert

语法：

-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values  (值1,值2,值3..); 
--向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);

注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
如果要插入空值，可以不写字段，或者插入 null。

例如：

INSERT INTO category(cid,cname) VALUES('c001','电器');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','书籍');

INSERT INTO category(cid) VALUES('c002');
INSERT INTO category(cname) VALUES('耗材');

1.4.2更新表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值
语法：

--更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
--更新符号条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;

注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起

1.4.3删除记录：delete

语法：

delete from 表名 [where 条件];
或者
truncate table 表名;

面试题：

删除表中所有记录使用delete from 表名; 还是用truncate table 表名;
删除方式：delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，重新建表，auto_increment将重置，从1重新开始。

1.5DQL数据查询语言

简称DQL(Data Query Language)，用来查询数据库中表的记录。

1.5.1准备工作

#创建商品表：
CREATE TABLE product(
	pid INT PRIMARY KEY,
	pname VARCHAR(20),
	price DOUBLE
	
);

INSERT INTO product(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product(pid,pname,price ) VALUES(2,'海尔',3000);
INSERT INTO product(pid,pname,price ) VALUES(3,'雷神',5000);

INSERT INTO product(pid,pname,price ) VALUES(4,'JACK JONES',800);
INSERT INTO product(pid,pname,price ) VALUES(5,'真维斯',200);
INSERT INTO product(pid,pname,price ) VALUES(6,'花花公子',440);
INSERT INTO product(pid,pname,price ) VALUES(7,'劲霸',2000);

INSERT INTO product(pid,pname,price ) VALUES(8,'香奈儿',800);
INSERT INTO product(pid,pname,price ) VALUES(9,'相宜本草',200);
INSERT INTO product(pid,pname,price ) VALUES(10,'面霸',5);

INSERT INTO product(pid,pname,price ) VALUES(11,'好想你枣',56);
INSERT INTO product(pid,pname,price ) VALUES(12,'香飘飘奶茶',1);

INSERT INTO product(pid,pname,price ) VALUES(13,'果9',1);

1.5.2语法：

select [distinct]
*| 列名,列名
from 表名字
where 条件

1.5.3简单查询

1.查询所有的商品.		
select * from 表名;

2.查询商品名和商品价格.
 select 列名1,列名2 from 表名;
 
3.去掉重复值.	
	select distinct 列名 from 表名;
	
4.查询结果是表达式（运算查询）：
	select 列名 运算符 操作数 from product;
	如 ：
	查询所有商品的价格显示时加10元.

5.别名查询使用的关键字是as（as可以省略的）	
	列别名：select 列名 as 别名 from 表名;	
   
    表别名: select * from product as p;

1.查询所有的商品.		select * from product;
2.查询商品名和商品价格.	select pname,price from product;
3.别名查询.使用的关键字是as（as可以省略的）.	
   3.1表别名: select * from product as p;
   3.2列别名：select pname as pn from product;	
4.去掉重复值.	select distinct price  from product;
5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
   select pname,price+10 from product;

1.5.4条件查询

比较运算符 > < <= >= = <>
!= 大于、小于、大于(小于)等于、不等于
BETWEEN …AND… 显示在某一区间的值(含头含尾)
IN(set) 显示在in列表中的值，例：in(100,200)
LIKE ‘张pattern’ 模糊查询，Like语句中，
%代表零个或多个任意字符，
_代表一个字符，
例如：first_name like ‘_a%’;
IS NULL 判断是否为空
逻辑运算符 and 多个条件同时成立
or 多个条件任一成立
not 不成立，例：where not(salary>100);
通配符 % 表示0个和多个字符，与like一同使用
_ 表示1个字符，与like一同使用

题干：
#查询商品名称为“花花公子”的商品所有信息：

#查询价格为800商品的全部信息

#查询价格不是800的所有商品的名字和价格

#查询商品价格大于60元的所有商品全部信息

#查询商品价格在200到1000之间所有商品的全部信息

#查询商品价格是200或800的所有商品的全部信息

#查询名字含有’想’字的所有商品的全部信息

#查询商品名字以’香’开头的所有商品的商品名和价格

#查询名字第二个字为’想’的所有商品的全部信息

#查询商品价格为空的商品全部信息

#查询商品价格不为空的商品全部信息

#查询商品名称为“花花公子”的商品所有信息：
select * from product where pname = '花花公子';

#查询价格为800商品的全部信息
select  * from product  where price = 800;

#查询价格不是800的所有商品的名字和价格

select  pname, price from product where price != 800;
select  pname, price from product where price <> 800;


#查询商品价格大于60元的所有商品全部信息
select * from product where price > 60;

#查询商品价格在200到1000之间所有商品的全部信息
select * from product where price>= 200 and price <= 1000;
#select * from product where price>= 200 && price <= 1000;

select * from product where price between 200 and 1000;

#查询商品价格是200或800的所有商品的全部信息

select * from product where price = 200 or price=800;

select * from product where price in (200,800);

#查询名字含有'想'字的所有商品的全部信息
select * from product where pname like '%想%';

#查询商品名字以'香'开头的所有商品的商品名和价格
select * from product where pname like '香%';
#查询名字第二个字为'想'的所有商品的全部信息
select * from product where pname like '_想%';

-- 查询 商品名字中 以想结尾的 商品的所有信息
select * from product where pname like '%想';
#查询商品价格为空的商品全部信息
select  * from product where price is null ;
#查询商品价格不为空的商品全部信息
select  * from product where price is not null ;