MySQL

记录一下数据库的基础操作

登录数据库

mysql -u root -p

数据库基本操作

# 查询所有数据库
show databases;

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

#使用数据库
use 数据库名;

# 删除数据库
drop database 数据库名;

表操作

创建和删除

#创建表
create table 表名(
    字段名,数据类型,
    字段名, 数据类型,
    ......
    字段名,数据类型
);
#最后一行末尾，不能加逗号
create table 表名(
    id,int,
    name,varchar(20),
    password,varchar(32)
);

# 查询当前数据库下所有表名称
show tables;

# 查询单个表
desc 表名称;

# 删除表
drop table 表名;

MySQL 常用数据类型

SQL 常用的数据类型如下

• 整型

取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为 (0~256)。

int (n) 里的 n 是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个 n 有什么用

• 浮点型

• 字符串 (char, varchar, text)

• 日期时间类型

• more 布尔类型等

修改表

# 修改表名
alter table 表名 rename to 新表名;

# 添加列
alter table 表名 add 新列名 数据类型;

# 修改列数据类型
alter table 表名 modify 列名 新的数据类型及约束;

# 修改表名，不重命名版
alter table 表名 modify 列名 类型及约束;

# 修改表名，重命名版
alter table 表名 change 原名 新名 类型及约束;

# 增加主键约束（索引）
alter 表名  add primary key(约束列名);

创建索引

目的：当数据量很大时，加快数据的查询速度，但是会降低更新表的速度，还会占用一定的存储空间
索引的类型：

