MySQL学习笔记(1)MySQL基础

MySQL学习笔记(1)MySQL基础

 

MySQL的在线中文文档
http://dev.mysql.com/doc/refman/5.1/zh/index.html
MySQL的SQL语句语法在线中文文档
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#create-table

1.MySQL的主要目录
    数据库服务器端 mysql/bin/mysqld
    客户端         mysql/bin/mysql
    数据           mysql/data/

    各类数据文件位置:
    MyISAM存储引擎
    表结构文件     mysql/data/db_name/table_name.frm
    数据文件       mysql/data/db_name/table_name.MYD
    索引文件       mysql/data/db_name/table_name.MYI
    InnoDB存储引擎
    表结构文件     mysql/data/db_name/table_name.frm
    数据和索引     mysql/data/ibdata*
                   mysql/data/ib_logfile*

2.运行MySQL.
    (1)运行MySQL服务

    windows平台.
    net start mysql
    net stop mysql

    (2)客户端连接数据库
    mysql -hlocalhost -P3306 -uroot -p
    mysql -h localhost -P 3306 -u root -p

3.数据库操作
    (1)创建数据库
    create database dbname;
    create database `dbname`
    数据库名如有关键字或纯数字或特殊字符时，可以使用反引号``包含.
    数据库名大小写取决于操作系统是否区分大小写.
    数据库名可以使用中文，但要求客户端编码 set names gbk，创建的数据库名使用编码.
    创建数据库时在mysql的data目录下会创建一个目录，目录名是数据库名.
    在目录内，会创建一个文件db.opt.用于保存数据库的选项信息。

    一般语法:
    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name   
        [create_specification [, create_specification] ...]

    create_specification:   
        [DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

    (2)查看数据库
    show databases;                   查看当前的所有数据库.
    show databases [like 'patten'];   查看某些数据库
    show create database db_name;     查看数据库的创建语句.

    (3)修改数据库
    alter database db_name [修改指令]  修改数据库的属性
    例: alter database db_name character set gbk;
    修改数据库名: 可以直接修改数据库目录来修改数据库名.
    可以将数据库导出，新建一个数据库将内容导入.
    创建一个数据库，将旧数据库内的表移动(重命名)到新数据库内，删除旧数据库。

    (4)删除数据库
    drop database db_name;
    drop database if exists db_name;

    (5)指明当前使用的数据库
    use db_name;

4.表操作
    (1)创建表
    create table db_name.table_name(列结构) [表选项];
    或
    use db_name;
    create table table_name(列结构) [表选项];

    一般语法
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
         [(create_definition,...)]
         [table_options] [select_statement]

    column_definition:
         col_name type [NOT NULL | NULL] [DEFAULT default_value]
             [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
             [COMMENT 'string']

    (2)查看表
    show tables;                     查看当前数据库所有表
    show tables [like 'pattern'];    查看当前数据库某些表
    show create table table_name     查看表的创建语句
    show create table table_name \G  查看表的创建语句,显示格式有处理
    describe table_name;             描述表结构
    desc table_name;                 描述表结构,简写
    表结构对应数据库目录下的table_name.frm文件.

    (3)修改表
    修改表名
    rename table old_table_name to new_table_name,old_table_name to new_table_name;
    支持跨数据库重命名. rename table_name to db_name.table_name;
    可以利用跨数据库重命名表，可以为数据库重命名，创建一个新的数据库，旧数据库内的表都rename到新的数据库内，删除旧数据库.
   
    修改列定义
    alter table table_name [add|modify|change|drop]
    alter table table_name add [列定义]
    alter table table_name modify [列定义]
    alter table table_name change [列名] [列定义]
    alter table table_name drop [列名]

    修改表选项
    alter table table_name [表选项]
    alter table table_name character set utf8;

    (4)删除表
    drop table table_name;
    drop table if exists table_name;

5.数据操作(增删改查,crud)
    (1)查询
    select 字段列表 from 表名 where 查询条件
    select * from table_name where 1;  *表示所有，1表示条件成立.

    在查询的结尾可以使用limit限制获得的记录数量:
    LIMIT {[offset,] row_count | row_count OFFSET offset}
    如: Limit offset, row count
    offset 偏移量，索引位置从0开始，可以省略，默认为0
    row count 获得的总记录数

    可以使用distinct去掉重复记录。

    (2)新增
    insert into 表名(字段列表) values(值列表);

    (3)修改
    update 表名 set 字段 = 值,字段 = 值,.. where 条件.
    update 表名 set 字段列表 = 值列表 where 条件.

    (4)删除
    delete from 表名 where 条件
    delete from table_name where 1;

6.字符集
    (1)字符集
    如果通过客户端操作服务器，客户端与服务器之间进行数据通信，要保证编码一致，可以将互相发送的数据转换成目标可以接收的编码。
    MySQL的字符集变量:
    show variables like 'character_set%';
    character_set_client     utf8    客户端发送数据的编码(mysql/my.conf)
    character_set_connection utf8    连接数据库的编码
    character_set_database   gbk     当前默认数据库编码(mysql/data/db_name/db.opt)
    character_set_filesystem binary  文件系统的编码(二进制)
    character_set_results    utf8    客户端接收数据的编码
    character_set_server     utf8    服务器端的编码(mysql/my.conf)
    character_set_system     utf8    标识符的编码，如数据库名
    character_sets_dir       mysql/share/charsets/  字符集目录
   
    (2)字符集变量设置
    set 变量名=值

    set character_set_client=gbk;    告知服务器，客户端发送的数据是gbk编码，执行没有返回数据的语句没有问题了。 
    set character_set_results=gbk;   如果需要从服务器返回数据，需要设置服务器发送给客户端的编码, 服务器在发送数据时，才能转成客户端认识的编码.
    set character_set_connection=gbk;设置连接层编码

    可以使用以下命令设置编码，会同时改变上面三个变量的设置。
    set names gbk;

    典型的情况，用set names即可，如果情况复杂，需要分开设置。

    (3)字符集转换的过程
    client -> connection -> 服务器内部编码 -> result

    (4)校对规则
    每一套编码字符集都有与之相关的校对规则，校对规则即在当前编码下，字符之间的比较顺序  
    每个字符集都提供一个或多个校对规则，通常的命名规则是: 字符集_语言名_比较规则(ci|cs|bin)
    ci  不区分大小写
    cs  区分大小写
    bin 编码比较

    每个字符集都有一个默认的校对规则，不指定的话就使用默认的校对规则
    show collation;                查看校对规则
    show collation like 'pattern'  查看校对规则

7.数据类型
    (1)数值类型
       整数类型
       tinyint              1字节  -128 ～ 127
       smallint             2字节  -32768 ～ 32767
       mediumint            3字节  -8388608 ～ 8388607
       int/intege           4字节  -2147483648 ～ 2147483647
       bigint               8字节  -9223372036854775808 ～ 9223372036854775807
       tinyint unsigned     1字节  0 ～ 255
       smallint unsigned    2字节  0 ～ 65535
       mediumint unsigned   3字节  0 ～ 16777215
       int/intege unsigned  4字节  0 ～ 4294967295
       bigint unsigned      8字节  0 ～ 18446744073709551615
       ·使用unsigned控制是否有正负
       ·可以使用zerofill来进行前导0填充.
       ·存在布尔bool类型，它是tinyint(1)的别名
       ·通过规定数据的显示宽度，达到统一显示的目的,类型(M) M表示显示的最小宽度
       ·指定显示宽度一般跟上zerofill进行前导0填充，alter table tablename add c tinyint(2) zerofill;
       ·显示宽度不影响数值的范围，宽度小的填充前导0， 宽度大的不影响，也不会截取.

       小数类型
       float       4字节  单精度浮点数   -3.402823466E+38 ~ 3.402823466E+38 (理论值)
       double      8字节  双精度浮点数   -1.7976931348623157E+308 ~ 1.7976931348623157E+308 (理论值)
       decimal     变长定点数，每9个数字，采用4个字节存储，整数和分数分开计算.  M,最大是65，D,最大是30，默认是10,2 , -65个9 ~ 65个9
       ·可以利用M,D控制数值范围，M表示所有的数值位数，不包括小数点和符号，D表示允许的小数位数.
         float(10,2), double(10,2), decimal(10,2)
       ·可以zerofill，可以使用科学计数法.支持无符号，但一般不用.

    (2)字符串类型
       字符串
       char           固定长度字符串，最大长度255字节   char(M),M允许的字符串长度数，是字符数不是字节数.
       varchar        可变长度字符串，最大长度65535字节 但需要1-2个字节保存信息，同时有记录的限制65536,实际最大为65532，
                      编码不同字符数不同 gbk<=32767字符 utf8<=21845字符  varchar(M),M字符数
       文本:
       tinytext       最大长度2^8+1   L + n, L为最大允许长度
       text           最大长度2^16+2
       mediumtext     最大长度2^24+3
       longtext       最大长度2^32+4

       enum           枚举类型,选项量65535 , 内部存储是2字节整型表示(数值1,2,3...)，字段值只能是某一个.
                      enum('value1','value2',...)
       set            集类型，1,2,3,4,8。元素数量64.内部存储是8字节整型表示(数值1,2,4,8,16...)，字段值可以是多个.
                      set('value1','value2',...)
       binary,varbinary,blob   二进制数据

       ·varchar总长度65535,
       ·varchar当类型数据超过255个字符时，采用2个字节表示长度 65535-2=65533
       ·字段的最大长度不能超过记录的总长限制65535.
       ·整条记录需要一个哦外的字节保存当前字段为null值，除非所有的字段都不是null，这个字节才可以省略。
         只有一个varchar字段,为null时实际最大长度65532，为not null时实际最大长度65533
       ·text类型不算在记录总长中

    (3)日期时间类型
       datetime   年月日时分秒 YYYY-MM-DD HH:MI:SS  8字节  1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
       timestamp  时间戳       YYYY-MM-DD HH:MI:SS  4字节  1970-01-01 00:00:00 ~ 2038-01-19 11:14:07
       date       年月日       YYYY-MM-DD           3字节  1000-01-01          ~ 9999-12-31
       time       时分秒       HH:MM:SS             3字节  -838:59:59          ~ 838:59:59
       year       年           YYYY                 1字节  1901                ~ 2155
       ·timestamp时间戳年份是一个范围，存储时是整型，表示时用日期时间性.
       ·datatime,timestamp,date在保存数据时，针对数据格式与合法性进行验证，不对日期时间的合法性进行验证.
       ·年月日时分秒的分隔符可以是任意的标点，但常用的是-和:，不使用也可以.
       ·两位的年也是被允许的，但是表示的范围70-69表示1970-2069.
       ·time类型不仅可以用于表示一天的时间，还可以表示一个时间间隔.因此该类型可以设置为多少个小时，设置是几天几小时几分几秒的情况 D HH:MI:SS
       ·检索时间戳，列加0可以检索时间戳 select c_timestamp + 0
       ·一般php项目中通常保存时间是unix时间戳，直接以整型的形式保存在数据库中，不是mysql提供的timestamp类型.

8.列的属性和约束
    属性
    Reference                 关联操作
    Default value             字段默认值
    Primary key | Unique key  主索引|唯一索引
    Auto_increment            自动增长(默认是1)
    Null|not null             是否允许为空
    Comment                   列注释

    五个约束
    Primary Key  主键约束
    Foreign Key  外键约束
    Unique       唯一性约束
    CHECK        检查约束
    NOT NULL     空值约束
    DEFAULT      默认值约束

    自动增长
    修改自动增长初始值.
    alter table table_name auto_increment n;
    如果n小于已经存在的值，插入表时，会取列的最大值+1.
    自动增长的列可以手动插入值，也可以更改。
    删除最大行后再插入行，自动增长值不会从列的最大值+1开始.

    主键的几种语法
    create table table_name(
      c_1 int primary key auto_increment
    );

    create table table_name(
      c_1 int,
      primary key (c_1)
    );

    create table table_name(
      c_1 int,
      constraint pk_table_name primary key (c_1)
    );

    外键语法
    Foreign Key (外键字段) References 主表名 (关联字段) [主表记录删除时的动作][主表记录更新时的动作]
    create table table_name(
      c_1 int,
      foreign key (c_1) references m_table_name (c1)
    );

    create table table_name(
      c_1 int,
      constraint fk_table_name primary foreign key (c_1) references m_table_name (c1)
    );

    设置级联操作
    主表数据发生改变时，与之关联的从表数据应该如何处理.
    主表更新 on update
    主表删除 on delete
    cascade  关联操作，主表被更新或删除，从表也执行相应的操作
    set null 设置为null，表示从表不指向任何主表记录
    restrict 拒绝主表的相关操作.

    alter table S_LOGIN_INFO add constraint FK_S_LOGIN_INFO_R_S_USERM foreign key (USER_NO)
        references S_USERM (USER_NO) on delete restrict on update restrict;