mysql

Mysql笔记：
今日任务：
完成对mysql数据库中的数据库，表及数据的CRUD的操作
目标：
掌握mysql的基本数据库，表及表中的记录的操作
1、完成对分类表的CRUD的操作
需求分析:使用cmd命令来创建一个数据库，并对数据库中的分类表进行增删改查
1.1：数据库的概述：
1.1.1:什么是数据库？    
数据库是一个文件系统，只不过我们需要命令(sql)操作。
数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
数据库是长期储存在计算机内、有组织的、可共享的数据集合
1.1.2：数据库的作用？
储存数据，数据的仓库，带有访问权限限制不同人可以由不同的操作
1.1.3：为什么要学习数据库？
操作的都是后台数据，取到后台数据进行封装，然后交给前台去展现
         1.1.4：常见的数据库：
Mysql:开源免费的适用于中小型企业的免费数据库，sun收购了mysql
Sun公司被oracle公司收购了。Oracle收购之后，开始收费了。
Marladb：是由mysql创始人摘出来的，直接是msql开源版本的一个分支，基本上命令都是一样的。
Oracle：甲骨文公司，商业软件，收费软件，适用于大型电商网站。收购了sun公司，java语言的运行环境是不能被修改的。
Db2:IBM公司，thinkpad，解决方案：软件和硬件，服务器架构，银行系统大多采用的是DB2的数据库。
SqlServer：windows里面，政府的网站，asp.net，大学教学基本采用的是sqlserver，图形化工具做的不错。
Sybase:被淘汰的数据库
2、关系型数据库：
主要是用来描述实体与实体之间的关系。
实实在在存在的事物：学生与班级，员工与部门。
E-R关系图：要求必须得会画。
实体：方框
属性：椭圆
关系：菱形
3、mysql数据库服务器：
Mysql数据库：数据库管理软件
服务器：就是一台电脑。这台安装相关的服务器软件，这些软件
会监听不同的端口号，根据访问的端口号，提供不同的服务。

4、mysql数据库的安装和卸载：
卸载：
    (1)打开控制面板，卸载软件
    (2)删除mysql安装目录的所有文件C:\Program Files\MySQL
    (3)删除mysql存放文件：C:\ProgramData\MySQL
安装：傻瓜式安装。
图形化界面：sqlyog软件

5、mysql的SQL语句：
 Sql：structure Query language结构化查询语言
DDL：
    数据定义语言：定义数据库，数据表，他们的结构
    Create(创建) ，drop(删除)，alter(修改)
DML：
    数据操作语言：主要是用来操作数据得。
    Insert(c插入),update(修改),delete(删除)
DCL：
    数据控制语言。定义访问权限，取消访问权限，事务，用户
DQL：
    数据查询语言。Select(查询),form子句，where子句

6、数据库的CRUD的操作：
首先登陆数据库的服务器:mysql -uroot -proot
证明连接成功：

创建数据库****：
    Create database 数据库的名字;
    Create database day06; ----例子
    创建数据库的时候，指定字符集
    Create database 数据库的名字 character set 字符集;

删除数据库：
        DROP database day06;
查看数据库：
    show databases;
使用或进入某个数据库：
    use 数据库名称;

创建表****：
创建：
    Create table 表名(字段描述，字段描述)
    字段描述  ----字段名称 字段类型
    举例子：create table users(
    id int,
    name varchar(20)
    );

删除：
    drop table 表名;
举例子：drop table users;
(了解)常用命令：
查看当前数据库下所有表：show tables;
查看表结构：desc 表名;
查看建表语句：show create table 表名;

修改：
修改表名：
    Alter table 表名;
修改表名：alter table 表名 rename to 新表名;
举例子：alter table users rename to user;
        修改字段：
Alter table 表名 change 字段名称  新字段名称描述
举例：alter table user change name username varchar(40);
修改字段类型：
Alter table 表名 modify 字段名称 字段类型
举例：alter table user modify name varchar(50);
添加字段：
Alter table 表名 add 字段描述;
举例：alter table user add password varchar(20);
删除字段：
Alter table 表名 drop 字段名称;
举例：alter table user drop password;

