MySQL数据库入门与实战

常见数据库

关系型数据库

常见的关系型数据库有 MySQL、Oracle、SQL Server、JDBC、DB2 等；主要是用来描述实体与实体之间的关系，实实在在存在的事物。例如：男生和女生、学生和班级、员工和部门。

非关系型数据库

常见的非关系型数据库有 Redis、MongoDB、NoSQL 等；非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

数据库与服务器

数据库

数据库管理软件。通常一个应用对应一个数据库。

服务器

服务器就是一台电脑，这台安装相关的服务器软件，这些软件监听不同的端口号，根据用户访问的端口号，提供不同的服务。

MySQL 的 SQL 语句

SQL：Structure Query Languages （结构化查询语言）

数据定义语言(DDL)

定义数据库，数据表的结构：create(创建)、drop(删除)、alter(修改)。

数据操纵语言(DML)

主要是用来操作数据：insert(插入)、update(修改)、delete(删除)。

数据控制语言(DCL)

定义访问权限，取消访问权限，安全设置 grant。

数据查询语言(DQL)

select(查询) from 子句 where 子句。

数据库的 CRUD 的操作

前置知识

• 登录数据库：mysql –uroot –proot

• 退出数据库：exit、quit、\q、Ctrl+c

• 退出当前命令行：\c

• 复制命令行的内容：标记->选择内容->回车，不用Ctrl+C

• 导出数据库文件：

1. 进入 MySQL 的安装目录的 bin 目录，如：D:\Program Files (x86)\MySQL\MySQLServer5.0\bin
2. 用 cmd 打开
3. 输入命令：mysqldump –h localhost –u root –p 数据库名字>导出路径

mysqldump –h localhost –u root –p worker>e:worker.sql

• 启动MySQL服务：net start mysql
• 关闭MySQL服务：net stop mysql

创建数据库

create database 数据库名字;

create database test;

• 创建数据库的时候，指定字符集
• create database 数据库的名字 character set 字符集;

create database test character set utf8;

create database 数据库的名字 character set 字符集 collate 校对规则;

create database test character utf8 collate utf8_bin;

查看数据库

show create database 数据库名字;

查看所有数据库

show databases;

注意：三个文件不可删
information_schema 相关信息约束
performance_schema 相关表现约束
mysql

修改数据库的操作

修改数据库的字符集

alter database 数据库的名字 character set 字符集

alter database test character set gbk;

删除数据库

drop database 数据库名字;

切换数据库(选中数据库)

use 数据库名字;

查看当前正在使用的数据库

select database ();

表的 CRUD 操作

创建表

create database 数据库名字  
create table 表名(  
列名 列的类型(长度) 约束,  
列名2 列的类型(长度) 约束  
);

列的类型

• 列的约束
  主键约束：primary key
  唯一约束：unique
  非空约束：not null

create table student(
sid int primary key,
sname varchar(10),
sex int,
age int
);

查看表

查看所有的表

show tables;

查看表的创建过程

show create table 表名;

show create table student;

查看表的结构

desc student;

修改表

添加(add)，修改列(modify)，修改列名(change)，删除列(drop)，修改表名(rename)，修改表的字符集

• 添加列(add)

alter table 表名 add 列名 列的类型 列的约束

alter table student add score int not null;

• 修改列(modify)

alter table student modify sex varchar(2);

• 修改列名(change)

alter table student change sex gender varchar(2);

• 删除列(drop)

alter table student drop score;

• 修改表名(rename)

rename table student to students;

• 修改表的字符集

alter table student character set gbk;

删除表

drop table students;

移动列的顺序

alter table 表名 modify 字段名 字段类型 after 字段
alter table student modify sex int(11) after sname;(移动到sname后面)
alter table student modify sex int(11) first;(移动到最前面)

SQL 表中的 CRUD 的操作

插入数据

insert into 表名(列名1,列名2,列名3) values(值1,值2,值3);

insert into student(sid,sname,sex,age) values(1,'zhangsan',1,22);

• 简单写法(插入部分列列名不可省略)

insert into student values(2, 'zhangsan',1,22);

• 批量插入

insert into student values
(3, 'zhangsan',1,22),
(4, 'zhangsan',1,22),
(5, 'zhangsan',1,22);

查看表中数据

select * from student;

解决表中插入数据中文乱码的问题
1、 设置数据库的表字符编码为 jbk；
2、 停止 MySQL 服务(net stop mysql)；
3、 找到 MySQL 的安装路径，找到 my.ini 文件，把 default-character-set=utf8 修改为 default-character-set=gbk；
4、 启动 MySQL 服务(net start mysql)，重新登录数据库。

删除记录

delete from 表名 [where 条件]

delete from student where sid=5;

把表中数据全部删除

delete from student;

问题：
delete 删除数据和 truncate 删除数据有什么差别？
delete:DML 一条一条删除表中的数据
truncate:DDL 先删除表再重建表
关于哪条执行效率高，具体要看表中的数据
如果数据比较少，delete 比较高效
如果数据比较多，truncate 比较高效

