MySQL常用操作

MySQL篇

今日任务

Ø  完成对MySQL数据库和表以及表数据的CRUD操作

Ø  完成对系统模型可以分析其中表关系并完成建表操作

Ø  完成对MySQL数据库的多表查询及建表操作.

1.1      数据库整体介绍

【MySQL的概述】

Ø 什么是数据库：

数据库就是一个文件系统，需要通过标准SQL语句才能访问.

Ø 常见的数据库：

MySQL  ：开源免费的数据库，中小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始         收费.

Oracle ：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司

DB2     ：IBM公司的数据库产品,收费的.银行系统中用的比较多

SQLServer：MS公司.收费的中型的数据库.

SyBase ：已经不用了，但是供了一个非常专业数据建模的工具PowerDesigner.

SQLite : 嵌入式的小型数据库,应用在手机端，比如android和ios

Java相关的数据库：MYSQL，Oracle．

Ø 关系型数据库：

数据库中存的实体和实体之间的关系.

Ø MySQL数据库服务器结构

见图解

【MYSQL的安装和卸载】

安装和卸载的过程记录下服务器安装的路径和数据文件存放的路径.卸载的时候将这个两个路径下的文件一并都删除掉.

如果使用mysql -u root -p不好使，可以将mysql的bin目录配置到path变量值里面

【MYSQL的SQL语句】

Ø 什么是SQL

SQL：结构化查询语言.

Ø SQL的分类：

※数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。

※数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。

※ 数据查询语言(DQL)，例如：SELECT语句。（一般不会单独归于一类，因为只有一个语句）。

※数据控制语言(DCL)，例如：GRANT、REVOKE等语句。

※事务控制语言（TCL），例如：COMMIT、ROLLBACK等语句。

SQL语言包括四类种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML) 及数据控制语言(DCL)还有事务控制语言（TCL）。

1.2      SQL语句的使用

1.2.1    使用SQL语句对数据库进行CRUD操作

【创建数据库】

语法:

    * createdatabase 数据库名;

    * createdatabase 数据库名character set 字符集;

【查看数据库】

* 查看数据库服务器中的所有的数据库:show databases;

* 查看某个数据库的定义的信息:show create database 数据库名;

【删除数据库】

* drop database 数据库名称;

【修改数据库】

* alter database 数据库名 character set 字符集;

【其他的数据库操作命令】

* 切换数据库：use 数据库名;

* 查看正在使用的数据库:select database();

1.2.2    使用SQL语句对数据库中表进行CRUD的操作

【创建表】

create table 表名(

   字段名 类型(长度) 约束,

   字段名 类型(长度) 约束

);

Java               MYSQL

int                 int

float              float

double             double

char/String       char/varchar(char固定长度字符串，varchar可变长度的字                           符串)

Date               date,time,datetime,timestamp

文件类型            BLOB、TEXT   TEXT指的是文本文件  BLOB二进制文件

单表约束：

    * 主键约束(一个表中只能有一个主键，肯定是唯一的)：primary key

    * 唯一约束(必须是唯一的，但是一个表中可以有多个)：unique

    * 非空约束：not null

创建一个商品分类表：

    分类id int类型主键 自动增长（auto_increment）

    分类名称 字符串类型长度20

create table category(id int primary keyauto_increment,

name   varchar(20));

注意：建表之前一定先选择数据库

use 数据库名；

【查看表】

查看数据库中的所有表：show tables;

查看表结构：desc 表名;

查看表的创建信息：show create table 表名;

【删除表】

drop table 表名;

【修改表】

alter table 表名 add 列名 类型(长度) 约束;       修改表添加列

alter table 表名 modify 列名类型(长度) 约束; 修改表修改列的类型长度及约束

alter table 表名 change 旧列名新列名 类型(长度) 约束;   修改表列名

alter table 表名 drop 列名;                      修改表删除列

rename table 表名 to 新表名;                      修改表名

alter table 表名 character set 字符集;          修改表的字符集

1.2.3    使用SQL语句对表中的记录进行CRUD操作

创建商品表：

 

【插入记录】

语法：

* insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..);  向表中 插入某些列

* insert into 表 values (值1,值2,值3..); 向表中插入所有列

注意：

* 1.列名数与values后面的值的个数相等

* 2.列的顺序与插入的值得数据类型要保持一致

* 3.插入值得时候不能超过最大长度.

