第四 mysql数据库和SQL语句

mysql数据库和SQL语句

任务

1、数据库介绍&安装

2、创建库和表

3、简单增删改查语句

4、sql函数

5、数据备份和恢复

笔记

1、数据库介绍&安装

1.1、什么数据库

数据库：它是一个软件，可以保存数据。在一个项目中，项目中的所有数据最后都需要保存在专业的数据库软件中。

 

早期的数据库都是关系型数据库:

数据和数据之间必然会存在相应的关系，而这些数据保存在数据库中之后，需要维护这些数据之间的关系。

 

云计算大数据中使用的非关系型数据库：它其中依然可以保证数据之间的关系，但是他们的关系不是使用外键等约束来完成。他们中根据一些下标等操作维护关系。

 

1.2、数据库产品介绍

常见的数据库软件：

Oracle数据库：Oracle公司提供的，商业的大型的数据库。收费的。

DB2数据库：IBM公司。商业的大型的数据库。收费的。

SQL Server 数据库：微软的，中型数据库，主要和微软的开发语言结合使用。收费的。

MySql数据库：早期瑞典公司开发，后期被sun公司收购，sun被Oracle收购。从6.0版本出现了收费的。

 

SqlLite：微型的数据。

 

1.3、数据库的安装和卸载

卸载：

1、使用控制面板找到mysql的安装文件卸载。

2、找到mysql的安装目录，找到my.ini文件（mysql核心配置文件）

找到数据文件所在的目录，然后把所有的数据文件全部删除。

 

安装：

参考安装文档。

 

 

安装完成之后，一定要测试：

1、查看windows的服务，mysql的服务是否启动 运行中输入 services.msc

2、使用命令行登录mysql数据库：

 

1.4、超级管理员密码重置

1) 停止mysql服务 运行输入services.msc 停止mysql服务

2) 在cmd下 输入 mysqld --skip-grant-tables 启动服务器光标不动 （不要关闭该窗口），启动mysqld 服务

 

3) 新打开cmd 输入mysql -uroot -p 不需要密码

use mysql;

update user set password=password('abc') WHERE User='root';

 

4) 关闭两个cmd窗口 在任务管理器结束mysqld 进程

5) 在服务管理页面 重启mysql 服务。

重新登录的时候需要使用 新的密码。

 

2、数据库的操作

学习数据库的学习路线：

1、数据库软件中的库操作：

2、数据库中的数据表操作：

3、数据表中的数据操作：

2.1、mysql数据库的内部存储结构

当一台电脑安装了MySql数据库之后，这个电脑就可以认为是一个数据库服务器。

在一个数据库服务器电脑中可以安装很多的数据库软件。

 

安装了数据库软件的这个电脑，在这个软件中可以创建出很多个数据仓库，用来管理不同的数据。

2.2、SQL语句介绍

所有的数据库产品它们有共同的语言来操作：

SQL语言：

2.3、SQL分类

SQL语言的标准，把SQL分成4类：

DDL：数据库定义语言，主要完成数据库，数据表的创建删除等操作

DML：数据库操作语言：主要是操作数据表中的数据（insert update delete）

DCL：数据库控制操作语言：数据库中的用户权限操作。

DQL：数据查询语言：主要是查询数据表中的数据（select）

 

只要听到数据库的操作：

一般都是CRUD（create read update delete）。

2.4、查询所有数据库

数据库中的数据仓库（数据库）主要学习增删 改 查。

 

在操作数据库之前，必须先链接到数据库上。

 

C:\Users\QuBo>mysql-u root -p

Enter password:***

 

所有数据库语句写完之后，最后需要一个分号结束。

 

查询数据库的语法：

语法：showdatabases;   查询所有数据库

 

查看当前数据库的编码表

语法：show create database 数据库名称;

   

在数据库中，utf-8编码表的名称必须写成utf8.

2.5、创建数据库

语法：create database 数据库名称[character set 编码表名] ;

 

练习：

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

 

如果创建数据库的时候没有指定编码表，默认和安装数据库时设置的系统编码表一致。

 

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

 

2.6、删除数据库

语法：drop database 数据库名;

练习：

     删除前面创建的mydb1数据库

删除之前一定要备份数据，或者请示领导

2.7、修改数据库编码集

语法： alter database 库名 character set 编码表名

 

练习

      修改mydb2字符集为gbk；

 

2.8、切换数据库和查看正在使用的数据

我们在操作某个数据库的时候，首先需要先切换到当前这个数据库中。

 

切换数据库：

语法：use 数据库名;

 

 

切换到某个数据库中之后，可以查看当前数据库下的所有数据表：

 

 

查询当前正在使用的数据库：

语法：selectdatabase();

 

 

我们在mysql数据库软件中每创建一个数据库，在数据库的数据文件夹下会多一个文件夹。

3、数据表的操作

 

主要学习数据库中的数据表的创建修改 查询 删除操作

 

3.1、数据表的创建

语法：create table 表名(

列名列的类型 [列的约束],

列名列的类型,

......

);

