SQL语句：Mac 下 处理myql 不能远程登录和本地登录问题

mac下，mysql5.7.18连接出错，错误信息为：Access denied for user 'root'@'localhost' (using password: YES)

（）里面的为shell中输入的命令，一定要输全包括；&等符号

第一步：苹果->系统偏好设置->最下面点mysql，关闭mysql服务

第二步：进入终端输入（cd /usr/local/mysql/bin/）回车

输入（sudo su）回车以获取管理员权限

输入（./mysqld_safe --skip-grant-tables &）回车以禁止mysql验证功能，mysql会自动重启，偏好设置中的mysql状态会变成running

第三步：输入命令（./mysql）回车

输入命令（flush privileges;）分号别忘记输了

输入命令（set password for 'root'@'localhost' = password('root');） password('root')中的root为新密码，自己随便设置，分号别忘记输入

至此，密码修改成功，可以正常登入了。

1、改表法。
可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从“localhost”改称“%”
mysql -u root -pvmwaremysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
2、授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'IDENTIFIED BY 'mypassword' WI
TH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3'IDENTIFIED BY
'mypassword' WITH GRANT OPTION;
我用的第一个方法，刚开始发现不行，在网上查了一下，少执行一个语句 mysql>FLUSH RIVILEGES
使修改生效，就可以了
另外一种方法：
在安装mysql的机器上运行：
1、d:mysqlin>mysql -h localhost -u root
//这样应该可以进入MySQL服务器
2、mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION
//赋予任何主机访问数据的权限
3、mysql>FLUSH PRIVILEGES
//修改生效
4、mysql>EXIT
//退出MySQL服务器
这样就可以在其它任何的主机上以root身份登录啦。

首先手动启动Mysql应用程序

打开终端输入如下命令：  /usr/local/mysql/bin/mysql -u root -p

（注意：Windows下的是：  mysql -u root -p）

其中root为用户名。这时会出现如下命令：Enter password:  123456

这样就可以访问你的数据库服务器了。

                                     数据库操作

下面介绍一些关于从数据库创建到使用的一些简单的方法；

创建一个名字为mydatabase数据库：  create database mydatabase ;

可以用以下地命令来查看创建的数据库是否成功： show databases ;

更改数据库名字 ： alter databases  Hdatabase ;

更改数据库mydatabase的字符集 ：  alter database mydatabase charset GBK ;

进入数据库：  use
 mydatabase ;

用下面的命令来查看该数据库中的表：  show tables ;

                                表操作

用下面的命令来创建表：create table student (

                                      name varchar(10) , 

                                     gender varchar(10) , 

                                     sno    int    primary key(id)

                                    )charset utf8;

用下面的命令来检查表的创建是否成功：   show tables;

查看该表结构，表中字段的信息：  desc 表名 ;        describe 表名;       show columns from  表名;

修改表名：  rename命令格式：rename table 原表名 to 新表名;

                   alter  table  旧表名  rename  新表名;         alter table student  rename  my_student;

修改字段，字段操作很多：新增(add)、修改(modify)、重名(change)、删除(drop)

--给学生表增加ID放到第一个位置。                          alter table  表名  add  字段名  数据类型[位置];

alter  table my_student add  id int first;

---将学生表中的number学号字段变成固定长度，且放到第二位。   alter table 表名  modify  字段名  数据类型  [位置]; 

alter  table  my_student  modify  number  char(10)  after id;

---修改学生表中的gender字段为sex。                       alter table 表名  change  旧字段名  新字段名  数据类型 ;   alter table my_student  change  gender  sex  varchar(10);

---删除学生表中的字段age。                                alter  table  表名   drop   字段名;    

alter  table  my_student  drop  age;                                         

删除数据表： drop  table  表名1，表名2，……;         一次性删除多个表

                 数据操作

---新增数据（插入全表字段数据） insert into my_student values('Jim','female',1106101);

---新增数据（指定字段列表）   insert into  my_student(number,sex)  values(1106,'male');

---查看所有的数据  select  * 

                  from  表名

                  [where 条件];

