【石家庄校区】JavaWeb之数据库 MySQL

day02数据库的基本概念,MySQL数据库软件,SQL

数据库的基本概念

1.数据库的英文单词:DataBase 简称:DB

2.什么数据库:

用于存储和管理数据的仓库

3.数据库的特点:

1.持久化存储数据的.其实数据库就是一个文件系统

2.方便存储和管理数据

3.使用了统一的方式操作数据库 -- SQL

4.常见的数据库软件

参见<MySQL基础.pdf>

MySQL数据库软件

1.安装

参见<MySQL基础.pdf>

2.卸载

1.去mysql的安装目录找到my.ini文件

复制 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

2.卸载MySQL

3.删除C:/ProgramData目录下的MySQL文件夹.

3.配置

MySQL服务启动

1.手动

2.cmd ->services.msc 打开服务的窗口

3.使用管理员打开cmd

net start mysql : 启动mysql的服务

net stop mysql: 关闭mysql服务

MySQL登陆

1.mysql -uroot -proot

2.mysql -hip -uroot -p连接目标的密码

3.mysql --host=ip --user=root --password=连接目标的密码

MySQL退出

1.exit

2.quit

MySQL目录结构

1.MySQL安装目录

配置文件 my.ini

2.MySQL数据目录

几个概念

数据库:文件夹

表:文件

数据:数据

SQL

1.什么是SQL?

Stryctured Query Language:结构化查询语言

其实就是定义了操作所有关系型数据库的规则.每一种数据库操作的方式存在不一样的地方,称为”方言”

2.SQL通用语法

1.SQL 语句可以单行或者多行书写,以分号结尾.

2.可使用空格和缩进来增强语句的可读性.

3.MYSQL数据库的SQL语句不区分大小写,关键字建议使用大写.

4.3种注释

* 单行注释: -- 注释内容 或 # 注释内容(myql 特有)

* 多行注释: /* 注释 */

3.SQL分类

1)DDL(Data Definition Language) (DDL 数据定义语言) 如：建库，建表.关键字:create,drop,alter等

2)DML(Data Manipulation Language)(DML 数据操纵语言)，如：对表中的记录操作增删改.关键字:insert,delete,update等

3)DQL(Data Query Language)(DQL 数据查询语言)，如：对表中的查询操作.关键字:select,where等

4)DCL(Data Control Language)(DCL 数据控制语言)，如：对用户权限的设置.关键字:GRANT,REVOKE等

DDL:操作数据库.表

1.操作数据库:CRUD

1.C(Create):创建

创建数据库:

create database 数据库名称;

创建数据库,判断不存在,再创建:

create database if not exists 数据库名称;

创建数据库,并指定字符集

create database 数据库名称 character set 字符集名;

练习:创建db4数据库,判断是否存在,并指定字符集为gbk

create database if not exists db4 character set gbk;

2.R(Retrieve):查询

查询所有数据库的名称:

show databases;

查询某个数据库的字符集:查询某个数据库的创建语句

show create database 数据库名称;

3.U(Update):修改

修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

4.D(Delete):删除

删除数据库

drop database 数据库名称;

判断数据库存在,存在再删除

drop database if exists 数据库名称;

5.使用数据库

查询当前正在使用的数据库名称

select database();

使用数据库

use 数据库名称;

操作表

1.C(Create):创建(在java一般有一个实体类就会与之对应创建一个表)

1.语法:

create table 表名(

列名1 数据类型1(长度),

列名2 数据类型2,

...

列名n 数据类型n

);

注意:最后一列,不需要加逗号(,)

数据库类型:

1.int:整数类型

age int,

2.double:小数类型

score double(5,2)

3.date日期,只包含年月日,yyyy-MM-dd

4.datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss

5.timestamp:时间错类型  包含年月日时分秒 yyyy-MM-dd                 HH:mm:ss

如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值

6.varchar:字符串(可变长度的字符)

name varchar(20):姓名最大20个字符

zhangsan 8个字符 张三 2个字符

创建表

create table student(

id int,

name varchar(32),

age int,

score double(4,1),

birthday date,

insert_time timestamp

);

复制表:

