MySQL is a widely used relational database management system. It is used by developers, database administrators, and data analysts to store and manage data. In this tutorial, we will discuss how to create a table in MySQL using the CREATE TABLE statement. The CREATE TABLE statement is used to create a new table in a … Read more
Here we are creating user with name manish and password for manish is password;
1 | CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password'; |
Creating a user and force user to change password at first login for that PASSWORD EXPIRE is used.
1 | CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE; |
Creating a user and expiring the password after 180 days, PASSWORD EXPIRE INTERVAL 180 DAY is used
1 | CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 180 DAY; |
Creating a user … Read more
We have two tables company and location. First table manages company details and second table stores location detail of company. one company can span in multiple location. Initially we have created table for company and location as below.
1 2 3 4 | create table company(id int(11) NOT NULL AUTO_INCREMENT, name varchar(50), description varchar(200), PRIMARY KEY(id)); |
1 2 3 4 | create table location(id int(11) NOT NULL AUTO_INCREMENT, name varchar(50), address varchar(200), PRIMARY KEY(id)); |
checking the description of both table
1 2 3 4 5 6 7 8 9 | mysql> desc company; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | description | varchar(200) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.09 sec) |
1 2 3 4 5 6 7 8 9 | mysql> desc location; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | address | varchar(200) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 3 rows in set (0.06 sec) |
Now We find that one company can … Read more
Some times we have to drop the foreign key constraint that already exists, for that we have to first get the foreign key constraint name. To see detailed description of created table we use following query.
1 | SHOW CREATE TABLE location; |
Result of above query is
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | mysql> SHOW CREATE TABLE location; +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ | Table | Create Table | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ | location | CREATE TABLE `location` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `address` varchar(200) DEFAULT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `company_fk_id` (`company_id`), CONSTRAINT `company_fk_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`i d`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ 1 row in set (0.08 sec) |
Here we can see that company_id column is referring company tables id … Read more
PHP MySQL CRUD involves the connection of PHP with the MySQL database and performing Create (Insert), Read (Select), Update (Modify), and Delete Operation on the table. PHP and MySQL connectivity is a very important topic while learning PHP here step by step PHP and MySQL connectivity and creat update delete and read operations are discussed using … Read more
To see created table structure show create table command is used Syntax
1 2 | SHOW CREATE TABLE TABLE_NAME; |
Example
1 2 | SHOW CREATE TABLE student; |
Result
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+ | Table | Create Table| +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+ | student | CREATE TABLE `student` ( `id` int(11) DEFAULT NULL, `first_name` varchar(30) DEFAULT NULL, `last_name` varchar(30) DEFAULT NULL, `semester` varchar(20) DEFAULT NULL, `address` varchar(80) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+ 1 row in set (0.17 sec) |
To avoid showing leading and trailing dots we use \G with command syntax is as below Example
1 2 | SHOW CREATE TABLE student\G |
Result *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) DEFAULT NULL, first_name varchar(30) DEFAULT … Read more
To set Time zone in GMT set time_zone is used with specified time_zone value
1 | SET time_zone='+00:00'; |
To check our change reflated on database use following query
1 | SELECT @@global.time_zone, @@session.time_zone; |
1 2 3 4 5 6 | +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | +00:00 | +--------------------+---------------------+ 1 row in set (0.00 sec) |
Read More Setting innodb_lock_wait_timeout Granting Privileges to users Show engine innodb status Finding all column name of table
