Mysql

Summary: Author: 张亚飞 | 阅读时间: 5 minute read | Published: 2016-05-13
Filed under Categories: LinuxTags: Note,

数据库操作相关

  • 创建数据库
mysql> CREATE DATABASE Coam_Data DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
  • 删除数据库
mysql> DROP DATABASE Coam_Data;

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;

用户相关

  • 查看所有用户
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’@‘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’@‘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';

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

添加外键的语句:

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

删除外键的语句:

ALTER TABLE 表名
DROP FOREIGN KEY 外键名;

添加唯一键的语句:

ALTER TABLE 表名
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均是如此.

参考列表


修改列

  • 一次修改单列
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;

  • 日期格式加减
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

Comments

Cor-Ethan, the beverage → www.iirii.com