• UNIQUE (唯一索引)：不可以出现相同的值，可以有 NULL 值
• INDEX (普通索引)：允许出现相同的索引内容
• PROMARY KEY (主键索引)：不允许出现相同的值
• fulltext index (全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

# 添加索引
#普通索引
alter table 表名 add index 索引名 (列名) ;

# 唯一索引
alter table 表名 add unique (列名) ;

# 主键索引
alter table 表名 add primary key (列名) ;

# 删除索引（两种方式）
drop index index_name on 表名 ;

alter table 表名 drop index 索引名 ;
# 删除主键
alter table 表名 drop primary key ;

其中，在前面的两条语句中，都删除了表名中的索引-索引名。而在最后一条语句中，只在删除 PRIMARY KEY 索引中使用，因为一个表只可能有一个 PRIMARY KEY 索引，因此不需要指定索引名。如果没有创建 PRIMARY KEY 索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

插入数据

插入数据通常有两种形式

• 插入一个元组
• 插入子查询结果

insert into 表名 values(值1，值2....); # 举例 insert into student values('20180001','李勇','男','2000-3-8','信息安全'); # 主键列值不能为空或重复，否则会报错，插入失败

插入子查询结果

#创建了一张表，并且将smajor，和年份的平均值插入进去
create table smajor_age(
smajor varchar(20),
avg_age smallint);

insert into smajor_age(smajor,avg_age) 
select smajor,Avg(extract(year from current_date) - extract(year from sbirthdate))
from student GROUP BY smajor;

更新数据

# 修改某一个元组的值
update 表名 set 列名 = '修改的值' where 查询列 = '修改的那一列的值'; 

# 例子
update 表名 set 列名 = '修改的值' where 查询列 = '修改的那一列的值';

# 修改多个元组
update sc set grade = grade-5 where semester ='20201' and cno='81002'; 

# 带子查询的修改语句
in(select 另一个表的同名 from student where smajor='计算机科学与技术');

# 将计算机科学与技术的学生成绩修改为1
update sc set grade = 1 where sno in(select sno from student where smajor='计算机科学与技术');

删除数据

# 删除单个
delete from student where sno='20180007';

# 删除所有
delete from 表名;

# 带子查询的删除
delete from 表名 where .....
# 例子
delete from sc where sno in(select sno from student where smajor='计算机科学与技术');

转义

# 转义, 将斜杠转移成反斜杠
select \* from course where cname like 'DB/\_Design' ESCAPE '/';
# 其实不转义直接写也行
select \* from course where cname like 'DB\\\_Design';

上课记录 1

# 查询学生姓名和学号（可以自定义显示顺序，学号姓名也是可以的）
select sname,sno from student;
# 查询学号，姓名，专业
select sno,sname,smajor from student;
# *是通配符，查询所有
select * from student;

# 获取年龄，（数据库存储的是出生年月）
select sname,(extract(year from current_date) - extract(year from sbirthdate)) "年龄" from student;

# 在前面加一列<"当前年龄是:">
select sname,"当前年龄是:",(extract(year from current_date) - extract(year from sbirthdate)) "年龄" from student;

# 选择表中的若干元组（行记录）
# 1.消除取值重复的行，使用distinct消除
select distinct sno from sc;

# 2.默认是all 查询所有,下面两行等价
select sno from sc;
select all sno from sc;

# 查询条件
# = 等于,> 大于,< 小于,>= 大于等于,<= 小于等于,!=或<> 不等于.!>不大于，!<!不小于

# 几条例子
select  sname from student where smajor="计算机科学与技术";

select sname from student where extract(year from sbirthdate) >= 2000;

select sname from student where ssex = "男";

select distinct sno from sc where grade < 60;

# 确定范围
# 成绩不在70-80
select  distinct sno from sc where grade not BETWEEN 70 and 80;

#两张表配合查
select  distinct sname,sno from student where sno in (select sno from sc where grade not BETWEEN 70 and 80);

select sname,ssex from student where smajor not in ("计算机科学与技术");

# 查询2018级学生信息
select * from student where sno like '2018%';

# 查询姓刘的学生，百分号是代表任意长度
select sname,ssex,sno from student where sname like '刘%';

#不姓刘的学生
select sname,ssex,sno from student where sname not like '刘%';

# 查询学号末尾是3的学生,一个_占一位
select sname,ssex,sno from student where sno like '2018___3';

上课记录 2

# 查询成绩为空
select sno,cno,grade from sc where grade is null;
select sno,cno,grade from sc where grade is not null;

select sno,sname,ssex from student where smajor='计算机科学与技术' and extract(year from sbirthdate)>=2000;

# 默认降序
select sno,grade from sc where cno='81003';

# 升序
select sno,grade from sc where cno='81003' ORDER BY grade asc;

# 学号升序，成绩降序
select * from sc ORDER BY cno asc,grade desc;

select * from student where sname like'王%' and smajor='计算机科学与技术';

# 聚集函数
count(*) # 统计元组个数
select count(*) from student;

# count #统计一列值的个数
# 查询选修了课程的人数
select COUNT(DISTINCT sno) from sc;

# avg() # 求某一列平均值
# 选修81001号课程学生的平均成绩
select avg(grade) from sc where cno='81001';

# max() min()
# 81001号课程中学生的最高成绩
select max(grade),min(grade) from sc where cno='81001';

# 课程号，及选修人数
select cno,count(sno) from sc GROUP BY cno;

# GROUP BY 分组
# 2019第二学期课程数大于等于1的学号
select sno from sc where semester='20192' GROUP BY sno having COUNT(*) >= 1;

#平均成绩大于85
select sno,avg(grade) from sc GROUP BY sno having avg(grade)>=85;

# 查询数据库排名前2的学生，limit限制，多表连接
select sno from sc,course where cname like'数据库%' and 
course.cno = sc.cno order by grade desc limit 2;

# 取前三行，但是忽略前两行，输出第三行数据
select sno from sc,course where cname like'数据库%' and 
course.cno = sc.cno order by grade desc limit 2,3;
# 一样的
select sno from sc,course where cname like'数据库%' and 
course.cno = sc.cno order by grade desc limit 3 offset 2;

#连接查询 ：涉及两张表及以上的
# 等值查询，首先连接条件中的各连接字段类型必须可比
# 查询每个学生及其选修课的情况
select student.*,sc.* from student,sc where student.sno = sc.sno;

#上面的会出现2个sno，因为两个表都有
# 自然连接查询：把结果表目标列中重复的属性列去掉的等值查询
# 由于两个表都有sno，所以第一个sno必须指定，是哪一张表
select student.sno,sname,ssex,sbirthdate,smajor,cno,grade FROM
student,sc where student.sno = sc.sno;

# 复合连接查询：where子句中有多个条件的连接查询
# 查询选修81002课程且成绩在90分以上的学生学号和姓名
select student.sno,sname from student,sc where student.sno = sc.sno and sc.cno = '81002' and sc.grade > 90;