关系型数据库常用sql语句（MySql举例）_数据库eg

1、Mysql数据库和表

1.1、创建数据库

eg：创建一个名为test1的数据库
crate database test1;
Mysql不允许两个数据库使用相同的名字，使用if not exists从句可不显示错误信息：
create database test1 if not exists test1;
注：在MySQL中每条语句都已’;’结尾

1.2、修改数据库

使用alter database命令

1.3、删除数据库

使用drop database命令：
eg:drop database test1;

1.4、创建表

列定义格式如下：
col_name type [not null|null] [default default_value]
[auto_increment] [unique [key] | [primary] key]
[comment ‘string’] [reference_definition]
col_name表中列的名字
type列的数据类型
auto_increment设置自增属性
not null|null指定该列是否允许为空
default default_value为列指定默认值
unique key | primary key都表示字段中的值使唯一的，primary key
表示设置为主键，一个表只能定义一个主键
eg：
use test1;
create table xs(
学号 char(6) not null primary key,
姓名 char(8) not null,
性别 tinyint(1) not null dafault 1
)engine = innodb;
“engine = innodb”表示采用的存储引擎是InnoDB，InnoDB是MySQL在windows平台默认的存储引擎，所以其可以省略

1.5、复制现有的表

like：使用like关键字创建一个与old_table_name表相同结构的新表，
列名、数据类型、空指引和索引也将复制，但表的内容不会复制，因此创建的新表是一个空表
as：使用as关键字可以复制表的内容，但索引和完整性约束是不会复制的
eg：create table uer_copy1 like user;
create table uer_copy2 as (select * from user)

1.6、修改表

add向表中增加新列
eg：在表t1中增加新的一列a
alter table t1 add column a tinynull null;
change修改列的名称
eg:要把一个integer列的名称从a变更到b
alter table t1 change a b integer;
modify修改指定列的类型
eg：把一个列的数据类型改为bigint
alter table t1 modify b bigint not null;
drop从表中删除或约束列
rename修改该表的表名
eg：将表a改名为b
alter table a rename to b;
eg:在test数据库的学生表中，增加‘奖学金等级’一列，并将表中的姓名列删除
ues test;
alter table xs
add 奖学金等级 tinyint not null，
drop column 姓名;

1.7、删除表

eg：删除表user1
drop table if exists user1;

2、表记录操作

2.1、插入记录

eg：insert into xs(学号，姓名，性别)
value(‘1604’,’张三’，dafault)

2.2、替换旧记录

eg:replace into xs(学号，姓名，性别)
value(‘1604’,’李四’，dafault)

2.3、修改记录

eg：将test数据库学生表中的所有学生总分都增加10，将姓名刘华的同学的备注填写为‘辅修计算机专业’，学号改为081250
updata xs
set 总分 = 总分 + 10
updata xs
set 学号 = ‘081250’，备注 = ‘辅修计算机专业’
where 姓名 = ‘刘华’；

2.4、删除记录

eg：删除person表中张三的数据
delete from person
where 姓名 = ‘张三’；
假设有三个表t1、t2、t3，他们都含有id列。要删除t1中id值等于t2的id值的所有行和t2中id值等于t3中id值的所有行
delete t1,t2
from t1,t2,t3
where t1.id = t2.id and t2.id = t3.id

2.5、清除表记录

truncate table table-name

3、数据库查询

3.1、select语句

3.1.1、选择指定的列

eg：查询xscj数据库的xs表中各个同学的姓名、专业名、总学分
use xscj;
select 姓名，专业名，总学分
from xs；

3.1.2、定义列别名

eg：查询xs表中计算机专业同学的学号、姓名和总学分，结果中各列的标题分别为number、name和mark
select 学号 as number,姓名 as name, 总学分 as mark
from xs
where 专业名 = ‘计算机’

3.1.3、替换查询结果中的数据

eg：查询学生表中计算机专业各同学的学号、姓名和总学分，对总分按如下规则进行替换：
若总学分为空值，替换为尚未选课；若总分小于50，替换为不及格；若总分在50~52之间，替换为合格；若总分大于52，替换为优秀。总学分列表的标题更改为等级。
select 学号，姓名，
case
when 总学分 is null then ‘尚未选课’
when 总学分 < 50 then ‘不及格’
when总学分 >= 50 and总学分 <= 52 then ‘合格’
else ‘优秀’
end as 等级
from xs
where 专业名 = ‘计算机’

