当前位置:Gxlcms > 数据库问题 > DBA成长之路---mysql数据库服务(二)

DBA成长之路---mysql数据库服务(二)

时间:2021-07-01 10:21:17 帮助过:21人阅读


增加

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

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

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


查询

单表查询

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


条件匹配的表示方法

数值比较

        字段名 符号 值 符号:>  >=  <  <=  =  !=

        字符比较

        字段名 符号 "值"   符号: =  !=

        范围内匹配

        字段名 in (值列表) 在...里

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

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

        字段名 not in(值类表) 不在...里

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

        字段名 between 值  and 值 在...之间(数值类型)

        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)


逻辑匹配: 有多个条件时

        逻辑与  and   多个条件必须都成立

        逻辑或 or 多个条件有一个成立即可

        逻辑非  ! 取反

        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() 集合平均值

        sum() 对集合的各参数求和

        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 user limit 1; #显示查询的前一行

        select * from user limit 2,3; #设置显示行范围  从第2行显示(行数开始为0行) 显示3行


多表查询

        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;

        create table studb.t4 select name,uid,shell from user limit 5;

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

        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

        索引的使用规则

         默认可以重复,可以赋NULL值

         可以由多个index字段

         把查询条件做为索引

        查看 decs 表名;

         show index from 表名;

         标志 MUL

        创建

         建表时创建索引:

         mysql> create table t25(

            -> name char(10),

            -> age int,

            -> sex enum("boy","girl"),

            -> index(sex) #索引名 默认和字段名相同

            -> index(name)

            -> );

         在已有表创建索引 create index 索引名 on 表名(被赋索引的字段名)

         mysql> create index age on t21(age);

         mysql> show index from t21\G;

         Table: t21

           Non_unique: 1

             Key_name: age

         Seq_in_index: 1

          Column_name: age

            Collation: A

          Cardinality: 4

             Sub_part: NULL

               Packed: NULL

         Null: YES

           Index_type: BTREE

              Comment: 

         Index_comment: 

        

        默认使用的索引类型(Index_type):BTREE(二叉树)

        还支持 hash B+TREE

        

        删除 drop index 索引名 on 表名;

        mysql> drop index a1 on t21;


fulltext 全文索引


unique 唯一索引

一个表中有多个unique字段

可以为空 但是有值不能重复

mysql> create table t211( stu_id char(9), name char(10), sex enum('boy','girl'), unique(stu_id) );

mysql> desc t211;

key标识是UNI 

mysql> alter table t211 modify stu_id char(9) not null;

mysql> desc t211;

key标志是PRI 但是不是主键

mysql> drop index stu_id on t211;

创建 unique index

mysql> create unique index stu on t211(stu_id);



主键

        主键使用规则

         一个表中只能有一个primary key

         不允许重复 不能为空

        查看 decs 表名;

         标志 PRI

        创建 建表时创建主键:

         mysql> create table t26(

            -> name char(10),

            -> age int,

            -> likes set("a","b","c"),

            -> primary key(name)

            -> );

         mysql> create table t22(

            -> id int primary key,

            -> name char(10)

            -> );

         在已有表创建主键:

         mysql> alter table t25 add primary key(name);

        删除 alter table 表名 drop primary key;

         mysql> alter table t25 drop primary key;

        

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

         查看

         mysql> desc t28;

         建表时创建主键:

         mysql> create table t28(

            -> cip char(15),

            -> port smallint,

            -> status enum("allow","deny") defualt "deny",

            -> primary key(cip,port)

            -> );

         在已有表创建主键:

         mysql> alter table t28 add primary key (cip,port);

         删除

         mysql> alter table t28 drop primary key;

        主键一般 与auto_increment 连用

            字段值自动增长

         满足条件 主键 数值类型

         创建表

         mysql> create table t27(

            -> id int(2) zerofill primary key auto_increment,

            -> name char(10),

            -> class char(4),

            -> index (name)

            -> );

         删除自动增长的主键

         mysql> alter table t27 modify id int(2) unsigned zerofill not null;

         mysql> alter table t27 drop primary key;

    

