How does this MySQL statement work?

Question

CREATE TABLE `myDB`.`usersystem` (
`userid` INT NOT NULL AUTO_INCREMENT , 
`username` VARCHAR( 50 ) NOT NULL , 
`password` VARCHAR( 32 ) NOT NULL , 
`email` VARCHAR( 50 ) NOT NULL , 
PRIMARY KEY ( `userid` )
);

Sorry, I would search but I have no idea how to search for such a specific misunderstanding.

I'm starting to learn MySQL, and I'm getting comfortable, but I'm confused what the backticks do, and why when the table was created it has a period between them. What does usersystem denote?

Backticks: http://stackoverflow.com/questions/1962859/importance-of-backtick-around-table-name-in-mysql-query — ChrisW, Jun 06 '12 at 23:45

Ernest Friedman-Hill · Accepted Answer · 2012-06-07T00:15:27.063

All of the stuff you don't understand could be removed without changing the meaning of this statement!

The backticks are just for quoting; they're optional, but they distinguish between a keyword and the name of a table or column. If you wanted a table named SELECT, putting the table name in quotes like this would let you create one.

Backticks are used for quoting identifiers by default, but there's a special ANSI_QUOTES mode that, if activated, lets you use double-quote characters for this instead. Single quotes are used for quoting character strings.

myDB.usersystem refers to a table named usersystem in the database named myDB. Naming the database is optional, as it defaults to the connected one. You can also set the implicit database by using the use command, as in "use myDB".

Oh okay, so two questions if I could! 1. Are backticks interchangeable with ' and "? 2. Could I get rid of the `myDB.` part by typing `use myDB;` beforehand? — Doug Smith, Jun 06 '12 at 23:57

score 1 · Answer 2 · answered Jun 06 '12 at 23:47

Backkticks prevent names from being interpreted as SQL keywords so you can create table names like create or date.

The dot separates the schema name from the table name.

Schemas allow you to have identically named tables for different purposes, such as a users table in N different applications.

score 1 · Answer 3 · answered Jun 06 '12 at 23:47

usersystem is the table name, myDB is the database name.

<database name>.<table name> is the fully qualified way to reference a table.

The back ticks are used to denote the name of the resource. They are not always necessary but it is a good practice to use them.

How does this MySQL statement work?

3 Answers3