[大数据] CP7 Doing it for us with MySQL

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> CP7 Doing it for us with MySQL -> 正文阅读

[大数据]CP7 Doing it for us with MySQL

1.Inserting Data Using Queries

mysql> CREATE TABLE recommend (
? ? -> ? film_id SMALLINT UNSIGNED,
? ? -> ? language_id TINYINT UNSIGNED,
? ? -> ? release_year YEAR,
? ? -> ? title VARCHAR(128),
? ? -> ? length SMALLINT UNSIGNED,
? ? -> ? sequence_id SMALLINT AUTO_INCREMENT,
? ? -> ? PRIMARY KEY (sequence_id)
? ? -> ? );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO recommend (film_id, language_id, release_year, title, length)
? ? -> SELECT film_id, language_id, release_year, title, length
? ? -> FROM film ORDER BY RAND() LIMIT 10;?
Query OK, 10 rows affected (0.00 sec)
Records: 10 ?Duplicates: 0 ?Warnings: 0

mysql> SELECT * FROM recommend;
+---------+-------------+--------------+------------------------+--------+-------------+
| film_id | language_id | release_year | title ? ? ? ? ? ? ? ? ?| length | sequence_id |
+---------+-------------+--------------+------------------------+--------+-------------+
| ? ? 466 | ? ? ? ? ? 1 | ? ? ? ? 2006 | INTOLERABLE INTENTIONS | ? ? 63 | ? ? ? ? ? 1 |
| ? ? ?15 | ? ? ? ? ? 1 | ? ? ? ? 2006 | ALIEN CENTER ? ? ? ? ? | ? ? 46 | ? ? ? ? ? 2 |
| ? ? 959 | ? ? ? ? ? 1 | ? ? ? ? 2006 | WARLOCK WEREWOLF ? ? ? | ? ? 83 | ? ? ? ? ? 3 |
| ? ? 979 | ? ? ? ? ? 1 | ? ? ? ? 2006 | WITCHES PANIC ? ? ? ? ?| ? ?100 | ? ? ? ? ? 4 |
| ? ? 782 | ? ? ? ? ? 1 | ? ? ? ? 2006 | SHAKESPEARE SADDLE ? ? | ? ? 60 | ? ? ? ? ? 5 |
| ? ? 208 | ? ? ? ? ? 1 | ? ? ? ? 2006 | DARES PLUTO ? ? ? ? ? ?| ? ? 89 | ? ? ? ? ? 6 |
| ? ? 101 | ? ? ? ? ? 1 | ? ? ? ? 2006 | BROTHERHOOD BLANKET ? ?| ? ? 73 | ? ? ? ? ? 7 |
| ? ? 931 | ? ? ? ? ? 1 | ? ? ? ? 2006 | VALENTINE VANISHING ? ?| ? ? 48 | ? ? ? ? ? 8 |
| ? ? 644 | ? ? ? ? ? 1 | ? ? ? ? 2006 | OSCAR GOLD ? ? ? ? ? ? | ? ?115 | ? ? ? ? ? 9 |
| ? ? 765 | ? ? ? ? ? 1 | ? ? ? ? 2006 | SATURN NAME ? ? ? ? ? ?| ? ?182 | ? ? ? ? ?10 |
+---------+-------------+--------------+------------------------+--------+-------------+
10 rows in set (0.00 sec)

The SELECT statement in an Insert Into statement can use all? of the usual features of select statements.

you'll encounter duplication issue when inserting with a select statement.if you try to insert the same primary key value twice,MySQL will abort.

mysql> INSERT INTO recommend (film_id, language_id, release_year,
? ? -> title, length, sequence_id )
? ? -> SELECT film_id, language_id, release_year, title, length, 1
? ? -> FROM film LIMIT 1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> INSERT IGNORE INTO recommend (film_id, language_id, release_year,
? ? -> title, length, sequence_id )
? ? -> SELECT film_id, language_id, release_year, title, length, 1
? ? -> FROM film LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 1 ?Duplicates: 1 ?Warnings: 1

mysql>?

?Note that it is possible to insert into a table that's listed in the select statement,but you still need to avoid duplicate primary key.