* 4.值如果是字符串或者日期需要加’’.

cmd下插入中文的乱码的解决：

* 修改mysql.ini文件.

    * 将[mysql]下面的字符集改为gbk

    * 重启mysql服务.services.msc

【修改记录】

语法：

* update 表名 set 字段名=值,字段名=值 [where 条件];

注意：

* 1.列名的类型与修改的值要一致

* 2.修改值得时候不能超过最大长度

* 3.值如果是字符串或者日期需要加’’

#where后的条件写法：#

    * >,<,=,>=,<=,<>

    * like 使用占位符 _ 和 %  _代表一个字符 %代表任意个字符.

    * in在某个范围中获得值.

增加cid这一列：alter table product add cid int;

【删除记录】

语法：

* delete from 表名 [where 条件];

注意：删除表中所有记录使用delete from 表名; 还是用truncatetable 表名;

* 删除方式：delete 只是将记录删除. 而truncate直接将表结构记录删除.

* 事务控制DML(transaction),而delete属于DML.如果在一个事务中，delete数据，这些数据可以找回.truncate删除的数据找不回来.

【查询记录】

语法：

* select [distinct]*[列名,列名] from 表 [where 条件].

简单查询:

* 1.查询所有的商品:

* 2.查询商品名和商品价格:

* 3.别名查询.使用的关键字是as.as可以省略的. 

    * 3.1表别名:  select * from product as p;

    * 3.2列别名：

* 4.去掉重复值的查询：

select运算查询：

* 1.将所有商品的价格+20元进行显示.

条件查询:

* 1.查询商品名称为xxx的商品所有信息：

* 2.查询商品价格>3000元的所有的商品信息:

(order by)排序查询:

* 1.查询所有的商品，按价格进行排序.(asc-升序,desc-降序)

* 2.查询名称有xxx的商品的信息并且按价格降序排序.

聚合函数:

* sum(),avg(),max(),min(),count();

* 1.获得所有商品的价格的总和：

* 2.获得所有商品的平均价格：

* 3.获得所有商品的个数：

分组查询：使用group by

* 1.根据cid字段分组，分组后统计商品的个数：

如果使用分组想要加条件进行筛选使用的关键字是having

select 列名或者* from表名 group by 列名 having 条件;

* 2.根据cid分组，分组统计每组商品的平均价格，并且平均价格> 3000：

1.3      SQL创建多表及多表的关系

1.3.1    需求

分类表和商品表之间是不是有关系?如果有关系，在数据库中如何表示这种关系.

1.3.2    技术分析和使用

【外键约束】

多个表之间是有关系的，那么关系靠谁来维护?

多表约束：

外键约束写法：

alter table product addforeign key (cid) references category(id);       

 

【多表的关系】

Ø 一对多关系：

客户和订单，分类和商品...

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

Ø 多对多关系：

学生和课程...

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

Ø 一对一关系：

在实际的开发中应用不多.因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

主键对应：让一对一的双方的主键进行建立关系.

 

1.5      分析商城表关系完成多表的查询

1.5.1    需求

在商城的案例中很多涉及多表查询的功能，[我的购物车]

1.5.2    技术分析和使用

【多表查询】

Ø 交叉连接查询(基本不会使用-得到的是两个表的乘积)

语法：

* select * from A,B;

Ø 内连接查询(使用的关键字 inner join  -- inner可以省略)

* 隐式内连接：

    * select* from A,B where 条件;

* 显示内连接：

    * select * from A inner join B on 条件;

Ø 外连接查询(使用的关键字 outer join -- outer可以省略)

* 左外连接：left outer join

    * select* from A left outer join B on 条件;

* 右外连接：right outer join

    * select* from A right outer join B on 条件;

【子查询】

SQL语句的嵌套查询：查询“手机产品”分类的商品信息：

首先要查询"手机产品"的id：

              select id from category where name='手机产品';

       然后拿id去商品表里面和cid进行匹配查询:

              select * from product where cid=(select id from category wherename='手机产品');

【练习】

按分类的名称统计商品的个数：

select c.name,count(*)from category c,product p where c.id=p.cid     groupby c.name;

根据订单的id查询商品和订单项的信息：

给你订单的id=1，查询这个订单具有的商品信息

    select * from product p,orderItem o where p.id=o.pid and o.oid=1;

【MySQL的分页查询】

* select * from product limit a,b;  --a:从哪开始,b:查询多少条.