How to manage databases and tables in MySQL?

[Aahlaadith]

Sponsored Links

I have done MySQL (to bit extent, and definitely not noob) and created some database. When approaching to its working part, user has to possibly login to a local or remote MySQL server and create session. This session is the consign where we users are permitted to manage MySQL Databases within that meticulous connected MySQL server, and when you are done with performing all the obligatory functions you can disconnect from the server. But the main thing is that I am facing the problem while managing the databases. So thought to take some help from you members. Please tell me how to manage databases and tables in MySQL? Any help would be greatly appreciated.

[Naresh Modi]

Let's start with the creation of a database:

CREATE DATABASE [IF NOT EXISTS] db_name 
[CHARACTER SET charset] [COLLATE collation]

With the optional IF NOT EXISTS can avoid the error message if there is already a database with the same name (in which case the establishment does not happen of course). To perform this statement, you must have the CREATE privilege on the database. For a MySQL database is simply a subdirectory in the data directory. So if you create "by hand" in there you'll see a directory that will recognize it as MySQL database.

ALTER DATABASE db_name 
[CHARACTER SET charset] [COLLATE collation]

With this statement we can change the default database for a character set and collation.

DROP DATABASE [IF EXISTS] db_name

This is a statement as simple as potentially devastating to its implementation in fact erase all data in the database. It goes without saying that you must use extreme caution before using it, since the SQL language does not provide in any case, the request for confirmation. To run it you need the DROP privilege on the database.

[Abraham.J]

The basic instruction to create a table is as follows:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
[(Definition ,...)] 
[Option ...] [select]

The table is created in your database, you can specifically indicate which database to be created, indicating db_name. Table_name. The TEMPORARY keyword causes the table created is valid and visible only for this connection. Any table of the same name exists on the database is hidden by the presence of the temporary. IF NOT EXISTS is used, as already seen for the db to prevent error messages in case the table already exists.

[S_Asnodkar]

The definitions can be column definitions or definitions for the table. We see a column definition:

column_name type [NOT NULL | NULL] [DEFAULT value] 
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] 
[COMMENT 'comment'] [reference_definition]

If not specified, it is considered that a column can contain NULL values. The default should be a constant (with the exception of the CURRENT_TIMESTAMP value allowed for fields of type TIMESTAMP) and, unless indicated, is considered NULL if the column can contain NULL values. Otherwise there will be no default. Also I would like to warn you that this behavior was introduced with version 5.0.2 of MySQL, the first of which was always provided a default value (the default implicit type of column when not indicated). This is an important difference, because when entering data, if not stated value for a column that has no explicit default and is active strict_mode, an error is generated.

[Aanand]

AUTO_INCREMENT can be used with a column of type integer to have a sequential value is automatically generated by the server for each inserted row in the table. For each table there can be only one AUTO_INCREMENT, the column must be indexed and can not have default. When entering you use NULL to ensure that the value is generated by MySQL. UNIQUE is an index that can not contain duplicate values, PRIMARY KEY is the primary key of the table and, in addition to not allow duplicates, may not contain NULL values. The "reference_definition" is a foreign key to another table, and this is its syntax:

REFERENCES table_name [(column_index,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

MySQL allows the use of foreign keys only on InnoDB tables (see lesson 8), but the syntax is also allowed on other types of tables (obviously without consequences).

[Xena]

Now let's see the other possible definitions (for the entire table):

[CONSTRAINT [symbol]] PRIMARY KEY [type_index] (column_index ,...) 
| KEY [index_name] [type_index] (column_index ,...) 
| INDEX [index_name] [type_index] (column_index ,...) 
| [CONSTRAINT [symbol]] UNIQUE [INDEX] 
[Index_name] [type_index] (column_index ,...) 
| [FULLTEXT | SPATIAL] [INDEX] [index_name] (column_index ,...) 
| [CONSTRAINT [symbol]] FOREIGN KEY 
[Index_name] (column_index ,...) [reference_definition]

These options allow you to define indexes or foreign keys. If you want to define an index on more than one column, the only way to do this is to define it in this part of the CREATE TABLE statement. Note that in this case is synonymous KEY INDEX, and when used on a column definition is synonymous with PRIMARY KEY. The name for the index is always PRIMARY KEY and for the other indexes, if not specified, will use the name of the first column that makes up the index (with possible adjustments to make unique names).