MySQL 增删改查

一、mysql的增查改删

- 增加一条数据：insert into

insert into tb_name(column1, column2) values(v1, v2);

#如:
mysql> insert into student(name, age) values('lina', 17);

• 查找数据：select

SELECT column1,column1_name
FROM tb_name
[WHERE Clause]
[LIMIT N] [OFFSET M ]

#如:
mysql> select name, register_date
    -> from student
    -> where uid>0
    -> limit 2 offset 2;
+------+---------------+
| name | register_date |
+------+---------------+
| luna | 2017-11-24    |
+------+---------------+

查询语句中可使用一个或者多个表，表之间使用逗号(,)分割，WHERE语句可用来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
星号（*）可代替所有字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
offset 指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
limit 可设定返回的数据数。

• where 语句
  SELECT field1, field2,...fieldN FROM tb_name1, tb_name2...
  [WHERE condition1 [AND [OR]] condition2.....
  条件中可以跟各种：> < = !=等运算符号

• 修改一条数据：update
  UPDATE tb_name SET field1=v1, field2=v2
  [WHERE Clause]

mysql> update student set name='naer' where uid=1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where uid>0;
+-----+-------+---------------+
| uid | name  | register_date |
+-----+-------+---------------+
|   1 | naer  | 2017-05-17    |
|   2 | david | 2017-11-24    |
|   3 | luna  | 2017-11-24    |
+-----+-------+---------------+
3 rows in set (0.00 sec)

• 删除表中数据：delete
  delete from tb_name where clause;
  如：delete from student where name='luna';
• 模糊查询like语句
  select * from table where field1 like condition [AND [OR]] filed2 = 'somevalue';

mysql> select * from student where name like 'da%';

+-----+-------+---------------+
| uid | name  | register_date |
+-----+-------+---------------+
|   2 | david | 2017-11-24    |
|   4 | daxxx | 2017-05-11    |
+-----+-------+---------------+

二、alter 命令的 增加、修改、删除字段等

• 增加字段：add
  alter table tb_name add field column_type;

mysql> alter table student add gender enum('F','M') not null default 'M';
Query OK, 0 rows affected (1.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| uid           | int(11)       | NO   | PRI | NULL    | auto_increment |
| name          | char(32)      | NO   |     | NULL    |                |
| register_date | date          | YES  |     | NULL    |                |
| gender        | enum('F','M') | NO   |     | M       |                |
+---------------+---------------+------+-----+---------+----------------+

• 修改字段-数据类型：modify
  alter table tb_name modify column_name column_type;

mysql> alter table student modify gender char(8) not null;
Query OK, 4 rows affected (1.43 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
.......................................
| gender        | char(8)  | NO   |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.06 sec)

• 修改字段名称及类型：change
  alter table tb_name change old_name new_name column_type;

mysql> alter table student change gender sex enum('m','f') not null;
Query OK, 4 rows affected (1.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
.....................................
| sex           | enum('m','f') | NO   |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

• 删除字段：drop
  alter table tb_name drop column;

mysql> alter table student drop sex;
Query OK, 0 rows affected (1.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 修改表名：rename
  alter table old_tb_name rename new_name

小结：几个删除语句
delete : 只有删除数据使用delete

drop: 删除库、表、字段。