第三章 MySQL应用管理及进阶实战操作

NealCaffrey_

已于 2023-07-29 11:21:05 修改

阅读量111

点赞数

分类专栏：老男孩MySQL课程学习笔记文章标签： MySQL 数据库

于 2023-07-26 17:08:31 首次发布

本文链接：https://blog.csdn.net/siyanyanyanyai/article/details/131933638

版权

老男孩MySQL课程学习笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL 启停

配置mysqld服务

[root@mysql ~]# cp /application/mysql-5.5.32/support-files/mysql.server /etc/init.d/mysqld 
[root@mysql ~]# chmod +x /etc/init.d/mysqld 
[root@mysql ~]# /etc/init.d/mysqld start 
Starting MySQL.. SUCCESS!  

[root@mysql ~]# ps -ef |grep mysql |grep -v grep 
root       1942      1  0 20:20 pts/0    00:00:00 /bin/sh /application/mysql-5.5.32/bin/mysqld_safe --datadir=/application/mysql-5.5.32/data --pid-file=/application/mysql-5.5.32/data/mysql.pid 
mysql      2183   1942  0 20:20 pts/0    00:00:00 /application/mysql-5.5.32/bin/mysqld --basedir=/application/mysql-5.5.32 --datadir=/application/mysql-5.5.32/data --plugin-dir=/application/mysql-5.5.32/lib/plugin --user=mysql --log-error=/application/mysql-5.5.32/data/mysql.err --pid-file=/application/mysql-5.5.32/data/mysql.pid --socket=/application/mysql-5.5.32/tmp/mysql.sock --port=3306

停止mysqld

[root@mysql ~]# /etc/init.d/mysqld stop 
Shutting down MySQL. SUCCESS! 

[root@mysql ~]# mysqladmin -u root -p -S /application/mysql/tmp/mysql.sock shutdown 
Enter password: 
[root@mysql ~]# echo $? 
0

强制关停的集中方式

killall mysqld
pkill mysql
killall -9 mysqld
kill pid

参考文档
链接: killall 、kill 、pkill 命令详解

轻松应对IDC机房带宽突然暴涨问题：

链接: 625某电商网站数据库宕机故障解决实录（上）
链接: 625某电商网站数据库宕机故障解决实录（下）
链接: 轻松应对IDC机房带宽突然暴涨问题

登录方式：

单示例登录

mysql <无密码方式
mysql -uroot <无密码模式
mysql -uroot -p <有无密码均可，无密码可直接回车
mysql -uroot -p’xxxxxx’

控制历史命令history 是否记录：

HISTCONTROL=ignorespace

命令行修改命令提示符（临时生效）：

mysql> prompt \u@caojiemin \r:\m:\s-> 
PROMPT set to '\u@caojiemin \r:\m:\s->' 
root@caojiemin 09:19:30->

配置文件修改命令提示符（永久生效）my.cnf：

[mysql] 
prompt=\\u@caojiemin \\r:\\m:\\s->

多实例登录

本地登录制定socket，远程登录不需要。
本地登录：mysql -uroot -p -S /data/3306/mysql.sock

远程登录：

mysql -uroot -p -h127.0.0.1 -P3307

mysql中可以使用help 查看命令帮助
链接: linux中man 和 help的区别

退出MySQL 方式

ctrl+D
quit
exit
ctril+C

mysql修改密码

方法一：

mysqladmin -uroot password 'xxxxxx' 
mysqladmin -uroot -p'xxxxxx' password 'xxxxxx'

方法二：适用于丢失密码后修改，必须指定where条件，必须password()函数加密修改密码

>updata mysql.user set password=PASSWORD("xxxxxx")where user='root' 
>flush privileges

方法三：修改当前用户密码

>set password=PASSWORD("xxxxxx") 
>flush privileges;

找回丢失的MySQL用户密码：

第一步：停止数据库

      /etc/init.d/mysqld stop

第二步：指定特定参数启动数据库，注意二进制安装修改mysql_safe 将/usr/loca/mysql修改为/application/mysql

      #sed -i 's#/usr/local/mysql#/application/mysql/#g /application/mysql/bin/mysqld_safe 
      #mysqld_safe --skip-grant-tables --user=mysql &

