MySQL Topics

• MySQL Background
• Install MySQL Locally
• MySQL and FME
• MySQL and Autodesk Civil 3D
• MySQL Load Data
• MySQL Command Line Tool and Scripts
• MySQL GUI Tools
  • MySQL Query Browser
• MySQL Comments
• MySQL Privileges
• MySQL Databases
• MySQL Tables
• MySQL Select
  • GROUP_CONCAT
• MySQL Joins
• MySQL String Functions
• MySQL Variables
• MySQL Engines
  • MySQL CSV Engine
• MySQL Stored Procedures
• MySQL on Bluehost Internet Service Provider
• MySQL Export
• MySQL Questions

MySQL Background

• Support
  • MySQL Campus Tour 2009 - Duleepa "Dups" Wijayawardhana (dups@sun.com). Dups on Rails
  • formus.mysql.com (username jefferyjjensen@gmail.com and password is normal)
  • PHP 6 and MySQL 5 by Larry Ullman - Companion Web Site and forum (username jjensen and password normal)
• ESRI Products do not support MySQL (tabular or spatial)
  • Types of Geodatabases Release 9.3
  • System Requirements for ArcSDE
• Autodesk and MySQL
  • MySQL database compatibility with AutoCAD Map 3D
  • The Essential FDO
  • Free Your FDO - Autodesk University 2007
  • Using MySQL with FDO in AutoCAD Map 3D 2008

Install MySQL Locally

• Download MySQL Community Server 5.1 General Available (GA)
• Step through the Setup Wizard
• Typical
• Standard Configuration
• check "Include Bin Directory in Windows PATH"
• create root password normal
• Execute the install
• Once installed, use Start -> Programs -> MySQL -> MySQL Server 5.1 -> MySQL Command Line Client
• enter the normal password, should have the mysql> command prompt

How to import CSV files into MySQL using FME Workbench

• Step 0: obtain csv file. This demo using the NV Secretary of State database (Corporations.CrprtStc.1.100708123054.txt)
• Step 1: create a MySQL database on bluehost. Database ksalvcom_NVSOSOct2008, username ksalvcom_NVSOS password NVSOS1234 with all privileges
• Step 2: using the FME Workbench wizard, select the .csv file you want to create as a MySQL table (Corporations.CrprtStc.1.100708123054.txt)
  • 
  • 
• Step 3: select output destination format as MySQL Non-spatial. In the settings, specify the host name (if your MySQL database is installed locally, then enter localhost, if its a remote MySQL database, enter the domain name such as ksalv.com)
  • 
  • 
• Step 4: optional. Import Feature Type Definitions. If the fields are already defined in another database, such as Microsoft Access, can just import them.
  • 
  • Then after FME has read the data, will be prompted to navigate to the database which you want to import the table/field definitions from
  • 
  • 
• Step 5: Connect the features
  • Should have green triangle in the FME Workbench, implies its ready to run the translation
  • 
• Notes
  • How do I control if the .csv records will overwrite or append records when the table already exists? It appears, FME will do an overwrite.
  • MySQL Spatial Reader/Writer or download the complete FME Readers and Writers Manual
  • FME MySQL FAQ

How to Connect Autodesk Civil 3D to MySQL spatial database

• Step 0: Install a local version of MySQL database
  • My Internet Service Provider - Bluehost.com uses MySQL version 5.0.67, so I will download and install MySQL Community Server 5.0 install Windows Essentials (x86). This will install libmySQL.dll (C:\Program Files\MySQL\MySQL Server 5.0\bin\libmySQL.dll). This works with both Civil 3D 2008 and 2009
• Step 1: check providers.xml includes MySQL snippet
  • C:\Program Files\AutoCAD Civil 3D 2008\FDO\bin\providers.xml

  • <FeatureProvider>
    <Name>OSGeo.MySQL.3.2</Name>
    <DisplayName>OSGeo FDO Provider for MySQL </DisplayName>
    <Description>Read/write access to MySQL-based data store. Supports spatial data types and spatial query operations. </Description>
    <IsManaged>False</IsManaged>
    <Version>3.2.1.0</Version>
    <FeatureDataObjectsVersion>3.2.1.0</FeatureDataObjectsVersion>
    <LibraryPath>MySQLProvider.dll</LibraryPath>
    </FeatureProvider>
    

  • C:\Program Files\AutoCAD Civil 3D 2009\FDO\bin\providers.xml

  • <FeatureProvider>
    <Name>OSGeo.MySQL.3.3</Name>
    <DisplayName>OSGeo FDO Provider for MySQL </DisplayName>
    <Description>Read/write access to MySQL-based data store. Supports spatial data types and spatial query operations. </Description>
    <IsManaged>False</IsManaged>
    <Version>3.3.0.0</Version>
    <FeatureDataObjectsVersion>3.3.0.0</FeatureDataObjectsVersion>
    <LibraryPath>MySQLProvider.dll</LibraryPath>
    

