JavaWeb-14 MySQL

SQL

一、SQL简介

SQL：Structured Query Language的缩写

中文名称：结构化查询语言

作用：是一种定义、操作、管理关系数据库的句法。大多数关系型数据库都支持。

结构化查询语言的工业标准由ANSI(美国国家标准学会，ISO的成员之一)维护。

二、常用数据库

1、Oracle、DB2、Informix、Sybase、SQL Server、ProstgreSQ面向对象数据库、MySQL、Access、SQLite（手机使用）等，数据库的模型有这几类：层次模型、网状模型、关系模型（大部分数据库）。

2、如何安装MySQL？

3、数据库服务器、数据库和表的关系：

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

数据库服务器、数据库和表的关系如图所示：

Client（SQL语句）（今天的关键）---->MySQL（Server）---->操作数据库、数据库的表

三、创建数据库

1、DDL数据定义语言：Data Definition Language

作用：用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。常用关键字：CREATE ALTER DROP TRUNCATE

2、登录MySQL：

指令：mysql -u root -p

3、创建数据库：

指令：create database 数据库名;

例如：create database day14;

创建数据库时指定字符集：指令：create database day14  character set gbk;

4、查看服务器上所有的数据库：

指令：show databases;

5、查看创建数据库的代码：

指令：show create database 数据库名;实例：show create database day14;

6、 修改某个数据库的编码

指令：alter database 数据库名  character set 字符编码;实例：alter database day14  character set utf8;

7、删除数据库

指令：drop database 数据库名;实例：drop database day14;

8、使用某个数据库

指令：use 数据库名;

四、在数据库中新建表

表结构的引入：为什么要用表结构？建表：（虽然表里不是java代码，但是java程序员需要想办法存进表里：

一个类中有多少个属性：对应表中多少列，一个类中有多少个对象，对应到表里就有多少行----->框架（系统架构师设计的来限制程序员编程的框架））。

1、建表

指令：    create table 表名(        字段名  数据类型，        字段名  数据类型    );实例：    create table Employee(        id int,        name varchar(20),        gender char(4),        birthday datetime,        entry_date datetime,        job varchar(100),        salary float(10,2),        resume text    );    char 与 varchar的区别:固定与可变（效率不一样）

2、显示当前数据下所有的数据表：

指令：show tables;

3、查看创建表的代码

指令：show create table 表名;实例：show create table employee;

4、查看表的明细，以列表的形式显示出结果

指令：desc 表名;/describe表名;实例：desc employee;/describe employee;

五、修改表的列

1、添加一列

指令：alter table  表名  add 列名  类型;实例：在上面员工表的基本上增加一个image列：alter table employee add image blob;

2、修改某列的数据类型

指令：alter table  表名 modify 列名  新的数据类型;实例：修改job列，使其长度为60:alter table employee modify job varchar(60);

3、删除某列：

指令：alter table 表名  drop 列名;实例：删除image列：alter table employee drop image;

4、修改表名：

指令：rename table 旧表名 to 新表名;实例：表名改为user：rename table employee to user;

5、修改表的字符集：

指令：alter table 表名 character set 字符集编码实例：修改表的字符集为gbk:alter table user character set gbk;

6、修改列名

指令：alter table 表名  change column 旧列名 新列名 数据类型;实例：列名name修改为usernamealter table user change column name username varchar(100);

7、删除表结构

指令：drop table 表名;实例：drop table user;

六、CRUD操作:

DML数据操纵语言：Data Manipulation Language

作用：用于向数据库表中插入、删除、修改数据。常用关键字：INSERT UPDATE DELETE

DQL数据查询语言（简单的）：Data Query Language

作用：查询数据。常用关键字：SELECT

1、插入：

指令：insert into 表名[(列名，列名)] values(值1，值2);实例：字符和日期型数据应包含在单引号中:insert into employee (id,name,sex,birthday,salary,entry_date,resume) values(1,'cgx','男','1982-10-1',10000,'2000-10-1','服务业干得不错');在输入命令之前：为了让服务器显示时和客户端输入时不会出现乱码现象，我们可以对服务器进行设置，操作如下：

2、删除数据：

指令：delete from 表名 【where条件】实例：delete from employee where name='cgx';

实例2： delete from employee;//清除表中所有记录

