【学习笔记】Mysql数据库，数据库基础和测试常用查询语句整理学习碎片

网络学习资料：

http://www.cnblogs.com/chiangchou/p/mysql-2.html

 

特殊备注：

①between 10 and 50（包含了10和50）；>10 and <50（不包含10和50)

②转移符/，支付窜'(单引号)，任意字符%，单一字符_，多选列表in

③where new-old=100 后面只能用表达式；having number=100 后面可以用昵称别名

④最大max(age)、最小min(age)、平均值avg(age)、统计总数sum()、分类group by age

相对行数count(age)，如果age有一行值为null，count不会统计的该行的数字（数据不准确，会少）

绝对行数count(*)，如果age有一行的值为null，count依然会统计的该行的数字（量大的话，效率低）

⑤order by age desc(排序) 是针对最终结果集，所以order by 要放在where/group by/having 后面。降序：desc； 升序：asc.

 

内连接、左连接、右连接：

select * from a_table a inner join b_table b on a.a_id = b.b_id;

select * from a_table a left join b_table b on a.a_id = b.b_id;

select * from a_table a right outer join b_table b on a.a_id = b.b_id;

 

 

【mysql】

1.基础命令

进入安装Bin目录：cd C:\Program Files\MySQL\MySQL Server 6.0\bin

登录账户：mysql -h localhost（或127.0.0.1） -u root -p，password：1234

开启/关闭服务器：net start/stop mysql。退出账户：exit。CMD清屏：cls。

结果按列清晰打印(末尾加\G)：user mysql;select * from mysql\G;

显示数据表的属性和主键索引：user mysql;show columns/index from user\G。

创建和删除数据库：create database stu1;drop database stu1;

创建和删除数据表：create table stu1;drop table stu1;

 

2.数据类型

数值：bigint（8字节，超大整数）、int（4字节，大整数）、tinyint（1字节）

float（4字节，单精度浮点数）、double（8字节，双精度浮点数）

日期：year、month、date、time、datetime（YYYY-MM-DD HH:MM:SS)、timestamp（YYYYMMDD HHMMSS，从1970-01-01 00：00：00/2038 开始计算，结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07）

字符串：varchar（变长字符串）、tinytext（短文本）、blob（二进制长文本）、longtext（超长文本）

 

【问题】

问题一：安装的时候最后一步，stat severce 失败，通过CMD检查mysql状态异常。

报错：Access denied for user @’localhost’ (using password: YES）

说明：报错内容提示的虽然密码正确，但是该“空”账户依旧拒绝被访问。

原理：配置文件加一段skip-grant-tables代码，使set password for设置密码的命令无效，然后下一次登录无需键入密码也可以成功登录，登陆后再删除该部分新增代码，通过CMD设置密码，然后可以使用。

解决： ①通过C:\Program Files\MySQL\MySQL Server 6.0找到my.ini配置文件，在[mysqld]后添加skip-grant-tables。 ②通过CMD进入命令行，输入net stop mysql停止服务器；再通过输入net start mysql开启服务器；输入mysql -uroot -p；输入“空”密码；然后登陆成功。 ③通过C:\Program Files\MySQL\MySQL Server 6.0找到my.ini配置文件，在[mysqld]后删除skip-grant-tables。 ④通过CMD,mysql已登录状态，输入set password for ‘root’@’localhost’=password(‘1234’),给当前用户设置密码,然后退出，正常登陆即可使用。

 

【练习】：

1.创建一个数据表stu_user：

%取值范围如果加了 unsigned，则最大值翻倍%;%ENGINE 设置存储引擎，CHARSET 设置编码%

create table if not EXISTS j_user(

id INT UNSIGNED auto_increment,

name varchar(50) not null,

phone varchar(50),

sex TINYTEXT,

age TINYINT,

bri DATE,

PRIMARY KEY(id))

ENGINE =INNODB DEFAULT CHARSET=utf8;

 

CREATE TABLE jforum_banlist ( banlist_id INT NOT NULL auto_increment, user_id INT, banlist_ip varchar(15), banlist_email varchar(255), PRIMARY KEY (banlist_id), INDEX idx_user (user_id), INDEX (banlist_ip), INDEX (banlist_email))

【运维常用日常sql命令】

MySQL修改邮件
      #mysql -u<用户名> -h<IP地址> -P<端口> -p<密码>

        mysql> use ptdb

        mysql> update test_user set email = "新的邮箱地址" where logon_id = '测试公司名称';

 MySQL修改分测试公司
      #mysql -u<用户名> -h<IP地址> -P<端口> -p<密码>

        mysql> use ptdb

        mysql> select * from test_role where name = 'xxx分测试公司'\G    #查出id

        mysql> select * from test_service_provider where name = "xxxx有限测试公司" \G

        mysql> update test_service_provider set role_id = "xxx分测试公司的id" where name = "xxxx有限测试公司";

        mysql> select * from test_invite_code where company_name = "xxx有限测试公司"\G

        mysql> select * from test_admin_user where admin_name like '%xxx分测试公司%'\G           #查看role是否跟test_invite_code表中的role_id相同,不同则修改,查看id是否跟test_invite_code表中的admin_login_name相同,不同则修改

        mysql> update test_cooperation_info set role_id = '' where service_provider_name like ''; 

MySQL查询每张表的大小
      #mysql -u<用户名> -h<IP地址> -P<端口> -p<密码>

        mysql> use  information_schema

        mysql> select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='ptdb' group by table_name;

MySQL查询每小时的交易笔数
      #mysql -u<用户名> -h<IP地址> -P<端口> -p<密码>

        mysql> use ptdb

        mysql> select hour(trans_time) as '小时', count(*) as '交易笔数' from test_mer_trans where date_format(trans_time, '%Y-%m-%d') = '2019-05-21' group by hour(trans_time);

redis查询接口key和删除key
      #redis-cli -h <IP地址> -p <端口> -a <密码>

查询  get "\xac\xed\x00\x05t\x00:gateway_url_activate:/api/thirdActivity/wechat/oasis/apply"

删除  del "\xac\xed\x00\x05t\x00:gateway_url_activate:/api/thirdActivity/wechat/oasis/apply"

查询某个测试服务商的活跃测试商户
      #mysql -u<用户名> -h<IP地址> -P<端口> -p<密码>

  mysql> use ptdb

  mysql> select count(total) from ( select count(*) as total from test_mer_trans_analyze trans, test_store_info mer where trans.mer_no = mer.mer_id and mer.sp_id = 'S20180919151112EA3E6' group by trans.mer_no)  as result; 

查询所有测试服务商对应分测试公司数据
  mysql> select server_provider.name as 测试服务商名称, if(server_provider.role_id = null or server_provider.role_id = '' or isnull(server_provider.role_id), 'admin', (select role.name from test_role role where role.id = server_provider.role_id)) as 分测试公司名称 from test_service_provider server_provider order by server_provider.status 

查询一段时间每日的活动测试商户数
  mysql> select trans_date,count(*) from test_mer_trans_analyze where trans_date between '2019-05-16' and '2019-06-06' group by trans_date; 

查询所有测试服务商的活跃测试商户数
  mysql> select count(*) from (select count(*) from test_mer_trans_analyze where trans_date between '2019-05-16' and '2019-06-06' group by mer_no) result;