• Step 2: libmysql.dll
  • Civil 3D 2008 - copy libmysql.dll into C:\Program Files\AutoCAD Civil 3D 2008\FDO\bin directory
  • Civil 3D 2009 - copy libmysql.dll into C:\Program Files\AutoCAD Civil 3D 2009\FDO\bin directory
  • FME 2008 comes with libmysql.dll (C:\Program Files\FME\plugins\mysql\libmysql.dll) but this does NOT work with Civil 3D 2008 or 2009. Beta FME 2009 also comes with libmysql.dll According to Robyn Rennie with Safe Software, the version of libmysql.dll included with FME is 4.1.9.0.
  • MySQL GUI Tools 5.0 comes with libmySQL.dll (C:\Program Files\MySQL\MySQL Tools for 5.0\libmySQL.dll). Note this is NOT compatible with Civil 3D 2008 or 2009.
  • MySQL Community Server 5.0 install Windows Essentials (x86). This will install libmySQL.dll (C:\Program Files\MySQL\MySQL Server 5.0\bin\libmySQL.dll). This works with both Civil 3D 2008 and 2009
  • If Civil 3D 2009 is running and you copy the libmysql.dll into the correct directory, FDO should work without having to restart Civil 3D.
• Step 3: AutoCAD map workspace
  • Start AutoCAD Civil 3D 2008
  • type MAPWSPACE, Task Pane On. Then click Data -> Connect to Data
  • Add MySQL Connection
  • Service name: ksalv.com, User Name: ksalvcom_NVSOS, Password: NVSOS1234
• Troubleshooting
  • The specified credentials are not valid or the provider is unable to establish a connection
    • Issue is with the libmysql.dll file not being compatible with Civil 3D and/or bluehost.
    • 

MySQL Load Data Infile

• LOAD DATA [LOCAL] INFILE 'c:/gisdata/filename.csv'; # mysql on windows uses forward slashes for the pathname
• LOAD DATA
  INFILE 'C:/gisdata/crnvsos/2009-01/complete/Corporations.CrprtStc.1.010709132850.txt'
  INTO TABLE nvsos.stocks
  FIELDS TERMINATED BY ','
  /* having problems with this ENCLOSED '"' */
  ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n'
  (col1, col2, col3, @col4)
  SET col4 = str_to_date(@col4, '%m/%d/%Y');
  
• Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r' (Reference: load data)
• when entering the FIELDS parameter from the LOAD DATA, there is no need to repeatly type in the FIELDS for each option. For example:
  BAD LOAD DATA
  
  LOAD DATA INFILE '/tmp/teams.txt'
  INTO TABLE team
  FIELDS TERMINATED BY '\t' # tab is the default delimited for MySQL
  FIELDS OPTIONALLY ENCLOSED BY '"'
  # no need to type FIELDS again FIELDS ESCAPED BY '\\'
  # no need to type FIELDS again LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (Name, ID)
  
  
  GOOD LOAD DATA
  
  LOAD DATA INFILE '/tmp/teams.txt'
  INTO TABLE team
  FIELDS TERMINATED BY '\t' # tab is the default delimited for MySQL
      OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (Name, ID)
  
  see Core MySQL by Leon Atkinson, p. 247
• How to add an autonumber field, such as the record number when importing a text file which doesn't have such a field.
  • when creating the table, add a field with the AUTO_INCREMENT field, then on import, do NOT specify the column in the LOAD DATA INFILE
  • "An auto-increment column intercepts default values and uses sequential numbers in that column. The column must be an integer, and should be unsigned. It must also have an index. Use the AUTO_INCREMENT flag to create an auto-increment column. You may only have one auto-increment column on a table. It's customary to make the auto-increment column the primary key." (Core MySQL by Leon Atkinson, p. 226)
  • see comment from Jay Alverson in MySQL Forums :: Newbie :: LOAD DATA INFILE issue