注意：创建数据表的时候，列名可以是开发者自己定义的名称， 一般和需要保存的Java对象的属性名一致。列的类型，一般要求和Java中对象的属性类型一致。

 

在Java中有自己的数据类型，mysql数据库也有自己的数据类型：

Java中的数据类型                                  mysql数据库中的数据类型

byte

short

int                                                          int

long

 

char String                                             char(数据长度)  varchar(数据长度)

boolean                                                   bit  0  1

double                                                     double  

float                                                               DECIMAL(数据总宽度,小数位数)和

NUMERIC(数据总宽度,小数位数)

 

date                                                       date  time datetime

 

数据库中字符或字符串使用char  varchar

 

char是定长

varchar是可变长度

char(5)    “aa”  aa在数据库中会占用5列

varchar(5)  “aa” aa在数据库中仅占用2列

 

FileReader 读取的字符数据  text  字符数据

FileInputStream 读取的字节数据 blob 字节数据

如果要给数据库中存储文件等数据，一般不会把文件保存在数据中，只会把文件在硬盘上的路径保存在数据库中。

 

创建一个数据表，描述公司的员工：

create table employee(

id int,

name varchar(100),

sex varchar(10),

age int,

salary double,

job varchar(50)

);

3.2、查看数据表结构

语法：查看某个库下的所有数据表        

show tables;

查看某个数据表的定义格式(编码表)：

语法：show create table 表名

 

查看数据表的结构：

语法：desc  表名;

3.3、数据表结构修改

修改数据表：

可以对表名、表中的列名、列的类型、列的约束进行增删改。

 

语法：altertable 表名增/删/改列名类型(长度) 约束；

 

1、增加列：

alter table 表名 add 列名 类型(长度) 约束;

2、修改现有列类型、长度和约束：

alter table 表名 modify 列名 类型(长度) 约束;

3、修改现有列名称：

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

4、删除现有列：

alter table 表名 drop 列名 ;

5、修改表名：

rename table 旧表名 to 新表名;

6、修改表的字符集：

alter table 表名 character set utf8;

 

练习：

1、在employee表上增加address列

alter table employee add address varchar(120);

2、修改birthday列不能为null

 

 

 

3、修改列名name为username

4、修改username列的长度为60

 

5、删除sex列

 

6、将employee表名修改为person表

3.4、数据表删除

语法：drop table 表名;

 

练习：删除employee表

3.5、单表约束

数据表的约束：主要是用来限定当前数据表中每列数据的。

约束的目的：是保证数据表中的数据完整性和有效性。

 

数据表的约束有三种：

主键约束、唯一约束、非空约束。

 

1、主键约束：primary key

主键是保证当前某一列在数据表中是永远不重复。

一般一个数据表中只有一个主键。

 

语法：

create table 表名(

列名 列的类型  约束primary key,

 

);

2、唯一约束，unique

    唯一约束也是保证当前这一列的数据不能重复。

主键约束在数据表中一般只会定义某一列，唯一约束可以让多列都是唯一的。

3、非空约束，not null

    某一列不能null。

4、数据表中数据的操作☆☆☆☆☆

数据表中的数据操作：增删 改 查

4.1、向数据表插入数据

语法：insert into 表名(列名，列名...)values(值，值，.....);

 

练习：

1、给person表中插入数据（所有列都有数据）

  如果需要给数据表中的每一列都插入数据，这时可以省略列名，直接插入数据：

如果需要给某些列插入数据，这时需要制定列名，数据表中可以为null的列，在插入的时候，可以不插入数据。

 

插入中文的问题：

在控制台中插入中文的时候报错，原因是当前我们控制台是gbk编码，而数据库，数据表，都是utf-8编码， 这时需要又该数据表中的客户端的编码表。

修改mysql数据库中对客户端的编码表支持：

找到mysql的配置文件my.ini

修改完mysql的配置文件，需要重复mysql服务。

4.2、数据表中的数据修改操作

语法: update 表名 set 列名=值，列名=值 [where条件];

 

1、修改person表中的所有用户的age 为 30岁

注意：修改数据表中的某列数据的时候，一定要添加条件，如果没有条件会把表中当前这列的所有数据全部修改。

 

2、修改姓名为zhangsan的这个用户的薪水 8888

 

3、把id为2 的用户 的 username 修改为 李四 和address修改为 深圳

 

 

4、把id为3 的这个用户 用户名修改为 中文的 王五;

 

4.3、数据记录的删除操作

语法：delete from 表名[where  条件];

 

删除数据表中的数据时，一定要注意加条件，如果没有条件，会把整个表中的数据全部删除。

 

1、删除person表中username 为zhangsan 的用户信息

 

2、删除表中所有

 

 

如果需要删除表中的所有数据，可以使用truncate删除表中的数据。

truncate和delete的区别：

delete是从表中一行一行删除。

truncate是先把表删除掉，再重新创建一个新表。

 

5、数据表记录的查询☆☆☆☆☆

5.1、语法一 ：

查询数据库中的某张表的所有数据

语法：select * from 表名[where 条件] ;

5.2、语法二 ：

查询某张表中指定的列

