MySql

一、SQL
Structured Query Language 结构化查询语言
作用：与数据库进行交互
知识扩展：
SQL标准，由ANSI（美国标准学会，属于ISO的核心成员）进行管理和维护的。
数据库厂商都支持该标准，并进行了扩展。扩展的部分，一般称之为方言。

SQL标准和方言：普通话和方言。
二、常用的数据库
1、Oracle
2、DB2
3、MySQL
4、SQL Server
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。
三、SQL语句的分类：

*DDL：Data Definition Language数据定义语言
*DML：Data Manipulation Language 数据操作语言
DCL：Data Control Language 数据控制语言
*DQL：Data Query Language 数据查询语言
*TPL：事务处理语言
数据库服务器、数据库、和表之间的关系：
mysql(数据库服务器)——多个DB(就是数据库)——每一个数据库是有多张表组成的（table）———表是由行和列组成（每一行又称之为一条记录第一行我们称之为字段）
4、
DDL：数据定义语言
作用：定义数据库或表结构用的 # 数据库的注释符号
关键字：
create创建
drop删除
alter 修改
show 查看数据库
对数据库结构操作：
不区分大小写 dos 命令行
\c清除当前输入的状态
show # 查看数据库
create database mydb; #创建数据数据库注意 mysql的结束 “”;“”用分号隔开
drop database mydb; #删除数据库
创建一个名称为mydb1的数据库。
mysql>CREATE DATABASE mydb1; (字符集采用数据库默认的–安装时的那个)
use database mydb1; # 使用数据库
查看数据库的创建细节
mysql>SHOW CREATE DATABASE mydb1;
查看当前所有的数据库
mysql>SHOW DATABASES;
创建一个使用gbk字符集的mydb2数据库。
mysql>CREATE DATABASE mydb2 CHARACTER SET gbk;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。
mysql>CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_general_ci;
查看当前数据库服务器中的所有数据库
mysql>SHOW DATABASES;
查看前面创建的mydb2数据库的定义信息
mysql>SHOW CREATE DATABASE mydb2;
删除前面创建的mydb1数据库
mysql>DROP DATABASE mydb1;
查看服务器中的数据库，并把mydb2的字符集修改为utf8;
mysql>ALTER DATABASE mydb2 CHARACTER SET utf8；
show collation; # 查看数据库中所有的校对规则
% 表示任意字符；
show collation like “%gb%”; #查看数据库中中文的校验规则
注： 用 校验规则用的是 collate
create database mydb2 character set gbk collate gbk_bin ; # 创建数据库mydb2,字符集用gbk,校验规则用gbk_bin
mysql中间敲得代码用空格隔开的；
alter database mydb1 character set utf8; #修改数据库mydb1的字符集 为utf8; 注意character set

总结起来关键字也就是 create show alter (修改) drop (删除) use
services.msc可以查看程序是否启动。
、、、、、、、、、、、、、以上是对数据库结构的操作—————————————————————————————-
对表的操作：
对表结构操作 ：
创建表之前要先选择数据库。 一个表就相当于一个类里面包括属性 也就是表的字段。
选择数据库 use
mysql>USE mydb2;
1、创建表 create table b (
id int,
name varchar(30)
);
2、查看：
show tables; # 查看当前数据库中的所有表
show create table t1；# 查看表t1#查看创建表的源码
3、修改
alter table t modify name varchar(60); #修改表格中name字段的字符段长度为60； 记得modify修改
4、删除
drop table t #删除表格t.

varchar(30); 数据库中的字符串类型。

创建一个员工表
mysql>CREATE TABLE employee(
id int,
name varchar(100),
gender varchar(10),
birthday date,
entry_date date,
job varchar(100),
salary float(8,2),
resume text
);
MySQL常用数据类型
**数值类型
BIT(M)** 位类型。M指定位数，默认值1，范围1-64
TINYINT [UNSIGNED] [ZEROFILL] 带符号的范围是-128到127。无符号0到255。
BOOL，BOOLEAN 使用0或1表示真或假
SMALLINT [UNSIGNED] [ZEROFILL] 2的16次方
INT [UNSIGNED] [ZEROFILL] 2的32次方
BIGINT [UNSIGNED] [ZEROFILL] 2的64次方
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] M指定显示长度，d指定小数位数
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 表示比float精度更大的小数