更新表记录

update 表名 set 列名=列的值，列名2=列名2的值 [where 条件]
如果参数是字符串、日期要加上单引号

update student set sname= '张三' where sid=5;

update student set sname= '李四' ,sex='1';

查询记录

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

说明：
distinct：去除重复的数据
select：选择显示哪些列的内容

简单查询

查询所有

select * from 表名

查询姓名和性别

select sname,sex from student;

别名查询

as 的关键字，as 关键字可以省略

表别名
select s.sname,s.sex from student as s;(主要用在多表查询)

列别名
select sname as 姓名,age as 年龄 from student;

去掉重复的值

select age from student;

select distinct age from student;

说明：
select： 运算查询：仅在查询结果上做了运算

select * ,age*2 from student;

select * ,age*2 as 两倍年龄 from student;

条件查询[where关键字]

查询年龄大于 20 的学生

select * from student where age > 20;

where 后的条件写法

说明：
关系运算符：> >= < <= != <>
<> 不等于 标准SQL语法
!= 不等于 非标准SQL语法

查询学生年龄不等于 20 的所有学生

select * from student where age <> 20;

select * from student where age != 20;

查询年龄在 20 到 30 之间

select * from student where age > 20 and age < 30;

使用 between… and… 查询

select * from student where age between 20 and 30;

逻辑运算：and , or , not

查询年龄小于 15 或者年龄大于 20

select * from student where age < 15 or age > 20;

模糊查询

like：

_ ：代表的是一个字符
% ：代表的是多个字符

1. 查询姓名中带有 张 的所有学生 ‘%张%’

select * from student where sname like '%张%';

2. 查询第二名字是三的所有学生 ‘_张%’

select * from student where sname like '_张%';

in：在某个范围获得值

查询年龄在 20，21，22 里面的所有学生

select * from student where age in (20,21,22);

• 排序查询

order by 关键字
asc :ascend 升序(默认的排序方式)
desc:descend 降序

查询所有学生的年龄，按照年龄进行排序

select * from student order by age;

查询所有学生的年龄，按照年龄进行降序排序

select * from student order by age desc;

查询学生姓名有“张”的学生，按年龄排序

查询学生姓名有“张”的所有学生

select * from student where sname like '%张%';

进行排序得出结果

select * from student where sname like '%张%' order by age asc;

复杂查询

聚合函数

sum(): 求和
avg(): 求平均值
count(): 统计数据
max(): 最大值
min(): 最小值

获得所有商品价格的总和

select sum(价格名称) from 表名;

select sum(price) from product;

获得所商品的平均价格

select avg(price) from product;

获得所有商品的个数

select count(*) from product;

注意：where 条件后面不能接聚合函数

查出商品价格大于平均的所有商品

1. 查出所有商品

select from product;

2. 大于平均价格

select avg(price) from product;

如平均价格为 100

select * from product where price > 100;

或者(子查询)

select * from product where price > (select avg(price) from product);

分组

group by

根据 cno 字段分组，分组后统计商品的个数

select cno,count(*) from product group by cno;

根据 cno 分组，分组统计每组商品的平均价格，并且商品平均价格 > 60

select cno,avg(price)
from product group by cno
having avg(price) > 60;

说明：
having 关键字 可以接聚合函数 出现在分组之后
where 关键字 它是不可以接聚合函数 出现在在分组之前

编写顺序

S…F…W…G…H…O

select..from..where..group by..having..order by

执行顺序

F…W…G…H…S…O

from..where..group by..having..select..order by

SQL 多表操作

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

create table category(
cid int primary key auto_increment,
cname varchar(10),
 cdesc varchar(31)
);

insert into category values(null,'手机数码','电子产品,');
insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');
insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');
insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');
insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');

select * from category;

select cname,cdesc from category;

所有商品
1.商品ID
2.商品名称
3.商品的价格
4.生产日期
5.商品分类ID

商品和商品分类：所属关系

create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int
);

insert into product values(null,'小米mix4',998,null,1);
insert into product values(null,'锤子',2888,null,1);
insert into product values(null,'阿迪王',99,null,2);
insert into product values(null,'老村长',88,null,3);
insert into product values(null,'劲酒',35,null,3);
insert into product values(null,'小熊饼干',1,null,4);
insert into product values(null,'卫龙辣条',1,null,5);
insert into product values(null,'旺旺大饼',1,null,5);

多表之间的关系如何维护

外键约束：foreign key

给 product 中的这个 cno 添加一个外键约束

alter table product add foreign key(cno) references category(cid);

删除的时候，先删除外键关联的所有数据，再才能删除分类的数据

建数据库原则

通常情况下，一个项目/应用建一个数据库

建表原则

一对一：常用于拆表操作。
建表原则：
1、将两张表合并成一张表。
2、将两张表的主键建立起关系。
3、将一对一的关系当作一对多的关系去处理。

一对多：
建表原则：在多的一方增加一个外键，指向一的一方。