mysql> Insert into actor select actor_id,first_name,last_name,now() from actor;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> Insert into actor(first_name,last_name,last_update) select first_name,last_name,now() from actor limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 ?Duplicates: 0 ?Warnings: 0

mysql> insert into actor select actor_id+200,first_name,last_name,now() from actor;
ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'
mysql> insert into actor select actor_id+400,first_name,last_name,now() from actor;?
Query OK, 201 rows affected (0.01 sec)
Records: 201 ?Duplicates: 0 ?Warnings: 0

mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
| ? ? ?402 |
+----------+
1 row in set (0.00 sec)

mysql> select * from actor; ? ? ??
+----------+-------------+--------------+---------------------+
| actor_id | first_name ?| last_name ? ?| last_update ? ? ? ? |
+----------+-------------+--------------+---------------------+
| ? ? ? ?1 | PENELOPE ? ?| GUINESS ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?2 | NICK ? ? ? ?| WAHLBERG ? ? | 2006-02-15 04:34:33 |
| ? ? ? ?3 | ED ? ? ? ? ?| CHASE ? ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?4 | JENNIFER ? ?| DAVIS ? ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?5 | JOHNNY ? ? ?| LOLLOBRIGIDA | 2006-02-15 04:34:33 |

2.Loading Data from Comma-Delimited Files

mysql> create database nasa;
Query OK, 1 row affected (0.00 sec)

mysql> use nasa;
Database changed
mysql>?
mysql> create table facilities (
? ? -> center TEXT,
? ? -> center_search_status TEXT,
? ? -> facility TEXT,
? ? -> facility_url TEXT,
? ? -> occupied TEXT,
? ? -> status TEXT,
? ? -> url_link TEXT,
? ? -> record_date DATETIME,
? ? -> last_update TIMESTAMP NULL,
? ? -> country TEXT,
? ? -> contact TEXT,
? ? -> phone TEXT,
? ? -> location TEXT,
? ? -> city TEXT,
? ? -> state TEXT,
? ? -> zipcode TEXT
? ? -> );
Query OK, 0 rows affected (0.02 sec)
?

Now that we've set up the database table, we can import the data from the file using the LOAD DATA INFILE command:

mysql> LOAD DATA INFILE 'NASA_Facilities.csv' INTO TABLE facilities Fields terminated By ',';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> select @@secure_file_priv;
+-----------------------+
| @@secure_file_priv ? ?|
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.00 sec)
?

You need to copy the file into that directory.

[root@DBAMAXWELL ~]# ls -ltr
total 28272
-rw-r--r-- ?1 root ?root ?25372998 Dec ?8 ?2020 Python-3.9.1.tgz
drwxr-xr-x ?2 ? 500 ? 500 ? ? ? 23 Feb ?1 07:06 world-db
-rw-r--r-- ?1 root ?root ? ? 92914 Feb ?1 07:06 world-db.tar.gz
drwxr-xr-x ?2 ? 500 ? 500 ? ? ? 72 Feb ?1 07:06 sakila-db
-rw-r--r-- ?1 root ?root ? ?732134 Feb ?1 07:06 sakila-db.tar.gz
-rw-r--r-- ?1 root ?root ? 2609803 Feb ?3 17:45 get-pip.py
-rw-r--r-- ?1 root ?root ? ? ?4942 Feb 10 21:53 wget-log
drwxr-xr-x 18 admin admin ? ? 4096 Feb 11 14:01 Python-3.9.1
-rw-r--r-- ?1 root ?root ? ? ? ? 0 Feb 15 10:54 first.db
drwxr-xr-x ?5 root ?root ? ? ?4096 Feb 22 14:00 test_db
drwxr-xr-x ?2 root ?root ? ? ? ? 6 Mar ?6 11:37 learning_bash_shell
drwxr-xr-x 10 root ?root ? ? ? 322 Mar 11 23:06 mysql-sys
-rw-r--r-- ?1 root ?root ? ?116480 Mar 12 11:44 NASA_Facilities.csv
[root@DBAMAXWELL ~]# pwd
/root
[root@DBAMAXWELL ~]# sudo cp -vip /root/NASA_Facilities.csv /var/lib/mysql-files
'/root/NASA_Facilities.csv' -> '/var/lib/mysql-files/NASA_Facilities.csv'
[root@DBAMAXWELL ~]# sudo chown mysql:mysql /var/lib/mysql-files/NASA_Facilities.csv
[root@DBAMAXWELL ~]# sudo ls -lh /var/lib/mysql-files/NASA_Facilities.csv
-rw-r--r-- 1 mysql mysql 114K Mar 12 11:44 /var/lib/mysql-files/NASA_Facilities.csv
[root@DBAMAXWELL ~]#?

