GBwebNET Wiki | MySQL SQL

General SQL

use dbname

show tables/databases like <field_name>;

select * from <tablename> where <column name> = ‘condition’; delete from <tablename> where <column name> = ‘condition’; describe <tablename>; insert into <tablename> values(‘column1_value’,’column2_value’,’column3_value’); or insert into <tablename> (column_name1,column_name2) values (‘column1_value’,’column2_value’); update user set Password = PASSWORD(‘whatever’) where user = ‘gbrown’;- the “PASSWORD” part encrypts the value

update entries earlier than given time

update ''table_name'' set ''field_name'' = ''new_value'' where <column> < timestamp('2008-11-20 07:00:00');

update multiple records

update ''table_name'' set ''field_name2''=NULL where ''field_name1'' in (value1,value2,value3);

~/pp~

Specifics

How to show used and free DBspace

MariaDB (none)> SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”, sum( data_free )/ 1024 / 1024 “Free Space in MB” FROM information_schema.TABLES GROUP BY table_schema ;

Data Base Name	Data Base Size in MB	Free Space in MB
information_schema	0.12500000	0.00000000
mysql	0.60591602	0.00000000
zabbix	13182.14062500	6930.00000000

3 rows in set (0.58 sec)

Give a list of users to show database grants for

SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';
SHOW GRANTS FOR 'maillogs'@'%';
SHOW GRANTS FOR 'nick'@'%';
SHOW GRANTS FOR 'reporter'@'%';
SHOW GRANTS FOR 'timuser'@'%';
SHOW GRANTS FOR 'netezza'@'10.241.14.83';
SHOW GRANTS FOR 'netezza'@'10.241.14.84';
SHOW GRANTS FOR 'netezza'@'10.254.208.158';
SHOW GRANTS FOR 'replicate'@'10.44.21.18';

Then run the output show grants for all users and copy the data into a text file and grep it: gbrown@dbmaster ~$ grep maintdatabase mysqlusers

GRANT SELECT ON `mydatabase `.`request_impact_definitions` TO 'reporter'@'%'
GRANT SELECT ON `mydatabase `.`requests` TO 'reporter'@'%'
GRANT ALL ON mydatabase.* TO 'maint'@'localhost'
GRANT ALL ON mydatabase.* TO 'maint'@'localhost'
GRANT ALL ON mydatabase.* TO 'oss'@'localhost'
GRANT ALL ON mydatabase.* TO 'user'@'192.168.0.%' identified by 'mypassword'

Adding user

INSERT INTO user (name,password,details,permissions) VALUES ('username',AES_ENCRYPT('password','secureKey'),'Users Name',3);

Select User login

SELECT * FROM user WHERE name='username' AND password=AES_ENCRYPT('password','secureKey');

User Access

Decrypting Passwords

SELECT name,AES_DECRYPT(password, 'secureKey') AS unencrypted FROM user;

Updating Passwords

update user set Password = PASSWORD('whatever') where user = 'gbrown';- the “PASSWORD” part encrypts the value

update passwd set crypt=encrypt('drdgfrgdd') where uid=1003;

Grant Permissions

grant all privileges on db_name.* to 'username'@hostname identified by 'password';
flush privileges;

Drop user

DROP USER 'username'@hostname;

Loading files

Within HTML form tag set and using <input type=”file”> enctype should be set to “multipart/form-data”

alter table contact auto_increment = 0;

load data local infile 'Computer/Databases/CONTACT30.csv' into table contact FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY '\n' (name,company,position,phone,email);

load data local infile 'Computer/Databases/COMPANY30.csv' into table company FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY '\n' (name,address,postcode,website,phone,fax,employees,reg,notes);

Query based on contact (and finding duplicates):

select contact.name, company.name, contact.phone, contact.email,company.id,contact.company from company, contact where contact.name like 'hazel%' and contact.company  = company.id order by contact.name,company.name ;

select company.name,contact.name from company,contact where contact.company = company.id group by company.name,contact.name HAVING COUNT(*)&gt;1;

Delete duplicate across tables:

