一.SQL语言组成部门

 DDL database table index view 

 DML  Select update insert delete

 主键,外键 唯一键,条件,非空,事务,

 视图的定义:虚表,存储下来的Select语句

 事务控制

 动态SQL和嵌入式SQL

 DCL 授权

   

##数据类型

1.为什么会有数据类型


1.存储的值类型

2.占据的樵存储空间

3.定长,变长 

4.如何被索引及排序

5.是否能够被索引


二.数据类型的有哪些

   

  1)字符型

     

      char 

      varchar

      binary

      varbinary

      text

      blob

     

    char,varchartext几种字符型常用的属性修饰

      1.  NOT NULL : 非空约束

      2.  NULL :允许为空

      3.  DEFAULT 'string' :默认值,不适用与Text类型 

      4.  character set'字符集'

          mysql> SHOW VARIABLES LIKE '%char%'  这是服务器上的

            mysql> SHOW CHARACTER SET 全部的字符集 


        5.  show collation 排序规则

          

           

  2)数值型

     

    精确数值型

        整型

        十进制数据:decimal

    近似数值型

        单精度浮点型

        双精度浮点型

     

  整形的常用属性修饰符

  

      1.AUTO_INCREMENT:自动增长

      前提:非空,且唯一,支持索引

      mysql> select LAST_INSERT_ID();


      2.LEST_INSERT_ID():可以查看上次增长的数值,当插入多行时,只记录第一行

      3.UNSIGNED:无符号

      4.NULL

      5.NOT NULL

      6.DEFAULT


   TRUNCATE 用来清空表中数据

       

  3)日期时间型

     

      日期型

      时间型

      日期时间型

      时间戳

      year

 

  4)布尔型

 

  5)内建类型

      enum(枚举)

      set(集合)

 

三.数据字典

  定义: 可以说为是系统编目,目录(system catalog) ,保存数据库服务器上的元数据库 

   

   ** 元数据:非数据本身,但是又能够管理数据需要的一些信息  数据与表  表的名字 **

元数据:

       

      关系的名字

      每个关系的各字段的名字

      各字段的数据类型和长度

      约束

      每个关系上的视图的名字及视图的定义

      授权用户的名字

      用户的授权和账户信息   

   

统计类的数据

    

    每个关系字段的个数

    每个关系中行数

    每个关系的存储方法

   

保存元数据的数据库


       information_schema

       mysql

       performance_schema  


##SQL_MODESQL模型

SQL_MODE定义:用来定义字符超出的操作和模仿别的数据库的类型,通过修改全局变量


常用的模式有:


  TRADITIONAL 使用传统模式

  STRICT_TRANS_TABLES 仅对支持事务的表的严格模式

  STRICT_ALL_TABLES 对所有表使用严格模式

   

   

 设定服务器变量值得方式(通常仅支持动态的变量)

    支持修改服务器变量:

       

       动态变量:可以MySQL运行时修改

       静态变量:于配置文件中修改其值,并重启后方能生效;

    

   服务器变量从其生效范围来讲,有两类:

   

      全局变量 服务器级别,修改后对新建立的会话有效 GLOBAL 

      会话变量 会话级别,仅对当前会话有效 session 

       <!--会话建立时,从全局继承各变量-->

   


查看服务器变量:

  

***mysql> show {global|session} variables [like '(sql_mode)'];***

  

***mysql> select @@{globa|session}.variable_name  ;***

        

**mysql> select * from    GLOBAL_VARIABLES where variable_name='sql_Mode';**

          

**select * from  session_VARIABLES where variable_name='sql_Mode';**

         

修改变量:   

   <!-- 前提:仅管理员有权限修改全局变量-->

   

   **mysql> set {global|session} variable_name='value';**

  

  ***注意:无论是全局还是会话级别的动态变量修改,在重启mysql后都会失效,想永久有效,可以定义在配置文件中的响应段中[mysqld]**

  

  

##MySQL中字符大小写:


    1.SQL关键字及函数不区分大小写

    2.数据库、表及视图名称的大小写区分与否取决于底层OSFS

    3.存储过程、存储函数及事件调度器的名字不区分大小写,但触发器区分大小写

    4.表别名区分大小写

    5.对字段中的数据,如果字段类型为binary类型,则区分大小写,非binary不区分大小写


四.MySQL基本使用

数据库:


    创建数据库

     create database

     CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [[DEFAULT] CHARACTER SET [=] charset_name 字符集

    | [DEFAULT] COLLATE [=] collation_name 排序方式]

    

    列如:create database if not exists mydb;

         create database if not exists mydb character set utf-8 

             

    删除数据库

     DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

     列如:drop database if not exists mydb;

     

    修改数据库的字符集和排序字符以及数据字典

    ALTER {DATABASE | SCHEMA} [db_name]

    [[DEFAULT] CHARACTER SET [=] charset_name 字符集

    | [DEFAULT] COLLATE [=] collation_name 排序方式]

    

