|Character Set Fundamentals|
To store data in MySQL, we will set up a database and then place tables, relationships and other objects in that database, following a design that maps to our application requirements. We will use a command-line tool - mysql, running a script to generate our objects. Several other options are available to create components via User Interfaces such as phpMyAdmin, MySQL Query Browser and MySQL Workbench.
|phpMyAdmin||More on phpMyAdmin under User Intefaces.|
|MySQL Query Browser||A product from MySQL that provides a good set of drag-and-drop tools to visually define, run, manage and optimize SQL queries for MySQL Databases. Covered in another lesson.|
|SQLyog MySQL||SQLyog MySQL GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser, Administrator, phpMyAdmin and other MySQL Front Ends and MySQL GUI tools in a single intuitive interface.|
CREATE DATABASE [IF NOT EXISTS] <database name>
[[DEFAULT] CHARACTER SET <character set name>]
[[DEFAULT] COLLATE <collation name>]
Note: If the database named above already exists, the statement above will throw an error. By specifying the IF NOT EXISTS clause, MySQL returns just a note (warning).
The CHARACTER SET specifies the default character set to use and the COLLATE clause selects default collation for ordering. More on these later.
We will now set up our new database sakilakubili using mysql.
CREATE DATABASE sakilakubili;
The 1 row affected response from mysql indicates that one row was changed in the internal MySQL table containing the list of all databases. On statement execution, a database named sakilakubili is added to MySQL server. The database uses the default character set and collation as we didn't specify any.
Note:On Windows, all object names, such as databases and tables, are converted to lowercase. In Linux and other Unix-like operating systems, the case is preserved. More on case- senstivity in other lessons.
Here, we actually specify the character set and collation to use:
CREATE DATABASE sakilakubili
DEFAULT CHARACTER SET latin1
DEFAULT COLLATE latin1_bin;