create table 表名 like 被复制的表名;

2.R(Retrieve):查询

查询某个数据库中所有的表名称

show tables;

查询表结构

desc 表名;

3.U(Update):修改

--1.修改表名

        alter table 原表名 rename to 新的表名;

2.修改表的字符集

alter table 表名 character set 字符集名称;

3.添加一列(字段)

alter table 表名 add 列名 数据类型(长度);

4.修改列名称 类型

alter table 表名 change 列名 新列名 新数据类型(长度);

5.改字段的类型(长度):

alter table 表名 modify 列名 新数据类型(长度);    扩容

6.删除列

alter table 表名 drop 列名;

4.D(Delete):删除

drop table 表名;

drop table if exists 表名;

客户端图形化工作:SQLYog

**DML:增删改表中数据(pdf第8节)

1.添加数据:

语法:

insert into 表名(列名1,列名2,...列名n)values(值1,值2,...值n);

insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');

注意:

1.列名和值要一一对应.

2.如果表名,不定义列名,则默认给所有列添加值

insert into 表名 values(值1,值2,...值n);

3.除了数字类型,其他类型需要使用引号(单双都可以)引起来

2.删除数据:

语法:

delete from 表名[where 条件]

delete from student where id=1;        --删除 id 为 1 的记录

-- 删除数据 删除 name 为 崔斯塔娜 并且 id = 1的记录

DELETE FROM student2 WHERE NAME='崔斯塔娜' AND id=1;

注意:

1.如果不加条件,则删除表中所有记录.

2如果要删除所有记录

1.delete from 表名; -- 不推荐使用.有多少条记录就会执行多少次删除操作.(日志中有记录,可恢复)

2.TRUNCATE TABLE 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表.(属于DDL,面试笔试常考)

3.修改数据:

语法:

update 表名 set 列名1 = 值1,列名2 = 值2...[where 条件];

注意:

1.如果不加任何条件,则会将表中所有记录全部修改.(业务场景不多)

DQL:查询表中的记录

select*from 表名;

1.语法(详见下方day03)

2.基础查询

1.多个字段的查询

select 字段名1,字段名2...from 表名;

select name,age from student;

注意:

如果查询所有字段,则可以使用*来替代字段列表.

2.去除重复

distinct(有两个结果集字段一样才可以去重)

select distinct        address from student;

3.计算列

一般可以使用四则运算计算一些列的值.(一般只会进行数值型的计算)

ifnull(表达式1,表达式2):null参与的运算,计算结果都为null

表达式1:哪个字段需要判断是否为null

如果该字段为null后的替换值.

4.起别名

as:as也可以省略

-- 给结果集的字段起别名

SELECT NAME 名字 ,address 地址 FROM student3;

SELECT NAME as 名字 ,address as 地址 FROM student3;

-- 给表起别名

SELECT t.name 名字 ,t.address 地址 FROM student3 t

3.条件查询

1.where字句后跟条件

2运算符

> 、< 、<= 、>= 、= 、<>  

BETWEEN...AND:

在一个范围之内，如：between 100 and 200相当于条件在 100 到         200 之间，包头又包尾

IN( 集合) :

集合表示多个值，使用逗号分隔

IS NULL:

查询某一列为 NULL 的值，注：不能写=NULL

例-- 查询 math 分数大于 80 分的学生

select * from student3 where math>80;

-- 查询 english 分数小于或等于 80 分的学生

select * from student3 where english <=80;

-- 查询 age 等于 20 岁的学生

select * from student3 where age = 20;

-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法

select * from student3 where age <> 20;

select * from student3 where age != 20;

LIKE ' 张%' :模糊查询

占位符:

_:单个任意字符

%多个任意字符

例:-- 查询姓马的学生

select * from student3 where name like '马%';

select * from student3 where name like '马';??????不行

33 / 33

-- 查询姓名中包含'德'字的学生

select * from student3 where name like '%德%';

-- 查询姓马，且姓名有两个字的学生

select * from student3 where name like '马_';

and  或 &&

or  或 ||

not  或 !

例:-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)

select * from student3 where age>35 and sex='男';

-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)

select * from student3 where age>35 or sex='男';

