Re: New Value From Concatenated Values?
Your solution was exactly what the doctor ordered. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreate Table With Sorted Data
The solution was CREATE TABLE copyname SELECT * FROM originalname I was having problem with a PHP command that's not pulling what I want from the table Ruling out random order for the rows was narrowing the focus on the PHP problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Questions About Indexes
INDEXES - A Science AND an Art I've been continuing to look for answers to my own questions. I've found a few ... Q1. What good does it do to store the primary key or a unique key if you're normally SELECTing columns that don't use that primary or unique key? As you can see, it only makes sense to index those fields you use in the WHERE clause. http://www.databasejournal.com/features/mysql/article.php/10897_1382791_2 Q2. Does a SELECT statement look at an index before it looks at a table? Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries: EXPLAIN. EXPLAIN shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed... http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1 Q3. Are JOINs where the real timesaving occurs and SELECTs just a peripheral issue muddying the water? In MySQL, Paul DuBois writes: Index columns that you search for, not columns you select ... [t]he best candidate columns for indexing are the columns that appear in your WHERE clause or columns named in join clauses. Q4. What about non-unique indexes? Is the structure of a non-unique index file similar to the index in the back of a book, the phrase you're searching for plus a list of row numbers (page numbers for a book) where that phrase is found? I haven't found the answer to this question, but I did find: Indexes work best for columns with unique values, and most poorly with columns that have many duplicate values Paul DuBois, MySQL Q5. Is an item in an index tied to a memory address (like a pointer in C ++) where the indexed data appears inside the larger memory area staked out by the table? ? Q6. As for memory, when you choose a database inside the mysql client, are all the tables within that database read into memory from the hard drive, or just the indexes? ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Questions About Indexes
I have a few questions about indexes. I understand (1) what an index is, and (2) why indexes are useful, but I don't have even a rough idea about HOW they work. The internet resources I've been able to find don't answer the questions I'm asking. I also tried cat /var/lib/mysql/srms07/staff.MYI to see if I could glean some information directly from an index file, but the MYI file wasn't human-readable. Q1. What good does it do to store the primary key or a unique key if you're normally SELECTing columns that don't use that primary or unique key? Q2. Does a SELECT statement look at an index before it looks at a table? Q3. Are JOINs where the real timesaving occurs and SELECTs just a peripheral issue muddying the water? Q4. What about non-unique indexes? Is the structure of a non-unique index file similar to the index in the back of a book, the phrase you're searching for plus a list of row numbers (page numbers for a book) where that phrase is found? Q5. Is an item in an index tied to a memory address (like a pointer in C++) where the indexed data appears inside the larger memory area staked out by the table? Q6. As for memory, when you choose a database inside the mysql client, are all the tables within that database read into memory from the hard drive, or just the indexes? Thanks in advance for taking the time to read this, and even more thanks if you take the time to respond to my questions with either an explanatory URL or your words explaining the matter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replace, Substitute, Delete
For years, I've been using FileMaker Pro to generate a staff photo gallery and staff phone directory from the same table of staff information. I'm switching to PHP/MySQL for the year ahead. In STEP 1 below, I concatenate a name for the teacher/staff person image and in STEP 3 I concatenate an XHTML table cell for the image and name. Steps 1 and 3 have been tested and work fine. I don't know how to accomplish STEP 2 however. Suppose I start with a last name like De Long or Van Schmidt? I wind up with de lonxx.jpg or van scxx.jpg for my image names. I have a superstitious dread of putting spaces in Linux/Unix web file names. Could someone suggest a way to replace the in imgName with ? STEP 1: Create the root of the image name update staff set imgName = Lower(CONCAT(Left(last,6),Left(first,2))); STEP 2: How do I delete spaces in the imgName? STEP 3: update staff set webLine = CONCAT(tdimg src='images/,imgName,.jpg' width='100' height='125'br clear='all' /,first, ,last,/td); Thanks in advance for your time spent in reading or responding. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
John Meyer wrote ... you may want to do a perl script to find and replace the spaces. Scott Haneda wrote ... I would move your html and string parse logic into php, If I'm doing data entry for individuals via a web page, Javascript is a third option. = Here's the reasons I was thinking MySQL. (1) I thought it might be quicker than Perl or PHP. Correct me if I'm wrong. (2) I have read a short description of Triggers, and I thought these three lines of code might be an excellent AFTER INSERT trigger. (I don't know enough about Triggers yet to know if they'll even take multiple lines of code however.) (A) Could this be a Trigger? and (B) Would it be worth doing? (3) I've written plenty of Perl and PHP code that concatenates fields and builds XHTML cells and rows. I thought it might be interesting to build the rows inside the database table and have my PHP do nothing but count MySQL records in order to know when to open and close the XHTML table rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
Instead of individual replacements, as in ...
SELECT REPLACE('De Long', ' ', '');
would this global approach work?
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName REGEXP ' ';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Databases are used to store data,
Databases are used to store data This line spoke the loudest to me. Over the years I had become very proficient with FileMaker Pro's built in scripting language. I had even gotten FileMaker to construct the web pages that would be used to connect to FileMaker (including writing the page's Javascript data validation). The line between database and processing language had become very blurred for me. I'll take your advice and keep the XHTML coding in PHP (but I will use Steps 1 and 2 to create the imgName that PHP will use). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--xml or --html output to file
When I try using the --xml or --html option with a batch file using INTO OUTFILE 'dirpath', the --xml or --html option seems to be ignored in favor of the tab-delimited default. (If I get rid of the INTO OUTFILE, xml or html displays fine in the terminal.) I tried using the pager to write to a file from inside MySQL. I succeeded, but it was table data. I couldn't figure out how to add the --xml or --html options from inside the pager. I tried the redirection operator from the command line, but I haven't stumbled on the correct syntax if such a syntax does exist. Does anyone know how to write an --xml or --html file from a SELECT statement, either from the command line or from a batch file? This would really be useful information. Thanks in advance for reading or responding. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleted Root Account
Fortunately, I'm learning MySQL on my home machine, so this MEGA-Mistake doesn't sink a Fortune 500 corporation. I was experimenting with a batch file that ... (1) Created a set of user accounts with tiered privileges, (2) Set passwords for the new accounts, and then (3) Displayed the grants for these new accounts on the command line. The last few lines of code took advantage of a new security tip I had just learned--change the name of the root account to something unguessable and then give the disguised root account a new password. I was running this file over and over again to debug it, dropping new users left and right, and ... oops ... not recognizing the off-the-wall new name I had given to the root account, I Dropped the root account. Duh. I deleted MySQL server and client 5.0, rebooted, then reinstalled server and client 5.0 with Synaptic. I still seem to be locked out, however. Are there some configuration files that are clinging to Linux that I need to manually delete in order to get a installation with a password-less root access? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted Root Account
The Synaptic checkbox menu has a Mark for Removal and Mark for Complete Removal. Complete Removal seems to be same as Purge; when I look at details, it says the configuration files are being removed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted Root Account
Even though I completely removed everything I could find related to MySQL in Synaptic Package Manager, a folder still remained at /var/lib/mysql/ containing all my old table information. If that info hung around, it seemes logical to assume the Grants table were still somewhere interfering with my attempts to login to mysql as root. However, when I followed instructions at http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html , I was able to kill mysqld and restart it with the skipping the grants table option. I was then able to to login as the the weird Debian/Ubuntu equivalent of root (a user using sudo) and access the mysql database. From there I was able to reinvigorate root. The thing that surprised me was that all my data survived the reinstallation process. I thought the databases and tables I had created would go up in smoke when I removed mysql_server. They're all intact. Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Less | More
Is there a mysql command line equivalent to | less or | more to make it easier to scan rows one screen at a time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Less | More
Thank you, gentlemen. From the little I had read about pager so far, I assumed it had something to do with outputting to a text file. First chance I get, I'll read up on pager in detail. On Thu, 2007-05-03 at 12:39 +, [EMAIL PROTECTED] wrote: at your friendly mysql command-line prompt enter a ?, and then look at the options ... hint pager. and/or search the documentation for set pager. - Rick Original Message Date: Thursday, May 03, 2007 08:33:57 AM -0400 From: John Kebbel [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Subject: Less | More Is there a mysql command line equivalent to | less or | more to make it easier to scan rows one screen at a time? -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
I went to the MySQL documentation pages and read up on using COLLATE. I knew SELECT was case-insensitive, but I was sort of surprised that using a character class didn't override that. Anyway, I next tried the status command to see if it gave me any characterset information. Client characterset:latin1 Server characterset:latin1 Once I thought I understood what was going on with COLLATE and case sensitivity, I tried this command... SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' COLLATE latin1_bin; It seemed to work fine. I searched the column to see if I could find any instances of all caps value, but did not find any. (They do exist; I created the data for this table from a Perl script solely to practice using character class regular expressions.) Then I tried this command. It should not have found any instances of all lower case passwords, but it did. SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' COLLATE latin1_bin; +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | Is there something obvious that I'm missing here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
I was experimenting with Character Classes because they were covered in MySQL Crash Course. There are probably substitutes for all the character classes--such as ^[a-z] for [:lower:]--that I probably should stick with instead of wandering off into foreign territory. Fooling with Character Classes did pay off, however, because I heard about COLLATE and declaring columns as binary from other responses. On Wed, 2007-05-02 at 08:08 -0400, [EMAIL PROTECTED] wrote: Wouldn't the regular expression be ^[a-z].* ^ = start of string [ a-z] = class range for lower case . = any character * = mods last to grab anything after that... actually you should just be able to get by with ^[a-z] John Kebbel [EMAIL PROTECTED] 05/02/2007 05:33 AM Please respond to [EMAIL PROTECTED] To MySQL mysql@lists.mysql.com cc Subject Re: REGEXP Character Classes I went to the MySQL documentation pages and read up on using COLLATE. I knew SELECT was case-insensitive, but I was sort of surprised that using a character class didn't override that. Anyway, I next tried the status command to see if it gave me any characterset information. Client characterset:latin1 Server characterset:latin1 Once I thought I understood what was going on with COLLATE and case sensitivity, I tried this command... SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' COLLATE latin1_bin; It seemed to work fine. I searched the column to see if I could find any instances of all caps value, but did not find any. (They do exist; I created the data for this table from a Perl script solely to practice using character class regular expressions.) Then I tried this command. It should not have found any instances of all lower case passwords, but it did. SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' COLLATE latin1_bin; +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | Is there something obvious that I'm missing here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
It looks like it was a string named NULL posing as a null value. I got 0 for ISNULL(suffix), which I assume means false. I tried this command ... update persons set suffix = 'Empty' where suffix = ''; It changed the NULLs to Empty. On Mon, 2007-04-30 at 18:12 -0700, Jeremy Cole wrote: Hi John, Are you sure they are actually NULL and not NULL (i.e. the string NULL)? Try this: SELECT first, last, ISNULL(suffix), LENGTH(suffix) FROM persons LIMIT 5; Regards, Jeremy John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEXP Character Classes
Linux Version: Linux version 2.6.15-28-386 MySQL Version: 5.0.22-Debian_0ubuntu6.06.3-log I have two queries using REGEXP character classes and their respective outputs below. The first is supposed to match an upper case character in a column, but I wind up with 4 rows out of 25 that contain only lower case characters. The second is supposed to match lower case characters but returns 11 rows out of 25 that contain only upper case characters. Am I using these character classes correctly? -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | +--+--+--++++ 25 rows in set (0.00 sec) -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |5 | VBOEUTTM | Human Resources | Purchasing | Clerical | c18528 | |9 | ENDPAXWW | Human Resources | Accounting | Clerical | 73d00f | | 14 | TEVXTOBK | Human Resources | Accounting | Sales Rep. | 6606a0 | | 15 | WREZUFAU | Customer Service | Receiving | Asst. | 14159 | | 17 | LGMMPJEY | Customer Service | Accounting | Asst. | 291512 | | 21 | DMCLWWDX | Customer Service | Receiving | Sales Rep. | 968745 | | 23 | BZZCQWWE | Customer Service | Payroll| Asst. | 11f2b7 | | 24 | EPGWQEXC | Customer Service | Payroll| Clerical | 706894 | | 31 | NYOOQVJI | Human Resources | Accounting | Sales Rep. | e7d0bc | | 33 | BUTSHOUS | Human Resources | Payroll| Asst. | 548082 | | 34 | VOSCTTGZ | Customer Service | Receiving | Sales Rep. | 858435 | +--+--+--++++ 25 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rename or Delete Users
I've discovered that I can type ... use mysql; show tables; describe user; update user set user='newName' where user='oldName' However, this seems to have no effect on the user name in the privileges table when I searched them. The MySQL site says the alternative way to do this has the same problem: RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events. Is there some kind of shortcut to a privilege swap, or am I better off just deleting users and recreating them with the new name. ___ Sorry to deluge this mailing list with so many requests, but I'm trying to build my knowledge base for a CMDEV and I spend hours painting myself into corners with MySQL every night and weekend day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rename or Delete Users
Thank you for stating something obvious that I was completely overlooking. With normal tables I had created, I would never expect related tables to be magically updated by something I did to one table. Because I was dealing with a table in the mysql database that was created by MySQL, I had assumed it had some special magical status, that behind the scenes routines would synchronize this table with others if I found the right key (command) to do so. They're just normal tables, aren't they? Anyway, it's DELETE and CREATE for me from now on. Thank you. On Tue, 2007-05-01 at 19:51 -0400, Michael Dykman wrote: I trust that you realize that you can never expect related tables to be uodated in the mysql database if you using using SQL to manipulate them directly. moving on.. I can't think of any mainstream systrm that generally allows usernames to be changed.. I mean, sure, there is a trivial hack to do it on not-so-secure unix systems but it's still far from common practice. I recommend that you take the easy road and just delete/create. On 5/1/07, John Kebbel [EMAIL PROTECTED] wrote: I've discovered that I can type ... use mysql; show tables; describe user; update user set user='newName' where user='oldName' However, this seems to have no effect on the user name in the privileges table when I searched them. The MySQL site says the alternative way to do this has the same problem: RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events. Is there some kind of shortcut to a privilege swap, or am I better off just deleting users and recreating them with the new name. ___ Sorry to deluge this mailing list with so many requests, but I'm trying to build my knowledge base for a CMDEV and I spend hours painting myself into corners with MySQL every night and weekend day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Research Subjects drawn randomly from databases
id is an integer ... describe persons; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | ID | int(11) | YES | | NULL| I got a 0 count ... SELECT COUNT(*) FROM persons WHERE ROUND(id) != id; +--+ | COUNT(*) | +--+ |0 | +--+ 1 row in set (0.06 sec) On Mon, 2007-04-30 at 10:45 -0400, Jerry Schwartz wrote: Is your ID field an integer? If not, you might be running into some rounding corner cases. I don't see why that would happen, off-hand, since integers can be stored exactly as binary floating point numbers, but who knows. To satisfy your curiosity, you could SELECT COUNT(*) FROM table WHERE ROUND(id) != id; If you get a non-zero count, then you know that there is a possibility of CEIL(RND()) not hitting an ID. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: John Kebbel [mailto:[EMAIL PROTECTED] Sent: Sunday, April 29, 2007 11:49 AM To: MySQL Subject: Re: Research Subjects drawn randomly from databases I rewrote my line using your suggestion ... select id,first,middle,last from persons order by rand() limit 10; and it worked perfectly. I'm still curious about why my original version gave such cockeyed results, but I'll focus on the successful solution and leave that unsolved problem for another day. Thank you for your solution Michael. On Sun, 2007-04-29 at 08:48 -0400, Michael Dykman wrote: If might suggest: SELECT * FROM BAR ORDER BY RAND() LIMIT 10 On 4/29/07, John Kebbel [EMAIL PROTECTED] wrote: For possible educational research purposes, I was playing around with a query that would randomly select people from a database. The database I experiment with has a group of fictitious persons with id numbers (primary key) ranging sequentially from 2 to 378. When I ran these queries below, I was expecting to select five random persons from the database. The query partially worked. I was getting random subjects, but everytime I ran the query, I got a different number of subjects, stretching from 0 and up (sometimes as many as 8 or 9). I could see the query generating fewer rows if I duplicated an id or made an off-by-one error, but I don't see how it could generate more than five. Does anyone see my error? (I've used two equivalent forms for the query below; both did the same thing) select id,first,middle,last from persons where id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1); select id,first,middle,last from persons where id in (ceil(rand()*377 +1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1)); +--+-++--+ | id | first | middle | last | +--+-++--+ | 35 | Viridiana | W | McCarthy | | 47 | Crystal | O | Cassady | | 67 | Ricardo | L | Johnson | | 183 | Christopher | E | Denver | | 237 | Christopher | B | Brenner | | 255 | Danielle| W | Nickels | | 299 | Christine | D | Dexter | | 300 | Rachel | J | Baker| | 339 | Jenna | O | Murray | +--+-++--+ 9 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IS NULL Question
I'm having problems understanding NULL. I grasp what a NULL value is, but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Research Subjects drawn randomly from databases
For possible educational research purposes, I was playing around with a query that would randomly select people from a database. The database I experiment with has a group of fictitious persons with id numbers (primary key) ranging sequentially from 2 to 378. When I ran these queries below, I was expecting to select five random persons from the database. The query partially worked. I was getting random subjects, but everytime I ran the query, I got a different number of subjects, stretching from 0 and up (sometimes as many as 8 or 9). I could see the query generating fewer rows if I duplicated an id or made an off-by-one error, but I don't see how it could generate more than five. Does anyone see my error? (I've used two equivalent forms for the query below; both did the same thing) select id,first,middle,last from persons where id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1); select id,first,middle,last from persons where id in (ceil(rand()*377 +1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1)); +--+-++--+ | id | first | middle | last | +--+-++--+ | 35 | Viridiana | W | McCarthy | | 47 | Crystal | O | Cassady | | 67 | Ricardo | L | Johnson | | 183 | Christopher | E | Denver | | 237 | Christopher | B | Brenner | | 255 | Danielle| W | Nickels | | 299 | Christine | D | Dexter | | 300 | Rachel | J | Baker| | 339 | Jenna | O | Murray | +--+-++--+ 9 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Giving Back...Well, Maybe
I had the same behavior when I substituted this line using char(10) for
the line that used '\n' . The query sent from the file still just prints
the '\n' instead of interpreting it.
select Concat('\n', Honorific, ' ' ,First, ' ', Middle, '. ', Last, ' ',
Suffix, ' (', spouse, ')', char(10), house, ' ', street, ' ',
streetType, char(10), city, ', ', state, ' ', zip, char(10)) as
supporter from cert.persons order by last,first limit 3;
On Sun, 2007-04-29 at 08:14 -0700, Gordon wrote:
Just one suggestion re the behavior of special characters between file
input and command line input. Try using char(10) {I think that is new
line} instead of \n. That should work in both scenerios.
-Original Message-
From: John Kebbel [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 28, 2007 7:19 AM
To: mysql@lists.mysql.com
Subject: Giving Back...Well, Maybe
I don't have enough MySQL knowledge to contribute much to this
mailing
list, but as a 23 year veteran teacher, I have some expertise when it
comes to developing methods of instruction (especially
self-instruction). While studying for the MySQl CMDEV exam, I created a
method for MySQL skill-building that may help others trying to master
MySQL systematically.
I had been building my expertise with queries by redirecting
commands
from a text file into MySQL. When I discovered MySQL comments through
this mailing list, I realized I could store my learning by putting an
active command I was testing at the top line of my text document, and my
previously tested commands below them inside a commented out area. This
morning I went a step further when I realized I could embed XHTML coding
inside MySQL comments and have a dual-purpose page, a page that (1) lets
me test queries by redirecting them to MySQL on my local computer, and
then (2) stores the tested queries inside commented-out areas in a
format I can post on the web.
The web page format allows me to review what I've done and lets
me
continue my research at home or work by downloading, testing and
expanding, then uploading my new research.
I hope someone finds the concept useful. What I've accomplished
so far
is at
http://scripting-solutions.com/certifications/mysql/dothis.html
Thanks to everyone who has been of help to me here now and in the
future.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Research Subjects drawn randomly from databases
I rewrote my line using your suggestion ... select id,first,middle,last from persons order by rand() limit 10; and it worked perfectly. I'm still curious about why my original version gave such cockeyed results, but I'll focus on the successful solution and leave that unsolved problem for another day. Thank you for your solution Michael. On Sun, 2007-04-29 at 08:48 -0400, Michael Dykman wrote: If might suggest: SELECT * FROM BAR ORDER BY RAND() LIMIT 10 On 4/29/07, John Kebbel [EMAIL PROTECTED] wrote: For possible educational research purposes, I was playing around with a query that would randomly select people from a database. The database I experiment with has a group of fictitious persons with id numbers (primary key) ranging sequentially from 2 to 378. When I ran these queries below, I was expecting to select five random persons from the database. The query partially worked. I was getting random subjects, but everytime I ran the query, I got a different number of subjects, stretching from 0 and up (sometimes as many as 8 or 9). I could see the query generating fewer rows if I duplicated an id or made an off-by-one error, but I don't see how it could generate more than five. Does anyone see my error? (I've used two equivalent forms for the query below; both did the same thing) select id,first,middle,last from persons where id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1); select id,first,middle,last from persons where id in (ceil(rand()*377 +1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1)); +--+-++--+ | id | first | middle | last | +--+-++--+ | 35 | Viridiana | W | McCarthy | | 47 | Crystal | O | Cassady | | 67 | Ricardo | L | Johnson | | 183 | Christopher | E | Denver | | 237 | Christopher | B | Brenner | | 255 | Danielle| W | Nickels | | 299 | Christine | D | Dexter | | 300 | Rachel | J | Baker| | 339 | Jenna | O | Murray | +--+-++--+ 9 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Giving Back...Well, Maybe
I don't have enough MySQL knowledge to contribute much to this mailing list, but as a 23 year veteran teacher, I have some expertise when it comes to developing methods of instruction (especially self-instruction). While studying for the MySQl CMDEV exam, I created a method for MySQL skill-building that may help others trying to master MySQL systematically. I had been building my expertise with queries by redirecting commands from a text file into MySQL. When I discovered MySQL comments through this mailing list, I realized I could store my learning by putting an active command I was testing at the top line of my text document, and my previously tested commands below them inside a commented out area. This morning I went a step further when I realized I could embed XHTML coding inside MySQL comments and have a dual-purpose page, a page that (1) lets me test queries by redirecting them to MySQL on my local computer, and then (2) stores the tested queries inside commented-out areas in a format I can post on the web. The web page format allows me to review what I've done and lets me continue my research at home or work by downloading, testing and expanding, then uploading my new research. I hope someone finds the concept useful. What I've accomplished so far is at http://scripting-solutions.com/certifications/mysql/dothis.html Thanks to everyone who has been of help to me here now and in the future. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Permissions and Into Outfile
Thank you. It's working now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Permissions and Into Outfile
I experimented with a local /var/www folder. I assumed setting 2, 6, or 7 for the Other value would give mysql write privileges, but mysql would not settle for anything less than a 7 in that last slot. What was really curious to me was that the User and Group settings were inconsequential. I even set the folder for 007 and mysql could write to the folder. But as soon as I tried making that Other setting anything less than 7, mysql generated an error message. I've been using Linux for years, but more as a hobby than profession (though I do maintain an internal Mac OS X web server at my school). I am unfamiliar with the syntax you were using with the username in front of :mysql. I didn't know of any way other than chmod nnn to change permissions for UGO (user,group,other). Could you post one or two sample command line statements that illustrate this syntax in action. I did a brief web search, but it only turned up the stuff I was familiar with. Thanks to everyone who helped with this issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
