一、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: 删除库、表、字段。