文本、二进制类型 ：
CHAR(size) char(20 ) 固定长度字符串
VARCHAR(size) varchar(20) 可变长度字符串
BLOB LONGBLOB 二进制数据
TEXT(clob) LONGTEXT(longclob) 大文本
时间日期 DATE/DATETIME/TimeStamp 日期类型(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间
向表中插入数据：
insert into t3 (id,name) values (1,’张三’);
向表中插入数据：
设置客户端和结果集的编码 用于正常的插入和显示插入的中文字符： set character_set_
注：每次启动dos都需要设置客户端和结果集的字符集

插入之前得先设置一下客户端的编码否则插入不进去
set character_set_client=gbk; 注意的是下划线的连接。
insert into t3 (id,name) values (1,’张三’);
查询表中插入的字段：
select * from t3; 显示的是t3表格中的所有内容
此时显示的内容为乱码，因为需要定义结果集的字符集
set character_set_results=gbk;
注意插入数据时into可以省略
insert t3(id,name) values (2,’李四’) # mysql int类型的数据可以直接写出来，字符串类型的需要加单引号。
查询所有的字段：
select * from t4; #显示的是t4表格中的所有字段的内容
插入数据 insert：三种方式都可以
单引号 日期戳 不能省 mysql中 into 字段 都可以省略
insert into t4(id,name) values(1,’张无忌’) ; #注意字符串类型在mysql中要用单引号隔开；
insert t4(id,name) values(2,’乔峰’) ; #插入时省略into
insert t4 values(3,’杨过’,’2014-4-3’) ; # 省略字段，意味着所有的字段都必须给值(自增例外) 注意日期也是要用单引号的。
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DML:数据操作语言
作用：操作的是表中的记录（数据）
关键字：INSERT UPDATE DELETE
MySQL:
字符串类型 使用单引号引起来 ‘abcdefg’
日期时间 使用单引号引起来 ‘2001-01-08’
特殊值 null
1、插入数据
insert: values
insert t4 values(3,’杨过’,’2014-4-3’) ;
2、修改/更新 数据 update set
update t4 set name =‘张三’where id =3; #将表t4的第三条记录姓名字段改为张三
update t4 set name =’张三’;# 将t4表中所有的字段的名字改为张三
更新
将表t4的第三条记录姓名字段改为 杨康
update t4 set name=’杨康’ where id = 3 ;
将所有记录的名字都改为东方不败
update t4 set name = ‘东方不败’ ;
修改多个字段
update t4 set id=6,name=’萧峰’ where id = 2 ;

删除 delete from 删除表格中的记录
delete from t4 where id = 4 ;
删除所有的记录
delete from t4 ;
删除所有的记录
truncate table t4 ;

给表t4增加一个字段address
alter table t4 add address varchar(100) ;
删除字段address
alter table t4 drop column address ; //alter table t4 drop address;
查看表的结构
desc t4 ;
TRUNCATE TABLE user; 把整张表格摧毁，然后重建的表结构。这比一行一行的删除行要快很多
//
数据查询语言 dql
DQL：数据查询语言
作用：查询
关键字：SELECT select查询
简单查询：
查看所有数据
select * from stu ; 查看stu表中的所有数据

查看李四的信息：
select * from stu where id = 2 ;
select * from stu where name=’李四’ ;

查看年龄在20~30之间的人
select * from stu where age >=20 and age <=30 ;
select * from stu where age between 20 and 30 ; # 包括20和30
查看所有人的姓名
select name from stu;
查看所有的的姓名,年龄，性别
select name,age,sex from stu ;
查看男性年龄在２０和３０之间的。　　　　注意在这字段与字段的隔开用关键字ａｎｄ
select＊　ｆｒｏｍ　ｓｔｕ　ｗｈｅｒｅ　　ｓｅｘ＝＇男＇　ａｎｄ　　　　ａｇｅ　ｂｅｔｗｅｅｎ　２０　ａｎｄ　３０；　　

字段可以有表达式

select id,china+10,english,history from score ;

给字段起别名

select id as 编号,china as 语文,english as 英语,history as 历史 from score ;
select id 编号,china 语文,english 英语,history 历史 from score ;

查看所有人考试的总分是多少

select id,china + english + history 总分 from score ;

查看总分大于200的人

select * from score where china + english + history > 200 ;

查询家在桃花岛或者黒木崖的人

select * from stu where address = ‘桃花岛’ or address = ‘黒木崖’ ;
select * from stu where address in(‘桃花岛’,’黒木崖’) ;

查询没有参加考试的人

select id ,name from stu where id not in(select sid from score) ;

查询没有地址的人

select * from stu where address = null ; #错误的
select * from stu where address is null ;

查询有地址的人

select * from stu where address is not null ;

排序(order by )　　ａｓｃ　升序

对考试的人的语文升序排列

select * from score order by china asc;

对考试的人的历史降序排列　　　　ｄｅｓｃ　降序

select * from score order by history desc;

根据多个字段进行排序(语文升序，对语文成绩一样的人再进行历史降序类排)

select * from score order by china asc,history desc;

根据考试总分降序进行排序

select *,china + english + history 总分 from score order by china + english + history desc ;

主键：唯一的去区分每一条记录的一列或者多列的值. 特点：唯一，非空

创建引用约束

alter table score add constraint stu_score_FK foreign key(sid) references stu(id) ;

删除约束

alter table score drop foreign key stu_score_FK ;

自动增长

create table t5
(
id int primary key auto_increment,
name varchar(20)
) ;
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、——————–
数据的完整性：
1、数据完整性是为了保证插入到表中的数据是正确的，它防止了用户可能的输入错误。
2、分为三类
实体完整性
域完整性
参照完整性
1、实体完整性 规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现
主键的特点：不能为null，必须有值，且不能重复。 primary key 主键
逻辑主键：不代表实际意义，只是区分不同记录用的。比如id
业务主键：代表者具体的实际意义。比如身份证号 用户名
CREATE TABLE t3(
id int PRIMARY KEY auto_increment, #auto_increment 数据库自动增长
name varchar(100)
);
2、域完整性
指数据库表的列（即字段）必须符合某种特定的数据类型或约束。
非空约束：not null
唯一约束：unique
CREATE TABLE t6(
id int PRIMARY KEY auto_increment,
username varchar(100) not null unique, 非空和唯一约束
gender varchar(10) not null 非空约束
);
3、参照完整性（多表） (也叫引用完整性)
表间的关系：
一对多（用的最多）
多对多（用的很多）
一对一（几乎不用）
一 对多：部门和员工的关系
CREATE TABLE department(
id int primary key,
name varchar(100)
);

CREATE TABLE employee(
id int primary key,
name varchar(100),
salary float(8,2),
department_id int,
CONSTRAINT department_id_fk FOREIGN KEY(department_id) REFERENCES department(id) #约束 constraint
);
多对多：老师和学员
CREATE TABLE teacher(
id int primary key,
name varchar(100),
salary float(8,2)
);
CREATE TABLE student(
id int primary key,
name varchar(100),
grade varchar(10)
);
CREATE TABLE teacher_student(
t_id int,
s_id int,
CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id),
PRIMARY KEY(t_id,s_id)
);
解释外键约束的格式：
create table score
(
id int primary key,
sid int ,
china int,
english int ,
history int,
constraint sid_FK foreign key(sid) references stu(id)
) ;
constraint（约束）sid_FK（fk起的外键的缩写） foreign key （sid） references(引用) stu(id)
（给约束起个名字 ） 外键 score表中的字段名 stu表的id
当score个表都建立好后没写外键约束，可以在外部添加方法：
创建约束：
alter table score add constraint stu_score_FK foregin key(sid) references stu(id); #记住外键的两个字段要加上括号。 也记住加上关键字add。
外键只能是主表中的主键 primary key . 外键所在的表称之为字表。
引用约束
注意： 1. 添加记录时必须先添加主表中的记录，再添加子表中的记录
2. 不能更改主表中具有外键约束的记录的主键
3. 删除记录的时候不允许删除具有外键关系的主表中的记录(删除的顺序应当是先删除字表中的记录，然后删除主表中的记录)
删除约束：
alter table score drop foregin key stu_score_FK; 删除外键约束
alter table tablename drop primary key ; 删除主键
总结一下 定义表的约束包括：
定义主键约束： primary key
定义主键自动增长 : auto_increment(需要与整型相关的数据)
定义唯一约束： unique
定义非空约束： not null
定义外键约束： constraint orderside_key_FK foregin key (orderside) references ordes(id)
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
数据库中存储数据是按页存的。每页大小是8kb.
连接查询：
select *ｆｒｏｍ　ｓｔｕ　ｃｒｏｓｓ　ｊｏｉｎ　ｓｃｏｒｅ；　　返回的是多表连接的笛卡尔积。‘
交叉查询时条件用关键字ｏｎ
查询每个人的考试成绩：
ｓｅｌｅｃｔ　＊ｆｒｏｍ　ｓｔｕ　ｓ　ｃｒｏｓｓ　ｊｏｉｎ　ｓｃｏｒｅ　ｃ　ｏｎ　ｓ．ｉｄ＝ｃ．ｓｉｄ；　＃条件是主外键相等。　　　这个就是交叉查询。　　#交叉 比较了45次　　　
交叉连接（cross join）:不带on子句时，返回连接表中所有数据行的笛卡儿积。