-- 查询 id 是 1 或 3 或 5 的学生

select * from student3 where id=1 or id=3 or id=5;

day03 DQL查询语句 约束 数据库的设计

DQL 查询语句

1排序查询

语法:order by

SELECT  字段名 FROM  表名 WHERE  字段= 值 ORDER BY  字段名 [ASC|DESC];

asc 升序,默认值

desc 降序

--日期类型的字段是可以排序的,离我们最近的日期大的,反之是小的.

例:select * from student3 t order by t.math desc;

2..聚合函数:将一列数据作为一个整体,进行纵向的计算

1.count:计算个数

1.一般选择非空的列:主键  --性能

2. count(*)

select count(id) from student;

2.max:计算最大值

select max(math) from student;

3.min:计算最小值

select min(math) from student;

4.sum:计算和

select sum(math) from student;

5.avg:计算平均值

select avg(math) from student;

注意:聚合函数的计算,排除null

解决方案:

1.选择不包含非空的列进行计算

2IFNULL函数

3.分组查询:

1.语法:group by 分组字段;

2.注意

1.分组之后查询的字段:分组字段,聚合函数

按照性别分组,分别查询男女同学的数学平均分

select sex,avg(math) from student group by sex;

按照性别分组,分别查询男女同学的数学平均分,人数

select sex,avg(math),count(id) from student group by sex;

按照性别分组,分别查询男女同学的数学平均分,人数 要求:数学分数低于70分的人不参加分组

select sex,avg(math),count(id) from student where math > 70 group by sex;

查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

2.where和having的区别?

1.where在分组之前进行限定,如果不满足条件,则不参与分组.having在分组之后进行限定,如果不        满足结果,则不会被查询出来.

2.where后不可以跟聚合函数,having可以进行聚合函数的判断

4.分页查询

1.语法:limit开始的索引,每页查询的条数;

2.公式:开始的索引 = (当前的页码 -1)*每页显示的条数

--每页显示3条记录

select*from student limit 0,3; --第1页

select*from student limit 3,3; --第2页       

select*from student limit 6,3; --第3页

3. limit 是一个MySQL"方言"

分组排序

-- select distinct ...from...where...group by...having...order by...limit

select sex,min(math) mins from student3 where math > 60 and name like ‘%马%’ group by sex having mins >60 order by mins desc limit 5;

约束

概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性.

分类:

1.主键约束:primary key

2.非空约束:not null

3.唯一约束:unique

4.外键约束:foreign key

非空约束:not null

1.创建表时添加约束

