[置顶] 数据库和数据表的操作

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

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

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

创建一个学校(school)数据库，指定字符集为utf8create database if not exists school charset utf8;创建库 school,使用字符集GBKcreate 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, birthdaycreate table teacher (    id int auto_increment,    name varchar(20),    age int,    phone varchar(15),    birthday date, primary key(id));---------------------------------------------创建teacher表 id自增，name, age, phone, birthdaycreate table teacher(    id int auto_increment,    name varchar(20),    age int, phone varchar(15),    birthday date, primary key(id) );---------------------------------------------创建表student id自增，name，age,classid,addresscreate 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的表结构创建表country2create 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    studentmodify    name varchar(30) default '无名';
学生表中添加新列homealter table    studentadd    home varchar(300);
学生表中添加新列gender,在age列后添加alter table    studentadd    gender enum('男','女') default '男'    after age;
学生表中的最前面插入新列idcardalter table student    add idcard char(20) first;
丢弃学生表中的idcard列alter table    studentdrop column    idcard;
彻底修改列(力度大于modify)alter table    studentchange    address currentAddress varchar(500) default '北京昌平';

对表的操作

重命名

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

将student表重命名为studentsalter table student rename to students;将teacher表重命名为teachersalter table teacher rename to teachers;

清空表

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

 

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

清空表city2truncate 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    teachersvalues (null,'田老师','女',40,'135234234234','1976-5-2 10:1:1','北京昌平32号');通过使用insert---into---set设置值insert into    teachersset    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 p2from'insert intostudents(name,age,gender,classid,currentAddress)values (?,?,?,?,''北京昌平'')';设置用户变量set @name = '小明', @age = 20, @gender = '男', @classid = 1;执行execute p2 using @name, @age, @gender, @classid;