少年健忘之『记录Mysql语句命令』

作者: Super三脚猫 | 来源:发表于2017-10-23 14:59 被阅读0次

Mysql.jpg

基本语法（命令）

- - - 【连接】数据库

mysql -h localhost -u root -p
 -h   服务器地址
 -u   用户名
 -p   密码(回车后输入)
 -P(大写) 端口号
 -b   关闭蜂鸣器

mysql -u root -p

【库】的操作：

【查看库】show databases;
【创建库】create database (库名) default charset=utf8mb4;
【删除库】drop database (库名);
【打开库】use (库名);

【表】的操作：

【查看表】show tables;
【创建表】：
 mysql> create table (表名) (
    -> uid int,
    -> name char(4)
    -> ) engine=innodb default charset=utf8mb4;
【查看建表语句】show create table (表名)\G;
【查看语句分析语句是否运用索引】：
desc select * from (表名) where uid=1;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: const
possible_keys: PRIMARY   // 是否存在索引
          key: PRIMARY  // 是否运用索引
      key_len: 4
          ref: const
         rows: 1    // 受影响行数(主要看这个)
        Extra: 
1 row in set (0.00 sec)
【删除表】drop table (表名);
【设置表严格模式】truncate (表名);
【查看表结构】desc (表名);
【查看表注释】show create table (表名);
【查看表里字段详细信息】show full columns from (表名);
【修改表注释】alter table (表名) comment "(修改后的字段注释)";
【修改字段的注释】
 alter table (表名) modify column (要修改的字段名) varchar(200) comment "(修改后的字段注释)";
【修改字段的注释和字符集】
 alter table (表名) modify column (字段名) varchar(200) character set utf8;
【把字段 shipping_status  的类型从 ENUM('未收货') 改为 ENUM('未收货','未发货')】
ALTER TABLE yed_orders MODIFY shipping_status ENUM('未收货','未发货');

对数据的【增删改查】：

【增加数据】insert into (表名)(id,name,nickname,sex) value(1,"zhangsan","张三","女");
【增加多条】insert into (表名)(id,name,nickname,sex) value(1,"zhangsan","张三","女"),(2,"lisi","李四","人妖");
【暴力添加数据】insert into (表名)(name,email) select name,email from (同前面的表名) where id = 5;

【删除数据】delete from (表名) where id=250;

【修改数据】update (表名) set (列名)=(新值) where (列名)=(字段名);

查询数据可以多样化，下面我列出各种奇葩查询方式

1. 条件部分查询

【查询数据】select * from (表名);                            (*代表所有字段,一般数据量大的不要这么查询会卡死)
【查询部分字段】select name,sex from (表名);
【列合并】select id,name,count(age,sex)xxoo from (表名);     (xxoo是指给age和sex合并字段后起的别名)
【查询10年后年龄的人】select id,name,age+10 xxoo from (表名);
【去除重复字段】select distinct * from (表名);
【某个班级的学生】select * from (表名) where class="一年级三班";
【一年级三班的女生】select * from (表名) where class="一年级三班" and sex="女";     (and用法''并且'')
【年龄>20的学生】select * from (表名) where age > 20;
【id号除了10-40的都查询 方法1:】select * from (表名) where id<10 or id>40;          (or用法''或者'')
【id号除了10-40的都查询 方法2:】select * from (表名) where id not between 10 and 40;
【A班男生和B班女生】select * from (表名) where (class="A" and sex="男") or (class="B" and sex="女");
【查找多个id】select * from (表名) where id in(2,4,8,23,58);
【找名字为两个字的人】select * from (表名) where name like "__";                        (一个‘_’代表一个字符)
【查找带"猫"的词】select * from (表名) where name like "%猫%";
【查找有三也有猫字】select * from (表名) where name like "%三%" and name like "%猫%";
【时间从远到近排序】select * from (表名) order by time;
【时间从近到远排序】select * from (表名) order by time desc;
【先时间再年龄排序】select * from (表名) order by time,age desc;                    (处理相同情况下一般用不到)

3. 分组查询

group by统计函数  count(*)统计记录条数  sum(字段名)对该字段求和  
avg()平均数  max()某字段最大值 min()最小值
【总共多少人】select count(*) from (表名);
【总共多少人起别名查询】select count(*) countName from (表名);
【每个班人数】select class,count(*) from (表名) group by class;
【每个班最大年龄】select  class,max(age) from (表名) group by class;
【在平均年龄之上的人】select * from (表名) where age >(select avg(age) from (表名));
【每个班的男生女生各多少】select class,sex,count(*) from (表名) group by class,sex;

4. 分页查询

【显示前5条数据】select * from (表名) limit 5;
【跳过前两条,显示后面3条】select * from (表名) limit 2,3;

显示第X页的公式：select * from (表名) limit(x-1)*跳过条数,显示条数;
通俗一点就是：(当前页-1)*每页多少条,每页多少条;

5. Join联表查询