• Reference: load data
• Problems import int(4) when blank, works if I use varchar(10) for the Corporations table
• Importing Dates
  • Problem: I have a date field in MM/DD/YYYY but MySQL wants its dates in YYYY-MM-DD format.
  • Solution from Jay Alverson - load data date fields, basically import the date as a varchar then add a new field with type DATE.
  • Ramam Pullella also discusses converting strings into dates while loading data using LOAD DATA INFILE.

MySQL Command Line Tool and Scripts

• To run a MySQL script file (.sql) from the mysql> command prompt, use the SOURCE command, for example
  
• MySQL 5.1 Reference Manual - The MySQL Command-Line Tool
• Executing SQL Statements from a Text File

MySQL GUI Tools

• Download MySQL GUI Tools
• Useful to test connection to the MySQL database
• MySQL GUI Tools Manual

MySQL Query Browser - MySQL GUI Tools

• Used to enter and display SQL query results
• Example SQL Query: select * from ksalvcom_NVSOS2008.Corporations where CorporationID between 1000 and 1010
• 

MySQL Comments

• "MySQL ignores test after a # or two dashes followed by a space "-- ". MySQL also ignores text inside /* and */. (Core MySQL by Leon Atkinson p. 214)
• SELECT NOW(); # get current time
• SELECT NOW(); -- get current time
• /*
  ** get current time
  */
  SELECT NOW();
• 

MySQL Privileges

• SHOW GRANTS;

  # will display the privileges of the current user

• SHOW PRIVILEGES

  # will display all the privileges available
• File Privileges
  • it appears bluehost does NOT give you the permission to read and write files on the server using the FILE privilege for the SELECT ... INTO OUTFILE and the LOAD DATA INFILE
• Execute Privileges
  • Used to execute stored routines (functions and procedures)
  • It appears bluehost does NOT give you the permission to run MySQL procedures
• References
  • MySQL 5.1 Reference Manual - MySQL Access Privilege System
  • Privileges Provided by MySQL

MySQL Databases

• CREATE DATABASE [IF NOT EXISTS] name
• CREATE DATABASE nvsos
  • "Databases are simply subdirectories to MySQL, so this command creates an empty directory." (Core MySQL by Leon Atkinson p. 224)
  • square brackets [ ] show otional clauses
  • name is the database name you want to create,
  • Note, SQL statements are case-insensitive
  • 
• DROP DATABASE [IF EXISTS] name;
  • "Use DROP DATABASE to remove a database from the system. All tables and data are permanently removed. The IF EXISTS flag suppresses errors when the database doesn't exist. Databases are implemented as directories containing files for maintaining the data and indexes in tables. A DROP DATABASE statement deletes all files in the directory that are clearly MySQL files. Other files are left. If the directory is empty, it is removed as well. Placing your own file inside the directory prevents it from being deleted. Therefore, it's possible to drop a databse, deleting all tables, but the database itself remains. You can also remove a database completely by deleting its directory on the file system. InnoDB tables must be dropped individually before dropping a database." (Core MySQL by Leon Atkinson p. 236)

MySQL Create and Drop Tables

• CREATE TABLE nvsos.stocks ( /* Columns/Fields */ stockid int(4) unsigned not null, corporationid int(4) unsigned not null, sharecount bigint(8), sharevalue double, /* Indexes */ PRIMARY KEY (stockid), /* this is not working - FOREIGN KEY (corporationid) */ );
• SHOW FULL TABLES FROM db_name; # will return all tables in the db_name MySQL database
• MySQL Data Types
• DESCRIBE NVSOS.STOCKS;
  • will show all the columns in the table
  • 
• CREATE TABLE with SELECT statement

  • CREATE TABLE gisengin_ccrec.trash AS
    SELECT a.*, b.namesid, b.partycode, b.partycodedesc, b.partylastname, b.partymidname, b.partyfullname
    FROM gisengin_ccrec.instrument a
    LEFT OUTER JOIN gisengin_ccrec.names b
    ON a.instrno = b.instrno;

  • CREATE TEMPORARY TABLE IF NOT EXISTS gisengin_ccrec.tmpnames

    
    "A TEMPORARY table is visible only to the current connection and is dropped automatically when the connection is closed."
  • MySQL 5.1 Reference Manual - 12.1.17 CREATE TABLE syntax
• DROP TABLE
  • DROP TABLE removes all files related to a table. You may specify multiple tables by separating them with commas. (Core MySQL by Leon Atkinson p. 237)
  • DROP TABLE nvsos.stock;

  • DROP TEMPORARY TABLE IF EXISTS gisengin_ccrec.tmpnames

    
    the temporary keyword only drops temporary tables and does not end an ongoing transaction
  • MySQL 5.1 Reference Manual - 12.1.28 DROP TABLE syntax

