mysql笔记

                                      2016.12.13

 1、DataBase:(数据库)（DB）
    DBMS：（数据库管理系统）
    Oracle Mysql
    SQL Server
         DB2
    数据库管理员DBA
2、 数据库端口：3306
     latin1不支持中文
      GBK/GB2312/GB13080/UTF-8支持中文
    用户名：root
    密码：自选
3、服务器 端口 用户名 密码

4、SQL：结构化查询语言
   DDL:数据定义语言：create drop alter 定义数据结构
   DML:数据操纵语句:insert delete update select增删改查
   DCL：数据控制语句:grant  revoke 定义数据库访问权限和安全级别
   TCL:事务控制语句:commit rollback savepoint
5、drop删除表数据及表机构
   delete：删除表数据

6、insert into student(stunum,name,grade,major) values(6,'张学友','土木工程','0103');
   少用insert into grade_table VALUES(1,'01',73),(1,'02',88),

7、distinct 去掉重复的内容

8、什么是主键
9、desc降序、asc升序
10、总数量：count()   最大值：max()  最小值：min()   平均值：avg() 求和：sum()

11、sql对字母大小不敏感

12、select：从数据库表中获取数据     SELECT 列名称 FROM 表名称
    update：更新数据库中的数据      UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    delete：从数据库中删除数据       DELETE FROM 表名称 WHERE 列名称 = 值
    insert into：向数据库中插入数据  insert into table_name (列1, 列2,...) VALUES (值1, 值2,....)

    create database创建新数据库    CREATE DATABASE database_name
    alter  database修改数据库
    create table创建新表
    alert table变更数据库表
    drop table删除表
    create index创建索引
    drop index删除索引


13、select：从表中选取数据，结果储存在一个结果表中
    where：有条件的从表中选取数据
    order by: 语句默认按照升序对记录进行排序

    limit:范围之内，比如limit 5=====>>小于5
    limit 1,1 取第2条 limit 2,5 取第3条到8条，共5条

    top：表示前前面多少 top 10 ：前面十条    top  50precent 前面的50%
    like：用来搜索包含的内容，比如 ：
    SELECT * FROM Persons  WHERE City LIKE 'N%'    搜索以N开头的城市的人
    SELECT * FROM Persons  WHERE City LIKE '%g'   搜索以g结尾的城市的人
    SELECT * FROM Persons  WHERE City LIKE '%lon%' 搜索城市包含lon的人
    SELECT * FROM Persons  WHERE City NOT LIKE '%lon%' 搜索城市不包含lon的人
    SELECT * FROM Persons  WHERE FirstName LIKE '_eorge'选取名字的第一个字符之后是 "eorge" 的人：
    SELECT * FROM Persons  WHERE LastName LIKE 'C_r_er'：
   从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"
    SELECT * FROM Persons WHERE City LIKE '[ALN]%'  "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
    SELECT * FROM Persons WHERE City LIKE '[!ALN]%'  从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人

14、
    1)、in 操作符允许我们在 where 子句中规定多个值。
        SELECT * FROM Persons WHERE LastName IN ('Adams','Carter') 选取姓氏为 Adams 和 Carter 的人

    2）、操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
       SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
                             以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人
   
    3）、可以为列名称和表名称指定别名（Alias即as）
          SELECT column_name AS alias_name FROM table_name       
                                               
    4）、SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据              
         JOIN: 如果表中有至少一个匹配，则返回行
         LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
         RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
         FULL JOIN: 只要其中一个表中存在匹配，就返回行

    5）、UNION 操作符用于合并两个或多个 SELECT 语句的结果集。                
 
    6）、SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
         SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
        （1）、SELECT *  INTO Persons_backup FROM Persons          把Persons拷贝到Persons_backup
             
         （2）、SELECT *INTO Persons IN 'Backup.mdb'FROM Persons    IN 子句可用于向另一个数据库中拷贝表：

         （3）、SELECT LastName,FirstName INTO Persons_backup FROM Persons  把Persons里面的LastName,FirstName拷贝

         （4）、SELECT LastName,Firstname
                INTO Persons_backup
                 FROM Persons
                 WHERE City='Beijing'
            从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表
 
         （5）、SELECT Persons.LastName,Orders.OrderNo
                INTO Persons_Order_Backup
                FROM Persons
                INNER JOIN Orders
                ON Persons.Id_P=Orders.Id_P
             创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息