3.1.4、计算列值

eg:按120分制重新计算成绩，显示xs_kc表中学号为081101的学生成绩信息
select 学号，课程，成绩*1.2 as 成绩120
from xs_cj
where 学号 = ‘081101’;

3.1.5、聚合函数

eg：对xscj数据库的xs表只选择专业名和总学分，消除结果集中的重复行
use xscj;
select distinct 专业名，总学分
from xs;

3.2、from子句

3.2.1、引用一个表

eg：假设当前数据库是db1，现在要显示数据库db2里面的表tb内容
select * from db2.tb；

3.2.2、多表连接

eg：查找xscj数据库中所有学生选过的课程名和课程号
select distinct kc.课程名,xs_kc.课程号
from kc,xs_kc
wherr kc.课程号 = xs_kc.课程号；
eg：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩
select 学号，姓名，课程名，成绩
from xs jion xs_kc on xs.学号 = xs_kc.学号
where 课程号 = ‘计算机基础’and 成绩 > 80；
eg:查找xscj数据库中课程不同，成绩相同的学生的学号、课程号和成绩
select 学号，课程号，成绩
from xs_kc as a jion xs_kc as b
on a.成绩 = b.成绩 and a.学号 = b.学号 and a.课程号 ！= b.课程号；

3.3、where子句

3.3.1、模式匹配

(1)like运算符

eg：查询xscj数据库学生表中姓王的学生学号、姓名及性别
select 学号，姓名，性别
from xs
where 姓名 like ‘王%’；
*如果我们想查找特殊符号中的一个或全部（和%），必须使用一个转义字符
eg：查询学生表中包含下划线的学生学号和姓名
select 学号，姓名
from xs
where 学号 like ‘%#%’ escape ‘#’;

(2)regexp运算符（正则表达式）

eg：查询学号以08开头，以08结尾的学生学号、姓名和专业名
select 学号，姓名，专业名
from xs
where 学号 regexp ‘^08.*08$’;

3.3.2、范围比较

eg：查询学生成绩数据库xs表中不在1993年出生的学生情况
select 学号，姓名，出生日期
from xs
where 出生日期 not between ‘1993-1-1’ and ‘1993-12-31’
eg:查询xs表中专业名为计算机、通信工程或无线电的学生的情况
select *
from xs
where 专业名 in (‘计算机’,’通信工程’,’无线电’)；

3.3.3、子查询

eg：查找在xscj数据库中选修了课程号为206的课程的学生的姓名、学
号
select 姓名，学号
from xs
where 学号 in(
select 学号
from xs_kc
where 课程号 = ‘206’
);
eg：查找为选修离散数学的学生的姓名、学号、专业名
select 姓名，学号，专业名
from xs
where 学号 not in(
select 学号
from xs_kc
where 课程号 in(
select 课程号
from kc
where 课程名 = ‘离散数学’
)
);

3.4、group by子句

eg：将xscj数据库中各专业名输出
select 专业名
from xs
group by 专业名；
eg：在学生数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数，以及学生总人数
select 专业名，性别，count(*) as 学生数
from xs
group by 专业名，性别
with rollup;

3.5、having子句

eg：查找学生成绩数据库中平均成绩在85分以上的学生的学号和平均成绩
select 学号，avg(成绩) as ‘平均成绩’
from xs_kc
group by 学号
having avg(成绩) >= 85;

3.6、order by子句

关键字asc表示升序排序，desc表示降序排序，系统默认是asc
eg：将计算机专业学生的计算机基础课程成绩按降序排序
select 姓名，课程名，成绩
from xs,kc,xs_cj
where xs.学号 = xs_cj.学号
and xs_kc.课程号 = kc.课程号
and 课程名 = ‘计算机基础’
and 专业名 = ‘计算机’
order by 成绩 desc

3.7、limit子句（限制被select语句返回的行数）

eg：查找xs表中学号最靠前的5位学生的信息
select 学号，姓名，专业名
from xs
order by 学号
limit 0,5;

3.8、union语句（可以把许多select语句的结果组合到一个结果集合中）

eg：查找学号为081101和学号为081210的两位同学的信息
select学号，姓名，专业名
from xs
where 学号 = ‘081101’
union
select学号，姓名，专业名
from xs
where 学号 = ‘081210’