Mysql 知识点汇总 2018

【查询相同数据的SQL】

-- 查相同单号  
select main_waybill_no,count(*) as count from overseas_port_warning_unload_detail group by main_waybill_no having count>1;



打开了之后在此输入 mysql -h你的IP -uroot -p该数据库的密码

例如：mysql -h30.158.59.78 -uroot -pabc123

mysql -htom.xicp.net -uroot -pabc123

my.ini  必须是ASCLL编码字符

启动初始化命令
启动服务
登录即可，无需配置环境变量

最后一个错误提示的原因是plugin的问题，首先使用命令“select user,password,plugin from user”查看一下user表里面的plugin是不是

都是mysql_native_plugin，不是的话，修改之。

mysqld --defaults-file="E:\mysql-5.7.17-winx64\my.ini" --console --skip-grant-tables

#登录命令
mysql -uroot -p
#查看命令
show databases;
use mysql;
#修改数据库系统密码
UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
或者
SET PASSWORD FOR 'root'@'localhost' = 'password';
#刷新数据库
FLUSH PRIVILEGES;
#退出
quit
------------------------------------------------------------------------------
c:mysql\bin\>mysqld -nt --install MySQL

mysql -uroot -p

mysql.sys

mysql -umysql.sys -p

sc delete mysql  #切底删除MYSQL服务
mysqld-nt -remove #删除

--- mysql 服务启动命令
#停止服务命令
net stop mysql     

#启动服务命令
net start mysql 
#查询版本号
mysql -version

#来赋予所有用户远程连接权限，终于成功了.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;

#查看mysql 端口号
show variables like 'port';

#cmd下mysql 格式输出混乱
命令不要用分号结尾用\G
select * from tbname \G

#使用pager设置显示方式，使用pager可以设置调用os的more或者less等显示查询结果，和在os中使用more或者less查看大文件的效果一样
pager more
pager less
\P less
#还原成stdout
nopager
#使用tee保存运行结果到文件
 tee d:\output.txt
 或者
 \T d:\output.txt
 #关闭记录功能
 notee;  
 或者 \t
 
 如：select * from ts_user \T d:\output.txt
 -->;
 -->\t
 
 eg:
 #开启记录到文件功能
  \T d:\output.txt
  #执行sql
  select * from ts_user;
  #结束记录，保存到文本
  \t
  
------------------------------------
 #执行文本中的脚步
source test.sql
#查看服务器版本和当前日期
select version(),current_date; 
select now(); 
select version(); 

#把mysql作为一个简单的计算器
select pi(); 
select pi()*10; 
#查看用户
select user(); 
#使用load
load data local infile filePath into table tableName; 


#创建数据库

#创建数据库test
create database test;
primary key PK_positon (id) #设定主键

#创建表是先判断表是否存在
create table if not exists students(……);
#create table table2 select * from table1 where 1<>1;
create table table2 select * from table1 where 1<>1;

#复制表
create table table2 select * from table1;
#对表重新命名
alter table table1 rename as table2;
#修改列的类型
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned

#创建索引
　　alter table table1 add index ind_id (id);
　　create index ind_id on table1 (id);
　　create unique index ind_id on table1 (id);//建立唯一性索引
#删除索引
　　drop index idx_id on table1;
　　alter table table1 drop index ind_id;
#联合字符或者多个列(将列id与":"和列name和"="连接)
　　select concat(id,':',name,'=') from students;
#limit(选出10到20条)<第一个记录集的编号是0>
　　select * from students order by id limit 9,10;
#MySQL不支持的功能
　　事务，视图，外键和引用完整性，存储过程和触发器
#MySQL会使用索引的操作符号
　　<,<=,>=,>,=,between,in,不带%或者_开头的like
#使用索引的缺点
　　1)减慢增删改数据的速度；
　　2）占用磁盘空间；
　　3）增加查询优化器的负担；
　　当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加工作量，导致无法选择最优的查询方案；
#分析索引效率
　　方法：在一般的SQL语句前加上explain；
　　分析结果的含义：
　　1）table：表名；
　　2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；
　　3）possible_keys：查询可以利用的索引名；
　　4）key：实际使用的索引；
　　5）key_len：索引中被使用部分的长度（字节）；
　　6）ref：显示列名字或者"const"（不明白什么意思）；
　　7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；
　　8）extra：MySQL的建议；
#使用较短的定长列
　　1）尽可能使用较短的数据类型；
　　2）尽可能使用定长数据类型；
　　a）用char代替varchar，固定长度的数据处理比变长的快些；
　　b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；
　　c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；
　　d）对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；
#使用not null和enum
　　尽量将列定义为not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；
　　如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下应该考虑将其转换为enum列的值，MySQL处理的更快，因为所有的enum值在系统内都是以标识数值来表示的；
　　
#使用optimize table
　 　对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查询性能。具有可变长的表都存在磁盘碎片问题，这个问题对blob数据类 型更为突出，因为其尺寸变化非常大。可以通过使用optimize table来整理碎片，保证数据库性能不下降，优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表，然后使用转存后的文件并重新建数据 表；
　　
#使用procedure analyse()
　　可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedure analyse()就可以了；例如：
　　select * from students procedure analyse();
　　select * from students procedure analyse(16,256);
　　第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；
　　
#使用查询缓存
　　1）查询缓存的工作方式：
　　第一次执行某条select语句时，服务器记住该查询的文本内容和查询结果，存储在缓存中，下次碰到这个语句时，直接从缓存中返回结果；当更新数据表后，该数据表的任何缓存查询都变成无效的，并且会被丢弃。
　　2）配置缓存参数：
　 　变量：query_cache _type，查询缓存的操作模式。有3中模式，0：不缓存；1：缓存查询，除非与 select sql_no_cache开头；2：根据需要只缓存那些以select sql_cache开头的查询； query_cache_size：设置查询缓存的最大结果集的大小，比这个值大的不会被缓存。
#调整硬件
　　1）在机器上装更多的内存；
　　2）增加更快的硬盘以减少I/O等待时间；
　　寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读则很快；
　　3）在不同的物理硬盘设备上重新分配磁盘活动；
#查询日志功能是否开启
SHOW VARIABLES LIKE 'general%';
#开启日志
set GLOBAL general_log='ON';
#通用日志查询是否开启
show variables like '%version%';
#日志输出格式
show variables like '%log_output%';
#慢日志状态
show global status like '%slow%';

