; MySQL基础知识 | Linux运维部落

MySQL基础知识

1、SQL:结构化查询语言(Structured Query Language):

DDL:DataDefinitionLanguage(数据定义语言):其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE或DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

DML:Data Manipulation Language(数据操作语言) :其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。

 

编程接口

存储过程:procedure

存储函数:function

触发器:trigger

事件调度器:event scheduler

2、三层模型

物理层:决定数据的存储格式,即如何将数据组织成为物理文件;

逻辑层:描述DB存储什么数据,以及数据间存在什么样的关系;

视图层:描述DB中的部分数据

3、MySQL客户端交互式CLI工具:mysql

 

常用选项:

-u, –user=username:用户名,默认为root

-h, –host=hostname:远程主机(即mysql服务器)地址,默认为localhost;客户端连接服务端,服务器会反解客户的IP为主机名,关闭此功能(skip_name_resolve=ON)

-p, –password[=PASSWORD]:USERNAME所表示的用户的密码; 默认为空

注意:mysql的用户账号由两部分组成:‘USERNAME’@’HOST’;其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务

HOST的表示方式,支持使用通配符

%:匹配任意长度的任意字符

172.16.%.%, 172.16.0.0/16

_:匹配任意单个字符

-P, –port=#:mysql服务器监听的端口;默认为3306/tcp

-S, –socket=/PATH/TO/mysql.sock:套按字文件路径

-D, –database=DB_name:连接到服务器端之后,设定其处指明的数据库为默认数据库

-e, –execute=’SQL STATEMENT’:连接至服务器并让其执行此命令后直接返回

4、MySQL数据类型:

 

  • 字符型:

定长字符型:

CHAR(#):不区分字符大小写(0-255字节)

BINARY(#):区分字符大小写

变长字符型:

VARCHAR(#):多占一个或两个字符空间(最后要有一个字符串结束标记)不区分字符大小写(0-65535字节)

VARBINARY(#):区分字符大小写

对象存储:

TEXT:不区分字符大小写;TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT

BLOB:Binary Large OBject,区分字符大小写;TINYBLOB,SMALLBLOB,MEDIUMBLOB,BLOB,LONGBLOB;

  • 数值型:

精确数值型:

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT

近似数值

4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

近似数值型:

FLOAT

DOBULE

  • 日期时间型:
类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/’838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

  • 字符类型修饰符:

NOT NULL:非空约束

NULL:允许为空

DEFAULT value:指定默认值

CHARACTER SET:使用的字符集

 

COLLATION:使用的排序规则

AUTO_INCREMENT:自动增长

  • 整型数据修饰符

NOT NULL:非空约束

DEFAULT NUMBER:指定默认值

PRIMARY KEY:主键

UNIQUE KEY:惟一键

AUTO_INCREMENT:自动增长

 

5、数据定义语言(DDL):

(1)数据库

创建:

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

CREATE {DATABASE 或SCHEMA}后面指明数据库名称

[DEFAULT] CHARACTER SET [=] charset_name

定义默认字符集

[DEFAULT] COLLATE [=] collation_name

定义排序规则

查看支持的所有字符集:SHOW CHARACTER SET

查看支持的所有排序规则:SHOW COLLATION

修改:

ALTER {DATABASE | SCHEMA} [db_name]

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

删除:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看:

SHOW DATABASES LIKE ‘’;

(2)表

创建:

CREATE TABLE [IF NOT EXISTS] [db_name.]tbl_name (create_defination) [table_options]

字段:col_name data_type

添加:ADD [COLUMN] col_name data_type [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]

键:

PRIMARY KEY (col1, col2, …)

UNIQUE KEY (col1, col2,…)

FOREIGN KEY (column)

添加:ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1, col2,…)

删除:

主键:DROP PRIMARY KEY

外键:DROP FOREIGN KEY fk_symbol

 

查看数据库支持的所有存储引擎类型

mysql> SHOW ENGINES;

查看某表的状态信息:

mysql> SHOW TABLES STATUS [LIKE ‘tbl_name’][WHERE clause]

修改:

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] …]

 

 

 

删除:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] …(不推荐使用)

表的引用方式:

tbl_name

db_name.tbl_name

 

(3)索引管理:

索引是特殊的数据结构;

索引:要有索引名称;

创建:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,…)

删除:

DROP INDEX index_name ON tbl_name

查看表上的索引的信息:

mysql> SHOW INDEXES FROM tbl_name;

(4)视图

创建

CREATE VIEW view_name AS select_statement;

删除

DROP VIEW view_name;

注意:视图不能插入数据,受基表(创建视图用到的表)限制,视图的数据事实上存储与”基表“中

6、数据操作语言(DML):