表:

 

  创建表:第一种方式

    CREATE [TEMPORARY(临时表,保存在内存中)] TABLE [IF NOT EXISTS] tbl_name

    (create_definition,...)

    [table_options]

 

      (create_definition,...)  可以定义:

  字段的定义:字段名、类型和类型修饰符

       键、索引和约束

          primary keyunique keyforeign keycheck

          {index|key} 

       

        表创建:第二种方式(复制表数据)

          CREATE [TEMPORARY] TABLE [IF        NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options]

    select_statement

    

        表创建:第三种方式(复制表结构)

          CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    { LIKE old_tbl_name | (LIKE old_tbl_name) }    

  

  查看表的状态信息

         show table status like ‘table_name’


 

  MyISAM表:每表有三个文件,都位于数据库目录中 

     

        tb_name.frm:表结构定义

        tb_name.MYD:数据文件

        tb_name.MYI:索引文件


InnoDB表,有两种存储方式

       

        1.默认:每表有一个独立文件和一个多表共享的文件

        tb_name.frm:表结构的定义,位于数据库目录中

        ibdata#:共享的表空间文件,默认位于数据目录(datadir指向的目录)中

        2.独立的表空间文件:

        每表有一个表结构文件tb_name.frm

        一个独立的表空间文件 tb_name.ibd

          应该修改innodb_file_per_tableON   


删除表

      

      DROP [TEMPORARY] TABLE [IF EXISTS]

      tbl_name [, tbl_name] ...

      [RESTRICT | CASCADE]


修改表: 

      

      ALTER TABLE tbl_name

      [alter_specification [, alter_specification] ...]

    修改字段定义:

       插入新字段:

         ADD [COLUMN] col_name column_definition

         [FIRST | AFTER col_name ]

       删除字段

         DROP [COLUMN] col_name

       修改字段

          修改字段名称

          CHANGE [COLUMN] old_col_name new_col_name column_definition

          [FIRST|AFTER col_name]

          修改字段类型及属性等

          MODIFY [COLUMN] col_name column_definition

          [FIRST | AFTER col_name]

       表改名:

          rename to|as new tb_name

       修改存储引擎

           engine = 

       指定排序标准的字段

           ORDER BY col_name [, col_name] ... 


  

5.MySQL的查询分类

    1.单表查询:简单查询

    2.多表查询:连续查询

    3.联合查询

4.选择和投影

投影:挑选要符合的字段

    选择:挑选符合条件的行


      投影:select 字段1,字段2... from tb_name

        selcet * from tb_name

        

        选择:select 字段1,字段2,.... from tb_name where 子句(布尔条件表达式);

           

   

##布尔条件表达式操作符

= 等值比较

<=> 跟空值比较不会产生额外信息

<> 不等值

<   小于 

<=  小于等于

>   大于

>=  大于等于


IS NULL: 是否为空

IS NOT NULL:是否不空

LIKE:支持的通配符%(任意长度的任意字符) _(任意单个字符)

RLIKEREGEXP:支持使用正则表达式作为条件

IN:判断某行的某一字段的值是否在给定的列表中

BETWEEN...AND....:判断指定的值是否位于指定的范围之间

 

6.单表查询用法   

 组合条件测试


NOT |

AND &&

OR ||

排序

    

    order by ‘排序字段

    默认为升序:ASC

    降序:DESC


内置的聚合函数

    

    sum():和

    AVG():平均值

    MAX():最大值

    MIN():最小值

    COUNT():个数统计


分组

   

    group by

    select Gender,sum(age) from student group by Gender;

    

    对分组的条件过滤

    having

     select ClassID,Count(Name) FRom students GROUP BY CALSSSID HAVING Count(Name) >=2;

    

   

只返回有用的行

 

  LIMIT 

  一个数为显示的行数

  两个数字为偏移第一个数字行,显示第二个数字     

   

    select * from studnet LIMIT 2,3;

    

**select语句的执行流程

  from clause --> where clause --> group by --> having clause -->order by --> select -->limit**   


select语句

  

  distinct   重复的只显示一次

  SQL_CACHE  缓存查询结果

  SQL_NO_CACHE 不缓存查询结果



##MySQL多表查询和子查询


1.联结查询:事先将两张或者多张表join,根

join的结果进行查询


   交叉联结:

    

      (a+b)c+d+e) 6

        

      效率低,基本上不用

   

  自然联结

       

       等值联结:把相同的字段进行等值连接

  外联结:

        

        左外联接:只保留出现在左外连接元算之前(左边)的关系中的元组(以左表为准,)   

        

          left_tb LEFT JOIN right_tb ON 条件 

       

        右外联接:只保留出现在右外连接元算之后(右边)的关系中的元组(以右表为准)

           left_tb RIGHT JOIN right_tb ON 条件 

      

        全联接:

         SELECT * FROM table01 JOIN table02

ON 条件 


    <!--别名:AS

       表别名:

       字段别名-->

子查询:在查询中嵌套的查询


  A) 用于WHERE中的子查询

    1.用于比较表达式中的子查询

        子查询的返回值只能有一个

    2.用于EXISTS中的子查询

        判断存在与否

    3.用于IN中的子查询

        判断存在于指定列表中

    B 用于FROM中子查询

    select alias.col,....FROM(SELECT CLUSE) alias WHERE condition


**MYSQL不擅长于子查询,应该避免使用子查询 优化一般**  ??


 

##MYSQL视图(虚表)

 存储下来的select语句当表来玩   


创建:

  create view 视图名 as select语句

删除:

  drop view 视图名

   

##索引


查看索引   show indexs from tb_name

查看索引   alter table tb_name add index(字段)