select sleep(1);

#临时开启慢查询日志
set global slow_query_log = on;
#临时设置慢查询时间临界点
set long_query_time = 1;
#现在起，所有执行时间超过1秒的sql都将被记录到慢查询文件中（我这里就是 /data/mysql/mysql-slow.log）
#设置慢查询存储的方式
set globle log_output = file;

#查询慢查询日志的开启状态和慢查询日志储存的位置
show variables like '%quer%';
#永久设置慢查询日志开启，以及设置慢查询日志时间临界点
#mysql配置文件一般默认在 /mysql/my.ini

#对sql进行优化-------------------------------------------------------
#使用explain查询sql的执行计划

explain select comic_id,name,pen_name,cover,last_verify_time from comic;

#mysql 清屏功能，退出到cmd 命令上 cls 清除，mysql本身无清除命令

------------------------------------
创建授权：grant select on 数据库.* to 用户名@登录主机 identified by \"密码\"
修改密码：mysqladmin -u用户名 -p旧密码 password 新密码
删除授权: revoke select,insert,update,delete om *.* fromtest2@localhost;

显示数据库：show databases;
显示数据表：show tables;
显示表结构：describe 表名;

创建库：create database 库名;
删除库：drop database 库名;
使用库：use 库名;

创建表：create table 表名 (字段设定列表);
删除表：drop table 表名;
修改表：alter table t1 rename t2
查询表：select * from 表名;
清空表：delete from 表名;
备份表: mysqlbinmysqldump -h(ip) -uroot -p(password) databasenametablename > tablename.sql
恢复表: mysqlbinmysql -h(ip) -uroot -p(password) databasenametablename < tablename.sql（操作前先把原来表删除）

增加列：ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADDINDEX (c);
修改列：ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b cCHAR(20);
删除列：ALTER TABLE t2 DROP COLUMN c;

备份数据库：mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename> database.sql
恢复数据库：mysql\bin\mysql -h(ip) -uroot -p(password) databasename< database.sql
复制数据库：mysql\bin\mysqldump --all-databases >all-databases.sql
修复数据库：mysqlcheck -A -o -uroot -p54safer

文本数据导入： load data local infile \"文件名\" into table 表名;
数据导入导出：mysql\bin\mysqlimport database tables.txt

第一招、mysql服务的启动和停止
net stop mysql
net start mysql

第二招、登陆mysql

1.首先需要进入mysql的安装文件夹bin目录下：cd + C:\Program Files\MySQL\MySQL Server 5.5\bin
2.然后连接mysql语法如下： mysql -u用户名-p用户密码
或键入命令mysql -uroot -p，回车后提示你输入密码，输入12345，然后回车即可进入到mysql中了，mysql的提示符是：
mysql>
注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP,不加地址参数，则默认是连接本地地址。

第三招、增加新用户
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
如，增加一个用户user1密码为password1，让其可以在本机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* touser1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
如果你不想user1有密码，可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* touser1@localhost identified by"";

第四招： 操作数据库
登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。
1、 显示数据库列表。
show databases;
缺省有两个数据库：mysql和test。mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。
2、 显示库中的数据表：
use mysql;
show tables;
3、 显示数据表的结构：
describe 表名;
4、 建库与删库：
create database 库名;
drop database 库名;
5、 建表：
use 库名;
create table 表名(字段列表);
drop table 表名;
6、 清空表中记录：
delete from 表名;
7、 显示表中的记录：
select * from 表名;

