SQL语言中的基本操作

SQL：Structured Query Language, 结构化查询语言，SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能。其特点是：非过程性语言，那么什么是非过程语言呢？
过程性语言：当前的这条语句执行需要依赖于上一条或几条语句；
非过程性语言：可一条语句，就会执行一个结果。

SQL分类
（1）DDL （数据定义语言）：Data Definition Language，用来定义数据库的对象，如数据表、视图、索引等；
（2）DML （数据操纵语言）：Data Manipulation Language，在数据库表中更新，增加和删除记录，如 update， insert， delete；
（3）DCL （数据控制语言）：Data Control Language，指用于设置用户权限和控制事务语句，如grant，revoke，if…else，while，begin transaction；
（4）DQL （数据查询语言）：Data Query Language，如select。

1，创建数据库
语法 ： create database 数据库名称 ; (创建数据库采用数据库服务器默认字符集 )
复杂写法 create database 数据库名称 character set 字符集 collate 比较规则 ;
每次创建一个数据库在数据存放目录中生成一个文件夹 ， 每个文件夹中存在 db.opt 存放默认字符集和校对规则
例如：
创建一个名称为mydb1的数据库。 create database mydb1;
创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。create database mydb3 character set utf8 collate utf8_bin;

2，查询数据库

show databases; —– 查看所有数据库
show create database 数据库名; —— 查看数据编码集

3，删除数据库
语法 ：drop database 数据库名称;

4，修改数据库
修改数据库编码集 ：语法：alter database 数据库名称 character set 字符集 collate 比较规则; eg：修改mydb2字符集为gbk; alter database mydb2 character set gbk;

切换当前使用数据库： use 数据库名称
查看当前正在使用数据库: select database();

下面是有关数据库中表的操作：
1，创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字符集 collate 校对规则
field：指定列名　datatype：指定列类型。一个数据表 可以存在很多列，每列具有类型和长度，创建表时没有指定 字符集，将采用数据库默认字符集，创建表之前 必须使用use db 语法指定操作数据库
创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度

MySQL常用数据类型

（1）字符串型：VARCHAR、CHAR，Java 中 String char 对应于mysql 中字符串型 char varchar ，其中char是定长，如果不够用空格补全， varchar是变长，经常使用。char的效率要高。例如：char(8) 保存star，因为star只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度
（2）大数据类型：BLOB、TEXT，java中 大数据类型 inputStream 二进制文件和Reader文本文件对应于mysql 大数据类型 blob(存放大二进制数据) text(存放大的文本文件)，tinyblob tinytext 255字节 blob text 64KB mediumblob mediumtext 16MB longblob longtext 4GB。
（3）数值型：java 中 byte short int long float double对应于 mysql 中数值类型 TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
（4）逻辑型：BIT，java 中 boolean对应于mysql 逻辑型bit 存放一位数值 0 或者1
（5）日期型：DATE、TIME、DATETIME、TIMESTAMP。mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有) timestamp(日期时间都有 )datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间），即datetime需要手动录入时间，timestamp若不传入数据，会默认选择当前系统的时间。
定义单表字段的约束
约束是用来保证数据有效性和完整性，下面为约束的几种类型
（1）定义主键约束（primary key ）：信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键 （唯一 非空），不允许为空，不允许重复。
删除主键：alter table tablename drop primary key ;
主键自动增长 ：auto_increment
（2）唯一约束（unique）：该字段的值不允许重复，一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束。eg：name varchar(20) unique；
（3）非空约束（not null ）：该字段的值不能为空，eg：salary double not null。
举例：如果主键约束类型为 数值型 int bigint ，添加auto_increment 自动增长
create table employee2 (
id int primary key auto_increment,
name varchar(20) unique not null,
gender varchar(20) not null,
birthday date not null,
entry_date date not null,
job varchar(30) not null,
salary double not null,
resume longtext
);

2,数据表删除
语法：drop table 表名;

3,查看数据表结构
desc 表名; 查看表结构
show tables ; 查看当前库内所有表名
show create table 表名; 查看建表语句和字符集

4,修改表
1) 修改表名: rename table 旧表名 to 新表名;
2) 增加列 语法: alter table 表名 add 列名 类型(长度) 约束;
3) 修改现有列类型、长度和约束：语法：alter table 表名 modify 列名 类型(长度) 约束;
4) 修改现有列名称：语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
5) 删除现有列：语法：alter table 表名 drop 列名 ;

5，数据库CRUD语句
（1）Insert语句：使用 INSERT 语句向表中插入数据，语法如下：
INSERT INTO table [(column [, column…])]
VALUES (value [, value…]);
其中需要注意的有：插入的数据应与字段的数据类型相同；
数据的大小应在列的规定范围内，例如：不能将一个长度为60的字 符串加入到长度为30的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值：不指定或insert into table values (null)

例如：插入一条中文记录
insert into employee(id,name,job,salary) values(1,’小明’,’医生’,1500);
出错了：
ERROR 1366 (HY000): Incorrect string value: ‘\xC3\xF7’ for column ‘name’ at row 1 ;
错误原因：mysql client 采用默认字符集编码 gbk
此时，查看系统所有字符集：show variables like ‘character%’;
解决办法：修改客户端字符集为gbk
第一种：当前窗口临时修改 set names gbk ;只对当前窗口有效，关闭后就会失效
第二种：配置mysql/my.ini 文件
[mysql] 客户端配置：修改客户端字符集 [mysql] 后字符集 default-character-set=gbk
[mysqld] 服务器端配置