注意：多实例方式 #mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &

第三步：修改密码

>updata mysql.user set password=PASSWORD("xxxxxx")where user='root' and host='localhost' 
>flush privileges;

第四步：停止数据库

#mysqladmin -uroot -pxxxxxx shutdown -S /tmp/mysql.sock

第五步：启动数据库

/etc/init.d/mysqld start

SQL语言分类：

DQL 数据查询语言 Data Query Language select
DML 数据操作语言 Data Manipulation Language update,delete,insert
TPL 事务处理语言 begin transaction,rollback,commit
DCL 数据控制语言 Data Control Language grant,revoke
DDL 数据定义语言 Data Define Language create,drop,alter
CCL 指针控制语言 Cursor Control Language declare cursor,fetch into,update where current

常见数据库管理应用

创建数据库

    >show database; 
    >create database oldboy; 
    >show databases; 
    >help show 
    >show create database oldboy\G

确认数据库默认字符集,latin1字符集可支持中文。编译安装时指定特定字符集则为默认字符集，若未指定为latin1

      -DDEFAULT_CHARSET=utf8 \ 
      -DDEFAULT_COLLATION=utf8_general_ci \  
    >create database oldboy_gbk default character set gbk collate gbk_chinese_ci; 
    >create database oldboy_utf8 character set utf8 collate utf8_chinese_ci;

注意：有无default均可。字符集不一致是数据库乱码的罪魁祸首。企业中怎么创建数据库：

根据开发的程序确定字符集，建议UTF8
编译时候指定字符集，例如：

-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
然后创建数据库的时候默认创建即可create database oldboy;

编译时未指定字符集或与指定了与程序不同的字符集，如何解决？指定字符集创建数据库即可
 >create database oldboy_gbk default character set gbk collate gbk_chinese_ci;  <==创建GBK字符集数据库 
 >create database oldboy_utf8 default character set utf8 collate utf8_general_ci; <==创建UTF8字符集数据库 
数据库软件要支持创建数据库的字符集：

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

显示数据库

    >show databases; 
    >show databases like '%old%'; 
    >select database ();  显示当前连接的数据库 
    >help show databases;

删除数据库

    >drop database oldboy; 
    >help drop database

连接数据库

    >use database oldboy_gbk; 
    >help use database

查看数据库版本

    >select version();

查看当前用户

  >select user();

查看当前时间

   >select now();

当前数据库包含的表信息

切换到数据库里面查看

    >show tables;  <==查看当前数据库 
    >show tables from oldboy_gbk; <==查看指定数据库中的表 
    >show tables in oldboy_utf8; <==查看指定数据库中的表

删除MySQL系统多余账户

    >use mysql 
    >select user,host from user; 
    >drop user 'oldgirl'@'localhost';  <==主机名大写不能删除 
    >select user,host from user;

注意：删除后的结果如上面返回的结果
如果drop删除不了（一般是特殊字符集或大写），可以用下面方式删除（以root用户，oldboy主机为例）：

delete from mysql.user where user=‘root’ and host=‘oldboy’;
flush privileges;

创建MySQL用户及赋予用户权限

通过help查看grant命令帮助

      >help grant 
      >create user 'jeffrey'@'localhost' identified by 'mypass'; 
      >grant all privileges on db1.* to 'jeffrey'@'localhost'; 
      >grant select on db2.invoice to 'jeffrey'@'localhost'; 
      >grant usage on *.* to 'jeffrey'@'localhost' with max_queries_per_hour 90;

通过查看grant的命令帮助，可以很容易找到创建用户并授权的例子！
2. 使用grant命令在创建用户的同时进行权限授予，
具体授权例子为：

      >grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

上述grant命令帮助里还提供了一个先用create命令创建用户，然后在用grant授权的方法，即创建用户和授权权限分开进行，例：

      >help create user 
      >create user 'jeffrey'@'localhost' identified by 'mypass'; 
      >grant all on db1.* to 'jeffrey'@'localhost'; 
      以上两条命令相当于下面一条命令 
      >grant all privileges on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

注意：数据库不存在仍可授权

授权测试验

方法一：

      >grant all privileges on test.* to oldboy@'localhost' identified by 'oldboy123' 
      >show grants for 'oldboy'@localhost';

