Java第二十五天（mysql）

Mysquel数据库与JDBC

1.完成对分类表的CRUD的操作

数据库的概述

数据库是一个文件系统，只不过我们需要通过命令（SQL）来操作这个文件系统

数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。

作用：储存数据，数据的仓库，带有访问权限限制不同的人可以有不同的操作

数据库操作的都是后台数据，取到后台数据进行封装，然后交给前端去展现

常见的数据库：

mysql：开源免费的适用于中小型企业的免费数据库，sun公司收购了mysql，sun公司被oracle公司收购开始收费

mariadb：由mysql创始人搞出来的，直接是mysql开源版本的一个分支，基本上所有的命令都是一样

oracle：甲骨文公司，商业软件收费，适用于大型电商网站，收购了sun公司

db2：IBM公司，thinkpad，解决方法：软件硬件，服务器架构包含全套软件，稳定，银行系统大多采用db2

sqlserver：windows里面，政府网站asp.net，大学教学通常采用sqlserver，图形化工具做的不错

sybase：被淘汰的数据库

NOSQL非关系型数据库：存放形式 key:value

mongodb

redis  ：set get 设置和获得数据

关系型数据库：主要是用来描述实体与实体之间的关系

E-R关系图：

实体：方框

属性：椭圆

关系：菱形

Mysql数据库服务器

MYSQL数据库：数据库管理软件

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

MYSQL的安装和卸载

安装：

卸载：

1.打开程序控制面板删除软件MySQL Serve

2.删除mysql安装目录下的所有文件C:\programs files\MySQL

3.删除MySQL数据存放文件C:\programsData\MySQL

MySQL的SQL语句

SQL：Structure Query Language结构化查询语言

DDL：数据定义语言：定义数据库，数据表他们的结构：create创建  drop删除 alter 修改

DML：数据操纵语言：主要是用来操作数据 insert插入   update 修改  delete删除

DCL：数据控制语言：定义访问权限定义取消访问权限，安全设置   grant

DQL：数据查询语言：select查询   from字句   where字句

数据库的CRUD操作

首先要登陆数据库服务器：mysql -uroot -proot

(有的会出现问题，我就没有那么顺利出问题了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)     以管理员权限运行cmd程序输入 services.msc  ；   找到MySQL  重启动其服务   或者关闭修改密码在网上搜方法，太长了懒得co过来)

创建数据库

create database 数据库的名字

create database hlj0628;

创建数据库的时候指定字符集：

create database 数据库的名字 character set 字符集;

create database hlj0628_1 character set utf8;

创建数据库的时候指定字符集和校对规则：

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

create database hlj0628_2 character set utf8 collate utf8_bin;

查看数据库

查看所有数据库    show databases;

information_schema

performance_schema

mysql

查看数据库定义的语句

show create database 数据库的名字

show create database hlj0628_1;

show create database hlj0628_2;

修改数据库（一般不需要修改也不需要我们创建）

修改数据库的字符集

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

alter database hlj0628_1 character set gbk;      把hlj0628_1字符集改成gbk

删除数据库

drop database 数据库的名字;

drop database hlj0628_2;

drop database test;

其他数据库的操作命令

切换数据库

use 数据库的名字;

use hlj0628;

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

select database();

表的CRUD操作

创建表

create database 数据库的，名字

create table 表名（

列名 列的类型 约束，

列名 列的类型 约束，

...

）;

列的类型：

Java：int  char/string   duble  float  boolean   date

sql:     int  char/varchar   duble  float  boolean   date  time  datetime  timestamp  text  blob

char是固定的长度类型，varchar是可变长度类型，例如char(3)当存入的字符数不够3个的时候会以空格填充显示，varchar(3)存入的字符是多少个就是多少个，比较节省空间一点

date:   YYYY-MM-DD

time:    hh:mm:ss

datetime:    YYYY-MM-DD hh:mm:ss 默认值是null

timestamp:    YYYY-MM-DD hh:mm:ss 默认值使用当前时间

