MySQL学习总结1

MySQL学习总结1

数据库概述:

• 什么数据库: 存放数据的仓库

  • 数据库就是一个文件系统,只不过我们需要通过命令(SQL)来操作这个文件系统

  • 数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

• 数据库的作用 :存放数据的仓库

• 为什么要学习数据库 : JAVA后台开发不可缺少的一部分

常见的数据库:

• 关系型数据库:
  
  • mysql: 小型.开源免费,大多数中小型互联网公司都在使用这个,sun公司收购了
  • mariadb: 开源免费数据, 和mysql基本上一模一样,但是它是免费的,并且在不停的更新,是 mysql开源版本的一个分支
  • Oracle: 中大型的数据库,服务收费的,安全稳定. 证券,银行,保险公司, Oracle收购了sun公司, mysql6.x之后就开始收费了
  • SQL Server : 大学里面教学使用比较多, 政府官网, 图形化界面做的很好
  • DB2 : IBM公司, 主要运用在银行系统, 服务器软硬件解决方案
  • sybase : powerdesinger 数据建模工具
  • SQLite : 运用在嵌入式系统中的小型数据库,android
  • 一般和Java有关的Oral&mySQL
• 非关系数据库: key:value
  
  • mogodb :
  • redis :

关系型数据库: E-R关系图

• 主要是用来存储实体与实体之间的关系:（具体百度E-R图）
  
  • 例如：学生和班级. 学生和课程 老师和学生.

MYSQL数据库服务器

• 安装了Mysql服务软件的电脑

MYSQL的安装和卸载

• 卸载

  • 在控制面板中删除mysql
  • 要将安装路径下的: C:\Program Files\MySQL 文件夹全部删除
  • 要将C:\ProgramData\MySQL 文件夹删除

• 安装

  • 字符集必须是设置为UTF8

  • include bin dirctory in windows path 前面的勾必须勾上（这个是在cmd输入的选项）

  • 密码必须得自己记住

• 中文乱码解决方案：
  
  • 1. 先关闭掉MYSQL服务器（在我的电脑里的管理然后服务里面选择）
  • 1. 找到安装路径C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini
  • 1. 修改第57行的编码为gbk
  • 1. 重新启动mysql服务

MYSQL的SQL语句

• SQL: Structrued Query Langauage 结构化的查询语言

• 主要分类：

  • DDL: 数据定义语言 : 定义数据库或者表结构. alter(修改) create(创建) drop(删除) truncate(截断)
  • DML: 数据操纵语言: 用来操作表中数据: insert(插入) update(更新) delete(删除)
  • DQL: 数据查询语言: 用来查询数据 select(选择) from(从哪里查) where(哪一条,判断条件)
  • DCL: 数据空值语言: 用来控制用户权限 grant(授权) revoke(取消授权)

• 登录数据: mysql -uroot -proot

  • -u username 用户名
  • -p password密码

数据库的CRUD的操作

• 增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)

创建数据库

• sql命令：
• create database 数据库的名字 —用的非常多的
  
  • create database day08_1;
• create database 数据库的名字 character set 字符集;
  
  • create database day08_2 character set gbk;
• create database 数据库的名字 character set 字符集 collate 校对规则;
• 校对规则 : 比较规则，字符串比较的规则 , a，A，a=A 默认情况下不区分大小写
  a=1 ，A=2
  
  • create database day08_3 character set utf8 collate utf8_bin;

查看数据库

• sql命令：
  
  • 查看数据库定义的语句: 查询的是数据库创建的语句
    
    • show create database 数据库的名字
  • 查看所有数据库
    
    • show databases;

修改数据库的操作

• sql命令：
  
  • 修改数据的字符集
  • alter database 数据库的名字 character set 字符集
  • alter database day08_2 character set utf8;

删除数据库

• sql命令：
  
  • drop database 数据库名字;
  • drop database day08_3;

其他数据库操作命令

• sql命令：
• 切换数据库(选中数据库)
  
  • use 数据库名字
• 当前选中是哪个数据库,当前正在使用哪个数据库
  
  • select database();
• 会存在DBA 数据库管理员来管理数据库

表的CRUD操作

创建表

 > 创建表的SQL命令

 create table 表名(
 列名 列的类型 [列的约束],
 列名 列的类型 [列的约束]
 );

• 列的类型

java              mysql
int               int
String                char(长度)/varchar(长度)
                  char(10): 固定长度字符hello 没有填满用空格补齐10个
                  varchar(10):可变长度字符hello  只占5个字符
                              可以存放10个汉字 ,10个字符
