数据库基础操作

简介：

对于数据库的操作四类：

1. 增：添加新的行
2. 删：删除表中的行、表本身、库
3. 改：修改
4. 查：查看库中都有哪些表、表中的数据

对于数据库的操作对象：

• 库
• 表
• 数据

操作库：

• 查看哪些库
• 新建数据库
• 删除数据库

操作1：查看系统中都有那些数据库

show  databases

 

 操作2：新建数据库

创建数据库，命令格式：

create  database  库名  character  set  '字符集‘  collate  ’排序规则‘;

 

案例：创建数据库，库名tb6，字符集是utf8，排序规则是utf8-general-cl

create  database  tb6  character  set  ’utf8‘  collate  ’utf8-general-cl‘

操作3：删除数据库

方法一：

右击删除

方法二：

drop  database  库名； 

案例：删除数据库tb2

drop  database  tb2； 

总结数据库的操作：

1. 查看：show  databases
2. 新建：create  database  库名 character  set  字符集  collate  排序规则
3. 删除：drop database  库名

操作表

• 查看当前数据库中都有哪些表（提前选定一个库)
• 新建库
• 删除表

操作1：选定数据库：

use   库名

查看选定的数据库：

select  database  （） 

查看当前库中都有哪些表

show  tables 

案例：

use  db003                        #使用数据库db003

select  database  （）      #查看当前数据库

首位 tables                       #查看当前库中所有表 

操作2：新建表：

• 表中每一列都是一个字段
• 每个字段只能存储一种类型数据 

数据类型：

• int：   整型：只能存储整数类型
• fioat： 浮点型：可以存储小数类型
• char：  字符类型：固定的长度字符，提前分配好存储空间，无论是否使用，存储空间大小不变
• varchar：  字符类型：可变长度

案例：创建表，表名是tb001，字段有name（可变字符),age(整型），id(整型 主键)

create  table  tb001 （name  varchar (50)，

age  int ，

id  int not  null  primary  key );

操作3：查看一个已经存在表的结构

• 在查看表的时候，除了可以查看表名之外，也可以查看表结构
• 表结构：表中有几个字段，字段名分别是什么，字段属性是#####

查看表名： show  tables

查看表结构： desc  表名 

 

field：表示这个表中有几个字段，分别叫什么名字

type：表示每个字段的数据类型

null： 下面的值有俩种（yes|no）yes表示这个字段可以为空，no就是不可以

key：表示哪个字段是主键

default：默认值，如果没有那些内容，表示没有设置默认值

extra：表示每个字段的额外的属性有哪些

操作4：删除表

drop  table  表名

案例：删除表lx1

drop  table  lx1 

总结：表的操作

查看：

查看都有哪些表： show  tables

查看表结构：         desc   表名

创建：

create  table  表名（字段  属性，字段     属性，字段      属性）

删除：

drop  table  表名

总结2：对比删除库和删除表

库： drop  database  库名

表： drop  table  表名 

字符的修饰（属性）

1.int：整数，只能存储整数类型

2.float：浮点型：可以存储小数类型

3.char：字符类型:固定长度的字符,提前分配好存储空间，无论是否使用，存储空间大小不变

4.varchar:字符类型：可变长度

5.unsigned：表示的数字是无符号数字（只能是正数）

6.enum：枚举类型（给用户一个选择范围，用户仅仅可以选择一个）

7.null：这个字段可以空

8.not null：这个字段不可以为空

9.primary  key：主键，用来唯一标识表中的一条记录（主键的内容不能重复）

10.auto-increment：设置主键自增

11.default：设置字段的默认值 

 案例：

#选择数据库

use  tb1；

#创建tb001表，字段id..

create  table  tb001（id  int  unsigned  auto-increment  not  null  primary key， name char（30）  not  null，age int  unsigned， gender enum （“M",”F“），addr carchar(50) default  

"heibi")

#查看都有哪些表

show  tables

#查看表结构

desc  tb001

 

在表中插入数据

注意点：

1. 插入值的类型，必须和字符的类型保持一致
2. 如果数据类型是字符串，必须引号引起来，数字的话，可以不加
3. 插入的数据顺序和字段的顺序必须保持一致 

格式一：向表中插入数据

insert  into  表名  values(值1，值2，值3) 

 案例：向表中插入数据

insert  into  tb001  values （4，”云中君“，18，”M“，”shanxi“）

格式二：

insert into  表名（字段，字段，字段）values（值1，值2，值3)

案例

insert  into  tb001（age，name，gender） values（66，”云中君“，”F") 

注意点：

• 这里仅仅是向部分字段写入值
• 没有指定字段，会写如空
• 字段的顺序要和值的顺序保持一致4

总结：插入数据的方式

