Basic MySQL Command?

LOGIN to MySQL  for First Time.

** If first time using MySQL and/or never created new user before, simply use “root” to login.

sudo mysql -u root -p

CREATE DATABASE

CREATE DATABASEdatabasename

** we can change databasename with any name. Example I use “my_temp”. For example:

CREATE DATABASE DB_temp;

For example:

In this example, I have create “shaharil” as my USER for database named “my_temp” while ‘123’ is the password for user shaharil. The command “GRANT ALL” means to assign all privileges to that user but can only access (read, delete, modify or add data) in that specific database, not another.

Create the USER and make it grant access is used to connect your scripts (e.g: WordPress) to MySQL database. Its allow your scripts getting access to database.

CHECKING MYSQL VERSION

mysqladmin -u root -p version

SELECT DATABASE 

To see all databases. The command is:

SHOW databases;

SHOW EXISTING TABLE

To see all the tables in the db. The command is:

USE DB_temp;

SHOW tables;

CREATE TABLE IF NOT EXISTS

Lets create a table named ‘temp_table’ in the database ‘DB_temp’. In excel office, the ‘temp_table’  is a SHEET name while the ‘DB_temp’ is the file name that you saved in excel office.

In the table temp_table that contains the columns:

• date
• time
• value

CREATE TABLE IF NOT EXISTS temp_table(date DATE,time TIME,value NUMERIC);

The DATE, TIME and NUMERIC means the data types of variables that we created. For example:

DATE : A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30^th, 1973 would be stored as 1973-12-30.

TIME:  Stores the time in a HH:MM:SS format.

NUMERIC: Same like DECIMAL(M,D). An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is require NUMERIC is a synonym for DECIMAL.

DESCRIBE CHOSEN TABLE/ SHOW all content in the table

DESCRIBE temp_table;

It will shows all columns involved but not the data values. You also can used this command:

SHOW COLUMNS FROM temp_tables;

INSERT DATA INTO TABLE

INSERT INTO temp_table(date,time,value) value ('2020-02-11','10:00:00', 20.3)

SHOW ALL DATA in a TABLE

Show all data in a table. The command is:

SELECT * FROM temp_table;

SHOW SELECTED ROWS

For example, we want to see the

SELECT * FROM temp_table WHERE value = 20;

The other example is we want to show the rows that containing the time at ’10:00′ only and the value is 20.

SELECT * FROM temp_table WHERE time = '10:00' and value = 20;

DELETE DATA IS NULL

DELETE * FROM temp_table WHERE value is null;

DELETE DATA per ROW

DELETE FROM temp_table WHERE value=20;

DELETE ALL DATA BUT MAINTAIN COLUMN NAME

TRUNCATE TABLE temp_table;

OTHER EXAMPLE 1:

In the same database which is DB_temp, lets create a new table named deleterow. The command is:

CREATE TABLE IF NOT EXISTS deleterow(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(20));

Then, we insert some data in the table:

INSERT INTO deleterow(name) values('john');

INSERT INTO deleterow(name) values('mary');

OTHER EXAMPLE 2:

In the same database which is DB_temp, lets create a new table named cpu_monitor. The command is:

CREATE TABLE IF NOT EXISTS cpu_monitor(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, time TIME, temp VARCHAR(255));

INSERT INTO cpu_monitor(date, time, temp) values(current_date(),now(),28);

RESET AUTO INCREMENT OF id

Sometime, we have delete some data and we insert a new data. However, the id will start and increment for the last data that we deleted.

In order to reset back the id,  you can type the below command.

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

 

For example:

SET  @num := 0;

UPDATE list SET id = @num := (@num+1);

ALTER TABLE list AUTO_INCREMENT =1;

Suppose we have a students database for a subject. It is saved in a table named “list”. ‘

list

Then, we have the attendance of students on that day. So we can compare or look who are not attend during that day.

attendance

We can type this command:

MORE DETAIL can access to this website:

https://gist.github.com/keeperofthenecklace/3404559