boolean               boolean
Date              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: 大电影 java-存放和读取

• 例子：创建员工表: 员工编号,员工名称,工资,部门编号 employee

  create table emp1(
    empno int,
        ename varchar(20),
        sal   double,
        deptno int
  );
  

• 列的约束

  • 主键约束: primary key不能重复,不能为空 通常用在编号ID
  • 唯一约束: unique 唯一约束: 不能重复,但是可以为空
  • 非空约束: not null 不能为空
  • auto_increment 自动增长属性
• 例子：
  

  create table emp2( <br>
    empno int primary key auto_increment, <br>
        ename varchar(20) unique, <br>
        sal double not null, <br>
  deptno int <br>
  );</li>
  

查看表

• SQL命令：
  
  • 查看所有的表
    
    • show tables;
  • 查看表的创建过程
    
    • show create table 表名
  • 查看表结构
    
    • desc 表名

修改表

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

• SQL命令

  • 添加列(add) alter table 表名 add 列名 列名类型

    • 添加手机号：
    • alter table emp1 add phone int;

  • 修改列(modify) alter table 表名 modify 列名 列的类型

    • 修改手机号的列的类型为字符类型
    • alter table emp1 modify phone varchar(11);

  • 修改列名(change) alter table 表名 change 旧的列名 新列名 列的类型

    • 修改phone 改成 shoujihao
    • alter table emp1 change phone shoujihao varchar(11);

  • 删除列(drop) alter table 表名 drop 列名;

    • 删除shoujihao这列数据
    • alter table emp1 drop shoujihao;

  • 修改表名(rename) rename table 旧表名 to 新表名

    • rename table emp1 to emp11;

  • 修改表的字符集 alter table character set 字符集

    • alter table emp11 character set gbk;

删除表

• SQL命令
  
  • drop table 表名
  • drop table emp11;

Sql完成对表中数据的CRUD的操作

插入数据

• 例子：

  create table emp1(
  
    empno int primary key auto_increment,
        ename varchar(20),
        sal   double,
        deptno int
  );
  

• 指定列名插入

  • insert into emp1(empno,ename) values(9527,’huaan’);
  • insert into emp1(empno,ename) values(null,’zhangsan’);
• 不指定列名插入:必须按照顺序,把表中所有的列的数据都填上,如果不填必须填null
  
  • insert into emp1 values(null,’lisi’,18,10);
  • insert into emp1 values(null,’lisi’); –错误的写法,列的数量不匹配

删除记录

• delete from 表名 [ where 条件 ]

• delete from emp1; –逐条删除数据

  • 删除9527这个员工
  • delete from emp1 where empno=9527;

• 面试问题: 请说一下 delete 删除表中所有数据 和

• cate 删除表中所有数据有什么差别
  
  • delete : DML(操作数据的) 一条一条删除表中记录,并且数据可以找回
  • truncate : DDL(操作结构) 先删除表,再创建表，数据不可以找回
  • –如果数据量少,delete 执行效率高（关键字少）
  • –如果数据量大,truncate执行效率高

更新表记录

• update 表名 set 列名=列的值,列名2=列的值2 [where 条件]
  
  • update emp1 set ename=’张三’ where empno=9528;
  • update emp1 set sal = 1000; –没有指定条件,全部修改

查询记录

• 例子：准备一张商品分类表(分类ID,分类名称,分类描述)

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

• 例子：准备一张商品表(商品编号,商品名称,商品价格,商品描述,商品分类编号)

  create table product(
    pid int primary key auto_increment,
        pname varchar(10) not null,
        price double,
        pdesc varchar(20),
    cno int
  );
  

• 数据的准备

insert into category values(null,'手机数码','黑马生产的小手机');
insert into category values(null,'鞋靴箱包','传智生产的包包');
insert into category values(null,'香烟酒水','黄鹤楼,茅台');
insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');

insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(null,'小米',1599,'为发烧而生',1);
insert into product values(null,'李宁',99,'不走寻常路',2);
insert into product values(null,'耐克',399,'just do it',2);
insert into product values(null,'黄鹤楼',20,'饭后一根烟,胜做活神仙',3);
insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(null,'老干娘辣椒酱',19,'永远不变的味道',4);
insert into product values(null,'黄鹤楼',null,'饭后一根烟,胜做活神仙',3);