MySQL Select

MySQL and GROUP_CONCAT()

• References for GROUP_CONCAT
  • MySQL 5.1 Reference Manual - 11.12.1. GROUP BY (Aggregate) Functions
  • VT's Tech Blog - GROUP_CONCAT in MySQL
  • GROUP_CONCAT() simplifies grouping parents with their children - Listing 5
• SELECT a.instrno AS ID, GROUP_CONCAT(a.partylastname) AS PARTY
  FROM gisengin_ccrec.names a
  GROUP BY a.instrno
  ORDER BY a.instrno;
  
• MySQL Forums - How to concatenate two field values

MySQL Joins

• Inner Joins

  • SELECT * FROM t1.table1 AS DatabaseName.table1 INNER JOIN t2 AS DatabaseName.table2 ON t1.id = t22.id WHERE t2.name = 'somevalue'

  • "When selecting from multiple tables, you must use the dot syntax (table.column) if the tables named in the query have columns with the same name. This is normally the case when dealing with relational databases because a primary key from one table will have the same name as a foreign key in another." (PHP 6 and MySQL 5 by Larry Ullman, p. 173 - Companion Web Site)
• Outer Joins

  • SET SQL_BIG_SELECTS=1;
    SELECT a.*, b.* FROM gisengin_ccrec.instrument a
    LEFT OUTER JOIN gisengin_ccrec.names b ON a.instrno=b.instrno;
    

  • How do I select the records in one table which do NOT have a corresponding value in another table?

    • SET SQL_BIG_SELECTS=1;
      SELECT a.*, b.* FROM gisengin_ccrec.instrument a
      LEFT OUTER JOIN gisengin_ccrec.names b
      ON a.instrno = b.instrno
      HAVING b.instrno is null;

    • this won't work, INNER JOIN will basically just return the entire instrument table
    • this won't work, LEFT OUTER JOIN will basically just return the entire instrument table

    • SET SQL_BIG_SELECTS=1;
      SELECT a.*, b.* FROM gisengin_ccrec.instrument a
      LEFT OUTER JOIN gisengin_ccrec.names b
      ON a.instrno <> b.instrno;
      

    • Reference
      • Chapter 14 Outer Joins, Finding the defects in a pattern (SQL Fundamentals 3rd Edition, by John Patrick, p. 542)

MySQL String Functions

• How do I combine two columns/fields into one?
  • References

    SELECT CONCAT_WS(' ', a.firstname, a.lastname) AS fullname
      FROM gisengin_ccrec.names a;

    • MySQL 5.1 Reference Manual - 11.4 String Functions
    • SQL Fundamentals, 3rd Edition by John J. Patrick, p. 344 Chapter 9 - Row Functions, 9-10 Combining the first and last names

MySQL variables

• SHOW VARIABLES will return all the MySQL variables
• SHOW VARIABLES LIKE 'max%'; will return only those variables starting with max
• Reference: MySQL 5.0 Reference Manual - 12.5.5. SHOW Syntax

MySQL Engines

MySQL CSV Engine

• References
  • A Look at the MySQL CSV Storage Engine

MySQL Stored Procedures

• To create a procedure using the GUI Tools, just right click on the database name under the Schemata tab and select Create New Procedure / Function
  • 
• Bluehost gives the ability to create a procedure, just cannot call it
• 
• Reference
  • SQL for MySQL Developers by Rick F. van der Lans. Chapter 31 - Stored Procedures.

MySQL on bluehost issues

• Bluehost does NOT support LOAD DATA INFILE
  • LOAD DATA INFILE '~/public_html/MYSQLDB/gisdata/CorporationOfficers.csv' REPLACE INTO TABLE gisengin_sosombud.CorporationOfficers FIELDS TERMINATED BY ',' ENCLOSED BY '"';
• How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)

How to Export MySQL database

• Option 1: using the GUI Tools the resulting sets of a query can be exported as .CSV, .XML, .HTML, .XLS or PLIST by just right clicking on any result record and chosing Export Resultantset -> Export As CSV File.... See MySQL Query Browser - Exporting Result Sets
• Option 2: SELECT ... INTO OUTFILE
  • Problem is this requires FILE Privileges which are not allowed by bluehost.com
