mysql 数据库知识点

　　

MySQL

1.数据库简介：

1.1什么是数据库？

数据库（Database）是按照数据的结构来组织、存储和管理数

据的仓库，数据库也是一个文件系统。

1.2数据库的分类？

数据库一般分为两种类型，一种是关系型数据库(主流)，

另一种是面向对象的非关系型数据库.(nosql=not only sql)

1.3什么是关系型数据库？

多个实体之间存在关系，我们管其叫关系数据，这样的数据

可以存储在关系数据库中。(ER.png)

1.4常见的关系型数据库？

mysql,sqlserver,oracle,postgresql,sybase,db2...

 

-----------------------------------------------------------------

2.mysql简介：

2.1MySQL数据库的安装和配置，卸载,密码重置？

（安装包中有图解教程）

配置Mysql安装目录到bin目录到Path中

 

 

2.2mysql的连接方式？

第一种连接方式（dos命令窗口）：

cmd 回车（打开dos命令窗口）

c:  回车 （切换盘符，我切换到c盘了）

cd C:\Program Files\MySQL\MySQL Server 5.5\bin  回车（进入可执行文件目录）

-- 我的mysql安装路径C:\Program Files\MySQL\MySQL Server 5.5\bin

-- 简单连接（一般本地连接用）

mysql –u用户名 –p密码

mysql -uroot -p

mysql -uroot -proot

-- 完全连接（一般远程连接用）

mysql -u 用户名 -h 服务器的ip -P 端口 -p 密码

mysql -u root -h localhost -P 3306 -proot

mysql -u root -h 192.168.199.159 -P3306 -proot

mysql -uroot -h192.168.199.159 -P3306 –proot

 

基本命令：

显示数据库列表      show databases;

进入某一个数据库    use 数据库名;

显示数据表          show tables;

显示表结构          describe 表名;

 

第二种连接方式 （mysql自带的命令窗口）

第三种连接方式   第三方客户端软件（海豚软件）

2.3MySQL数据库服务器、数据库和表的关系？

 

2.4数据在数据库中的存储方式?

 

---------------------------------------------------------------------------

3.sql语言：

3.1什么是sql?

Structured Query Language, 结构化查询语言

3.2作用及分类？

SQL是用来存取关系数据的语言，具有查询、

操纵、定义和控制关系型数据库的四方面功能

DDL （数据定义语言）

数据定义语言 - Data Definition Language

用来定义数据库的对象，如数据表、视图、索引、触发器等

DML （数据操纵语言）

数据处理语言 - Data Manipulation Language

在数据库表中更新，增加和删除记录

如 update， insert， delete

DCL （数据控制语言）

数据控制语言 – Data Control Language

指用于设置用户权限和控制事务语句

如grant，revoke，if…else，while，begin transaction

DQL （数据查询语言）

数据查询语言 – Data Query Language

select

--------------------------------------------------------------------------

要求c/s开发模式和b/s开发模式下都要会操作数据库

--------------------------------------------------------------------------

4.DDL数据定义语言

4.1对数据库的操作

--海豚工具创建数据库testdb,指定字符集为utf8

-- 显示数据库

SHOW DATABASES

show databases;

-- 显示数据库创建的信息

SHOW CREATE DATABASE db_name

show create database testdb;

-- 创建数据库

create database 数据库名

create database testdb01;

create database if not exists testdb01;

create database 数据库名 character set 编码

create database testdb02 character set utf8;

create database 数据库名 character set 编码 COLLATE 校验集

create database bdyc01 character set utf8 collate utf8_general_ci;

-- 删除数据库

DROP DATABASE [IF EXISTS] db_name

drop database testdb01;

drop database if exists testdb01;

-- 修改数据库

create database testdb01;

alter database 数据库名 character set 字符编码集 collate 校验集

alter database testdb01 character set utf8 collate utf8_general_ci;

show create database testdb01;

-- 查看当前使用的数据库

select database()

select database();

-- 切换数据库

use db_name

use testdb;

------------------------------------------------------------------------------

4.2对表结构的操作

create database mydb;

use mydb;

-- 创建表

CREATE TABLE table_name

