首页 > 代码库 > 数据库的基本操作

数据库的基本操作

一、创建数据库:

语法:create {database|schema}[if not exists] db_name [default] character set[=] charset_name;

{}:表示必选项  []:表示可选  |:表示选择

如1:create database demo;

执行情况:当名为demo的数据库不存在时,执行后,创建成功,没有错误。

当名为demo的数据库已经存在时,执行后,不会重新创建,且报错。

如2:create database if not exists demo;

执行情况:当名为demo的数据库不存在时,执行后,创建成功,没有错误。

当名为demo的数据库已经存在时,执行后,不会重新创建,没有错误。

 

二、查看当前服务器下的数据库列表:

语法:show {databases|schemas} [like ‘pattern’| where expr];

如:show databases;  (注意复数形式S

 

三、修改数据库编码方式:

语法:alter {database|schema} [db_name] [default] character set [=] charset_name

 

四、删除数据库

语法:drop {database|schema} [if exists] db_name

drop database if exists demo;

 

五、创建数据库

第一步:打开数据库

语法:use db_name;     (PS:查询当前打开的数据库:select database();)

第二步:查看数据库中存在的数据表:

语法:show tables [from db_name] [like ‘pattern’|where expr];  (注意复数形式S

第三步:创建数据库表:

语法:create table [if not exists] table_name(

         Column_name data_type,

         ….

)

例如:建立一个工资表,表的内容包含员工姓名、年龄、工资

create table tb1(

username varchar(20),

age tinyint unsigned,

salary float(8,2) unsigned

);   (PS:最后一行没有逗号

 

六、查看数据表结构:

语法:show columns from table_name;

例如:技术分享

 

七、创建数据库表细节引申(约束)

约束的作用:1. 保证数据的完整性和一致性  2. 分为表级约束和列级约束

约束的类型包括:Not null(非空约束)、Primary key(主键约束)、Unique key(唯一约束)、Default(默认约束)、Foreign key(外键约束)

 

a、设置数据表的时候设定字段属性:空值与非空(null 以及not null)

例如:create table tb2(

username varchar(20) not null,//用户名不可为空

age tinyint unsigned null//年龄可为空

);

b、自动编号属性(auto_increment):在众多记录当着保证某条记录的唯一性

特殊点:1、必须与主键组合使用 2、默认情况下,起始值为1,每次的增量为1

例如:create table tb3(

id smallint unsigned auto_increment,

username varchar(20) not null,

age tinyint unsigned null

);

执行后,报错。自动编号的字段未被定义为主键。

c、主键约束:primary key     特点:每张表只能存在一个主键,主键保证记录的唯一性且主键自动为not null

例如:

create table tb3(

id smallint unsigned auto_increment primary key,

username varchar(20) not null,

age tinyint unsigned null

);

d、唯一约束:unique key  特点:保证记录的唯一性、字段可谓空值且每张表可以存在多个唯一约束

primary key和unique key都能保证数据的唯一性,区别在于primary key一张表里只能存在一个,unique key一张表可以存在多个。

e、默认约束:default    特点:当插入记录时,没有明确为字段赋值,则自动赋予默认值。

例如:create table tb7(

id smallint unsigned auto_increment primary key,

username varchar(20) not null unique key,

sex enum(‘1‘,‘2‘,‘3‘) default ‘3‘

)

sex字段:1:男,2:女,3:保密,默认值为保密。

f、外键约束Foreign key    作用:1.保持数据的一致性,完整性。  2.实现一对一或一对多的关系

外键约束的要求:

  1. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
  2. 数据表的存储引擎只能为InnoDB
  3. 外键列和参数列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话,mysql将自动创建索引。

(PS:编辑数据表的默认存储引擎:Mysql配置文件my.ini文件内:default-storage-engine=INNODB)

 查看此表是否使用了刚设置的默认存储引擎,语法为:show create table tb_name;

技术分享

示例:

创建数据表省份表:

create table province(

id smallint unsigned auto_increment primary key,

pname varchar(20) not null

);

创建另一个表:

create table users(

id smallint unsigned auto_increment primary key,

username varchar(20) not null,

pid bigint,

foreign key (pid)  references province(id)

);

运行后报错,错误的原因是子表中的pid字段数据类型与父表中id的数据类型不一样。

修改后运行:

create table users(

id smallint unsigned auto_increment primary key,

username varchar(20) not null,

pid smallint,

foreign key (pid)  references province(id)

);

运行后报错,错误的原因是子表中的pid字段数据类型与父表中id的数据类型还是不一样。

再修改为:

create table users(

id smallint unsigned auto_increment primary key,

username varchar(20) not null,

pid smallint unsigned,

foreign key (pid)  references province(id)

);

效果:Users为子表,province为父表。Pid为外键列,id参照列

查询以上记录是否创建索引:

ID为主键,主键会自动添加索引。

show index from province;

技术分享

技术分享

外键约束的参照操作:

  1. cascade:从父表删除或更新且自动删除或更新子表中匹配的行
  2. set null:从父表删除或更新行,并设置子表中的外键列为null。如果使用该选项,必须保证子表列没有指定的not null.
  3. restrict:拒绝对父表的删除或更新操作。
  4. No action:标准SQL的关键字,在mysql中与restrict相同。

 

例如:

create table users1(

id smallint unsigned auto_increment primary key,

username varchar(20) not null,

pid smallint unsigned,

foreign key (pid)  references province(id) on delete cascade

);

插入记录:

insert province(pname) values (‘a‘);

insert province(pname) values (‘b‘);

insert province(pname) values (‘c‘);

 

insert users1(username,pid) values (‘tom‘,3);

insert users1(username,pid) values (‘john‘,3);

insert users1(username,pid) values (‘rose‘,1);

执行删除操作:delete from province where id=3;

允许后可以发现province标准c记录被删除,users1表中,pid为3的tom和John都被删除

八、增删改查:

a、增

添加单列:

语法:alter table tal_name add [column] col_name column_definition [first|after col_name]

First:新增列为第一列,省略则为最后一列

添加多列:

语法:alter table tal_name add [column] (col_name column_definition,…)

插入记录:(有三种方法)

方法一语法:insert [into] table_name[(col_name,…)]values(val,…)

例如:

create table users(

id smallint unsigned primary key auto_increment,

username varchar(20) not null,

password varchar(32) not null,

age tinyint unsigned not null default 10,

sex BOOL

);

分析:字段id是自动编号的,如果所有字段都要赋值的话,有两种方法:

第一种方法:insert users values (null,‘tom‘,‘123‘,25,1);

第二种方法:insert users values (default,‘jack‘,‘456‘,25,1);

查询select * from users;结果如下:

技术分享

ps:对有默认值的字段进行复制的方法:

示例: 查看表:show columns from users;

 技术分享

字段age有默认值10

插入一条记录,给age字段赋予值10,有以下两种方式:

insert users values (default,‘kiki‘,‘555‘,10,1);

insert users values (default,‘lucy‘,‘234‘,default,1);

查询结果:select * from users;

 技术分享

方法二语法:Insert [into] tbl_name set col_name={expr|default},…

说明:与第一种方式的区别在于,此方法可以使用子查询。

insert users set username=‘ben‘,password=‘123‘;

查询结果:select * from users;

 技术分享

插入数据的第三种方法:

Insert [into] tbl_name [(col_name,…)]select …

说明:此方法可以将查询结果插入到指定数据表。

例如:建新表:

create table test(

id tinyint unsigned primary key auto_increment,

username varchar(20)

);

执行操作,将users表中年龄大于30的记录写入到test表

执行:insert test select username from users where age>=30;

执行后报错:技术分享

insert test(username) select username from users where age>=30;

执行后:select * from test;

技术分享

b、删

删除列:alter table tal_name drop [column] col_name;

删除多列:alter table tal_name drop [column] col_name,drop [column] col_name1,…

删除主键约束:alter table tbl_name drop primary key

删除唯一约束:alter table tbl_name drop {index|key} index_name

删除外键约束:alter table tbl_name drop foreign key fk_symbol

使用语句:show create table users3;查看表结构

CREATE TABLE `users3` (

  `username` varchar(10) NOT NULL,

  `pid` smallint(5) unsigned DEFAULT NULL,

  `id` smallint(5) unsigned NOT NULL DEFAULT ‘0‘,

  `age` tinyint(3) unsigned NOT NULL DEFAULT ‘15‘,

  KEY `pid` (`pid`),

  CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除约束:alter table users3 drop foreign key users3_ibfk_1;

执行后再查看表结构::show create table users3;

CREATE TABLE `users3` (

  `username` varchar(10) NOT NULL,

  `pid` smallint(5) unsigned DEFAULT NULL,

  `id` smallint(5) unsigned NOT NULL DEFAULT ‘0‘,

  `age` tinyint(3) unsigned NOT NULL DEFAULT ‘15‘,

  KEY `pid` (`pid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

发现还是存在索引的。如果不想要, 可以执行:alter table users3 drop index pid;

再次查看结构::show create table users3;

CREATE TABLE `users3` (

  `username` varchar(10) NOT NULL,

  `pid` smallint(5) unsigned DEFAULT NULL,

  `id` smallint(5) unsigned NOT NULL DEFAULT ‘0‘,

  `age` tinyint(3) unsigned NOT NULL DEFAULT ‘15‘

) ENGINE=InnoDB DEFAULT CHARSET=utf8

单表删除:delete from tbl_name [where_condition]

例如:delete from users where id = 5;

 

c、改

修改列定义:

Alter table tbl_name modify [column] col_name column_definition [first|after col_name]

比如:以下表,id字段并没有摆在第一个(虽然位置没有关系)

 

修改下:Alter table users3 modify id smallint unsigned first;

查询表结构:show columns from users3;

 

 

修改列名称:alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]

 

执行后:alter table users3 change pid p_id tinyint unsigned not null;

查看表结构:show columns from users3;

 

更改数据表的名字:

方法1:alter table tbl_name rename[to|as] new_tbl_name

方法2:rename table tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]…

单表更新update

Update [low_priority][ignore]table_reference set col_name1={expr1|default}{, col_name2={expr2|default}}…[where where_condition]

更新记录:update users set age = age + 5 where 1;

查看记录:select * from users;

 技术分享

添加约束:

添加主键约束:

语法:alter table tbl_name add [constraint [symbol]] primary key [index_type](index_col_name,…)

例如:建表如下:

create table users3(

username varchar(10) not null,

pid smallint unsigned

);

查看表的结构:show create table users3;

为表添加一列:alter table users3 add id smallint unsigned;

将新添加的列设置为主键:alter table users3 add primary key(id);

alter table users3 add constraint pk_users3_id primary key(id);

添加唯一约束:(主键约束只有一个,唯一约束可以有多个)

Alter table tbl_name add [constraint [symbol]] unique [index|key][index_name][index_type](index_col_name,…)

例如:alter table users3 add unique (username);

添加外键约束:alter table tbl_name add [constraint[symbol]]foreign key [index_name](index_col_name,…)reference_definition

添加或删除默认约束:alter table tbl_name alter [column]col_name {set default literal|drop default}

添加是:set default literal

删除是: drop default

例如:在表中添加列:alter table users3 add age tinyint unsigned not null;

添加约束,为字段age添加默认值15

Alter table users3 alter age set default 15;

 

d、查

查找表记录:

语法:select expr,… from table_name   (PS: expr表示表达式)

例如:select * frome tb1;(查找出所有记录)

查询表达式解析:

Select select_expr [,select_expr…]

{

    From table_references

    [where where_condition]

    [group by {col_name|position}[asc|desc],…]

    [having where_condition]

    [ORDER BY {col_name|expr|positon}[asc|desc],…]

    [limit{[offset,]row_count|row_count offset offset}]

}

查询表达式:每一个表达式表示想要的一列,必须有至少一个

      多个列之间以英文逗号分隔

      星号(*)表示所有列。Tbl_name.*可以表示命名表的所有列。

      查询表达式可以使用[as]alias_name为其赋予别名。

      别名可用于group by,order by或having子句。

例如:select username,id from users

select users.id,users.username from users(与上面查询方式不同的是,指定了哪张表的id及username,在多表查询中用得到)

赋予别名(在PHP中用得到):例如select id,username from users;

技术分享

使用:select id AS UID,username AS uname from users;

结果:

其中AS是可以省略的,例如:select id UID,username uname from users;

但是不建议省略,因为省略的话会出现错误,如下:

select id username from users;(username作为id的别名),结果是:

技术分享

对查询的结果进行分组:group by

[group by {col_name|position}[asc|desc],…]

Asc是升序,默认的   desc是降序

查询:select * from users;结果是:

技术分享

select * from users group by password;结果是:

技术分享

select * from users group by 1;

(后面添加数字是指字段值的位置,1表示安装id分组,2表示按照username分组)

技术分享

设置分组条件:having

select sex from users group by 1 having age>30;

执行后会报错:技术分享

保证having条件的字段age必须出现在当前的select语句当中,或者having条件时聚合函数。

聚合函数是指常规的max,min,avg,sum等函数

select sex,age from users group by 1 having age>30; 运行正确。

或者使用一下方法:select sex from users group by 1 having count(id)>=2;

对查询结果进行排序:[order by {col_name|expr|position}[asc|desc],…]

select * from users order by id desc;

技术分享

按照两个字段进行排序:如下:

select * from users order by age desc;

技术分享

查询出age为13的记录有三条,再按照id降序排。

select * from users order by age desc,id desc;

 

限制查询结果返回的数量:limit

[limit {[offset,]row_count|row_count OFFSET offset}]

例如:select * from users;结果:

技术分享

查询只想返回前两条数据,执行:select * from users limit 2;

技术分享

查询只想返回第3、4两条数据,执行:select * from users limit 2,2;

技术分享

查询只想返回第5、6两条数据,执行:select * from users limit 4,2;

技术分享

 

 

 

 

 

 

 

 

数据库的基本操作