How To Create Table In Mysql
Contents
How we can CREATE TABLE in MySQL?
Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you: mysql> SHOW TABLES; Empty set (0.00 sec) The harder part is deciding what the structure of your database should be: what tables you need and what columns should be in each of them. You want a table that contains a record for each of your pets. This can be called the pet table, and it should contain, as a bare minimum, each animal’s name. Because the name by itself is not very interesting, the table should contain other information. For example, if more than one person in your family keeps pets, you might want to list each animal’s owner. You might also want to record some basic descriptive information such as species and sex. How about age? That might be of interest, but it is not a good thing to store in a database. Age changes as time passes, which means you’d have to update your records often. Instead, it is better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not difficult. Storing birth date rather than age has other advantages, too:
You can use the database for tasks such as generating reminders for upcoming pet birthdays. (If you think this type of query is somewhat silly, note that it is the same question you might ask in the context of a business database to identify clients to whom you need to send out birthday greetings in the current week or month, for that computer-assisted personal touch.) You can calculate age in relation to dates other than the current date. For example, if you store death date in the database, you can easily calculate how old a pet was when it died.
You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient: name, owner, species, sex, birth, and death. Use a CREATE TABLE statement to specify the layout of your table: mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); VARCHAR is a good choice for the name, owner, and species columns because the column values vary in length. The lengths in those column definitions need not all be the same, and need not be 20, You can normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement. Several types of values can be chosen to represent sex in animal records, such as ‘m’ and ‘f’, or perhaps ‘male’ and ‘female’, It is simplest to use the single characters ‘m’ and ‘f’, The use of the DATE data type for the birth and death columns is a fairly obvious choice. Once you have created a table, SHOW TABLES should produce some output: mysql> SHOW TABLES; +-+ | Tables in menagerie | +-+ | pet | +-+ To verify that your table was created the way you expected, use a DESCRIBE statement: mysql> DESCRIBE pet; +-+-+-+-+-+-+ | Field | Type | Null | Key | Default | Extra | +-+-+-+-+-+-+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +-+-+-+-+-+-+ You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what types they have. For more information about MySQL data types, see Chapter 11, Data Types,
How to create a table in MySQL student?
Creating Tables – To see the tables in your database do: mysql> show tables; To create a table for students in this class we can do: mysql> CREATE TABLE student (lastName VARCHAR(20), firstName VARCHAR(20), -> major VARCHAR(20), bDay DATE); The format for the DATE is YYYY-MM-DD. To see if the table has been correctly set up do: mysql> DESCRIBE student; To enter data into the table use the command: mysql> INSERT INTO student -> VALUES (‘Duck’, ‘Donald’, ‘Dance’, ‘1934-06-09’);
How do I create a user in MySQL?
Create a new MySQL user account – MySQL defines users with a username and the hostname or IP address that they’re using to access the MySQL instance. To create a new user in MySQL, specify the username, the hostname the user can use to access the database management system, and a secure password: mysql> CREATE USER ‘local_user’@’localhost’ IDENTIFIED BY ‘password’; This command will allow the user with username local_user to access the MySQL instance from the local machine (localhost) and prevent the user from accessing it directly from any other machine. Alternatively, you can use a wildcard character (%) in the host definition to grant access to the MySQL instance for a user: mysql> CREATE USER ‘subnet_user’@’10.0.%’ IDENTIFIED BY ‘password’; In the above example, the `10.0.%` specifies that the user can access the MySQL instance from any client that has an IP address beginning with `10.0.`. You may use the wild card at any level of the IP address in the host definition. To view all users in your MySQL instance, use the SELECT command: mysql> SELECT * FROM mysql.user;
How to use a database in MySQL?
MySQL – USE Statement The USE statement of MySQL helps you to select/use a database. You can also change to another database with this statement. Once you set the current database it will be same until the end of the session unless you change the it.
How to create table in MySQL with primary key command?
SQL Query to Create Table With a Primary Key A primary key uniquely identifies each row table. It must contain unique and non-NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called composite keys. To view whether “Emp_ID” is the primary key or not we use Describe command to view the structure of the Table. DESCRIBE is used to describe something. Since in database we have tables, that’s why we use DESCRIBE or DESC(both are same) command to describe the structure of a table. Query: DESCRIBE Employee; Or DESC Employee; Output: Now, to create a PRIMARY KEY constraint on any column when the table already exists (NO EARLIER PRIMARY KEY DEFINED), use the following SQL Syntax: ALTER TABLE ADD PRIMARY KEY (ID); Query: ALTER TABLE Employee ADD PRIMARY KEY (Phone_No); Output: If any earlier primary key is defined, then there will be errors like; Output: This error is because; Only one primary key can exist. So, we have to first delete the initial PRIMARY KEY to create a new PRIMARY KEY.1. To create PRIMARY KEY on multiple columns: Query: CREATE TABLE `Employee` ( `Emp_ID` VARCHAR(20) NOT NULL, `Name` VARCHAR(50) NOT NULL, `Age` INT NOT NULL, `Phone_No` VARCHAR(10) NOT NULL, `Address` VARCHAR(100) NOT NULL, PRIMARY KEY (`Emp_ID`,`Name`)); Output: 2. Add Multiple Primary Keys when Table already existing Query: ALTER TABLE Employee ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Emp_ID, NAME); DESC Employee; Output:
Last Updated : 13 Sep, 2021 Like Article Save Article
: SQL Query to Create Table With a Primary Key
