Database Queries

Database Queries ~~full-width~~

MySql Quries

Show Databases;Query is used to show the list of all Database is Present in your System.

Show Databases;

Create Databases;Query is used to Create the Database(Schema).

Create Database database_Name; or CREATE SCHEMA database_name;

use Query

use Databases;Query is used to Select the Database for creating the table.Remember without use database you cann't create table in the database.

use database_Name;

All Table show in Database

show tables;

Query is used to show the list of all table in the database.Befor this Query you can select the database where you are check the table using Use Query.

show tables;

Create Query

Create Table Query is used to Create the Table in the Database.You can create multiple table in the database according to your requirements.
not null is used when the user insert the data the user is necessary to insert the data in column in otherwise the data conn't be saved in database.It show error.if you not add the not null in your create table query then the user have the option when they input the data in the database if any field empty the data would be saved in the databases.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

         //Create Table

create table hassan(
id int(10) not null,
name varchar(20) not null,
fname varchar(30) not null,
primary key(id)
);

insert Query

Insert QueryQuery is used to insert the data in the Database.

insert into table_name(id,name,fname) values(1,'Hassan Ali','Riaz-ul-Hassan');

Select Query

Select TableQuery is used to show the Record that have saved in the table.

select * from table_name;

Drop Query

Drop TableQuery is used to Delete the table in the Database.

Drop table table_name;

Drop DatabaseQuery is used if you can delete the the complete the database then you can ente use drop database database_name; and your database is delete if you run this query.

Drop database database_name;

ALTER TABLE

Alter Rename ColumnQuery is used to rename the column name by using alter query.

ALTER TABLE (table_name) RENAME Column (original_table_name) TO (new_table_name);

Add Column in Table Query is used to Add the new column in the database by using alter query.

ALTER TABLE table_name ADD COLUMN new_column_name datatype(size);

ALTER TABLE - DROP COLUMN Query is used to Delete the column in the table by using alter query.

ALTER TABLE table_name DROP COLUMN column_name;

Alter ADD Primary Key in ColumnQuery is used to Add the Primary key in the column.But Remember Primary key is only apply on column follow the rule of primary key or empty column.

Alter table (table_name) ADD Constraint PRIMARY key(column_name);

Alter ADD Foreign Key in ColumnQuery is used to Add the foreign key in the column.But Remember foreign key is only apply on empty column.

Alter table (table_name) ADD Constraint foreign key(column_name) references course(course_code);

Alter ADD Default Constraints in ColumnQuery is used to Add the Default value in the database. If the user not Enter the Value of any column then the Default value would be set.

Alter table (table_name) Alter Column_name SET DEFAULT 'Value';

Primary and Foriegn key

Alter Rename ColumnWe have created two table one table name is hassan and other table name is ali.First we have create table hassan and create column in the table and write their primary key and datatypes.In other table ali we have created a column and one column name hassanID is take value from the primary key from other table and give the reference of the table.

create table hassan(
  -> id int not null,
  -> name varchar(200) not null,
  -> father_name varchar(100) not null,
  -> primary key(id)
  -> );


create table ali
  -> (
  -> hassanID int not null,
  -> auto_id int not null,
  -> name varchar(50) not null,
  -> primary key(auto_id),
  -> foreign key(hassanID) references hassan(id)
  -> );

Syntax :

//Primary Key Table
CREATE TABLE table_name
(
Column1 datatype,
Column2 datatype,  PRIMARY KEY (Column-Name)
.
);

                                 //Foreign Key Table
CREATE TABLE table_name(
column1    datatype,
column2    datatype,
constraint (name of constraint)
FOREIGN KEY [column1, column2...]
REFERENCES [primary key table name] (List of primary key table column) ...);

How to Find mySql Database File in System?

Simple you can Go to C Drive and Enter the Search menu C:\ProgramData then you see the mySql folder.

C:\ProgramData\MySQL\MySQL Server 8.0\Data

How to get Backup of mysql Database?

Simple open cmd and run as Administator then type cd C:\Program Files\MySQL\MySQL Server 8.0\bin and hit Enter.After this Process you can Enter the blow code;

mysqldump -u -p database_name > new_database_name.sql

How to get Restore of mysql Database?

Simple open cmd and run as Administator then type cd C:\Program Files\MySQL\MySQL Server 8.0\bin and hit Enter.But Remember First you Create the Database name(mean Create Schema b/c the tables are import to the Schema) where you Import/Restore the Database.After this Process you can Enter the blow code;

mysql -u root -p database_name < import_database_name.sql
mysql -u root -p [sql_database_create_name] < import_database_name;

How to get Restore of mysql Database in Xampp?

When you Import the mysql Database in Xampp the they show unicode Error then following code should be remove then your problem will be solve.

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;  //Change to
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Mysql Database Query

