mysql数据库增删改查_MySQL数据库之四大操作(增 删 改 查)

一.对数据库,表,记录---四大操作(增 删 改 查)

1.操作数据库

(1)对数据库(文件夹):进行增加操作

Create database 库名;

例: Create databasedb7;

查询库: show databases;

结果:

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

| Database |

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

| information_schema |

| db6 |

|db7|

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

(2)对数据库(文件夹):进行删除操作

Drop database 库名;

例: drop database db6;

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

| Database |

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

| information_schema |

| db7 |

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

(3)对数据库(文件夹):进行改操作

#更改数据库字符集

Alter database 库名charset编码集;

例 alter database db7charset gbk;

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

| Database | Create Database||

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

| db7 | CREATE DATABASE `db7` /*!40100 DEFAULTCHARACTER SET gbk*/ |

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

(4)对数据库(文件夹):进行查操作

查看建库信息: show create database 库名;

例: show create database db7;

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

| Database | Create Database||

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

| db7 | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET gbk */ |

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

2.操作表

(1)对表(文件):进行增加操作

create table 表名(字段名 数据类型,....);

例: create tablemsg(id int primary key auto_increment数据库查询操作,name char(10));

查询: Show tables;

结果:

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

| Tables_in_db7 |

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

| floatlist |

|msg|

| msg1 |

| str |

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

(2)对表(文件):进行删除操作

drop table 表名;

例: drop table str;

查询: Show tables;

结果:

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

| Tables_in_db7 |

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

| floatlist |

| msg |

|msg1|

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

(3)对表(文件):进行改操作

#只更改数据类型modify

alter table 表名modify字段名 数据类型;

原数据:

Desc msg1;

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

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

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

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

|name|char(10)| YES | | NULL | | |

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

例: alter table msg1 modify name varchar(10);

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

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

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

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

|name|varchar(10)| YES | | NULL | | |

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

#更改字段名和数据类型change

alter table 表名change字段名 新字段名 数据类型;

例: alter table msg1 change name NAME text;

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

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

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

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

|NAME|text| YES | | NULL | | |

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

#更改表名rename

alter table 表名rename新表名;

原表名:

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

| Tables_in_db7 |

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

| floatlist |

| msg |

|msg1|

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

例: alter table msg1 renamemsg2;

| Tables_in_db7 |

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

| floatlist |

| msg |

|msg2|

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

#增加字段add

alter table 表名add新字段名 数据类型;

原表字段:

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

| id | name |

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

| 1 | aa |

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

例: alter table msg2 addnewonechar;

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

| id | NAME |newone|

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

| 1 | aa | NULL |

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

# 删除字段drop

alter table 表名drop字段名;

例: alter table msg2 drop NAME;

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

| id | newone |

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

| 1 | NULL |

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

(4)对表(文件):进行查操作

#查看建表语句:

show create table 表名;

例: show create table msg2;

结果:

| msg2 | CREATE TABLE `msg2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`newone` char(1) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |

#查看表结构:

desc 表名;

例: desc msg2;

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

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

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

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

| newone | char(1) | YES || NULL| | |

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

3.操作记录

(1)对记录(文件内容):进行增加操作:

Insert into 表名(可指定字段,也可不指定但是值要与表字段对应) values(值1,值2值3...);

例:insertintomsgvalues(null,'dd',55,23,null);(这边用了auto_increment所以id自动加1)

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

| id | name | num | num2 | hobby |

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

| 1 | aa | 255 | 255 | NULL | |

| 2 | dd | 55 | 23 | NULL | |

| 3 | dd | 55 | 23 | NULL | |

| 4 | dd | 55 | 23 | NULL | |

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

(2)对记录(文件内容):进行删除操作:

#delete 清除数据但是保留id号.

delete from 表名; (此时若是在insert id会接续之前的id号往下排,也可以自己指定id)

原表数据:

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

| id | name | num | num2 | hobby| |

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

| 1 | aa | 255 | 255 | NULL | |

| 2 | dd | 55 | 23 | NULL | |

| 3 | dd | 55 | 23 | NULL | |

|4| dd | 55 | 23 | NULL | |

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

例:deletefrommsg;

查询:select *frommsg;

结果:Empty set (0.00 sec)

插入:insertintomsgvalues(null,'dd',55,23,null);

再次查询:select *frommsg;

结果:

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

| id | name | num | num2 | hobby | |

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

|5| dd | 55 | 23 | NULL | |

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

#truncate 清除数据不保留id号.

truncate table 表名; (此时若是在insert id会从头开始)

原数据:

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

| id | name | num | num2 | hobby | |

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

|5| dd | 55 | 23 | NULL | |

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

例: truncate table msg;

查询:select *frommsg;

结果:Empty set (0.00 sec)

插入:insertintomsgvalues(null,'dd',55,23,null);

再次查询:select *frommsg;

结果:

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

| id | name | num | num2 | hobby | |

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

|1| dd | 55 | 23 | NULL | |

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

(3)对记录(文件内容):进行改操作:

#update

update table 表名set字段=值where条件;(这边要加条件否则字段的值会全部改变)

或者update 库名.表名set字段=值where条件;

原表数据:

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

| id | name |

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

| 1 |aa|

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

例: update db7.msg set name='ll' where id=1;

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

| id | name |

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

| 1 |ll|

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

(3)对记录(文件内容):进行查操作:

select (指定某个字段)字段1,字段2 from表名;

例: select id,name from msg;

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

| id | name |

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

| 1 | ll |

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

Select * from 表名;或select * from库名.表名;

例: select * from db7.msg;

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

| id | name | num | num2 | hobby |

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

| 1 | ll | 255 | 255 | NULL | |

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

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!