一、数据库的登陆：当使用mysql -usqlxxl -p登陆数据库的那一刻，到数据库操作提示符MariaDB [(none)]>的出现，就是数据库的登陆过程
mysql -usqlxxl -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 3 Server version: 10.5.8-MariaDB Arch Linux 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)]>
二、基本概念：数据库主要靠表来存储数据。表的主要元素有属性，一个属性为一列，所有的属性构成列的集合。每一行数据的每一个列都有数据。反映着用户查看数据的方式。
数据库：存储数据表的库为数据库。表必须在数据库中。
数据库的操作主要有应用、查看、创建和删除。
MariaDB [(none)]> show databases; //显示数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student_info | +--------------------+ 4 rows in set (0.036 sec) MariaDB [(none)]> create database sample; //创建数据库 Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> show databases; //查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sample | | student_info | +--------------------+ 5 rows in set (0.001 sec) MariaDB [(none)]> drop database sample; //删除数据库 Query OK, 0 rows affected (0.039 sec) MariaDB [(none)]> show databases; //查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student_info | +--------------------+ 4 rows in set (0.001 sec) MariaDB [(none)]> use student_info; //应用数据库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
三、数据表的基本操作
MariaDB [student_info]> show tables; //查看数据库中的表们 +------------------------+ | Tables_in_student_info | +------------------------+ | stu_info | +------------------------+ 1 row in set (0.001 sec) MariaDB [student_info]> create table books(book_id INT, title TEXT, status INT); //创建表books Query OK, 0 rows affected (0.186 sec) MariaDB [student_info]> show tables; //查看数据库中的表们 +------------------------+ | Tables_in_student_info | +------------------------+ | books | | stu_info | +------------------------+ 2 rows in set (0.001 sec) MariaDB [student_info]> describe books; //查看表的详细结构 +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | book_id | int(11) | YES | | NULL | | | title | text | YES | | NULL | | | status | int(11) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.001 sec) MariaDB [student_info]> insert into books values(100, 'Heart of Darkness', 0); //插入一条数据 Query OK, 1 row affected (0.066 sec) MariaDB [student_info]> insert into books values(101, 'The Catcher of the Rye', 1); //插入第二条数据 Query OK, 1 row affected (0.033 sec) MariaDB [student_info]> insert into books values(102, 'My Antonia', 0); //插入第三条数据 Query OK, 1 row affected (0.025 sec) MariaDB [student_info]> insert into books values(103, 'Uncle's Home', 2); //插入第三条数据 Query OK, 1 row affected (0.033 sec) MariaDB [student_info]> select * from books; //查询表的内容 +---------+------------------------+--------+ | book_id | title | status | +---------+------------------------+--------+ | 100 | Heart of Darkness | 0 | | 101 | The Catcher of the Rye | 1 | | 102 | My Antonia | 0 | | 103 | Uncle's Home | 2 | +---------+------------------------+--------+ 4 rows in set (0.000 sec) MariaDB [student_info]> select * from books where status = 0; //条件查询表的内容 +---------+-------------------+--------+ | book_id | title | status | +---------+-------------------+--------+ | 100 | Heart of Darkness | 0 | | 102 | My Antonia | 0 | +---------+-------------------+--------+ 2 rows in set (0.025 sec) MariaDB [student_info]> select * from books where status = 0G; //查询表，改变显示方式 *************************** 1. row *************************** book_id: 100 title: Heart of Darkness status: 0 *************************** 2. row *************************** book_id: 102 title: My Antonia status: 0 2 rows in set (0.001 sec) ERROR: No query specified MariaDB [student_info]> update books set status = 1 where book_id = 102; //更新表信息 Query OK, 1 row affected (0.029 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [student_info]> select * from books where status = 1; //条件查询表的内容 +---------+------------------------+--------+ | book_id | title | status | +---------+------------------------+--------+ | 101 | The Catcher of the Rye | 1 | | 102 | My Antonia | 1 | +---------+------------------------+--------+ 2 rows in set (0.001 sec) MariaDB [student_info]> update books set status = 0 where book_id = 101; //更新表信息 Query OK, 1 row affected (0.022 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [student_info]> select * from books where status = 0; //条件查询表的内容 +---------+------------------------+--------+ | book_id | title | status | +---------+------------------------+--------+ | 100 | Heart of Darkness | 0 | | 101 | The Catcher of the Rye | 0 | +---------+------------------------+--------+ 2 rows in set (0.000 sec) MariaDB [student_info]> update books set title = 'The Catcher in the Rye', status = 1 where book_id = 101; //更新表信息 Query OK, 1 row affected (0.030 sec) Rows matched: 1 Changed: 1 Warnings: 0
四、表的高级操作
MariaDB [student_info]> create table status_names(status_id INT, status_name char(8)); //创建表 Query OK, 0 rows affected (0.182 sec) MariaDB [student_info]> insert into status_names values(0, 'inactive'), (1, 'active'), (2, 'inactive'); //向表中插入数据 Query OK, 3 rows affected (0.038 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [student_info]> select * from status_names; //查询表内容 +-----------+-------------+ | status_id | status_name | +-----------+-------------+ | 0 | inactive | | 1 | active | | 2 | inactive | +-----------+-------------+ 3 rows in set (0.000 sec) MariaDB [student_info]> select book_id, title, status_name from books join status_names where status = status_id; //两个表的链接操作 +---------+------------------------+-------------+ | book_id | title | status_name | +---------+------------------------+-------------+ | 100 | Heart of Darkness | inactive | | 101 | The Catcher in the Rye | active | | 102 | My Antonia | active | | 103 | Uncle's Home | inactive | +---------+------------------------+-------------+ 4 rows in set (0.001 sec)
说明：
1、select book_id, title, status_name from books //从books表中选择特定的列们
2、join status_names //指定第二个表
3、where status = status_id //指定查询条件
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引