登录数据库
[root@VM_0_5_centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1200
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
一、库操作
1、创建库
创建库、查询所有库
MariaDB [(none)]> create database db1 charset utf8;
Query OK, 1 row affected (0.02 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
+--------------------+
1 rows in set (0.00 sec)
2、删除库
MariaDB [(none)]> drop database db1;
Query OK, 0 rows affected (0.11 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
+--------------------+
0 rows in set (0.00 sec)
3、备份库
这种备份方式,是把所有数据导出成sql。
MariaDB [(none)]> create database dbname charset utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@VM_0_5_centos ~]# mysqldump -u root -p dbname>dbname.sql
Enter password:
[root@VM_0_5_centos ~]# ls
dbname.sql
4、恢复库
恢复的时候,就是执行sql。(要先创建好库)
MariaDB [(none)]> drop database dbname;
Query OK, 0 rows affected (0.11 sec)
MariaDB [(none)]> create database db2 charset utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@VM_0_5_centos ~]# mysqldump -u root -p db2<dbname.sql
Enter password:
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: db2
-- ------------------------------------------------------
.........
-- Dump completed on 2019-07-16 10:56:11
二、表操作
1、建表
use 进入库
create table 创建表
show tables 查看表
MariaDB [(none)]> use db2
Database changed
MariaDB [db2]> create table stu(
-> id int primary key auto_increment,
-> name varchar(50) unique,
-> gender char(1),
-> birthday date,
-> score double
-> );
Query OK, 0 rows affected (0.13 sec)
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| stu |
+---------------+
1 row in set (0.00 sec)
2、删表
MariaDB [db2]> drop table stu ;
Query OK, 0 rows affected (0.13 sec)
3、结构设计
Mysql基本数据类型:
数值: bit tinyint int bigint decimal FLOAT DOUBLE
字符串: char varchar text mediumtext longtext enum
时间:DATE YYYY-MM-DD (1000-01-01/9999-12-31)
TIME HH:MM:SS('-838:59:59'/'838:59:59')
DATETIME YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 )
4、表间关系
多表之间的关系:
一对多: 从学校的角度,一个学校有多个学生;
多对一 : 从学生的角度,多个学生对应一个学校;
一对一: 一个学校只有一个名称,而一个名称只能代表一个学校;
通过主、外键进行约束;
多对多:学生与选修课之间的关系,一个学生可以选择多门选修课,而每
个选修课又可以被多名学生选择;
多对多关联关系一般需采用中间表的方式处理,将多对多转化为
两个一对多
5、约束
MariaDB [db2]> create table stu(
-> id int primary key auto_increment,
-> name varchar(50) unique,
-> gender char(1),
-> birthday date,
-> score double
-> );
primary key 主键
auto_increment 自增长
unique 唯一值
not null 不为空
default null 默认为空
default '1' 默认为1
以及外键约束
6、索引
MariaDB [db2]> alter table stu add index gender_index (gender);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db2]> alter table stu drop index gender_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
三、数据操作
1、增
insert into XXX
Values XXX;
2、删
delete from XXX
Where XXX;
3、改
update XXX set XXX
Where XXX;
4、查
select XXX from XXX;
5、存储过程
稍等。。
四、操作条件
1、排序
order by 排序的列XXX asc 升序
order by 排序的列XXX desc 降序
MariaDB [db2]> select * from stu order by score desc ;
+----+---------+--------+----------+-------+
| id | name | gender | birthday | score |
+----+---------+--------+----------+-------+
| 1 | charle | Y | NULL | 99 |
| 2 | charle2 | Y | NULL | 99 |
| 3 | charle3 | Y | NULL | 88 |
| 4 | charle4 | Y | NULL | 77 |
+----+---------+--------+----------+-------+
4 rows in set (0.04 sec)
MariaDB [db2]> select * from stu order by score asc ;
+----+---------+--------+----------+-------+
| id | name | gender | birthday | score |
+----+---------+--------+----------+-------+
| 4 | charle4 | Y | NULL | 77 |
| 3 | charle3 | Y | NULL | 88 |
| 1 | charle | Y | NULL | 99 |
| 2 | charle2 | Y | NULL | 99 |
+----+---------+--------+----------+-------+
4 rows in set (0.00 sec)
2、模糊查询
MariaDB [db2]> select * from stu where score like "9%" ;
+----+---------+--------+----------+-------+
| id | name | gender | birthday | score |
+----+---------+--------+----------+-------+
| 1 | charle | Y | NULL | 99 |
| 2 | charle2 | Y | NULL | 99 |
+----+---------+--------+----------+-------+
2 rows in set (0.00 sec)
3、函数
统计、求和、平均数、最大、最小
MariaDB [db2]> select count(id) from stu ;
+-----------+
| count(id) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
MariaDB [db2]> select sum(score) from stu ;
+------------+
| sum(score) |
+------------+
| 363 |
+------------+
1 row in set (0.00 sec)
MariaDB [db2]> select avg(score) from stu ;
+------------+
| avg(score) |
+------------+
| 90.75 |
+------------+
1 row in set (0.00 sec)
MariaDB [db2]> select max(score) from stu ;
+------------+
| max(score) |
+------------+
| 99 |
+------------+
1 row in set (0.00 sec)
MariaDB [db2]> select min(score) from stu ;
+------------+
| min(score) |
+------------+
| 77 |
+------------+
1 row in set (0.00 sec)
4、分组
统计男女人数
MariaDB [db2]> select count(id),gender from stu group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
| 2 | X |
| 4 | Y |
+-----------+--------+
2 rows in set (0.00 sec)
5、多表查询
MariaDB [db2]> select * from stu s left join class c on s.id=c.s_id;
+----+---------+--------+----------+-------+------+-------+------+
| id | name | gender | birthday | score | id | name | s_id |
+----+---------+--------+----------+-------+------+-------+------+
| 1 | charle | Y | NULL | 99 | 1 | 42班 | 1 |
| 2 | charle2 | Y | NULL | 99 | 2 | 42班 | 2 |
| 5 | charle5 | X | NULL | 86 | 3 | 42班 | 5 |
| 3 | charle3 | Y | NULL | 88 | 4 | 43班 | 3 |
| 4 | charle4 | Y | NULL | 77 | 5 | 43班 | 4 |
| 6 | charle6 | X | NULL | 89 | 6 | 43班 | 6 |
+----+---------+--------+----------+-------+------+-------+------+
6 rows in set (0.00 sec)