本篇是对数据库和对数据表的操作方式进行总结。
数据库的创建,丢弃和修改
语法描述:
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;