开发者社区> 余二五> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

管理表记录 


增加

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=存储引擎;

        










本文转自 Xuenqlve 51CTO博客,原文链接:http://blog.51cto.com/13558754/2057391,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL数据库基础(1)数据库基础
一、数据库简介 二、mysql数据库 三、客户端连接mysql服务 四、Navicat for mysql
22 0
MySQL数据库基础-2
MySQL数据库基础-2
42 0
MySQL数据库--基础篇
打开数据库 sudo mysql -u root -proot 查看 show database 新建 show create database 数据库名 default character set utf8 删除 drop database 数据库名...
853 0
MySql[二]
视图 视图就是一个虚拟表(非真实存在),其本质是[根据SQL语句获取动态的数据集,并为其命名],用户使用时只需要使用[名称]即可获取结果集并可以将其当做代表来使用·   -- 临时表搜索 SELECT * FROM ( SE...
833 0
+关注
20377
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载