MySQL Commands: Show databases; Create database db_name; Use dbname; Show tables; Create table tb_name(id int, name varchar(20)); Desc tb_name; Insert into tb_name values(101 , ‘name of person’); Insert into tb_name (id) values(102); Update tb_name set name=’person name’ where id=102; Select * from tb_name; Delete from tb_name where id=102; Drop table tb_name; Drop database db_name; Rename table tb_old_name to tb_new_name; Alter table customer add (remark varchar(20)); Alter table customer modify remark varchar(25); Alter table customer modify remark varchar(20); Alter table customer…

Read More Read More

Introduction Note: “MySQL” it third party (“sun micro system”) C:\mysql –u root Types of Table (Engine) MyISAM: Foreign key constraint does not support InnoDB: used to support foreign key constraint BDB: support for UNIX environment Heap: it is temporary or virtual table, which is created only in memory not in hard disk Merge: it is used, if we want to merge more than one table (it is also temporary or virtual table) Syntax: Create table list ( — , —…

Read More Read More

mysql> SELECT * FROM tablename WHERE rec RLIKE “^r”; To find records beginning with “r” in MySQL with case sensitivity enforced, you would use the following query: sql SELECT * FROM your_table WHERE your_column REGEXP BINARY ‘^r’; This query uses the REGEXP BINARY operator to enforce case sensitivity, and ^r as the regular expression pattern to match any record beginning with “r”.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255)); To create a table in MySQL, you can use the CREATE TABLE statement followed by the table name and the list of columns with their data types and any constraints. Here’s an example: sql CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) UNIQUE, hire_date DATE, salary DECIMAL(10, 2), department_id INT );…

Read More Read More

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql To restore a database or a specific table from a backup in MySQL, you typically use the mysql command-line client or a similar tool. Here’s a general approach: Ensure you have a backup: First, make sure you have a recent backup of the database or table you want to restore. Access the MySQL command-line interface: Open your terminal or command prompt and log in to MySQL using a command like: css…

Read More Read More

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql To dump a MySQL database for backup, you can use the mysqldump command. Here’s the basic syntax: bash mysqldump -u username -p database_name > backup_file.sql Replace username with your MySQL username, database_name with the name of the database you want to backup, and backup_file.sql with the name you want to give to your backup file. After running this command, you’ll be prompted to enter your MySQL password. If you want to…

Read More Read More

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql To dump all databases in MySQL for backup, you can use the mysqldump command-line utility. Here’s the command: bash mysqldump -u username -p –all-databases > backup.sql Replace username with your MySQL username. When you run this command, it will prompt you to enter your MySQL password. After entering the password, it will dump all databases into a file named backup.sql. This backup.sql file contains SQL commands to recreate all databases, including their…

Read More Read More

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…

Read More Read More

mysql> alter table [table name] modify [column name] VARCHAR(3); mysql> alter table [table name] drop index [colmn name]; To make a column bigger in MySQL, you can use the ALTER TABLE statement along with the MODIFY COLUMN clause. Here’s an example: sql ALTER TABLE your_table_name MODIFY COLUMN your_column_name new_data_type; Replace your_table_name with the name of your table, your_column_name with the name of the column you want to modify, and new_data_type with the new data type and size you want to…

Read More Read More

mysql> alter table [table name] change [old column name] [new column name] varchar (50); mysql> alter table [table name] add unique ([column name]); To change the column name and make it unique in MySQL, you can use the ALTER TABLE statement. Assuming you want to change the column name from old_column to new_column and make it unique, you can execute the following SQL command: sql ALTER TABLE your_table CHANGE COLUMN old_column new_column datatype UNIQUE; Replace your_table with the name of…

Read More Read More