(

field1 datatype [约束条件],

field2 datatype[约束条件],

field3 datatype[约束条件]

)character set 字符集 collate 校对规则

 

-- field：指定列名（字段名）　datatype：指定列类型

-------------------------------------------------------------------------------

常见的数据类型？

字符串型

VARCHAR、CHAR 

大数据类型

TEXT

数值型

TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

逻辑型

boolean

日期型

DATE、TIME、DATETIME

 

create table user(

id int,

nname varchar(20),

pwd varchar(20)

)

-------------------------------------------------------------------

约束：

约束的是什么？它的作用?

 对字段进行约束就是强制对字段的特性进行控制。

 添加约束可以提高我们的数据的完整性与安全性。

 定义主键约束？

 primary key:不允许为空，不允许重复

     删除主键：alter table tablename drop primary key ;

 主键自动增长 ：auto_increment

 定义唯一约束？

 unique

 例如：name varchar(20) unique

 定义非空约束？

 not null

 例如：salary double not null

-------------------------------------------------------------------

create table user1(

id int primary key auto_increment not null,

nname varchar(20) unique,

pwd varchar(20)

)

-------------------------------------------------------------------

作业：

两套作业题中的表创建出来

--------------------------------------------------------------------

create table employee(

id int primary key auto_increment not null,

name varchar(20) unique,

gender varchar(2),

birthday date,

entry_date date,

job varchar(20),

salary double not null,

resume text

) charset = utf8 collate = utf8_general_ci;

 

CREATE TABLE `db1201`.`employee1`( 

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(20) NOT NULL,

  `gender` VARCHAR(2),

  `birthday` DATE,

  `entry_date` DATE,

  `job` VARCHAR(20),

  `salary` DOUBLE,

  `resume` TEXT,

  PRIMARY KEY (`id`)

) CHARSET=utf8 COLLATE=utf8_general_ci;

 

create table employee2(

id int primary key auto_increment,

name varchar(20) unique not null,

gender varchar(2),

birthday date,

entry_date date,

job varchar(20),

salary double not null,

resume text

)charset = utf8 collate = utf8_general_ci;

 

-------------------------------------------------------------------------------------------

-- 修改表结构

create table users(

uname varchar(20)

);

       -- 添加字段

alter table 表名 add 字段名 数据类型

alter table users add password varchar(20);

 

       -- 删除字段

alter table 表名 drop 字段名

alter table users drop password;

 

       -- 修改表的名称

rename table 表名 to 新表名

rename table users to user02;

 

       -- 修改字段类型

alter table user02 add password int(20);

alter table 表名 modify 字段名 字段新类型

alter table user02 modify password varchar(20);

       -- 修改列的名称

alter table 表名 change 字段名 新字段名 字段类型

alter table user02 change password pwd varchar(10);

       -- 修改表的字符集

show create database user02;

alter table 表名 character set 新字符集

alter table student character set utf8;

       -- 查看表结构

desc 表名

desc user02;

 

--删除表

DROP TABLE 表名

create table user03(

name varchar(20),

pass varchar(20),

birthday date

);

drop table user03;

 

修改表结构练习作业.png

-----------------------------------------------------------------

5.DML数据操纵语言

-- 操作数据的语句

-- 插入数据

INSERT INTO 表名 [(字段名 [, 字段名...])]

VALUES (值 [, 值...])

 

create table user03(

name varchar(20),

pass varchar(20),

birthday date

);

 

insert into user03(name,pass) values('张三','123456');

insert into user03(name,pass,birthday) values('李四','123456','1991-01-01');

 

