We can delete a table in 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;
A view is a database object whose values are based on the base table. It is a virtual table created by a query by joining one or more tables. It is operated similarly to the base table but does not contain any data of its own. If any changes occur in the underlying table, the same changes reflected in the View also. Following is the general syntax of creating a VIEW in MySQL: CREATE [OR REPLACE] VIEW view_name AS SELECT…
A trigger is a set of codes that executes in response to some events. A trigger in MySQL is a set of SQL statements that automatically “fires” or executes when a specific event occurs on a table, such as INSERT, UPDATE, or DELETE operations. Triggers are commonly used to enforce data integrity rules, audit changes to the database, or automate tasks based on certain conditions. They are defined to run either before or after the event that triggers them, allowing…
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. In MySQL, NOW() and CURRENT_DATE() are both functions used to retrieve the current date and time, but they return slightly different values: NOW(): This function returns the current date and time, including the time portion. It returns the date and time in the format ‘YYYY-MM-DD HH:MM:SS’. CURRENT_DATE(): This function returns only the current date…
MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory. The MySQL data directory is the location on a file system where MySQL stores its databases, tables, and related data files. By default, it’s typically located in /var/lib/mysql on Linux systems, but it can vary depending on the installation method and configuration. You…
How to dump a table from a database. # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql How we get Sum of column mysql> SELECT SUM(*) FROM [table name]; How to allow the user “sonia” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs # mysql -u root -p mysql> use mysql; mysql> grant usage on *.* to sonia@localhost identified by ‘passwd’; mysql> flush…
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id In MySQL, you can join tables on common columns using the JOIN clause in a SELECT statement. The common columns are specified in the ON clause. There are different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Here’s a basic example using INNER JOIN: sql SELECT * FROM table1 INNER JOIN table2 ON table1.common_column…
mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’ limit 1,5; To show all records starting with the letters ‘sonia’ and the phone number ‘9876543210’ limited to records 1 through 5 in MySQL, you can use the following SQL query: sql SELECT * FROM your_table_name WHERE column_name LIKE ‘sonia%’ AND phone_number_column = ‘9876543210’ LIMIT 1, 5; Replace your_table_name with the actual name of your table, column_name with the actual name of the column containing the names,…
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3); To load a CSV file into a table in MySQL, you can use the LOAD DATA INFILE statement. Here’s a basic example of how to do it: sql LOAD DATA INFILE ‘path_to_your_csv_file.csv’ INTO TABLE your_table_name FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 1 ROWS; — If your CSV file contains a header row Replace ‘path_to_your_csv_file.csv’ with…
mysql> SELECT DISTINCT columnname FROM tablename; To show unique records in MySQL, you can use the DISTINCT keyword in a SELECT statement. Here’s an example: sql SELECT DISTINCT column1, column2, … FROM your_table; Replace column1, column2, … with the columns for which you want to retrieve unique records, and your_table with the actual name of your table. If you want to select all columns, you can use the asterisk *: sql SELECT DISTINCT * FROM your_table; This query will return…