一、数据库的基本语法

2018-08-24  本文已影响133人  圣贤与无赖

一、数据库概述

数据库与数据库管理系统的关系


数据库与数据库管理系统的关系.png

数据库中以表为组织单位存储数据。
表类似我们的Java类，每个字段都有对应的数据类型。
那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系:

类----------表
类中属性----------表中字段
对象----------记录

表记录与java类对象的对应关系:


表记录与java类对象的对应关系.png

二、数据库的安装登录

1. 安装

安装完成后，需要开启对应的数据库服务才能使用。
开启和关闭服务的操作：右键点击我的电脑→管理→服务→可以找到MySQL服务或Oracle服务开启或停止。

OracleService<SID>：数据库实例服务
OracleOraDb11g_hom1TNSListener ：数据库监听服务

每次进到服务里面去打开相关服务很麻烦，所以我们可以写个bat脚本：
Oracle服务脚本

net start OracleOraDb11g_home1TNSListener
net start OracleServiceORCL
pause

MySQL服务脚本

net start mysql

把文本的后缀改成bat就可以了。
在dos窗口也可以使用脚本里的命令运行：


MySQL-dos.png

2. 登录

2.1 MySQL的登录

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。
格式1：cmd> mysql –u用户名 –p密码
例如：mysql -uroot –proot
注意：-p后无空格

登录MySQL.png

格式2：cmd> mysql --host=ip地址 --user=用户名 --password=密码
例如：mysql --host=127.0.0.1 --user=root --password=root

mysql登录方式2.png

三、 SQL语法

数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数据库的代码。

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

创建数据库、创建数据表、向数据表中添加一条条数据信息均需要使用SQL语句。

SQL语句分类：

SQL语法规则：

MySQL中的我们常使用的数据类型：


MySQL中的我们常使用的数据类型.png

Oracle中常用数据类型：


Oracle常用数据类型.jpg

1. 数据定义语言-DDL

1.1 DDL-数据库操作

例如:

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

例如：

show create database day21_1;

例如：

drop database day21_2;

例如：

use day21_1;
1.2 DDL-表结构相关语句

例如：

#创建分类表

CREATE TABLE sort (

 sid INT, #分类ID

 sname VARCHAR(100) #分类名称

);

例如：

###创建分类表
CREATE TABLE sort (
  sid INT PRIMARY KEY auto_increment, #分类ID 
  sname VARCHAR(100) #分类名称
);

其他约束：其他约束还有如外键、唯一、非空等。
唯一约束：unique
非空约束：not null

例如：desc sort;

例如：drop table sort;

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

例如：

#1，为分类表添加一个新的字段为  分类描述 varchar(20)

ALTER TABLE sort ADD sdesc VARCHAR(20);

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

例如：

#2, 为分类表的分类名称字段进行修改，类型varchar(50) 添加约束 not null

ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL;

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

例如：

#3, 为分类表的分类名称字段进行更换  更换为 snamesname varchar(30)

ALTER TABLE sort CHANGE sname snamename VARCHAR(30);

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

例如：

#4, 删除分类表中snamename这列

ALTER TABLE sort DROP snamename;

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

例如：

#5, 为分类表sort 改名成 category

RENAME TABLE sort TO category;

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

例如：

#6, 为分类表 category 的编码表进行修改，修改成 gbk

ALTER TABLE category CHARACTER SET gbk;

2.数据操作语言-DML

insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..); -- 向表中插入某些列
insert into 表 values (值1,值2,值3..); --向表中插入所有列

注意：

  1. 插入的数据应与字段的数据类型相同
  2. 数据的大小应该在列的长度范围内
  3. 在values中列出的数据位置必须与被加入列的排列位置相对应。
  4. 除了数值类型外，其它的字段类型的值必须使用引号引起。
  5. 如果要插入空值，可以不写字段，或者插入 null。
  6. 对于自动增长的列在操作时，直接插入null值即可。

例如：

INSERT INTO sort(sid,sname) VALUES('s001', '电器');

INSERT INTO sort(sid,sname) VALUES('s002', '服饰');

INSERT INTO sort VALUES('s003', '化妆品');

INSERT INTO sort VALUES('s004','书籍');

update 表名 set 字段名=值,字段名=值;
update 表名 set 字段名=值,字段名=值 where 条件;

注意：

  1. 列名的类型与修改的值要一致.
  2. 修改值得时候不能超过最大长度.
  3. 值如果是字符串或者日期需要加’’.

例如：

#1，将指定的sname字段中的值  修改成  日用品

UPDATE sort SET sname='日用品';

#2, 将sid为s002的记录中的sname改成  日用品