方法二：

      >create user 'oldgirl'@'localhost' identified by 'oldgirl123'; 
      >grant all on test.* to 'oldgirl'@localhost'; 
      >show grants for 'oldgirl'@localhost';

查看用户权限

    >help show grants 
    >show grants for 'oldboy'@'localhost'

授权局域网内主机远程连接数据库

根据grant命令语法，我们知道test@'localhost’位置为授权访问数据库的主机，localhost可以用域名，IP地址或IP段来代替，因此，要授权局域网内主机可以通过如下方案实现

百分号匹配法

      >grant all on *.* to test@'10.0.0.%' identified by 'test123'; 
      >flush privileges;

子网掩码法

      >grant all on *.* to test1@'10.0.0.0/255.255.255.0' identified by 'test1123'; 
      >flush privileges;

注意：不可简写掩码

回收权限

>revoke insert on test.* on oldboy@'localhost'; 
    >flush privileges;

MySQL用户可以授权的权限有哪些

#mysql -uroot -poldboy123 -e "show grants for oldboy@localhost;"|grep -i grant|tail -1|tr ',' '\n' >all_privileges.txt

由于回收来insert ，所以缺少此权限。

    >use mysql 
    >show tables; 
    user表,db表,host表 
    user表中有所有权限信息。

注意：即在授权时，可以授权用户最小的满足业务需求的权限，而不是以为的授权all priveleges

企业生产环境如何授权用户权限：

博客、CMS等产品

grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy';

生成数据库表后，要回收create,drop权限

      >help revoke 
      revoke create,drop on blog.* from 'blog'@'10.0.0.%';

生产环节针对主库（写为主，读为辅）用户的授权

      普通环境： 
        本机：lnmp,lamp环境数据库授权 
      >grant all privileges on 'blog'.* to 'blog'@'localhost' identified by 'oldboy456'; 
        应用服务器和数据库服务器不在一个主机上的授权 
      >grant all privileges on 'blog'.* to 'blog'@'10.0.0.%' identified by 'oldboy456'; 
        严格的权限控制 
      >grant select,insert,update,delete on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy456';

生产环境从库（只读）用户的授权

      >grant select on 'blog'.* to 'blog'@'10.0.0.%' identified by 'oldboy456';

主从库高级授权策略
生产场景授权具体命令为：

      a.主库授权用户的命令 
      >grant select,insert,update,delete on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy456'; 
      b.从库授权用户的命令 
      >grant select on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy456';

这里有一个较为重要的问题就是主从库的MySQL库和表是同步的，无法针对同一个用户授权不同的权限，因为主库授权后回自动同步到从库，导致从库的授权只读失败。
解决方法：

取消MySQL库的同步
授权主库权限后，从库执行回收增删改权限
不在授权上控制增删改，而是用read-only参数，控制普通用户更新从库，注意read-only参数对超级用户无效。

表的操作

建立默认字符集数据库

    >create database oldboy; 
    >show create database oldboy\G

建立表

    >use oldboy 
    >help create table 
    >create table student( 
    id int(4) not null, 
    name char(20) not null, 
    age tinyint(2) not null, 
    dept varchar(16) default null);

查看创建表的语句

mysql> show create table student;
create table student( #create table表示创建表的固定关键字,student为表名
id int(4) not null, #学号列,数字类型，长度为4，不能为空值 
name char(20) not null, #名字列，定长字符类型，长度20，不能为空 
age tinyint(2) NOT NULL default '0', #年龄列，很小的数字类型，长度为2，不能为空，默认为0值 
dept varchar(16) default NULL #系别列，变长字符类型，长度16，默认为空。 
ENGINE=lnnoDB DEFAULT CHARSET=latinl #引擎和字符集，引擎默认为InnoDB，字符集，继承库的latinl 
);

区分InnoDB 引擎和MyISAM引擎：

列类型

主要区分int,char,varchar

数字类型

INT[(M)]型：正常大小整数类型

日期和时间类型（DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列）
字符串类型

CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度
VARCHAR型：变长字符串类型

有关MySQL字段类型详细内容，可以参考MySQL手册

