MySQL — Completely one-lined
A complete list of one liner explanations for Mysql commands
Hi there, the non-tech guy once again. In this, I’m adding the one liner explanations for the MySQL commands. I wanted to write this because many people doesn’t want long explanations for SQL as it is a easy English based one. This is why we need one liners which are easy to understand and easy to implement.
Basic commands :
My sql :
Create database : creates a database
Use database : uses a database
Create table : creates a table
drop table : deletes the table
Truncate : delete all the rows in table
Alter table — altering a table
Modify — modify the datatype
Change — change the name of the column and datatype
Insert into (table_name) values (values)- inserting values inside the column
Select — used to select the values
Select * from table — select all the values
Desc / Describe : used to describe the table showing the datatypes
Update — modify existing record (like column values etc)
Where — condition. Where the value is etc
Set — setting a value to a particular column
Delete — delete the entire values
Datatype :
- date : gives only the date
- datetime : date and time
- time — gives the time
- int — integer
- float — decimal values
- varchar — variable characters, presumbly string
- char — string datatype
Functions with date and time :
current_time() — gives the current time
now() — gives date and time
current_date() — gives the current time
utc_timestamp () — gives the universal time i.e european time
Types of joins :
Inner join : The MySQL INNER JOIN would return the records where table1 and table2 intersect.
Left outer : The MySQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
Right outer : The MySQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.
Locks :
Locks in MySQL
By default if we start a select * it is in lock in share mode
Select * from student for update — write lock exclusive
Lock is giving access to particular instances for this also we must set auto commit to 0 because we are gonna make changes
If you are placing a lock in table u cannot update the table = it will show lost connection
Read access vs shared lock : Read is (select *) and can be able to read a data. This is not locked for any purpose.
Shared lock : Session 1 can use and session 2 can use
Update cannot be used by session 1 and also session 2.
Read access is different from shared lock
Read committed :
- It will read only the stored data
- When inserted a data in the instance 1 that copy of data has been taken at the beginning of transaction
and when the same data has been accessed in the instance 2 the copy of data which is taken at the beginning only will be visible even after commit is used in instance 1.
Read uncommitted :
read uncommitted:read data modified within another transaction but not yet committed
Serializable : Making data stagnant and data is locked until there is a release.
Transaction Processing :
How do we do that in MySQL — We have keywords called — commit, start transaction etc
What is commit — Commit is like a savepoint
Where we save everything = making changes everything
rollback = revert
Note : My SQL runs on the auto commit mode.
*/ Start transaction
If everything is fine = commit
If anything goes wrong then = rollback
*/
First thing : set auto commit = 0 is what we do
Roll back and save point :
rollback makes sure things go back to original level
a save point ensures that there is data saved and when roll back occurs it goes back to the previous save point
for this rollback : we need to insert rollback ti save-point_name
When you find everything is okay, then it is committed by using commit. Commit will make changes to DB permanently.
To create a bookmark : Use save-point (give a name) and then we can create it
set transaction isolation level — read committed read uncommitted reputable read
These are some basic commands and I will update more as time goes by !
Thanks for reading