CREATE TABLE stu(

        id INT;

        NAME VARCHAR(20) NOT NULL --非空

2.创建表后,添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3.删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束:unique,某一列的值不能重复

1.注意:

唯一约束可以有多个null值,其他不能重复

2.创建表时,添加唯一约束

CREATE TABLE stu(

id INT;

        phone_number VARCHAR(20) unique --手机号

3.删除唯一约束

ALTER table stu drop index phone_number;

4.在表创建完后,添加唯一约束

ALTER table stu modify  phone_number varchar(20) unique;

主键约束:primary key

1.注意:

1.含义:非空且唯一

2.一张表只能有一个字段为主键

3.主键就是表中记录的唯一标识

2.在创建表时,添加主键约束

create table stu(

id int primary key, --给id添加主键约束

name varchar(20)

);

3.删除主键(自增后不能删主键)

--错误 alter table stu modify id int;

alter table stu drop primary key;

4.创建完表后,添加主键

alter table stu modify id int primary key;

5.自动增长:

1.概念:如果某一列是数值类型的,使用auto_increment 可以完成值的自动增长

2.在创建表时,添加主键约束,并且完成主键自增长

create table stu(

id int primary key auto_increment, --给id添加主键约束(自增)

name varchar(20),not null, --非空

sex varchar(4) unique --唯一

);

3.删除自动增长

ALTER TABLE stu MODIFY id INT;

4.添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束:foreign key 让表与表产生关系,从而保证数据的正确性.(pdf5.5)

1.在创建表时,可以添加外键

语法:

create table 表名(

....

外键列

constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)

例:constraint emp_dept_fk foreign key (dep_id) references department(id)

);

2.删除外键

alter table employee drop foreign key emp_dept_fk;

alter table 表名 drop foreign key 外键名称;

3.创建表之后,添加外键

alter table employee add constraint emp_dept_fk foreign key(dep_id) references department(id)

alter table 表名 add constraint外键名称 foreign key(外键字段名称) references 主表名称(主表列名称)

4.级联操作:在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

1.添加级联操作

语法:alter table 表名 add constraint 外键名称

foreign key(外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

2.分类

1. 级联更新：ON UPDATE CASCADE

2. 级联删除：ON DELETE CASCADE

数据库的设计

1.多表之间的关系

1分类

1.一对一(了解):

如:人和身份证

2.一对多(多对一):

如:部门和员工

分析:一个部门有多个员工,一个员工只能对应一个部门

3.多对多

如:学生和课程

分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

2.实现关系

1.一对多

如:部门和员工

实现方式:在多的一方建立外键,指向一的一方的主键.

2.多对多

如:学生和课程

实现方式:多对多关系实现需要借助第三张中间表.中间表至少包含两个字段,这两个字段作为第        三章表的外键,分别指向两张表的主键.

3.一对一(了解)

如:人和身份证

实现方式:可以在任意一方添加唯一外键指向另一方的主键.

多表设计

1，一个实体对应创建一个表，不要掺杂到一起

2，每个表中要保证有主键字段

3，根据实体之间的三种关系所对应的原则去创建关联字段(至于是否建立成真正的外键，看需求)

2.数据库设计的范式

概念:设计数据库时,需要遵循的一些规范.要遵循后边的范式要求,必须先遵循前边的所有范式要求.

分类:

1.第一范式(1NF):每一列都是不可分割的原子数据项

2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)

1.函数依赖:

2.完全函数依赖:

3.部分函数依赖:

4.传递函数依赖:

3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

数据库的备份和还原

1.命令行:

语法:

备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

还原:

1.登陆数据库

2.创建数据库

3.使用数据库

4.执行文件.source 文件路径

【石家庄校区】JavaWeb之数据库 MySQL

day02数据库的基本概念,MySQL数据库软件,SQL

数据库的基本概念

1.数据库的英文单词:DataBase 简称:DB

2.什么数据库:

用于存储和管理数据的仓库

3.数据库的特点:

1.持久化存储数据的.其实数据库就是一个文件系统

2.方便存储和管理数据

3.使用了统一的方式操作数据库 -- SQL

4.常见的数据库软件

参见<MySQL基础.pdf>

MySQL数据库软件

1.安装

参见<MySQL基础.pdf>

2.卸载

1.去mysql的安装目录找到my.ini文件

复制 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

2.卸载MySQL

3.删除C:/ProgramData目录下的MySQL文件夹.

3.配置

MySQL服务启动

1.手动

2.cmd ->services.msc 打开服务的窗口

3.使用管理员打开cmd

net start mysql : 启动mysql的服务

net stop mysql: 关闭mysql服务

MySQL登陆

1.mysql -uroot -proot

2.mysql -hip -uroot -p连接目标的密码

3.mysql --host=ip --user=root --password=连接目标的密码

MySQL退出

1.exit

2.quit

MySQL目录结构

1.MySQL安装目录

配置文件 my.ini

2.MySQL数据目录

几个概念

数据库:文件夹

表:文件

数据:数据

SQL

1.什么是SQL?

Stryctured Query Language:结构化查询语言

其实就是定义了操作所有关系型数据库的规则.每一种数据库操作的方式存在不一样的地方,称为”方言”

2.SQL通用语法

1.SQL 语句可以单行或者多行书写,以分号结尾.

2.可使用空格和缩进来增强语句的可读性.

3.MYSQL数据库的SQL语句不区分大小写,关键字建议使用大写.

4.3种注释

* 单行注释: -- 注释内容 或 # 注释内容(myql 特有)

* 多行注释: /* 注释 */

3.SQL分类

1)DDL(Data Definition Language) (DDL 数据定义语言) 如：建库，建表.关键字:create,drop,alter等