INT[(M)]型：正常大小整数类型
DOUBLE[M,D] [ZEROFILL]型：正常大小（双精密）浮点数字类型
DATE 日期类型：支持的范围是1000-01-01到9999-12-31.MySQL以YYY-MM-DD格式来显示DATE值，但是允许你使用字符串给数字把值赋给DATE列
CHAR(M)型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
BLOB TEXT类型，最大长度65535（2^16-1）个字符
VARCHAR型：变长字符串类型
下面的图说明了 CHAR 和 VARCHAR 之间的差别：

解释：例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串（L）的长度，加上一个记录字符串长度的字节。对于字符串’abcd’，L是4，存储需要5个字节。

小结
■ char定长，不够的用空格补全，浪费存储空间，查询速度快，多数系统表字段都是定长
■ varchar变长，查询速度慢
例子：mysql.user用的就是定长

Password char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ‘’,
user表用的是 CHAR

生产环境创建数据库示例

se sns; 
et names gbk; 
REATE TABLE `subject_comment_manager` ( 
subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键', 
subject_type` tinyint(2) NOT NULL COMMENT '素材类型', 
subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键', 
subject_title` varchar(255) NOT NULL COMMENT '素材的名称', 
edit_user_nick` varchar(64) default NULL COMMENT '修改人', 
edit_user_time` timestamp NULL default NULL COMMENT '修改时间', 
`edit_comment` varchar(255) default NULL COMMENT '修改的理由', 
`state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常', 
PRIMARY KEY (`subject_comment_manager_id`), 
KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)), #<==括号内的32表示对前32个字符做前缀索引。 
KEY `IDX_SUBJECT_TITLE` (`subject_title`(32)) 
KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查看表结构

    >desc student; 
    >show columes student;

查看已建表的语句

    >show create table test.student\G

为表的字段创建索引

 索引就象书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。

创建主键索引

查询数据库，按主键查询是最快的，每个表只有一个主键列，但是可以用多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引不要求内容必须唯一，主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。
首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索。
建立主键索引的方法：

在建表示，可以增加建立主键索引的句子如下：

drop table student;
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2)  NOT NULL default '0',
dept varchar(16)  default NULL,
primary key(id),
KEY index_name(name)
);

提示：
■ primary key(id)<==主键
■ KEY index_name(name)<==name字段普通索引
2. 查看刚刚创建的表结构。

mysql> desc student; 
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   || 0       |                |
| dept  | varchar(16) | YES  |     | NULL|                |
+-------+-------------+------+-----+---------+----------------+

PRL为主键的标示，MUL为普通索引的表示
auto_increnment 代表数据自增
3. 利用 alter命令修改id列为自增主键值

alter table student change id id int primary key auto_increment;

创建的表的时候，可以指定

mysql> create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16)  default NULL,
primary key(id),
KEY index_name(name)      
);

提示：

KEY index_name
(name)
<==name字段普通索引

优化：在唯一值多的列上建索引查询效率高

还可以自定义自增的长度

EBGUBE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查看索引

mysql> show index from student; 
查看索引 \G 可以查看的更详细 
mysql> show index from student\G      
*************************** 1. row ***************************  主建  一个表只能有一个
            Table: student
       Non_unique: 0
         Key_name: PRIMARY    
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 0
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE           表类型
          Comment:
    Index_comment:
    *************************** 2. row ***************************  索引 一个表可以有多个
            Table: student
       Non_unique: 1
         Key_name: index_name   名字
     Seq_in_index: 1
      Column_name: name         列
        Collation: A
      Cardinality: 0
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE                  类型
          Comment:
    Index_comment:
    2 rows in set (0.00 sec)

查看表结构

desc student；

建表后利用alter增加普通索引，删除建表时创建的index_name索引
删除索引

alter table student drop index index_name;
drop index index_name on student;

操作实践：

给 name创建索引，也可以按照上述进行指定字节

create index index_name on student(name);

查看索引

mysql> show index from student;

创建语句

create index index_dept on student(dept(8));

为表的多个字段创建联合索引
　　如果查询数据的条件是多列时，我们可以为多个查询的列创建联合索引，甚至，可以为多个列的前n个字符创建联合索引，演示如下：
创建联合索引，和单个索引不冲突

mysql>  create index ind_name_dept on student(name,dept);

联合索引作用，查询更快

mysql> show index from student\G
    *************************** 1. row ***************************
    Table: student
    Non_unique: 0
    Key_name: PRIMARY
     Seq_in_index: 1
    Column_name: id
    Collation: A
    Cardinality: 0
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    Index_comment:
    *************************** 2. row ***************************
    Table: student
    Non_unique: 1
    Key_name: index_name
     Seq_in_index: 1
    Column_name: name
    Collation: A
    Cardinality: 0
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    Index_comment:
    *************************** 3. row ***************************
    Table: student
    Non_unique: 1
    Key_name: index_age
     Seq_in_index: 1
    Column_name: name
    Collation: A
    Cardinality: 0
    Sub_part: 8
           Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    Index_comment:
    *************************** 4. row ***************************
    Table: student
    Non_unique: 1
         Key_name: ind_name_dept
     Seq_in_index: 1
    Column_name: name
    Collation: A
    Cardinality: 0
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    Index_comment:
    *************************** 5. row ***************************
    Table: student
    Non_unique: 1
         Key_name: ind_name_dept
     Seq_in_index: 2
    Column_name: dept
    Collation: A
    Cardinality: 0
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    Index_comment:
    5 rows in set (0.00 sec)

创建联合索引，并指定值大小


create index ind_name_dept on student(name(8),dept(10));
name 前8个字符，dept 前10个字符
相关信息：
   *************************** 2. row ***************************
   Table: student
   Non_unique: 1
        Key_name: index_dept
    Seq_in_index: 1
   Column_name: name
   Collation: A
   Cardinality: 0
        Sub_part: 8
   Packed: NULL
   Null:
   Index_type: BTREE
   Comment:
   Index_comment:
   *************************** 3. row ***************************
   Table: student
   Non_unique: 1
        Key_name: index_dept
    Seq_in_index: 2
   Column_name: dept
   Collation: A
    Cardinality: 0
         Sub_part: 10
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    Index_comment:
    3 rows in set (0.00 sec)

提示：尽量在唯一值多的大表上建立索引。
什么时候创建联合索引？
只有程序用这两个条件查询，采用联合索引，这个主要是看开发。

提示：按条件列查询数据时，联合索引是由前缀生效特性的
提示：按条件列查询数据时，联合索引是有前缀生效特性的。index(a,b,c)仅a,ab,abc三个查询条件可以走索引。b,bc,ac,c等无法使用。

创建唯一索引（非主键）

create unique index uni_ind_name on student(name);

索引小结：

创建主键索引

alter table student chage id id int primary key auto_increment;

删除主键索引（主键列不能自增）

alter table student drop primary key;

创建普通索引

alter table student add index index_dept(dept);

根据的前n个字符创建索引

create index index_dept on student(dept(8));

根据多个列创建联合索引

create index index_name_dept on student(name,dept);

根据多个列的前n个字符创建联合索引

create index index_name_dept on student(name(8),dept(8));

创建唯一索引

create unique index uni_ind_name on student(name);

删除普通索引与唯一索引

alter table student drop index index_dept;
drop index index_dept on student;

索引列的创建及生效条件

问题1、既然索引可以加快查询速度，那么就给所有的列加索引吧？
解答：因为索引不但占用系统空间，而且更新数据时还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的需要少建立索引
问题2、需要在哪些列上创建索引才能加快查询速度呢？

select user,host from mysql.user where
2.password=…..,索引一定要创建在where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女生性别列唯一值少，不适合建立索引。

查看唯一值数量

select count(distinct user) from mysql.user;

唯一值就是相同的数量，例如查询user那么相同的user就是唯一值

mysql> select count(distinct user) from mysql.user;
+----------------------+
| count(distinct user) |
+----------------------+
|                    7 |
+----------------------+
 1 row in set (0.07 sec)

用户列表，根据上放进行解释

mysql> select user,host from mysql.user;
+-----------+---------------------------+
| user| host                      |
+-----------+---------------------------+
| cyh| 10.1.1.%                  |
| root| 127.0.0.1                 |
| bbs| 172.16.1.1/255.255.255.0  |
| wordpress | 192.168.1.%               |
| oldboy| 192.168.1.%/255.255.255.0 |
| abc| localhost                 |
| blog| localhost                 |
| oldboy| localhost                 |
| root| localhost                 |
+-----------+---------------------------+
9 rows in set (0.00 sec)

创建索引的基本知识小结：

索引类似书籍的目录，会加快查询数据的速度
要在表的列（字段）上创建索引
索引会加快查询速度，但是也会影响更新的速度，因为更新要维护索引数据
索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引
小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件上创建索引
多个列联合索引有前缀生效特性
当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
索引从工作方式区别，有主键，唯一，普通索引
索引类型有BTREE(默认)和hash（适合做缓存（内存数据库））等。

主键索引和唯一索引的区别

对于主键 /unique constraint oracle/sql server/mysql等都会自动建立唯一索引；
主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；
主健可作外健，唯一索引不可；
主健不可为空，唯一索引可；
主健也可是多个字段的组合；
主键与唯一索引不同的是：
a. 有not null属性；
b. 每个表只能有一个。

往表中插入数据

命令语法

insert into <表名>[(<字段名1>[..<字段名n>])]values(值1)[，(值n)]

● 建立一个简单的测试表 test

CREATE TABLE `test` (
   `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ;

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL|                |
+-------+----------+------+-----+---------+----------------+

● 插入值

insert into test(id,name) values(1,'oldboy');

● 查询

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | oldboy |
+----+--------+
1 row in set (0.00 sec)

第二种方法：

id列可以不指定，不指定就自己增长

insert into test(name) values('oldgirl');  

mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)

