数据库和数据表的操作

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

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

语法描述：   create database if not exists 数据库名 [charset 指定字符集]; 创建数据库，字符集是可选的，若是不指定默认为本地编码   drop database if exists 数据库名; 丢弃数据库    alter database 库名 charset 新字符集; 只能修改数据库的字符集    若想要修改数据库名，新建一个库，把数据导入，丢弃老库。

创建一个学校(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;

表的创建和相关操作

创建表

     通过定义创建

语法描述： create table 表名(   列名1 数据类型[长度] default 缺省值 auto_increment,   列名2 数据类型[长度] default 缺省值,     ...   primary key(列名) --- 定义主键 );

创建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)

);

基于虚表创建实表(对某一表进行复制) 语法描述： create table 表名 [as]select * from 表名(copy的表);表数据完全一致，表结构不一致，没有主键 create table 表名 like 表名(copy的表);表结构完全一致，但是没有数据

复制country表中的数据到country1中

create table country1 select * from country;

利用country的表结构创建表country2

create table country2 like country;

利用查询到的结果创建表 as 可以省略 语法描述： create table 表名 [as]查询语句; ---这里只要是一个查询就行。 create table 表名(列1 数据类型1, 列2数据类型2...) [as] select 列1,列2,...from其他表[where] 查询的值要和要创建表的列定义的数据类型兼容。

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

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

丢弃表 语法描述： drop table if exists 表名1,表名2...; 丢弃某一表

小例子：

drop table chn;

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

修改表：alter

主要是对表中的列进行修改。 ---对某列的操作 语法描述： 修改某列 alter table 表名 modify 修改的东西(数据类型,长度,缺省值,其他选项); 修改表中的某一列 或者利用change关键字 删除某列 alter table 表名 drop [column] 列名; 删除指定列 添加某列 alter table 表名 add 列名 数据类型[(长度)]; 给某一表中增加一列 alter table 表名 add 列名 数据类型[(长度)] after 列名1;    给某一表中列名1后边增加一列 add 列名 数据类型[(长度)] first; 给某一表中增加一列，放在第一列

修改学生表中的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 '北京昌平';

对表的操作

重命名

语法描述： alter table 表名     rename to 新表名;

将student表重命名为students

alter table student rename to students;

将teacher表重命名为teachers

alter table teacher rename to teachers;

清空表

truncate table 表名;该语句是一个DDL语句, 只要一执行就会提交事务. 效率高

 

delete from 表名; 这个操作可以实现回滚 实现回滚步骤： 1.开启事务(begin;/start transaction;) 2.delete操作 3.提交事务(若是不想删了，rollback;回滚 / 若是执意删除 commit;提交)

清空表city2

truncate table city2;

begin;

delete from city2;

rollback; 回滚/删除失败

或者

commit; 提交/删除成功

给表中插入数据

要了解表的结构 desc 表名; 语法结构： insert into 表名(列1,列2,列3...) values(对应值1,值2,值3...),  (...),  (...); values 一次可以插入多条数据。 利用其他表快速的插入数据，查询出的值要和定义的列数据类型兼容  insert into 表名(列1,列2,列3...) select 列1, 列2, 列3...from其他表[where];

 

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

表中数据的更新

update 表名 set 更新的语句 where 设置条件(实现对表中数据有选择的更新)

update classes set id = 1 where id = 4;

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

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

   

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

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

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

prepare p2

from

'insert into

students

(name,age,gender,classid,currentAddress)

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

设置用户变量

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

执行

execute p2 using @name, @age, @gender, @classid;