---更新数据    alter是操作数据库，表，字段。 update更新数据

--更新名字叫Jim的性别。      update  表名  set  字段 = 值[where 条件]；

update my_student set sex = 'female' where name = 'Jim';

---删除数据 

--删除表中性别男的      delete  from  表名  [where  条件];

delete  from my_student  where  sex = 'male';

中文数据问题本质是字符集问题，set names  gbk;   一定要设置字符集。

外键：如果一张表中有一个字键段（非主键）指向另外一张表的主键，那么将该字段称之为外键。

主键问题

主键直接在字段之后，可以有多个字段作为主键，这样的主键称为复合主键。

没有主键，追加主键    alter  table  my_database  modify  course  char(10)  primary key;

没有主键，追加复合主键  alter table  my_student  add  primary key (sno,cno);

更新主键 & 删除主键  alter  table  表名 drop primary  key;

范式：终极目标为了减少数据的冗余，凡是通过关系寻找出来的数据，坚决不再重复存储。

第一范式：在设计表存储数据时，数据具有原子性。

如表（姓名，性别，教课时间（开始，结束））  此表的教课时间仍可拆分，不具备原子性。

解决办法（姓名，性别，开始，结束）

第二范式：解决表设计出现的部分依赖，部分依赖就是存在字段依赖主键中某个字段（主键的部分）。只要不存在复合主键，表的设计就一定满足2NF

