OpenUnix - to BSD or not to BSD

MySQL

logging to the database using mysql root password
$ mysql -u root -p
list of frequent commands

- creating a new database (utf8)
CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

- choosing database to work with
mysql> use mail; Database changed

- adding a user with password into the e-mail database
mysql> insert into users (login, name, password, maildir) -> values ('info@domain.com', 'Robin Hood', ENCRYPT('arrow'), 'domain.com/boss/');

- changing users name
update users set name="Rob Roy" where login="info@domain.com";

- searching the database for a person using the person's login
select * from users where login="suspicious@domain.com";

- deleting a user based on his e-mail
delete from users where login="guisbern@domain.com";
- changing users e-mail for a person using the person's login
update users set password=ENCRYPT("sword") where login="robin@sher.wood";

MySQL SUM Function

- how many Norman soldiers killed this week mysql> SELECT SUM(killed_soldiers) FROM sherwood;

- You can take sum of various records set using GROUP BY clause. To sum up all the hits related to each robber:
mysql> SELECT name, SUM(killed_soldiers) FROM sherwood GROUP BY name;
MySQL COUNT Function

- count lines in the table
mysql>SELECT COUNT(*) FROM sherwood;

- count all records for brother Tuck
mysql>SELECT COUNT(*) FROM sherwood WHERE name="brtuck";
PRIVILEGES in MySQL

- granting priviledges to a new user (and creating him)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON mylittledb.* TO littlejohn@'localhost' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

- removing some (and keeping other) priviledges from an existing user
mysql> REVOKE SELECT, DELETE ON mylittledb.* FROM littlejohn@'localhost';

- updating privileges table
mysql> FLUSH PRIVILEGES;

- overviewing priviledges
mysql> SHOW GRANTS FOR littlejohn@'localhost';
creating a table, splitting it and joining tables
mysql> CREATE TABLE view; mysql> use view; mysql> CREATE TABLE user > ( id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, > login VARCHAR(30), name VARCHAR(40), num VARCHAR(7), > active tinyint(1) ); mysql> insert into user (login,name,num ,active) > values("brtuck","Brother Tuck","3255","1"); mysql> CREATE TABLE date > ( id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, datum DATE ); mysql> insert into date (datum) values ("1126-10-30"); mysql> CREATE TABLE main > ( user SMALLINT NOT NULL PRIMARY KEY, date MEDIUMINT(6), > val-A INT, in BIGINT, val-B INT, out BIGINT ); mysql> insert into main (user,date,val-A,in,val-B,out) > values("1","2","17","8281723","39","7373171891901723"); mysql> show tables; +-------------------+ | Tables_in_view | +-------------------+ | date | | main | | user | +-------------------+ 3 rows in set (0.00 sec) mysql> select * from date; +----+------------+ | id | datum | +----+------------+ | 2 | 1126-10-30 | +----+------------+ 1 row in set (0.00 sec) mysql> select * from main; +------+------+-------+---------+-------+------------------+ | user | date | val-A | in | val-B | out | +------+------+-------+---------+-------+------------------+ | 1 | 2 | 17 | 8281723 | 39 | 7373171891901723 | +------+------+-------+---------+-------+------------------+ 1 row in set (0.00 sec) mysql> select * from user; +----+----------+--------------+-------+---------+ | id | login | name | num | active | +----+----------+--------------+-------+---------+ | 1 | brtuck | Brother Tuck | 3255 | 1 | +----+----------+--------------+-------+---------+ 1 row in set (0.00 sec) mysql> select user.name, date.datum, main.in, main.out from user, date, main -> where main.user = user.id and main.date = date.id order by user.name; +--------------+------------+---------+------------------+ | name | datum | in | out | +--------------+------------+---------+------------------+ | Brother Tuck | 1126-10-30 | 8281723 | 7373171891901723 | +--------------+------------+---------+------------------+ 1 row in set (0.00 sec)

Import whole lotta data to mysql from a file with AWK.
$ awk ' BEGIN{q="'\''"} {print "INSERT INTO users (login,name,num,active) VALUES(" q $1 q "," q $2 q "," q $3 q "," q "1" q ");"}'< space_delitmited_data.csv |mysql --host=localhost --user=username --password=yourpassword tablename

Set auto increment counter to zero.
mysql> ALTER TABLE user AUTO_INCREMENT = 1;
Change name and type of column
(syntax: CHANGE original name, new name, new type)
mysql> ALTER TABLE tmp CHANGE user user varchar(30);

Getting MySQL data from bash.
$ cat myfile mysql --host=localhost --user=robin --password=marianne sherwood << EOF SELECT name, killed_soldiers FROM robbers ORDER BY login LIMIT 2; EOForecho "SELECT name, killed_soldiers FROM robbers ORDER BY login LIMIT 2;" | mysql --host=localhost --user=robin --password=marianne sherwood

update MySQL data using a subquery to another table
UPDATE main SET killed_normans = killed_normans + 300, silver_stolen = silver_stolen + 400 WHERE user=(SELECT id FROM user WHERE login='littlejohn') AND date=(SELECT id FROM date WHERE datum='1126-11-01');

insert MySQL data using a subquery to another table
insert into main (user, date, killed_normans, gold_stolen, silver_stolen, saved_vilagers) values ((SELECT id FROM user WHERE login='littlejohn'), (SELECT id FROM date WHERE datum='1126-11-01'), '11', '22', '10', '150');


            


            alter table main add column id int(18) AUTO_INCREMENT PRIMARY KEY;
            


            alter table main change column user user smallint(6) NOT NULL, DROP PRIMARY KEY;

Export:
mysqldump -u root -p --all-databases > alldb.sql mysqldump -u root -p --opt --all-databases > alldb.sql mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql mysqldump -u root -p --skip-set-charset --default-character-set=binary sherwood>shwd.sql

Install a secondary key (constraint) to link two tables:

mysql> alter table user add constraint FK_user2comp foreign key (comp_id) references comp(id);

Remove the secondary key:

mysql> alter table user drop foreign key FK_user2comp;

Check the secondary key:

mysql> SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE   REFERENCED_TABLE_SCHEMA = 'tasklist' AND REFERENCED_TABLE_NAME = 'comp';
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| task       | comp_id     | FK_task2comp    | comp                  | id                     |
| task       | comp_id     | FK_taskcomp     | comp                  | id                     |
| user       | comp_id     | FK_user2comp    | comp                  | id                     |
| user       | firma_id    | FK_user2firma   | comp                  | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+
4 rows in set (0.05 sec)

Openunix.eu

MySQL