内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。
查询参加考试的人的成绩　　　ｉｎｎｅｒ　ｊｏｉｎ
select name,china,english,history,china+english+history 总分 from stu s inner join score c on s.id = c.sid ;　　＃　　ｓ　ｃ　是起的别名　ａｓ　可以省略掉。
内连接必须加上条件主外键的关系，外键是小于等于主键的个数的。

外连接：分为左外连接（left join）、右外连接（right join）。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。
关键字的左边或者右边。（ｌｅｆｔ　ｊｏｉｎ）（ｒｉｇｈｔ　ｊｏｉｎ）的左边或右边。
查询所有人的成绩：
select name,china,english,history,china+english+history 总分 from stu s left out join score c on s.id = c.sid ;

子查询
查询没有参加考试的人 用以上查询就不方便了
select * from stu where id not in (select sid from score);
注意的是所有的内连都可以用子查询。但是并不是所有的子查询都能转成内查询， select * from stu where id in(select sid from score);
或者： select name,china,english,history,china+english+history 总分 from stu s,score c where s.id=c.sid;

联合查询
将两个结果集变成一个结果集 中间用union隔开。

报表查询
对数据进行 数据行进行分组统计，其语法格式为：
[select …] from … [where…] [ group by … [having… ]] [ order by … ]
其中group by 子句指定按照哪些字段分组，having子句设定分组查询条件。在报表查询中可以使用SQL函数。
sql的函数包括 总计5个聚合函数 count sum max min avg
合计函数－count Count(列名)返回某一列，行的总数
Select count(*)|count(列名) from tablename
合计函数－SUM 求和
合计函数－AVG 求平均值
合计函数－MAX/MIN 求最大最小值
分组函数
select count(*) 数量,sex,name from stu group by sex,name ; #根据多个字段进行分组
分组条件
select count(),sex from stu where age >=16 group by sex having count() >1 ;
on 连接查询时设立条件
where 普通查询的条件
having 对分组设立条件。
日期函数
now（）； 显示当前时间 select now();

数据库的备份和恢复：
退出数据库 在dos窗口中进行备份和恢复
数据库的备份：
mysqldump -u root -proot mydb>d:\mydb.sql
备份转存 用户名密码 数据库的名字 > 表示要导到哪 注意不要加分号
数据库恢复：
首先创建数据库并选择该数据库
恢复：
mysql -u root -proot mydb