第五招、导出和导入数据
1. 导出数据：
mysqldump --opt test > mysql.test
即将数据库test数据库导出到mysql.test文件，后者是一个文本文件
如：mysqldump -u root -p123456 --databases dbname >mysql.dbname
就是把数据库dbname导出到文件mysql.dbname中。
2. 导入数据:
mysqlimport -u root -p123456 < mysql.dbname。
不用解释了吧。
3. 将文本数据导入数据库:
文本数据的字段数据之间用tab键隔开。
use test;
load data local infile "文件名" into table 表名;
1:使用SHOW语句找出在服务器上当前存在什么数据库：
mysql> SHOW DATABASES;
2:2、创建一个数据库MYSQLDATA
mysql> CREATE DATABASE MYSQLDATA;
3:选择你所创建的数据库
mysql> USE MYSQLDATA; (按回车键出现Database changed时说明操作成功！)
4:查看现在的数据库中存在什么表
mysql> SHOW TABLES;
5:创建一个数据库表
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sexCHAR(1));
6:显示表的结构：
mysql> DESCRIBE MYTABLE;
7:往表中加入记录
mysql> insert into MYTABLE values ("hyq","M");
8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTOTABLE MYTABLE;
9:导入.sql文件命令（例如D:/mysql.sql）
mysql>use database;
mysql>source d:/mysql.sql;
10:删除表
mysql>drop TABLE MYTABLE;
11:清空表
mysql>delete from MYTABLE;
12:更新表中数据
mysql>update MYTABLE set sex="f" where name='hyq';13：备份数据库mysqldump -u root库名>xxx.data14：

例2：连接到远程主机上的MYSQL
　　假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：　　　

　　mysql-h110.110.110.110 -uroot -pabcd123 　　

　　（注:u与root可以不用加空格，其它也一样） 　　

　　3、退出MYSQL命令：exit

(一) 连接MYSQL：

　　1、首先:打开CMD命令窗，输入cd mysql.exe所在路径
      2、然后输入： mysql -h主机地址 -P端口 -u用户名－p用户密码

　　（注：如果连接本地数据库，默认可以不写-h,如果数据库端口默认的3306没有被更改，也可以不写-P；

　   　反之若远程非本地（他人电脑上）的mysql必须写-h地址，默认的3306端口若已被修改，也必须写-P端口。）

　　3、显示了welcome to the monitor则成功连接数据库。

1、例1：连接到本机上的MYSQL
  首先在打开DOS窗口，然后进入mysql安装目录下的bin目录下，例如： D:\mysql\bin，再键入命令mysql -uroot-p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql>
2、例2：连接到远程主机上的MYSQL
  假设远程主机的IP为：10.0.0.1，用户名为root,密码为123。则键入以下命令：
   mysql -h10.0.0.1 -uroot-p123
（注：u与root可以不用加空格，其它也一样）
3、退出MYSQL命令
   exit （回车）

(二) 修改密码：
   格式：mysqladmin -u用户名 -p旧密码 -password 新密码
1、例1：给root加个密码123。首先在DOS下进入目录C:\mysql\bin，然后键入以下命令：
   mysqladmin -uroot -password123
  注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
2、例2：再将root的密码改为456
   mysqladmin -uroot -pab12password 456
(三) 增加新用户：（注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符）
   格式：grant select on 数据库.* to用户名@登录主机 identified by "密码"
  例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：
   grantselect,insert,update,delete on *.* to test1@"%" Identified by"abc";

  但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见例2。
  例2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。
   grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby "abc";
  如果你不想test2有密码，可以再打一个命令将密码消掉。
   grantselect,insert,update,delete on mydb.* to test2@localhost identifiedby "";
(四) 显示命令
1、显示数据库列表：
   show databases;
  刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示库中的数据表：
   use mysql;      //打开库
   show tables;
3、显示数据表的结构：
   describe 表名;
4、建库：
   create database 库名;
5、建表：
   use 库名;
   create table 表名(字段设定列表)；
6、删库和删表:
   drop database 库名;
   drop table 表名;
7、将表中记录清空：
   delete from 表名;
8、显示表中的记录：
   select * from 表名;

MySQL导入导出命令

　　首先cd进入mysql.exe所在路径。若导出非本地（他人机器上）数据库，必须加上-P端口、-h地址

　　下列123导出无需连接数据库，导入需先连接数据库。

1.导出整个数据库

　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名（文件名前可指定：导出sql文件的保存路径）
　　示例：mysqldump -u root -p fdm > F://fdm.sql
2.导出一个表
　　mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名（文件名前可指定：导出sql文件的保存路径）
　　示例：mysqldump -u root -p fdm p_code> F://fdm.sql

3.导出一个数据库结构
　　mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc>d:wcnc_db.sql
　　-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库脚本
　　连接mysql数据库，
　　如mysql -u root -p
　　mysql>use 数据库
　　然后使用source命令，后面参数为脚本文件(*.sql)
　　mysql>source d:wcnc_db.sql