一、mysql数据库的基本操作命令（第一章）

数据库的基本操作

查看数据库

show databases;

创建数据库

create database database_name;

管理数据库

打开数据库

use database_name;

修改数据库

alter database mysqltest

删除数据库

drop database [if exists] database_name;

数据库表的基本操作

增加数据库表

创建数据表

create table [if not exists] student(
    属性名 字符属性 是否为空 默认值,
    studentno char(11) not null comment'学号';
)

//创建数据表的同时创建主键约束
//方法一、行级约束
create table [if not exists] student(
studentNo char(11) not null,
studentName char(16) not null,
studentAge int(4) not null,
)primary key(setudentNo);

//方法二、列级约束
create table [if not exists] student(
studentNo char(11) not null primary key,
studentName char(16) not null,
studentAge int(4) not null,
);

//创建表时创建外键约束
//行级约束
create table [if not exists] Scoures(
studentNo char(12) not null,
studentName char(12) not null,
Pcoures int(4) not null,
)primary key(studentNo),
foreign key(Pcoures) references Coures(coures);
//外码是Pcoures   参照表是Coures中的coures属性

查看数据库表

查看数据表

show tables;

查看数据表的基本结构

describe table_name;

查看数据表详细结构语句

show create table table_name;

修改数据库表

增加一列

alter table table_name add new_lie varchar(10) not null after XXX;

重命名

alter table old_lie rename to new_lie;

修改表的字段

将table_name表中的type enum(‘必修’,‘选修’)字段改为default’必修’

alter table table_name modify type enum('必修','选修') default'必修';

删除字段

删除student表中的address字段

alter table student drop address;

修改主键

alter table table_name drop primary key;
alter table table_name add primary key(No_1);

删除数据库表

drop table table_name;

表的数据操作

表的数据插入

insert

insert into table_name values
('','','',''),
('','','','');

replace

replace into table_name valuse
('','','',''),
('','','','');

insert 与 replace 差别在于insert是直接插入,不查重.

replace是要查重,如果发现表中有此行数据(根据主键或者唯一索引判断)则直接覆盖此数据

load data

！！！！！！用load data语句插入！！！！！！！

load data语句是导入外部的txt类型的表到此数据表中

load data local infile"txt的绝对路径" into table table_name;

注意:txt的路径要用:\\两个反斜杠作为间隔,因为第一个是转义字符

set

用set子句直接赋值时可以不按顺序插入数据,对允许为空值得列可以不插入

insert into table_name
set No_1='',No_3='',No_7='';

表的数据修改

用update…set…命令可以修改一个表的数据.

//将table_name这个表中No_3为...和No_6为...的No_1属性的值改为data
update table_name set No_1=data
where No_3=''&&No_6='';

表数据的删除

用delete…from… 语句可以从单个表中删除指定表数据

delete from table_name
where
roder by;

表数据的查询

基本语句

select ... from table_name
where

全部查询

select * from table_name

单个属性查询

select No_1,No_2,No_3 from table_name;

select…from…基本用法

用 select…as…修改别名

select No_1 as 'No_new' , No_2 as'No_new2' 
from student
where phone like '166%'

distinct 的基本使用方法

利用distinct子句可以从结果集中出去重复的行.只要有一行符合条件就显示符合条件的那一行

select distinct No_1,No_2
from table_name
where No_3 > 99
order by studentno;

用where子句过滤

select * from table_name
where No_1>60;

带in关键字查询

//在分数表score中查询学号为003,004,005,006的学生的学号,生日,性别
select studentno,birthday,sex from score
where studentno in('003','002','005','006');

带between and的范围查询

在分数表中查询课程号为'c01'的学生的学号和期末成绩,并且要求平时成绩在80~95分
select studentno,final from score
where courseno = 'c01' and daily between 80 and 95;

带like的字符匹配查询

%可以匹配多个字符

_只可以匹配一个字符

带is null 关键字查询空值

查询一个字段是否为空用is null表示

查询一个字段不为空用is not null表示

带and的多条件查询

带or的多条件查询

使用order by对结果进行排序

排序语法必须是想查询到结果再排序

升序asc

降序desc

//在student表中查询高于850分的学生学号,姓名,和入学成绩,并按照入学成绩的降序排列
select studentno,sname,entrance from student
where entrance > 850
order by entrance desc;

使用group by 和 having一起使用

having 的作用主要是进行子查询，用在where子句之后

查询选课在3门以上且各门课程期末成绩高于75分的学生的学号及其总成绩，查询结果按照总成绩降序列出

select studentno 学号 ,sum(daily*0.3+final*0.7) as '总分'
from score
where final > 75
group by studentno
having count(*)>=3
order by sum(daily*0.3+final*0.7) desc;

group by和with rollup一起进行统计

with rollup

with rollup是在已经查询出来的结果的基础上再进行排序

查询socre表中每一门课的期末成绩平均值和所有成绩的平均值

select courseno 课程号,avg(final) 课程期末平均学分
from socre
grounp by courseno with rollup

limit 限制查询结果的数量

第一种用法（一个参数）

• limt 参数1

• 从初始位置开始向下选取 参数1 行

第二种用法（两个参数）

• limt 参数1，参数2
• 参数1：开始读取的第一条记录的编号
• 参数2：要查询记录的个数