第三种方法

可以不指定列，后面按照循序插入，必须所有列都有值，

mysql> insert into test values(3,'inca');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca|
+----+---------+
3 rows in set (0.00 sec)

批量插入：

mysql> insert into test values(4,'zuma'),(5,'kaka');
Query OK, 2 rows affected (0.00 sec)
Records: 2Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma|
|  5 | kaka|
+----+---------+
5 rows in set (0.00 sec)

备份数据库

#mysqldump -uroot -poldboy123 -B oldboy > /opt/oldboy_bak.sql
#mysqldump -uroot -poldboy123 -A > /opt/oldboy_bak.sql
#grep -E -v "#|\/|--" /opt/oldboy_bak.sql

提示：错误的点是-A表示所有数据库，后面不能在指定oldboy_gbk库列。

删除

清空所有值

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

truncate table test 和delete from test 区别：前者更快，delete为逻辑删除，truncate清空物理文件。

mysql> select * from test;
Empty set (0.00 sec)

查询数据

查询表的所有数据行

命令句法：

select<字段1,字段2，…>from
<表名>where<表达式>

其中， select,from,where是不能随便改的，是关键字，支持大小写
2.列：查看表 test中所有数据

如果不进入库可以使用

mysql> select * from oldboy.test;
mysql> select user,host,password from mysql.user;
+-----------+---------------------------+-------------------------------------------+
| user| host                      | password|
+-----------+---------------------------+-------------------------------------------+
| root| localhost                 | *7495041D24E489A0096DCFA036B166446FDDD992 |
| root| 127.0.0.1                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wordpress | 192.168.1.%               | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| cyh| 10.1.1.%                  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| bbs| 172.16.1.1/255.255.255.0  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| blog| localhost                 | *7495041D24E489A0096DCFA036B166446FDDD992 |
| oldboy| localhost                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| oldboy| 192.168.1.%/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| abc| localhost                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------------------+-------------------------------------------+

