MySQL基础语法

第一节、数据类型

a、整型

create table test(
    a    int(4), -- 限制a为四位，如果大于4位，则全部显示
    b    int unsinged,    -- 无符号
    c    int unsinged zerofill    --   无符号且前面补0
);

b、浮点数和定点数

        如果插入的值大于定义的类型系统会自动四舍五入，float、double不会抛出警报。但是decimal会抛出警报。decimal不指定精度，默认保存为整数。

create table test(
    a    float,
    b    double,    
    c    decimal(6,2)    -- 数据长度为6，保留两位小数
);

c、日期和时间

year赋值方式：

1、使用4位字符串或数字。范围1901到2155。输入格式为‘YYYY’或YYYY。如：‘2008’或2008。如果超出该范围，插入‘0000’

    如： insert into test(a, b) values(1990, '1990'); -- 插入数字0转为0000，插入‘00’转换为‘2000’

2、使用两个字符串表示，‘00’到‘69’——> 2000到2069， ‘70’到‘99’——>1970到1999

    如： insert into test(a, b) values('23', '78'); ——> 2023、1978

time赋值方式：

1、以HH:MM:SS显示time类型的值。范围：-838：59：59——>838:59:59

    如：'D HH:MM:SS'其中D表示天数，取值范围是0-34。保存时用D*24 + HH表示小时

2、HHMMSS或current_time或now()

date赋值方式：

1、YYYY-MM-DD 范围 '1000-01-01'——>‘9999-12-31’

2、YY-MM-DD或YYYYMMDD

datetime赋值方式：

1、‘YYYY-MM-DD HH:MM:SS’

2、‘YY-MM-DD HH:MM:SS’

3、‘YYMMDDHHMMSS’或 now()

timestamp赋值方式：

1、范围1970-01-01 08：00：01到2038-01-19 11：14：07

2、current_timestamp如果输入为null或空，默认为当前时间

mysql广告位

d、字符串

空格处理方式：char去掉空格、varchar保留空格

text还包括其他类型：

tinytext(0~255)、text(0~65535) 值长度+2、mediumtext(0~167772150) 值长度+3、longtext(0~4294967295) 值长度+4个字节

enum类型可以包含65535个值。每次只能选择一个

set类型可以包含64，可以选择多个

e、二进制

第二节、数据库操作

1、create database 数据库名;

2、drop database 数据库名;

3、show databses; --  显示MySQL中有哪些数据库

4、show engines; -- 显示MySQL中所有支持的数据库引擎

5、show variables like 'have%';

6、show variables like 'storage_engine'; --显示默认引擎

7、常见数据库引擎

    a、InnoDB

        优点：

            1> 提供事物、回滚、崩溃修复能力、多版本并发控制的事物安全。

            2> 支持auto_increment自动增长列；

            3> 外键(foreign key)；

            4> 创建的表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中

        确定： 读写数据效率较差、占用空间较大

    b、MyISAM

        优点： 

            1> 表存储为三个文件。文件名与表明相同。扩展名包括.frm、MYD、MYI。其中，frm为扩展名的文件存储表的结构；MYD（MYData）为扩展名的文件存储数据。MYI（MYIndex）为扩展名的文件存储索引； 

            2> 占用空间小

        缺点：不支持事物的完整性和并发性

    c、MEMORY

修改默认引擎可以修改my.ini文件中的【default-storage-engine=INNODB】字段

第三节、数据库表操作

a、创建表

语法：

create table 表名称(

    属性  数据类型 [约束]

);

如：创建一个学生信息表

create table t_student(
    id            int not null primary key auto_increment,
    name     varchar(100),
    sex         enum('male','famale') default 'male'
);

b、完整性约束

1> primary key

create table test(
    id int not null primary key -- 设置单值主键
);
create table test(
    id int not null,
    primary key(id) -- 设置多值主键（多个值以逗号分割）
);

2> foreign key

create table test(
    id int not null primary key,
    f_id int not null,
    constraint f_id_key foreign key(f_id) references test2(id) -- 其中f_id_key为外键别名、多个外键用逗号分割
);

3> not null 见primary key中的id属性

4> unique

create table test(
    name varchar(100) unique -- 该name属性不能插入重复的值
);

5> auto_increment 见primary key中的id属性

6> defualt

create table test(
    sex enum('男','女') default '男'     --设置sex属性只能取值'男'或'女'。默认为'男'
);

