本文共 6183 字,大约阅读时间需要 20 分钟。
登录数据库
[root@VM_0_5_centos ~]# mysql -u root -pEnter password:Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 1200Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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)]> exitBye[root@VM_0_5_centos ~]# mysqldump -u root -p dbname>dbname.sqlEnter password:[root@VM_0_5_centos ~]# lsdbname.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)]> exitBye[root@VM_0_5_centos ~]# mysqldump -u root -p db2
二、表操作
1、建表
use 进入库
create table 创建表
show tables 查看表
MariaDB [(none)]> use db2Database changedMariaDB [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: 0MariaDB [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)
转载地址:http://hibsn.baihongyu.com/