mysql

一、SQL简介

1、结构化查询语言，一种定义、操作关系型数据库的语句。

2、由ANSI管理和维护。

工业标准：绝大多数数据库都支持。（SQL99）

方言：各个数据库厂商在工业标准上进行的扩展。

原则：尽量写SQL语句与具体数据库无关。

3、SQL的组成

DQL：数据查询语言

DML：数据操作语言

DDL：数据定义语言

DCL：数据控制语言

TPL：事务处理语言

CCL：指针控制语言

二、MySQL的安装与配置

1、验证安装是否成功

C:/>mysql -u root -p

三、DDL：数据定义语言

DDL：Data Definition Language

作用：用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。

常用关键字：

CREATE ALTER DROP TRUNCATE

约定：关键字小写，用户自定义的东西全部大写。多个单词组成，用下划线分隔。

语法：默认情况下，语句以分号为分界符。

大小写问题：window下MySQL不区分大小写。其他系统严格区分大小写。

1、库操作

创建一个名称为mydb1的数据库。

create database MYDB1;

查看当前有多少库。

show databases;

查看数据库的创建细节

show create database MYDB1;

创建一个使用gbk字符集的mydb2数据库。

create database MYDB2 character set gbk;

创建一个使用gbk字符集，并带校对规则的mydb3数据库。

create database MYDB3 character set gbk collate gbk_chinese_ci;

删除前面创建的mydb3数据库

drop database MYDB3;

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database MYDB2 character set utf8;

2、表结构

查看当前选择的数据库

select database();

选择一个数据库

use MYDB1;

创建一个员工表

create table EMPLOYEE(

ID int,

NAME varchar(100),

GENDER varchar(10),

BIRTHDAY date,

ENTRY_DATE date,

JOB varchar(100),

SALARY  float(8,2),

RESUME LONGTEXT

);

查看表结构

desc EMPLOYEE;

在上面员工表的基本上增加一个image列。

alter table EMPLOYEE add (IMAGE longblob);

修改job列，使其长度为60。

alter table EMPLOYEE modify JOB varchar(60);

删除image列。

alter table EMPLOYEE drop IMAGE;

表名改为user。

rename table EMPLOYEE to USER;

查看当前库中的所有表

show tables;

修改表的字符集为utf8

alter table USER character set utf8;

列名name修改为username

alter table USER change NAME USERNAME varchar(100);

四、DML：数据操作语言

作用：用于向数据库表中插入、删除、修改数据。

常用关键字：

INSERT UPDATE DELETE（新手注意和DDL关键字的区别）

数据：

字符或字符串数据，用单引号引起来

日期或时间，用单引号引起来，日期的格式用yyyy-MM-dd

空值：null。

使用insert语句向表中插入三个员工的信息

insert into USER values (1,'niuqian','unkown','1943-09-08','2014-07-30','ceo',10000,'boy or girl');

