mysql 数据库基本操作设置

发布时间：2023-09-21 15:44:59 所属栏目：MySql教程来源：

导读：数据库基础知识

创建和查看数据库

　　create database 数据库名称; #创建数据库

　　show databases; #查看数据库

drop database 数据库名称； #删除数据库

数据库基础知识

创建和查看数据库

　　create database 数据库名称;    #创建数据库

　　show databases;              #查看数据库

   drop database 数据库名称；     #删除数据库

数据类型

常用数据类型 int，char，varchar，等等

创建数据表

创建数据表

    use 数据库名称；                                    　　　 #选择创建表的数据库

    create table 表名（id int(11)，name varchar(11));        #创建表和字段名称

    desc 表名；                                              #查看数据表

    alter table 旧表名 rename to 新表名；                     #修改表名

　　 alter table 表名 change 旧字段名新字段名新数据类型；   　 #修改字段名

　　 alter table 表名 modify 字段名数据类型；       　　　　　　 #修改字段名的数据类型

　　 alter table 表名 add 新字段名数据类型；        　　　　　　 #添加字段

　　 alter table 表名 drop 字段名；                　　　　　　#删除字段

　　 alter table 表名 modify 字段名数据类型 first;    　　　　 #修改字段的排列位置(排在第一个位置)

　　alter table 表名 modify 字段名1 数据类型 after 字段名2;    #字段名1 放在字段名2 后面

　　 drop table 表名；                                       #删除数据表

表的约束（列举的约束条件都是针对表中字段进行限制，从而保证数据表中的正确性和唯一性）

　　primary key　　　　主键约束，用于唯一标识对应的记录

　　foreign key　　　　外键约束

　　not null 　　　　　非空约束

　　unique　　　　　　唯一性约束

　　default　　　　　　默认值约束

主键约束

create table table2(id int(11) primary key,    #单字段主键

　　　　　　　　　　　　name varchar(11) not null,

　　　　　　　　　　　　grade float)；

create table table2(id int(11)，

　　　　　　　　　　　　name varchar(11),

　　　　　　　　　　　　grade float，

　　　　　　　　　　　　primary key(id,name));     #多字段主键

非空约束

create table table2(id int(11)，

　　　　　　　　　　　name varchar(11) not null， #非空约束　　　　　　　　　　　

　　　　　　　　　　 grade float);

唯一性约束

create table table2(id int(11)，

　　　　　　　　　　　 stu_id int unique，        #唯一性约束　　　　　　　　　　

　　　　　　　　　　   grade float);

默认约束

create table table2(id int(11)，

　　　　　　　　　　　name varchar(11)，　　　　　　

　　　　　　　　　　 grade float default 0，      #默认约束

　　　　　　　　　　 stu_id int);        　　　　　　　　　　

索引 (普通索引 - 唯一性索引 - 全文索引 - 单列索引 - 多列索引 - 空间索引)

unique 可选参数，表示唯一索引

fulltext 可选参数，表示全文索引

spatial 可选参数，表示空间索引

index 和 key，用来表示字段的索引，二者选一即可

别名可选参数表示创建的索引的名称

字段名 1 指定索引对应字段的名称

长度可选参数用于表示索引的长度

ASC 和 DESC 可选参数其中 ASC 表示升序排列 DESC 表示降序排列

创建普通索引

create table t1(id int,

　　　　　　　　　　name varchar(11),

　　　　　　　　　　score float,

　　　　　　　　　　index(id)); #在id上建立索引

show create table 表名 \G；      #查看表的结构

explain select * from 表名 where id=1 \G；   #查看索引是否被使用，当possible_keys和key的值都为id，说明索引已经存在

唯一性索引

create table t2(id int not null,

　　　　　　　　　　name varchar(20) not null,

　　　　　　　　　　score float,

　　　　　　　　　　unique index unique_id(id asc)); #在id的字段上建立索引名为unique_id的唯一性索引，并且按照升序排列

全文索引

create table t3(id int not null,

　　　　　　　　　　name varchar(20) not null,

　　　　　　　　　　score float,

　　　　　　　　　　fulltext index fulltext_id(name)    #在name字段上建立索引名为full_text的全文索引

　　　　　　　　　　)engine=myisam; #目前只有myisam存储引擎之v胡全文索引

单列索引

create table t4(id int not null,

　　　　　　　　　　name varchar(20) not null,

　　　　　　　　　　score float,

　　　　　　　　　　index single_name(name(20)))    #建立一个名为signal_name的单列索引，并且索引的长度为20

多列索引

create table t5(id int not null,

　　　　　　　　　　name varchar(20) not null,

　　　　　　　　　　score float,

　　　　　　　　　　index muli(id,name));            #在id和name上建立一个muli的多列索引

使用create index 语句在已经存在的表上创建索引

create index index_id on t1(id);       　　 #创建普通索引

create unique index unique_id on t1(id);   #创建唯一性索引

create index multidex on t1(id,name);      #创建多列索引