-- 省略字段名，values中必须按照建表时字段的顺序依次指定）(不推荐使用）

insert into user03

       values('赵六','12312','1990-03-03');

 

insert into user03(name,password,birthday)

       values('张三','123123','1990-01-01'),

       ('张三1','123123','1990-01-02'),

       ('张三2','123123','1990-01-03'),

       ('张三3','123123','1990-01-04'),

       ('张三4','123123','1990-01-05');

 

注意：

mysql中所有的数据（除了boolean）类型外所有的值都必须使用''引起来

数值型也可以使用'' 引起来，也可以不用。

插入的数据应与字段的数据类型相同。

数据的大小应在列的规定范围内，例如：不能将一个长度为

80的字符串加入到长度为40的列中。

在values中列出的数据位置必须与被插入的列的排列位置相

对应。

 

插入数据作业练习.png

---------------------------------------------------------------------------

补充：

-- 查看所有字符集

show variables like 'character%';

-- 因为dos客户端默认gbk编码，所以临时修改结果集编码为gbk

set character_set_results=gbk; / set names gbk;

 

mysql有六处使用了字符集，分别为：client 、connection、database、

results、server 、system。

client是客户端使用的字符集。

connection是连接数据库的字符集设置类型，如果程序没有指明连

接数据库使用的字符集类型就按照服务器端默认的字符集设置。

database是数据库服务器中某个库使用的字符集设定，如果建库时

没有指明，将使用服务器安装时指定的字符集设置。

results是数据库给客户端返回时使用的字符集设定，如果没有指明，

使用服务器默认的字符集。

server是服务器安装时指定的默认字符集设定。

system是数据库系统使用的字符集设定。（utf-8不可修改）

通过修改my.ini 修改字符集编码

set names gbk；临时修改

 

------------------------------------------------------------------------------------

-- 修改数据

update 表名 set 字段=新值 条件表达式

update user03 set name = '褚召威' where id = 2;

 

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

 

---------------------------------------------------------------------------------------

-- 删除数据

delete from 表名

[WHERE 条件表达式];

 

delete from user03

       where name = '张三';

 

注意：

如果不使用where子句，将删除表中所有数据。

 Delete语句不能删除某一列的值（可使用update）

 使用delete语句仅删除记录，不删除表本身。如要删除表，使用

drop table语句。

-------------------------------------------------------------------------------

-- 清空表数据，不会删除表结构

create table user04(

name varchar(20),

pass varchar(20),

birthday date

);

insert into user04(name,pass) values('张三','123456');

insert into user04(name,pass,birthday) values('李四','123456','1991-01-01');

delete from user04;

desc user04;

 

-- 清空表数据，先将表结构删除，在重新创建表结构

create table user05(

name varchar(20),

pass varchar(20),

birthday date

);

insert into user05(name,pass) values('张三','123456');

insert into user05(name,pass,birthday) values('李四','123456','1991-01-01');

delete from user05;

truncate table user05;

desc user05;

 

注意：

 delete删除时，是一条一条删除。它效率比较低. 受事务管理

 truncate删除是先将表结构删除，在重新创建表结构. 不受事务管理

delete和truncate区别：

              1、delete 一条记录一条记录删除，不会删除表结构

              如果表中的数据量过大时不推荐使用。效率低下。

              2、truncate 先删除表结构，然后在新建表结构，如果

              表中的而数据很大是，推荐使用。效率高。

6.DCL数据控制语言

6.1事务

什么是事务？

    一个Java应用系统，如果要操作数据库，则通过JDBC来实现的。增加、修改、删除都是通过相应方法间接来实现的，事务的控制也相应转移到Java程序代码中。因此，数据库操作的事务习惯上就称为Java事务。

事务是为解决数据安全操作提出的，事务控制实际上就是控制数据的安全访问。具一个简单例子：比如银行转帐业务，账户A要将自己账户上的1000元转到B账 户下面，A账户余额首先要减去1000元，然后B账户要增加1000元。假如在中间网络出现了问题，A账户减去1000元已经结束，B因为网络中断而操作 失败，那么整个业务失败，必须做出控制，要求A账户转帐业务撤销。这才能保证业务的正确性，完成这个操走就需要事务，将A账户资金减少和B账户资金增加方 到一个事务里面，要么全部执行成功，要么操作全部撤销，这样就保持了数据的安全性。

 

事务的四个特征（ACID）：

原子性(Atomicity)是指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.

一致性(Consistency)是指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.

独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.

持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.

 

一个支持事务（Transaction）的数据库，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

 

 

-- 开启事务

start transaction;

-- 回滚事务

rollback;

-- 提交事务

commit;

 

 

7.DQL数据查询语言

-- 查询是重点！！！

 

SELECT [DISTINCT] *|{column1, column2. column3..}

FROM table 条件表达式

 

create table t_user(

name varchar(20),

pass varchar(20),

birthday date

);

 

insert into t_user(name,password,birthday)

       values('张三','123123','1990-01-01'),

       ('张三1','123123','1990-01-02'),

       ('张三2','123123','1990-01-03'),

       ('张三','123123','1990-01-04'),

       ('张三4','123123','1990-01-05');

 

select '张三' from t_user;

select name,birthday from t_user;

select distinct name,birthday from t_user;

-- 指定查询列

select name,pass,birthday from t_user;

 

-- 只查询某个字段

select name from t_user where pass = 123123;

 

-- 查询表中的所有数据 * 所有字段（默认和建表顺序一致）不推荐使用）

select * from t_user;

 

-- 为字段起别名,如果别名中没有特殊字符不用""，反之必须使用""引起来

--''在mysql执行不会报错（mysql的容错机制），但''一般是在设值的时候用的。

select name as 姓名 ,birthday as 生日 from t_user;

 

select name as "姓_*as名" ,birthday as "生 日" from t_user;

 

-- as 关键字可以省略

select name  "姓 名" ,birthday  "生 日" from t_user;

----------------------------------------------------------------------------------

select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

from指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据（记录）。

-----------------------------------------------------------------------------------------------------

条件查询

使用where子句，进行过滤查询，在where子句中经常使用的运算符？

在where子句中经常使用的运算符.png

 

-- 创建班级表

create table t_grade(

       id int primary key auto_increment,

       name varchar(20),

       birthday date,

       chinese float,

       math float,

       english float

);

 

-- 班级表插入数据

insert into t_grade(name,birthday,chinese,math,english)

values('张三','1990-01-01',85.5,75,60),

('刘德华','1990-01-01',61,75,85),

('王钊','1980-01-01',88,80,66),

('周瑞发','1990-01-01',85.5,75,60),

('张三丰','1900-01-01',88,72,55),

('张翠山','1920-01-01',86,73.5,64),

('张无忌','1950-01-01',83.5,75,32),

('张小翠','1950-01-01',83.5,75,32),

('小翠','1950-01-01',83.5,75,32),

('翠','1950-01-01',83.5,75,32);

 

 

 --  1、查询英语分数在 80－90之间的同学。

       select * from t_grade

       where english >= 80 and english <= 90;

 

 

 -- 2、查询数学英语为85,66,91的同学

       select * from t_grade

       where english in(85,66,91);

 

 --  3、查询所有姓张的学生成绩。

       -- %：0~n个字符

       select  name 姓名,math 数学,chinese 语文,english 英语

       from t_grade

       where name like '张%';

 

       -- _：一个字符

       select  name 姓名,math 数学,chinese 语文,english 英语

       from t_grade

       where name like '张_';

 

       select  name 姓名,math 数学,chinese 语文,english 英语

       from t_grade

       where name like '%翠%';

 

       -- 查询英语成绩为null人

       select * from t_grade

       where english is null;

 

       -- 查询英语成绩为null且姓王的人

       select * from t_grade

       where english is null and name like '王%';

 

 

       -- 查询英语成绩为null 或 姓张的人

       select * from t_grade

       where english is null or name like '张%';

 

 

    -- 查询数学分>=80  且 语文分>80的同学。

       select * from t_grade

       where math >= 80  and chinese > 80;

 

 

    -- 查询名字不等于王钊的人

       select * from t_grade

       where name <> '王钊';

 

    -- 查询英语成绩不大于60

       select * from t_grade

       where not(english >= 60);

-----------------------------------------------------------------------------

 排序

Order by 指定排序的列，排序的列即可是表中的列名，也可以

是select 语句后指定的列名。

Asc 升序（默认）、Desc 降序

ORDER BY 子句应位于SELECT语句的结尾。

      

   --  按照英语成绩降序输出

     select * from t_grade

     order by english desc;

 

   --  按照英语成绩升序输出

     select * from t_grade

     order by english asc;

 

   --  按照英语成绩升序输出（默认asc 升序）

     select * from t_grade

     order by english;

 

     按照英语成绩降序输出，如果英语相同，

     再按照数学降序，如果数学相同，再按语文降序

 

     select * from t_grade

        order by english,math desc,chinese desc;

---------------------------------------------------------------------------

 

   -- 聚合函数：count

   Count(列名)  返回对应列的总数

 

       -- 统计成绩表中的人数：

       select count(*) 总人数 from t_grade;

       --不推荐使用count(*)

       select count(id) 总人数 from t_grade;

 

       select count(id) 总人数 from t_grade

       where english >= 60;

 

     -- 统计语文成绩总和：sum(列)  竖

     select sum(chinese) 语文总分 from t_grade;

 

     --统计语文成绩平均分：avg(列)  竖

     select avg(chinese) 语文平均分 from t_grade;

 

     select sum(chinese)/count(id) 语文平均分 from t_grade;

 

     -- 统计语文成绩最高分：max(列)  竖

     select  max(chinese) 分数 from t_grade;

 

     -- 统计语文成绩最低分：min(列)  竖

     select  min(chinese) 分数 from t_grade;

----------------------------------------------------------------------------------------

分组

select name,max(chinese)

from t_grade

 

-- 统计班级人数，按班级分组

select clazz 班级编号, count(id) 人数

from t_grade

group by clazz

 

-- 统计每个班的语文最高成绩

分组查询.png

 

select  clazz, max(chinese) 语文最高成绩,

from t_grade

group by clazz;

 

注意：

在group by 语句中select/having中出现的字段只能是group by 的分组字段

如果出现其他字段那么这些字段只能放在聚合函数中

使用having 子句 对分组结果进行过滤

 

-- 统计每个班数学总分大于200的 班级

select clazz

from t_grade

group by clazz

having sum(math) > 200;

 

 

-- 统计每个人的总分

select name,(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) 总分

from t_grade;

 

-- 每个人的总分排名

select name,(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) zf

from t_grade

order by  zf desc;

 

--------------------------------------------------------------------------------------------------

 

-- 备份数据库（在cmd中）

       cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql

       c:/>mysqldump -u root -p mydb > d:/mydb.sql

       mysqldump -u root -proot mydb>c:/mybank.sql         //只备份数据

 

       source 文件名.sql // 在mysql内部使用 切换到当前数据库

       mysql –u 用户名 p 数据库名 < 文件名.sql // 在cmd下使用

       c:/>mysql -u root -p mydb < d:/mydb.sql

       注意：执行恢复时一定要先创建数据库

 

       source c:/mybank1.sql      //还原数据，在此之前需要先新建一个数据库mybank1。

 

--------------------------------------------------------------------------------------------

多表查询(联合（2表字段个数一样）/内连接/外连接/子查询)

多表设计中三种实体关系（详解见MySQL的PDF文档）

 

7.1一对一：

根据业务给哪个表（后出现的）加外键约束

7.2一对多：

多的那个表加外键约束

7.3多对多：

中间表

 

外键约束：

       foreign(被依赖表的主键也就是依赖表的外键字段如key) references 被依赖表(被依赖表的主键)

外键作用：依赖表中外键字段名可以任意指定，但是字段类型必须和被依赖表对应字段类型一致

       描述表和表之间的关系，约束外键字段的合法性

 

一对一：人 -- 身份证

先有人再有身份证，所以外键约束加在身份证表上，即约束表上。

create table person(

       pid int primary key auto_increment,

       pname varchar(20)   

);

create table card(

       cid int primary key auto_increment,

       cnum varchar(18) unique,

       caddress varchar(50),

       nid  int unique, -- 外键唯一（unique，not null根据需求）约束

 

       foreign key(nid) references person(pid)

);

 

insert into person(pame) values

('张三'),

('李四'),

('王五');

 

insert into card(cnum,caddress,nid)

values

('411111111111','黑龙江',1),

('411111111112','黑龙江',2),

('411111111113','黑龙江',3),

('411111111114','黑龙江',4);

 

 

 

insert into t_card(c_no,c_address,p_id)

values('411111111112','黑龙江',1);

 

 

班级 -- 学生  ：一 对 多

       交给多方去维护关系

 

一对多：班级（被依赖表） --- 学生（依赖表）

create table class(

       cid int primary key auto_increment,

       cnum varchar(20),

       cname varchar(20)

);

 

create table student(

       sid int primary key auto_increment,

       snum varchar(20),

       stname varchar(20),

       cid int unique, -- 班级id  外键                              

       foreign key(cid) references class(cid)

 

);

 

insert into class(cnum,cname) values

('b903','java基础一班'),

('b803','java基础二班');

 

 

insert into student(snum,sname,cid) values

('x201601','王钊',2),

('x201602','林群彭',2),

('x201603','张三',1),

('x201603','张三',4);

 

多对多：学生（依赖表） -- 老师（被依赖表）

       两个一对多:多对多

 

create table teacher(

       tid int primary key auto_increment,

       tname varchar(20)

);

 

create table student(

       sid int primary key auto_increment,

       sname varchar(20),  

);

 

 

-- 学生老师关系表：中间表

create table zj_stu_tea(

       zd int primary key auto_increment,

       sid int unique,

       tid int unique,

       foreign key(sid) references students(sid),

       foreign key(tid) references teacher(tid)

);

insert into teacher(tname) values

('张老师'),

('潘老师'),

('马老师');

 

insert into t_stu_tea(stu_id,tea_id) values

(1,1),

(1,2),

(1,3),

(2,1),

(2,2),

(2,3);

 

子查询

在sql语言中，select…from…语为一个查询块，将一个查询块嵌套在另一个查询块

中作为条件称为嵌套查询,也称为子查询.外层的查询块称为父查询，内层的查询

块称为子查询。

子查询的结果是一个表。

 

关联查询：

       内连接：[inner] join   查询的结果：关联结果

              如果没有加条件：

              结果：笛卡尔集：没有任何意义，只是单纯的将两张表数据合并

              select * from t_card inner join t_person

              select * from t_card,t_person

 

              一定记得加关联条件：

              结果：有意义的关联合并数据

              select * from t_card c,t_person p

              where c.p_id = p.id;

             

              select * from t_card c inner join t_person p

              on c.p_id = p.id;

 

       外链接: 不一定都有关联

              LEFT [OUTER] JOIN：左外连接：以左边表为准,右边表可以为空。

              RIGHT [OUTER] JOIN：右外连接：以右边表为准，左边表可以为空。

 

       所有的人的详细信息（自己信息+身份证信息）：

      

       select *

       from t_person p left outer join t_card c

       on p.id = c.p_id;

 

       select *

       from  t_card c right outer join t_person p

       on p.id = c.p_id;

 

 

--------------------------------------------------------------------------

-- 前两条：

SELECT * FROM Employee LIMIT 2;

 

-- '1993-06-03'

year(CURDATE())-year(bdate)

 

-- 计算每个雇员的实际收入

SELECT *,(income-outcome) FROM Salary

 

 

 

 

 

 

 

 

 

 

 

-- 创建数据库

CREATE DATABASE homework;

-- 创建表（3）

-- 员工表

CREATE TABLE Employee(

num INT,

NAME VARCHAR(10),

addr VARCHAR(20),

zip VARCHAR(20),

tel VARCHAR(20),

email VARCHAR(40),

birth DATE,

sex CHAR(2),

depno INT

);

DESC Employee;

INSERT INTO Employee(num,NAME,addr,zip,tel,email,birth,sex,depno)

 VALUES

(004,'王小燕','武汉交大','430071','85743261','lili@sina.com','1950-7-30','女',1),

(006,'李明','华中师大',    '430075','85362143','zhujun@sina.com','1955-09-28','男',5),

(007,'田丽','中南财大',    '430076','85693265','zgming@sohu.com','1968-08-10','女',3),

(008,'吴天','武汉电力',    '430077','36985612','zjamg@china.com','1964-10-01','男',5),

(010,'张飞','武汉钢铁',    '430079','69865632','liyu@tom.com','1958-09-20','男',4);

 

SELECT * FROM Employee;

INSERT INTO Employee(num,NAME,addr,zip,tel,birth,sex,depno)

 VALUES

(001,'王林','武汉大学','430074','87598405','1985-2-1','男',2),

(002,'王芳','华中科大',    '430073','62534231','1966-3-28','男',1),

(003,'张晓','武汉理工大','430072','87596985','1972-12-9    ','男',1),

(005,'李华','华中农大',    '430070','87569865','1962-10-18','男',5),

(009,'刘备','武汉邮科院','430078','69865231','1967-04-02','男',3),

(011,'赵云','学府家园',    '430071','68592312','1968-11-18','男',4),

(012,'貂禅]','湖北工大','430074','65987654','1959-09-03','女',4);

 

SELECT * FROM Employee;

 

-- 部门表

CREATE TABLE Department(

depno INT,

depname VARCHAR(10),

remark VARCHAR(40)

);

DESC Department;

INSERT INTO Department(depno,depname)

VALUES

(1,'财务部'),

(2,'人力资源部'),

(3,'经理办公室'),

(4,'研发部'),

(5,'市场部');

 

SELECT * FROM Department;

 

-- 工资表

CREATE TABLE Salary(

num INT,

income FLOAT,

outcome FLOAT

);

DESC Salary;

INSERT INTO Salary

VALUES

(001,2100.8,123.09),

(002,1582.62,88.03),

(003,2569.88,185.65),

(004,1987.01,79.58),

(005,2066.15,108.0),

(006,2980.7,210.2),

(007,3259.98,281.52),

(008,2860.0,198),

(009,2347.68,180),

(010,2531.98,199.08),

(011,2240.0,121.0),

(012,1980.0,100.0); 

SELECT * FROM Salary;

 

SELECT * FROM Employee;

SELECT * FROM Employee LIMIT 5;

SELECT addr,tel FROM Employee;

SELECT addr,tel FROM Employee WHERE num='001'

SELECT sex,addr AS '地址',tel '电话' FROM Employee WHERE sex='女'

SELECT income,outcome,(income-outcome) FROM Salary;

SELECT '王林' FROM Employee;

SELECT '王林',addr FROM Employee;

SELECT DISTINCT depno,NAME FROM Employee WHERE NAME LIKE '王%'

SELECT * FROM Salary WHERE income BETWEEN 2000 AND 3000

 

SELECT * FROM Employee 

WHERE depno=(SELECT  depno FROM Department

WHERE depname='财务部'

)

 

SELECT NAME FROM Employee WHERE depno=      -- name换为*

(SELECT depno FROM Department WHERE depname='财务部')

AND

birth<

(

SELECT MIN(birth) FROM Employee

    WHERE  depno=

   (SELECT depno FROM Department WHERE depname='研发部')

)

 

 

-- 联合查询  union

SELECT * FROM Employee;

 

 

 

 

-- 多表查询

-- 联合查询   union 注意：联合的两个结果集的字段个数要相同

SELECT * FROM Salary;

SELECT * FROM Department;

(SELECT * FROM Department)UNION

(SELECT * FROM Salary);

 

-- 连接查询

-- 内连接 inner join   on

-- 两种写法：

-- 写法一：

-- select 字段名 from 表1 inner join 表2 on 表1.共同的字段=表2.共同的字段

SELECT * FROM a INNER JOIN b ON a.id=b.id;

-- 写法二：

-- select 字段名 from 表1，表2 where 表1.共同的字段=表2.共同的字段

SELECT * FROM a,b WHERE a.id = b.id;

 

 

 

-- 全外连接 full join

-- 左外连接 left join

-- 右外连接 right join

 

 

8.三范式

关系数据库的几种设计范式：

8.1第一范式（1NF）

    在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
    所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。例如，对于图3-2 中的员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。

8.2第二范式（2NF）

    第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。如图3-2 员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是唯一的，因此每个员工可以被唯一区分。这个唯一属性列被称为主关键字或主键、主码。
    第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

 

8.3第三范式（3NF）

    满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

 

 

 

9.笛卡尔积

 

在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

 

    在数据库中，一个查询往往涉及到多个表，因为很少有数据库只有一个表，如果大多查询只涉及到一个表，那么那个表也往往低于第三范式，存在大量冗余和异常。连接（Join）就是一种把多个表连接成一个表的重要手段。

 

比如简单的两个表，学生表和班级表，如图：

 

进行连接后，如图：