• 查询语句的基本结构:
  
  • select [distinct] [列名,列名] * from 表名 [where 条件]
  • 记住只是影响查询结果（显示的结果），数据库并没有被改变
• 简单查询:
  
  • 查询所有商品信息:
  • select * from product;
• 查询商品名称和商品价格:
  
  • select pname,price from product;

• 别名查询. as 的关键字 , as 关键字是可以省略 类似于 张三 ： 小三

  • 表别名: select p.pname , p.price from product p; (主要是用在多表查询);
    
    • select pro.* from product pro;
    • select pro.pname from product as pro;
  • 列别名: select pname as 商品名称,price as 商品价格 from product;
    
    • select pro.pname as 商品名称 from product as pro;
    • select * from product;

• 去掉重复的值 distinct

• 查询商品所有的价格
  
  • select cno from product;
  • select distinct cno from product;
  • select distinct pname,cno from product;

• select运算查询 : 仅仅在查询结果上做了运算 + - * /

  • select pname,price*0.75 折后价 from product;
  • select 1+1; –mysql里面可以这样写

• 条件查询 [where关键字] : 查询表中符合条件的数据

• where 列名 条件
• –关系/比较运算符: > ,>=, =, <, <= ,!=, <>
• != : SQLserver
• <> : 标准SQL中不等号
• 注意: null值: 代表的是不确定的,未知的,不可预知的内容,不能使用关系运算符
• 逻辑运算: and , or , not

• 其它运算符:

  • is null
  • is not null
  • in (集合)
  • between..and..

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

  • select 显示的列名 from 表名 where 查询条件
  • select * from product where price > 60;

• where 后的条件写法
  *例子

    --查询商品价格不等于99的所有商品
    select * from product where price <99;
    select * from product where price != 99;
    --查询出商品价格不为null的所有商品
    select * from product where price !=null;(错误的写法)
    select * from product where price is not null;
    select * from product where price is null;
    -- 查询商品价格在10 到 100之间
    select * from product where price >=10 and price <=100;
    select * from product where price between 10 and 100;
    --数学区间写法: [10,100] 
    ---错误写法: select * from product where price between 100 and 10;
  
    --查询出商品价格 小于35 或者商品价格 大于900
    select * from product where price <35 or price >900;
    --like: 模糊查询  
        %  匹配多个字符
        _  匹配单个字符
    -- 查询出名字中带有辣的所有商品:%辣%  
    select * from product where pname like '%辣%';   
    -- 查询出名字第三个字是辣字的所有商品信息
    select * from product where pname like '__辣%';  
    -- in 判断值是否在某个集合/范围   in(1,3,4)
    --查询出商品分类ID在 1,3,4里面的所有商品
    select * from product where pid in(1,3,4); 
    -- delete from product where pid=10;
  

• 排序查询: order by 关键字
  

  • 例子
    

    升序: asc   ascend  默认是升序 <br>
    降序: desc  descend <br>
    --0. 查询所有商品,按照价格进行排序 <br>
    select * from product order by price; <br>
    --1.查询所有的商品,按价格进行降序排序 (asc-升序  desc-降序); <br>
    select * from product order by price desc; <br>
    --2.查询名称有 辣 的商品,按价格升序 <br>
    --1.找到有 辣的商品 <br>
    select * from product where pname like '%辣%'; <br>
    --2.排序 <br>
    select * from product where pname like '%辣%' order by price asc;</li></ul>
  

• 聚合函数:
  

  • 例子
    

  sum() 求和  avg()求平均值 count()统计个数 max() min() <br>
    --1.获得所有商品价格的总和:  <br>
    select sum(price) from product; <br>
    --2.获得所有商品的平均价格:  <br>
    select avg(price) from product; <br>
  

  
  –3.获得所有商品的个数:
  select count(pid) from product;
  select count(*) from product;
  
  select 1 from product;
  select count(1) from product; –写常量,比较高效
  
  –注意: where 条件后面不能接聚合函数 having 后面可以加
  

• 分组: group by
  

  • 例子
    

  select 分组的条件,分组之后的操作 from 表名  group by 分组条件 having 条件; <br>
    --1.根据cno字段分组,分组后统计商品的个数 <br>
    select cno, count(*) from product group by cno; <br>
  

  
  –分组之后的条件过滤: having
  –2.根据cno分组,分组统计每组商品的平均价格 并且商品平均价格 60
  select cno, avg(price) from product group by cno ;
  select cno, avg(price) from product group by cno having avg(price) 60;

• 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