内联方式1:select * from (表A),(表B) where (表A).id = (表B).id;
内联方式2:select * from (表A) inner join (表B) on (表A).id = (表B).id; (inner join用法)
左联:select * from (表A) left join (表B) on (表A).id = (表B).id; (left join用法)
右联:select * from (表A) right join (表B) on (表A).id = (表B).id; (right join用法)

6. 修改数据库root密码

mysqladmin修改

mysqladmin -u root -p password

Sql修改

>use mysql
>set password for root@localhost = password('123');  // 热修改不需要重启
>flush privileges;

7. 授权添加用户

           授权  具体权限         ON 库.表  TO   '谁'@'在哪儿登录' IDENTIFIED BY 密码
【添加用户】grant select,insert on discuz.stu to 'xxoo'@'%' identified by '密码'
【删除用户】drop user ''@'localhost';

操作数据表结构（命令）

对表字段的【增删改查】

【添加字段】alter table (表名) add (字段名) (字段属性);
 // 示例：
 // alter table user add column is_disable tinyint(1) not null default '0' comment '是否启用0启动1禁用' after email;
 // comment是字段注释 after是指定放在字段后面
【删除字段】alter table (表名) drop (字段名);
【修改字段】alter table (表名) modify (字段名) (新的字段属性);              (修改方法1)
【修改字段】alter table (表名) change (原字段名) (新字段名) (新字段属性);    (修改方法2)
【查询字段】desc (表名);
【修改表名】alter table (原表名A) rename as (新表名B);
【字段后面加字段】alter table (表名) add (新字段名) (新字段属性) after (参照物字段名);

数据库的导入导出

导出数据【库】:mysqldump -u root -p (库名)>(文件名).sql;
导出数据【库】只要表结构:mysqldump -u root -p -d (库名)>(文件名).sql;
导入数据【库】:mysql -u root -p (库名)<(文件名).sql;
导出数据【表】:mysqldump -u root -p (库名) (表名)>(文件名).sql;
导入数据【表】:

方法1：mysql -u root -p (库名)</data/home/(单个表名).sql
方法2：Mysql>source /data/home/(单个表名).sql

数据库事务模式

begin     开启事务（只记录有效，删除表、库、字段无效）
rollback  回滚（撤销）
commit    确认（提交）

操作数据表添加索引

方式1 （alter）

【添加普通索引】alter table (表名) add index 索引名(字段名);
【添加唯一索引】alter table (表名) add uniqe 索引名(字段名);
【添加主键索引】alter table (表名) add primary key 索引名(字段名);

方式2 （create）

【创建普通索引】create index (索引名) on 表名(字段名);
【创建唯一索引】create uniqe index (索引名) on 表名(字段名);

字段约束的词语:

unsigned 无符号    zerofill 零填充,位数不够时0填充   not null 不能为空    unique 唯一
default 给默认值   auto_increment 自增             primary key 主键,不能为空且唯一

索引 : 可以提高查询速率（但是在insert和update时会产生额外的开销）

mysql索引类型normal，unique，full text的区别是什么？

normal：普通索引唯一的任务是加快对数据的访问速度
unique：唯一索引表示唯一的，不允许重复的索引，可以为null
full text：全文索引表示全文搜索的索引。 FULLTEXT用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的INDEX 也可以
spatial：空间索引空间索引只能在存储引擎为MYISAM的表中创建
key：普通索引普通非唯一索引
primary：唯一索引，不允许为null

MySQL索引方法

B-Tree：

B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。
所以`B-Tree`适合用来查找某一范围内的数据，而且可以直接支持数据排序`（ORDER BY）`

`B-Tree`在`MyISAM`里的形式和`Innodb`稍有不同：
`MyISAM`表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址
`InnoDB`表数据文件本身就是主索引，叶节点`data`域保存了完整的数据记录

Hash索引：

`1.`仅支持"=","IN"和"<=>"精确查询，不能使用范围查询：
由于Hash索引比较的是进行Hash运算之后的Hash值，不能用于范围的过滤
`2.`不支持排序：由于Hash索引中存放的是经过Hash计算之后的Hash值
`3.`在任何时候都不能避免表扫描：无法从Hash索引中直接完成查询，
还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
`4.`检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，
最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
`5.`只有Memory引擎支持显式的Hash索引

Mac 用包管理 brew 安装 Mysql

【查看所有版本】brew search mysql
【指定安装版本】brew install mysql@5.7
【启动命令】brew services start mysql@5.7
 或 /usr/local/opt/mysql@5.7/bin/mysql.server start
【查看进程】ps -ef | grap mysql
【查看包路径】brew --prefix mysql@5.7
【设置bash命令】alias mysql=/usr/local/opt/mysql@5.7/bin/mysql
 或 sudo ln -s /usr/local/opt/mysql@5.7/bin/mysql /usr/bin

配置外部访问

1.【打开配置文件】vim /usr/local/etc/my.cnf
 注释bind-address = 127.0.0.1 改为 # bind-address = 127.0.0.1
2.【修改mysql连接限制】后退看我 Navicat 文章。

如果你也是少年健忘 || 老年健忘，希望能给你带来方便使用。