delete from company,contact using contact inner join company where company.id=contact.company and contact.name like 'hazel%';

delete from contact,company using contact inner join company on contact.company = company.id  where contact.name like 'hazel%' and contact.email is null;

Query company details:

select company.name,company.website,contact.name,contact.phone,contact.email from company,contact where company.name like 'spencer%' and contact.company = company.id group by company.name;

Query based on callback date:

select company.name,company.website,contact.name,contact.phone,contact.email,calls.callback,calls.company from company,contact,calls where calls.company = company.id and contact.company = company.id and calls.callback >  '2010-02-28 20:10:12';

select company.name,company.website,contact.name,contact.phone,contact.email,contract.end_date,contract.network,contract.subs from company,contact,contract where contract.company = company.id and contact.company = company.id and contract.end_date &gt;= now();

Query blank or null fields:

select from company,contact using contact inner join company where company.id=contact.company and contact.name like ‘hazel%’;

Query contract end date:

select company.name,contact.name,calls.callback,calls.notes from calls inner join company inner join contact where calls.company=company.id and contact.company=company.id and calls.callback <= now() group by company.id; select company.name,company.website,contact.name,contact.phone,contact.email,contract.end_date,contract.network,contract.subs from company,contact,contract where contract.company = company.id and contact.company = company.id and contract.end_date >= now();

update blank fields to NULL values:

update contact set email = default where email = ‘’;

How to do a case-insensitive search on a binary (BLOB) text field and send it to a text file (created in the mysql db directory):

select <fieldname> from <tablename> where upper(convert(<fieldname_to_query> using latin1)) like '%SEARCH_CRITERIA%' into outfile 'myfile.txt'\G;

Table joins

There are other examples of joins further up the page.

root@localhost [DB=zabbix]>select i.hostid,h.name,hs.itemid,hs.value  
  from history_str hs, items i, hosts h 
  where i.key_ like 'system.run[date +%Z]'
    and i.itemid = hs.itemid and h.hostid = i.hostid  and value not in('GMT','UTC') and hs.clock > unix_timestamp(DATE_SUB(NOW(), INTERVAL 1 day)) 
  group by hostid;
+--------+-------------------------------------+---------+-------+
| hostid | name                                | itemid  | value |
+--------+-------------------------------------+---------+-------+
|  10398 | host001.somehostdomain.co.uk        | 4632875 | BST   |
|  13822 | host002.somehostdomain.co.uk        | 4632741 | BST   |
|  15172 | host003.somehostdomain.co.uk        | 4632816 | BST   |
|  15962 | host004.somehostdomain.co.uk        | 4737972 | BST   |
|  15963 | host005.somehostdomain.co.uk        | 4633138 | BST   |
|  15964 | host006.somehostdomain.co.uk        | 4633139 | BST   |
|  17429 | host007.somehostdomain.co.uk        | 4632806 | BST   |
|  20878 | host008.somehostdomain.co.uk        | 4633158 | BST   |
|  20879 | host009.somehostdomain.co.uk        | 4633159 | BST   |
|  32703 | host010.somehostdomain.co.uk        | 5014006 | BST   |
|  34916 | host011.somehostdomain.co.uk        | 5626432 | BST   |
+--------+-------------------------------------+---------+-------+
11 rows in set (1.66 sec)
root@localhost [DB=zabbix]>

Insert a csv file into a table

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started. Reset the table auto increment fields:

ALTER TABLE company AUTO_INCREMENT = 0;

Load the file

load data local infile './COMPANY30.csv' into table company FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY '\n' (id,name,address,postcode,website,phone,fax,employees,reg,notes);

optionally enclosed removes any double quotes. Windows files may need a \r\n for line ending

Show global status statistics

show status [like <field_name>]

Show innodb status

Looks for locks and transactions

show innodb status;

Notes

Backticks - The backticks help you from accidentally using a name that is a reserved word in SQL for example. Take a table named “where”, it’s a stupid name for a table I agree, but if you wrap it in backticks it will work fine.

MySQL SQL

Table of contents