insert into USER (ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values (2,'niuqian','unkown','1943-09-08','2014-07-30','ceo',10000,'boy or girl');

插入中文：

insert into USER (ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values (3,'王璐','男性','1999-09-08','2014-07-30','cto',10000,'帅锅');

编码问题：

告知服务器，客户端使用的编码是什么？

set character_set_client=gbk;

告知服务器，返回的结果用什么编码？

set character_set_results=gbk;

查看数据用到的编码情况：

show variables like 'character_set%';

将所有员工薪水修改为5000元。

update USER set SALARY=5000;

将姓名为’zs’的员工薪水修改为3000元。

update USER set SALARY=3000 where USERNAME='王璐';

将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。

update USER set SALARY=6000,JOB='ufo' where USERNAME='王璐';

将wu的薪水在原有基础上增加1000元。

update USER set SALARY=SALARY+1000 where USERNAME='王璐';

删除表中名称为’zs’的记录。

delete from USER  where USERNAME='王璐';

删除表中所有记录。

delete from USER;  (逐条删除)

使用truncate删除表中记录

truncate table USER; (摧毁表格，让后重建表结构)

一、DQL：数据查询语言，针对单表的查询

查询表中所有学生的信息。

select * from STUDENT;

查询表中所有学生的姓名和对应的英语成绩。

select NAME,ENGLISH from STUDENT;

显示英语成绩，过滤表中重复数据

select distinct ENGLISH from STUDENT;

在所有学生数学分数上加10分特长分。

select NAME,MATH+10 from STUDENT;

统计每个学生的总分。

select NAME,MATH+CHINESE+ENGLISH from STUDENT;

使用别名表示学生分数。

select NAME as 姓名,MATH+CHINESE+ENGLISH 总分 from STUDENT;

查询姓名为wu的学生成绩

select * from STUDENT where NAME='陈冠希';

查询英语成绩大于90分的同学

select * from STUDENT where ENGLISH>90;

查询总分大于200分的所有同学

select * from STUDENT where (MATH+CHINESE+ENGLISH)>200;

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

select NAME,ENGLISH from STUDENT where ENGLISH between 80 and 90;

查询数学分数为89,90,91的同学。

select NAME,MATH from STUDENT where MATH in (89,90,91);

查询所有姓李的学生成绩。

select NAME,MATH from STUDENT where NAME like '李%';

查询数学分>80，语文分>80的同学。

select NAME,MATH,CHINESE from STUDENT where MATH>80 and CHINESE>80;

注意：not and or 优先级依次降低。一元运算符比二元的要高。

对数学成绩排序后输出。

select NAME,MATH from STUDENT order by MATH;

对总分排序后输出，然后再按从高到低的顺序输出

select NAME,MATH+CHINESE+ENGLISH 总分 from STUDENT order by 总分 desc;

对姓李的学生语文成绩排序输出

select NAME,CHINESE from STUDENT where NAME like '李%' order by CHINESE;

注意：订单，order。创建表时要注意不要和关键字冲突。

解决办法：

1、把用户定义的内容用反引号引起来。`(Esc按键的下方)

2、数据库命名习惯：表名ORDERS （建议）

二、完整性约束

数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误。

2.1实体（记录）完整性

规定表的一行（即每一条记录）在表中是唯一的实体(Entity)。实体完整性通过表的主键来实现。

主键：不能为null；唯一

创建一个表格T1，并声明一个主键

写法一：（只能指定一个字段作为主键）

create table T1(

ID int primary key,

NAME varchar(100)

);

写法二：（指定联合主键）

create table T2(

ID int,

NAME varchar(100),

primary key(ID)

);

写法三：(创建没有任何约束的表格，最后修改约束)建议

create table T3(

ID int,

NAME varchar(100)

);

alter table T3 add primary key(ID);

注意：

逻辑主键（建议）：除了唯一标识一条记录外，没有别的意义。一般取名ID

业务主键：还有一定的业务意义。

MySQL：

create table T4(

ID int primary key auto_increment,#自动增长，不建议使用。数据库迁移不方便，并不是所有的数据库都支持自动增长，比如Oracle就不支持。

NAME varchar(100)

);

2.2域（字段）完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束

1、约束数据的类型：强类型

2、非空约束：not null

3、唯一约束：unique

create table T5(

ID int primary key auto_increment,

USERNAME varchar(100) not null unique,

PHONE_NUM varchar(11) unique,

GENDER varchar(10) not null

);

2.3参照完整性（多表的设计）：定义外键

注意：类和表结构的关系；对象和记录的关系（帮助学习ORM：Hibernate、MyBatis）

ORM:Object Relation Mapping（Java面向对象 DB关系型数据库 映射）

第一：（非常多）一对多

create table CUSTOMERS(

ID int primary key,

NAME varchar(100),

ADDRESS varchar(100)

);

create table ORDERS(

ID int primary key,

ORDER_NUM varchar(100),

AMOUNT float(10,2),

CUSTOMER_ID int,

constraint CUSTOMER_ID_FK foreign key (CUSTOMER_ID) references CUSTOMERS(id)

);

 

注意：

表结构：Java类定义

表中的记录：Java对象

表间的关系：Java对象间的关系

第二：多对多（比较常见）

create table TEACHERS(

ID int primary key,

NAME varchar(100),

SALARY float(10,2)

);

create table STUDENTS(

ID int primary key,

NAME varchar(100),

GRADE char(1)

);

create table TEACHERS_STUDENTS(

T_ID int,

S_ID int,

primary key(T_ID,S_ID),

constraint T_ID_FK foreign key (T_ID) references TEACHERS(id),

constraint S_ID_FK foreign key (S_ID) references STUDENTS(id)

);

 

三、多表的查询（面试）

1、连接查询(面试几率很大)

a、交叉连接：（cross join）

返回多表的记录的笛卡尔积。（第一张表：5  第二章表：7，返回5*7=35）

select * from CUSTOMER,ORDERS;

select c.ID,c.NAME,o.ORDER_NUMBER,o.price from CUSTOMER c,ORDERS o;

b、内连接(inner join)

隐式内连接：select * from CUSTOMER c,ORDERS o where c.ID=o.CUSTOMER_id;

显式内连接：

select * from CUSTOMER c inner join ORDERS o on c.Id=o.CUSTOMER_id;

c、外连接(outer join)

查看所有客户信息，同时有订单的显示订单的详细信息。

左外：返回满足连接条件的记录，同时返回左表中剩余的其他记录。

select * from CUSTOMER c left outer join ORDERS o on c.ID=o.CUSTOMER_ID;

查看所有订单信息，同时返回下单的客户信息

右外：返回满足连接条件的记录，同时返回右表中剩余的其他记录。

select * from CUSTOMER c right outer join ORDERS o on c.ID=o.CUSTOMER_ID;

2、子查询

又称之为嵌套查询。查询中有查询。

执行顺序：先执行子查询（内查询），再执行外查询。

子查询：必须放在小括号之内。

查询陈冠希的所有订单信息。

形式一：多条查询语句

select ID from CUSTOMER where NAME='陈冠希';

select * from ORDERS where CUSTOMER_ID=1;

形式二：subselect（子查询）

select * from ORDERS where CUSTOMER_ID=(select ID from CUSTOMER where NAME='陈冠希');

查询订单价格大于100的有哪些客户

select * from CUSTOMER where ID in (select CUSTOMER_ID from ORDERS where PRICE>100);

3、MySQL常用的函数

SUM():求和

COUNT()：计数

AVG()：求平均

MAX()：求最大

MIN()：求最小

统计一个班级共有多少学生？

select count(*) from STUDENT;

统计数学成绩大于90的学生有多少个？

select count(*) from STUDENT where MATH>=90;

统计总分大于250的人数有多少？where可以使用表达式

select count(*) from STUDENT where (ENGLISH+CHINESE+MATH)>250;

统计一个班级数学总成绩？

select sum(MATH) from STUDENT;

统计一个班级语文、英语、数学各科的总成绩

select sum(CHINESE),sum(ENGLISH),sum(MATH) from STUDENT;

统计一个班级语文、英语、数学的成绩总和

select sum(ENGLISH+CHINESE+MATH) from STUDENT;

统计一个班级语文成绩平均分

select sum(CHINESE)/count(*) from STUDENT;

select avg(CHINESE) from STUDENT;  #如果字段为null，不参与平均的计算。

4、分组（报表）查询：group by

对订单表中商品归类后，显示每一类商品的总价

select PRODUCT,sum(PRICE) from PRODUCTS group by PRODUCT;

查询购买了几类商品，并且每类总价大于100的商品

select PRODUCT,sum(PRICE) from PRODUCTS group by PRODUCT having sum(PRICE)>100;

5、其他函数的简介

四、MySQL的备份与数据恢复

1、 备份数据(表结构和数据)

c:MySQLinmysqldump -h localhost -p3306 -u root -p day14>d:/day14.sql

2、恢复数据库中的数据

前提：必须先创建数据库名

方式一：

mysql>user day14;

mysql>source d:/day14.sql;

方式二：

c:MySQLinmysql -u root -p day14<d:/day14.sql