数据库的基本语法和约束条件

1,数据库的基本语句.

1.1>数据库的增删改查.

查看所有的库:

show databases;

就是已经创建好的库,通过这个命令去查看;

information_schema:虚拟库,不占用磁盘空间,存储的是数据库启动后的一些参数,如用户表信息,列信息,权限信息,字符信息等.

performance_schema:mysql5.5开始新增一个数据库:主要用于收集数据库服务器的性能参数,记录处理查询请求时发生的各种事件,锁等现象.

mysql:授权库,主要用于存储系统用户的权限信息

text:mysql数据库系统自动创建的测试数据库

1.2>创建数据库

1.2.1>求救语法:(在mysql语句中没写完一句话就要用;隔开)

help create databases;

1.2.2>创建数据库语法

CREATE DATABASES 数据库 charset utf-8

因为在之前设置好了编码格式,所以不用每次都编码了.

1.2.3>数据库的命名规则:

可以由字母,数字,下划线,@,#,$组成.
区分大小写
唯一性
不能使用关键字如:create select
不能单独使用数字
最长128位
#和python和js命名规则一样

1.3>数据库的相关操作.

#查看数据库
show database;
#查看当前库
show create databases db1;  #查看当前新创建的数据库
#查看所在的库
select databases();
#选择数据库
use 数据库名

#删除数据库
DROP DATABASES 数据库名;
#修改数据库
alert databases db1 charset utf-8

###以上这些操作不区分大小写,对于已经建好的数据库mysql没有重命名的指令,只能1>通过删除就得数据库,并把数据迁移到新创建的数据库中,2>取文件夹中找到那个数据库名右键重命名###

2,表的操作

数据库中的表有不同的类型,标的类型不同,会对应mysql不同的存储机制,表类型又称为存储引擎.

SQL解析器,SQL优化器,缓存池,储存引擎等组件在每个数据库中都存在,但不是每个数据库都有这么多引擎,MySQL的插件式储存可以让存储引擎层的开发人员涉及他们需要的存储层.

2.1>mysql支持的储存引擎

show enginesG;      #查看所有支持的引擎
show variables like "storage_engine$";      #查看正在使用的存储引擎

2.1>InnoDB存储引擎

支持事物,其设计目标主要向联机事物处理的应用,其特点是行锁设计,支持外键,并支持类似Oracle的非锁定读,即默认读取操作不会产生锁,从MySQL5.5.8版本开始是默认的存储引擎

InnoDB存储引擎将数据放在一个逻辑表空间中,这个表空间黑盒一样由InnoDB存储引擎的,表单独放到一个独立的ibd文件中,此外,InnoDB存储引擎支持将将裸设备(row disk)用于建立其表空间

InnoDB通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4中隔离级别,默认为REPEATABLE级别,同时使用一种称为netx-key locking的策略来避免幻读现象的产生除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。

对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID,并以此作为主键。

InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

2.2>MyISAM 存储引擎

不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与大多数的数据库都不相同.

2.3>NDB 存储引擎

年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

2.4>Memory 存储引擎

正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希索引,而不是通常熟悉的 B+ 树索引。

2.5>Infobright 存储引擎

第三方的存储引擎。其特点是存储是按照列而非行的,因此非常适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据仓库案例可供分析

2.6>NTSE 存储引擎

网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

2.7>BLACKHOLE

黑洞存储引擎，可以应用于主备复制中的分发主库。

MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅力所在.

2.8>指定表类型/存储引擎

create table t1(id int)engine = innoDB;   
#默认不写是innoDB

2.9>查看data文件下数据库的文件:

#frm是存储数据表的框架结构 
#.1bd是mysql数据文件 
#MYD是MyISAM表的数据文件的拓展名 
#.MYI是MyISAM表的索引的扩展名 
#发现后两种存储引擎只有表结构,无数据 
#memory,在重启mysql或者重启机器后,表内数据清空 
#blackhole,往表内插入任何数据,都相当于丢入黑洞,表内用原不存记录

2.10>表介绍:

表想相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为标的字段.

2.9>创建表

语法:

create table 表名(
字段名1 类型[(宽度)  约束条件],
字段名2 类型[(宽度)  约束条件],
字段名3 类型[(宽度)  约束条件]
);

#注意
1,在同一张表中,字段名不能相同
2,宽度和约束条件可选
3,字段名和类型是必须的

2.9.1>创建数据库

create databases db2;

2.9.3>使用数据库

use db2;

2.9.4>创建表

create table  a1 (
     id int,           
     name varchar(50),
     
     age int(3)
);

2.9.5>插入表的记录

insert into a1  values
(1,"雪雪",29),
(2,"小雪",25),
(3,"雪儿",23);