2)DML(Data Manipulation Language)(DML 数据操纵语言)，如：对表中的记录操作增删改.关键字:insert,delete,update等

3)DQL(Data Query Language)(DQL 数据查询语言)，如：对表中的查询操作.关键字:select,where等

4)DCL(Data Control Language)(DCL 数据控制语言)，如：对用户权限的设置.关键字:GRANT,REVOKE等

DDL:操作数据库.表

1.操作数据库:CRUD

1.C(Create):创建

创建数据库:

create database 数据库名称;

创建数据库,判断不存在,再创建:

create database if not exists 数据库名称;

创建数据库,并指定字符集

create database 数据库名称 character set 字符集名;

练习:创建db4数据库,判断是否存在,并指定字符集为gbk

create database if not exists db4 character set gbk;

2.R(Retrieve):查询

查询所有数据库的名称:

show databases;

查询某个数据库的字符集:查询某个数据库的创建语句

show create database 数据库名称;

3.U(Update):修改

修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

4.D(Delete):删除

删除数据库

drop database 数据库名称;

判断数据库存在,存在再删除

drop database if exists 数据库名称;

5.使用数据库

查询当前正在使用的数据库名称

select database();

使用数据库

use 数据库名称;

操作表

1.C(Create):创建(在java一般有一个实体类就会与之对应创建一个表)

1.语法:

create table 表名(

列名1 数据类型1(长度),

列名2 数据类型2,

...

列名n 数据类型n

);

注意:最后一列,不需要加逗号(,)

数据库类型:

1.int:整数类型

age int,

2.double:小数类型

score double(5,2)

3.date日期,只包含年月日,yyyy-MM-dd

4.datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss

5.timestamp:时间错类型  包含年月日时分秒 yyyy-MM-dd                 HH:mm:ss

如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值

6.varchar:字符串(可变长度的字符)

name varchar(20):姓名最大20个字符

zhangsan 8个字符 张三 2个字符

创建表

create table student(

id int,

name varchar(32),

age int,

score double(4,1),

birthday date,

insert_time timestamp

);

复制表:

create table 表名 like 被复制的表名;

2.R(Retrieve):查询

查询某个数据库中所有的表名称

show tables;

查询表结构

desc 表名;

3.U(Update):修改

--1.修改表名

        alter table 原表名 rename to 新的表名;

2.修改表的字符集

alter table 表名 character set 字符集名称;

3.添加一列(字段)

alter table 表名 add 列名 数据类型(长度);

4.修改列名称 类型

alter table 表名 change 列名 新列名 新数据类型(长度);

5.改字段的类型(长度):

alter table 表名 modify 列名 新数据类型(长度);    扩容

6.删除列

alter table 表名 drop 列名;

4.D(Delete):删除

drop table 表名;

drop table if exists 表名;

客户端图形化工作:SQLYog

**DML:增删改表中数据(pdf第8节)

1.添加数据:

语法:

insert into 表名(列名1,列名2,...列名n)values(值1,值2,...值n);

insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');

注意:

1.列名和值要一一对应.

2.如果表名,不定义列名,则默认给所有列添加值

insert into 表名 values(值1,值2,...值n);

3.除了数字类型,其他类型需要使用引号(单双都可以)引起来

2.删除数据:

语法:

delete from 表名[where 条件]

delete from student where id=1;        --删除 id 为 1 的记录

-- 删除数据 删除 name 为 崔斯塔娜 并且 id = 1的记录

DELETE FROM student2 WHERE NAME='崔斯塔娜' AND id=1;

注意:

1.如果不加条件,则删除表中所有记录.

2如果要删除所有记录

1.delete from 表名; -- 不推荐使用.有多少条记录就会执行多少次删除操作.(日志中有记录,可恢复)

2.TRUNCATE TABLE 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表.(属于DDL,面试笔试常考)

3.修改数据:

语法:

update 表名 set 列名1 = 值1,列名2 = 值2...[where 条件];

注意:

1.如果不加任何条件,则会将表中所有记录全部修改.(业务场景不多)

DQL:查询表中的记录

select*from 表名;

1.语法(详见下方day03)

2.基础查询