15、 创建表：

      CREATE TABLE 表名称
      (
       列名称1 数据类型,
         列名称2 数据类型,
        列名称3 数据类型,
        ....
        )；

     1）、
             integer(size)
             int(size)
             smallint(size)
             tinyint(size)
          仅容纳整数。在括号内规定数字的最大位数。   
     
      2）、decimal(size,d)
           numeric(size,d)
          容纳带有小数的数字。
          "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。


      3）、char(size)
           容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。
           在括号中规定字符串的长度。

     4）、varchar(size)
          容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。
          在括号中规定字符串的最大长度。

     5）、date(yyyymmdd) 容纳日期。


16、SQL 约束
    
      not null
      unique
      primary key
      foreign key
      check
      default
 

（1）
    1)、NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

    下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值：
    CREATE TABLE Persons
    (
    Id_P int NOT NULL UNIQUE,  //方法一
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
     UNIQUE (Id_P)//方法二
   constraint uc_PersonID UNIQUE (Id_P,LastName)  //方法三

    )
 
  2）、UNIQUE 约束唯一标识数据库表中的每条记录，UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
       PRIMARY KEY 拥有自动定义的 UNIQUE 约束，请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

  3）、当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL
       ALTER TABLE Persons  ADD UNIQUE (Id_P)

  4）、如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

      ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

  5）、如需撤销 UNIQUE 约束，请使用下面的 SQL：
       ALTER TABLE Persons DROP INDEX uc_PersonID或者ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

 
 （2）、SQL PRIMARY KEY 约束
    
     1）、PRIMARY KEY 约束唯一标识数据库表中的每条记录，主键必须包含唯一的值主键列不能包含 NULL 值，
                 每个表都应该有一个主键，并且每个表只能有一个主键。
 
     2）、下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：

        CREATE TABLE Persons
        (
        Id_P int NOT NULL PRIMARY KEY,//方法一
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255),
        PRIMARY KEY (Id_P)//方法二
        CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName) //方法三
          )
 
     3）、如果在表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
 
        ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
 
     4）、如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

         ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

      5）、如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
         ALTER TABLE Persons DROP PRIMARY KEY 或者ALTER TABLE Persons DROP CONSTRAINT pk_PersonID


  （3）、FOREIGN KEY 约束
        一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
         
     1）、下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：
   
        CREATE TABLE Orders
       (
        Id_O int NOT NULL,
        OrderNo int NOT NULL,
        Id_P int,
        PRIMARY KEY (Id_O),
        foreign key (Id_P) references Persons(Id_P)//方法一
        Id_P int FOREIGN KEY REFERENCES Persons(Id_P)//方法二
        CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) //方法三

          )

      2）、如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

          ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

      3）、如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：
        
          ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

      4）、如需撤销 FOREIGN KEY 约束，请使用下面的 SQL
          ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders 或者ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders




                                 2016.12.14
 1、日期时间类型：
   date 表示年月日

   datetime用来表示年月日时分秒   优先使用
   
time来表示时分秒

   timestamp用于需要经常插入或者更新日期为当前系统时间
   
year表示年份。


   select now();  查询当前时间
   
    char
    varchar
    text  

    日期是字符串
    电话号码是字符串

     根据生日计算年龄year(now())-year(birthday)

 2、--显示数据库
     show DATABASES;
    --使用student数据库
     use student;
    --显示数据库中的表
     show tables;
    --删除test1的数据表
     Drop  database test1

    --查看表
     desc student;
 3、修改表emp的ename字段定义，将varchar(10)改为varchar(20)。
   
    alter  table  emp modify ename varchar(20)



 4、表emp上新增加字段age，类型为int(3)。
   
    Alter  table  emp
    add  column  age int(3);
 