java中    数据库中
Byte    Tinyint
Short    smallint
int    Int(*)
long    bigint
boolean    Tinyint|int
char|String    Char(长度)|varchar(长度)
Varchar(*)mysql方言，可变的长度
float|double    Float|double(*) double(5,2)数位数是5，小数占2位
Java.sql.Date    Date日期(*)
Java.sql.Time    Time时间(*)

Java.sql.timestamp    timestamp时间戳(*)
若类型为时间戳，name当值设置为null时，该列的数据值取系统当前的值
Java.sql.Clob(长文本)    mysql的方言(text)
Java.sql.Blob(二进制)    blob

7、数据库表的增删改查
DML：数据操作语言：
        操作对象：记录。
        关键字：insert update delete
插入insert:
格式1：
    insert into 表名 values(字段1,字段2....,字段n);
    注意：默认插入全部字段
    必须保证values内容(类型，顺序)和表中一致
    若类型非数值，必须加引号’’;
    举例：INSERT INTO USER VALUES (1,'zhangsan');
格式2(*):
    insert into 表名(字段名,字段名....,字段名n) values(字段值，字段值，字段值n);
    注意：插入指定的字段
    必须保证表名和values内容(类型，顺序)一致。
    INSERT INTO USER (id,username) VALUES (2,'王五');
修改：update
    Update 表名 set 字段名=字段值,字段名=字段值....【where 条件】;
    注意：where条件
    UPDATE USER SET username='xxx' WHERE id=1;
删除：delete
    Delete from 表名【where条件】;
    注意：where条件
    DELETE FROM USER WHERE id=1;
    (了解)truncate 表名;干掉表，重写建一张和表结构一样的表。不能逐条删除

查询：
DQL：数据查询语言：
关键字：select
格式：
    select .... from 表名
                Where条件
                Group by 分组字段
                Having 条件
                Order by 排序字段 ase| desc
初始化环境：
--创建商品表：
    create table products(
                pid int primary key auto_increment,
                pname varchar(20),
                price double,
                pnum int,
                cno int,
                pdate timestamp
            );

        insert into products(pname,price,pnum,cno,pdate) values ('泰国大榴莲',98,12,1,null);
            insert into products values (null,'新疆大枣',38,123,1,null);
            insert into products values (null,'新疆切糕',68,50,2,null);
            insert into products values (null,'十三香',10,200,3,null);
            insert into products values (null,'老干妈',20,180,3,null);
            insert into products values (null,'豌豆黄',20,120,2,null);    
  
    
简单查询:
        练习:
        1.查询所有的商品的信息.
            select * from products;
            
        2.查询商品名和商品价格.
            --查询指定列。
            -- 格式: select 字段名1,字段名2 from 表名    
                select pname,price from products;
                
        3.查询所有商品都有那些价格.
            -- 去重操作 distinct
            -- 格式: select distinct 字段名,字段名2 from 表名    
                select distinct price from products;
                
        4.将所有商品的价格+10元进行显示.(别名)
        -- 可以在查询的结果之上进行运算,不影响数据库中的值
            SELECT pname,price+10 FROM products;    
            
            -- 给列起别名 格式: 字段名 [as] 别名
            SELECT pname AS "商品名",price+10 商品价格 FROM products;

条件查询:
        练习:
        1.查询商品名称为十三香的商品所有信息：    
            SELECT * FROM products WHERE pname='十三香';
                
        2.查询商品价格>60元的所有的商品信息:
            SELECT * FROM products WHERE price>60;
        
        3.查询商品名称中包含”新”的商品    
            --模糊查询：LIKE
            --%很多任意字符
            --    格式: 字段名 like "匹配规则";
            --        匹配内容 %
                            "龙"    值为龙
                            "%龙"    值以"龙"结尾
                            "龙%"    值以"龙"开头
                            "%龙%"     值包含"龙"
            --        匹配个数 "__" 占两个位置
            
            SELECT * FROM products WHERE pname LIKE '%新%';
            
            
            此时\是转义字符，将_转义。
            SELECT * FROM products WHERE pname LIKE '%\_%';
        
        4.查询价格为38,68,98的商品
            SELECT * FROM products WHERE price=38 OR price=68 OR price=98;
            SELECT * FROM products WHERE price IN(38,68,98);
            SELECT * FROM products WHERE price BETWEEN 38 AND 98;
            
            where后的条件写法：
            * > ,<,=,>=,<=,<>,!=
            * like 使用占位符 _ 和 %  _代表一个字符 %代表任意个字符.
                    * select * from product where pname like '%新%';
            * in在某个范围中获得值.
                    * select * from product where pid in (2,5,8);
            * between 较小值 and 较大值
                select * from products where price between 50 and 70;