只查询前2行内容

mysql> select * from test limit 2;
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)

从第二条开始查，查找2个

mysql> select * from test limit 1,2;
+----+---------+
| id | name|
+----+---------+
|  2 | oldgirl |
|  3 | inca    |
+----+---------+
2 rows in set (0.00 sec)

按照条件查询

mysql> select * from test where id=1;
+----+--------+
| id | name|
+----+--------+
|  1 | oldboy |
+----+--------+
1 row in set (0.00 sec)

提示：mysql> select * from test where name=‘abcdocker’; <==查询字符串要加引号，如果不加不走索引。

mysql> select * from test where name='abcdocker';
+----+--------+
| id | name   |
+----+--------+
|  1 | abcdocker |
+----+--------+
1 row in set (0.00 sec)

提示：查找字符串类型的条件的值要带单引号，数字值不带引号。
查询多个条件

mysql> select * from test where name='oldgirl' and id=2;
+----+---------+
| id | name    |
+----+---------+
|  2 | oldgirl |
+----+---------+
1 row in set (0.00 sec)

范围查询

mysql> select * from test where id>2;
+----+------+
| id | name |
+----+------+
|  3 | inca |
|  4 | zuma |
|  5 | kaka |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from test where id>2 and id<5;
+----+------+
| id | name |
+----+------+
|  3 | inca |
|  4 | zuma |
+----+------+
2 rows in set (0.35 sec)