语法：select 列名 , 列名 .....from 表名  [where 条件];

 

1、查询所有学生的姓名 和薪水

 

5.3、语法三 ：

查询表中年龄大于等于23岁的学生信息

语法：select * from person where age >=23;

5.4、条件运算符：

1) 相等=        不等 <>

 

2) 区间：between  ...and... 在两者之间取值 between 70 and 80

    等价于 >=70  and  <=80   注意前面那个数要比后面那个数要小

sage >=24 and sage<=50 between 24 and 50

 

3) in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90

 

where 列名 in (值，值，值.......);

where 列名=值 or 列名=值 or 列名=值 .......

 

4) like '模糊查询pattern' 进行模糊查询 ，表达式有两个占位符 % 任意字符串 _ 任意单个字符

 

例如： name like '张%' 所有姓张学员   %张%

   name like '张_' 所有姓张名字为两个字学员 _张_

 

5) is null 判断该列值为空

sql中对null的判断，不能写 = null   在sql 中 null = null 结果不成立，需要书写成is null

不是null  ： is not null

 

6) and 逻辑与 or 逻辑或 not 逻辑非

and 需要满足多部分条件；

or或者（多个条件满足其一即可）；

not 非，反条件。

 

练习：

 

1、查询年龄在20~40(包含)之间的学生信息

 

 

 

2、查询年龄为18，23，25的同学信息

 

 

 

3、查询所有姓赵的学生信息

 

 

 

4、查询年龄>23，薪水>8500的学生信息

 

 

 

插入如下数据：

      insert into person  values(12,'秋香',24,null,null,null);

5、查询没有薪水学员信息

 

 

6、查询有薪水学员信息

 

 

5.5、语法四 ：

对查询的结果进行排序：使用order by 子句排序查询结果。

语法：select列名 , 列名 ...  from 表名  order by 列名 asc （升序）|desc（降序）

练习：

1、对薪水排序后显示

2、对年龄排序按从高到低（降序）的顺序显示

3、对学生年龄按照降序排序，年龄相同按照薪水降序

5.6、别名：

可以对查询出来的列名 起别名。

语法：select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;

 

在使用别名的时候，as 关键字可以省略。

 

select username salary from person; 查询的是什么？

查询的person表中的username这一列的数据，给username起别名为salary。

6、SQL中的函数

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

count 统计个数

sum函数：求和

avg函数：求平均值

max、min 求最大值和最小值

6.1、count函数：

语法：select count(*)|count(列名) from表名

 

练习：  count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。

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

 

2、统计薪水大于8500的学生有多少个？

1、先查询大于8500的学生信息，

2、在统计查询出这些信息的数据个数

6.2、sum函数：

语法：select sum(列名) from表名;

 

练习：

1、统计一个班级年龄和值

 

2、分别统计年龄和薪水的和

 

3、统计年龄和薪水和值

 

查询的时候需要两列加起来：

 

 

补充内容：

如果使用sum 多列进行求和的时候，如果某一列中的有null，这一列所在的行中的其他数据不会被加到总和。可以使用mysql 数据库提供的函数 ifnull(列名,值)

 

在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)

numeric    double float

6.3、avg函数：

语法：select avg(列名) from 表名;

 

 

练习：

求一个班级平均年龄

6.4、max，min函数：

 

max：获取某一列的最大值

min：获取某一列的最小值

 

练习：

求班级最高薪水和最低薪水

6.5、group by分组函数

create tableorders(

       id int,

       product varchar(20),

       price float

);

 

insert intoorders(id,product,price) values(1,'电视',900);

insert intoorders(id,product,price) values(2,'洗衣机',100);

insert intoorders(id,product,price) values(3,'洗衣粉',90);

insert intoorders(id,product,price) values(4,'桔子',9);

insert intoorders(id,product,price) values(5,'洗衣粉',90);

 

 

 

selectproduct,sum(price) from orders group by product;

selectproduct,sum(price) from orders group by product having sum(price)>100;

 

 

 

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

group by 它可以根据指定列对数据进行归类。如果这一列中有重复的数据会被合并成一个。

 

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

 

2、练习：查询购买的商品价格总和超过150的商品

在SQL语言中where关键字后面不能添加上面讲过的所有sql函数（分组函数）。sql语言中给我们专门提供了一个在分组中还可以进行条件过滤的关键字having.

having用法和where相同。having后面可以加过滤的函数，where后面不用用分组函数。

 

7、MySQL 数据库的备份和恢复（扩展）

7.1、备份命令

在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。

语法：mysqldump -u 用户名 -p 数据库名> 磁盘SQL文件路径

由于mysqldump命令不是sql命令，需要在dos窗口下使用。

 

演示：

1、重新开启一个新的dos窗口。

2、导出数据。

 

 

在备份数据的时候，数据库不会被删除。可以手动删除数据库。同时在恢复数据的时候，不会自动的给我们创建数据库，仅仅只会恢复数据库中的表和表中的数据。

7.2、恢复命令

 

恢复数据，需要手动的先创建数据库：

create database mybd3;