In the following query, we list all lastname held by 2 or more employees.
SELECT lastname, COUNT(*) c FROM employees GROUP BY lastname HAVING c > 1;
If you don't want a value to unique, you should add a unique constraint to the column. However, you would from time to time have to deal with a badly designed database.
Character: set of symbols and encodings Encoding: set of values assigned to characters Collation: set of rule for comparing a character set
Suppose you have the following characters,
M Y S Q L
that are assigned the following values:
13 25 19 17 12
sequentially. These numbers happen to be their order in the English alphabet. So A = 1, B = 2 and so on.
Recently, I had to copy a large database from one server to another. I had 100G free on the source server. Unfortunately, the server would run out of memory before the mysqldump would complete. So I had to find a way to make the mysqldumps smaller. This can be done by creating separate dumps for each table. Then I had to compress the files to conserve memory. Following is the script I wrote to accomplish this.
The Following SQL query will list all tables in a MySQL database and also list the row counts for each.
SELECT TABLE_NAME, TABLE_ROWS
FROM `information_schema`.`tables`
WHERE `table_schema` = 'mydatabase';
where mydatabase is the name or your database. The output will like something like the following:
+------------+------------+
| table_name | table_rows |
+------------+------------+
| lines | 2271 |
| links | 484 |
| word | 25004 |
+------------+------------+
If you have installed MySQL on your Mac and forgot your root password, these instructions are for you.
If a string contains a single quote, it will generate an error. mysqli->real_escape_string() escapes quotes for MySQL. See example:
Let's begin with some concepts:
Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
To reset MySQL password, you need to have root access to the machine. After logging in as root, start by shutting down MySQL:
$ /sbin/service mysqld stop
Verify that all mysql processes have been shut down.
$ ps waux | grep 'mysql'
For all processes that are listed
$ kill -9
Find your mysqld_safe.
$ locate mysqld_safe
My mysqld_safe is installed at /usr/bin/mysqld_safe. Yours might be elsewhere. Start MySQL without the grant tables.
mysqldump command is used to backup up MySQL databases. mysqldump has the following format:
mysqldump -u [login] -p [pass] [dbname] > [backup.sql]
where [backup.sql] is the file to which the backup should be written.
Backing up specific tables By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows: