MySQL(三)的体系结构

Mysql的常见存储引擎

什么是存储引擎?

引擎是Mysql自带的功能程序,每种存储引擎的功能和数据存储方式也不同。存储引擎就是处理表的处理器。
MyISAM存储引擎:不支持事务、也不支持外键,优势是访问速度快,对事务完整性没有 要求或者以select,insert为主的应用基本上可以用这个引擎来创建表
支持3种不同的存储格式,分别是:静态表;动态表;压缩表

存储引擎 相关的介绍
静态表 中的字段都是非变长字段,这样每个记录都是固定长度的,优点存储非常迅速,容易缓存,出 现故障容易恢复;缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格)ps:在取数据的时候,默认会把字段后面的空格去掉,如果不注意会把数据本身带的空格也会忽略。
动态表 记录不是固定长度的,这样存储的优点是占用的空间相对较少;缺点:频繁的更新、删除数据容易产生碎片,需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能
压缩表 因为每个记录是被单独压缩的,所以只有非常小的访问开支

数据库服务支持的存储引擎有那些?

show engines;       #查看当前所有的存储引擎
innodb —> default 默认的引擎

  • ** innodb特点**
    支持 --> 事务、事务回滚、行级锁、外键
    存储方式:一个表用两个文件存储
    
  • Myisam特点
    支持 --> 表级锁
    存储方式:一个表用三个文件存储
    
  • 两者的区别

    innodb

表名.frm 表名.ibd
表结构 数据和索引

Myisam

表名.frm 表名.MYD 表名.MYI
表结构 数据 索引
  • 事务:对数据库服务访问的过程(连接数据库、操作数据、断开连接)

  • 事务的回滚:在事务执行的过程中,任何一步的失败操作都会恢复之前的所有操作。要么全部成功要么全部是失败

一次事务的回滚示例:

插卡(与数据库服务器建立连接) 
-> 转账(对方卡号以及金额) 
-> ok (向数据库发送写命令)
-> 提示转账成功(自己账户-500)(对方账户+500)
-> 提示失败 (自己账户+500)(对方账户-500)
-> 退卡
mysql数据库服务使用事务日志文件记录对Innodb存储引擎执行的sql操作

什么是锁?

表级别锁(myisam)给整张表加锁
行级别锁(innodb)只给表中当前被操作行加锁
锁的作用:解决对表的并发访问冲突问题

select
insert
delete from t1
update t1 set name='bob' where name='lucy';

锁的类型:读锁 写锁 共享锁 互斥锁

当我们建立表的时候如何决定使用什么类型的存储引擎?

  1. 执行写操作多的表适合使用innodb存储引擎,可以并发访问
  2. 执行查操作多的表是和使用myisam存储引擎,可以解释系统资源

数据导入导出(批量操作数据)

数据导入:把系统文件的内容存储到数据库的服务器的表中

示例:

把系统已有的/etc/passwd用户的信息保存到db3下的user表中
创建存储数据库表
create table db3.usertab(
    username char(50),
    password char(1),
    uid int(2),
    gid int(2),
    comment char(100),
    homedir char(100),
    shell char(5),
    index(username)
    );

查看导入文件内容时候,默认在系统的什么目录下搜索文件?
cp /etc/passwd /var/lib/mysql-files/
mysql>show variables like 'secure_file_priv';       #显示当前的可导入的默认目录
    +------------------+-----------------------+
    | Variable_name    | Value                 |
    +------------------+-----------------------+
    | secure_file_priv | /var/lib/mysql-files/ |
    +------------------+-----------------------+
mysql>load data infile "/var/lib/mysql-files/passwd" into table db3.usertab fields terminated by ':' lines terminated by '\n';      #将本地的表导入数据库中
mysql>alter table db3.usertab add id  int(2) primary key auto_increment first;      #为表加入ID行且自动增长

修改导入文件内容时候,默认在系统的哪一个目录下搜文件?

vim /etc/my.cnf
        [mysqld]
        secure_file_priv="/mysqldata"