或者的意思 
mysql> select * from test where id>2 or id<5;
+----+---------+
| id | name    |
+----+---------+
|  1 | abcdocker  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
5 rows in set (0.34 sec)

排序

什么都不加相当于升序

mysql> select * from test;
相当于
mysql> select * from test order by id asc;     升序（默认）
倒序
mysql> select * from test order by id desc;  倒序

例子：创建学生表

drop table student;
create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16)  default NULL  COMMENT '学生所在系别', 
PRIMARY KEY  (Sno) ,
key index_Sname (Sname)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
create table course(
Cno int(10) NOT NULL COMMENT '课程号',
Cname varchar(64) NOT NULL COMMENT '课程名',
Ccredit tinyint(2) NOT NULL COMMENT '学分',
PRIMARY KEY  (Cno) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
CREATE TABLE `SC` (
   SCid int(12) NOT NULL auto_increment COMMENT '主键',
  `Cno` int(10) NOT NULL COMMENT '课程号',
  `Sno` int(10) NOT NULL COMMENT '学号',
  `Grade` tinyint(2) NOT NULL COMMENT '学生成绩',
  PRIMARY KEY  (`SCid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入内容

INSERT INTO student values(0001,'宏志','男',30,'计算机网络');
INSERT INTO student values(0002,'王硕','男',30,'computer application');
INSERT INTO student values(0003,'oldboy','男',28,'物流管理');
INSERT INTO student values(0004,'脉动','男',29,'computer application');
INSERT INTO student values(0005,'oldgirl','女',26,'计算机科学与技术');
INSERT INTO student values(0006,'莹莹','女',22,'护士');

插入内容

INSERT INTO course values(1001,'Linux中高级运维',3);
INSERT INTO course values(1002,'Linux高级架构师',5);
INSERT INTO course values(1003,'MySQL高级Dba',4);
INSERT INTO course values(1004,'Python运维开发',4);
INSERT INTO course values(1005,'Java web开发',3);

插入内容

INSERT INTO SC(Sno,Cno,Grade)  values(0001,1001,4);
INSERT INTO SC(Sno,Cno,Grade) values(0001,1002,3);
INSERT INTO SC(Sno,Cno,Grade)  values(0001,1003,1);
INSERT INTO SC(Sno,Cno,Grade)  values(0001,1004,6);
INSERT INTO SC(Sno,Cno,Grade)  values(0002,1001,3);
INSERT INTO SC(Sno,Cno,Grade)  values(0002,1002,2);
INSERT INTO SC(Sno,Cno,Grade)  values(0002,1003,2);
INSERT INTO SC(Sno,Cno,Grade)  values(0002,1004,8);
INSERT INTO SC(Sno,Cno,Grade)  values(0003,1001,4);
INSERT INTO SC(Sno,Cno,Grade)  values(0003,1002,4);
INSERT INTO SC(Sno,Cno,Grade)  values(0003,1003,2);
INSERT INTO SC(Sno,Cno,Grade)  values(0003,1004,8);
INSERT INTO SC(Sno,Cno,Grade)  values(0004,1001,1);
INSERT INTO SC(Sno,Cno,Grade)  values(0004,1002,1);
INSERT INTO SC(Sno,Cno,Grade)  values(0004,1003,2);
INSERT INTO SC(Sno,Cno,Grade)  values(0004,1004,3);
INSERT INTO SC(Sno,Cno,Grade)  values(0005,1001,5);
INSERT INTO SC(Sno,Cno,Grade)  values(0005,1002,3);
INSERT INTO SC(Sno,Cno,Grade)  values(0005,1003,2);
INSERT INTO SC(Sno,Cno,Grade)  values(0005,1004,9);

检查，查看表格式或者表内容，注意：表名区分大小写

mysql>desc SC; or select * from SC;

连表查询：

mysql>select student.sno,student.sname,course.cname,sc.grade from student,course,SC 
where student.sno=SC.sno and course.Cno=SC.cno order by student.sno

优化
sql语句优化
explain 查看是否含有建立索引的语句

mysql> explain select * from test where name='oldboy'\G 在一个语句前面加上explain相当于模拟查询
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: test
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 5
            Extra: Using where
    1 row in set (0.00 sec)

创建索引

mysql> create index index_name on test(name);
| name| char(20) | NO   | MUL | NULL    |              

mysql> explain select * from test where name='oldboy'\G
*************************** 1. row ***************************
           id: 1
      select_type: SIMPLE
            table: test
             type: ref
    possible_keys: index_name
              key: index_name
          key_len: 60
              ref: const
             rows: 1   查询行数，表示当前只查询了1行
            Extra: Using where; Using index
    1 row in set (0.00 sec)

帮助

mysql> help explain  包含EXPLAIN的用法
使用explain命令优化SQL语句（ select语句）的基本流程

■ 抓慢查询SQL语法方法

每隔2,秒输入mysql> SHOW FULL PROCESSLIST; 如果出现2次说明是慢查询

mysql> SHOW  FULL PROCESSLIST;

■ 分析慢查询日志
配置参数记录慢查询语句

log_query_time = 2
log_queries_not_using_indexes
log-slow-queries = /data/3306/slow.log

■ 对需要建索引的条件列建立索引
大表不能高峰期建立索引， 300万条记录（如果访问已经慢了，可以直接创建）
■ 分析慢查询SQL的工具 mysqlala(每天早上发邮件)
切割慢查询日志，去重分析后发给大家，如果并发太大可以按小时，去重。
1）mv 然后flush进程
2）cp复制，然后利用>清空。
3）定时任务

mv /data/3306/slow.log /opt/$(date +%F)_slow.log
mysqladmin -uroot -poldboy -S /data/3306/mysql.sock flush-logs

增删改表的字段

命令语法及默认添加演示

命令语法

alter table 表名 add 字段 类型 其他；

测试表数据

     >show create table test\G 
     >desc test\G

实践案例

例：在表test中添加字段sex,age,qq 类型分别为char(4),int(4),varchar(15)

执行的命令演示
添加性别列，默认语句

      >alter table test add sex char(4); 
      >desc test

指定添加列的位置

指定添加年龄

      >alter table test add age int(4) after name;      
      >desc test;

在第一列添加qq

      >alter table test add qq varchar(15) first 
      >desc test;

生产环境多个复杂添加修改多字段信息的案例

增加1个字段：

    alter table 'etiantian' add 'firtphoto_url' varchar(255) default null comment '第一张图片URL'；

增加2个字段

    alter table 'basic' add adhtml_top' varchar(1024) default NULL comment '顶部广告html', 
             add 'adhtml_righe' varchar(1024) default NULL comment '右侧广告html';

改变字段

    alter table ett_ambiguity change ambiguity_state ambiguity_state tinyint comment '状态，默认 1=正常，0=失败'; 
    alter table 'ett_photo' modify column 'photo_description' varchar(512) character set utf8 collate utf8_general_ci not null comment '描述' after photo_title;

修改字段类型

    alter table test modify age char(4) after name;

更改表名

rename法

命令语法 rname table 原表名 to 新表名

      >rename table test to test1; 
      >show tables; 
      >show create table

语法帮助

      >help rename

alter法

      >alter table test1 rename to test;

删除表格

    drop table test；

NealCaffrey_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第三章 MySQL应用管理及进阶实战操作

主键列要求列的所有内容必须唯一，而普通索引不要求内容必须唯一，主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。解答：因为索引不但占用系统空间，而且更新数据时还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的需要少建立索引。b,bc,ac,c等无法使用。首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索。
复制链接

扫一扫