【 INSERT、DELETE、SELECT、UPDATE】

   1.INSERT语句:一次插入一行或多行
 insert into table_name[(column[column…])]values(value[,value…]);默认情况下,一次插入操作只插入一行
 一次性插入多条记录:
  insert into table_name[(column[,column…])]values(value[,value…]),(value[,value…]);
 如果为每列都指定值,则表名后不需要列出插入的列名
 如果不想在表名后列出列名,可以为那些无法指定的值插入NUll
 如果需要插入其他特殊字符,应该采用\转义字符做前缀

 

   2.DELETE语句
 语法:delete [from] table_name [where condition];
 删除不需要指定列名,因为删除总是整行整行删除,where子句是一个条件表达式,只有符合该条件的行才会被删除。没有where子句意味着删除所有数据
 删除可以一次删除多行,删除哪些行采用where子句限定

   3.UPDATE语句
 语法:update table_name set column = value[,column = value]…[where condition];
  修改可以一次修改多行数据,修改的数据可用where子句限定,where子句是一个条件表达式,只有符合该条件的行才会被修改。没有where子句意味着where子句表达式值为true
  也可以同时修改多列,多列的修改中采用逗号隔开

 

   4.SELECT语句
  语法:select_expr [, select_expr ...] [FROM table_references [WHERE where_condition]
  where子句:指明过滤条件以实现选择的功能
     where子句限定返回的记录,where子句在from子句后
     where中的字符串和日期值,字符串和日期要用单引号括起来,字符串是大小写敏感的,日期值是格式敏感的
     比较操作符:>, >=, <, <=, =, <>, BETWEEN min_num AND max_num,
               in(element1,...):匹配所有列出的值
            like:模糊匹配(%:任意值,_:任意单个字符
               IS NULL是空值
     逻辑运算符:ONT, AND, OR, XOR(相同为假,不同为真)
  group by子句
  group by子句的真正作用在于与各种聚合函数配合使用,它用来对查询出来的数据进行分组
  分组的含义是:把该列具有相同值的多条记录当成一组记录处理,最后只输出一条记录
  分组函数忽略空值
  结果集隐式按升序排列,如果需要改变排序方式可以使用order by子句
      count(*|列名)统计行数 avg(数值类型列名)平均值 sum(数值类型列名)求和
   max(列名)最大值      min(列名)最小值
     having子句用来对分组后的结果再进行条件过滤
  在select语句最后可以用limit来限定查询结果返回的起始记录和总数量
  对查询结果中的数据请求施加锁
       for update:写锁,独占锁
       lock in share mode:读锁,共享锁

7、数据库的设计
-当数据库比较复杂时我们需要设计数据库
-良好的数据库设计
-节约数据的存储空间
-能够保证数据的完整性
-方便进行数据库应用系统的开发

☆数据库设计规范化
-仅有好的DBMS并不足以避免数据冗余,必须在数据库的设计中创建好的表结构
-数据库规范化的三个级别,范式是具有最小冗余的表结构,如下:
-第一范式:(1stNF first normal formate)
-第二范式:(2stNF second normal formate)
-第三范式:(3stNF third normal formate)

◆第一范式(1NF)(第一范式必须有主键)
-在关系模式R中的每一个具体关系r中,必须要有主键,并且每个属性都是不可再分的最小数据单位

◆第二范式(2NF)
-如果关系模式R中的所有非主属性都完全依赖于主关键字,则称关系R是属于第二范式

◆第三范式(3NF)
-关系模式R中的非主关键字不能依赖于其他非主关键,即非主关键字之间不能有函数(传递)依赖关系,则称关系R是属于第三范式

8、一个存储系统是否支持事务,测试标准:

ACID

ACID是数据库事务正确执行的四个基本要素的缩写,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。一个支持事务(Transaction)的数据库系统,必需要具有ACID特性,否则在事务过程(Transaction processing)当中无法保证数据的正确性,交易过程极可能达不到交易方的要求。

A:原子性

整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。

C:一致性

一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。

也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性

I:隔离性

隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据

D:持久性

在事务完成以后,该事务对数据库所作的更改持久的保存在数据库之中,并不会被回滚。

由于一项操作通常会包含许多子操作,而这些子操作可能会因为硬件的损坏或其他因素产生问题,要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕,但实际上并不可行。 目前主要有两种方式实现ACID:第一种是Write ahead logging,也就是日志式的方式(现代数据库均基于这种方式)。第二种是Shadow paging。

9、编译安装mariadb-5.5.44

# yum install cmake
# yum groupinstall "Development Tools" "Server Platform Development"
# groupadd -r -g 306 mysql
# useradd -r -g 306 mysql -u 306 mysql
# tar xf mariadb-5.5.44.tar.gz
# cd maeradb-5.5.44
# cmake
DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.44\    安装路径
DMYSQL_DATADIR=/mydata/data\                        数据目录
DSYSCONFDIR=/etc                                    指定安装文件的安装路径
DMYSQL_UNIX_ADDR=/tmp/mysqld.sock\                  sock文件
DDEFAULT_CHARSET=utf8\                              默认字符集
DDEFAULT_COLLATION=utf8_general_ci\                 默认排序规则
DWITH_INNOBASE_STORAGE_ENGINE=1\                    默认编译的存储引擎
DWITH_ARCHIVE_STORAGE_ENGINE=1\
DWITH_BLACKHOLE_STORAGE_ENGINE=1\
DWITHOUT_READLINE=1\
DWITH_ZLIB=system\
DWITH_SSL=system\
DWITH_LIBWRAP=0\
# make
# make install
# cd /usr/local
# ln -sv mariadb-5.5.44 mysql
# chmod -R root.mysql ./*
# ll
# scrips /mysql_install_db --user=mysql --datadir=/mydata/data 
# cp support-file/my-large.cnf /etc/mysql/my.cnf
# vim /etc/mysql/my.cnf
     datadir=/mydata/data
     innodb_file_per_table = ON
     skip_name_resolve = ON
# cp support-file/mysql.server /etc/rc.d/init.d/mysqld
# chmod +x /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# service mysqld start
# /usr/local/mysql/bin/mysql_secure_installation     给所有用户设定密码,删除匿名用户
# /usr/local/mysql/bin/mysql





 

 

原创文章,作者:nene,如若转载,请注明出处:/88757

发表评论

电子邮件地址不会被公开。 必填项已用*标注

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1660809109@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班

友情链接:万达开户  万达娱乐主管  万达娱乐登录  万达招商QQ  万达招商  万达娱乐主管QQ  万达娱乐  万达娱乐注册  华宇招商  guoqibee.com