MySQL Server
MySQL is an open-source database management system, commonly installed as part of the popular LAMP
(Linux, Apache, MySQL, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query
Language) to manage its data.
update your package index, install the MySQL-server package, and then run the included security script.
sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
systemctl start mysql
sudo systemctl enable mysql
At the command prompt, run the following command to launch the mysql shell and enter it as the root user:
mysql -u root -p
When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one,
press Enter to submit no password.
The following MySQL shell prompt should appear:
mysql>
MySQL Create Database
CREATE DATABASE employees;
SHOW DATABASES;
USE employees;
MySQL Drop Database
DROP DATABASE employees;
MySQL CREATE TABLE
Syntax:
CREATE TABLE table_name (column_name column_type...);
Example:
Here, we will create a table named "cus_tbl" in the database "customers".
CREATE TABLE cus_tbl(
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(100) NOT NULL,
surname VARCHAR(100) NOT NULL,
age VARCHAR (100) NOT NULL,
PRIMARY KEY ( id )
);
Note:
Here, NOT NULL is a field attribute and it is used because we don't want this field to be NULL. If you will try to
create a record with NULL value, then MySQL will raise an error.
The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next available number to the
id field.PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by
comma to define a primary key.
See the created table:
SHOW tables;
See the table structure:
DESCRIBE cus_tbl;
MySQL ALTER Table
The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the
situation.
1) ADD a column in the table
ALTER TABLE cus_tbl ADD phone varchar(40) NOT NULL;
2) Add multiple columns in the table
ALTER TABLE cus_tbl
ADD address varchar(100) NOT NULL
AFTER surname,
ADD salary int(100) NOT NULL
AFTER age;
In this example, we add two new columns 'address" after surname and "salary" in after age column.
See the recently added columns:
SELECT* FROM cus_tbl;
3) MODIFY column in the table
ALTER TABLE cus_tbl
MODIFY surname varchar(50) NULL;
In this example, we modify the column surname to be a data type of varchar(50) and force the column to allow
NULL values.
4) DROP column in table
ALTER TABLE cus_tbl
DROP COLUMN address;
5) RENAME column in table
ALTER TABLE cus_tbl
CHANGE COLUMN surname title
varchar(20) NOT NULL;
In this example, we will change the column name "surname" to "title".
6) RENAME table
ALTER TABLE cus_tbl
RENAME TO cus_table;
MySQL TRUNCATE Table
MYSQL TRUNCATE statement removes the complete data without removing its structure.
TRUNCATE TABLE cus_tbl;
See the table:
SELECT* FROM cus_tbl;
it will show empty table.
MySQL DROP Table
DROP TABLE cus_tbl;
MySQL Insert Query
MySQL insert query is used to insert records into table. For example:
1) Insert Query
insert into cus_tbl values(1,'abhi','zarkar','chandrapur','23','200000');
2) MySQL Update Query
update cus_tbl set firstname='abhijit', address='nagpur' where id=1;
3) MySQL Delete Query
delete from cus_tbl where id=1;
4)inserting multiple records
INSERT INTO cus_tbl
(Id, firstname, surname, address, age, salary) VALUES
(1, 'Abhijit', 'zarkar','chandrapur','23','20000'),
(2, 'devendra', 'ramtekar','nagpur','25','20000'),
(3, 'abhishek', 'dahare','ramtek','24','20000');
How to Create a New User
mysql> CREATE USER 'abhijit'@'localhost' IDENTIFIED BY 'temp123';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'abhijit'@'localhost';
(*.*--> *=database, *=tables)
always be sure to reload all the privileges.
mysql> FLUSH PRIVILEGES;
To provide a specific user with a permission, you can use this framework:
GRANT type_of_permission ON database_name.table_name TO ‘username’@'localhost’;
delete a user
mysql> DROP USER ‘abhijit’@‘localhost’;
Use below SQL query to see list of mysql users.
mysql> SELECT User FROM mysql.user;
If you want to list of MySQL user information, including user permission information and all user's data.
mysql> select User, Host, Password from mysql.user;
