Difference between revisions of "SQL"

From Organic Design wiki
(See also: Good thread on table lock problems and solutions)
m (Fixing crashed tables)
 
(8 intermediate revisions by the same user not shown)
Line 6: Line 6:
 
== Backup & restore ==
 
== Backup & restore ==
 
See [[backup]] for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!
 
See [[backup]] for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!
 +
 +
To restore only a single database from a full dump use the following:
 +
<source lang="bash">
 +
mysql -D DBNAME -o DBNAME < dump.sql
 +
</source>
  
 
== Fixing crashed tables ==
 
== Fixing crashed tables ==
 
To fix a crashed table just use '''repair TABLENAME''' from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.
 
To fix a crashed table just use '''repair TABLENAME''' from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.
 
<source lang="bash">
 
<source lang="bash">
mysqlcheck --auto-repair DBNAME -u USER -p
+
mysqlcheck -vvvv --auto-repair DBNAME -u USER -p
 
</source>
 
</source>
 
== Dealing with duplicates on import ==
 
See [[Backup]]
 
 
== Documentation ==
 
*[http://www.sql-tutorial.com/ SQL Tutorial] ''- A nice tutorial on SQL''
 
*[http://dev.mysql.com/doc/index.html Official MySQL Documentation]
 
*[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)]
 
 
== MySQL vs MSSQL ==
 
*[http://www.x7media.com/resources/SQL_Differences_Between_Microsoft_SQL_Server_and_MySQL.asp List of common differences between MySQL and MSSQL]
 
 
== MySQL News & Information ==
 
*[http://dev.mysql.com/doc/falcon/en/falcon-overview.html Falcon Overview] ''- the new MySQL 6 Falcon storage engine''
 
*[http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html MySQL Stored procedures & triggers]
 
 
== RODBC ==
 
Using RODBC to interact with R requires the user to configure the system as described in the file README after installing [http://cran.stat.auckland.ac.nz/web/packages/RODBC/index.html RODBC].
 
;See
 
*http://www.unixODBC.org
 
*http://www.easysoft.com/developer/interfaces/odbc/linux.html (tutorial using unixODBC)
 
 
== Towards SQL for P2P environments ==
 
*[[PeerPedia]]
 
*[[MediaWikiLite]]
 
*[http://pier.cs.berkeley.edu The PIER Project]
 
*[[w:Prefix Hash Tree|Prefix Hash Trees]]
 
*[http://www.cs.huji.ac.il/~ittaia/papers/AAY-OPODIS05.pdf Skip B-Trees]
 
*[http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server] ''- Yoshinori Matsunobu's blog''
 
  
 
== Database procedures ==
 
== Database procedures ==
 +
*[[MediaWiki SQL queries]]
 
*[[Reset MySQL root password]]
 
*[[Reset MySQL root password]]
 
*[[Renaming MySQL tables and databases]]
 
*[[Renaming MySQL tables and databases]]
Line 50: Line 27:
 
*[[Manually backup a wiki]]
 
*[[Manually backup a wiki]]
 
*[[Add a wiki database]]
 
*[[Add a wiki database]]
 +
 +
== Character sets ==
 +
*[http://tlug.dnho.net/?q=node/276 Convert a MySQL DB from latin1 to UTF8]
 +
*[https://mathiasbynens.be/notes/mysql-utf8mb4 MySQL and utf8mb4]
  
 
== See also ==
 
== See also ==
 +
*[[MariaDB]]
 
*[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im Good thread on table lock problems and solutions]
 
*[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im Good thread on table lock problems and solutions]
*[https://mariadb.org/en/about/ MariaDB] ''- see [[PeerPedia]] Feb 2013 update for more info''
+
*[http://www.sql-tutorial.com/ SQL Tutorial] ''- A nice tutorial on SQL''
 +
*[http://dev.mysql.com/doc/index.html Official MySQL Documentation]
 +
*[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)]
 +
*[https://www.databasestar.com/sql-joins/ Introduction to SQL joins]
 +
*[http://www.x7media.com/resources/SQL_Differences_Between_Microsoft_SQL_Server_and_MySQL.asp List of common differences between MySQL and MSSQL]
 +
*[https://www.heidisql.com/screenshots.php HeidiSQL] ''- see and edit data and structures from MariaDB, MySQL, Microsoft SQL or PostgreSQL databases''
 +
*[https://edgedb.com/blog/we-can-do-better-than-sql/ We can do better than SQL]

Latest revision as of 00:35, 23 September 2019

Broom icon.svg The content of this article requires cleaning up to meet OD's quality standards. Check the wiki best practices for guidelines on improving article and categorisation quality.

SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL has been standardised by both ANSI and ISO.

SQL is commonly spoken either as the names of the letters ess-cue-el, or like the word sequel. The official pronunciation of SQL according to ANSI is ess-cue-el. However, each of the major database products (or projects) containing the letters SQL has its own convention: MySQL is officially and commonly pronounced "My Ess Cue El"; PostgreSQL is expediently pronounced postgres (being the name of the predecessor to PostgreSQL); and Microsoft SQL Server is commonly spoken as Microsoft-sequel-server. See MediaWikiLite for information about SQLite support in MediaWiki, or MSSQL for information about Microsoft SQL Server support. We're now using MariaDB for our SQL server which is a drop-in replacement for MySQL but is fully open source and more efficient too.

Backup & restore

See backup for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!

To restore only a single database from a full dump use the following:

mysql -D DBNAME -o DBNAME < dump.sql

Fixing crashed tables

To fix a crashed table just use repair TABLENAME from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.

mysqlcheck -vvvv --auto-repair DBNAME -u USER -p

Database procedures

Character sets

See also