In MySQL, regular expressions are used in queries for searching a pattern in a string. * 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…
Syntax and Queries 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);…
mysql> SELECT * FROM tablename WHERE name != “sonia” AND phone_number = ‘9876543210’ order by phone_number; To retrieve all records not containing the name “sonia” and the phone number ‘9876543210’ from a MySQL table, and order the results by the phone_number field, you can use the following SQL query: sql SELECT * FROM your_table_name WHERE name != ‘sonia’ AND phone_number != ‘9876543210’ ORDER BY phone_number; Replace your_table_name with the actual name of your table. This query selects all columns (*)…
Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command: mysqldump -u username -p “password” -R oldDbName > oldDbName.sql Now, use the…
A USER in MySQL is a record in the USER-TABLE. It contains the login information, account privileges, and the host information for MySQL account to access and manage the databases. We can create a new user account in the database server using the MySQL Create User statement. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. The following is the basic syntax to create a new user in MySQL: CREATE USER [IF NOT EXISTS] account_name…
MySQL is a free, fast, reliable, open-source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users. MySQL uses only just under 1 MB of RAM on your laptop, while Oracle 9i installation uses 128 MB. MySQL is great for database enabled websites while Oracle is made for enterprises. MySQL is portable.
A defined point in any transaction is known as savepoint. SAVEPOINT is a statement in MySQL, which is used to set a named transaction savepoint with the name of the identifier. A savepoint in MySQL is a point within a transaction where you can roll back to if needed. It allows you to set a named marker within a transaction so that you can later roll back to that specific point if necessary, rather than rolling back the entire transaction….
In MySQL, the “i-am-a-dummy” flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present. The “i-am-a-dummy” flag in MySQL is a humorous option that can be used as a safety measure. When enabled, it prevents accidental updates or deletes on a table by issuing an error message instead. It’s often used in development or testing environments to avoid unintentional data modifications. However, it’s not typically used in production environments due to its…
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…