Copy rows from one table to another

SQL: How to copy rows from one table to another
http://stackoverflow.com/questions/57168/how-to-copy-a-row-from-one-sql-server-table-to-another

Advertisements

SHOW CREATE VIEW v

CREATE VIEW nloview AS
select nlo.id, nlo.word
from nlo left join forms on forms.word = nlo.word
where isnull(forms.word)

MySQL

MySQL JOIN / LEFT JOIN

https://www.codeproject.com/kb/database/visual_sql_joins.aspx

from:
Chapter 14
The Language of SQL, 2nd Edition
By Larry Rockoff
Published Jul 27, 2016 by Addison-Wesley Professional
http://www.informit.com/store/language-of-sql-9780134658254

As in all computer languages, there is frequently more than one way to specify
any desired objective.
p. 1

MySQL Foreign key

MySQL Foreign key
http://www.mysqltutorial.org/mysql-foreign-key

SQL > GROUP BY

SQL > GROUP BY

mysql-connector-python 2.1.5

The installed version (mysql-connector-python-2.1.5-py3.4-winx64.msi) is undetected by Oracle’s MySQL

…ending support for OS versions that are obsolete or have reached end of life, we plan to discontinue building all MySQL binaries for Microsoft Windows 7 as of June 23, 2016
http://www.mysql.com/support/eol-notice.html

MySQL 5.7.1 since ~ April 2013

mysql-connector-python 2.1.5
MySQL driver written in Python
https://pypi.python.org/pypi/mysql-connector-python is this page updated??
https://dev.mysql.com/downloads/connector/python/2.1.html

MySQL Connector/Python Developer Guide
http://dev.mysql.com/doc/connector-python/en/index.html
Chapter 1 Introduction to MySQL Connector/Python
http://dev.mysql.com/doc/connector-python/en/connector-python-introduction.html
up to and including MySQL Server version 5.7.
Chapter 3 Connector/Python Versions
http://dev.mysql.com/doc/connector-python/en/connector-python-versions.html
http://dev.mysql.com/doc/connector-python/en/connector-python-coding.html
Another recommendation is to declare a numeric primary key for each table, which offers the fastest way to look up values and can act as a pointer to associated values in other tables (a foreign key).

=======================

import mysql.connector

help(module)

dir(module)

mysql.connector.__version__

======================

For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called.
In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorbuffered.html
solution: cnx.close()

“developer milestone”
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-2.html

Duplicate values in a SQL table

mysql> use histology_db;
Database changed
mysql> SELECT word, count(word)
-> FROM dictionary
-> GROUP BY word
-> HAVING count(word) > 1;

CREATE VIEW duplicates AS …

CREATE VIEW verbs AS …

mysql> SELECT d.word_ID, LEFT(d.word, 10), d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word;
+———+——————+———-+
| word_ID | LEFT(d.word, 10) | category |
+———+——————+———-+
| 15 | study | verb |
| 19 | study | noun |
| 57 | focus | noun |
| 160 | advance | noun |
| 183 | focus | verb |
| 193 | section | noun |
| 247 | section | verb |
| 343 | advance | Verb |
+———+——————+———-+

mysql> SELECT d.word_ID, LEFT(d.word, 10) AS multiple, d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word
-> ORDER BY multiple, d.category;

http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table
http://www.w3schools.com/sql/sql_join.asp

mysql> SELECT category, count(word_ID)
-> FROM dictionary
-> GROUP BY category;
+————–+—————-+
| category | count(word_ID) |
+————–+—————-+
| adjective | 58 |
| adverb | 8 |
| conjunction | 3 |
| determiner | 1 |
| noun | 201 |
| unclassified | 52 |
| verb | 61 |
+————–+—————-+

INNER JOIN
http://dev.mysql.com/doc/refman/5.6/en/example-foreign-keys.html

Finding a duplicate value, one of which has category=”unclassified”