1.多个字段的查询

select 字段名1,字段名2...from 表名;

select name,age from student;

注意:

如果查询所有字段,则可以使用*来替代字段列表.

2.去除重复

distinct(有两个结果集字段一样才可以去重)

select distinct        address from student;

3.计算列

一般可以使用四则运算计算一些列的值.(一般只会进行数值型的计算)

ifnull(表达式1,表达式2):null参与的运算,计算结果都为null

表达式1:哪个字段需要判断是否为null

如果该字段为null后的替换值.

4.起别名

as:as也可以省略

-- 给结果集的字段起别名

SELECT NAME 名字 ,address 地址 FROM student3;

SELECT NAME as 名字 ,address as 地址 FROM student3;

-- 给表起别名

SELECT t.name 名字 ,t.address 地址 FROM student3 t

3.条件查询

1.where字句后跟条件

2运算符

> 、< 、<= 、>= 、= 、<>  

BETWEEN...AND:

在一个范围之内，如：between 100 and 200相当于条件在 100 到         200 之间，包头又包尾

IN( 集合) :

集合表示多个值，使用逗号分隔

IS NULL:

查询某一列为 NULL 的值，注：不能写=NULL

例-- 查询 math 分数大于 80 分的学生

select * from student3 where math>80;

-- 查询 english 分数小于或等于 80 分的学生

select * from student3 where english <=80;

-- 查询 age 等于 20 岁的学生

select * from student3 where age = 20;

-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法

select * from student3 where age <> 20;

select * from student3 where age != 20;

LIKE ' 张%' :模糊查询

占位符:

_:单个任意字符

%多个任意字符

例:-- 查询姓马的学生

select * from student3 where name like '马%';

select * from student3 where name like '马';??????不行

33 / 33

-- 查询姓名中包含'德'字的学生

select * from student3 where name like '%德%';

-- 查询姓马，且姓名有两个字的学生

select * from student3 where name like '马_';

and  或 &&

or  或 ||

not  或 !

例:-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)

select * from student3 where age>35 and sex='男';

-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)

select * from student3 where age>35 or sex='男';

-- 查询 id 是 1 或 3 或 5 的学生

select * from student3 where id=1 or id=3 or id=5;

day03 DQL查询语句 约束 数据库的设计

DQL 查询语句

1排序查询

语法:order by

SELECT  字段名 FROM  表名 WHERE  字段= 值 ORDER BY  字段名 [ASC|DESC];

asc 升序,默认值

desc 降序

--日期类型的字段是可以排序的,离我们最近的日期大的,反之是小的.

例:select * from student3 t order by t.math desc;

2..聚合函数:将一列数据作为一个整体,进行纵向的计算

1.count:计算个数

1.一般选择非空的列:主键  --性能

2. count(*)

select count(id) from student;

2.max:计算最大值

select max(math) from student;

3.min:计算最小值

select min(math) from student;

4.sum:计算和

select sum(math) from student;

5.avg:计算平均值

select avg(math) from student;

注意:聚合函数的计算,排除null

解决方案:

1.选择不包含非空的列进行计算

2IFNULL函数

3.分组查询:

1.语法:group by 分组字段;

2.注意

1.分组之后查询的字段:分组字段,聚合函数

按照性别分组,分别查询男女同学的数学平均分

select sex,avg(math) from student group by sex;

按照性别分组,分别查询男女同学的数学平均分,人数

select sex,avg(math),count(id) from student group by sex;

按照性别分组,分别查询男女同学的数学平均分,人数 要求:数学分数低于70分的人不参加分组

select sex,avg(math),count(id) from student where math > 70 group by sex;

查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

2.where和having的区别?

1.where在分组之前进行限定,如果不满足条件,则不参与分组.having在分组之后进行限定,如果不        满足结果,则不会被查询出来.

2.where后不可以跟聚合函数,having可以进行聚合函数的判断

4.分页查询

1.语法:limit开始的索引,每页查询的条数;

2.公式:开始的索引 = (当前的页码 -1)*每页显示的条数

--每页显示3条记录

select*from student limit 0,3; --第1页

select*from student limit 3,3; --第2页       