聚合函数

count()函数

sum()函数

avg()函数

max()函数

min()函数

多表链接

内连接(inner join)

通过from 。。。on语句进行内连接

• 内连接查询是通过比较数据源表间共享列的值，从多个源表检索符合条件的的操作。可以使用等号运算符的链接，也可以链接多个不等值的列中的值

通过内连接两个表查询不同表中的列的值并汇总到一张表中

select student.studentno,sname,final
from student inner join score
on student.studentno = score.studentno
where score.courseno = 'c05109';

左外连接(left join)

• 将左表的所有数据分别与右边中的每条数据进行链接组和，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并再右表的相应列中添加null
  

• 语法

select student.studentno,sname,sex,daily,final
from score left join student
on student.studentno = score.studentno;

右外连接(right join)

• 右外连接是外部链接的一种，其中包含join子句中最右侧表的所有行。如果右侧表中的行与左侧表中的行不匹配，将为结果集中来自左侧表中的所有列分配null值

mysql> select student.studentno,sname,sex,daily,final
    -> from score right join student
    -> on student.studentno = score.studentno;

A left join B === B right join A

交叉连接(cross join)

• 交叉链接是在没有where子句的情况下，产生的表的笛卡尔积。两个表作交叉连接时，结果集大小为二者行数之积。

select student.*,score.*
from student cross join score;

连接多个表

• 连接n张表就要使用n-1次连接条件

mysql> select student.studentno,sname,cname,final,round(period/16,1)
    -> from score join student on student.studentno = score.studentno
    -> join course on score.courseno = course.courseno
    -> where substring(student.studentno,1,2) = '18';

子查询

利用子查询做表达式

• 可以把子查询的结果当作一个普通的表达式来看待，用在外查询的选择条件中
• 

利用子查询生成派生表

• select的数据源由from子句指定，from子句可以指定单个表或者多个表，还可以查询来自视图、临时表或者结果集里的数据

• 派生表可以定义一个别名 用as

• mysql> select TT.studentno 学号,TT.courseno 课程号,TT.final*0.8+TT.daily*0.2 总评
      -> from(
      -> select * from score
      -> where final > 85) as TT
      -> where TT.final*0.8+daily*0.2 > 90;
  

• 

• 总结：此派生查询是先查询里面的最终最终成绩大于85的叫做别名TT，作为外层查询的派生表，再进行where查询。

• 实际上就是在from子句中使用子查询作为派生表数据源

where子句中的子查询

• 将语句中的子查询的结果作为该语句条件中的一部分，然后利用这个条件过滤本层查询的数据

• mysql> select studentno,courseno,final
      -> from score as a
      -> where final < (
      -> select avg(final)
      -> from score as b
      -> where a.courseno = b.courseno
      -> group by courseno);
  

• 

• a表中取出一个学生的信息与相同表中该课程的平均成绩做比较

带in关键字的子查询

• 比较运算符只符合子查询放回的结果只包含一个值的情况

• in运算符可以与子查询是一个结果集进行比较

• 例如：查询期末成绩高于93分的学生的学号、姓名、电话和Email

• mysql> select studentno,sname,phone,Email
      -> from student
      -> where studentno in (
      -> select studentno
      -> from score
      -> where final > 93);
  

• 

带exists关键字的查询

• exists的作用是内查询语句不返回查询记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，就返回一个真值。否则返回假。

• not exists 与其作用相反

• 重点：exists强调的是，只要有一条数据符合，则就会返回所有数据

• 普通查询与exists查询的区别

• 普通查询

• 找出在2000-01-01之前出生的学生的学号，姓名，生日，电话

• 

• exists查询（模糊查询）

• 查询是否有在2000-01-01之前出生的学生，列出其学号，姓名，生日，电话
  -

对比较运算进行限制的子查询

• all、some、any

• 作用是让外层查询的值与子查询中的结果的每一个值都比较。

• all：每一个值都比较，返回结果为false或者ture

• 例如：在score表中查找比c05109课程中每一位同学的期末考试成绩都要高的学生的学号，姓名，电话，最终成绩

• mysql> select student.studentno,sname,phone,final
      -> from student inner join score
      -> on student.studentno = score.studentno
      -> where final > all (
      -> select final
      -> from score
      -> where courseno = 'c05109');
  

• 

更新数据库

• update 。。。set。。。

使用正则表达式进行模糊查询（regexp）

• ^ 可以匹配以特定字符或者字符串开头的记录

• 

• 可以匹配无任何字符串的值

• $ 任意字符串结尾

• . 表示字符串中的任意一个字符

• | 将需要找的字符串隔开
  -

• -> from student inner join score
      -> on student.studentno = score.studentno
      -> where final > all (
      -> select final
      -> from score
      -> where courseno = 'c05109');
  
  

更新数据库

• update 。。。set。。。

使用正则表达式进行模糊查询（regexp）

• ^ 可以匹配以特定字符或者字符串开头的记录

• 

• 可以匹配无任何字符串的值

• $ 任意字符串结尾

• . 表示字符串中的任意一个字符

• | 将需要找的字符串隔开

• 

基本知识完成啦O(∩_∩)O

下一章：mysql的索引和视图的基本知识