MySQL数据库常用操作入门

一.MySQL

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle旗下产品。在WEB应用方面,MySQL是最好的RDBMS应用软件。MySQL体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。

 

二.建表

环境:MySQL(5.7.20)

CREATE TABLE IF NOT EXISTS `shop`.`spu` (
  `spu_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(64) NOT NULL COMMENT '商品名称',
  `brand_id` int NOT NULL COMMENT '品牌ID',
  `is_online` tinyint(1) unsigned DEFAULT '0' COMMENT '是否在线',
  PRIMARY KEY (`spu_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SPU信息';

默认Linux系统中,数据库名和表名区分大小写,字段名不区分大小写
编码使用utf8或者utf8mb4,utf8mb4是utf8的超集,utf8mb4能存储4字节的表情字符。
KEY包含两层意义(约束和索引),建表语句中KEY等同于INDEX,即普通索引。

 

二.插入

1.INSERT INTO
INSERT INTO `shop`.`spu` VALUES (1, "I Phone 7", 3, 0);  
INSERT INTO `shop`.`spu` (`spu_id`, `name`, `brand_id`, is_online) VALUES (2, "SONY Z3", 7, 1); 

2.INSERT INGORE INTO
INSERT IGNORE INTO `shop`.`spu` (`spu_id`, `name`, `brand_id`, is_online) VALUES (2, "SONY XZ", 7, 1); 

IGNORE插入与INSERT插入功能一样,区别是:如果发现表中已经有此行数据(根据主键或唯一索引判断)则忽略当前插入;否则,插入新数据。

3.REPLACE INTO
REPLACE INTO `shop`.`spu` (`spu_id`, `name`, `brand_id`) VALUES (2, "MI MIX 4", 5); 

REPLACE插入与INSERT插入功能一样,区别是:如果发现表中已经有此行数据(根据主键或唯一索引判断)则先删除此行数据,然后插入新的数据;否则,直接插入新数据。

 

三.查询

SELECT * FROM `shop`.`spu`;

SELECT * FROM `shop`.`sku`;

1.INNER JOIN

SELECT * FROM `shop`.`spu` spu INNER JOIN `shop`.`sku` sku ON spu.`spu_id` = sku.`spu_id` AND spu.`brand_id` > 3 AND sku.`desc` != '64G';


INNER JOIN只返回符合ON条件的记录,同时会过滤LEFT表和RIGHT表(ON中的过滤条件同时有效)。

2.LEFT JOIN

SELECT * FROM `shop`.`spu` spu LEFT JOIN `shop`.`sku` sku ON spu.`spu_id` = sku.`spu_id` AND spu.`brand_id` > 3 AND sku.`desc` != '64G';


LEFT JOIN不管ON上的条件是否为真,都返回LEFT表中的所有记录,ON只起连接作用(ON中的过滤条件对LEFT表不起作用,只对RIGHT表起过滤作用)。

3.RIGHT JOIN

SELECT * FROM `shop`.`spu` spu RIGHT JOIN `shop`.`sku` sku ON spu.`spu_id` = sku.`spu_id` AND spu.`brand_id` > 3 AND sku.`desc` != '64G';


RIGHT JOIN不管ON上的条件是否为真,都返回RIGHT表中的所有记录,ON只起连接作用(ON中的过滤条件对LEFT表起作用,对RIGHT表不起过滤作用)。

 

四.聚合函数

SELECT `spu_id`, COUNT(`price`), COUNT(*), MIN(`price`), MAX(`price`), AVG(`price`), SUM(`price`) FROM `shop`.`sku` GROUP BY `spu_id`;


COUNT(*)不忽略NULL值,COUNT(字段名)忽略NULL值
MIN()、MAX()、AVG()、SUM()等忽略NULL值

 

五.更新

MySQL禁止嵌套更新

1.使用子查询

UPDATE `shop`.`sku` SET `price` = (SELECT * FROM (SELECT MAX(`price`) as max_price FROM `shop`.`sku`) as tmp);


2.使用INNER JOIN

UPDATE `shop`.`sku` sku INNER JOIN (SELECT MAX(`price`) AS max_price FROM `shop`.`sku`) tmp SET sku.`price` = tmp.`max_price`;



六.删除

DELETE FROM `shop`.`spu` WHERE `goods_id` = 1; 
TRUNCATE TABLE `shop`.`spu`; 

TRUNCATE能够快速清空一个表,清空时不写服务器log,执行效率比DELETE高

 

七.导入导出

导出

SELECT * FROM `shop`.`spu` INTO OUTFILE '/tmp/out.csv' FIELDS TERMINATED BY ',';

导入

LOAD DATA INFILE '/tmp/out.csv' INTO TABLE `shop`.`spu` FIELDS TERMINATED BY ',';

 

八.备份还原

备份
root@e0cdff86e1d2:~# mysqldump -u root -p shop spu sku > /tmp/backup.sql
还原
root@e0cdff86e1d2:~# mysql -u root -p shop < /tmp/backup.sql

posted @ 2017-11-19 19:28  faramita2016  阅读(463)  评论(0编辑  收藏  举报