• C:\> mysqldump --user=root --password=gisengineering --database=nvsos >nvsosall.sql
  • Had problems with Windows XP and mysqldump, the following worked
  • C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe --user=root --password=Normal ccrec >ccrecall.sql
• using Windows Explorer, compress nvsosall.sql (1.1GB) to nvsosall.zip (169MB)
• ftp to ftp.gisengineering.com/gisdata/crnvsos/2009-01/nvsosall.zip
• login to bluehost SSH shell account: unzip nvsosall.zip
• ensure you have a blank database called gisengin_nvsos, if not, bluehost only lets you create MySQL databases from the Control Panel.
• bluehost doesn't allow you to create a database at the command prompt, can only create using the Control Panel. Also can only delete a MySQL database from the Control Panel. The directory where the MySQL database is stored is above the directory you access with SSH login.
  • Comment the following lines in nvsosall.sql by adding a # symbol in front
  • # CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nvsos` /*!40100 DEFAULT CHARACTER SET latin1 */;
  • # USE `nvsos`;
• mysql --user=root --password=gisengineering --database=gisengin_nvsos <nvsosall.sql
  • Bluehost needs gisengin added to the username, for example
    mysql --user=gisengin_root --password=GISJ???1 --database=gisengin_nvsos <nvsosall.sql
• Reference:
  • mysqldump - A Database Backup Program
  • How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)

Connecting to MySQL installed on Bluehost ISP

• SSH Secure Telnet Shell Method
  • # mysql --user=gisengin_root --password=gisengineering --host=gisengineering.com --database=gisengin_test
  • How do I import an .sql file
    • mysql --user=gisengin_root --password=gisengineering --host=gisengineering.com --database=gisengin_test Notes: it appears you must already create the database you want to import. So if your .sql file was created with mysqldump of the entire database and its tables, then you will have to make the database first before you can import records.
    • How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)
• Step 0: login to the bluehost cPanel, e.g. using a web browser visit http://www.ksalv.com/cpanel then will be prompted for a username (ksalvcom) and password (Normal)
• Step 1: Under the Databases heading, click MySQL Databases module
• Step 2: Create a New Database
• Step 3: Add New User
• Step 4: Add User to the New Database
• Step 5: at the cPanel homepage, Under the Databases heading, click Remote MySQL module. Enter the WAN IP address in the Add Access Host
  • home: 67.77.42.39
  • UNLV: 131.216.19.%
• Step 6: then click the Customer Profile link, then in the Customer Preferences, will see the MySQL Whitelisted IPs and add the WAN IP address there as well.
  • bluehost Support Center - Whitelist IP Address
• If you don't do these steps, will get the following error message from the MySQL GUI tools:
  Could not connect to the specified instance. MySQL Error Number 2003. Can't connect to MySQL server on 'ksalv.com' (10060). If you want to check the network connection, please click the Ping button
• BlueHost Tutorials - Setting up MySQL Databases

Google and Bluehost Notes

• Name Servers
  • This will forward all web traffic from godaddy.com (or whatever company you have your domain registered with) to Bluehost.com
  • NS1.BLUEHOST.COM
  • NS2.BLUEHOST.COM
  • example GoDaddy.com Name Servers (NS51.DOMAINCONTROL.COM and NS52.DOMAINCONTROL.COM)
• Total DNS Control and MX Records
  • CNAMES (Aliases)
    • mail - ghs.google.com
    • docs - ghs.google.com
    • calendar - ghs.google.com
    • sites - ghs.google.com
    • start - ghs.google.com
• Bluehost IP - 74.220.219.63
• Godaddy IP - 68.178.232.100
• VegasWiFi IP - 66.116.107.3
• Managing Your Domain Names
• Canonical Name (CNAME) see What are zone files and zone records?
• Manage GISEngineering.com Domain on Google, http://www.google.com/a/gisengineering.com, username: jjensen, password: Normal
• Google custom image logo should be PNG or GIF format scaled to 143 x 59 pixels (see How do I change the logo?)
• Edit Google Web Pages at http://pages.google.com/a/www.gisengineering.com
• Google Sites Video Tutorial

MySQL Questions

