Mysql_connectMysql_pconnectMySQL 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\binmysqld --init-file=C:\\mysql-notepadfile.txtsp_" 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_TableSELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_TableCHAR_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 INSERTAFTER INSERTBEFORE UPDATEAFTER UPDATEBEFORE DELETE andAFTER DELETEMySQL :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,1select distinct(salary)from employee order by salary desc limit 2,1NOW() 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