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.<\/p>\n
The first example below illustrates what LENGTH() will return:<\/p>\n
SELECT LENGTH('this is a test');<\/pre>\nThis will return the number 14.<\/p>\n
To work out the length of the largest string in a MySQL table, combine the LENGTH() and MAX() functions together like so:<\/p>\n
SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;<\/pre>\nwhere “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.<\/p>\n
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.<\/p>\n
This final example will show all the unique lengths along with a count:<\/p>\n
SELECT LENGTH(field_to_query), COUNT(*)\r\nFROM table_to_query GROUP BY LENGTH(field_to_query);<\/pre>\nTo 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.:<\/p>\n
SELECT LENGTH(field_to_query), COUNT(*)\r\nFROM table_to_query GROUP BY LENGTH(field_to_query)\r\nORDER BY COUNT(*) DESC<\/pre>\nSELECT LENGTH(long_description), COUNT(*)\r\nFROM table_to_query GROUP BY LENGTH(field_to_query)\r\nORDER BY LENGTH(field_to_query) DESC<\/pre>\nUPDATE store_list SET customer_id = REPLACE(customer_id, \u201809-\u2019, \u201802-\u2019) LIMIT 1
\nSELECT REPLACE(\u2019www.mysql.com\u2019, \u2018w\u2019, \u2018Ww\u2019);<\/p>\n
Above statement will return \u2018WwWwWw.mysql.com\u2019 as result.<\/p>\n
WHERE somefileld IN (1,2,3)<\/p>\n
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` )<\/p>\n
mysql -u phpmyadmin -p -h localhost mipueblo_data<\/p>\n
where you need to replace domain with your actual domain and example.org with correct domain and .tld.<\/p>\n
-u is the switch to specify the user. Your MySQL username is xdomain where domain is your domain.<\/p>\n
-p tells MySQL to prompt for a password<\/p>\n
-h tells MySQL what host to use. Your host is mysql.example.org (replace with your actual domain and .tld)<\/p>\n
xdatabase in the above command line instruction is the name of the database.<\/p>\n
SELECT * FROM `directory_data` WHERE cat_name =’CARNIVALS’;
\nSELECT user_email, count( user_email )
\nFROM phpbb_users
\nGROUP BY user_email
\nHAVING count( * ) >1 LIMIT 0 , 30<\/p>\n
The most normal use of mysqldump is probably for making a backup of whole databases. See section 4.4.1 Database Backups.<\/p>\n
mysqldump –opt database > backup-file.sql
\nYou can read this back into MySQL with:<\/p>\n
mysql -u user -p database < backup-file.sql<\/p>\n
rsync -a \/home\/webadmin\/ \/backups\/
\ntar jcf \/home\/webadmin\/backup.tar.bz2 \/backups<\/p>\n
Select
\nReturn all records all columns in a table:<\/p>\n
select * from TableName
\nReturn all records but only field1 and field2 in a table:<\/p>\n
select field1, field2 from TableName
\nReturn field1 for all records in a table with a specific value for field2:<\/p>\n
select field1 from TableName where field2=123
\nReturn the number of records in a table:<\/p>\n
select count(*) from TheTable
\nReturn the number of records in a table with a specific value for field2:<\/p>\n
select count(*) from TableName where field2=123
\nSimple join:<\/p>\n
select * from table1, table2
\nwhere table1.field1=table2.fieldA
\nor<\/p>\n
select table1.field1, table2.fieldA from table1, table2
\nwhere table1.field2=table2.fieldB<\/p>\n
Select all unique values in field1 from a table:<\/p>\n
select distinct(field1) from TableName
\nor<\/p>\n
select distinct field1 from TableName
\nSelect the number of unique values:<\/p>\n
select count(*) from
\n(select distinct field1 from TableName)
\nSelect all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single field:<\/p>\n
select field, count(field) from TableName
\ngroup by field
\nhaving count(*) > 1
\nSelect all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of fields:<\/p>\n
select field1, field2, count(*) from TableName
\ngroup by field1, field2
\nhaving count(*) > 1
\nSelect 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):<\/p>\n
select * from table as A, table as B
\nwhere A.field1=B.field1
\nand A.field2=B.field2
\nand A.field3<>B.field3;
\nNote:<\/p>\n
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.
\nThis query will not find duplicate records, i.e. records with every field the same.
\nSelect all records from a table which do not share a common ID with records from a second table:<\/p>\n
select * from table1
\nwhere field1 not in (select field2 from table2)
\nNote: Sub-queries are quite slow and are not supported in MySQL, so the above will not work in MySQL.<\/p>\n
or an alternative using a join (which can be much faster):<\/p>\n
select table1.* from table1
\nleft join table2 on (table1.field1 = table2.field2)
\nwhere table2.field2 is null;<\/p>\n
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):<\/p>\n
select * from table1
\nwhere not exists (select field2 from table2 where table2.field2 = table1.field1)<\/p>\n
To perform a two way join:<\/p>\n
select * from
\ntable1 left join table2 on (table1.field1 = table2.field1),
\ntable1 left join table3 on (table1.field2 = table3.field3)<\/p>\n
this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.<\/p>\n
To combine the results of two queries (be aware that the number and types of fields in both queries must agree):<\/p>\n
select * from table1
\nunion select * from table2
\nTo return a value based on the contents of a field. This can be done using either Iif, Decode or Case, depending on the database.<\/p>\n
The following works with MSAccess:<\/p>\n
select Iif(field1 = 1, ‘one’, ‘not one’)
\nfrom TableName
\nThis is equivalent to the following on SqlServer:<\/p>\n
select Case field1 = 1 then ‘One’ else ‘Two’ End
\nfrom TableName
\nFor Oracle use the DECODE function.
\n——————————————————————————–<\/p>\n
Insert
\nInsert new record into a table:<\/p>\n
insert into TableName values (1,2,3)
\nInsert new record into a table explicitly naming fields:<\/p>\n
insert into TableName (field1,field2,field3) values (1,2,3)<\/p>\n
Insert new record into a table using values from another table:<\/p>\n
insert into TableName (field1,field2,field3)
\nselect fieldA,2,fieldC from SomeTable<\/p>\n
——————————————————————————–<\/p>\n
Update
\nUpdate all records in a table:<\/p>\n
update TableName set field1=2
\nUpdate specific records in a table:<\/p>\n
update TableName set field1=2 where field1=1
\nTo update more than one field at a time:<\/p>\n
update TableName set field1=2, field2=3
\nUpdate a field in a table using a value from another table where both records are referenced by a common key:<\/p>\n
update TableOne
\ninner join TableTwo on TableOne.commonID = TableTwo.commonID
\nset TableOne.field1 = TableTwo.fieldX<\/p>\n
or<\/p>\n
update TableOne, TableTwo
\nset TableOne.field1 = TableTwo.fieldX
\nwhere TableOne.commonID = TableTwo.commonID<\/p>\n
——————————————————————————–<\/p>\n
Delete
\nDelete all records in a table (dangerous):<\/p>\n
delete from TableName
\nDelete specific records in a table:<\/p>\n
delete from TableName where field1=1
\nDelete records from one table which do not have a matching field in another table:<\/p>\n
delete from TableName where field1 not in
\n(select field2 from TableTwo)<\/p>\n
——————————————————————————–<\/p>\n
Keys
\nBe 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.<\/p>\n
Create a primary key on a table:<\/p>\n
Alter Table TheTable Add Primary Key (field1, field2)<\/p>\n
To add an index on a field:<\/p>\n
alter table TableName Add Index (field1)
\nTo remove a primary key:<\/p>\n
alter table drop primary key
\nCreating a Crontab
\nWhat is Crontab?
\nCrontab 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.\u00a0 If you are adding a crontab for an individual user you must sign on as that user first.\u00a0 (ex. su userid)<\/p>\n
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:<\/p>\n
Format
\nminute hour day month weekday command
\nMinute – Minutes after the hour (0-59).
\nHour – 24-hour format (0-23).
\nDay – Day of the month (1-31).
\nMonth – Month of the year (1-12).
\nWeekday – Day of the week. (0-6; the 0 refers to Sunday).<\/p>\n
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.<\/p>\n
Options
\ncrontab -e
\nEdits the current crontab or creates a new one. *<\/p>\n
crontab -l
\nLists the contents of the crontab file.<\/p>\n
crontab -r
\nRemoves the crontab file.<\/p>\n
Creating a Crontab for use with AccessWatch
\nYou must first sign on as the individual user you are adding the crontab for.\u00a0 Do not create crontab while you are signed on as “su” under root or you will change the root crontab.\u00a0 After you sign on as root su as individual user.
\nsu userid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ex.\u00a0 su nercols<\/p>\n
Change editor to Joe from VI:<\/p>\n
EDITOR=joe
\nexport EDITOR<\/p>\n
Create the crontab from the command line:<\/p>\n
crontab -e<\/p>\n
Insert the following lines and the save the file (Ctrl-kx):<\/p>\n
30 23 * * * \/home\/userid\/www\/aw\/aw-parser.pl -d
\n45 23 * * * \/home\/userid\/www\/aw\/aw-report.pl<\/p>\n
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..<\/p>\n
Type man crontab for more detailed information.\u00a0 If you don’t want to set up a crontab just run the by hand when you want a report.<\/p>\n
.\/aw-parser.pl
\n.\/aw-report.pl<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/83"}],"collection":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=83"}],"version-history":[{"count":4,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions"}],"predecessor-version":[{"id":567,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions\/567"}],"wp:attachment":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}