Mysql Basic Part1
MySQL is Relational Database Management system, which allow you to stored data in the form table, or data in rows or columns. It Used Structured Query language to fetch the data from the table. It is an open source software.
Term used in DBMS.
Record : In DBMS record is known as Row.
Field: In DBMS Field is known as column.
Creating users in mysql
To create users in mysql ,you should be login as root user.
Query for creating user.
Create user ‘username’ @’localhost’ identified by ‘user_password’.
@localhost means the user can access the mysql server from that computer only, where mysql is running.
How to view users in Mysql.
Below query is used for viewing the users in Mysql.
Select user from mysql.user;
The data will be fetch from the user table of mysql database.
How to remove users from the Mysql.
we can use Drop command to remove the user from the Mysql.The below syntax is used for removing the user from the mysql.
Drop user ‘username’ @localhost.
How to view the databases present in Mysql Server.
format or syntax for viewing the databases present in the Mysql Server is.
Show databases;
How to create database in Mysql.
Below is the Syntax for creating database in Mysql.
create database your_database_name;
viewing the database.
As we can see that ,the database(practice )is created.
Selecting the database.
Below is the syntax for selecting database from the mysql.
use your_database_name;
How to create table in Mysql.
Before creating table,you have to select the database,here i will be using the Practice database. My table will be store in Practice database. I will store dummy data in the table.
Create table your_table_name(field1_Name datatype,field2_Name datatype,…);
in the above, query varchar is datatype, the number inside the varchar represent the size of the data,e.g in Emp_id field,the number of character it can store is upto 20.
How to see the tables in the database.
Syntax for viewing the tables.
show tables;
How to Rename the Table.
rename table old_table_name to your_new_table_name;
let see the table.
Tabled_name is changed from emp to employee.
How to enter data in the table.
syntax for entering data in table.
insert into table_name values(‘Field1data’,’Field2 data’,…..);
How to view the record of the table.
we can use select command to view the record of the data.
command
select * from table_name;
The asterisk means fetch all the records.
we can extract the particular field or column of the table.
below is the command
select colname1,colname2 from table_name;
Entering multiple records.
the below command is used for entering multiple records.
insert into table_name(fieldName1, fieldName2,….) values(‘Record1field1data,’Record1field2data’,…),(‘Record2field1data,’Record2field2data’,…);
Viewing the record
As, the record is successfully entered.
How to Rename the column.
Using alter and change keyword we can change the column name of the table
Command for renaming the column is.
Alter table table_name change old_colname New_colname datatype;
let view the record of the employee table.
As ,the E_Name has been change to Employee_name.
How to change the size datatype of the column.
Alter and modify keyword can be used to change the datatype of the column.
Alter table table_name modify colname new datatype;
let see the structure of the table.
Now let change the datatype of Employee_name.
The query get executed without error.
Get Vijaythapa’s stories in your inbox
Join Medium for free to get updates from this writer.
Now let check the structure of the table.
As we see that Employee_name field datatype size is changed.
How Rearrange the column of the table.
Command
alter table table_name modify column columnname datatype first;
The above the command will set the specified columnname as first filed.
As,the column Emp_id is the set as first field of the table.
alter table table_name modify column colname1 datatype after colname2;
the above command will place colname1 after colname2.
How to delete the table from the database.
first you have to select the database, in which table is stored.
we can use drop and truncate command to delete the table.
Drop table table_name;
when you want to delete the record as well as the Schema of the table use Drop command.
Truncate table table _name;
The truncate command will delete the record of the table but the Schema of the table will remain.
now let select the table, after using the truncate query on it.
as the query return an empty set, that means there is no record present in the table.
let check the whether the structure of the table is remain or not.
The structure of the table is remain after using the truncate query.
Now let use the drop command.
Now let use select query ,on the table to see what does it return.
As, compare to truncate ,the Drop query return an error, as it delete the record as well as structure of the table.
let use desc query on the table what it will return.
Unlike truncate which keep the structure of the table, the drop command delete the record as well as the the structure of the table.