如一张表中有(讲师，性别，班级，教室，带课时间，开始时间，结束时间）   因为讲师没办法作为主键，需要结合讲师班级才能作为主键（一个老师在一个班只带一个阶段的课，性别并不依赖班级，只依赖讲师；教室不依赖讲师，只依赖班级，因此出现了性别和教室依赖主键中的一部分）

解决方案： 可以将性别与讲师单独成表，班级与教室也单独成表，以此取消复合主键。

有人也认为可以使用逻辑主键，例如增加IDP。这样真的可以吗？  不可以，如下，会出现传递依赖。

第三范式：理论上讲，应该一张表中的所有字段都直接依赖主键，如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键，如一张表中有(主键IDP，讲师，性别，班级，教室，带课时间，开始时间，结束时间）  性别依赖于讲师，讲师依赖于主键IDP；教室依赖班级，班级依赖主键IDP。性别和教师都存在传递依赖。

解决方案：将存在传递依赖的字段，以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息的时候，使用对应的实体表的主键加进来。一句话就是实体单独建表。

查询数据完整语法

select 字段名/*

from  数据源

[where 条件子句]

[group by 子句]

[having 子句]

[order by 子句]

[limit 子句];

select *  from my_student; 

---去重

select distinct *  from  my_student;

--插入数据

insert into my_student values(null,'itcast01','张三','男'),(null,'itcast02','李四','男'),(null,'itcast03','王五','女'),(null,'itcast03','男');

--字段别名

select id,number as 学号，name as 姓名, sex 性别 

from my_student;

where子句

原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断，判断的结果如果成立保存到内存，反之放弃。

---找学生id为1，3，5的学生

select  *  from  my_student  where id =1 || id =3 || id =5;

或者select * from my_student where id in(1,3,5);

group by  字段 [asc/desc];   分组  根据某个字段进行分组，相同的放一组，不同的分到不同的组。统计的是数据，不是记录

--按性别分组

select * from my_student group by sex; 这是绝对错误的，分组是为了统计数据，按分组字段进行数据统计

SQL提供了一系列统计函数

Count();  统计分组后的记录数，每一相同的组有多少行记录

Max();   统计每组最大的值

Min();   统计每组最小的值

Avg();   统计每组的平均值

Sum();   统计每组的和

---身高高矮，平均年龄，和总年龄

select sex,count(*),max(height),min(height),avg(age),sun(age)   from my_student  group by sex;

注意使用group by却不使用统计函数，没意义!

多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段分组。

having子句

与where子句一样进行条件判断，思考：为什么是group by……having…… 而不是group by……where???

进行数据统计时，是数据进入内存之后，会进行分组-》统计-》having条件判断。而where是针对从磁盘读取数据时进行判断，此刻还没分组，进行统计就判断了。所以错误！！！

---求出所有班级人数大于等于2的学生人数

order by 子句

order by根据某个字段进行升序或降序排序，依赖校对集

基本语法： order by 字段名 [asc/desc];  asc升序 可不写      desc降序

 

limit子句是一种限制结果的语句

1）用来限制数量

--查询学生前两个   select * from my_student limit 2;

2)限制起始位置  limit 起始位置，长度

--查从编号4开始，询找两个学生

select * from my_student limit 4,2;

内连接，外连接，自然连接，交叉连接

内连接，从左表中取出每一条记录，和右表中所有的记录进行匹配，匹配必须是左表中与右表中某个条件相同，最终会保留结果，否则不保留。

基本语法：  左表 [inner] join 右表  on 左表.字段 = 右表.字段;

select * from my_student inner join my_class on my_student.C_id = mu_class.id;

左外连接：左表为主，然后每条记录与右表进行连接，不管能不能匹配的上，左表都会保留。能匹配，右表某条记录保留，不能匹配，某条记录置为NULL，最终记录数至少不少于左表已有的记录。

基本语法：  左表  left join  右表  on  左表.字段 = 右表.字段;

 

 a表     id   name      b表     id   job   parent_id   
              1   张3                   1     23     1   
              2   李四                 2     34     2   
              3   王武                 3     34     4       
  a.id同parent_id   存在关系   

--------------------------------------------------    
 1） 内连接   
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
    
  2）左连接   
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null   

 

 3） 右连接   
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  null                        3     34     4   
    
 4） 完全连接   
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

  结果是     
  1   张3                  1     23     1   
  2   李四                 2     34     2   
  null               　　  3     34     4   
  3   王武                 null

RIGHT：字段截取

CASE WHEN 条件查询
instr  字段用“,”分隔的and查询

 <!--查询新韩卡流水-->
  <select id="listTKoreaTransact" resultType="map">
    select
    RIGHT(t1.korea_card_no,4) AS koreaCardNo,
<!--     CASE t1.pay_type WHEN  'koreacharge' THEN 'into' -->
<!--     else 'out' END as wayType, -->

    t1.createtime as billTime,

    CASE t1.bill_amount WHEN .00 THEN 0.00
    ELSE t1.bill_amount END AS billAmount,
<!--     t1.bill_amount as billAmount, -->

    t1.sale_amount as saleAmount,
    t1.pay_type as singleBillType,

    CASE
    WHEN t1.order_state = 'refund' THEN 'refund'
    WHEN (t1.pay_type = 'koreascan' or t1.pay_type = 'koreacard' or t1.pay_type = 'korearefund') THEN 'koreaout'
    WHEN t1.pay_type = 'koreacharge' THEN 'acctfrozen'
    else '' END as billStatus,
    t1.transact_no as transactNo,
    CASE t1.original_amount WHEN .00 THEN 0.00
    ELSE t1.original_amount END AS originalAmount,
<!--     t1.original_amount as originalAmount,
        SELECT * FROM t_korea_transact where instr('koreacharge,korearefund',pay_type);
-->
    t3.bank_logo_url as logoUrl,
    t3.bank_logo_not_color_url as logoNotColorUrl
    from t_korea_transact t1
    inner join t_korea_info t2 on t1.korea_card_no = t2.korea_card_no
    inner join t_bank_base t3 on t2.t_bank_base_id = t3.id
    where 1=1
    <if test="null != billType and '' != billType and billType != 'all'">
      AND instr(#{billType},t1.pay_type)
    </if>
    and t2.t_user_info_id= #{userId}
    <if test="null != billStartDate and '' != billStartDate ">
      and t1.createtime &gt;= #{billStartDate}
    </if>
    <if test="null != billEndDate and '' != billEndDate ">
      and t1.createtime &lt;= #{billEndDate}
    </if>
    and t2.korea_card_type = 'prepaid'
    and t2.id = #{currencyCardId}
    ORDER BY t1.createtime DESC
  </select>