实例3： truncate table employee;//清除表中所有记录

 区别：    a.truncate table 删除数据效率更高(先摧毁整个表结构，再重建表)    b.delete 删除所有记录时，一条一条往后删除    c.truncate table只能删除所有    d.delete可以选择性的删除部分记录

3、更新

指令：update 表名 set 列名=值，列名2=值2 where 条件;实例：update employee set name='阿娇',gender='女' where name='aj';

4、查询：（重点！！）

用*代表所有列

distinct可以消除重复数据

order by 如果指定了多个排序字段，优先按第一个字段排，如果第一个字段取值相同时，才考虑第二个字段排序

指令：    select 列名 [as] 改列名,列名 from 表名 where 条件 order by 排序字段 （ASC(默认值) |  DESC）实例：查询表中所有学生的信息：select * from student;

实例：查询表中所有学生的姓名和对应的英语成绩：select name,english from student;

实例：过滤表中重复数据：select distinct english,name from student;

实例：在所有学生数学分数上加10分特长分：select math as 原来的数学成绩,math+10 as 现在数学成绩 from student;

实例：统计每个学生的总分：select chinese+math+english 总分 from student;

实例：使用别名表示学生分数：select name 姓名,math 数学, chinese 语文, english 英语 from student;

实例：查询姓名赵六的学生成绩：select  name 姓名,math 数学, chinese 语文, english 英语 from student where name='赵六';

实例：查询英语成绩大于70分的同学：select * from student where english>70;

实例：查询总分大于200分的所有同学：select *, chinese+math+english 总分 from student where ( chinese+math+english)>200;

实例：查询英语分数在 70－90之间的同学:select * from student where english between 70 and 90;

实例：查询数学分数为67,89,87的同学：select * from student where math in(67,89,87);

实例：查询所有姓李的学生成绩：select * from student where name like '李%';

实例：查询数学分>80，语文分>80的同学：select * from student where math>80 and chinese>80;

实例：对数学成绩排序后输出：select * from student order by math,chinese asc ;

实例：对总分排序后输出，然后再按从高到低的顺序输出：select *,  chinese+math+english from student order by chinese+math+english desc;

实例：对姓李的学生成绩排序输出：select * from student where name like '李%' order by chinese+math+english desc;    

七、多表操作

实例：

create table student(    id int primary key auto_increment,    name varchar(20) unique not null,    chinese float,    english float,    math float);

实例：

insert into student(id,chinese,english,math) values(1,89,78,90);insert into student(id,chinese,english,math) values(4,67,98,56);insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

1、实体完整性约束：

规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键（primary key)来实现。主键的特性：唯一性，非空性

2、域完整性约束:保证某个字段符合要求

    not null    unique:唯一 (对于null值无效)

3、参照完整性约束

外键建表时添加外键：constraint 外键名 foreign key(外键字段名) references 主键表（主键字段） 先建表再添加外键：alter table 表名 constarint FK_employee_dept_id foreign key(dept_id) references departement(id)表与表之间的关系:一对一 （一个人只能有一个身份证号码）

实例1：按照外键关联+唯一约束

create table PERSON(ID int primary key,NAME varchar(100));create table ID_CARD(ID int primary key,NUM varchar(20),PERSON_ID int unique,constraint PERSON_ID_FK foreign key(PERSON_ID) references PERSON(ID));insert into person(id,name)values(1,'wyj');        insert into ID_CARD(id,num,person_id)values(1,3701,1);

实例2：按照主键关联

create table PERSON(ID int primary key,NAME varchar(100));create table ID_CARD(ID int primary key,NUM varchar(20),constraint PERSON_ID_FK foreign key(ID) references PERSON(ID));insert into person(id,name)values(1,'wyj');        insert into ID_CARD(id,num)values(1,3701);

一对多（一个老师可以教多个学生，一个学生只能让一个老师教） （开发中用的最多）

实例：

create table DEPARTMENT(ID int primary key,NAME varchar(100) not null);create table EMPLOYEE(ID int primary key,NAME varchar(100),DEPT_ID int);alter table EMPLOYEE add constraint FK_employee_dept_id foreign key(dept_id) references department(id);insert into department(id,name,)values(1,'开发部');            insert into department(id,name,)values(2,'销售部');            insert into employee(id,name,dept_id)values(1,'A',1);            insert into employee(id,name,dept_id)values(2,'B',1);            insert into employee(id,name,dept_id)values(3,'C',2);

