mysql数据库增删改查_MySQL数据库之四大操作(增 删 改 查)
发布时间：2023-02-06 13:48:23 所属栏目：MySql教程 来源：
导读： 一.对数据库,表,记录---四大操作(增 删 改 查)
1.操作数据库
(1)对数据库(文件夹):进行增加操作
Create database 库名;
例: Create databasedb7;
查询库: show databas
1.操作数据库
(1)对数据库(文件夹):进行增加操作
Create database 库名;
例: Create databasedb7;
查询库: show databas
|
一.对数据库,表,记录---四大操作(增 删 改 查) 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 | | +----+------+------+------+-------+-----------+ （编辑：百客网 - 百科网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