五、常用的MySQL命令

一、SQL分类

DDL （数据定义语言）

数据定义语言 - Data Definition Language

用来定义数据库的对象，如数据表、视图、索引等

DML  （数据操纵语言）

数据处理语言 - Data Manipulation Language

在数据库表中更新，增加和删除记录

如 update， insert， delete

DCL （数据控制语言）

数据控制语言 – Data Control Language

指用于设置用户权限和控制事务语句

如grant，revoke，if…else，while，begin transaction

DQL （数据查询语言）

数据查询语言 – Data Query Language select

二、MySQL常用数据类型

字符串型

VARCHAR、CHAR

大数据类型

BLOB(存储二进制数据，如图片)、TEXT(存储文本数据，如介绍)

数值型

TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

逻辑性

BIT

日期型

DATE、TIME、DATETIME、TIMESTAMP

三、MySql的基本操作

Sql语句的命令是不分大小写的。

3.1.创建数据库

CREATE  DATABASE  [IF NOT EXISTS] db_name   

[create_specification [, create_specification] ...]

create_specification:   

[DEFAULT] CHARACTER SET charset_name 

[DEFAULT] COLLATE collation_name

CHARACTER SET：指定数据库采用的字符集

COLLATE：指定数据库字符集的比较方式

练习：

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

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

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

3.2.查看、删除数据库

（1）显示系统所有的数据库语句：

SHOW DATABASES

（2）显示数据库创建语句：

SHOW CREATE DATABASE db_name

（3）数据库删除语句：

DROP DATABASE  [IF EXISTS]  db_name

练习：

查看当前数据库服务器中的所有数据库

查看前面创建的mydb2数据库的定义信息

删除前面创建的mydb1数据库

3.3.修改数据库

ALTER  DATABASE  [IF NOT EXISTS] db_name   

[alter_specification [, alter_specification] ...]

alter_specification:   

[DEFAULT] CHARACTER SET charset_name 

[DEFAULT] COLLATE collation_name

练习：

切换数据库 use db_name;

查看当前使用的数据库 select database();

查看服务器中的数据库，并把其中某一个库的字符集修改为gbk;

3.4.查看、创建表(基本语句)

注意：创建表前，要先使用use db语句使用库。

CREATE TABLE table_name

(

field1  datatype,

field2  datatype,

field3  datatype

)character set 字符集 collate 校对规则

field：指定列名　datatype：指定列类型

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象

id        int                              

name string

password string

birthday date

练习：

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

删除user表：

创建一个员工表employee ---- 查看表结构: desc 表名;

查看employee表的结构：

3.5.定义单表字段的约束(保证数据的有效性和完整性)

定义主键约束

primary key:不允许为空，不允许重复

删除主键：alter table tablename drop primary key ;

主键自动增长 ：auto_increment

 

定义唯一约束

unique  

例如：name varchar(20) unique

定义非空约束

not null  

例如：salary double not null

3.6.修改表

使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.

练习：

修改表的名称：rename table 表名 to 新表名;

修改列的名称： change  [column] old_col_name column_definition;

修改表的字符集：alter table user character set utf8;

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

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

删除gender列。

修改表的字符集为utf8

3.7.数据库CRUD语句

Insert语句    (增加数据)

Update语句  (更新数据)

Delete语句   (删除数据)

Select语句　(查找数据)

3.7.1.INSERT 插入语句

使用 INSERT 语句向表中插入数据。

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

数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

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

字符和日期型数据应包含在单引号中。

插入空值：不指定或insert into table value(null)

练习：使用insert语句向表中插入数据

3.7.2.UPDATE更新语句

使用 update语句修改表中数据。

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

练习：

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

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

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

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

3.7.3.DELETE 删除语句

使用 delete语句删除表中数据。

如果不使用where子句，将删除表中所有数据。

Delete语句不能删除某一列的值（可使用update）

使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题（外键约束）

删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，truncate删除表的数据，原理是先将该表摧毁（不会影响主键），然后再重建该表。也就是说truncate摧毁表的时候，会将主键的初始值变为1.而delete删除，并不会重置主键的初始值。

练习：

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

删除表中所有记录。

使用truncate删除表中记录。

3.7.4.SELECT 查询语句

（1）语句一：

select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

from指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

练习：

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

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

过滤表中重复数据。

（2）语句二：

在select语句中可使用表达式对查询的列进行运算

在select语句中可使用as语句

练习

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

统计每个学生的总分。

使用别名表示学生分数。

（2）语句三：

使用where子句，进行过滤查询。

练习：

查询姓名为XXX的学生成绩

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

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

（4）语句四：

在where子句中经常使用的运算符

注意：当and和or运算符同时存在时，优先执行and。

练习：

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

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

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

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

（5）语句五：

使用order by 子句排序查询结果。

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

Asc 升序、Desc 降序，默认是不写时为 asc升序

ORDER BY 子句应位于SELECT语句的结尾。

练习：

对数学成绩排序后输出。

对总分排序按从高到低的顺序输出

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

（6）语句六：

使用group by 子句对列进行分组

Having和Where均可实现过滤，但在having可以使用聚集函数,having通常跟在group by后，它作用于分组

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

归类前：

归类后：

使用having 子句 对分组结果进行过滤

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

3.8.聚合函数

3.8.1.聚合函数COUNT

Count(列名)返回某一列，行的总数

练习：

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

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

统计总分大于250的人数有多少？

3.8.2.聚集函数SUM

Sum函数返回满足where条件的行的和

练习：

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

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

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

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

注意：sum仅对数值起作用，否则会报错。

注意：对多列求和，“，”号不能少。

3.8.3.聚集函数AVG

AVG函数返回满足where条件的一列的平均值

练习：

求一个班级数学平均分？

求一个班级总分平均分

3.8.4.聚集函数－MAX/MIN

Max/min函数返回满足where条件的一列的最大/最小值

练习：

求班级最高分和最低分（数值范围在统计中特别有用）

四、多表设计—外键约束

定义外键约束

foreign key

foreign key(ordersid) references orders(id)

多表设计中三种实体关系：

4.1.多表设计中会遇到的问题 

第一类:　一对多的关系

一对多的时候, 会在 多的一方添加 一方的主键做为 外键约束.

例如: 作者 与 博客之间的关系是 一对多的关系

 

第二类：　多对多的关系

多对多是时候, 需要引入 第三张关系表, 将两个表中的主键拿过来当作外键.

例如: 员工与 项目是 典型的多对多的关系

 

第三类：　一对一的关系

一对一,实际上完全可以 将任何一方 添加 一方中去 .

4.2.多表设计案例

teacher表对student表的一对多的关系，需要在多的一方(student)添加另一方(teacher)的主键作为外键约束。

CREATE TABLE teacher (

  id int(11) NOT NULL primary key auto_increment,

  name varchar(20) not null unique

 );

CREATE TABLE student (

  id int(11) NOT NULL primary key auto_increment,

  name varchar(20) NOT NULL unique,

  score double default '0',   //默认值

  teacher_id int(11) NOT NULL,

  FOREIGN KEY (teacher_id) REFERENCES teacher (id)

  ) ;

通过alter命令来添加外键约束

4.3.多表查询

(1)同时显示出dept和emp表的有效数据

(2)查询工资大于9000的员工来自于哪个部门？