UPDATE sort SET sname='日用品' WHERE sid='s002';

UPDATE sort SET sname='日用品' WHERE sid='s003';

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

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

例如：

DELETE FROM sort WHERE sname='日用品';

#表数据清空

DELETE FROM sort;

以上操作，只针对当前窗口有效果，如果关闭了服务器便失效。如果想要永久修改，通过以下方式:

3.数据库查询语言-DQL

查询语句，在开发中使用的次数最多，此处使用“zhangwu” 账务表。

创建账务表：

CREATE TABLE zhangwu (

id INT PRIMARY KEY AUTO_INCREMENT, -- 账务ID

name VARCHAR(200), -- 账务名称

money DOUBLE, -- 金额

);

插入表记录：

INSERT INTO zhangwu(id,name,money) VALUES (1,'吃饭支出',247);

INSERT INTO zhangwu(id,name,money) VALUES (2,'工资收入',12345);

INSERT INTO zhangwu(id,name,money) VALUES (3,'服装支出',1000);

INSERT INTO zhangwu(id,name,money) VALUES (4,'吃饭支出',325);

INSERT INTO zhangwu(id,name,money) VALUES (5,'股票收入',8000);

INSERT INTO zhangwu(id,name,money) VALUES (6,打麻将支出,8000);

INSERT INTO zhangwu(id,name,money) VALUES (7,null,5000);

例如
表别名：
select * from zhangwu as zw;
列别名：
select money as m from zhangwu;
或
select money m from zhangwu;

例如：

查询所有吃饭支出记录
SELECT * FROM zhangwu WHERE name = '吃饭支出';

查询出金额大于1000的信息
SELECT * FROM zhangwu WHERE money >1000;

查询出金额在2000-5000之间的账务信息
SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000;
或
SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000;

查询出金额是1000或5000或3500的商品信息
SELECT * FROM zhangwu WHERE money =1000 OR money =5000 OR money =3500;
或
SELECT * FROM zhangwu WHERE money IN(1000,5000,3500);

查询出账务名称包含”支出”的账务信息。
SELECT * FROM zhangwu WHERE name LIKE "%支出%";

查询出账务名称中是无五个字的账务信息
SELECT * FROM gjp_ledger WHERE ldesc LIKE "_____"; -- 五个下划线_

查询出账务名称不为null账务信息
SELECT * FROM zhangwu WHERE name IS NOT NULL;
SELECT * FROM zhangwu WHERE NOT (name IS NULL);

例如：

  1. 查询所有的商品，按金额进行排序.(asc-升序,desc-降序)
    select * from zhangwu order by money;

  2. 查询名称包含“支出”的账务信息，并按金额降序排序.
    select * from zhangwu where name like '%支出%' order by money desc;

例如：

  1. 统计账务表中共有多少条记录
    SELECT COUNT(*) FROM zhangwu;

  2. 统计账务表中金额大于3000的有多少条记录
    SELECT COUNT(*) FROM zhangwu WHERE money>3000;

  3. 统计有多少收入的账务信息
    SELECT SUM(money) FROM zhangwu WHERE name like '%收入%';

  4. 统计出记录中支出的平均值
    SELECT AVG(money) FROM zhangwu where name like '%支出%';

  5. 统计出商品表中收入的最大与最小值
    SELECT MAX(money),MIN(money) FROM zhangwu;

分组查询是指使用group by字句对查询信息进行分组,例如：我们要统计出zhanguw表中所有分类账务的总数量,这时就需要使用group by 来对zhangwu表中的账务信息根据parent进行分组操作。
格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY 字段 HAVING 条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

having与where的区别:

  1. having是在分组后对数据进行过滤.
    where是在分组前对数据进行过滤

  2. having后面可以使用分组函数(统计函数)
    where后面不可以使用分组函数。

例如：

  1. 对账务分种类统计，求出每个种类的账务总金额
    SELECT parent,SUM(money) FROM zhangwu GROUP BY parent;

  2. 对账务分种类统计，求出每个种类的账务总金额，金额要大于500才显示
    SELECT parent,SUM(money) FROM zhangwu GROUP BY parent HAVING SUM(money)>500;

以上的两种主法哪一种好？
模板1的速度快，随着页码越来越从，速度将接近于模板2
模板2的速度慢，因为是第二个子查询把所有数据都查询出来

分页实例：分页按照薪水高低显示数据，每页5条数据，显示第2页数据。