以;作为mysql的结束语

2.9.6>查询数据表的数据和结构

1)查询a1表中的存存储数据:

2)查看a1表的结构

3)查看表的详细结构

2.9.7>复制表

1)创建一个新的数据库db4

2)使用db4

mysql> use db4;
Database changed

当进入到db4数据库事物,如果想要查看数据库db1中的t5表,则需如下操作.(和导入模块一样)

3)复制db1.t5的表的结构和记录

mysql> create table b1 select * from db1.t5;
Query OK, 3 rows affected (2.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
#这是复制标的操作(即复制了表的结构,又复制了表的数据)

4)查看db4.b1中的数据和表结构

ps1:如果只要表结构,不要表中的数据

#在db2数据库下新创建一个b2表，给一个where条件，条件要求不成立，条件为false，只拷贝表结构

查看表结构:

查看表中的数据,会发现是空数据

ps2:使用like(只拷贝表结构,不拷贝记录)

命令操作如下:

查看数据结构;

查看b3表的数据;

3,表的数据结构

储存引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但狂赌是可选的,详细参考:http://www.runoob.com/mysql/mysql-data-types.html

mysql常用数据类型概括:

#1,数字
    类型:tinyint   int   bigint
    小数:
        float:在位数比较短的情况下不精确
        double:在位数比较长的情况下比较不精确
        decimal:(如果用小数,则用推荐使用decimal)
            精准,   内部原理是以字符串的形式去存
#2,字符串:
    char(10):简单粗暴,浪费空间,存取速度快
                    root存成root000000
    varchar:精准,节省空间,存取速度慢
    sql优化:创建表时,当场的类型往前放,边长的往后放
                比如性别         比如地址或者描述信息(为以后表与表之间的管联做准备)
    >255个字符,超了就把文件路径存放到数据库中.
            比如图片,视频找到一个文件服务器,数据库中只存放路径或url
#3,时间类型:
        最常用:datetime
#4,枚举类型与几何类型

    enum和set

3.1>数值类型

类型:tinyint,smallint,mediumint int bigint

作用:存储年龄,等级,id各种和数字有关联的数据.

1,tinyint[(m)] [unsigned]  [zerofill]
    小整型,数据类型用于保存一些范围的整数数值范围;
    有符号:
            -128 ~127
    无符号:
            0~255
    ps:MySQL中无布尔值,使用tinyint(1)表示true,用tinyint(0)表示true.
2,int[(m)] [unsigned]  [zerofill]
    整数,数据类型用于保存一些范围的整数数值范围:
    有负号:-2147483648 ～ 2147483647
    
    无负号: 0 ～ 4294967295
3,bigint [(m)] [unsigned] [zerofill]
    大整数,数据类型用于保存一些范围的整数数值范围:
    有负号:-9223372036854775808 ～ 9223372036854775807
    无负号: 0  ～  18446744073709551615

有负号和无负号tinyint

#创建s1表,并规定x字段为tinyint数据类型(默认是有负号的)

#验证插入-1这个数

#查询表(默认是有负号的)

#插入的反为的边界是可以插入的

证明后面的类型是显示宽度,而不是存储宽度

###小结:一般在数字不用在int后边加类型,使用默认值就够了,当指定了宽度即使超过了宽度也能全部显示出来,但是不够指定的宽度,也是可以显示出来的,只不过前边几位没有用数字填充,用0填充也是可以的###

3.2>浮点型:

定点数类型:DEC等同于DECIMAL

浮点类型:FLOAT DOUBLE

作用:存储薪资,身高,体重,体质参数等.

1,float[(M,D)] [unsigned] [zerofill]
#参数解释:单精度浮点数(非准确小数值),M是全长,D是小数点后个数.M最大值为255,D最大值为30.
#有负号:
             -3.402823466E+38 to -1.175494351E-38,
           1.175494351E-38 to 3.402823466E+38
有负号:
            1.175494351E-38 to 3.402823466E+38
精确度:
    
            **** 随着小数的增多，精度变得不准确 ****
2,double

double[(M,D)] [unsigned]  [zerofill]

#参数解释:双精度浮点数(费准确小数值), M是全长,D是小数点后个数,M最大值是255,D最大值为30

有负号:

        -1.7976931348623157E+308 to -2.2250738585072014E-308
           2.2250738585072014E-308 to 1.7976931348623157E+308
#无负号:
            2.2250738585072014E-308 to 1.7976931348623157E+308
#精确度:
            ****随着小数的增多，精度比float要高，但也会变得不准确 ****
