Centos7使用mariadb数据库

安装mariadb数据库

使用yum install mariadb进行傻瓜式安装，遇到输入就输入Y；
完成之后使用

# 启动 mariadb（开启 当前）
systemctl start mariadb

# 设置 mariadb 开机自启
systemctl enable mariadb

# 查看 mariadb 状态
system status mariadb

# 关闭 mariadb（关闭当前）
system stop mariadb

# 重启 mariadb 
system restart mariadb

# 关闭 mariadb 开机自启
system disable mariadb

登录mariadb数据库

使用mysql -uroot -p，刚安装好无密码，直接enter。

---

mysql基本命令

# 查看数据库
show datacase;

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

# 查看数据表
show tables;

# 查看表结构
desc 数据表名;

# 查看表对应关键字数据
select host,user,password from user;

# 修改表中对应关键字数据
update user set password=password("root") where user='root';

# 更新数据库
flush privileges;

练习

# 创建数据库zuoye以utf8编码
create database zuoye character set utf8;

# 使用 zuoye 数据库
use zuoye;

# 创建学生表 Student
# 学号 Sno，设置主键，不允许为空
# 学生姓名Sname
# 学生性别Ssex
# 学生出生年月日Sbirthday
# 学生所在班级 Class
create table Student(sno char(5) primary key not null,
                     Sname char(10) not null,
                     Ssex char(4) not null,
                     Sbirthday datetime,
                     Class char(10));
					 
# 创建教师表 Teacher
# 教工编号Tno，设置为主键
# 教工姓名Tname
# 教工性别Tsex
# 教工出生年月日Tbirthday
# 职称Prof
# 教工所在部门Depart
create table Teacher(Tno char(5) primary key not null,
                     Tname char(5) not null,
                     Tsex char(5) not null,
                     Tbirthday datetime,
                     Prof char(10),
                     Depart char(10) not null);

# 课程表Course
# 课程号Cno，设置主键
# 课程名称Cname
# 教工编号Tno，设置为教师表外键
create table Course(Cno char(5) primary key not null,
                    Cname char(10) not null,
                    Tno char(5) not null references Teacher(Tno));

# 成绩表Score
# 学号Sno，设置为学生表外键
# 课程号Cno，设置为课程表外键
# 成绩Degree，小数点精准到1位
# 主键为学号和课程的联合主键
create table Score(Sno char(5) not null references Student(Sno),
                   Cno char(5) not null references Course(Cno),
                   Degree double(4,1),
                   primary key(Sno,Cno));

# 修改Student表sno关键字改为Sno
alter table Student change sno Sno char(5) not null;

# Student表插入数据
insert into Student values(101,'李军','男','1974-02-20','95033'),
                           (103,'陆军','男','1974-06-03','95031'),
                           (107,'王丽','女','1976-01-23','95033'),
                           (108,'曾华','男','1977-09-01','95033'),
                           (109,'王芳','女','1975-02-10','95031');

# Teacher表插入数据
insert into Teacher values(804,'李承','男','1958-12-02','副教授','计算机系'),
                          (856,'张旭','男','1969-03-12','讲师','电子工程系'),
                          (825,'王萍','女','1972-05-05','助教','计算机系'),
                          (831,'刘冰','女','1977-08-14','助教','电子工程系');

# Course表插入数据
insert into Course values('3-105','计算机导论',825),
                         ('3-245','操作系统',804),
                         ('6-166','数字电路',856),
                         ('9-888','高等数学',831);


# Score表插入数据
insert into Score values(101,'3-105','64'),
                        (101,'6-165','85'),
                        (103,'3-105','92'),
                        (103,'3-245','86'),
                        (105,'3-105','88'),
                        (105,'3-245','75'),
                        (107,'3-105','91'),
                        (107,'6-166','79'),
                        (108,'3-105','78'),
                        (108,'6-166','81'),
                        (109,'3-105','76'),
                        (109,'3-245','68');

查询练习

# 查询 Student 表中的所有记录的 Sname、Ssex和Class列
select Sname,Ssex,Class from Student;
+--------+------+-------+
| Sname  | Ssex | Class |
+--------+------+-------+
| 李军   | 男   | 95033 |
| 陆军   | 男   | 95031 |
| 王丽   | 女   | 95033 |
| 曾华   | 男   | 95033 |
| 王芳   | 女   | 95031 |
+--------+------+-------+

# 查询教师所有的单位即不重复的Depart列
select distinct Depart from Teacher;
+-----------------+
| Depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+

# 查询Student表的所有记录
select * from Student;
+-----+--------+------+---------------------+-------+
| Sno | Sname  | Ssex | Sbirthday           | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1974-02-20 00:00:00 | 95033 |
| 103 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

# 查询Score表中成绩在60到80之间的所有记录
select * from Score where Degree between 60 and 80;
+-----+-------+--------+
| Sno | Cno   | Degree |
+-----+-------+--------+
| 101 | 3-105 |   64.0 |
| 105 | 3-245 |   75.0 |
| 107 | 6-166 |   79.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
| 109 | 3-245 |   68.0 |
+-----+-------+--------+

# 查询Score表中成绩为85，86或88的记录
select * from Score where Degree in (85,86,88);
+-----+-------+--------+
| Sno | Cno   | Degree |
+-----+-------+--------+
| 101 | 6-165 |   85.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
+-----+-------+--------+

# 查询Student表中"95031"班或性别为"女"的同学,记录
select * from Student where Class='95031' or Ssex='女';
+-----+--------+------+---------------------+-------+
| Sno | Sname  | Ssex | Sbirthday           | Class |
+-----+--------+------+---------------------+-------+
| 103 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

# 以Class降序查询Student表的所有记录
select * from Student order by Class DESC;
+-----+--------+------+---------------------+-------+
| Sno | Sname  | Ssex | Sbirthday           | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1974-02-20 00:00:00 | 95033 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

# 以Cno升序、Degree降序查询Score表的所有记录
select * from Score order by Cno asc,Degree DESC;
+-----+-------+--------+
| Sno | Cno   | Degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 101 | 6-165 |   85.0 |
| 108 | 6-166 |   81.0 |
| 107 | 6-166 |   79.0 |
+-----+-------+--------+

#查询"95031"班的学生人数
select count(Class) from Student where Class='95031';
+--------------+
| count(Class) |
+--------------+
|            2 |
+--------------+

# 查询Score表中的最高分的学生学号和课程号 (子查询或者排序)
select Sno,Cno from Score where Degree= (select max(Degree) from Score);
+-----+-------+
| Sno | Cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+

# 查询Score表中除了课程最高分的学生学号和课程号（子查询或者排序）
select Sno,Cno from Score where Degree!= (select max(Degree) from Score);
+-----+-------+
| Sno | Cno   |
+-----+-------+
| 101 | 3-105 |
| 101 | 6-165 |
| 103 | 3-245 |
| 105 | 3-105 |
| 105 | 3-245 |
| 107 | 3-105 |
| 107 | 6-166 |
| 108 | 3-105 |
| 108 | 6-166 |
| 109 | 3-105 |
| 109 | 3-245 |
+-----+-------+