排序查询:
        order by 后面可以是字段名,序号,别名.
            
        1.查询所有的商品，按价格进行排序.(asc-升序-默认,desc-降序)
            select * from products order by price;
        2.查询名称有新的商品的信息并且按价格降序排序.
            select * from products where pname like '%新%' order by price desc;

聚合函数|通用函数|组函数|多行函数:
            对一列进行计算 返回值是一个,忽略null值
        * sum(),avg(),max(),min(),count();
        1.获得所有商品的价格的总和：
            select sum(price) from products;
        
        2.获得商品表中价格的平均数：
        SELECT AVG(price) FROM products;

        -- round(x,2) 保留x的两位小数，四舍五入
        SELECT ROUND(AVG(price),2) FROM products;
        
        3.获得商品表中有多少条记录：
        select count(pid) from products;

   group by 增强 ： group by rollup(a,b)  ）

分组：使用group by，查询时，凡是select后不在组函数中的字段，
                                都需要写在group by后面
                                
        1.根据cno字段分组，分组后统计商品的个数.
        select count(*) from products;
        SELECT pid,cno,COUNT(*) FROM products GROUP BY pid,cno;
        -- having 是对分组后的数据进行条件过滤。
        -- where 是对分组前的数据进行条件过滤。并且where后面不能使用组函数。
        2.根据cno分组，分组统计每组商品的总数量，并且总数量> 200;
            SELECT cno,SUM(pnum) FROM products GROUP BY cno HAVING SUM(pnum)>200;
