mysql用c 增删改查_MySQL 增删改查

最新推荐文章于 2024-08-31 00:16:08 发布

weixin_39542742

最新推荐文章于 2024-08-31 00:16:08 发布

阅读量267

点赞数

文章标签： mysql用c 增删改查

本文链接：https://blog.csdn.net/weixin_39542742/article/details/113032998

版权

一、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;

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

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

| uid | int(11) | NO | PRI | NULL | auto_increment |

| 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;

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

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

.......................................

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

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;

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

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

.....................................

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