多对多  （一个老师可以教多个学生，一个学生也可以被多个老师教）用中间表来描述关系：    联合主键：primary key(字段1，字段2）

实例：

create table TEACHER(ID int primary key,NAME varchar(100));create table STUDENT(ID int primary key,NAME varchar(100));create table TEACHER_STUDENT(T_ID int,S_ID int,primary key(T_ID,S_ID),constraint T_ID_FK foreign key(T_ID) references TEACHER(ID),constraint S_ID_FK foreign key(S_ID) references STUDENT(ID));insert into student(id,name)values(1,'徐航');insert into student(id,name)values(2,'张成');insert into teacher(id,name)values(1,'laobi');insert into teacher(id,name)values(2,'wyj');insert into teacher_student(t_id,s_id)values(1,1);insert into teacher_student(t_id,s_id)values(1,2);insert into teacher_student(t_id,s_id)values(2,1);insert into teacher_student(t_id,s_id)values(2,2);

4、连接查询：

4.1、交叉连接（cross join）:不带on子句，返回连接表中所有数据行的笛卡儿积。    select * from A cross join B;(显式交叉连接)    select * from A ,B;           (隐式交叉连接）实例：        select * from department cross join employee;        select * from department,employee;

4.2、内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。select * from A inner join B on (连接条件) where 条件示例：    查询陈冠希的所有订单    查询开发部的A的资料    select * from department A inner join employee B on (A.id=B.id) where A.name='开发部';

    查询所有人的包括所在部门的所有资料：    select * from department A inner join employee B on (A.id=B.dept_id) 

4.3、外连接：分为左外连接（left out join）、右外连接（right outer join）。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。    select * from A left join B on (连接条件） where条件特点：左表中能连接上右表的记录显示出来，还会将左表中连接不上的其它所有记录也显示出来示例：    select * from department left join employee on (department.id=employee.dept_id);

    select * from A right join B on (连接条件） where 条件特点：将右表中的记录都要保留下来示例：    select * from department right join employee on (department.id=employee.dept_id);

4.4、子查询    指令：select * from 表名 where 字段名 in (select 字段名 from 表名  where条件);  (放在外面的查询叫父查询，放在内部的查询叫子查询)    子查询可以出现在select ,from ,where部分。    实例：查询属于开发部的员工信息：        select * from employee where dept_id in (select id from department where name='开发部');

4.5、联合查询select * from A unionselect * from B将A表中的记录与B表中的记录合并在一起，并消除重复记录select * from student where math>80unionselect * from student where name='胡八';

4.6、报表查询    指令：select * from 表名 where 条件  group by 分组字段 having(分组后的条件)    注意：having 是跟着group by一起出现的    聚合函数：        count():统计个数        sum()：求和        avg()：求平均值        max()最大值        min()最小值    实例：        统计一个班级共有多少学生？        select count(*) from student;

    实例：        统计数学成绩大于等于90的学生有多少个？        select count(*) from student where math>=90;

    实例：        统计总分大于250的人数有多少？        select count(*) from student where (math+chinese+english)>250;

    实例：        求一个班级数学平均分？        select avg(math) from student;

    实例：        求一个班级总分平均分：        select avg(math+chinese+english) from student;        select sum(math+chinese+english)/count(*) from student;

    实例：        求班级最高分和最低分：        select max(math+chinese+english) 最高分,min(math+chinese+english) 最低分 from student;

    实例：        报表查询每个学生的总分成绩：        select sum(math+english+chinese) from student group by name;

    实例：        报表查询每一科成绩大于60分的每一个学生的总分成绩：        select sum(math+english+chinese),name from student group by name having(avg(math+chinese+english)>60);

八、数据备份与还原

1、数据库备份：

WINDOWS命令行：mysqldump -h localhost -u root -p[也可以加密码] day14>d:/day14.sql   

2、还原

a.WINDOWS命令mysql -u root -psorry 数据库名（要事先存在）<test.sql（sql文件所在位置）实例：mysql -u root -p day14<d:\day14.sql

b.MYSQL命令  1.确保数据库存在    create database day14;  2.source sql文件所有位置    source d:/mysql.sql;    实例：source d:\day14.sql;    

资料下载