多对多：
建表原则：将多对多转成一对多的关系，创建一张中间表。

主键约束：
1、默认就是不能为空，唯一 。
2、外键都是指向另外一张表的主键。
3、主键一张表只能有一个。

唯一约束

列面的内容，必须是唯一，不能出现重复情况，为空；唯一约束不可以作为其它表的外键 ，可以有多个唯一约束。

一对一建表原则：合并一张表，将主键建立关系，将它当作一对多的情况来处理。

一对多建表原则：在多的一方添加一个外键,指向一的一方。
　
多对多建表原则：拆成一对多，创建一张中间表，至少要有两个外键，指向原来的表。

网上商城表实例（复杂查询）

用户购物流程(用户表、订单表、商品表、商品分类表)

用户表 (用户的ID,用户名,密码,手机)

create table user(
      	uid int primary key auto_increment,
      	username varchar(31),
      	password varchar(31),
      	phone  varchar(11)
  	);

插入数据

insert into user values(1,'zhangsan','123','13811118888');

订单表 (订单编号,总价,订单时间 ,地址,外键用户的ID)

create table orders(
    oid int primary key auto_increment,
    sum int not null,
    otime timestamp,
    address varchar(100),
    uno int,
    foreign key(uno) references user(uid)
    );

批量插入数据

insert into orders values(1,200,null,'广州',1);
insert into orders values(2,250,null,'深圳',1);

商品表 (商品ID, 商品名称,商品价格,外键cno)

create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    cno int,
    foreign key(cno) references category(cid)
   );

批量插入数据

insert into product values(null,'小米mix4',998,1);
insert into product values(null,'锤子',2888,1);
insert into product values(null,'阿迪王',99,2);
insert into product values(null,'老村长',88,3);
insert into product values(null,'劲酒',35,3);
insert into product values(null,'小熊饼干',1,4);
insert into product values(null,'卫龙辣条',1,5);
insert into product values(null,'旺旺大饼',1,5);

订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)

  create table orderitem(
      ono int,
      pno int,
      foreign key(ono) references orders(oid),
      foreign key(pno) references product(pid),
      ocount int,
      subsum double
  );

给1号订单添加商品 200 块钱的商品

insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,101,100);

给2号订单添加商品 250 块钱的商品

insert into orderitem values(2,5,1,35);
insert into orderitem values(2,3,3,99);

商品分类表(分类ID,分类名称,分类描述)

create table category(
    cid int primary key auto_increment,
    cname varchar(15),
    cdesc varchar(100)
    );

批量插入数据

insert into category values(null,'手机数码','电子产品,黑马生产');
insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');
insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');
insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');
insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');

交叉连接查询(笛卡尔积)

SELECT * FROM product;

SELECT * FROM category;

笛卡尔积，查出来是两张表的乘积，查出来的结果没有意义。

SELECT * FROM product,category;

过滤出有意义的数据

SELECT * FROM product,category WHERE cno=cid;

SELECT * FROM product AS p,category AS c WHERE p.cno=c.cid;

SELECT * FROM product p,category c WHERE p.cno=c.cid;

数据准备

INSERT INTO product VALUES(NULL,'耐克帝',10,NULL);

内连接查询

隐式内链接

SELECT * FROM product p,category c WHERE p.cno=c.cid;

显示内链接

SELECT * FROM product p INNER JOIN category c ON p.cno=c.cid;	

• 区别:

1. 隐式内链接：在查询出结果的基础上去做的 WHERE 条件过滤。
2. 显示内链接：带着条件去查询结果, 执行效率要高。

左外连接
左外连接，会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用 NULL 代替。

SELECT * FROM product p LEFT OUTER JOIN category c ON p.cno=c.cid;

准备工作

INSERT INTO category VALUES(100,'电脑办公','电脑叉叉差');

右外连接：会将右表所有数据都查询出来，如果左表没有对应数据的话，用 NULL 代替。

SELECT * FROM product p RIGHT OUTER JOIN category c ON p.cno=c.cid;

分页查询

每页数据数据3
起始索引从0
第1页: 0
第2页: 3
起始索引: index 代表显示第几页 页数从1开始
每页显示3条数据

startIndex  = (index-1)*3

第一个参数是索引，第二个参数是显示的个数。

select * from product limit 0,3;
select * from product limit 3,3;

子查询

1、查询出(商品名称,商品分类名称)信息
2、查询分类名称为手机数码的所有商品

查询分类名为手机数码的 ID

select * from category where cname ='手机数码';

得出ID为1的结果

SELECT * FROM product WHERE cno = (SELECT cid FROM category WHERE cname='手机数码');

查询出(商品名称,商品分类名称)信息

左连接

SELECT p.pname,c.cname FROM product p LEFT OUTER JOIN category c ON p.cno = c.cid;

子查询

SELECT pname ,(SELECT cname FROM category c WHERE  p.cno=c.cid ) AS 商品分类名称  FROM product p;

总结