Mysql之数据库操作分类

登录数据库

[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)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值