--1,按薪水高低排序
SELECT * FROM EMP ORDER BY SAL  DESC
--2，以上的查询结果为基础   再查询出前10条数据
SELECT ROWNUM RN,T.* FROM (SELECT * FROM EMP ORDER BY SAL  DESC) T WHERE ROWNUM<=10;
--3,以上的的查询结果上再套一层，查询RN>5的数据
SELECT *
  FROM (SELECT ROWNUM RN, T.*
          FROM (SELECT * FROM EMP ORDER BY SAL DESC) T
         WHERE ROWNUM <= 10)
 WHERE RN > 5;
--4,第二种写法
SELECT *
  FROM (SELECT ROWNUM RN, T.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) T)
 WHERE RN BETWEEN 1 AND 5;

4. 数据控制语言-DCL

因为MySQL和Oracle存储方式有较大区别，所以它们管理用户和权限的方式也有很大不同。

4.1 MySql的用户权限管理

Mysql中的用户，存储在系统数据库mysql中的user表中，表初始内容只有localhost-----root用户。

MySql的用户管理
MySql的权限管理：

权限：mysql数据库将其中所能做的所有操作都进行分类，分配到不同的权限（30多个），其中每个权限都用一个单词表示。
如：select 表示查询，delete表示删除等，all表示所有权限

如：
Grant select on php39.* to ’user1‘@’localhost’;

4.2 Oracle中的用户权限管理

安装完Oracle之后系统为开发者提供了一些用户如SCOTT SYSTE SYS等用户，但是这都是系统给的，能不能自己创建用户呢?显然是可以的。
Oarcle的数据是存在放到目录下的oraldata目录下的DBF文件里面，那么可以不可以创建自己的存放数据库的DBF文件呢？显然是可以。


oraldata目录下的DBF
Oracle的表空间管理

Oracle的权限控制主要是对表空间的权限控制，即将表赋给某个用户。所以我们先学习如何创建Oracle的表空间

1，创建临时表空间
create temporary tablespace erp_temp
tempfile 'D:\oracledata\erp_temp.dbf' DBF文件的位置自定义
size 50m 初始大小
autoextend on 大小自动增加
next 50m maxsize 20480m 每次增加的大小和最大空间
extent management local[本地管理]/dictionary[数据字典管理];

2，创建表空间
create tablespace erp_data
datafile 'D:\oracledata\erp_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m ;

3，修改表空间
语法： alter tablespace 旧名称 rename to 新名称;
alter tablespace newspace rename to myspace;

4，删除表空间,并把包含的数据和约束删除
语法：drop tablespace 表空间名 [including contents and datafiles];
drop tablespace ERP_TEMP including CONTENTS AND DATAFILES;

5，查看表空间
select * from v$tablespace;

Oracle的用户管理
Oracle的权限管理

四、创建多表及多表关系

【外键约束】
多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键约束.
alter table product add foreign key (cno) references category(cid);
【多表的关系】

1. 网上商城案例实体抽取和表关系建立

网上商城案例实体抽取和表关系建立.png 
一对多的实现

###创建分类表（已存在）
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100)        #分类名称
);

# 商品表（已存在，删除，重新创建）
CREATE TABLE `products` (
  `pid` varchar(32) PRIMARY KEY  ,
  `name` VARCHAR(40) ,
  `price` DOUBLE 
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);



多对多的实现
### 订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double   #总计
);

### 订单项表
create table orderitem(
  oid varchar(50),-- 订单id
  pid varchar(50)-- 商品id
);
### 联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);

###---- 订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

###---- 商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);


初始化数据
-- 给商品表初始化数据
insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);


-- 给分类表初始化数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');

2. 使用商城表关系完成多表的查询

【多表查询】

我们以一对多为例来练习一下

初始化数据:
-- 用户表(user) 
   create table `user` (                                  
          `id` int auto_increment primary key,                
          `username` varchar(50)  -- 用户姓名                                                
        );

-- 订单表(orders)
   create table `orders` (                                                  
          `id` int  auto_increment primary key,                                  
          `price` double,                                           
          `user_id` int                                       
        );
-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id); 

-- 向user表中添加数据
        insert into user values(3,'张三');
        insert into user values(4,'李四');
        insert into user values(5,'王五');
        insert into user values(6,'赵六');

-- 向orders 表中插入数据
        insert into orders values(1,1314,3);
        insert into orders values(2,1314,3);
        insert into orders values(3,15,4);
        insert into orders values(4,315,5);
        insert into orders values(5,1014,null);

【子查询】
在sql语言中，当一个查询是另一个查询的条件时，称之为子查询

【分页查询】
分页查询每个数据库的语句是不通用的.

MYSQL:使用limit的关键字.
select * from product limit a,b;
--a:从哪开始,b:查询多少条.

Oracle:使用SQL语句嵌套.
SQLServer:使用top的关键字.

上一篇下一篇

猜你喜欢

热点阅读