使用alter table 语句在已经存在的表上创建索引

  alter table t1 add index index_id (id);   # 创建普通索引

  alter table t1 add unique uniqueidx (id); #创建唯一性索引

  alter table t1 add index multidx (id,name)# 创建多列索引

　使用 alter table 删除索引

  alter table 表名 drop index 索引名；

　使用 drop index 删除索引

　drop index 索引名 on 表名；

数据库的增删改查

#select * from 表名；查看表中数据

添加数据

建表

create table student(id int(4) primary key auto_increment,    #id会自增

　　　　　　　　　　　　name varchar(20) not null,

　　　　　　　　　　　　grade float);

insert into student(id,name,grade)values(1,'zhangsan',98.5);

insert into student (name,grade,id) values ('zhangsan',98.5,2);   # 注意：字段名和值对应

insert into student(id,name) values (1,'zhangsan');   # 为表的指定字段添加数据

insert 语句中不指定字段名

insert into 表名 values (值 1，值 2，。。。)           # 添加值的顺序必须按照表中定义的字段顺序

insert into student values(1,'zhangsan',98.5);

insert 语句的其他用法

insert into 表名 set 字段名 1 = 值 1, [字段名 2 = 值 2,...]

insert into student set id=5,name='boya',grade=99;   # 添加一条记录

insert into student values

　　　　　　　　　　(6,'a',99),

　　　　　　　　　　(7,'b',99),

　　　　　　　　　　(8,'c',99);                         # 同时添加多条记录，按表中定义的字段顺序

insert into student (name,id,grade) values           # 同时添加多条数据，对应字段名和值

　　　　　　　　　　('a',6,99),

　　　　　　　　　　('b',7,99),

　　　　　　　　　　('c',8,99);

更新数据

update更新部分数据：

update student set name='asd',grade=89 where id=5;   #更新id=5的数据

update stedent set grade=100 where id<10;            #更新id<10的数据

update更新全部数据

update student set grade=100；                       #更新grade全部为100

删除数据

delete from 表名 [where 条件表达式]

delete 删除部分数据

delete from student where id=1;    #删除id=1的数据

delete from student where id>6;    #删除id>6的数据

delete 删除全部数据

delete from student                #删除全部数据

使用关键字truncate删除表中数据        #使用delete删除数据会留下记录，体现在删除数据之后的id会继续增加不会从1开始，truncate不会留下记录，从0开始，所以效率更高

truncate student;                 #删除全部数据

查询数据

简单查询

在 select 语句中指定所有字段

select 字段名 1，字段名 2，。。。from 表名

select id name,grade,gender from student；

在 select 语句中使用 * 通配符代替所有字段

select * from 表名；

查询指定字段

select 字段名 1，字段名 2，。。。from 表名

select id,name from student;

按条件查询

select id,name from t where id=4;   # 查询 t 表中 id=4 的数据

select id,name,grade from t where grade>60;   # 查询表中 grade>60 的数据

带 in 的关键字查询

select id,name,grade,gender from t where id in (7,8,9);     # 查询 id，name，grade，gender 等数据在 id 为 7，8，9

select id,name,grade,gender from t where id not in (7,8,9);     # 查询 id，name，grade，gender 等数据除了 id 为 7，8，9

带 between and 关键字的查询

select id,name,grade,gender from t where id between 5 and 9;   # 查询 id，name，grade，gender 等数据在 id 为 5-9 之间

select id,name,grade,gender from t where id not between 5 and 9;   # 查询 id，name，grade，gender 等数据除了 id 为 5-9 之间

空值查询

select id,name,grade,gender from t where grade is null;       # 查询 id，name，grade，gender 等数据在 grade 为 null 值

带 distinct 关键字的查询

select distinct 字段名 from 表名；

select distinct gender from t;         # 过滤掉重复的 gender 值

distinct 关键字作用于多个字段

select distinct gender，name from t;   # 过滤掉 gender 和 name 联合起来重复的值

带 like 关键字的查询

% 通配符

select id,name from t where name like"a%";    #查询字段中以字符 a 开头的 id 和 name 的值

select id,name from t where name like"d% h";    #查询字段中字符 d 开头，h 结尾的 id 和 name 的值

select id,name from t where name like"% h%";    #查询字段中含有 h 的 id 和 name 的值

select id,name from t where name not like"% h%";    #查询字段中不含有 h 的 id 和 name 的值

_通配符 (一个下划线只能匹配一个字符)

select id,name from t where name like"__gs";       # 查询字段中四个字节，后两位为 gs 的   id 和 name 的值

select id,name from t where name like"_ _gs";      #查询字段中带有一个空格，后两位为 gs 的 id 和 name 的值

匹配字符串总的 % 和_怎么办？

例子

%\%% 匹配字符串中带有 % 的

%\_% 匹配字符串中带有_的

带 and 关键字的条件查询

select id,name,gender from t where id<9 and gender=' 女 '; #匹配 id<9 且 gender = 女