外键 

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

        表的存储引擎必须是 innodb

        字段类型要一致

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

        创建命令

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

        on update cascade 同步更新

        on delete cascade 同步删除


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

        delete from 表名 where 条件

        删除外键

        mysql> show create table xsb; #查看建表命令

        可以查看外键名

        alter table 表名 drop foreign key 外键名

        

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

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

        on update cascade 同步更新

        on delete cascade 同步删除


mysql 服务的体系结构:(8个功能模块)

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

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

        分析器:分析语法错误

        优化器:优化执行命令

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

        存储引擎

        文件系统

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

        

mysql存储引擎:


存储引擎介绍

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

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

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

        mysql> show engines;

        | InnoDB             | DEFAULT | #default 默认存储引擎

        | MyISAM             | YES     |

常用的存储引擎

    myisam

        表.frm 表结构

        表.MYI 索引信息

        表.MYD 数据

        支持表级锁(锁一张表)

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

   innodb

        表.frm 表结构

        表.ibd 表结构 索引信息

        支持行级锁(只给当前被访问的行加锁)

        支持事务 事务回滚

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

        /var/lib/mysql/ib_logfile*

        

   

         锁类型:读锁 select 

         写锁 insert delete update

         锁粒度:行级锁  表级锁

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

        

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

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

        事务的特性:一致性,原子性,隔离性

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

        

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

        接收写操作多的表适合使用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=存储引擎;

        


数据管理

数据导入:把系统文件的内容存储到数据库的表里

/etc/passwd studb.user

        用户名 密码占位符 UID GID  描述信息  家目录   shell

        create database studb;

        create table studb.user(

        name char(50),

        password char(1),

        UID int(2),

        GID int(2),

        comment varchar(100),

        homedir char(100),

        shell char(25)

        )engine=innodb; 

        select * from studb.user;  

        load data infile '目录/文件名' into table '库.表名' fields terminated by "字段间隔符号" lines terminated by "行间隔符号"

        

        查看默认使用目录及目录是否存在

        mysql> show variables like "secure_file_priv";

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

        | Variable_name            | Value                                          |

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

        | secure_file_priv           | /var/lib/mysql-files/                    |

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

        [root@mysql4-1 ~]# cp /etc/passwd /var/lib/mysql-files/

        [root@mysql4-1 ~]# setenforce 0

        mysql> load data infile '/var/lib/mysql-files/passwd' into table user fields terminated by ":" lines terminated by "\n";

        Query OK, 44 rows affected (0.04 sec)

        Records: 44  Deleted: 0  Skipped: 0  Warnings: 0

        

        修改默认使用目录

        [root@mysql4-1 ~]# mkdir /myfile

        [root@mysql4-1 ~]# chown mysql /myfile/

        [root@mysql4-1 ~]# vim /etc/my.cnf

        [mysqld]

        secure_file_priv="/myfile"

        [root@mysql4-1 ~]# systemctl restart mysqld

        mysql> show variables like "secure_file_priv";

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

        | Variable_name    | Value    |

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

        | secure_file_priv | /myfile/ |

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

        1 row in set (0.00 sec)

        


数据导出:把表记录存储到系统

        sql 查询 into outfile "目录/文件名";

        sql 查询 into outfile "目录/文件名" fields terminated by "字段间隔符号" lines terminated by "行间隔符号"; 

        mysql> select name,UID from user limit 5 into outfile "/myfile/user1.txt";

        [root@mysql4-1 ~]# ls /myfile/

        user1.txt

        [root@mysql4-1 ~]# cat /myfile/user1.txt #默认字段间隔符号为<tab>  默认行间隔符号"\n"

        root 0

        bin 1

        daemon 2

        adm 3

        lp 4

        

        mysql> select name,UID from user limit 5 into outfile "/myfile/user2.txt" fields terminated by "#" lines terminated by ":";

        [root@mysql4-1 ~]# ls /myfile/

        user1.txt  user2.txt

        [root@mysql4-1 ~]# cat /myfile/user2.txt 

        root#0:bin#1:daemon#2:adm#3:lp#4:

        

        

        

        

        

        


DBA成长之路---mysql数据库服务(二)

标签:enc   存在   接口   tree   工作   apach   语法错误   占用   sign   

人气教程排行