5、将表emp改名为emp1。

   alter table emp rename emp1;



 二、MySQL常用函数
   
     insert()
    
     concat()
    
      左填充：lpad():
      右填充：rpad()
   
      截取：左：ltrim()  
            右：rtrim()
 
      ceil():返回最大值得整数，向上取整

     -连接concat、大小写转换upper/lower、填充lpad/rpad、代替replace、消除空格trim、截取字符串substring、重复结果repeat、取绝对值ABS、
     返回最大最小整数ceil、生成随机数rand、四舍五入round、 直接截断truncate、返回当前年月日curdate、返回时间戳unix_timestamp、
     返回时间戳的日期from_unxitime、返回一年中某个时间year/month/week/day/hour/minute/second、日期转换date_format、
     返回所给日期相差时间date_add、if语句、聚合函数sum/avg/count/max/min
    1、--连接
       select concat('da','aaa','ccc');//daaaaccc
    2、--大小写转换
      select upper('aaaaa'),lower('BBBBB'); //AAAAA bbbbb
    3、--对一段字符串的左边或者右边进行填充直到满足指定的字符串长度为止
        select lpad('zheshiyige',20,'*****'),rpad('zheshiyige',20,'*****');//**********zheshiyige;zheshiyige**********
 
    4、--消除左右空格
        select ltrim('  有一个空格在左边'),ltrim('有空格在右边   ');  //有一个空格在左边|有空格在右边  （空格消除了）
         select trim('  左右都有空格  ');     //左右都有空格
    5、--重复结果
       select repeat('我要重复3次',3);//我要重复3次我要重复3次我要重复3次

    6、--替换一段字符

       select REPLACE('我爱北京天安门','门','窗'); //我爱北京天安窗  (把门换成窗)

     7、--截取字符串从x的字符串开始的y的字符串长度
       select substring('我爱中国大好河山里的那个鱼',3,5);  //中国大好河


     8、取绝对值
     
        select ABS(-0.8) ,ABS(0.8);


     9、--返回值得最大整数

        select ceil(22.01),ceil(-22.01);//  23|-22

    10、--返回值得最小整数

        select floor(22.01),floor(-22.01);   22|-23

    11、--rand 返回0到1内的值

        select rand();

    12、--四舍五入
        select round(112.55); //113
 
    13、--直接截断，不进行四舍五入

        select TRUNCATE(112.5541,2);  //112.55

    14、--curdate返回当前日期只包含年月日
 
          select CURDATE();

     15、--返回日期date 的UNIX 时间戳。

         select UNIX_TIMESTAMP(now());//1481703184

     16、--返回UNIXTIME 时间戳的日期值，和UNIX_TIMESTAMP(date)互为逆操作。

         select FROM_UNIXTIME(415415441);  // 1983-03-02 09:10:41

      17 、--返回一年的中第几周  

          select week(now());     其中 year()/month()/day()/hour()/minute()方法都是当前时间

      18、--日期转换
   
        select DATE_FORMAT(now(),'%Y/%m/%d %h:%i:%s'); //大写Y是4位，大写H代表24位时间

      19、--返回所给日期相差时间如：
          SELECT  date_add(now(),interval 2 year); //2018-12-14 16:21:30(后面两年)

          -- 相差日期 向后推三天
           select DATE_ADD(now(),interval 3 day);
             - 相差日期 向前推三天
            select DATE_ADD(now(),interval -3 day);
             -- 相差日期 向后推三年
            select DATE_ADD(now(),interval 3 year);

           -- 相差日期 向前推三年
           select DATE_ADD(now(),interval -3 year_month);

      20、--if语句

          select if(8<5,'对','错');   //错



    三、保证数据的完整性所具有的约束
   
    非空：not null
    唯一：unique
    主键 ：primary key
    外键: foreign key
    check约束:check
    默认约束:default
    唯一约束和主键约束的区别：
             1)、唯一约束可以为""值 ,主键约束不可以
             2）、但是都不能有重复值
     自增长：auto_increment

  四、索引
     
 目的：提高查询的效率
   
  五、总结：
    
   1、数据库三范式：
      1）、数据库中表的每一列都是单一属性，不可再分
      2）、每一张表都要有一个主键，便于每一行都能被区分
      3）、不能用其他表的非关键字部分的信息
   2、主键的作用：1）、标识一行的唯一标识
                 2）、作为一个可以被外键有效引用的对象
      主键的设置原则：
         1）、对用户没有什么意义，不存在实际的信息
         2）、主键应该是单列的
         3）、不要对主键进行更新
         4）、不要包含动态的数据

   3、各种数据约束类型的特点  ：保证数据的完整性
     1）、主键约束（primary key）：

         主键列不能重复、不能为空
         唯一标识数据库的每条记录，必须是唯一的值，不能为空，每一个表都应该有一条主键并且只能有一条

     2）、unique约束（唯一约束）：
         不能重复，可以为""空，不能为null
        

     3）、非空约束（not null）：
         
           要求列不接受空值

     4）、外键约束（foreign key）：
        当前列只能是主表中主键数据或者null值
 
        外键的作用主要起表与表之间的关联，通过外键可以确定另外一张表的数据，能保持数据的一致性

     5）、默认约束（default）
   
        默认约束就是给列赋默认值，如果不向其中添加值得话，它就默认为我们赋的那个值，如果添加就以添加的值为准

     6）、check约束：
   
        主要作用是约束列中值得取值范围

  4、内连接和外连接的区别：
     
     内连接：也叫自然连接，多表作用联合查询，满足条件的数据才能呈现，内连接主要输返回具有交集的几个表中的数据（不一定只有2个表）
     
     外连接又分为左联接和右连接和全外连接：
      
      左连接：返回以左表数据为准的内容，如果左表有的数据而右表没有，右表所在行以null代替；left join
      右连接：返回以右表数据为准的内容，如果右表有的数据而左表没有，左表所在行以null代替；right join
      全外连接：返回左右表中所有的行，如果没有内容以null显示。  
              如果inner join 有on 一定程度上相当于内连接，如果没有on就是外连接 和cross join一样（笛卡尔积）


  5、mysql函数：
     字符串函数、数字型函数、日期函数、聚合函数、流程函数
 
  6、视图：存储在数据库中的查询的SQL语句集，包含一个名称和列，是一个虚拟表，视图的数据来源于引用视图时动态查询引用表中的数据。
     优点：1）、只引用特定行和特定列，不暴露所有的数据，隐藏表与表之间的联系，保证数据的安全性
 
           2）、简化操作，复杂的查询语句封装在视图中
    
           3）、定制：不同用户不同任务查看同一张表，看到的数据不一样

   7、存储过程:一组为了完成特定功能的SQL语句集，存储在数据库服务器中，第一次编译后，就不用再编译，可以重复使用
       优点：可以重复使用
             减少网络传输量
             安全：减少数据库SQL注入攻击
             授权:给特定用户给予储存过程授权
       
       缺点：调试储存过程麻烦
             移植困难，比如masql的存储过程不能移植到oracle中
             重新编译
             如果项目大量使用存储过程，而客户需求发生变更，就变得很麻烦
    8、jdbc操作步骤：
       1）、Class.forName()加载驱动
       2）、创建Coonection连接
       3）、通过Connection创建Statement对象
       4）、通过Statement对象执行SQL语句，完成操作
       5）、如果是查询操作，需要返回一个结果集
            如果是增删改，返回一个受影响的行数
       6）、关闭结果集、Connection、Statement

     9、java的存储过程：
       1）、Class.forName()加载驱动
       2）、创建Coonection连接
       3）、通过Connection创建CallableStatement对象
       4)、设置传入传出参数
       5）、执行sql获取传出参数
       6）、释放资源，关闭结果集、CallableStatent、Collection



                                          2016.12.15
 
 一、JDBC
     
    1、jdbc是一种访问数据库的javaAPI,开发人员可以根据jdbc来编写数据库应用程序
   
    2、jdbc可以访问任何类型的列表数据，尤其是关系数据库数据，jdbc代表java数据库连接
    3、步骤：
      
      1）、加载jdbc的驱动程序

         Class.forName("com.mysql.jdbc.Driver");
      2）、创建数据库连接
          //  试图建立到给定数据库 URL 的连接
         //通过url、用户名、密码创建Connection数据库连接,返回的是Connection对象
      Connection  conn=DriverManager.getConnection(URL, USER, PASSWORD);
 
      3）、//创建一个 Statement 对象来将 SQL 语句发送到数据库
      Statement st=conn.createStatement();

      4）、//进行数据库更新操作 返回的是行数
       int num=st.executeUpdate(sql);

   