mkdir -p /mysqldata
chown mysql /mysqldata
重起服务
mysql>show variables like 'secure_file_priv';

数据导出的命令格式及数据导出时候的注意事项

数据导出:把表记录存储在系统文件中
命令格式:
sql查询命令 into outfile "/目录/文件名" [fields terminated by"列间隔符号" lines terminated by '\n'];
mysql>select id,username,uid from db3.usertab into outfile "/mysqldata/user1.txt" ;     #将需要的行导出数据库
mysql>select * from 库名.表名 into outfile "导出的路径" fields terminated by '分隔符';  #将默认导出的分割符号TAB变成你想要的

管理表记录

1. 插入记录

插入记录: insert into (数值与字段类型和约束条件匹配)

(1)插入1条记录给所有字段赋值
    insert into 库.表 values(字段值列表);
        mysql> insert into db3.usertab
                -> values
                -> (43,'jb','x',1001,1001,'','/home/jb','/bin/bash');

(2)插入N条记录值给所有的字段赋值
    insert.into 库.表 values(字段列表值),(。。。)
                    mysql> insert into db3.usertab
                            -> values
                            -> (43,'jb','x',1001,1001,'','/home/jb','/bin/bash');
                            -> (44,'jbx','x',1002,1002,'','/home/jbx','/bin/bash');

(3)插入1条指定的记录给指定的字段
    insert into 库.表 (指定的字段) values(指定的数值)
        mysql>insert into db3.usertab
        (id,username,homedir,shell)values
        (44,'ccy','/home/ccy','/sbin/nogin');

(4)插入N条指定的记录给指定的字段
    insert into 库.表 (指定的字段) values(指定的数值列),(指定的数值列),(。。。。)
        mysql>insert into db3.usertab(id,username,homedir,shell)values
                ->(44,'ccy','/home/ccy','/sbin/nogin');
                ->(45,'ccyx','/home/ccyx','/sbin/nogin');

2. 查看记录

(1)select 字段名列表 from 库.表
    select 字段名列表 from 库.表 【where 匹配条件】;

(2)查看表中所有行的所有字段的数值
    select * from 库.表;

(3)查看表中指定行的数值
    select 字段名1,字段名2 from 库.表;

(4)查看表中指定行的中匹配条件的数值
    select 字段名1,字段名2 from 库.表 where 匹配条件;

3. 更新记录

update 库.表 set 字段名=值 where 匹配条件;        #如果没有where条件很危险会批量修改

4. 删除记录

(1)删除表中的所有行;
delete from 库.表

(2)仅仅删除于条件匹配的记录:
delete from db3.usertab where id=45;

匹配条件(查看、修改、删除时候的条件)

1.数值比较

字段名字        符号(= != < <= > >=)      数字
mysql> select id,username from db3.usertab where uid>10 and uid<50 and shell="/bin/bash";
mysql> select id,username from db3.usertab where uid>10 and uid<50 and shell="/sbin/nologin";

2.字符比较

字段名字        符号          “字符串”
mysql> select username from db3.usertab where username='apache';

3.范围内比较

字段名字        between     数字1         and         数字2
mysql> select username from db3.usertab where uid between 100 and 150;
mysql> select username from db3.usertab where uid in(10,20,60,70);
mysql> select username from db3.usertab where username not in("mysql","sync","root");

4.逻辑比较

逻辑与and多个条件同时成立才匹配
逻辑或or多个条件,某一个匹配就成立
逻辑非 !、not、取反
匹配空 字段名 is null
匹配空 字段名 is not null
select username,git,uid from db3.usertab where git is null and uid is null;

5.模糊匹配

字段名 like    "表达式";
(1)--> %    表示零个或多个字符
(2)--> _    表示任意一个字符
示例:
    mysql> select username from db3.usertab where username like "_____";    #匹配名字4个的人
            +----------+
            | username |
            +----------+
            | avahi    |
            | games    |
            | mysql    |
            | radvd    |
            | rtkit    |
            +----------+
    mysql> insert into db3.usertab(username)values('a');
    mysql> select * from db3.usertab where username like 'a%';

