mysql --user='bugs_ro' --password='termite' --database='bugs'
-OR-
mysql --user='bugs' --password
and enter the admin password when prompted.
Select the database: mysql> use bugs;
- show databases;
- use database_name;
- show tables;
- SCR Fields: explain bugs;
- explain products;
- explain components;
- SELECT current_user(), user();
- select bug_id, reporter from bugs where bug_status='NEW';
- select count(*) from bugs where bug_status='NEW';
NOTE: RLIKE CCB1 is like LIKE CCB% where % is a wildcard like *
- SELECT userid FROM profiles WHERE login_name RLIKE 'derrico';
SELECT userid,login_name,realname FROM profiles ORDER BY realname;
- GROUPS Show users in a group
SELECT groups.name,profiles.realname FROM groups,profiles,user_group_map
WHERE groups.name = 'CCB' AND groups.id = user_group_map.group_id AND profiles.userid = user_group_map.user_id;
- Find names in reverse order (First Last). Just fix 'users'.
SELECT realname FROM profiles WHERE realname NOT RLIKE ', ';
- SELECT bug_id, products.name As Product, components.name As Component, bug_status As Status, version,
profiles.realname as Reporter, priority, bug_severity As Severity
FROM bugs, products, components, profiles
WHERE reporter=profiles.userid
AND component_id=components.id
AND bugs.product_id=products.id
ORDER BY bug_status, bug_severity;
- SELECT bug_id, products.name, components.name, bug_status, version, profiles.realname, priority, bug_severity
FROM bugs, products, components, profiles
WHERE reporter=profiles.userid
AND component_id=components.id
AND bugs.product_id=products.id
AND (bug_id=1 OR bug_id=00002 OR bug_id=5);
- SELECT bug_id,filename,description
FROM attachments
WHERE bug_id=2;
- SELECT bug_id,products.name,components.name,bug_status,version,profiles.realname,priority,bug_severity
FROM bugs,products,components,profiles
WHERE reporter=profiles.userid
AND component_id=components.id
AND bugs.product_id=products.id
ORDER BY bug_id;
- Subqueries and Aliases
FIND ALL BUGS ASSIGNED TO derrico:
SELECT bug_id, T1.realname AS Reporter, T2.realname AS "Assigned to"
FROM bugs, profiles T1, profiles T2
WHERE reporter=T1.userid
AND assigned_to=T2.userid
AND assigned_to=(select userid from profiles where login_name RLIKE "derrico");
- NEW Submit Form - Extra Fields In Comment
Find bugs with CCB Version = 10.03
NOTE: I HAVE to use a custom field to check for CCB Version > 2.5!!!
but that doesn't apply to CCB Bucket (no greater-than functionality).
(This doesn't reduce correctly before checking the longdescs for CCB Version!)
SELECT longdescs.bug_id, cf_version_add_1, cf_version_fixed_1, profiles.realname
FROM bugs, longdescs, profiles
WHERE reporter = profiles.userid
AND who=reporter
AND bugs.bug_id = longdescs.bug_id
AND longdescs.thetext RLIKE 'CCB Version: 10.03'
ORDER BY bug_id;
- Field Change History ie. for bug 26
SELECT bug_id,attach_id,profiles.realname as "User",bug_when as "Modified Date",fielddefs.name as "Field",
removed as "Value Removed", added as "Value Added"
FROM bugs_activity,profiles,fielddefs
WHERE who=profiles.userid AND fielddefs.id=fieldid AND bug_id=26;
- work_time is by comment!!!
Show estimated_time and work_time in the same output.
(Note the SUM cmd needs the GROUP BY clause!)
SELECT bugs.bug_id,bug_status,components.name AS 'Components',estimated_time,remaining_time,SUM(work_time),T2.login_name AS 'Assigned to'
FROM bugs, profiles T2,products,components,longdescs
WHERE assigned_to=T2.userid
AND bugs.component_id=components.id
AND products.name='FIPER'
AND cf_ccb_version=''
AND target_milestone=''
AND bugs.bug_id=longdescs.bug_id GROUP BY bug_id
- Hours by bug like above
SELECT bugs.bug_id,bug_status,estimated_time,remaining_time,SUM(work_time)
FROM bugs, longdescs
WHERE bugs.bug_id=32315
AND bugs.bug_id=longdescs.bug_id GROUP BY bug_id;
- Search for Real Reporter, where changed due to invalid user. Also for assigned_to.
NOTE: the Summary (short_desc) contains "reporter: powell" and can be searched that way!
SELECT longdescs.bug_id,profiles.realname as "Reporter"
FROM bugs,longdescs,profiles
WHERE reporter=profiles.userid
AND bugs.bug_id=longdescs.bug_id
AND longdescs.thetext RLIKE 'ORIGINAL reporter: powell';
From the shell cmd prompt:
- Format: mysqlshow [options] database [table [column]]
- Version: mysqlshow --user=bugs --password=0rkinMan -V
- mysqlshow --user=bugs --password=0rkinMan bugs
- mysql --user='bugs' --password='0rkinMan' \
-e "SELECT bug_id, product_id, component_id, bug_status, version, priority FROM bugs \
WHERE bug_status='ASSIGNED'" bugs
- OR - Let it prompt you for the password:
mysql --user='bugs' --password \
-e "SELECT bug_id, product_id, component_id, bug_status, version, priority FROM bugs \
WHERE bug_status='ASSIGNED'" bugs
General Notes on MySQL
- Built in functions
- SELECT COUNT(*) FROM bugs;
- SELECT AVG(field) FROM grades;
- SELECT MIN(physics) FROM grades;
- more available (Math Functions)
examples:
SELECT MAX(accountBalance), MIN(accountBalance) FROM accounts;
SELECT COUNT(*) FROM accounts;
List Reporters (Submitters) and the count of the number of bugs they submitted.
SELECT profiles.realname, COUNT(profiles.realname) FROM bugs, profiles WHERE reporter=profiles.userid
GROUP BY profiles.realname;
SELECT profiles.realname as Name, COUNT(*) AS "Bug Count" FROM bugs, profiles
WHERE reporter=profiles.userid GROUP BY Name;
SELECT profiles.realname as Name, reporter, COUNT(*) FROM bugs, profiles WHERE reporter=profiles.userid GROUP BY Name
HAVING reporter=(select userid from profiles where login_name RLIKE "derrico");
SELECT profiles.realname as Name, COUNT(*) as "Bug Count" FROM bugs, profiles
WHERE reporter=profiles.userid AND reporter=(select userid from profiles where login_name RLIKE "derrico")
GROUP BY Name;
List the count of NEW and ASSIGNED (unresolved?) bugs reported by derrico:
SELECT profiles.realname as Name, COUNT(*) as "Bug Count" FROM bugs, profiles
WHERE reporter=profiles.userid
AND reporter=(select userid from profiles where login_name RLIKE "derrico")
AND (bug_status RLIKE 'NEW' OR bug_status RLIKE 'ASSIGNED')
GROUP BY Name;
SELECT SQL_CALC_FOUND_ROWS bug_id, profiles.realname FROM bugs, profiles
WHERE reporter = profiles.userid
AND reporter = (select userid from profiles where login_name RLIKE "derrico")
AND (bug_status RLIKE 'NEW' OR bug_status RLIKE 'ASSIGNED');
SELECT FOUND_ROWS() AS "Bug Count";
- Subqueries and IN: If the subquery returns multiple values.
ie. A bank branch (using branch ID: bid) uses several services, each with its sid:
SELECT sname FROM services WHERE sid IN (SELECT sid FROM branch_services WHERE bid = 1031);
- DISTINCT: SELECT DISTINCT cnam FROM branches... no duplicates.
- ORDER BY field [DESC] DESC (Descending) is reverse sort.
- BETWEEN: SELECT symbol, price FROM exchangeB WHERE price BETWEEN 40 and 50;
- LIMIT - SELECT * FROM bugs LIMIT 2,4; Start with row 2? (3rd) and show 4 rows
- Wildcards - % = zero or more occurrences of a character
_ = exactly one occurrence of a character
LIKE - Use the wild cards above with LIKE, ie. SELECT * FROM members WHERE fname LIKE '%man%';
THIS IS INEFFICIENT! Use full-text indices and a MATCH AGAINST command.
- Joining Tables - query two or more tables and display a combined result.
- When using a join, prefix each col name with the table to which it belongs, ie. members.fname
ie. SELECT member_id, video_id FROM status, members WHERE status.member_id = members.member_id;
- NOW() - When filling a timestamp field, you can use this value: NOW() SELECT NOW();
List of all accounts created more than three years ago:
SELECT acntName FROM accounts WHERE YEAR(DATE_ADD(accntCreatedOn, INTERVAL 3 YEAR)) <= YEAR(NOW());
- UPDATE (change) existing fields: UPDATE table SET field = value, SET field = value ... WHERE conditional
- DELAYED - client returns right away, cmd is queued.
- DELETE FROM table WHERE conditional - BE CAREFUL! This is irreversible! Get the WHERE right!
- TRUNCATE TABLE bugs; Clear the table and restart the autoincrement bug numbers.
- Modify Time Stamp: SELECT acntname FROM accounts WHERE YEAR(acntCreatedOn) = 2006;
- Aliasing (AS) and an alias after the table or field name you want to alias.
SELECT accountNumber AS num, accountName AS name FROM accounts;
SELECT accountName, accountCreatedAt FROM accounts AS a WHERE a.accountName LIKE '%Poo%';
- LIMIT: restrict the total number of records returned. LIMIT [row, beginning at 0],number of rows
SELECT accountName, accountNumber FROM accounts LIMIT 3,6;
Return records to end of table, use -1 as number of rows. Show row 19 to end of table:
SELECT * FROM tbl LIMIT 18,-1;
- GROUP BY: see "Built in functions" above.
- RAND: select one or more records at random from a table:
SELECT fld1, fld2 FROM tbl ORDER BY RAND() LIMIT 0,1;
select a random bug number:
SELECT ROUND((SELECT RAND() * (SELECT COUNT(*) FROM bugs)),0) AS "Random Bug Number";
- HAVING is used in the GROUP BY clause (see above) and operates on the records after they
have been retrieved from the table. So when using HAVING, the field must have been SELECTed
to test it to see if it fits the conditional in the HAVING clause.
- SPEED UP TRANSACTIONS! See SQL_CACHE, SQL_BUFFER_RESULT, SQL_BIG_RESULT, SQL_SMALL_RESULT,
SQL_HIGH_PRIORITY to significantly improve the speed of transactions.
- LOAD DATA INFILE inserts multiple records in one fell swoop.
DESCRIBE birthdays;
name
dob
Text file: data.txt
Alan P, 1967-09-11
Emma Y, 1963-01-23
LOAD DATA INFILE '/home/me/data.txt' INTO TABLE birthdays FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
If fewer fields in data file, specify a list of field names after the cmd:
LOAD DATA INFILE '/home/me/data.txt' INTO TABLE birthdays FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (name, dob);
Note the data file is assumed to be on the server. If it is on the client machine use:
LOAD DATA LOCAL INFILE...
- INTO OUTFILE: Get data from a db into a flat text file.
SELECT acntNumber, acntName, acntBalance FROM accounts
WHERE acntBalance > 5000
INTO OUTFILE '/tmp/high-value-accounts.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
use "INTO DUMPFILE" for binary fields, or the "mysqldump" utility program.
|