数据库和数据表的操作

Bertha 。 2022-07-17 04:49 258阅读 0赞

**本篇是对数据库和对数据表的操作方式进行总结。**

# **数据库的创建,丢弃和修改** #

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.87669543773121%"> 
    <div>
      语法描述： &nbsp; 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">create database if not exists 数据库名 [charset 指定字符集];&nbsp;</span>创建数据库，字符集是可选的，若是不指定默认为本地编码&nbsp; 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      &nbsp; 
     <span style="color:rgb(255,0,0)">drop database if exists 数据库名;</span>&nbsp;丢弃数据库 
     <br> &nbsp; 
     <br> &nbsp; 
     <span style="color:rgb(255,0,0)">alter database 库名 charset 新字符集;&nbsp;</span>只能修改数据库的字符集 
     <br> &nbsp; 
    </div> 
    <div>
      &nbsp;若想要修改数据库名，新建一个库，把数据导入，丢弃老库。 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

创建一个学校(school)数据库，指定字符集为utf8

create database if not exists school charset utf8;

创建库 school,使用字符集GBK

create database if not exists school charset gbk;

丢弃学校(school)数据库

drop database if exists school;

# **表的创建和相关操作** #

## **创建表** ##

**通过定义创建**

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      语法描述： 
    </div> 
    <div>
      create table 表名( 
    </div> 
    <div>
      &nbsp; 列名1 数据类型[长度] default 缺省值 auto_increment, 
    </div> 
    <div>
      &nbsp; 列名2 数据类型[长度] default 缺省值, 
    </div> 
    <div>
      &nbsp; &nbsp; ... 
    </div> 
    <div>
      &nbsp; primary key(列名) --- 定义主键 
    </div> 
    <div>
      ); 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

创建teacher表 id自增，name, age, phone, birthday

create table teacher (

id int auto\_increment,

name varchar(20),

age int,

phone varchar(15),

birthday date, primary key(id)

);

\---------------------------------------------

创建teacher表 id自增，name, age, phone, birthday

create table teacher(

id int auto\_increment,

name varchar(20),

age int, phone varchar(15),

birthday date, primary key(id) );

\---------------------------------------------

创建表student id自增，name，age,classid,address

create table student (

id int auto\_increment,

name varchar(20),

age int,

classid int,

address varchar(100),

primary key(id)

);

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      基于虚表创建实表(对某一表进行复制) 
    </div> 
    <div>
      语法描述： 
    </div> 
    <div>
      create table 表名 [as]select * from 表名(copy的表);表数据完全一致，表结构不一致，没有主键 
    </div> 
    <div>
      create table 表名 like 表名(copy的表);表结构完全一致，但是没有数据 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

复制country表中的数据到country1中

create table country1 select \* from country;

利用country的表结构创建表country2

create table country2 like country;

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      利用查询到的结果创建表 as 可以省略 
    </div> 
    <div>
      语法描述： 
    </div> 
    <div>
      create table 表名 [as]查询语句;&nbsp;---这里只要是一个查询就行。 
    </div> 
    <div>
      create table 表名(列1 数据类型1, 列2数据类型2...) [as] select 列1,列2,...from其他表[where] 
    </div> 
    <div>
      查询的值要和要创建表的列定义的数据类型兼容。 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

将编码为chn的数据复制到表chn中来

create table chn select \* from country where code = 'chn';

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> <h2>丢弃表</h2> 
    <div>
      语法描述： 
    </div> 
    <div>
      drop table if exists 表名1,表名2...; 丢弃某一表 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

小例子：

drop table chn;