c、修改表

    修改表用“alter table”语句。包括：修改表名、修改字段类型、修改字段名称、修改字段名称、增加字段、删除字段、修改字段顺序、更改默认存储引擎和删除外键约束。

1> 修改表名

alter table 旧表名 rename [to] 新表名

2> 修改字段的数据类型

alter table 表名 modify 属性名 数据类型

3> 修改字段名称

alter table 表名 change 旧属性名 新属性名 新数据类型

4> 添加字段

alter table 表名 add 属性 数据类型 [完整性约束] [first | after 属性]

如：alter table test add name varchar(100) after id; -- 将name添加到id属性的后面

       alter table test add name varchar(100) first; -- 将name添加到表首

5> 删除字段

alter table 表名 drop 属性名

6> 修改字段的顺序

alter table 表名 modify 属性名 数据类型 first | after 参照属性

7> 改变表的默认引擎

alter table 表名 engine=存储引擎名称

如：alter table test engine=MyISAM;    -- 将test表的默认存储引擎改为MyISAM

8> 删除外键关系

alter table 表名 drop foreign key 外键别名

9> 删除表（同时删除表中的数据和表结构）

drop table 表名 -- 如果其他表引用了该表主键作为外键，则需要将其他表中引用该表的外键删除

注意：

    属性被改变名称后且没有加上原来的约束条件，则可能会丢失部分约束条件。

    创建外键必须创建父表且外键是父表的主键或是复合主键的部分。

    自动增长字段不能设置默认值。

第四节、索引管理

        索引是一种特殊的数据库结构，可以用来快速查询数据表中的特定记录。MySQL中所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和控件索引。

        索引是创建在表上的，是对数据库中一列或多列进行排序的一种结构，索引可以提高数据查询速度。

        a> 普通索引（在创建索引时，不附加任何限制条件。这类索引可以创建在任何数据类型上，其值是否唯一、是否为空由字段本身的完整性约束条件决定）

        b> 唯一性索引（使用unique参数可以设置索引为唯一性索引。在创建时限制该值必须是唯一的。主键就是一种特殊的唯一性索引）

        c> 全文索引（使用fulltext参数设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上面。查询数据量较大时，使用全文索引可以提高查询效率）

        d> 单列索引（在表的单个字段上创建索引。单例索引只根据该字段进行索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证只有一列）

        e> 多列索引（在表的多个字段上面创建的索引。该索引指向创建时的多个字段。可以通过这几个字段进行查询。但是，只有查询条件中包含索引字段的第一个字段时，索引才被使用）

        f> 空间索引（使用spatial参数来设置索引为控件索引。空间索引只能创建到空间数据类型上，这样可以提高数据系统获取数据的效率。空间包括geometry、point、linestring、polygon等。目前MySQL只有MyISAM存储引擎支持空间索引）

索引设计原则：

    1> 选择唯一性索引

    2> 为经常需要排序、分组和联合操作的字段建立索引

    3> 为经常作为查询条件的字段建立索引

    4> 限制索引数目

    5> 尽量使用数据量少的索引

    6> 尽量使用前缀来索引

    7> 删除不再使用或不经常使用的索引

创建索引的方法有三种：在创建表时创建、在已有的表上创建和使用alter table创建

1、在创建表时创建

创建普通索引：

create table test (
    id int,
    name varchar(20),
    index(id)
);

创建唯一性索引：

create table test(
    id int unique,
    name varchar(20),
    unique index(id desc) -- 唯一性索引且降序排列
);

创建全文索引：

create table test(
    id int,
    name varchar(20），
    fulltext index index_name(id) -- 创建一个名为index_name的全文索引
);

创建单列索引：

create table test(
    id int,
    subject varchar(30),
    index index_name(subject(10)) -- 创建一个单列索引（且是前缀索引，则对subject前10个字符进行索引）
);

创建多列索引：

create table test（
     id int,
    name varchar(20),
    sex varchar(10),
    index index_name(name, sex)
）；

创建控件索引：

create table test(
    id int,
    space geometry,
    spatial index index_name(space)
) engine=MyISAM;

2、在已存在的表上创建索引

create [unique | fulltext | spatial] index 索引名 on 表名(属性名[长度] [ASC | DESC])

如：

create index index_name on test_table(name(20) desc);

3、用alter table语句来创建索引

alter table 表名 add [unique | fulltext | spatial] index 索引名(属性名[（长度）] ASC | DESC)

删除索引

drop index 索引名 on 表名