text：    主要用来存放当文本

blob：    存放二进制

列的约束：

主键约束：primary key

唯一约束：unique

非空约束：not null

创建表：

1.分析实体：学生

2.学生ID

3.姓名

4.性别

5.年龄

create table student(

sid int primary key,

sname varchar(31),

sex int,

age int

);

查看表

show tables;

查看表的定义

show create table 表名;

show create table student;

查看表的结构

desc 表名;

desc student;

修改表

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

添加列（add）

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

alter table student add chengji int not null;

修改列（modify）

alter table 表名 modify 列名 修改内容;

alter table student modify sex varchar(2);

修改列名（change）

alter table 表名 change 原列名 修改后列名 列类的类型;

alter table student change sex gender varchar(2);

删除列（drop）

alter table 表名 drop 列名;

alter table student drop chengji;

删除表（rename）

rename table 原表名 to 新表名;

rename table student to hero;

修改表的字符集

alter table 表名 character set 字符集;

alter table hero character set gbk;

删除表

drop table hero;

SQL完成对表中数据的CRUD的操作（重要）

插入数据：

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

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

查看表中数据：select * from student;

简单写法插入全列可以不写列名直接写值：insert into student values(2,'zhangsan',1,23);

只插入部分列列名不能省略：insert into student(sid,sname) values(3,'lisi');

批量插入：

insert into student values(4,'zhangsan',1,23),(5,'zhangsan',1,23),(6,'zhangsan',1,23);

单条插入和批量插入的效率：批量插入效率更高一些，

命令行下插入中文的问题：

临时解决方案：set names gbk; x相当于是告诉mysql服务器软件，我们在当前命令行下输入的内容是GBK编码。当命令窗口关闭之后，它再输入中文就存在问题

永久解决办法：修改my.ini配置（在mysql软件安装路径里） ：暂停mysql，在安装路径中找到my.ini配置文件修改如下，把57行代码改成gbk，保存文件推出，启动mysql

修改列的字符集alter table student change sname sname varchar(31) character set gbk;

查看表格的所有元素show full fields from student;

alter database character set utf8;

删除记录

delete from 表名 [where 条件]

delete from student where sid=10;

delete from student;如果没有指定条件，会将表中数据全部删除

面试题：请说一下delete删除数据和truncate删除数据有什么差别

delete:分类属于DML，一条一条删除表中的数据

truncate：分类属于DDL 先删除表再重建表

关于哪条效率高具体要看表中的数据量，较少--delete效率高；较多--truncate效率高

更新表记录

update 表名 set 列名=列的值，列名2=列的值2 [where 条件];

将sid为5 的名字改成lisa,如果参数是字符串或者日期要加上单引号，是数字直接写

update student set sname='lisa' where sid=5;

如果后面没有加条件，所有行都会更新update student set sname='lisa',sex=0;

查询记录

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

distinct   去除重复的数据

select选择显示哪些列的内容

商品分类：

1.分类ID

2.分类名称

3.分类描述

(auto_increment)表示id自动递增

create table category(

cid int primary key auto_increment,

cname varchar(10),

cdesc varchar(31)

);

insert into category values(null,'phone','huawei,xiaomi');

insert into category values(null,'fruit','xinjiang,hainan');

insert into category values(null,'book','about love,ahout youth');

insert into category values(null,'milk','mengniu,yili');

只查询名称和描述  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,'xaiomi',998,null,1);

insert into product values(null,'watermelon',2.88,null,2);

insert into product values(null,'banana',6,null,2);

