The LENGTH() function in MySQL returns the length of a string in bytes. Multi-byte characters will count as multiple bytes. The examples in this post will use the LENGTH() function but you can substitute them with the CHAR_LENGTH() function instead if you want to count the number of characters rather than bytes. Note that CHAR_LENGTH will treat a character that uses two bytes a one single character and would return 1.
The first example below illustrates what LENGTH() will return:
SELECT LENGTH('this is a test');
This will return the number 14.
To work out the length of the largest string in a MySQL table, combine the LENGTH() and MAX() functions together like so:
SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;
where “field_to_query” is the fieldname you want to query and table_to_query is the table. This will then return the length of the longest field named “field_to_query” as an integer.
This can be quite useful if you’ve loaded data from an external source into a table and want to ensure you have enough storage space for the data. I need to regularly load data from a text file into a MySQL table and one of the fields is labelled “long_description”. Initially I created the field as a VARCHAR(255) and then ran the above query against the table only to discover the longest field(s) were 255 bytes long. I then changed the table to type TEXT and ran the query again and found out the longest field was acutally 1210 bytes long.
This final example will show all the unique lengths along with a count:
SELECT LENGTH(field_to_query), COUNT(*) FROM table_to_query GROUP BY LENGTH(field_to_query);
To order it by the those with the highest count first, add “ORDER BY COUNT(*) DESC” and to order it by the longest count first, add “ORDER BY LENGTH(field_to_query) DESC” e.g.:
SELECT LENGTH(field_to_query), COUNT(*) FROM table_to_query GROUP BY LENGTH(field_to_query) ORDER BY COUNT(*) DESC
SELECT LENGTH(long_description), COUNT(*) FROM table_to_query GROUP BY LENGTH(field_to_query) ORDER BY LENGTH(field_to_query) DESC
UPDATE store_list SET customer_id = REPLACE(customer_id, ‘09-’, ‘02-’) LIMIT 1
SELECT REPLACE(’www.mysql.com’, ‘w’, ‘Ww’);
Above statement will return ‘WwWwWw.mysql.com’ as result.
WHERE somefileld IN (1,2,3)
LOAD DATA LOCAL INFILE ‘/directory.csv’ INTO TABLE `directory_data` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\’ LINES TERMINATED BY ‘rn'(`cat_name` , `item_name` , `phone` , `address` , `zip_code` )
mysql -u phpmyadmin -p -h localhost mipueblo_data
where you need to replace domain with your actual domain and example.org with correct domain and .tld.
-u is the switch to specify the user. Your MySQL username is xdomain where domain is your domain.
-p tells MySQL to prompt for a password
-h tells MySQL what host to use. Your host is mysql.example.org (replace with your actual domain and .tld)
xdatabase in the above command line instruction is the name of the database.
SELECT * FROM `directory_data` WHERE cat_name =’CARNIVALS’;
SELECT user_email, count( user_email )
FROM phpbb_users
GROUP BY user_email
HAVING count( * ) >1 LIMIT 0 , 30
The most normal use of mysqldump is probably for making a backup of whole databases. See section 4.4.1 Database Backups.
mysqldump –opt database > backup-file.sql
You can read this back into MySQL with:
mysql -u user -p database < backup-file.sql
rsync -a /home/webadmin/ /backups/
tar jcf /home/webadmin/backup.tar.bz2 /backups
Select
Return all records all columns in a table:
select * from TableName
Return all records but only field1 and field2 in a table:
select field1, field2 from TableName
Return field1 for all records in a table with a specific value for field2:
select field1 from TableName where field2=123
Return the number of records in a table:
select count(*) from TheTable
Return the number of records in a table with a specific value for field2:
select count(*) from TableName where field2=123
Simple join:
select * from table1, table2
where table1.field1=table2.fieldA
or
select table1.field1, table2.fieldA from table1, table2
where table1.field2=table2.fieldB
Select all unique values in field1 from a table:
select distinct(field1) from TableName
or
select distinct field1 from TableName
Select the number of unique values:
select count(*) from
(select distinct field1 from TableName)
Select all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single field:
select field, count(field) from TableName
group by field
having count(*) > 1
Select all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of fields:
select field1, field2, count(*) from TableName
group by field1, field2
having count(*) > 1
Select similar records, i.e. all records which have duplicate field1 and field2 in a table but with different field3 (i.e. specifying which fields must be the same and which different):
select * from table as A, table as B
where A.field1=B.field1
and A.field2=B.field2
and A.field3<>B.field3;
Note:
It is important to specify at least one field which is different between the two records otherwise this query will list a record as being the same as itself.
This query will not find duplicate records, i.e. records with every field the same.
Select all records from a table which do not share a common ID with records from a second table:
select * from table1
where field1 not in (select field2 from table2)
Note: Sub-queries are quite slow and are not supported in MySQL, so the above will not work in MySQL.
or an alternative using a join (which can be much faster):
select table1.* from table1
left join table2 on (table1.field1 = table2.field2)
where table2.field2 is null;
The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):
select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)
To perform a two way join:
select * from
table1 left join table2 on (table1.field1 = table2.field1),
table1 left join table3 on (table1.field2 = table3.field3)
this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.
To combine the results of two queries (be aware that the number and types of fields in both queries must agree):
select * from table1
union select * from table2
To return a value based on the contents of a field. This can be done using either Iif, Decode or Case, depending on the database.
The following works with MSAccess:
select Iif(field1 = 1, ‘one’, ‘not one’)
from TableName
This is equivalent to the following on SqlServer:
select Case field1 = 1 then ‘One’ else ‘Two’ End
from TableName
For Oracle use the DECODE function.
——————————————————————————–
Insert
Insert new record into a table:
insert into TableName values (1,2,3)
Insert new record into a table explicitly naming fields:
insert into TableName (field1,field2,field3) values (1,2,3)
Insert new record into a table using values from another table:
insert into TableName (field1,field2,field3)
select fieldA,2,fieldC from SomeTable
——————————————————————————–
Update
Update all records in a table:
update TableName set field1=2
Update specific records in a table:
update TableName set field1=2 where field1=1
To update more than one field at a time:
update TableName set field1=2, field2=3
Update a field in a table using a value from another table where both records are referenced by a common key:
update TableOne
inner join TableTwo on TableOne.commonID = TableTwo.commonID
set TableOne.field1 = TableTwo.fieldX
or
update TableOne, TableTwo
set TableOne.field1 = TableTwo.fieldX
where TableOne.commonID = TableTwo.commonID
——————————————————————————–
Delete
Delete all records in a table (dangerous):
delete from TableName
Delete specific records in a table:
delete from TableName where field1=1
Delete records from one table which do not have a matching field in another table:
delete from TableName where field1 not in
(select field2 from TableTwo)
——————————————————————————–
Keys
Be aware that there are often subtle syntax variations between different database systems. Also other key properties (for example ‘clustered’) will vary between database systems. Therefore please treat this part of the SQL crib sheet as a guide only.
Create a primary key on a table:
Alter Table TheTable Add Primary Key (field1, field2)
To add an index on a field:
alter table TableName Add Index (field1)
To remove a primary key:
alter table drop primary key
Creating a Crontab
What is Crontab?
Crontab is a program that allows users to create jobs that will run at a given time. Each individual user has their own crontab and the entire system has a crontab that can only be modified by those with root access. If you are adding a crontab for an individual user you must sign on as that user first. (ex. su userid)
The syntax of this file is very rigid. There are six fields to a file, each separated by a space. The first five fields specify exactly when the command is to be run; the sixth field is the command itself. The first five fields are:
Format
minute hour day month weekday command
Minute – Minutes after the hour (0-59).
Hour – 24-hour format (0-23).
Day – Day of the month (1-31).
Month – Month of the year (1-12).
Weekday – Day of the week. (0-6; the 0 refers to Sunday).
Asterisks (*) specify when commands are to be run in every instance of the value of the field. For instance, an asterisk in the Month field would mean that the command should be run every month. In addition, multiple events can be scheduled within a field by separating all instances with commas – with no space between.
Options
crontab -e
Edits the current crontab or creates a new one. *
crontab -l
Lists the contents of the crontab file.
crontab -r
Removes the crontab file.
Creating a Crontab for use with AccessWatch
You must first sign on as the individual user you are adding the crontab for. Do not create crontab while you are signed on as “su” under root or you will change the root crontab. After you sign on as root su as individual user.
su userid ex. su nercols
Change editor to Joe from VI:
EDITOR=joe
export EDITOR
Create the crontab from the command line:
crontab -e
Insert the following lines and the save the file (Ctrl-kx):
30 23 * * * /home/userid/www/aw/aw-parser.pl -d
45 23 * * * /home/userid/www/aw/aw-report.pl
AccessWatch will run against the log files in their home directory every evening at 11:30 and then generate a report at 11:45 p.m..
Type man crontab for more detailed information. If you don’t want to set up a crontab just run the by hand when you want a report.
./aw-parser.pl
./aw-report.pl