mysql> LOAD DATA INFILE '/var/lib/mysql-files/NASA_Facilities.csv' INTO TABLE facilities FIELDS TERMINATED BY ',';
ERROR 1292 (22007): Incorrect datetime value: 'Record Date' for column 'record_date' at row 1
mysql> LOAD DATA INFILE '/var/lib/mysql-files/NASA_Facilities.csv' INTO TABLE facilities FIELDS TERMINATED BY ',' IGNORE 1 LINES;
ERROR 1292 (22007): Incorrect datetime value: '03/01/1996 12:00:00 AM' for column 'record_date' at row 1
mysql>?
mysql> SELECT STR_TO_DATE('03/01/1996 12:00:00 AM','%m/%d/%Y %h:%i:%s %p') converted;
+---------------------+
| converted ? ? ? ? ? |
+---------------------+
| 1996-03-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/NASA_Facilities.csv'
? ? -> INTO TABLE facilities FIELDS TERMINATED BY ','
? ? -> OPTIONALLY ENCLOSED BY '"'
? ? -> IGNORE 1 LINES
? ? -> (center,center_search_status,facility,facility_url,
? ? -> occupied,status,url_link,@var_record_date,@var_last_update,
? ? -> country,contact,phone,location,city,state,zipcode)
? ? -> SET record_date = IF(
? ? -> CHAR_LENGTH(@var_record_date)=0,NULL,
? ? -> ? STR_TO_DATE(@var_record_date,'%m/%d/%Y %h:%i:%s %p')
? ? -> ),
? ? -> last_update = IF(
? ? -> ? CHAR_LENGTH(@var_last_update)=0,NULL,
? ? -> ? ? STR_TO_DATE(@var_last_update,'%m/%d/%Y %h:%i:%s %p') ? ?
? ? -> );
Query OK, 485 rows affected (0.02 sec)
Records: 485 ?Deleted: 0 ?Skipped: 0 ?Warnings: 0

mysql>?

?3.Writing Data into Comma-Delimited Files

You can use the Select into outfile statement to write out the result of a query into a CSV file that can be opened by a spreadsheet or other program.

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select emp_no,first_name,last_name,title,from_date
? ? -> from employees join titles using (emp_no)
? ? -> where title = 'Manager' AND to_date = '9999-01-01'
? ? -> INTO OUTFILE '/var/lib/mysql-files/managers.csv' ? ? ? ? ? ? ? ??
? ? -> FIELDS TERMINATED BY ',';
Query OK, 9 rows affected (0.20 sec)

mysql> exit
Bye
[root@DBAMAXWELL ~]# cat /var/lib/mysql-files/managers.csv
110039,Vishwani,Minakawa,Manager,1991-10-01
110114,Isamu,Legleitner,Manager,1989-12-17
110228,Karsten,Sigstam,Manager,1992-03-21
110420,Oscar,Ghazalie,Manager,1996-08-30
110567,Leon,DasSarma,Manager,1992-04-25
110854,Dung,Pesch,Manager,1994-06-28
111133,Hauke,Zhang,Manager,1991-03-07
111534,Hilary,Kambil,Manager,1991-04-08
111939,Yuchang,Weedman,Manager,1996-01-03

mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>?
mysql> select title,special_features from film limit 10
? ? -> INTO OUTFILE '/var/lib/mysql-files/film_quoted.csv' ? ? ? ? ?
? ? -> FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 10 rows affected (0.00 sec)

mysql> EXIT
Bye
[root@DBAMAXWELL ~]# cat /var/lib/mysql-files/film_quoted.csv
"ACADEMY DINOSAUR","Deleted Scenes,Behind the Scenes"
"ACE GOLDFINGER","Trailers,Deleted Scenes"
"ADAPTATION HOLES","Trailers,Deleted Scenes"
"AFFAIR PREJUDICE","Commentaries,Behind the Scenes"
"AFRICAN EGG","Deleted Scenes"
"AGENT TRUMAN","Deleted Scenes"
"AIRPLANE SIERRA","Trailers,Deleted Scenes"
"AIRPORT POLLOCK","Trailers"
"ALABAMA DEVIL","Trailers,Deleted Scenes"
"ALADDIN CALENDAR","Trailers,Deleted Scenes"
[root@DBAMAXWELL ~]#?

??4.Creating Tables with Queries

You can create a table or easily? create a copy of a table using a query.This is useful when you want to build a new database using existing data. Data reorganization is common when producing reports,merging data from two or more tables,and redesigning on the fly.

mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table actor_2 LIKE actor;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from actor_2;
Empty set (0.00 sec)

mysql> DROP TABLE actor_2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table actor_2 as select * from actor;
Query OK, 402 rows affected (0.02 sec)
Records: 402 ?Duplicates: 0 ?Warnings: 0

mysql> select * from actor_2 limit 5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name ? ?| last_update ? ? ? ? |
+----------+------------+--------------+---------------------+
| ? ? ? ?1 | PENELOPE ? | GUINESS ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?2 | NICK ? ? ? | WAHLBERG ? ? | 2006-02-15 04:34:33 |
| ? ? ? ?3 | ED ? ? ? ? | CHASE ? ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?4 | JENNIFER ? | DAVIS ? ? ? ?| 2006-02-15 04:34:33 |
| ? ? ? ?5 | JOHNNY ? ? | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql> create table report (title varchar(128),category varchar(25)) select title,name as category from film join film_category using (film_id) join category using (category_id);
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 ?Duplicates: 0 ?Warnings: 0

mysql>?

mysql> describe actor_2;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field ? ? ? | Type ? ? ? ? ? ? ? ? | Null | Key | Default ? ? ? ? ? | Extra ? ? ? ? ? ? ? ? ? ? ? |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id ? ?| smallint(5) unsigned | NO ? | ? ? | 0 ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| first_name ?| varchar(45) ? ? ? ? ?| NO ? | ? ? | NULL ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| last_name ? | varchar(45) ? ? ? ? ?| NO ? | ? ? | NULL ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| last_update | timestamp ? ? ? ? ? ?| NO ? | ? ? | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

