Mysql

Summary: Author: 张亚飞 | Read Time: 6 minute read | Published: 2016-05-13
Filed under — Categories: Linux — Tags: Note,

Navicat11全系列激活(注册机)

数据库操作相关

创建数据库

mysql> CREATE DATABASE Coam_Data DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

删除数据库

mysql> DROP DATABASE Coam_Data;

11.1.6 Configuring the Character Set and Collation for Applications

mysqldump

导出数据库:

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u root -p Coam_Data > Coam_Data.sql   (输入后会让你输入进入MySQL的密码)

(如果导出单张表的话在数据库名后面输入表名即可)

同时备份多个数据库

mysqldump -h hostname -u username -p password --databases db_name1 db_name2 db_namen >backup.sql

只备份数据库的结构,不备份数据

mysqldump -h hostname -u username -p password --no-data --databases db_name1 db_name2 db_namen >backup.sql

备份所有的数据库

mysqldump -h hostname -u username -p password --all-databases >backup.sql

// 其它

mysqldump —opt —skip-comments —extended-insert=false -uroot -p1234 inotseeyou_com > inotseeyou_com.sql

导入数据库

首先创建相应的 Database

CREATE DATABASE Coam_Data DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
use Coam_Data;
source Coam_Data.sql;

mysqldump.html
从一个数据库导入

从一台 Mysql 数据库 WordPress 导入到令一台 Mysql 数据库 cs_data

mysqldump -h 1.ucs.iirii.com -u zhangyanxi -v WordPress -p****** | mysql -u root -p****** -D cs_data
mysqldump -h 1.ucs.iirii.com -u zhangyanxi -v WordPress -p****** | mysql -h 1.ucs.iirii.com -u zhangyanxi -p****** -D cs_data

用户相关

查看所有用户

mysql> SELECT User, Host, Password FROM mysql.user;

添加新用户 - 以下方式适用于 5.7 以前的版本

mysql> INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword'));

添加新用户 - 以下方式适用于 5.7 以后的版本

mysql> GRANT USAGE ON *.* TO 'zhangyanxi'@'localhost' IDENTIFIED BY 'yanxi312' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'zhangyanxi'@'localhost';        ======> 给用户授权
mysql> flush privileges;

给用户授权相关

mysql> GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
GRANT SELECT, INSERT ON *.* TO 'zhangyanxi'@'localhost';
GRANT ALL ON *.* TO 'zhangyanxi'@'%';

检查用户授权

mysql> SHOW GRANTS FOR 'demouser'@'localhost';

在当前服务器 [47.90.15.40] 设定可远程访问的用户 zhangyanxi@47.90.15.40

mysql> GRANT USAGE ON *.* TO 'zhangyanxi'@'47.90.15.40' IDENTIFIED BY 'yanxi312' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'zhangyanxi'@'47.90.15.40';        ======> 给用户授权
mysql> flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘47.90.15.40’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘47.90.15.40’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘45.32.80.56’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘45.32.80.56’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘43.241.222.110’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘43.241.222.110’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘103.37.147.250’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘103.37.147.250’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘118.190.133.208’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘118.190.133.208’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘118.190.6.78’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘118.190.6.78’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘47.89.12.166’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘47.89.12.166’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘47.244.154.194’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘47.244.154.194’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘119.96.6.245’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘119.96.6.245’; flush privileges;

GRANT USAGE ON . TO ‘zhangyanxi’@‘%’ IDENTIFIED BY ‘yanxi312’ WITH GRANT OPTION; GRANT ALL ON . TO ‘zhangyanxi’@‘%’; flush privileges;

删除指定用户

# 本地远程访问 Mysql 数据账号
DROP USER 'zhangyanxi'@'127.0.0.1';
DROP USER 'zhangyanxi'@'47.90.15.40';
DROP USER 'zhangyanxi'@'59.173.224.118';
DROP USER 'zhangyanxi'@'119.96.6.245';

#  Mysql 主从同步复制账号
DROP USER 'syBackup'@'47.90.15.40';
DROP USER 'syBackup'@'103.37.147.250';
DROP USER 'syBackup'@'43.241.222.110';
DROP USER 'syBackup'@'45.32.80.56';
DROP USER 'syBackup'@'47.90.15.40';