/
auto_increment 自增  (oracle没有--序列)
    要求:
        1.被修饰的字段类型支持自增. 一般int
        2.被修饰的字段必须是一个key 一般是primary key
        3.用来在表的主键上修饰。

    create table products(
            pid int primary key auto_increment,
            -- pid这一列是表的主键 ：primary key
            -- pid并且自动增长：auto_increment
            

    create table ai01(
        id varchar(10) auto_increment
    );-- 错误 Incorrect column specifier for column 'id'

    create table ai01(
        id int auto_increment
    );-- 错误 Incorrect table definition;
    
    
    例子：
        CREATE TABLE ai01(
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(20),
            PASSWORD VARCHAR(40)
        );
        
        INSERT INTO ai01(id,NAME,PASSWORD) VALUES(1,'tom','123');
        INSERT INTO ai01(NAME,PASSWORD) VALUES('tom','123');
        INSERT INTO ai01(id,NAME,PASSWORD) VALUES(NULL,'tom','123');
/
约束:
    作用:
        为了保证数据的"有效性"和"完整性"。    

    mysql中常用的约束:都是加在某个字段上
        主键约束(primary key)  
        唯一约束(unique)
        非空约束(not null)
        外键约束(foreign key)
/
主键约束(primary key)  
        被修饰过的字段唯一,非空
        
        注意:一张表只能有一个主键,这个主键可以包含多个字段(联合主键)    
        
        方式1:建表的同时添加约束 格式: 字段名称 字段类型 primary key
            CREATE TABLE ai01(
                id INT PRIMARY KEY AUTO_INCREMENT,
                ...
                );

                
        方式2:建表的同时在约束区域添加约束
            所有的字段声明完成之后,就是约束区域了
            格式: primary key(字段1,字段2)    
            
            CREATE TABLE ai01(
                id INT,
                NAME VARCHAR(20),
                PASSWORD VARCHAR(40),
            
                -- 上面声明完表的字段后，从这里开始就属于约束区域。
                primary key(id)
                );

                
        方式3:建表之后,通过修改表结构添加约束，一般应用在多表建立时.
            create table pk03(
                id int,
                age int,
                username varchar(20)
            );    
            
            alter table pk03 add primary key(字段名1,字段名2..);
            alter table pk03 add primary key(id,age); -- 联合主键
/
唯一约束:(了解)
        被修饰过的字段唯一,对null不起作用
        方式1:建表的同时添加约束 格式: 字段名称 字段类型 unique
            create table un(
                id int primary key auto_increment,
                username varchar(20) unique
            );
            
            insert into un value(9,'tom');-- 成功
            insert into un value(10,'tom');-- 错误
            insert into un value(null,'jack');-- 成功
            insert into un value(null,'rose');-- 成功
            
        方式2:建表的同时在约束区域添加约束
            所有的字段声明完成之后,就是约束区域了
            unique(字段1,字段值2...)
            
            create table un2(
                id int primary key auto_increment,
                username varchar(20)
                unique(username)
            );
            
        方式3:建表之后,通过修改表结构添加约束
            create table un3(
                id int primary key auto_increment,
                username varchar(20)
            );
            
            alter table 表名 add unique(字段1,字段2);-- 添加的联合唯一
            alter table 表名 add unique(字段1);-- 给一个添加唯一
            alter table 表名 add unique(字段2);-- 给另一个添加唯一
    
    非空约束(了解)
        特点:被修饰过的字段非空
        方式:
            create table nn(
                id int primary key auto_increment,
                username varchar(20) not null unique -- 该字段两个约束
            );
            
            insert into nn values(null,null);--  错误的 Column 'username' cannot be null    
/
创建多表    
网上商城的实体:
    用户 订单 商品
常见关系:
    一对多. 用户和订单
    多对多. 订单和商品    学生和课程
    一对一. 丈夫和妻子    人和身份证    
    
    学习路线：
        1.明确多张表之间有哪些关系(一对多,多对多,一对一)
        2.如何建立数据库中多张表的关系(外键foreign key)
        3.如何查询多张表的数据(条件连接)
    
    (★)外键约束(foreign key),用于建立多个表之间的关系。
    
    
    java中关系：
        class User{
            int id;
            String username;
            List<Orders> orders;  -- 一个User对象，有多个Orders对象
        }
        
        class Orders{
            int id;
            double totalprice;
        }
/

一对多:
    用户和订单    
    -- 创建用户表 -- 1
    create  table user(
        id int primary key auto_increment,
        username varchar(20)
    );
    
    -- 创建订单表 -- N
    create  table orders(
        id int primary key auto_increment,
        totalprice double,
        user_id int -- 外键列,关联user表的id列
    );    
    
    
    为了保证数据的有效性和完整性,添加约束(外键约束).
    在多表的一方添加外键约束，关联另一张表某列(主键列)
        格式:
            alter table 多表名称 add foreign key(外键列) references 一表名称(主键);
        例如:
            alter table orders add foreign key(user_id) references user(id);
    
    添加了外键约束之后有如下特点:★
        1.主表中不能删除从表中已引用的数据
        2.从表中不能添加主表中不存在的数据
        
        
    开发中处理一对多:★
        在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
        为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.
//
多对多

例子:商品和订单
        -- 创建商品表
    create table product(
        id int primary key auto_increment,
        name varchar(20),
        price double
    );

    -- 创建中间表
    create table orderitem(
        oid int,
        pid int
    );        
        
    -- 添加外键约束
        alter table orderitem add foreign key(oid) references orders(id);
        alter table orderitem add foreign key(pid) references product(id);
        alter table orderitem add primary key(oid,pid); -- 做为联合主键存在。
    
    开发中处理多对多:★
        引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,
        这样就可以将多对多的关系拆分成两个一对多了
        为了保证数据的有效性和完整性
            需要在中间表上添加两个外键约束即可.
/
-- 多表查询

构建数据环境：新建一个数据库,按照顺序添加数据
    
    
    
    初始化数据:
    -- 用户表(user)
       create table `user` (                             
          `id` int auto_increment primary key,                
          `username` varchar(50)  -- 用户姓名                                                
        );

    -- 向user表中添加数据
        insert into user values(3,'张三');
        insert into user values(4,'李四');
        insert into user values(5,'王五');
        insert into user values(6,'赵六');

        
    -- 订单表(orders)
        create table `orders` (                                                  
          `id` int  auto_increment primary key,                                  
          `price` double,                                           
          `user_id` int                                       
        );
        
        -- 给订单表添加外键约束
        alter table orders add constraint user_fk foreign key (user_id) references user(id);

    -- 向orders 表中插入数据
        insert into orders values(1,1314,3);
        insert into orders values(2,1314,3);
        insert into orders values(3,15,4);
        insert into orders values(4,315,5);
        insert into orders values(5,1014,null);