一.介绍

前面我们了解了简单的SQL语句的分类与增删改查，这节课我们更加深入的去了解我们的SQL语句，让我们对SQL有更多的了解，并深入的使用他。

二.知识点介绍

1、SQL通用语法

2、表数据相关语句

3、DOS操作数据乱码解决

三.上课视频对应说明文档

1、SQL通用语法

SQL语句可以单行或多行书写，以分号结尾；可使用空格和缩进来增强语句的可读性

MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：SELECT * FROM user。

同样可以使用/**/的方式完成注释，MySQL中的我们使用的数据类型

详细的数据类型(不建议详细阅读！)

1.1、数据库相关语句

（1）create database 数据库名;

创建数据库

（2）show databases;

查看数据库

（3）show create database 数据库名;

查看建库语句

（4）drop database 数据库名;

删除数据库

（5）use 数据库名;

使用指定数据库

1.2、表结构相关语句

建表语句:

create table 表名(

字段1 类型,

字段2 类型,

...

)

1.3、主键

1.3.1、主键约束

主键是用于标识当前记录的字段。它的特点是非空，唯一。在开发中一般情况下主键是不具备任何含义，只是用于标识当前记录。使用方式：

（1）在创建表时创建主键，在字段后面加上primary key.

create table tablename(     

id int primary key,

.......

)

（2）在创建表时创建主键，在表创建的最后来指定主键       

create table tablename(     

id int，

.......，

primary key(id)

)

1.3.2、删除主键

alter table 表名 drop primary key ;

1.3.3、主键自动增长

一般主键是自增长的字段，不需要指定。

需要添加自增长语句：主键字段后加auto_increment(只适用MySQL)

1.3.4、其他约束

其他约束还有如外键、唯一、非空等基础加强中详解

1.4、查看表

（1）desc 表名;

查看某个现有表的结构

（2）show tables;

查看当前数据库下所有的表

（3）show create table 表名;

查看建表语句

1.5、修改/删除表

（1）rename table 旧表名 to 新表名

修改表名

（2）alter table 表名 add 列名 类型;

添加列操作

（3）alter table 表名 modify 列名 类型;

修改列类型

（4）alter table 表名 drop 列名;

删除列

（5）alter table 表名 change 旧列名 新列名 类型;

修改列名称

（6）drop table 表名;

删除表

（7）truncate 表名;

摧毁表(删除表)

2、表数据相关语句

2.1插入数据

（1）insert into 表名(列名1,列名2, ...) values(列值1, 列值2, ...); 指定列插入

（2）insert into 表名 values(列值1, 列值2, ...); 

不指定列插入(所有列)

注意：

A、插入的数据应与字段的数据类型相同

B、数据的大小应该在列的长度范围内

C、在values中列出的数据位置必须与被加入列的排列位置相对应。

D、除了数值类型外，其它的字段类型的值必须使用 单引号引起。

E、如果要插入空值，可以不写字段，或者插入 null。 

F、对于自动增长的列在操作时，直接插入null值即可。

2.2、更新(修改)数据

update 表名 set 字段=值 ,... where 条件 修改指定条件的数据，将这条数据的指定列修改为指定值

2.3、删除数据

（1）delete from 表名 where 条件 

删除指定条件的数据

（2）delete from 表名     

删除表内所有数据

（3）truncate table 表名 

摧毁表再重新创建表，达到删除表内所有数据

2.4、查询语句

（1）select 字段1,字段2,...from 表名;

查询指定字段信息       

（2）select * from 表名;

查询表中所有字段

注意:使用"*"在练习，学习过程中可以使用，在实际开发中，不建议使用。

（3）select distinct 字段 from 表名; 

distinct用于去除重复

使用as 别名可以给表中的字段，表设置别名. 当查询语句复杂时，使用别名可以极大的简便操作。

（4）select 字段 as 别名,字段 AS 别名 from 表名;

我们在sql操作中，可以直接对列进行运算。

2.5、where子句

where语句表条件过滤。满足条件操作，不满足不操作，多用于数据的查询与修改。

格式 :

（1）select 字段  from 表名  where 条件;

例如：

select 字段 from 表名 where  name=’香蕉’;

select 字段 from 表名 where  price<5;

select 字段 from 表名 where  name>5’;

where条件种类:   

（1）比较运算符：>， >= ，< ，<=， = ，!=，(<>)

（2）逻辑运算符：And(并且)，or (或者)，not(取反)     

（3）between ...and...：在指定范围内   

注意:between 后面的值必须是小值 and后面的是大值

（4）in：可以比较多个值     

（5）like：模糊查询

2.6、通配符使用:

（1）%： 匹配多个

（2）_： 匹配一个

例如：

select 字段 from 表名 where 字段 like ‘%果’;  #任意个字符+果，以果结尾

select 字段 from 表名 where 字段 like ‘果%’;  #果+任意个字符，以果开头

select 字段 from 表名 where 字段 like ‘%果%’;  #包含果

select 字段 from 表名 where 字段 like ‘_果’;  #某果