select*from student limit 6,3; --第3页

3. limit 是一个MySQL"方言"

分组排序

-- select distinct ...from...where...group by...having...order by...limit

select sex,min(math) mins from student3 where math > 60 and name like ‘%马%’ group by sex having mins >60 order by mins desc limit 5;

约束

概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性.

分类:

1.主键约束:primary key

2.非空约束:not null

3.唯一约束:unique

4.外键约束:foreign key

非空约束:not null

1.创建表时添加约束

CREATE TABLE stu(

        id INT;

        NAME VARCHAR(20) NOT NULL --非空

2.创建表后,添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3.删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束:unique,某一列的值不能重复

1.注意:

唯一约束可以有多个null值,其他不能重复

2.创建表时,添加唯一约束

CREATE TABLE stu(

id INT;

        phone_number VARCHAR(20) unique --手机号

3.删除唯一约束

ALTER table stu drop index phone_number;

4.在表创建完后,添加唯一约束

ALTER table stu modify  phone_number varchar(20) unique;

主键约束:primary key

1.注意:

1.含义:非空且唯一

2.一张表只能有一个字段为主键

3.主键就是表中记录的唯一标识

2.在创建表时,添加主键约束

create table stu(

id int primary key, --给id添加主键约束

name varchar(20)

);

3.删除主键(自增后不能删主键)

--错误 alter table stu modify id int;

alter table stu drop primary key;

4.创建完表后,添加主键

alter table stu modify id int primary key;

5.自动增长:

1.概念:如果某一列是数值类型的,使用auto_increment 可以完成值的自动增长

2.在创建表时,添加主键约束,并且完成主键自增长

create table stu(

id int primary key auto_increment, --给id添加主键约束(自增)

name varchar(20),not null, --非空

sex varchar(4) unique --唯一

);

3.删除自动增长

ALTER TABLE stu MODIFY id INT;

4.添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束:foreign key 让表与表产生关系,从而保证数据的正确性.(pdf5.5)

1.在创建表时,可以添加外键

语法:

create table 表名(

....

外键列

constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)

例:constraint emp_dept_fk foreign key (dep_id) references department(id)

);

2.删除外键

alter table employee drop foreign key emp_dept_fk;

alter table 表名 drop foreign key 外键名称;

3.创建表之后,添加外键

alter table employee add constraint emp_dept_fk foreign key(dep_id) references department(id)

alter table 表名 add constraint外键名称 foreign key(外键字段名称) references 主表名称(主表列名称)

4.级联操作:在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

1.添加级联操作

语法:alter table 表名 add constraint 外键名称

foreign key(外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

2.分类

1. 级联更新：ON UPDATE CASCADE

2. 级联删除：ON DELETE CASCADE

数据库的设计

1.多表之间的关系

1分类

1.一对一(了解):

如:人和身份证

2.一对多(多对一):

如:部门和员工

分析:一个部门有多个员工,一个员工只能对应一个部门

3.多对多

如:学生和课程

分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

2.实现关系

1.一对多

如:部门和员工

实现方式:在多的一方建立外键,指向一的一方的主键.

2.多对多

如:学生和课程

实现方式:多对多关系实现需要借助第三张中间表.中间表至少包含两个字段,这两个字段作为第        三章表的外键,分别指向两张表的主键.

3.一对一(了解)

如:人和身份证

实现方式:可以在任意一方添加唯一外键指向另一方的主键.

多表设计

1，一个实体对应创建一个表，不要掺杂到一起

2，每个表中要保证有主键字段

3，根据实体之间的三种关系所对应的原则去创建关联字段(至于是否建立成真正的外键，看需求)

2.数据库设计的范式

概念:设计数据库时,需要遵循的一些规范.要遵循后边的范式要求,必须先遵循前边的所有范式要求.

分类:

1.第一范式(1NF):每一列都是不可分割的原子数据项

2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)

1.函数依赖:

2.完全函数依赖:

3.部分函数依赖:

4.传递函数依赖:

3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

数据库的备份和还原

1.命令行:

语法:

备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

还原:

1.登陆数据库

2.创建数据库

3.使用数据库

4.执行文件.source 文件路径