mysql> show create table actor_2\G
*************************** 1. row ***************************
? ? ? ?Table: actor_2
Create Table: CREATE TABLE `actor_2` (
? `actor_id` smallint(5) unsigned NOT NULL DEFAULT '0',
? `first_name` varchar(45) CHARACTER SET utf8mb4 NOT NULL,
? `last_name` varchar(45) CHARACTER SET utf8mb4 NOT NULL,
? `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table actor_2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table actor_2 (unique(actor_id)) as select * from actor;
Query OK, 402 rows affected (0.02 sec)
Records: 402 ?Duplicates: 0 ?Warnings: 0

mysql> describe actor_2;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field ? ? ? | Type ? ? ? ? ? ? ? ? | Null | Key | Default ? ? ? ? ? | Extra ? ? ? ? ? ? ? ? ? ? ? |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id ? ?| smallint(5) unsigned | NO ? | PRI | 0 ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| first_name ?| varchar(45) ? ? ? ? ?| NO ? | ? ? | NULL ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| last_name ? | varchar(45) ? ? ? ? ?| NO ? | ? ? | NULL ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| last_update | timestamp ? ? ? ? ? ?| NO ? | ? ? | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

mysql>?
mysql> create table actor_3 (
? ? -> actor_id smallint unsigned not null auto_increment,
? ? -> first_name varchar(45) not null,
? ? -> last_name varchar(45) not null,
? ? -> last_update timestamp not null
? ? -> default current_timestamp on update current_timestamp,
? ? -> primary key (actor_id),
? ? -> key idx_actor_last_name (last_name)
? ? -> ) select * from actor;
Query OK, 402 rows affected (0.02 sec)
Records: 402 ?Duplicates: 0 ?Warnings: 0

mysql>?

?
?5.Performing Updates and Deletes with Multiple Tables?

Deletion

mysql>?
mysql> select * from inventory where not exists
? ? -> (select 1 from rental where rental.inventory_id = inventory.inventory_id);
+--------------+---------+----------+---------------------+
| inventory_id | film_id | store_id | last_update ? ? ? ? |
+--------------+---------+----------+---------------------+
| ? ? ? ? ? ?5 | ? ? ? 1 | ? ? ? ?2 | 2006-02-15 05:09:17 |
+--------------+---------+----------+---------------------+
1 row in set (0.01 sec)

mysql> DELETE FROM inventory where not exists
? ? -> (select 1 from rental where
? ? -> rental.inventory_id = inventory.inventory_id);
Query OK, 1 row affected (0.02 sec)

While this statement does indeed delete rows from one table based on data from another table.it's basically a variation of a regular DELETE.To convert this particular statement into a multitable DELETE,we should switch from a nested subquery to a left join Like so:

mysql> DELETE inventory FROM inventory left join rental using (inventory_id) where rental.inventory_id is NULL;
Query OK, 0 rows affected (0.01 sec)

mysql> DELETE FROM inventory using inventory left join rental using (inventory_id) where rental.inventory_id is null;
Query OK, 0 rows affected (0.01 sec)

The first one is to execute a series of DELETE statements within one transaction

mysql> Begin;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM film_actor using?
? ? -> film join film_actor using (film_id)
? ? -> left join inventory using (film_id)
? ? -> where inventory.film_id is null;
Query OK, 216 rows affected (0.01 sec)

mysql> DELETE FROM film_category using
? ? -> film join film_category using (film_id)
? ? -> left join inventory using (film_id)
? ? -> where inventory.film_id is null;
Query OK, 42 rows affected (0.00 sec)

mysql> DELETE FROM film using?
? ? -> film left join inventory using (film_id)
? ? -> where inventory.film_id is null;
Query OK, 42 rows affected (0.02 sec)

mysql> Rollback;
Query OK, 0 rows affected (0.00 sec)

You can see that we executed Rollback instead of COMMIT to preserve the rows.In reality,you would of course use commit to "save" the result of your operation.

We can do better than having to run three queries

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM film_actor,film_category using
? ? -> film join film_actor using (film_id)
? ? -> join film_category using (film_id)
? ? -> left join inventory using (film_id)
? ? -> where inventory.film_id is null;
Query OK, 258 rows affected (0.01 sec)

mysql> DELETE FROM film using?
? ? -> film left join inventory using (film_id)
? ? -> where inventory.film_id is null;
Query OK, 42 rows affected (0.01 sec)

mysql> Rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>?

UPDATE

illustrate multiple-table? updates

mysql>
mysql> select count(title)
? ? -> from film join film_category using (film_id)
? ? -> join category using (category_id)
? ? -> where name = 'Horror' and rating <> 'R';
+--------------+
| count(title) |
+--------------+
| ? ? ? ? ? 42 |
+--------------+
1 row in set (0.00 sec)

mysql> UPDATE film join film_category using (film_id)
? ? -> join category using (category_id)
? ? -> set rating = 'R' where category.name = 'Horror';
Query OK, 42 rows affected (0.01 sec)
Rows matched: 56 ?Changed: 42 ?Warnings: 0

mysql>?

?As with multitable deletes ,there are some limitations on multitable updates:

You can't use ORDER BY.
You can't use Limit
You can't update a table that's read from in a nested subquery.

Multitable updates are much the same as single-table ones.

Replacing Data

Sometimes want to overwrite data.You can do this in two ways using the techniques we've shown previously:

Delete an existing row using its primary key and then insert a replacement with the same primary key.
Update a row using its primary key,replacing some or all of the values (except the primary key)

mysql>?
mysql> REPLACE INTO actor VALUES(1,'Penelope','Guiness',NOW());
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sakila`.`film_actor`, CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE)
mysql> REPLACE actor_2 VALUES (1, 'Penelope','Guiness',NOW());
Query OK, 2 rows affected (0.01 sec)

mysql> REPLACE INTO actor_2 VALUES (1, 'Penelope','Guiness',NOW());
Query OK, 2 rows affected (0.00 sec)