将数据库对象用\`\`包起来，更灵活。

## **修改表：alter** ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.87669543773121%"> 
    <div>
      主要是对表中的列进行修改。 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      ---对某列的操作 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      语法描述： 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      修改某列 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      modify 修改的东西(数据类型,长度,缺省值,其他选项); 修改表中的某一列 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      或者利用change关键字 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      删除某列 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      drop [column] 列名; 删除指定列 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      添加某列 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      add 列名 数据类型[(长度)]; 给某一表中增加一列 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      add 列名 数据类型[(长度)] 
    </div> 
    <div>
      after 列名1;&nbsp; &nbsp; 给某一表中列名1后边增加一列 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      add 列名 数据类型[(长度)] first; 给某一表中增加一列，放在第一列 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

修改学生表中的name列长度，并设置默认值为'无名'

alter table

student

modify

name varchar(30) default '无名';

学生表中添加新列home

alter table

student

add

home varchar(300);

学生表中添加新列gender,在age列后添加

alter table

student

add

gender enum('男','女') default '男'

after age;

学生表中的最前面插入新列idcard

alter table student

add idcard char(20) first;

丢弃学生表中的idcard列

alter table

student

drop column

idcard;

彻底修改列(力度大于modify)

alter table

student

change

address currentAddress varchar(500) default '北京昌平';

## **对表的操作** ##

### 重命名 ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      语法描述： 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      &nbsp; &nbsp; rename to 新表名; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

将student表重命名为students

alter table student rename to students;

将teacher表重命名为teachers

alter table teacher rename to teachers;

### 清空表 ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      delete from 表名; 这个操作可以实现回滚 
    </div> 
    <div>
      实现回滚步骤： 
    </div> 
    <div>
      1.开启事务(begin;/start transaction;) 
    </div> 
    <div>
      2.delete操作 
    </div> 
    <div>
      3.提交事务(若是不想删了，rollback;回滚 / 若是执意删除 commit;提交) 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

清空表city2

truncate table city2;

begin;

delete from city2;

rollback; 回滚/删除失败

或者

commit; 提交/删除成功

###    给表中插入数据 ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.87669543773121%"> 
    <div>
      要了解表的结构 
    </div> 
    <div>
      desc 表名; 
    </div> 
    <div>
      语法结构： 
    </div> 
    <div>
      insert into 表名(列1,列2,列3...) 
    </div> 
    <div>
      values(对应值1,值2,值3...), 
    </div> 
    <div>
      &nbsp;(...), 
    </div> 
    <div>
      &nbsp;(...); 
    </div> 
    <div>
      values 一次可以插入多条数据。 
    </div> 
    <div>
      利用其他表快速的插入数据，查询出的值要和定义的列数据类型兼容 
    </div> 
    <div>
      &nbsp;insert into 表名(列1,列2,列3...) select 列1, 列2, 列3...from其他表[where]; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

insert into

teachers (id,name,gender,age,phone,birthday,home )

values (null,'杨老师','男',40,'135234234234','1976-5-2 10:1:1','北京通州32号');

insert into

teachers (id,name,gender,age,phone,birthday,home )

value (null,'张老师','女',40,'135234234234','1976-5-2 10:1:1','北京昌平38号');

insert into

teachers (id,name,gender,age,phone,birthday,home )

values (null,'徐老师','男',30,'135234234234','1986-5-2 10:1:1','北京朝32号'),

(null,'王老师','女',25,'124234234','1990-1-2','北京城')

在插入数据时某些列不给定值，将会插入null值或者默认值

利用values插入多条数据，插入时只需要按照表名后边的列插入数据即可。

insert into

teachers (phone,name,age)

values ('214234234234','郑老师',35),

('234234234','董老师',33);

在插入数据时不给定列, 插入时必须依据表当前的结构顺序, 虽然简单了, 不易于扩展

insert into

teachers

values (null,'田老师','女',40,'135234234234','1976-5-2 10:1:1','北京昌平32号');

通过使用insert---into---set设置值

insert into

teachers

set

name = '孔老师',

age = 30,

phone = '12423423';

### 表中数据的更新 ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      update 表名 
    </div> 
    <div>
      set 更新的语句 
    </div> 
    <div>
      where 设置条件(实现对表中数据有选择的更新) 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

update classes set id = 1 where id = 4;

# **利用事务对数据库进行操作：** #

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      将多步数据库操作，作为一个事务，这些操作具有原子性。 
    </div> 
    <div>
      事务的处理：上边的删除，回滚/提交就是一个简单的事务。 
    </div> 
    <div>
      DCL: 
    </div> 
    <div>
      开启事务 
    </div> 
    <div>
      set autocommit = false; 
    </div> 
    <div>
      start transaction; 
    </div> 
    <div>
      (begin); 
    </div> 
    <div> 
     <br> 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      结束事务 
    </div> 
    <div>
      commit(提交，事务执行成功) 
    </div> 
    <div>
      rollback(回滚，事务执行失败) 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      预编译 : 把一个SQL语句预先通过服务器编译成可执行对象, 
    </div> 
    <div>
      后面就可以多次直接方便地调用这个预编译,提高效率 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      语法描述： 
    </div> 
    <div>
      创建预编译 
    </div> 
    <div>
      prepare 
    </div> 
    <div>
      预编译名 
    </div> 
    <div>
      from 
    </div> 
    <div>
      '想要执行的SQL语句'; 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      执行预编译 
    </div> 
    <div>
      set @用户变量1 = 值1,@用户变量2 = 值2...; 
    </div> 
    <div>
      execute 预编译名 using @用户变量1, @用户变量2...; 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      释放预编译资源 
    </div> 
    <div>
      drop prepare 预编译名; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

预编译设置里的SQL语句，中的参数值可以用?表示，起到占位的作用，

在调用时必须通过用户变量完成赋值。

SQL中若是包含',必须在使用'对其转义。

prepare p2

from

'insert into

students

(name,age,gender,classid,currentAddress)

values (?,?,?,?,''北京昌平'')';

设置用户变量

set @name = '小明', @age = 20, @gender = '男', @classid = 1;

执行