zzapper's MySQL Examples
These are random MYSQL commands listed here to hopefully give you ideas:-
SELECT t2.intID, t2.intKeywordID FROM tbl_User AS t1, tbl_UserTopic AS t2
WHERE t1.txtName like '%atif%' and t1.intID = t2.intID;
insert into tbl_UserTopic SELECT 3,t1.intID FROM tbl_User AS t1
WHERE t1.txtName like '%atif%';
update tbl_UserData set strFieldValue='some@emailaddress.com' where strFieldValue='other@emailaddress.com'
insert into `tbl_Pigeon_AdminTopic` values (33,106,74);
SELECT * FROM `tbl_Pigeon_AdminTopic` where intId=106 order by intKeywordID;
MySQL regular expressions
mysql>select first_name,last_name from staff where last_name regexp "^G";
To make a regexp case sensitive, use "regexp binary":
mysql>select first_name,last_name from staff where last_name regexp binary "^G";
The below will locate all rows with number "34" in their comma-separated list:
mysql>select id,photos from categories where photos regexp "[[:<:]]34[[>:]]";
MySQL supports the following regexp meta characters "normally":
^ and $ -- anchors for the begining and end of string.
*?+ -- quantifiers for zero or more, zero or one, one or more.
. -- any one character, including a newline.
[a-zA-Z] -- character classes, such as a-zA-Z.
[^0-9] -- exclude a character class, such as 0-9
ALTER TABLE activity_log ADD COLUMN loginEmail VARCHAR(40) FIRST;
alter table tbl_basket add column `form_email` varchar(100) default NULL,add column `form_card_country` varchar(100) default NULL;
ALTER TABLE activity_log MODIFY COLUMN time VARCHAR(20); (change type)
ALTER TABLE activity_log CHANGE ContactID what varchar(40); (rename column)
ALTER TABLE `activity_log` ADD `who` VARCHAR(40) AFTER `msg`
alter table tbl_products type=MYISAM;
SELECT * FROM activity_log ORDER BY loginEmail,time DESC;
SHOW KEYS FROM expenses;
SELECT COUNT(*) FROM `expenses`;
tee out.txt (same output to a file)
notee
Case Insensitivity
In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case-sensitivity of the underlying operating system determines the case-sensitivity of database and table names. This means database and table names are case-insensitive in Windows, and case-sensitive in most varieties of Unix
COLUMNS (fields) are ALWAYS CASE INSENSITIVE
GRANT ALL PRIVILEGES ON * TO root@localhost IDENTIFIED BY 'zzapper' WITH GRANT OPTION; # eg user root password zzapper
GRANT Select, Insert, Update, Delete, Index, Alter, Create, Drop, References ON *.* TO 'db_username'@'%' IDENTIFIED BY 'zzapper'
FLUSH PRIVILEGES
GRANT ALL PRIVILEGES ON somedb.* TO somedatabase@localhost IDENTIFIED BY 'f4xxxxx' WITH GRANT OPTION; # ie user somedatabase password f4xxxxx
mysql> GRANT ALL PRIVILEGES ON * TO zzapper IDENTIFIED BY 'zzapper' WITH GRANT OPTION; #create superuser
mysql.exe -h dbs5.someweb.com -u userid -ppassword DATABASENAME
MySql Comparisons
Equal: =
Not equal: <> !=
Less than or equal: <=
Less than: >
Greater than or equal: >=
Greater than: >
NULL-safe equal: <=>
IS NULL
IS NOT NULL
BETWEEN min AND max
select 1 BETWEEN 2 and 3
SELECT 2 IN (0,3,5,'wefwf')
First non-null in list: COALESCE(list)
insert into `ytbl_postcodes` Values ('','OX14 6RA','449200','196600');
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
select * from location as t1
where (t1.x_coord between 440000 and 450000) AND
(t1.y_coord between 190000 and 200000)
SELECT * FROM pet WHERE name REGEXP "^[bB]";
select * from ytbl_development where txtdevpriceto = '' and txtdevpricefrom regexp '[0-9][0-9]' # cannot use \d etc
SELECT intDEVID,txtDEVPOSTCODE INTO OUTFILE "c:/aaa/dump.sql" from ytbl_development; (file must not exist)
load data local infile 'C:/Documents and Settings/davidr/My Documents/nextspace/somedatabase/postcodeapr2004.csv' into table `somedatabase`.`ytbl_postcodes` fields terminated by '\t' (`Postcode`, `Town`, `Easting`, `Northing`, `Grid` )
SELECT * FROM tbl_address as t1 WHERE Left([t1].[PostCode],6) like "OX14 5%";
SELECT "pi" REGEXP "^(pi)*$"; -> 1
SELECT "pip" REGEXP "^(pi)*$"; -> 0
SELECT "pipi" REGEXP "^(pi)*$"; -> 1
select ukpgdesc INTO OUTFILE "/aaa/dump.sql" from property_main where ukpgcomp like '%somedb%'
# count counts number of entries, sums the entries
select COUNT(*) from property_main where ukpgcomp like '%barr%'
select sum(count) from ytbl_brochure_report;
select sum(count) from ytbl_brochure_report WHERE TO_DAYS(NOW()) - TO_DAYS(date) <= 1;
GRANT Select, Insert, Update, Delete, Index, Alter, Create, Drop, References ON *.* TO
'somedatabase'@'%' IDENTIFIED BY 'f4xxxxx';
GRANT ALL ON database.* TO user IDENTIFIED BY "password"
GRANT ALL ON somedatabase.* TO somedatabase@127.0.0.1 IDENTIFIED BY "f4xxxxx";
from localhost:GRANT ALL ON database.* TO user@localhost IDENTIFIED BY "password"
from remote host: GRANT ALL ON database.* TO user@example.com IDENTIFIED BY "password"
Don't forget to "flush privileges"
select * from tbl_Article where dtmNewsDate < '2003-03-01';
delete from tbl_Article where dtmNewsDate < '2003-03-01';
select t2.* from tbl_User as t1,tbl_UserData as t2
where (t1.dtmDateStamp < '2003-11-01') and (t1.intID = t2.UserID);
delete from tbl_UserData using tbl_User as t1,tbl_UserData as t2
where (t1.dtmDateStamp < '2003-11-01') and (t1.intID = t2.UserID);
select t2.* from tbl_User as t1,tbl_UserTopic as t2
where (t1.dtmDateStamp < '2003-11-01') and (t1.intID = t2.intID);
delete from tbl_UserTopic using tbl_User as t1,tbl_UserTopic as t2 where (t1.dtmDateStamp < '2003-11-01') and (t1.intID = t2.intID);
delete from tbl_User where dtmDateStamp < '2003-11-01 15:00:00';
select * from ytbl_user where (dtmDateStamp > '2003-11-18 15:00:00') and
(dtmDateStamp < '2003-11-20 15:00:00');
select * from ytbl_temp_user as t1, ytbl_user as t2
where (t1.txtCftoken = t2.txtemail) and
(t2.dtmDateStamp > '2003-11-18 15:00:00') and (t2.dtmDateStamp < '2003-11-20 15:00:00');
SELECT DISTINCT txtName from tbl_addresses;
SELECT txtName FROM MyTable GROUP BY txtName HAVING count(*) = 1;
Select * INTO OUTFILE "c:/aaa/lostusers.csv" from ytbl_user where (dtmDateStamp > '2003-11-18 15:00:00') and (dtmDateStamp < '2003-11-20 15:00:00');
update ytbl_Properties set txtdevcode=ltrim(txtdevcode);
update tbl_county_lookup set countyname=replace(countyname,'&','and');
UPDATE employee_data SET salary=220000, perks=55000 WHERE title='CEO';
# multiple insert using select
INSERT INTO tbl_UserTopic ( intID, intKeywordID )
SELECT intID,
tbl_Topic.intKeywordID
FROM tbl_Topic
WHERE tbl_Topic.intKeywordID In (ChkKeyWords)
% Matches any number of characters, even zero characters
_ Matches exactly one character
delete from ytbl_temp_user where (dtmDateStamp < subdate(CurDate(),Interval 60 day));
select * from ytbl_temp_user;
delete from ytbl_user where (txtEmail not like '%@%') and (dtmDateStamp < subdate(CurDate(),Interval 60 day));
select dtmDateStamp,txtEmail from ytbl_user;
# primary pair (user_name & role_name combination must be unique)
create table user_roles (
user_name varchar(15) not null,
role_name varchar(15) not null,
primary key (user_name, role_name)
);
DROP TABLE IF EXISTS ytbl_regions;
create table ytbl_regions
(
intID mediumint(9) unsigned NOT NULL auto_increment,
txtRegion varchar(60) default NULL,
PRIMARY KEY (intID)
) TYPE=MyISAM;
INSERT INTO ytbl_regions (txtRegion) VALUES('South East England');
INSERT INTO ytbl_regions (txtRegion) VALUES('CentraI North England');
INSERT INTO ytbl_regions (txtRegion) VALUES('CentraI Southern England');
INSERT INTO ytbl_regions (txtRegion) VALUES('Essex');
INSERT INTO ytbl_regions (txtRegion) VALUES('Midlands');
INSERT INTO ytbl_regions (txtRegion) VALUES('North East England');
INSERT INTO ytbl_regions (txtRegion) VALUES('North West England');
INSERT INTO ytbl_regions (txtRegion) VALUES('Northern lreland');
INSERT INTO ytbl_regions (txtRegion) VALUES('Scotland');
INSERT INTO ytbl_regions (txtRegion) VALUES('South West England');
INSERT INTO ytbl_regions (txtRegion) VALUES('Southern lreland');
INSERT INTO ytbl_regions (txtRegion) VALUES('Wales');
#eliminating duplicates
CREATE TABLE new_table SELECT DISTINCT * FROM old_table
To add an index:
ALTER TABLE new_table ADD UNIQUE (field1,field2,field3)
repair table tbl_User; (may need to restart mysql)
#check integrity of all tables
>mysqlcheck.exe -A
# check tables (step into /data/trollope)
../../bin/myisamchk *.MYI
my $sql2=qq|select * from ytbl_temp_user where (txtCftoken = '$email') and
(WEEK(dtmDateStamp,1) >= '$weekminus') and (YEAR(dtmDateStamp) = '2004')|;
select count(*) from `somedatabase`.`ytbl_basket` limit 0, 5000 # counts number of records
update ytbl_development set txtDev10PictImageDate =now(); #datetime
Literal Characters
Match a character exactly a A y 6 % @ Letters, digits and many special
characters match exactly
\$ \^ \+ \\ \? Precede other special characters
with a \ to cancel their regex special meaning
\n \t \r Literal new line, tab, return
\cJ \cG Control codes
\xa3 Hex codes for any character
Anchors and assertions ^ Field starts with
$ Field ends with
[[:<:]] Word starts with
[[:>:]] Word ends with
Character groups
any 1 character from the group [aAeEiou] any character listed from [ to ]
[^aAeEiou] any character except aAeEio or u
[a-fA-F0-9] any hex character (0 to 9 or a to f)
. any character at all
[[:space:]] any space character (space \n \r or \t)
[[:alnum:]] any alphanumeric character (letter or digit)
Counts
apply to previous element + 1 or more ("some")
* 0 or more ("perhaps some")
? 0 or 1 ("perhaps a")
{4} exactly 4
{4,} 4 or more
{4,8} between 4 and 8
Add a ? after any count to turn it sparse (match as few as possible) rather than have it default to greedy
Alternation | either, or
Grouping ( ) group for count and save to variable
select txtdev5HouseModel from ytbl_development where txtdev5HouseModel regexp '....'
I have two tables one contains quiz scores the other contains users. When a user completes a quiz they are entered in the scores table.
Firstly I need to select user id's from the scores table based on a particular quiz. e.g. SELECT user_id from scores where quiz_id = 13. I then need to search the user table to find every user who hasn't completed the quiz.
users
user userid
fred 12
joe 13
scores
userid quizid
12 13
12 14
13 11
SELECT A.USER_ID
FROM USERS A LEFT JOIN SCORES B
ON A.USER_ID = B.USER_ID AND B.QUIZ_ID = 13
WHERE B.USER_ID IS NULL
insert into tbl_user (txtEmail,txtSpare,blnOptIn) values ('zzz@zzz.com','zzz@zzz.com','1');
insert into tbl_usertopic (intID,intKeywordID) select tbl_user.intID,'99' from tbl_user where txtSpare='zzz@zzz.com';
explain select * from scores where score < 500;
$sql=qq|SELECT SUBDATE(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY)|;
select txtBrochureList from `somedatabase`.`ytbl_user` where (txtBrochureList is not NULL) and (dtmDateStamp > '2004-04-22');
MySql Problems
1) In a Where Clause always put '' just in case a null value
2) In a multi-table select a select * may require a tbl_fred.*
select txtsomedatabaseid,txtdevname,txtdevaddress,txtdevcity,txtdevregion,txtdevpostcode into outfile "/aaa/somedatabases3.csv" from ytbl_development order by txtdevregion;
CREATE TEMPORARY TABLE tmpShortCodes
SELECT short_code
FROM .... (<------- this is your query that creates your "short code"
list.)
SELECT t1.*
FROM ytbl_development t1
INNER JOIN tmpShortCodes sc
ON t1.txtDevPostCode LIKE concat(sc.short_code, '%')
alter table `nextspace`.`tbl_Pigeon_Admin` ,add column `blnoldServer` tinyint (4) DEFAULT '0' NULL after `blnOptIn`
Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:
a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().
alter table `otherdatabase_dev`.`tbl_basket_client` ,change `timestamp` `timestamp` timestamp NOT NULL ; # timestamp must be set to "Not Null"
# Storing temporary values to swap values of fields in a record
update ytbl_development set txtdevbedsavail=(@TEMP:=txtdevbedsavail), txtdevbedsavail=txtdevbeds, txtdevbeds=@TEMP;
Show table status (last access date, creation date etc)
update ytbl_development set txtdevpropertytype=lower(txtdevpropertytype);
update `oakleaf`.`ytbl_region_county_lookup` set county_var=lower(replace(county,' ','_'));;
# nested replace (in one query)
UPDATE table_name SET field_name1=replace(replace(field_name1, '>', ''), '<', '')
select region_id,county,MapRegionName from ytbl_region_county_lookup as t1
inner join ytbl_mapregions mp
on t1.region_id = mp.intID;
select t1.*,t2.DivisionName,t3.county from ytbl_division_county_lookup as t1
inner join ytbl_division as t2
on t1.division_id = t2.ID
inner join ytbl_region_county_lookup as t3
on t1.county_id = t3.intID
where t1.division_id = '14' order by t2.DivisionName;
# select records having a duplicate value in a field
select txtemail,count(txtemail) from `tbl_user` GROUP BY txtemail HAVING COUNT(txtemail)>1;
create table tbl_temp2 select txtemail,count(txtemail) from `tbl_user` GROUP BY txtemail HAVING COUNT(txtemail)>1;
#delete duplicate records using a temporary table
CREATE TEMPORARY TABLE
bad_temp2(id VARCHAR(10), name VARCHAR(20))
TYPE=HEAP;
INSERT INTO bad_temp2(name) SELECT DISTINCT name FROM bad_table2;
DELETE FROM bad_table2;
INSERT INTO bad_table2(id,name) SELECT id,name FROM bad_temp2;
GRANT ALL PRIVILEGES ON * TO zzapper IDENTIFIED BY 'zzapper' WITH GRANT OPTION;
# solve any password problem
skip-grant-tables in my.ini
update `SOMEDB`.`tbl_Article` set blnNewsShow='0' where dtmTimeStamp < '2004-01-01'; #disable old news items
updatl ztbl_TagDef set AdminValue="http://www.nextspace.co.uk/countyhall/optout.cfm" Where AdminID="optoutpage";
select t1.* from `CURRELL`.`tbl_Article` as t1
inner join tbl_ArticleTopic as t2
on t2.intNewsId=t1.intnewsid
where t2.intkeywordid='1';
select * from `lakleaf`.`ytbl_development` where TO_DAYS(NOW()) = TO_DAYS(dtmdevdate); # select all records from today
UPDATE oakleaf.ytbl_development SET txtdevname = CONCAT(LEFT(txtdevname, 3),
LPAD(SUBSTRING(txtdevname, 4), 4, '0'))
WHERE txtdevname REGEXP '^[A-Z]{3}[0-9]{1,3}$';
update tbl_websitepages set page_content=replace (page_content,'develop','bucket');
update tbl_websitepages set page_content=replace (page_content,'http://www.dosh.co.uk/website/cms/','/website/images/cms/');
update tbl_pages set page_content=replace (page_content,'subnav','contentlink');
# How to select all newsarticles attached to a topic
select t1.intnewsid,t1.txtnewstitle,t1.txtnewsprice from tbl_Article as t1
inner join tbl_ArticleTopic as t2 on t2.intNewsId=t1.intnewsid
where (t2.intkeywordid='1') and (t1.blnNewsdeleted=0) and (t1.blnNewsShow=1) order by txtnewsprice ;
select count(*) from tbl_user;
select t2.* from tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='fabrisj@mailcity.com') and (t1.intID = t2.intID);
delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='fabrisj@mailcity.com') and (t1.intID = t2.intID);
alter table `otherdatabase`.`tbl_products`,change `06_desc` `p06_desc` varchar (255) NULL,change `06_LCode` `p06_LCode` varchar (255) NULL;
# ------------------------------------------------------------------------------
select txtsomedatabaseid,count(txtsomedatabaseid) from ytbl_development GROUP BY txtsomedatabaseid HAVING COUNT(txtsomedatabaseid)>1; # select Oakleaf Duplicates
update tbl_products set p01_price=replace (p01_price,'£','');
Gotcha/mysql traps
You cannot name a database table column database
If You have unknown Column Error means you haven't quoted RHS!!!
Error: Duplicate entry '1' for key 1 (trying to reinsert a record with a duplicate PK, probably haven't set autoincrement on the PK)
# compare database structures
select md5(concat_ws('A','B',''))
truncate table `otherdatabase_dev`.`tbl_products`;
select concat(firstname,' ',familyname,' ',birth) from tbl_products;
# generate random data
update tbl_products set select_normal=round(rand());
# select the next record
SELECT id FROM tbl_products where (id > '$p_id') ORDER BY id ASC LIMIT 1
# delete duplicates, where records with duplicate a & b are trimmed
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
(t1.txtDevName NOT RLIKE '^test[[:digit:]]+')
alter table `otherdatabase_dev1`.`users_admin` ,change `user_type` `user_type` enum ('1','2','3') DEFAULT '1' NOT NULL;