mysql> REPLACE INTO actor_2 (actor_id, first_name,last_name)
? ? -> VALUES (1,'Penelope','Guiness');
Query OK, 2 rows affected (0.01 sec)

mysql> REPLACE actor_2 (actor_id,first_name,last_name)
? ? -> VALUES (1, 'Penelope','Guiness');
Query OK, 2 rows affected (0.00 sec)

mysql> REPLACE actor_2 SET actor_id = 1,first_name = 'Penelope',last_name = 'Guiness';
Query OK, 2 rows affected (0.01 sec)

mysql>?

You also bulk-replace into a table,removing and inserting more than one row.

MySQL provide another nonstandard extension of SQL:INSERT ... ON DUPLICATE KEY UPDATE .It is similar to REPLACE,but instead of DELETE followed by INSERT,it executes an UPDATE? whenever a duplicate key is found.

mysql>?
mysql> INSERT INTO actor_3(actor_id,first_name,last_name)
? ? -> VALUES(1,'Penelope','Guiness')
? ? -> on DUPLICATE KEY UPDATE first_name = 'Penelope',last_name = 'Guiness';
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO actor_3 (actor_id,first_name,last_name) values
? ? -> (1,'Penelope','Guiness'),(2,'Nick','Wahlberg'),
? ? -> (3,'Ed','Chase'),(1001,'William','Dyer')
? ? -> ON DUPLICATE KEY UPDATE first_name = VALUES(first_name),
? ? -> last_name = VALUES(last_name);
Query OK, 5 rows affected (0.00 sec)
Records: 4 ?Duplicates: 2 ?Warnings: 0

mysql>?

?In most situations,we recommend that you default to using INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE.

?6.The EXPLAIN Statement

You can diagnose and solve query optimization problems using the explain statement.

mysql>?
mysql> explain select * from actor\G
*************************** 1. row ***************************
? ? ? ? ? ?id: 1
? select_type: SIMPLE
? ? ? ? table: actor
? ?partitions: NULL
? ? ? ? ?type: ALL
possible_keys: NULL
? ? ? ? ? key: NULL
? ? ? key_len: NULL
? ? ? ? ? ref: NULL
? ? ? ? ?rows: 402
? ? ?filtered: 100.00
? ? ? ? Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
? Level: Note
? ?Code: 1003
Message: /* select#1 */ select `sakila`.`actor`.`actor_id` AS `actor_id`,`sakila`.`actor`.`first_name` AS `first_name`,`sakila`.`actor`.`last_name` AS `last_name`,`sakila`.`actor`.`last_update` AS `last_update` from `sakila`.`film_actor` join `sakila`.`actor` where ((`sakila`.`actor`.`actor_id` = `sakila`.`film_actor`.`actor_id`) and (`sakila`.`film_actor`.`film_id` = 11))
1 row in set (0.00 sec)

mysql>?

7.Alternative Storage Engines

InnoDB?

InnoDB is reliable ,performant , and full-featured.

The InnoDB table type includes the following features:

Support for transaction
Advanced crash recovery features(use logs which are files that contain a record of the actions that MySQL has taken to change the database.Logs enable MySQL to recover effectively from power losses,crashes, and other basic database failures.)
Row-level locking(InnoDB provide fine-grained locking infrastructure)
Foreign key support(InnoDB is currently the only MySQL table type that suppose foreign keys)
Encryption support (InnoDB tables can be encrypted transparently by MySQL)
Partioning support(InnoDB supports partitioning,that is ,spreading of data physically between multiple data files based on some rules. This allows InnoDB to work with tables of tremendous size efficiently.)
Complexity(InnoDB is relatively complex.)
Data footprint(InnoDB is a relatively disk-hungry storage engine,making it less appealing for storing extremely large datasets.)
Scaling with database size(InnoDB shine when the so called "hot" dataset,or frequently accessed data,is present in its buffer pool,This limits its scalability)

MyISAM and Aria

MyISAM was the default storage engine in MySQL for a long time,and a staple of this database.

Aria is a reworked MyISAM provided in MariaDB.Apart from promising better performance and being improved and worked on continuously,the most important feature of Aria is its crash safety.

The MyISAM table type includes the following features: