mysql基本语句

1. database express
1.1 show database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevinb             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.03 sec)

1.2 create database

mysql> create database Jack;
Query OK, 1 row affected (0.02 sec)

1.3 connect user

mysql> use Jack;
Database changed

1.4 show table

mysql> show tables;
+------------------+
| Tables_in_kevinb |
+------------------+
| rs_dept          |
| rs_emp           |
| rs_office        |
+------------------+
3 rows in set (0.00 sec)

1.5 view table construct

mysql> describe rs_dept;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_id   | decimal(10,0) | NO   | PRI | NULL    |       |
| dept_name | varchar(50)   | YES  |     | NULL    |       |
| dept_code | varchar(10)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1.6 create table

1.7 insert data

mysql> insert into rs_dept (dept_id,dept_name,dept_code) values(4,'Mark','Hello Mark');
Query OK, 1 row affected (0.00 sec)

1.8 delete data
mysql> delete from rs_dept
    -> where dept_id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select *
    -> from rs_dept;
+---------+-----------+------------+
| dept_id | dept_name | dept_code  |
+---------+-----------+------------+
|       1 | Sal       | hello sal  |
|       2 | Tech      | hello Tech |
|       3 | Account   | hello Acc  |
+---------+-----------+------------+
3 rows in set (0.00 sec)

1.9 update data

mysql> select *
    -> from rs_dept;
+---------+-----------+------------+
| dept_id | dept_name | dept_code  |
+---------+-----------+------------+
|       1 | Sal       | hello sal  |
|       2 | Tech      | hello Tech |
|       3 | Account   | hello Acc  |
+---------+-----------+------------+
3 rows in set (0.00 sec)

mysql> update rs_dept
    -> set dept_code='Hello'
    -> where dept_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *
    -> from rs_dept;
+---------+-----------+------------+
| dept_id | dept_name | dept_code  |
+---------+-----------+------------+
|       1 | Sal       | Hello      |
|       2 | Tech      | hello Tech |
|       3 | Account   | hello Acc  |
+---------+-----------+------------+
3 rows in set (0.00 sec)

1.10 select data
mysql> select *
    -> from rs_dept;
+---------+-----------+------------+
| dept_id | dept_name | dept_code  |
+---------+-----------+------------+
|       1 | Sal       | hello sal  |
|       2 | Tech      | hello Tech |
|       3 | Account   | hello Acc  |
|       4 | Mark      | Hello Mark |
+---------+-----------+------------+
4 rows in set (0.00 sec)

1.11 Auto_increment
1.11.1 create table
mysql> create table dog (
    ->  dog_id mediumint not null auto_increment,
    ->  dog_name varchar(10),
    ->  primary key (dog_id));
Query OK, 0 rows affected (0.01 sec)

mysql> describe dog;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| dog_id   | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| dog_name | varchar(10)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

1.11.2 insert into date

mysql> insert into dog (dog_name)
    -> values ('dog1'),('dog2'),('dog3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *
    -> from dog;
+--------+----------+
| dog_id | dog_name |
+--------+----------+
|      1 | dog1     |
|      2 | dog2     |
|      3 | dog3     |
+--------+----------+
3 rows in set (0.00 sec)

2. System commond
2.1 get current date
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2008-08-23 13:37:29 |
+---------------------+
1 row in set (0.00 sec)

2.2 get database version

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.51a   |
+-----------+
1 row in set (0.00 sec)

2.3 data into table
2.3.1 build a txt file,content is :
1 Sal hello sal
2 Tech hello Tech
3 Account hello Acc

2.3.2 run commond;
load data local infile '/root/Desktop/rs_dept.txt' into table rs_dept;

Run:
mysql> load data local infile '/root/Desktop/rs_dept.txt' into table rs_dept;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select *
    -> from rs_dept;
+---------+-----------+------------+
| dept_id | dept_name | dept_code  |
+---------+-----------+------------+
|       1 | Sal       | hello sal  |
|       2 | Tech      | hello Tech |
|       3 | Account   | hello Acc  |
+---------+-----------+------------+
3 rows in set (0.00 sec)