MySQL Connection Using CMD through XAMPP

cd../../

cd xampp/mysql/bin

mysql mean Mysql Database
-u mean Username
-p mean password
-h mean host like localhost
-P means Port Number where you Run Sql

mysql -u root -p -h localhost

mysql -u root -p -h localhost -P 3307

Arrow Error in Mysql

Arrow Error show in mysql if you have press the wrong mysql command then the arrow repeat when you press.Simply you can ctrl+cthen hit Enter this error will be resolve.

if you press Wrong Syntax then show the Arrow Sign

Press Control + C

MariaDB [(none)]> in CMD:

MariaDB [(none)]>is used to all operation perform it. Like show databases, drop database databasename, create database databasename.
ctr+c is used in cmd to exit the MariaDB [(none)] to C:\xampp\mysql\bin>

Show Tables in CMD:

DESCRIBE tablename;

Show Tables Database in CMD:

SELECT * FROM tablename;

AUTO_INCREMENT       // Use for auto Number Assign
PRIMARY KEY          // use for unique indentity in Database

Create Database:

CREATE DATABASE KHAN(Database Name);

USE DATABASE:

USE Query in Database is use for the Select the Database to Continue the Operation Like Create Table, Drop Table or Other.It is Necessary to Select Database Before to continue to create table in Database.

USE databasename;

DROP DATABASE:

DROP DATABASE is used to Delete the Database to the Database and use for Delete a table from Database.

DROP DATABASE databasename;
DROP TABLE table_name;

Create Table:

CREATE TABLE table_name
     (
     column_name1 data_type,
     column_name2 data_type,
     column_name3 data_type,
     ...
     );

MySQL Connection Query:

mysqli_connect(Server Name, User Name, Password, Database Name);

die(); is used when the query not run sucessfully then show the error message.

$conn =mysqli_connect("localhost","root","","crud(databasename)") or die("Connection Unsuccessfully");

SQL Query:

$sql = "SELECT * FROM databasename";

Run MySQL Connection Query:

mysqli_query(Connection Name, SQL Query);

Example:

$mysqliquery = mysqli_query($conn,$sql) or die("Query Unsucessful.");

Close MySQL Connection Query:

mysqli_close(Connection Name);

Example:

mysqli_close($conn);

Header Query in Database

header query is used in the database to Redirect the page to other page.When the data is send to Database then form will redirect.In location Section you can use the Code to the Form after Send Data Redirects.

header("Location:http://localhost/foldername/filename.php");

Example:

header("Location:http://localhost/crud/show.php");

$_GET & $_POST

Read About $_GET AND $_POST

Form Call Self in Database

<?php echo $_SERVER['PHP_SELF'];?>

Pagination

Query:

SELECT * FROM table_name LIMIT offset, Limit;

Example:

SELECT * FROM table_name LIMIT 0, 3;

How to Calculate Offset:

In Page Number you can Add Page Start Number

offset = (page Number -1) * Limit;
   offset = (1-1)*3 =0

Database Queries

MySql Quries

use Query

All Table show in Database

Create Query

insert Query

Select Query

Drop Query

ALTER TABLE

Primary and Foriegn key

How to Find mySql Database File in System?

How to get Backup of mysql Database?

How to get Restore of mysql Database?

How to get Restore of mysql Database in Xampp?

Mysql Database Query

MySQL Connection Using CMD through XAMPP

Arrow Error in Mysql

MariaDB [(none)]> in CMD:

Show Tables in CMD:

Show Tables Database in CMD:

Create Database:

USE DATABASE:

DROP DATABASE:

Create Table:

MySQL Connection Query:

Run MySQL Connection Query:

Close MySQL Connection Query:

Header Query in Database

$_GET & $_POST

Form Call Self in Database

Pagination

Hassan Ali

Post a Comment

Get the new best freebies straight into your inbox for free!

Contact Form

Database Queries

MySql Quries

use Query

All Table show in Database

Create Query

insert Query

Select Query

Drop Query

ALTER TABLE

Primary and Foriegn key

How to Find mySql Database File in System?

How to get Backup of mysql Database?

How to get Restore of mysql Database?

How to get Restore of mysql Database in Xampp?

Mysql Database Query

MySQL Connection Using CMD through XAMPP

Arrow Error in Mysql

MariaDB [(none)]> in CMD:

Show Tables in CMD:

Show Tables Database in CMD:

Create Database:

USE DATABASE:

DROP DATABASE:

Create Table:

MySQL Connection Query:

Run MySQL Connection Query:

Close MySQL Connection Query:

Header Query in Database

$_GET & $_POST

Form Call Self in Database

Pagination

Hassan Ali

Related Post

Post a Comment

Contact Form