DBA成长之路---mysql数据库服务基础（二）

管理表记录 

增加

insert into 库.表 values(字段值列表);

insert into 库.表(字段值列表) values(字段值列表);

insert into 库.表 values(字段值列表),(字段值列表);

查询

单表查询

select 字段名列表 from 库.表 where 条件；

条件匹配的表示方法

数值比较

        字符比较

        范围内匹配

        select id,name from user where name in ("apache","root","bob");

        select id,name,uid from user where uid in (1,2,3,4);

        select name from user where uid not in(0,1,2,3,4,5);

        select * from user where id between 10 and 15;

        select name from user where uid between 1 and 10;

        匹配空  is null

        字段名 is null

        匹配非空  is not null

        字段名  is not null

        select id from user where name is null;

空的定义

        insert into user(name) values (""),("null"),(null);

        select id,name from user where name="";

        select id,name from user where name="null";

        select id,name from user where name is null;

        mysql> select id,name from user where id between 45 and 47;

        +-------+----------+

        | id      | name    |

        +-------+----------+

        |  45    |               |

        | 46     | null       |

        | 47     | NULL   |

        +-------+-----------+

        3 rows in set (0.00 sec)

        不显示重复值

        distinct 字段名

        select shell from user;

        select distinct shell from user;

        mysql> select distinct shell from user;

        +----------------------------+

        | shell                           |

        +----------------------------+

        | /bin/bash                   |

        | /sbin/nologin             |

        | /bin/sync                    |

        | /sbin/shutdown         |

        | /sbin/halt                    |

        | /bin/false                    |

        | NULL                          |

        +----------------------------+

        7 rows in set (0.00 sec)

逻辑匹配： 有多个条件时

        select id,name from user where name="zhangsan"and uid=500 and shell="/bin/bash";

运算操作  +  -  *  /  %

        字段名 符号  字段名

        select uid+gid as heid from user where name='root';

        select uid+gid  heid from user where name='root';

模糊查询

        where 字段名 like '表达式'

        _匹配任意一个字符 % 0个或多个字符

        select name from user where name like '____' and uid <= 10;

        select name from user where name like '%a%';

正则匹配

        where 字段名 regexp '正则表达式';

        . ^  $   [ ]  *

        mysql> select name,uid from user where uid regexp '^..$';

函数

        简单筛选/统计

        avg() 集合平均值

        min() 集合中的最小值

        max() 集合中的最大值

        count() 记录的各数

查询排序 

        sql查询 order by 字段名 asc/desc(降序)

        select name,uid from user where uid between 10 and 50 order by uid ;

查询分组 

        sql查询 group by 字段名

        select shell from user where uid between 10 and 50 group by shell;

        和不显示重复相似

查询限制显示行数 limit

        select shell from user where uid between 10 and 50 limit 1;

多表查询

        select 字段名列表 from 表名列表；笛卡尔集

        select 字段名列表 from 表名列表 where 条件；

        create table studb.t1 select name,uid,shell from user limit 3;

        create table studb.t2 select name,uid,homedir from user limit 4;

        select t1.*,t2.homedir from t1,t2 where t1.uid =t2.uid;

嵌套查询

        where 嵌套查询：把内层的查询结果做为外层查询的查询条件

        select 字段名列表 from 表名 where 条件 (select 字段名列表 from 表名 where 条件)

        select name,uid from user where uid > (select avg(uid) from user);

        select name from user where name in (select user from mysql.user);

复制表：作用：快速建表，备份表

        create table 库.表 sql查询；

        复制表

        create database dbbak;

        create table dbbak.user2 select * from user;

        复制表没有源表的属性和键值

复制表结构

        create table dbbak.user3 select * from user where 1=2;

连接查询

        左连接查询

        select 字段列表 from  表A left join 表B on 条件

        右连接查询

        select 字段列表 from  表A right join 表B on 条件

        create table studb.t3 select name,uid,shell from user limit 3;

        mysql> select * from t3 right join t4 on t3.uid=t4.uid; #以右为主 显示

修改

    批量修改

        update 库.表 set 字段名=值，字段名='值'

        mysql> update user set age="18";

        修改指定记录字段的值

        update 库.表 set 字段名=值，字段名='值' where 条件

        mysql> update user set name="zhangsan" where id=48;

删除

        以行为删除单位

        delete from 库.表 where 条件；

        mysql> delete from user where shell is NULL;

mysql 键值（限制如何给字段赋值）

普通索引 index

        什么是索引 : 类似“一个书的目录” 树型目录结构

        索引的优点 : 加快查询的速度

        索引的缺点 : 减慢写的速度 （insert update delete）;占用物理存储空间

使用普通索引  索引index

        索引的使用规则

        默认使用的索引类型（Index_type）：BTREE（二叉树）

        还支持 hash B+TREE

        删除 drop index 索引名 on 表名；

        mysql> drop index a1 on t21;

fulltext 全文索引

unique 唯一索引

主键

        主键使用规则

        复合主键 多个字段一起做主键 字段的值不允许同时重复

        主键一般 与auto_increment 连用

外键 

        作用：限制给字段赋值的。值必须在指定表中指定字段值的范围里选择

        表的存储引擎必须是 innodb

        字段类型要一致

        被参照字段必须要是索引类型的一种

        创建命令

        foreign key(字段名) references 表名(字段名) 

        update 表名 set 字段名=值  where 条件;

        delete from 表名 where 条件

        删除外键

        可以查看外键名

        alter table 表名 drop foreign key 外键名

        在已经创建的表上添加外键

        alter table 表名 add foreign key(字段名) references 表名(字段名) 

mysql 服务的体系结构：（8个功能模块）

        连接池：检查是否可以连接mysql

       sql接口： 执行的命令 传递给mysqld

        分析器：分析语法错误

        优化器：优化执行命令

        查询缓存：数据库的物理内存划分出的 每次查询 先找查询缓存

        存储引擎：

        文件系统：

      管理工具：安装mysql给提供的一些软件工具

mysql存储引擎:

存储引擎介绍

        mysql 数据库服务软件自带的程序。

        不同的存储引擎有不同的功能和数据存储方式

        查看数据库服务支持的存储引擎

        mysql> show engines;

        | MyISAM             | YES     |

常用的存储引擎

    myisam

        支持表级锁（锁一张表）

        不支持事务 不支持事务回滚

   innodb

        支持行级锁（只给当前被访问的行加锁）

        支持事务 事务回滚

        事务日志文件 ：记录对innodb存储引擎的表执行过的操作

        /var/lib/mysql/ib_logfile*

   锁：

        锁作用：解决并发访问冲突问题

 事务：一次从开始访问到访问结束的过程

        事务回滚：一次数据访问 任意一步执行失败，恢复所有操作。

        事务的特性：一致性，原子性，隔离性

        最典型的事务操作：银行转账

        工作如何决定表使用的存储引擎

        接收写操作多的表适合使用innodb存储引擎。（并发访问大）

        接收读操作多的表适合使用myisam存储引擎。(节省资源)

设置数据库服务的存储引擎

        设置服务的默认存储引擎

        [mysqld]

        defaulf-storage-engine=myisam

        mysql> create table tt1(id int(2));

        mysql> show create table tt1;

        ...

        | tt1   | CREATE TABLE `tt1` (

          `id` int(2) DEFAULT NULL

        ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

        ...

        修改表的存储引擎

        alter table 表名 engine=存储引擎；

        设置表的存储引擎

        creat table 表名（...）engine=存储引擎;

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>