• How do you index a field in a table?

  • CREATE INDEX idxNamesInstrno
    ON ccrec.names (instrno)

  • To drop an index, use

    DROP INDEX idxNamesInstrno

  • Did an OUTER JOIN and it took 1 hr 12 min to run. When I indexed the join fields, it took about 2 seconds to run. Indexes are very helpful.
  • "One way to improve the efficiency of your queries is to use the indexes that the database has. You need to find out which columns have indexes on them. Try to use those columns in your join conditions and when you place selection conditions in the WHERE clause. Most databases already have indexes on all the columns intended for use in join conditions. Here is one of my experiences involving indexes. Once I was writing some queries at a large company and every time I wrote a query using a particular table, the query would time out. When a query times out, this means that the query has processed in the computer for the maximum time allowed, which was about an hour at that shop. When I submitted one of these queries, it would run for an hour, then I would get an error message from the operating system, no result table, and a bill for $3,000. I investigated this by going to the Data Dictionary and looking for the indexes on the table. I found that an index was missing. I told the DBA, and within two days the index was built. Then my queries ran in just a few minutes." (SQL Fundamentals, 3rd Edition by John J. Patrick, p. 664)
  • References
    • MySQL 5.1 Reference Manual - 12.1.13 CREATE INDEX
    • SQL Fundamentals, 3rd Edition by John J. Patrick, p. 262-264
• How do you find the number of records in a table?
  • Option 1:

    SELECT COUNT(a.namesid) FROM gisengin_ccrec.names a

    • This should count the number of records in the table. It appears null values are also included in the count, so this is helpful, don't have to worry if your data field contains null values, will get an accurate count.
  • Option 2:

    SHOW TABLE STATUS FROM gisengin_ccrec;

    • "The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count. The Rows value is NULL for tables in the INFORMATION_SCHEMA database."
    • this will list all the tables in the gisengin_ccrec database, type of engine (MyISAM, InnoDB), number of rows and other things.
    • See MySQL 5.1 Reference Manual, 12.5.5.38. SHOW TABLE STATUS syntax
  • Option 3:

    SELECT MAX(a.namesid) FROM gisengin_ccrec.names a

    • If you have an AUTOINCREMENT (AUTONUMBER) field, just list the MAX record to get the total number of records in the table.
• How do I COUNT the total number records in my TABLE which have unique values in a FIELD

  • SELECT COUNT(DISTINCT n.instrno) FROM gisengin_ccrec.names n;

  • The DISTINCT n.instrno will remove all the duplicates in the names table of the gisengin_ccrec database
  • COUNT will then sum up the number of records and return the total
• How do I view all the records which have a duplicate value in a column?

  • SELECT *, COUNT(a.instrno) AS qty
    FROM gisengin_ccrec.instrument a
    GROUP BY a.instrno
    HAVING qty > 1;

  • SQL Query to Identify List of Duplicate Rows by Huajun Zhai
• How do you create a template table, that is column/fields are predefined along with the indexes?
• How do I view the column/field properties for a table?
  • SHOW FULL COLUMNS FROM ksalvcom_NVSOSOct2008.Corporations;
  • FULL will display the Comment column/field
  • see MySQL 5.1 Reference Manual - 12.5.5.6 SHOW COLUMNS Syntax
• How do you move tables between databases?
  • "As long as two databases are on the same filesystem, you can use RENAME TABLE to move a table from one database to another."

    RENAME TABLE current_db.tbl_name TO other_db.tbl_name; (see MySQL 5.0 Reference Manual, 12.1.19. RENAME TABLE Syntax)

  • Move table from one db to another on same server - how?
  • 
• How do you setup relationships (one-to-many, enforce referential integrity, ...) in MySQL?
• How do you define a column/field as a foreign key in an existing table?
  • ALTER TABLE ksalvcom_NVSOSOct2008.CorporationActions ADD FOREIGN KEY (CorporationID) REFERENCES ksalvcom_NVSOSOct2008.Corporations(CorporationID);
• How do you define a column/field as a primary key in an existing table?
  • ALTER TABLE ksalv_NVSOSOct2008.CorporationActions ADD CONSTRAINT PK_ActionID PRIMARY KEY (ActionID);
  • I'm not sure what the PK_ActionID is
  • DESCRIBE ksalvcom_NVSOSOct2008.CorporationActions;
    • Use to confirm the table has been altered and the primary key was added
    • "The Key field indicates whether the column is indexed. A value of PRI indicates that the column is part of the table's primary key. UNI indicates that the column is part of a UNIQUE index. The MUL value indicates that multiple occurrences of a given value are allowed within the column.
    • 
• Microsoft Access and MySQL
  • Migrating from Microsoft SQL Server and Access to MySQL by Mike Hillyer with MySQL AB
  • Does MySQL have an autonumber data field similar to Access?
  • MySQL Microsoft Access Forum
• How to Build MySQL on Windows