MySQL Schema and State - Revision history

Zach: /* Unique Key */

2023-01-10T06:06:12Z

Unique Key

2023-01-10T06:05:33Z

Foreign Key

2023-01-10T06:04:42Z

Syntax for Keys

2023-01-10T06:02:51Z

Syntax for Keys

2023-01-10T06:00:02Z

Foreign Key

2023-01-10T05:57:37Z

Unique Key

2023-01-10T05:56:30Z

Tables

2017-09-24T23:28:41Z

String Data Types: Updated data choices

2017-08-24T17:31:03Z

Removed Ubuntu support

2014-09-24T14:26:54Z

Inserting Data from a File

@@ Line 140: / Line 140: @@
 <source lang="mysql">
-unique key <INDEX_NAME> (<column1>, <column2>, <column3>)
+unique key INDEX_NAME (column1, column2, column3)
 </source>

@@ Line 154: / Line 154: @@
 <source lang="mysql">
-foreign key (<column1>, <column2>, <column3>) references <FOREIGN_TABLENAME> (<column1>, <column2>, <column3>)
+foreign key (column1, column2, column3) references FOREIGN_TABLENAME (column1, column2, column3)
 </source>

@@ Line 176: / Line 176: @@
 <source lang="mysql">
-create table <TABLENAME> (
+create table TABLENAME (
-	<column>,
+	column,
-	<column>,
+	column,
 	....,
-	primary key (<column1>, <column2>, ...),
+	primary key (column1, column2, ...),
-	unique key <INDEXNAME> (<column1>, <column2>, ...),
+	unique key INDEXNAME (column1, column2, ...),
-	foreign key (<column1>, <column2>, ...) references <FOREIGN_TABLENAME> (<column1>, <column2>, ...)
+	foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...)
+)
 </source>
@@ Line 189: / Line 189: @@
 <source lang="mysql">
-alter table <TABLENAME> add unique key <INDEXNAME> (<column1>, <column2>, ...);
+alter table TABLENAME add unique key INDEXNAME (column1, column2, ...);
-alter table <TABLENAME> add foreign key (<column1>, <column2>, ...) references <FOREIGN_TABLENAME> (<column1>, <column2>, ...);
+alter table TABLENAME add foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...);
 </source>

@@ Line 176: / Line 176: @@
 <source lang="mysql">
-create table TABLENAME (
+create table <TABLENAME> (
-	column,
+	<column>,
-	column,
+	<column>,
 	....,
-	primary key (column1, column2, ...),
+	primary key (<column1>, <column2>, ...),
-	unique key INDEXNAME (column1, column2, ...),
+	unique key <INDEXNAME> (<column1>, <column2>, ...),
-	foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...)
+	foreign key (<column1>, <column2>, ...) references <FOREIGN_TABLENAME> (<column1>, <column2>, ...)
+)
 </source>
@@ Line 189: / Line 189: @@
 <source lang="mysql">
-alter table TABLENAME add unique key INDEXNAME (column1, column2, ...);
+alter table <TABLENAME> add unique key <INDEXNAME> (<column1>, <column2>, ...);
-alter table TABLENAME add foreign key (column1, column2, ...) references FOREIGN_TABLENAME (column1, column2, ...);
+alter table <TABLENAME> add foreign key (<column1>, <column2>, ...) references <FOREIGN_TABLENAME> (<column1>, <column2>, ...);
 </source>

@@ Line 154: / Line 154: @@
 <source lang="mysql">
-foreign key (column1, column2, column3) references FOREIGN_TABLENAME (column1, column2, column3)
+foreign key (<column1>, <column2>, <column3>) references <FOREIGN_TABLENAME> (<column1>, <column2>, <column3>)
 </source>

@@ Line 20: / Line 20: @@
 <source lang="mysql">
-create table TABLENAME (
+create table <TABLENAME> (
-	FIELDNAME DATATYPE OPTIONS,
+	<FIELDNAME> <DATATYPE> <OPTIONS>,
-	FIELDNAME DATATYPE OPTIONS,
+	<FIELDNAME> <DATATYPE> <OPTIONS>,
-	FIELDNAME DATATYPE OPTIONS,
+	<FIELDNAME> <DATATYPE> <OPTIONS>,
-	FIELDNAME DATATYPE OPTIONS
+	<FIELDNAME> <DATATYPE> <OPTIONS>
-) TABLE_OPTIONS;
+) <TABLE_OPTIONS>;
 </source>
@@ Line 31: / Line 31: @@
 <source lang="mysql">
-describe TABLENAME;
+describe <TABLENAME>;
 </source>

@@ Line 49: / Line 49: @@
 The syntax for these data types is:
-* '''VARCHAR(#)''', a string ''at most'' # characters long, where 0 ≤ # ≤ 255
+* '''VARCHAR(#)''', a string ''at most'' # characters long, where 0 ≤ # ≤ 65535
-* '''CHAR(#)''', a string ''exactly'' # characters long, where 0 ≤ # ≤ 255
+* '''CHAR(#)''', a string ''exactly'' # characters long, where 0 ≤ # ≤ 65535
 * '''TINYTEXT''', a text block at most 255 characters long
 * '''TEXT''', a text block at most 65535 characters long
 * '''BLOB''', a binary string at most 65 KB in length
 * '''MEDIUMBLOB''', a binary string at most 16 MB in length

← Older revision		Revision as of 17:31, 24 August 2017
Line 244:		Line 244:

	'''Additional Note:''' If we SSH into our EC2 instance and then run the mysql command, the client is the EC2 instance, not the local computer that we SSH from. The LOCAL keyword in this case is allowing us to load files from the EC2 instance, not the local computer that we are using.		'''Additional Note:''' If we SSH into our EC2 instance and then run the mysql command, the client is the EC2 instance, not the local computer that we SSH from. The LOCAL keyword in this case is allowing us to load files from the EC2 instance, not the local computer that we are using.
−
−	~~'''Debian Quirk:''' If you are using a Debian-based system, you may need to pass a <code>--local-infile</code> flag to the <code>mysql</code> command.~~

	By default, the text files are assumed to have tab-delimited columns with column values specified in the same order as they are defined in the table (though you can specify additional/alternative delimiters using additional options).		By default, the text files are assumed to have tab-delimited columns with column values specified in the same order as they are defined in the table (though you can specify additional/alternative delimiters using additional options).

← Older revision		Revision as of 14:26, 24 September 2014
Line 242:		Line 242:

	'''Note:''' When the local keyword is used, it is best to specify an absolute path.		'''Note:''' When the local keyword is used, it is best to specify an absolute path.
		+
		+	'''Additional Note:''' If we SSH into our EC2 instance and then run the mysql command, the client is the EC2 instance, not the local computer that we SSH from. The LOCAL keyword in this case is allowing us to load files from the EC2 instance, not the local computer that we are using.

	'''Debian Quirk:''' If you are using a Debian-based system, you may need to pass a <code>--local-infile</code> flag to the <code>mysql</code> command.		'''Debian Quirk:''' If you are using a Debian-based system, you may need to pass a <code>--local-infile</code> flag to the <code>mysql</code> command.