select 字段 from 表名 where 字段 like ‘_果_’;  #某果某

2.7、null值操作

（1）is null;

判断为空

（2）is not null;

判断不为空

代码示例：

查询所有支出记录

SELECT * FROM gjp_ledger WHERE parent = '支出';

查询出金额价格大于1000的信息

SELECT * FROM gjp_ledger WHERE money >1000;

查询出金额在2000-5000之间的账务信息

SELECT * FROM gjp_ledger WHERE money >=2000 AND money <=5000;

SELECT * FROM gjp_ledger WHERE money BETWEEN 2000 AND 5000;

查询出金额是1000或5000或3500的商品信息

SELECT * FROM gjp_ledger WHERE money =1000 OR money =5000 OR money =3500;

SELECT * FROM gjp_ledger WHERE money IN(1000,5000,3500);

查询出账务的银行有工商银行的账务信息。

SELECT * FROM gjp_ledger WHERE account LIKE "%工商%";

查询出账务描述中是两个字的账务信息

SELECT * FROM gjp_ledger WHERE ldesc LIKE "__";

查询出账务accont不为null账务信息

SELECT * FROM gjp_ledger WHERE account IS NOT NULL;

SELECT * FROM gjp_ledger WHERE NOT account IS NULL;

2.8、order by子句

order by可以将查询出的结果进行排序。放置在select语句的最后。

（1）ASC：升序 (默认)

（2）DESC；降序

示例：

SELECT * FROM 表名 ORDER BY 字段ASC;

SELECT * FROM 表名 ORDER BY 字段DESC;

查询字段中最贵的3个。（逻辑倒叙排列取三个值）：

SELECT * FROM 表名 ORDER BY 字段DESC LIMIT 3;

注：LIMIT表示将查询出的结果取前，如LIMIT 3就表示取查询结果的前三条。

2.9、记录操作-聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

我们学习如下五个聚合函数：

（1）count：统计指定列不为NULL的记录行数；

格式：select count(字段) from 表名;

（2）sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

（3）max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

（4）min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

（5）avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

格式：

select avg(字段) from 表名;

select avg(字段) as ‘平均价格’ from 表名;

代码示例：

统计账务表中共有多少条记录

SELECT COUNT(*) FROM gjp_ledger;

统计账务表中金额大于3000的有多少条记录

SELECT COUNT(*) FROM gjp_ledger WHERE money>3000;

统计有多少收入的账务信息

SELECT SUM(money) FROM gjp_ledger WHERE parent = '收入';

SELECT SUM(IFNULL(money,0)) FROM gjp_ledger WHERE parent = '收入';

统计出记录中支出的平均值

SELECT AVG(money) FROM gjp_ledger where parent = '支出';

统计出商品表中收入的最大与最小值

SELECT MAX(money),MIN(money) FROM gjp_ledger;

SELECT MAX(money),MIN(IFNULL(money,0)) FROM gjp_ledger;

2.10、分组查询

分组查询是指使用group by字句对查询信息进行分组,例如：我们要统计出gjp_ledger表中所有分类账务的总数量,这时就需要使用group by 来对gjp_ledger表中的商品根据parent进行分组操作。

分组后我们在对每一组数据进行统计。

格式：

select 分组的字段 ,avg(计算的字段) from 表名 group by 分组的字段;

分组操作中的having子名是用于在分组后对数据进行过滤的，作用类似于where条件。

2.10.1、having与where的区别   

（1）having是在分组后对数据进行过滤.

where是在分组前对数据进行过滤

格式：select 分组的字段 ,avg(计算的字段) from 表名 where 计算的字段>20 group by 分组的字段 having avg(计算的字段)>20; 

逻辑：先确定where语句 ，然后在确定having语句

（2）having后面可以使用分组函数(统计函数)

where后面不可以使用分组函数。

代码示例：

对账务分类别统计，求出每一种类账务的总金额

SELECT parent,SUM(money) FROM gjp_ledger GROUP BY parent;

对账务分类别统计，求出每一种类账务的总金额，金额要大于20000才显示

SELECT parent,SUM(money) FROM gjp_ledger GROUP BY parent HAVING SUM(money)>2000;

3、DOS操作数据乱码解决

我们在dos命令行操作中文时，会报错

insert into user(username,password) values(‘张三’,’123’); 

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'username' at row 1

原因:

因为mysql的客户端编码的问题我们的是utf8,而系统的cmd窗口编码是gbk

解决方案（临时解决方案）:

（1）修改mysql客户端编码。

show variables like 'character%'; 查看所有mysql的编码

（2）在图中与客户端有关的编码设置:

client connetion result 和客户端相关

database server system 和服务器端相关

（3）将客户端编码修改为gbk.

set character_set_results=gbk; / set names gbk;

以上操作，只针对当前窗口有效果，如果关闭了服务器便失效。

如果想要永久修改，通过以下方式:

（1）在mysql安装目录下有my.ini文件

（2）default-character-set=gbk 客户端编码设置     

（3）character-set-server=utf8 服务器端编码设置

注意:修改完成配置文件，重启服务