二、视图：view是虚拟存在的 表，本身是没有数据的，视图的数据来源的是基表
      特点：1、简单，简化操作，不需要关心表之间的结构及关联条件
            2、安全，可显示不同行和列的数据，可以对不同权限的用户创建显示不同的视图
            3、独立，视图跟基表中的数据没有直接关系，基表中的数据改变了，视图中的数据会随之改变
            4、视图不保存数据
 

三、储存过程和函数：储存过程和函数是事先经过编译并储存在数据库中的一段sql语句的集合，调用存储过程和函数可以经过简化
     开发人员工作，减少数据在数据库和服务器间的传输，提高数据处理效率

     in 表示传入参数
     out 表示传出参数
     into即刻传入又可传出
      declare 申明变量 declare a int

     创建存储过程函数
    
       --创建过程函数

     create PROCEDURE  ways(in sid_1 int ,in name_1 varchar(20),in age_1 int,
    in sex_1 varchar(10) , in remark_1 varchar(100))
     begin  
     insert into student1(sid,name,age,sex,remark) values(sid_1,name_1,age_1,sex_1,remark_1);
     select * FROM  student1;
     END

      --调用
     call ways(14,'张',3,'男','你妹的');
四、API在java中就是类库

    jdbc:Mysql  Oracle SQLServer DB2
     driver是一个接口，数据库厂商必须提供实现的接口，能从其中获取数据库连接



ctrl+t 看继承关系   /**注解