mysql数据库（二）

 SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
 在数据库系统中，SQL语句不区分大小写（建议大写）
 SQL语句可单行或多行书写，以";"结尾
 关键词不能跨多行或简写
 用空格和缩进来提高语句的可读性
 子句通常位于独立行，便于编辑，提高可读性

SQL语句分类
DDL: Data Defination Language 数据定义语言：CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言：INSERT，DELETE，UPDATE
DCL：Data Control Language 数据控制语言：GRANT，REVOKE，COMMIT，ROLLBACK
DQL：Data Query Language 数据查询语言：SELECT

---

数据库操作

---

创建数据库

cerate database|schema db_name;

修改数据库

alter database db_name character set utf8;  --修改数据库字符类型

删除数据库

drop database|schema db_name;

查看数据库列表

show databases;

创建好数据库后，可以通过命令：use db_name 进入数据库
在数据库中，我们可以创建表、视图、函数、触发器等等
首先来说一下数据库最基本的组成：表操作，表是由数据组成的，数据就要区分数据类型，在这里我们先总结一下数据类型知识：

mysql支持多种数据类型：

数值类型
日期/时间类型
字符串(字符)类型

1、整型

tinyint(m)     1个字节 范围(-128~127)
smallint(m)    2个字节 范围(-32768~32767)
mediumint(m)   3个字节 范围(-8388608~8388607)
int(m)         4个字节 范围(-2147483648~2147483647)
bigint(m)      8个字节 范围(+-9.22*10的18次方)

加了unsigned，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

2、浮点型(float和double)，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

3、字符串(char,varchar,_text)

char(n)      固定长度，最多255个字符
varchar(n)   可变长度，最多65535个字符
tinytext     可变长度，最多255个字符
text         可变长度，最多65535个字符
mediumtext   可变长度，最多2的24次方-1个字符
longtext     可变长度，最多2的32次方-1个字符
BINARY(M)    固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

4.日期时间类型
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

NULL           数据列可包含NULL值
NOT NULL       数据列不允许包含NULL值
DEFAULT        默认值
PRIMARY KEY    主键
UNIQUE KEY     唯一键
CHARACTER SET  name 指定一个字符集
数值型
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED       无符号

学习了上面的数据类型和修饰符，下面我们开始来创建表：
基本格式：create table tbl_name (col1 type1 修饰符，col2 type2,…)

create table students(
id int unsigned primary key auto_increment,    /*设id为表主键，整型自动递增*\
name char(10) not null,   --name不能为空
gender enum('m','f') default 'm',   --枚举型，值只能取m或f，默认是m
mobile varchar(11) unique );   --唯一键

这样一个基本的students表就创建完成了，可以通过命令 show create table students 查看表创建过程， 通过 desc students 命令查看表的结果组成：

还可以通过已创建的表间接创建新表：

create table teacher like students;     //创建和student表结构一样的teacher表，没有数据
insert teacher select * from student;  //复制student表的数据
create table techer select * from student;  //直接复制表格

往表中插入数据：

insert students(name,gender,mobile) values(
'daqiao','f','15800888088');   --注意id为自增型，可以不赋值，默认从1开始，字符类型的赋值需要''括起来

也可以这样按顺序插入：

insert students values (3,'xiaoqiao','f','13088906886');

还可以指定数据项插入：

insert students set name='liubei';  --不为空值必须要填

修改数据

update students set mobile='1000000001' where id=4;

修改表结构：

alter table students add class varchar(10) after gender;  --在gender之后增加class列

alter table students rename stds;              --重命名表名
alter table stds modify name varchar(10);      --修改name数据类型
alter table stds drop column mobile;           --删除mobile列
alter table stds add phone varchar(10) before class;   --在class后增加phone列
alter table drop class;                        --删除class列
alter table stds change  column phone mobile char(11); --更改phone为mobile并修改数据类型
alter table stds character set utf8;           --修改表的字符类型
alter table stds change name name varchar(20); --修改name数据类型
alter table stds add unique key(mobile);       --修改mobile为唯一键

删除表

drop table stds;      --删除表
truncate table stds   --清空表中的值
delete from stds where id=1;  --删除表中指定值 

表查询
select查询语句几乎是我们在数据库中使用最多的语句，也是必须掌握的语句。
最简单的整表查询：

select * from classes;

select name,age from students;                   --只查看name和age两列的值
select * from students where age>30 and age<50;  --查询出age大于30且小于50的数据

LIKE:
  % 任意长度的任意字符
  _ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：and、or、not、xor
示例：查找name以x开头的数据：

ORDER BY: 根据指定的字段对查询结果进行排序
 升序：ASC 默认值，可以不写
 降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

select * from students order by age;   --按age从小到大排序显示
select * from students order by age desc limit 3;   --按从大到小排序，且只显示前3个数据
select * from students order by age desc limit 3,3; --跳过前三个，显示接下来的三个，即4到6的数据

有时候我们需要查询某个相同类型的平均值或者其他数据集计算，只使用单纯的查询很难达到效果，这时候使用分组可以快速达到目的，列如我们要分别查询男生女生的平均年龄，或者各班同学的平均分数，就需要用到分组。
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算avg(), max(), min(), count(), sum()等
HAVING: 对分组聚合运算后的结果指定过滤条件

以classID作为分组项，查询各班的平均分数，且过滤掉classID为null的数据项
select classid,avg(sorce) from students group by classid having classid is not null;
也可以写为下面的语句，注意分组后过滤用having，分组前过滤用where
select classid,avg(sorce) from students where classid is not null group by classid;

---

多表查询

---

我们需要查询的数据往往分布在几个表中，这就要用到多表查询，多表查询是数据库查询的一大难点也是重点。

我们这里简单总结一下常用的四大连接方式和三个延伸方式：