MySQL中共有6个地方字符集 ：client connetion result是和客户端相关 、database server system是和服务器端相关：
client是客户端使用的字符集。
connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
server是服务器安装时指定的默认字符集设定。
system是数据库系统使用的字符集设定（utf-8不可修改）

（2）Update语句：使用 update语句修改表中数据。语法如下：
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]
注意点：
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

（3）Delete语句：使用 delete语句删除表中数据，语法如下：
delete from tbl_name
[WHERE where_definition]
注意点：
如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

（4）Select语句：基本语法如下：
SELECT [DISTINCT] *|{column1, column2. column3..}
FROM table;

其中：
select 指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。
from指定查询哪张表。
DISTINCT可选，指显示结果时，是否剔除重复数据
注意点：
（1）在select语句中可使用表达式对查询的列进行运算
SELECT *|{column1｜expression, column2｜expression，..}
FROM table;
（2）在select语句中可使用as语句
SELECT column as 别名 from 表名;
（3）使用where子句，进行过滤查询
在where子句中经常使用的运算符：

Like语句中，% 代表零个或多个任意字符，_ 代表一个字符。
举一些小例子：
查询英语分数在 90－100之间的同学：select * from exam where english>=90 and english <= 100; 或者：select * from exam where english between 90 and 100;
查询数学分数为65,75,85的同学：select * from exam where math in(65,75,85);
查询所有姓赵的学生成绩：select * from exam where name like ‘赵%’;
查询英语分>80，语文分>80的同学：select * from exam where english > 80 and chinese > 80;
查询语文没有成绩学生：select * from exam where chinese is null;
查询语文有成绩学生：select * from exam where chinese is not null;
（4）使用order by 子句排序查询结果。
SELECT column1, column2. column3..
FROM table;
order by column asc|desc
其中：
Order by 指定排序的列，排序的列即既可以是表中的列名，也可以是select 语句后指定的列名；
Asc 升序、Desc 降序；
ORDER BY 子句应位于SELECT语句的结尾。
（5）聚集函数－count：Count(列名)返回某一列，行的总数
Select count(*)|count(列名) from tablename
[WHERE where_definition]

例如：
统计一个班级共有多少学生？：select count(*) from exam;
统计英语成绩大于90的学生有多少个？：select count(*) from exam where english > 90;
统计总分大于220的人数有多少？：select count(*) from exam where chinese+math+english > 220;
（6）聚集函数－SUM：Sum函数返回满足where条件的列的和（忽略NULL），sum仅对数值起作用，否则会报错。对多列求和时“，”号不能少。
Select sum(列名)｛,sum(列名)…｝ from tablename
[WHERE where_definition]
例如：
sum 统计某一列数据的和：select sum(列名) from 表名;
统计一个班级数学总成绩？：select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩：select sum(chinese),sum(math),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from exam; 或者：select sum(chinese)+sum(math)+sum(english) from exam;
若有一个同学小明的语文null ，null进行所有运算，结果都是null：
select sum(chinese)+sum(math)+sum(english) from exam; 含有小明英语和数学成绩
select sum(chinese+math+english) from exam; 不含小明英语和数学成绩

使用ifnull函数处理null情况 ：ifnull(xxx,0) 如果为null，则值是0
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 含有小明英语和数学成绩
统计一个班级语文成绩平均分：select sum(chinese)/count(*) from exam;

（7）聚集函数－AVG：AVG函数返回满足where条件的一列的平均值
例如：
求一个班级数学平均分：select avg(math) from exam;
求一个班级总分平均分：select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

（8）聚集函数－MAX/MIN：Max/min函数返回满足where条件的一列的最大/最小值。
Select max(列名)　from tablename
[WHERE where_definition]
例如：
求班级最高分和最低分：select max(chinese+math+english),min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

（9）GROUP BY子句：使用group by 子句对列进行分组
可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值，使用having 子句 对分组结果进行过滤（Having和where均可实现过滤，但在having可以使用聚集函数,having通常跟在group by后，它作用于分组
）。
SELECT column1, column2. column3.. FROM table;
group by column having …
举例如下：
create table orders(
id int,
product varchar(20),
price float
);

insert into orders(id,product,price) values(1,’电视’,900);
insert into orders(id,product,price) values(2,’洗衣机’,100);
insert into orders(id,product,price) values(3,’洗衣粉’,90);
insert into orders(id,product,price) values(4,’桔子’,9);
insert into orders(id,product,price) values(5,’洗衣粉’,90);

对订单表中商品归类后，显示每一类商品的总价（需要按照商品名称进行分组）：
select product,sum(price) from orders group by product;
查询购买了几类商品，并且每类总价大于100的商品：select product,sum(price) from orders group by product having sum(price) > 100;
注意：where 和 having 条件语句的区别 ？
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤
使用where地方都可以用having替换，但是having可以使用分组函数，而where后不可以用分组函数。

使用GROUP BY 时，SELECT 子句中只能由以下部分组成：
1，汇总函数
2，GROUP BY 中出现的列名
3， 1和2通过函数和表达式的结果

例如：
SELECT A_ID ,COUNT(*) FROM C GROUP BY A_ID; =>合法
SELECT A_DATE,COUNT(*) FROM C GROUP BY A_ID; =>不合法，按A_ID分组后，对于其他各列的查询只能是汇总查询，否则没有意义

小结 select 语句 ： S-F-W-G-H-O 组合 select … from … where … group by… having… order by … ; 它们的顺序不能改变