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");
#只要有一个字段不一样,都可以插入只要有一个数据不一样就可以插入.
4.1>primary key主键(一个列表中可以单列做主键,也可以多列做主键,约束等同于not null unique),字段的值不为空且唯一,存储引擎默认是(innoDB):对于innoDB存储引擎来说,一张表必须有一个主键.
化学反应:not null + unique === primary key
5.1>auto_increment:自增长(默认从1开始)
结论:可以指定id值去插入数据,注意插入的数据id必须是增长的,中间可以有间隔,但必须是增长的,但在后边在不指定插入数据的时候会在默认最后一个id值追加一个id值,(即使前边有没有用的id值)
5.2>设置自增长的值
5.2.1>设置步长为会话设置,只在本次连接中有效,当关闭服务器,或者退出管理员系统这个设置就会消失
set session auto_increment_increment=5;
全局设置步长都有效.
set gloable auto_increment_increment=5;
设置起始偏移量
set gloable auto_increment_offset=3;
注意:如果设置的偏移量大于步长的值,则偏移量的值会被忽略.
设置完之后exit,待系统识别以后,再登陆才会有效.
6,1>foreign key是在靓装关联表建立关系
建立关系:constraint fk_id foreign key(dep_id) references dep(id)
on delete cascade on update cascade
结论:1>当2张表建立关系以后,删除主表 的时候 ,从表因为关联,也会删除相关联的数据,删除从表的受任何数据,主表不会受到任何的影响 ,
2>在建立标的时候一定要先建立主表,再建立从表,在插入数据的时候也是先插入主表的数据,再插入从表的数据(都是血的教训)
补充:清空区分delete和truncate:
delete from t1; # 如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始
truncate table t1; # 数据量大,删除速度比上一条快,且直接先从0开始.