insert  into  表名  values(值1，值2，值3）

insert  into  表名 （字段，字段，字段） values（值1，值2，值3）

 删除表中的数据

drop  database  库名                            #删除一个数据库

drop   table  表名                                 #删除一个表

delete   from  表名                               #删除表中全部的数据

delete  from  表名  where 条件            #删除表中指定的数据

truncate   表名                            #删除表中全部的数据，而且会截断表（id会重新开始自增）

 案例：

use  tb1;

delete  from  tb001

select  *  from  tb001 where  gender="M' or name="zhangsanfeng"

truncate  tb001

修改表中数据

格式

update 表名 set 字段=值

update 表名 set 字段=值 where 记录=值 

案例：把id是6的年龄改成21

update tb001 set age=21 where id-6

修改数据库的名字无法修改

修改数据的字符集

alter database tb1 character set "ascii" collate "ascii_general_ci" 

 修改表

增加字段

alter table 表名 add column 字段名 字段属性 after 字段名

删除字段

•  建议不要删除

alter tablle 表名 drop column 字段名

案例：

alter table tb001 drop column phone 

修改字段名

alter table 表名 change column 字段名 新字段子 属性约束 

案例

alter table tb001 change column addr ad varchar（50） 

修改字段的默认值

alter table 表名 modify column 字段名 字段属性 default 值 

 

修改字段顺序

alter table 表名 modify column 字段名 属性 after 位置

案例

alter table tb001 modify column ad varchar(50) after id

补充

show charset  #查看数据所支持的字符集

show create database aa  #查看当前数据库的字符集 

 备份

导出：先对数据库进行备份，然后再提取备份文件中的sql语句

导入：直接再数据库中运行导出的sql文件

测试用的数据库：hellodb

• students：学生表，这个表中存储了学生的基本信息
• classes：班级表，这个表中保存了班级的基本信息
• courses：课程表
• scores：成绩表
• teachers：老师表
• coc：班级和开设的课程对照的关系表
• toc：老师和所教授的课程的对照表

stuid：这是学号，数字类型，非负，自增，非空，主键

name：这是学生姓名，是字符类型，非空

age：年龄

gender：性别

classid：班级名

teacherid：老师的编号

简单查询

MySQL的查询操作：

• 单表查询：就是从一个表中查询数据，也叫简单查询
• 多表查询：联合多个表查询数据，叫链接查询

查询的格式：

select   查询的内容   from  表名  where  条件

*：表示所有的内容

一个：仅仅查询一个字段

多个：查询多多个字段，多字段之间用，隔开

where 后面的条件用法：

>             大于

>=           大于等于

<             小于

<=           小于等于

!=            不等于

<>           不等于

between...and...            判断范围

is null                   可以为空

is not null             不可以为空

like                       模糊查询

in ji                       判断在某个字段在给定的集合中

案例：姓名以s为开头的： “s%”：以s为开头  "%s":以s为结尾  “%s%”:包含s的

select * from students where lie “s%”

案例：查询年龄在20或者22或者25的

方法一：select * from students where age in（20，22，25）

方法二：select * from students where age=20 or age=22 or age=25

查询格式：

select 查询的内容 from 表名 where 条件 order by 字段名

order by：

1. 默认是升序  asc：从小到大
2. 降序   desc：从大到小

#将所有的学生按照年龄排序，排序规则是降序

select * from students order by desc

#将所有的学生按照姓名和年龄排序

select * from students order by name,age

#指定输出几条记录

select 查询的内容 from 表名 where 条件 order by 字段 limit 数字

案例：输出年龄最大的学生信息

select * from students order by age desc limit 1

聚合函数：

• sum()    求和                              如：select   sum(age)   from   students
• avg()    求平均值                        如：select   avg(age)   from   students 
• max()    求最大值                       如：select   max(age)   from   students 
• count()    记录最大条数              如：select   count(age)   from   students 

#计算所有男生年龄的和

select sum(age) from students where gender "F" 

#查询年龄最小的学生信息

select * from students order by age asc

去重：去除重复 的字段 【distinct】

如：select distinct age from students

分组：group by

如：按照班级分组，并且统计各个班级有多少人

select count(*),classid from students grop by classid

分组后再加条件：having

格式：select 字段 from 表名 grop by 字段 having 条件

查询进阶：

• 交叉连接：有着大量无用数据
• 自然连接：在交叉连接基础上加条件

交叉连接格式：select * from students,classes

自然连接格式：select * from students,classes where students.classid=classes.classid

字段和表别名：

格式：select 字段名 as 新字段名 from 表名 ...

           select 字段名 from 表名 as 新字段名 ...

如： select name as name1 from students where name="张三"

select name，c.classid from as s，classes as c where s.classid=c.classid

使用自然连接存在问题：导致数据缺失

解决方法：

1. 左外连接：左表中的数据会出现，右表中的数据有就显示，没有则不显示
2. 右外连接：右表中的数据会出现，左表中的数据有就显示，没有则不显示

左外连接格式：

select * from 左表 left join 右表 on  左表.字段=右表.字段

右外连接格式：

select * from 左表 right join 右表 on  左表.字段=右表.字段

案例：使用右连接查询俩个表的数据 

select * from students right join classes on students.sid=classes.sid

子查询：在查询中，使用另外一个语句的查询结果

案例：查询所有年龄大于平均年龄的学生

select name from students where age=(select avg(age) from students)