insert into product values(null,'Titanic','28,null,3);

insert into product values(null,'guangming',16,null,4);

insert into product values(null,'youge',13,null,4);

简单的查询：

查询所有商品： select * from product;

查询商品名称和商品价格：select pname,price from product;

别名查询.as关键字，as关键字是可以省略

    表别名：select p.name,p.price from product p;(主要用在多表查询)

select p.pname,p.price from product as p;

    列别名：select pname as 商品名称,price as 商品价格 from product;

select pname as name,price as money from product;

省略as

select pname name,price money from product;

    去掉重复的值查询

        查询商品所有的价格

            select price from product;

            select distinct price from product;

        select运算查询:仅仅在查询结果上做了运算+-*/，在数据库中不做改变

            select *,price*1.5 from product;

            select *,price*1.5 as discountprice from product;

    条件查询[where关键字]     指定条件，确定要操作的记录

            查询商品价格>60元的所有商品信息

             select * from product where price > 60;

    where后面的条件写法

        关系运算符：>   >=     <    <=   =   !=   <>

<>：不等于：标准的SQL语法

！=：不等于：非标准的SQL语法

查询商品价格不等于88的所有商品

select * from product where price <> 88;

select * from product where price != 88;

    查询商品价格在10-100之间的价格

select * from product where price > 10 and price <100;

between ... and...

select * from product where price between 10 and 100;

    逻辑运算：and , or , not

查询商品价格小于100胡总和商品价格大于900

select * from product where price < 100 or price >900;

    like ：模糊查询

_  ：代表的是一个字符

% ：代表的手机多个字符

查询出名字中带有water的所有商品'%water%'

select * from product where pname like '%water%';

select * from product where pname like '_a%';

    in 在某个范围内获得值

select * from product where cno in (1,4,5);

    排序查询：order by 关键字

asc ：ascend 升序（默认的排序方式）

desc  ：descend 降序

查询所有商品按照价格进行排序

select * from product order by price;

查询所有商品按照价格进行降序排序

select * from product order by price desc;

查询名称中含有an的商品按照价格进行升序

select * from product where pname like '%an%' order by price asc;

    聚合函数

sum()  ：求和

avg()  ：求平均值

count()  ：统计数量

nax()  ：最大值

min()  ：最小值

获得所有商品价格的总和

select sum(price) from product;

获得所有商品价格的平均价格

select avg(price) from product;

获得所有商品的个数

select count(*) from product;

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

 查询商品价格大于平局价格的所有商品

    子查询select * from product where price > (select avg(price) from product);

    分组：group by

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

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

根据cno字段分组，分组统计每组商品的平均价格，并且商品平均价格大于10

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

注意where条件后面不能接聚合函数  出现在分组之前

having 关键字可以接聚合函数，出现在分组之后

SQL代码编写顺序

    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  . . 

总结

数据库的创建：create database 数据库的名字 character set 字符集 collect 校对规则

数据库的删除：drop database 数据库名

修改：alter database 数据库 character set 字符集(utf8)

查询：show databases;

  show create database 数据库的名字

  Select database();

切换数据库：

use 数据库的名字

表结构的操作：

创建：create table 表名(

    列名 列的类型 列的约束，

    列名 列的类型 列的约束

    )

    列的类型：char/varchar

    列的约束:

        Primary key 主键约束

        unique 唯一约束

        not null 非空约束

    自动增长：auto_increment

删除：drop table 表名

修改： alter table 表名(add.modify,change,drop)

            rename table 旧表名 to 新表名

            alter table 表名 character set 字符集

查询表的结构：

    show tables; 查询出所有的表

    show create table 表名;    查询表的创建语句，表的定义

    desc 表名;  表的结构

表中数据的操作：

    插入：

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

    删除：

        delete from 表名[where 条件];

    修改：

        update 表名 set 列名=’值’,列名=’值’ [where 条件];

    查询：

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

        as 关键字：别名查询

        where 条件后面：

            关系运算符：>  >=  <  <=  !=  <>

            判断某一列是否为空：is null  is not null

            in 在某范围内

            between...and

            逻辑运算符：and or not

        模糊查询：like

            _：代表单个字符

            %：代表多个字符

        分组：group by

        分组之后条件过滤：having

        聚合函数：sum()  avg()   count()   max()   min()

        排序：order by（asc升序，desc降序）

 

转载于:https://my.oschina.net/u/4110331/blog/3067452