sql 语句将查询结果中数字等标示转成汉字

select name,age,sex,case when state=0 then '正常' when state=1 then '删除' else '禁用' end state from student

Mysql按日、周、月进行分组统计

1）按天统计：

select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;

2）按周统计：

select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;

3）按月统计:

select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;

Mysql 聚合函数

count max min avg sum

mysql给已有表添加/删除外键和唯一键

添加外键的语句:

ALTER TABLE 表名ADD CONSTRAINT 外键名
FOREIGN KEY (需要设为外键的字段)
REFERENCES 参考表名(参考字段);

删除外键的语句:

ALTER TABLE 表名
DROP FOREIGN KEY 外键名;

添加唯一键的语句:

ALTER TABLE 表名
DROP INDEX 唯一键名;

注:如果需要删除唯一键的列也有外键约束的话,需要首先删除外键约束.

Msyql设置多列唯一

mysql的索引可以分为单列索引和多列索引

mysql 可以为多个列创建一个索引,最多可以16列,多列索引可以视为包含通过连接索引列的值而创建值的排序数组.mysql多列索引适用场合:当你在where 子句中为索引的第1个列指定已知的参数时,查询很快,即使你没有指定其它列的值,这里的其他列是多列索引里面,指定的其他列.

一,创建测试表index_test

