MySQL数据操作

新装MySQL修改密码

alter user root@localhost identified by 'n422tx%KVY87QthM';

查看用户密码

select user,host,authentication_string from mysql.user;

创建用户

create user root@localhost identified by 'root';

授权用户，先要创建用户再进行授权

create user root@localhost identified by 'root';

grant all on *.* to root@localhost;

更换授权地址

rename user 'root'@'localhost' TO 'root'@'127.0.0.1';

收回权限

revoke all on *.* from 'root'@'localhost'

更新密码

数据库以及表的创建

mysql> mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Query OK, 0 rows affected (0.43 sec)

mysql> show tables;

+--------------------+

| Tables_in_vlincent |

+--------------------+

| pet         |

+--------------------+

1 row in set (0.00 sec)

查看表的所有字段

mysql> describe pet;

+---------+-------------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name   | varchar(20) | YES  |   | NULL   |    |

| owner  | varchar(20) | YES  |   | NULL   |    |

| species | varchar(20) | YES  |   | NULL   |    |

| sex   | char(1)   | YES  |   | NULL   |    |

| birth  | date     | YES  |   | NULL   |    |

| death  | date     | YES  |   | NULL   |    |

+---------+-------------+------+-----+---------+-------+

表中插入数据

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Query OK, 1 row affected (0.08 sec)

查看表中的数据

mysql> select * from pet;

+----------+-------+---------+------+------------+-------+

| name   | owner | species | sex  | birth    | death |

+----------+-------+---------+------+------------+-------+

| Puffball | Diane | hamster | f   | 1999-03-30 | NULL  |

+----------+-------+---------+------+------------+-------+

1 row in set (0.00 sec)

导入txt的数据到表中

mysql> LOAD DATA LOCAL INFILE '/root/pet.txt' INTO TABLE pet;

Query OK, 8 rows affected (0.11 sec)

Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth    | death    |

+----------+--------+---------+------+------------+------------+

| Fluffy  | Harold | cat   | f   | 1993-02-04 | NULL    |

| Claws   | Gwen  | cat   | m   | 1994-03-17 | NULL    |

| Buffy   | Harold | dog   | f   | 1989-05-13 | NULL    |

| Fang   | Benny  | dog   | m   | 1990-08-27 | NULL    |

| Bowser  | Diane  | dog   | m   | 1979-08-31 | 1995-07-29 |

| Chirpy  | Gwen  | bird   | f   | 1998-09-11 | NULL    |

| Whistler | Gwen  | bird   | NULL | 1997-12-09 | NULL    |

| Slim   | Benny  | snake  | m   | 1996-04-29 | NULL    |

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

数据更新

mysql>  UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

Query OK, 1 row affected (0.08 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;

+----------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth    | death    |

+----------+--------+---------+------+------------+------------+

| Fluffy  | Harold | cat   | f   | 1993-02-04 | NULL    |

| Claws   | Gwen  | cat   | m   | 1994-03-17 | NULL    |

| Buffy   | Harold | dog   | f   | 1989-05-13 | NULL    |

| Fang   | Benny  | dog   | m   | 1990-08-27 | NULL    |

| Bowser  | Diane  | dog   | m   | 1989-08-31 | 1995-07-29 |

| Chirpy  | Gwen  | bird   | f   | 1998-09-11 | NULL    |

| Whistler | Gwen  | bird   | NULL | 1997-12-09 | NULL    |

| Slim   | Benny  | snake  | m   | 1996-04-29 | NULL    |

+----------+--------+---------+------+------------+------------+

8 rows in set (0.00 sec)

数据查询

mysql> SELECT * FROM pet WHERE name = 'Bowser';

+--------+-------+---------+------+------------+------------+

| name  | owner | species | sex  | birth    | death    |

+--------+-------+---------+------+------------+------------+

| Bowser | Diane | dog   | m   | 1989-08-31 | 1995-07-29 |

+--------+-------+---------+------+------------+------------+

1 row in set (0.00 sec)

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

  -> ;

+--------+-------+---------+------+------------+-------+

| name  | owner | species | sex  | birth    | death |

+--------+-------+---------+------+------------+-------+

| Chirpy | Gwen  | bird   | f   | 1998-09-11 | NULL  |

+--------+-------+---------+------+------------+-------+

1 row in set (0.00 sec)

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

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth    | death |

+-------+--------+---------+------+------------+-------+

| Buffy | Harold | dog   | f   | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

1 row in set (0.00 sec)

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

+----------+-------+---------+------+------------+-------+

| name   | owner | species | sex  | birth    | death |

+----------+-------+---------+------+------------+-------+

| Chirpy  | Gwen  | bird   | f   | 1998-09-11 | NULL  |

| Whistler | Gwen  | bird   | NULL | 1997-12-09 | NULL  |

| Slim   | Benny | snake  | m   | 1996-04-29 | NULL  |

+----------+-------+---------+------+------------+-------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')

  ->     OR (species = 'dog' AND sex = 'f');

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth    | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen  | cat   | m   | 1994-03-17 | NULL  |

| Buffy | Harold | dog   | f   | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

2 rows in set (0.00 sec)