6.正则匹配

字段名 regexp  "正则表达式"
^   $   .   *   [   ]
示例:
mysql> insert into db3.usertab(username) values("a1asd2"),("12aad"),("asdsad21");       #添加字段
mysql> select username from db3.usertab where username regexp "[0-9]";
        +----------+
        | username |
        +----------+
        | 12aad    |
        | a1asd2   |
        | asdsad21 |
        +----------+

7.四则匹配

+   -   *   /   %   (字段类型必须是数值类型,整形浮点都可以)
mysql> alter table db3.usertab add age tinyint(2) unsigned default 21;      #添加一行新的并设置默认值
mysql> select username,uid,git as zh,uid+git/2 as ojz from db3.usertab where username='mysql';
    +----------+------+------+---------+
    | username | uid  | zh   | ojz     |
    +----------+------+------+---------+
    | mysql    |   27 |   27 | 40.5000 |
    +----------+------+------+---------+

8.聚合函数(对字段的值做统计,字段的类型要求是数值类型)

函数:执行特定功能的代码块
算数运算函数
        (1)sum(字段名) 求和
            mysql> select sum(price) as "图书总价" from books;
        (2)avg(字段名) 平均值
            mysql> select sum(price) as "图书总价" from books;
        (3)max(字段名) 最大值
            mysql> select bname,price from books where price=(select max(price) from books);
            mysql> select bname,price from books order by price desc limit 0,1;
        (4)min(字段名) 最小值
            mysql> select bname,price from books where price=(select min(price) from books);
            mysql> select bname,price from books order by price limit 0,1;
        (5)count(字段名)   统计记录数
            mysql> select count(*) from books where price>40;
        (6)如果想去除有重复的:
            smysql> elect count(distinct price) from books where price>40;

算数运算
    例如:给所有的小于40元的书籍,涨价10000
        mysql> updata books set price=price+10000 where price<40;

字符串函数
        mysql> substr(string,start,len) 截取:从start开始,截取len长start 从1开始算起
        mysql> select substr(btypename,8,2) from category where btypeid=10;
        mysql> concat(str1,str2,str3..) 拼接:把多个字段拼成一个字段输出
        mysql> select concat(bname,publishing) from books;

大小写转换
        upper()大写输出
        mysql> select upper(bname) from books where price>80;
        lower()小写输出
        mysql> select lower(bname) from books where price>80;

日期
        mysql> curedata()
        mysql> curetime()
        mysql> now()

9.查询不显示字段重复值

select  distinict   字段名 from    库.表

10.查询分组

sql查询显示行数   group   by  字段名

11.查询排序

mysql查询排序
    esc  升序
    desc 降序
语法:select distinct 显示的【字段1,字段2】 from 表名 order by 排序条件{字段名} 【升/降】;
mysql> select username,uid from db3.usertab where uid>10 and uid<=100 order by uid desc;
mysql> select username,uid from db3.usertab where uid>10 and uid<=100 order by uid asc;

12.限制查询显示行数目

sql查询 limit 数字; 显示查询结果的前几行
sql查询 limit 数字1,数字2; 显示查询结果指定范围的行
select username,uid from db3.usertab where uid>10 and uid<=100 order by uid desc limit 2,3;

MySQL(三)的体系结构》有26个想法

  1. Thanks for the marvelous posting! I certainly enjoyed reading it, you can be a great author.
    I will remember to bookmark your blog and may come back from now on.
    I want to encourage you to ultimately continue your great writing,
    have a nice holiday weekend!

  2. Have you ever considered creating an ebook or guest authoring on other sites? I have a blog based upon on the same information you discuss and would really like to have you share some stories/information. I know my audience would value your work. If you are even remotely interested, feel free to send me an e mail.|

  3. amfvke,A fascinating discussion is definitely worth comment. I do think that you ought to publish more on this topic, it may not be a taboo qolywshxbr,subject but generally folks don’t talk about such subjects. To the next! All the best!!

发表评论