Mysql_connect
Mysql_pconnect
MySQL
version on Linux using the below command :mysql-v
MySQL
in windows, opening the MySQL
command-line tool displayed the version information without using any flags. If we want to know more about the server information, use the below statement :SHOW VARIABLES LIKE "%version%";
TRUNCATE
* It removes all rows from a table.DELETE
* It removes Some or All rows from a table.DROP
* It removes a table from the database.ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];
MySQL
using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL
. The statement is as follows :DROP TABLE table_name;
CREATE TABLE OR ALTER TABLE
statement :[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (col_name, ...)
REFERENCES parent_tbl_name (col_name,...)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqld --init-file=C:\\mysql-notepadfile.txt
sp_
" while creating a stored procedure.SELECT
command. Also avoid unnecessary columns in SELECT
clause.%
) at the starting of a predicate.DISTINCT
and UNION
need to be used solely if it is necessary.SQL SELECT
statement is as follows:SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
ACID
properties. Common Table Expression(CTE)
. MySQL
is MyISAM
. Refer below to know the five types of tables :=, <, or <>
. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.NULL
and IS NOT NULL
.SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
NVL
function, IFNULL
function, and the ISNULL
function all of them are used to replace the NULL
value with another value. The ORACLE
users use the NVL
function, MySQL
users use the IFNULL
function and the SQL
servers use the ISNULL
functionSELECT column_1 * (column_2 + column_3) FROM Example_Table
SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_Table
SELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_Table
SELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table
CHAR_LENGTH
is character count whereas the LENGTH
is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings. LIKE
and REGEXP
operators are used to express with ^
and %
.SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
mysql_fetch_array
and mysql_fetch_object
:mysql_fetch_array()
: Returns a result row as an associated array or a regular array from database.mysql_fetch_object
: Returns a result row as object from database.SIX
triggers are allowed in MySQL
table. They are as follows :BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE
MySQL
:CREATE TABLE history (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE InnoDB;
INDEX
, a PRIMARY KEY
, or a FULLTEXT
index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it's quicker to find your result or, telling the SQL
engine where to go to find your data.ALTER TABLE history ADD INDEX(author(10));
ALTER TABLE history ADD INDEX(title(10));
ALTER TABLE history ADD INDEX(category(5));
ALTER TABLE history ADD INDEX(year);
DESCRIBE history;
mysql
: An interactive program that enables you to send SQL statements to the server and to view the results. You can also use mysql to execute batch scripts (text files containing SQL statements).mysqladmin
: An administrative program for performing tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not to be functioning properly.mysqldump
: A tool for backing up your databases or copying databases to another server.mysqlcheck
and myisamchk
: Programs that help you perform table checking, analysis, and optimization, as well as repairs if tables become damaged. mysqlcheck works with MyISAM tables and to some extent with tables for other storage engines. myisamchk is for use only with MyISAM tables.FOREIGN KEY
is a field (or assortment of it) in one table that alludes to the PRIMARY KEY
in another table. The FOREIGN KEY
requirement is utilised to forestall activities that would crush joins between tables.FOREIGN KEY
query. Something like this :FOREIGN KEY (Any_ID) REFERENCES Table_to_reference(Any_ID)
MySQL
is a process of moving data from one place to another place. It is a very useful method for backing up essential data or transferring our data between different locations. For example, we have a contact book database, which is essential to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.MySQL
, we need to use the ALTER TABLE
and CHANGE
commands together. The following are the syntax used to rename a column in MySQL
:ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];
S_ID
, but we want to change this with a more appropriate title as Stud_ID
. We will use the below statement to change its name :ALTER TABLE Student CHANGE COLUMN S_ID Stud_ID varchar(10);
MySQL
using the JOIN
clause. MySQL
allows various types of JOIN
clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL
:SELECT name, scores, address, email FROM Student s
INNER JOIN Marks m on s.stud_id = m.stud_id
INNER JOIN Details d on d.school_id = m.school_id;
SELECT name, scores, address, email
FROM Student s, Marks m, Details d
WHERE s.stud_id = m.stud_id AND m.school_id = d.school_id;
ALTER TABLE table_name DROP PRIMARY KEY;
MySQL
by simply CALL
query. This query takes the name of the stored procedure and any parameters we need to pass to it. The following is the basic syntax to execute a stored procedure :CALL stored_procedure_name (argument_list);
CALL Product_Pricing (@pricelow, @pricehigh);
Product_Pricing
calculates and returns the lowest and highest product prices.CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/examplefile.csv'
INTO TABLE tablename
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;
MySQL
, we can set the value for an AUTO_INCREMENT
column using the ALTER TABLE
statement as follows :ALTER TABLE table_name AUTO_INCREMENT = value;
TRUNCATE
is a DDL
command, and DELETE
is a DML
command.TRUNCATE QLbut
you can use it with DELETE
command.TRUNCATE
cannot be used with indexed views, whereas DELETE
can be used with indexed views.DELETE
command is used to delete data from a table. It only deletes the rows of data from the table while truncate is a very dangerous command and should be used carefully because it deletes every row permanently from a table.AUTO_INCREMENT
.NOT NULL
.MySQL
is not so efficient for large scale databases.COMMIT
and STORED PROCEDURES
functions version less than 5.0
.MySQL
is highly dependent on other addons.MySQL Server
as the UNIX
root user.MySQL
timestamp are stored as 32-bit
integers, but MySQL
timestamp is represented in the readable format of YYYY-MM-DD HH:MM:SS
format. select distinct(salary)from employee order by salary desc limit n-1,1
select distinct(salary)from employee order by salary desc limit 2,1
NOW()
command is used to show current year, month, date with hours, minutes, and seconds while CURRENT_DATE()
shows the current year with month and date only. CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').
*
Matches 0 more instances of the string preceding it.+
matches one more instances of the string preceding it.?
Matches 0 or 1 instances of the string preceding it. .
Matches a single character.[abc]
matches a or b or z|
separates strings^
anchors the match from the start."."
Can be used to match any single character. "|" can be used to match either of the two stringsREGEXP
can be used to match the input characters with the database.Select employee_name
From employee
Where employee_name REGEXP '1000'
Order by employee_name