Create Database in two ways Show
1) By executing a simple SQL query 2) By using forward engineering in MySQL Workbench In this SQL Tutorial, you will learn-
As SQL beginner, let’s look into the query method first. How to Create Database in MySQLHere is how to create a database in MySQL: CREATE DATABASE is the SQL command used for creating a database in MySQL. Imagine you need to create a database with name “movies”. You can create a database in MySQL by executing following SQL command. CREATE DATABASE movies; Note: you can also use the command CREATE SCHEMA instead of CREATE DATABASE Now let’s improve our SQL query adding more parameters and specifications. IF NOT EXISTSA single MySQL server could have multiple databases. If you are not the only one accessing the same MySQL server or if you have to deal with multiple databases there is a probability of attempting to create a new database with name of an existing database . IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database. When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database’s name. Without the use of IF NOT EXISTS MySQL throws an error. CREATE DATABASE IF NOT EXISTS movies; Collation and Character SetCollation is set of rules used in comparison. Many people use MySQL to store data other than English. Data is stored in MySQL using a specific character set. The character set can be defined at different levels viz, server , database , table and columns. You need to select the rules of collation which in turn depend on the character set chosen. For instance, the Latin1 character set uses the
CREATE DATABASE IF NOT EXISTS movies CHARACTER SET latin1 COLLATE latin1_swedish_ci The best practice while using local languages like Arabic , Chinese etc is to select Unicode (utf-8) character set which has several collations or just stick to default collation utf8-general-ci. You can find the list of all collations and character sets here You can see list of existing databases by running following SQL command. SHOW DATABASES How to Create Table in MySQLCREATE TABLE command is used to create tables in a database Tables can be created using CREATE TABLE statement and it actually has the following syntax. CREATE TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine; HERE
MySQL Create Table ExampleBelow is a MySQL example to create a table in database: CREATE TABLE IF NOT EXISTS `MyFlixDB`.`Members` ( `membership_number` INT AUTOINCREMENT , `full_names` VARCHAR(150) NOT NULL , `gender` VARCHAR(6) , `date_of_birth` DATE , `physical_address` VARCHAR(255) , `postal_address` VARCHAR(255) , `contact_number` VARCHAR(75) , `email` VARCHAR(255) , PRIMARY KEY (`membership_number`) ) ENGINE = InnoDB; Now let’s see what the MySQL’s data types are. You can use any of them depending on your need. You should always try to not to underestimate or overestimate potential range of data when creating a database. DATA TYPESData types define the nature of the data that can be stored in a particular column of a table MySQL has 3 main categories of data types namely
Numeric Data typesNumeric data types are used to store numeric values. It is very important to make sure range of your data is between lower and upper boundaries of numeric data types.
Text Data TypesAs data type category name implies these are used to store text values. Always make sure you length of your textual data do not exceed maximum lengths.
Date / Time
Apart from above there are some other data types in MySQL.
Now let’s see a query for creating a table which has data of all data types. Study it and identify how each data type is defined in the below create table MySQL example. CREATE TABLE`all_data_types` ( `varchar` VARCHAR( 20 ) , `tinyint` TINYINT , `text` TEXT , `date` DATE , `smallint` SMALLINT , `mediumint` MEDIUMINT , `int` INT , `bigint` BIGINT , `float` FLOAT( 10, 2 ) , `double` DOUBLE , `decimal` DECIMAL( 10, 2 ) , `datetime` DATETIME , `timestamp` TIMESTAMP , `time` TIME , `year` YEAR , `char` CHAR( 10 ) , `tinyblob` TINYBLOB , `tinytext` TINYTEXT , `blob` BLOB , `mediumblob` MEDIUMBLOB , `mediumtext` MEDIUMTEXT , `longblob` LONGBLOB , `longtext` LONGTEXT , `enum` ENUM( '1', '2', '3' ) , `set` SET( '1', '2', '3' ) , `bool` BOOL , `binary` BINARY( 20 ) , `varbinary` VARBINARY( 20 ) ) ENGINE= MYISAM ; Best practices
How to create MySQL workbench ER diagram forward engineeringMySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically. We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database. Creating the MyFlix database from the MyFlix ER model Step 1) Open ER model of MyFlix database Open the ER model of MyFlix database that you created in earlier tutorial. Step 2) Select forward engineer Click on the database menu. Select forward engineer
Step 3) Connection options The next window, allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute
Step 4) Select the options shown below Select the options shown below in the wizard that appears. Click next
Step 5) Keep the selections default and click Next The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables. Keep the selections default and click Next.
Step 6) Review the SQL script The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql” file or copy the scripts to the clipboard. Click on next button
Step 7) Commit Progress The window shown below appears after successfully creating the database on the selected MySQL server instance. Summary
The Database along with Dummy Data is attached. We will be using this DB for all our further tutorials. Simple import the DB in MySQL Workbench to get started Click Here To Download MyFlixDB How do you set up a database?How do I Create and set up a Database?. Log into cPanel. The first step is to log in to your hosting account using cPanel. ... . Manage Databases. ... . Create a Database. ... . Create a User. ... . Add a User to the Database. ... . Assign the User to the Database. ... . Enabling Remote Database Access. ... . Managing Access Hosts.. How do I select a database in MySQL?You can use the SQL command use to select a database.. Example. Here is an example to select a database called TUTORIALS − [root@host]# mysql -u root -p Enter password:****** mysql> use TUTORIALS; Database changed mysql> ... . Syntax. mysqli_select_db ( mysqli $link , string $dbname ) : bool. ... . Example. ... . Output.. How do I create a new database in MySQL using terminal?Create a MySQL Database Using CLI. SSH into your server as root.. Log into MySQL as root: Copy. mysql -u root.. Create a new database user: Copy. GRANT ALL PRIVILEGES ON *. ... . Log out of MySQL by typing: \q .. Log in as the new database user you just created: Copy. mysql -u db_user -p. ... . Create a new database: Copy.. |