3,decimal
decimal[((m,d)] [unsigned]  [zerofill]
#参数解释:经过却的小数值,m是整数部分总个数(负号不算),d是畅销书点后个数,m最大值为65,d最大值为30.

#精确度:

            **** 随着小数的增多，精度始终准确 ****
           对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储。

由此可以看出随着小数的增多,精度同样都变得不精确,但是decimal的精度最高.

3.3>日期类型:

date time datetime timestamp year

作用:存储用户注册时间,文章发布时间,员工入职时间,过期时间等.

语法:
    year:
            YYYY(1901/2155)
    date:
            YYYY-MM-DD(1000-01-01/9999-12-31)

    time:
            HH:MM:SS("-838:59:59"/"838:59:59")

    datetime:

            YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59  Y)

    timestamp

            YYYYMMDD HHMMSS(1970-01-01 00:00:00/获取到的是年某时)

3.3.1>:

在year中无论指定何种宽度都是4位,年份的范围是(1901/2155)这两个临界点也包括,在这个范围插入数据才生效.

3.3.2>date year datetime

在mysql中有一个自带的now()函数,获取的是当前类型的时间(时间戳时间)

3.3.3>timestamp(时间戳)

###当插入timestamp的值为空或者为null,或者到用内置函数now()时都是获得当前时间

4,字符类型

官网:https://dev.mysql.com/doc/refman/5.7/en/char.html

注意:char和varchar括号内的参数指的都是字符的长度.

char类型:定长,简单粗暴,浪费空间,存储速度快

　　字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
    存储：
        存储char类型的值时，会往右填充空格来满足长度
        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

　　检索:在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（设置SQL模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
　　　　　　查询sql的默认模式：select @@sql_mode;）

varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）

　　存储：
        varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

    检索：
        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

官网:

查看:length();查看字节数; char_length();查看字符数;

当我们创建好表和数据的时候,查看char字符数的时候,系统会自动的将多余的空格删掉,因此像图一显示查到的字符数是汉子的数目,而varchar不会出现这样的情况.

如果想查看char的实际字符数,需要修改一下mode:set sql_mode = "pad_char_full_length";

查看字节数:

###char类型:3个中文字符+2个空格 = bytes

###varchar类型:3个中文字符+1个空格=10bytes.

5,没举型和集合型;

字段的值只能在给定范围中选择,如单选框,复选框

enum单选 ,只能在给定的范围内选一个值,如性别sex男male/女female

set多选在给定的范围内可以选择一个或一个以上的值

enum:

　　create table consumer(

　　　　　　　　id int unsigend,

　　　　　　　　name char(20),

　　　　　　　　sex enum("male","female")not null default "male",

　　　　　　　　fav set("抽烟","喝酒","烫头")

　　);

　　insert into consumer(id,name,sex,fav) values(1,"alex","female","抽烟","烫头");

　　insert into consumer(id,name,sex) values(2,"alex2","female");

6,表的约束:

约束是在创建表的时候对表以后插入的数据进行约束,从而来保证数据的一致性与完整性.

1>not null不为空

2>default设置默认值

3>unique唯一性

4>primary key,表示:唯一且不为空,主键在sql中只允许表中有一个主键,通常主键是id　

5>auto-increment表示自增长

6>foreign key:是建立多个表中的映射关系的.

7>undigned:表示无符号

8>zerofill:表示使用0填充

1,2.1>not null表示不为空,null表示为空,非字符串

create table tb1(
             nid int not null default 2,               #表示插入的这个数字不为空,且不给值的情况下默认值为2.         
             num int not null               #表示插入的第二个数字不能为空

);
insert into tb1(num) values(3);

1,)当有默认值字段的时候在插入数据表后面当不指明数据变量的时候,必须插入相应的数据,默认数据对应的数据类型不可空缺,当指明表姐够的数据变量的时候就好了指谁就插入谁.

3.1>unique:不同的唯一的

#单列唯一(给某一行设置它的唯一性)
create table  department(
          id int,
          name char(10) unique   
)


insert into department values(1,"雪雪"),(2,"小雪");

当插入的name字段的值相同的时候会包错:

3.2>多列唯一:每个字段都设置unique

#每个字段都设置unique
create table department2(
                in int unique,
                name char(10) unique
);      

#或者

create table department2(
                id int,
    
                name char(10),
                unique(id),

                 unique(name)
); 

#在最后设置字段的约束条件.必须每个都满足条件才可以插入数据

insert into department2 values(1,"it1"),(2"it2");

3.3>组合唯一:

create table department3(
              id int, 
              name char(10),
              unique(id,name)
);

insert into department3 values(1."it"),(2,"it");

#只要有一个字段不一样,都可以插入只要有一个数据不一样就可以插入.