linux 笔记： mysql : basic commands

login mysql

1. login without password

mysql -h hostname -u root

or

mysql -u root

2. promp password

mysql -u root -p

3. input password in command line

mysql -u root -pMYPASSWORD

or

mysql -u root --password=MYPASSWORD

4. login to a database(like mysql)

mysql -u root -pXXXX mysql

***********************************

grant privileges to a user

GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES on *.* to david@'%' identified by 'david';

Here, % means anywhere

***********************************

--create table with special characters

create table `ab c` (id int not null);

***********************************

Create and use a new database named "bedrock":

mysql> CREATE DATABASE bedrock;   -- Comments follow a double dash

mysql> USE bedrock;

Create and populate table with data:

mysql> CREATE TABLE retired_employee (

                    Name char(20) DEFAULT '' NOT NULL,

                    Dept char(10) DEFAULT '' NOT NULL,

                    JobTitle char(20),

                    UNIQUE name_dept (Name,Dept)

                    );

mysql> CREATE UNIQUE index name_dept on employee (name,dept); -- avoids duplicate keys

mysql> INSERT INTO employee VALUES ("Jane Smith","Sales","Customer Rep");

mysql> INSERT INTO employee VALUES ('Jane Smith','Sales','Account Manager');

mysql> INSERT INTO employee VALUES ('Jane Smith','Engineerin','Manager');

mysql> UPDATE employee SET dept='HR' WHERE name='Jane Smith';

mysql> CREATE TABLE pet (

                 name VARCHAR(20), 

                 owner VARCHAR(20), 

                 species VARCHAR(20), 

                 sex CHAR(1), 

                 birth DATE, 

                 death DATE);

Add constraints to a table:

-- Use "auto_increment" integer column:

mysql> ALTER TABLE employee ADD EmpId INT NOT NULL AUTO_INCREMENT PRIMARY KEY; 

mysql> ALTER TABLE employee DROP INDEX name_dept;  -- get rid of index

mysql> 

Interrogate an existing database:

mysql> SHOW DATABASES;

mysql> USE bedrock;

mysql> SELECT DATABASE();  -- returns current database. eg. bedrock

mysql> SELECT VERSION();

mysql> SELECT NOW();

mysql> SELECT USER();

mysql> SHOW TABLES;

mysql> DESC employee;

mysql> SHOW CREATE TABLE employee;  -- show command used to generate table

mysql> SHOW INDEX FROM employee;

mysql> SELECT DISTINCT dept FROM  bedrock;

mysql> SELECT * FROM  bedrock WHERE Name  LIKE "B%y";  -- "%" match any char: Gives Betty and Barney

mysql> SELECT * FROM  bedrock WHERE Name LIKE "B___y";  -- "_" match space: Gives Betty but not Barney

mysql> SELECT * FROM  bedrock WHERE Name RLIKE "^Betty$";  -- "^" match beginning. "$" to denote end of string

mysql> SELECT COUNT(*) FROM employee;  -- Number of records returned

mysql> SELECT Name, COUNT(*) FROM employee WHERE Name LIKE "B%y";  -- Return Names and number of records returned

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

mysql> SELECT * FROM pet WHERE name LIKE "b%";

mysql> SELECT * FROM pet WHERE name REGEXP "^b";

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

mysql> SELECT MAX(article) AS article FROM shop;

mysql> SELECT * FROM employee WHERE name LIKE "%Sm%";

mysql> SELECT * FROM employee WHERE name REGEXP "^Ja";

Database cleanup:

mysql> DROP TABLE employee;

mysql> DROP DATABASE bedrock;

**********************************************

Loading CSV or tab delimeted files into MySQL:

"LOAD DATA LOCAL INFILE" vs "LOAD DATA INFILE": The term "LOCAL" pertains to whether the file is local to the MySQL client. Without the keyword "LOCAL", the datafile must reside on the same computer as the database server. The location of the client in this case would be irrelevant. The "LOAD DATA INFILE" has many file permission pitfalls and is thus trickey. In fact I have never been sucessful using this method with a user directory.

Load a tab delimited file into the database:

Command: LOAD DATA LOCAL INFILE 'file.dat' INTO TABLE employer;

*********************************************

--   limit的功能很强大.可是使用类似   limit   n,m的语法,含义是从n开始取,取m条记录. 

select   *   from   表   limit   n,m;

*********************************************