select id,name,gender from t where id in (1,2,3,4) and name like '% ng' and grade<80; #匹配 id 为 1，2，3，4 且 name 的最后两个字符是 ng 且 grade<80

带 or 的关键字的多条件查询

select id,name,gender from t where id<9 or gender=' 女 '; #匹配 id<9 或 gender = 女

or 和 and 一起用的情况

select id,name,gender from t where gender=' 女 ' or gender=' 男’ and grade=100; #匹配 gender = 女或者 gender = 男的且 grade=100

高级查询

count 函数

select count (*) from 表名；

select count (*) from t;    #查询表中有多少条数据

sum 函数

select sum (字段名) from 表名；

select sum (grade) from t;    #计算 grade 字段的总和

avg 函数

select avg (字段名) from 表名；

select avg (grade) from t; #计算 grade 字段的平均值

max 和 min 函数

select max 或者 min (字段名) from 表名；

select max 或者 min (grade) from t; #查询 grade 字段最大值或者最小值

对查询结果排序

select * from t order by grade;        #对 t 表中的 grade 进行排序，默认升序排列

select * from t order by grade asc;    #对 t 表中的 grade 进行排序，升序排列     从小到大

select * from t order by grade desc;   # 对 t 表中的 grade 进行排序，降序排列     从大到小

分组查询

单独使用 group by

select * from t group by gender;   # 查询对 gender 字段中不同的值进行分类，然后显示每个类的其中一组数据

group by 和聚合函数一起使用

select count (*),gender from t group by gender;     # 对分类过后的数据统计并返回 gender 的几种情况

group by 和 having 关键字一起使用   #having 和 where 关键的作用相同，区别在于 having 关键字之后可以跟聚合函数，where 关键字不能

select sum (grade),gender from t group by gender having sum (grade)<300; #对于分组后的结果进行过滤   查询 gender 分组过后 grade 字段值之和小于 300 的

使用 limit 限制查询结果

select * from t limit 4;      #查询前四条记录

select * from t order by grade desc limit 4,4; #最后跟了两个参数，第一个表示偏移量为 4，即从第五条记录开始查询，第二表示返回几条记录 desc 表示 grade 字段从高到低排列

函数

select concat (id,'_',name,'_',grade,'_',gender) from t;        #查询 t 表中的记录，将个字段的值用下划线连接起来

select id,if (gender=' 男 ',1,0) from t;                        #查询 t 表中 id 和 gender 的字段值，如果 gender 的字段值是男，返回 1，否则返回 0

为表和字段取别名

select * from 表名 [as] 别名；

select * from t as s where s.gender=' 女 ';   # 为 t 表取别名为 s   s.gender 表示 t 表中 gender 的字段值

为字段取别名

select 字段名 [as] 别名 [, 字段名 [as] 别名，... ] from 表名；

select name as n,gender g from t;       # 将 name 字段和 gender 字段取别名分别为 n 和 g    查询结果也会显示 n 和 g

多表操作

外键

创建两种数据表

create table grade(id int(4) not null primary key,

　　　　　　　　　　　　name varchar(36));

create table student(sid int(4) not null primary key,

　　　　　　　　　　　　sname varchar(36),

　　　　　　　　　　　　gid int(4) not null);

为表添加外键约束

alter table student add constraint fk_id foreign key (gid) references grade (id);   # 为 student 表添加外键约束 grade 的 id 为主键 student 的 gid 为外键

删除外键约束

alter table 表名 frop foreign key 外键名；

alter table student drop foreign key fk_id;    #删除 student 表中的外键约束

操作关联表

添加数据

insert into grade (id,name) values (1,' 软件一班 ');

insert into grade (id,name) values (2,' 软件二班 ');

insert into student (sid,sname,gid) values (1,' 王红 ',1);

insert into student (sid,sname,gid) values (2,' 李强 ',1);

insert into student (sid,sname,gid) values (3,' 赵四 ',2);

insert into student (sid,sname,gid) values (4,' 李五 ',2);

select sname from student where gid=1;         # 查询 gid 为 1 的学生姓名   student 和 grade 表之间有联系   gid 的 1 表示的是软件一班

删除数据注意：一定要先删除从表中的数据，然后删除主表中的数据不然会报错

连接查询

交叉连接   select * from 表 1 cross join 表 2；   # 表一和表二分别有 4 条数据交叉查询后有 16 条数据 4*4=16

内连接　　select 查询字段 from 表 1 [inner] join 表 2 on 表 1. 关系字段 = 表 2. 关系字段； #关系字段相等的才会显示

外连接 (左连接，右连接)

复合条件连接查询：

子查询：

带 in 关键字的查询：

带 exists 关键字的查询：

带 any 关键字的子查询：

带 all 关键字的子查询：

带比较运算符的子查询：

事物存储与过程

事物管理

存储过程的创建

存储过程的使用

视图

视图概述

视图管理

数据库的高级操作

数据备份与还原

用户管理

权限管理

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!