mysql> CREATE TABLE `index_test` (
 -> `id` int(11) NOT NULL auto_increment,
 -> `user_id` int(11) NOT NULL,
 -> `username` varchar(20) NOT NULL,
 -> PRIMARY KEY (`id`)
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 Query OK, 0 rows affected (0.08 sec)

二,创建多列索引

1,普通多列索引

mysql> ALTER TABLE index_test ADD index test( user_id, username );
 Query OK, 0 rows affected (0.07 sec)
 Records: 0 Duplicates: 0 Warnings: 0

2,多列唯一索引

mysql> ALTER TABLE index_test ADD unique test( user_id, username );
 Query OK, 0 rows affected (0.06 sec)
 Records: 0 Duplicates: 0 Warnings: 0

3,多列主键索引

mysql> ALTER TABLE index_test ADD primary key test( user_id, username );
 Query OK, 0 rows affected (0.06 sec)
 Records: 0 Duplicates: 0 Warnings: 0

alter 删除主键约束

alter table temp drop primary key;

我们主键一般都是ID,并且是自增长的,如果有,就要先删除主键后在创建多列主键索引,不然会报错的,ERROR 1068 (42000): Multiple primary key defined;

查看表创建的所有index

SHOW INDEX FROM Table

删除索引,可以用

drop index test on index_test

MYSQL修改自增起始索引

ALTER TABLE CloudFileORM AUTO_INCREMENT=1333

但是如果设置的n比目前的数值小的话,执行的sql不会报错,但是不会生效!MyISAM和Innodb均是如此.

参考列表

mysql 多列索引详细说明

修改列

一次修改单列

ALTER TABLE `wp_comments` CHANGE `comment_date` `comment_date` DATETIME NOT NULL;

一次修改多列

ALTER TABLE `wp_comments` 
CHANGE `comment_date` `comment_date` DATETIME NOT NULL, 
CHANGE `comment_date_gmt` `comment_date_gmt` DATETIME NOT NULL;

How to ALTER multiple columns at once in SQL Server

日期格式加减

select date_add(now(), interval 1 day); - 加1天
select date_add(now(), interval 1 hour); -加1小时
select date_add(now(), interval 1 minute); - 加1分钟
select date_add(now(), interval 1 second); -加1秒
select date_add(now(), interval 1 microsecond);-加1毫秒
select date_add(now(), interval 1 week);-加1周
select date_add(now(), interval 1 month);-加1月
select date_add(now(), interval 1 quarter);-加1季
select date_add(now(), interval 1 year);-加1年

UPDATE mall_ticket_depart_date_release SET depart_date = date_add(depart_date, interval 1 day) WHERE id =1;

日期条件筛选

select * from mall_product_ticket_release where (UNIX_TIMESTAMP() + ahead_ts) < (UNIX_TIMESTAMP(depart_date) + 86400);

查看mysql当前表使用的存储引擎

InnoDB: 支持事物|MyISAM:不支持事物

确认 MySQL 服务器是否启用InnoDB存储引擎

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查看表创建的详情

mysql> SHOW CREATE TABLE Order_TruckInfo\G;
*************************** 1. row ***************************
       Table: Order_TruckInfo
Create Table: CREATE TABLE `Order_TruckInfo` (
  `oId` int(12) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  `oAsId` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'C-0' COMMENT '客户ASId',
  `soId` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '绑定签约服务号',
  `signId` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '签约服务承运方Id',
  `orderName` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '暂无标题' COMMENT '订单标题',
  `orderInfo` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `ton` float NOT NULL DEFAULT '0' COMMENT '货物吨位.吨',
  `square` float NOT NULL DEFAULT '0' COMMENT '体积.方',
  `orderTruckType` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'GLFT' COMMENT '下单需求货车型号',
  `orderTruckLength` float NOT NULL DEFAULT '13' COMMENT '订单所需车长',
  `startTime` timestamp NULL DEFAULT NULL COMMENT '预定起始时间',
  `endTime` timestamp NULL DEFAULT NULL COMMENT '预定终止时间',
  `startAreaSerial` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '000000' COMMENT '起始地址区域序列号',
  `startLat` double NOT NULL DEFAULT '0' COMMENT '起始地点纬度',
  `startLng` double NOT NULL DEFAULT '0' COMMENT '起始地点经度',
  `startLocation` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT '武汉' COMMENT '起始地点',
  `endAreaSerial` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '000000' COMMENT '终止地址区域序列号',
  `endLat` double NOT NULL DEFAULT '0' COMMENT '终止地点纬度',
  `endLng` double NOT NULL DEFAULT '0' COMMENT '终止地点经度',
  `endLocation` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT '上海' COMMENT '终止地点',
  `shipper` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '未指定发货方' COMMENT '发货方',
  `shipperContact` double NOT NULL DEFAULT '0' COMMENT '发货方联系方式',
  `consignee` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '未指定收货方' COMMENT '收货方',
  `consigneeContact` double NOT NULL DEFAULT '0' COMMENT '收货方联系方式',
  `orderDetailRequires` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '暂无特殊详细要求' COMMENT '客户详细要求',
  `distance` double NOT NULL DEFAULT '0' COMMENT '行程长度',
  `referFreight` float NOT NULL DEFAULT '0' COMMENT '参考订单运价',
  `oState` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT 'A' COMMENT 'A申请中,B审核,C客户已确认订单,D在运行中,E完成,F已确认收货,G忽略',
  `orderInfoAuth` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'CustomerPublicTruckOrder' COMMENT '订单展示管理方式 CustomerPublicTruckOrder:客户发布的整车货运订单;CustomerPublicPipelineOrder:客户发布的零担专线订单;PartnerInnerOrder:合作伙伴内部管理整车订单;LogisticsInnerOrder:物流商内部管理零担物流订单;',
  `orderTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单编号时间戳',
  PRIMARY KEY (`oId`)
) ENGINE=InnoDB AUTO_INCREMENT=1103 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='商运主页订单'
1 row in set (0.00 sec)

或者以下两种方式:

mysql> SHOW TABLE STATUS FROM Coam_Data WHERE Name='Order_TruckInfo'\G;
*************************** 1. row ***************************
           Name: Order_TruckInfo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 879
 Avg_row_length: 354
    Data_length: 311296
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1103
    Create_time: 2016-12-02 18:28:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: 商运主页订单

# mysqlshow  -uzhangyanxi -p --status Coam_Data Order_TruckInfo

查看mysql当前表使用的存储引擎

备用

MySQL 命令行工具

mysql -h rm-2zep0d51g118f62o5.mysql.rds.aliyuncs.com -u db_admin -p's8n73b@2ps&6k' -P3306 --database=vs_data --default-auth=mysql_native_password

Cor Ethan