1、数据库管理
(1)连接/断开数据库
[root@zjx-mysql ~]#mysql -u root -p (-h 127.0.0.1 -P 3306)
Enter password:******
此时已连接上数据库,这将为您提供 mysql> 命令提示符,您可以在其中执行任何 SQL 命令
可以随时在 mysql> 提示符下使用 exit 命令断开与 MySQL 数据库的连接
mysql> exit
Bye
(2)创建/删除数据库
需要特殊权限才能创建或删除 MySQL 数据库。因此,假设您有权访问 root 用户,则可以使用 mysqladmin 二进制文件创建任何数据库。
创建名为 TEST 的数据库的简单示例:
[root@zjx-mysql ~]# mysqladmin -u root -p create TEST
Enter password:
...........
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| TEST |
| information_schema |
| mysql |
| performance_schema |
| sys |
当然,也可以连接上数据库之后在mysql> 命令提示符下进行创建和删除
mysql> create database TEST02;
Query OK, 1 row affected (0.04 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| TEST |
| TEST02 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
删除以上创建的数据库的示例 :
mysql> drop database TEST02;
Query OK, 0 rows affected (0.02 sec)
[root@zjx-mysql ~]# mysqladmin -u root -p drop TEST
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
这将给你一个警告,它将确认你是否真的要删除这个数据库。
Do you really want to drop the 'TEST' database [y/N] y
Database "TEST" dropped
[root@zjx-mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
(3)显示/选择数据库
显示所有数据库及选择使用名为 TEST的数据库的示例 :
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| TEST |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use TEST;
Database changed
mysql>
现在已经选择了 TEST数据库,所有后续操作都将在 TUTORIALS 数据库上执行。
注意:所有数据库名称、表名、表字段名称均区分大小写。因此,在发出任何 SQL 命令时,您必须使用正确的名称。
2、表管理
(1)创建/删除表
表创建命令需要以下详细信息:表的名称、字段的名称、每个字段的定义
mysql> use TEST;
Database changed
mysql> create table tests_tbl(
-> test_id INT NOT NULL AUTO_INCREMENT,
-> test_title VARCHAR(100) NOT NULL,
-> test_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( test_id )
-> );
Query OK, 0 rows affected (0.33 sec)
mysql>
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| test_id | int | NO | PRI | NULL | auto_increment |
| test_title | varchar(100) | NO | | NULL | |
| test_author | varchar(40) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
(2)插入/选择查询
用于将数据插入MySQL表中使用INSERT INTO命令,通用SQL语法:
mysql> INSERT INTO tests_tbl
-> (test_title, test_author, submission_date)
-> VALUES
-> ("Learn MYSQL", "John", NOW());
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> INSERT INTO tests_tbl
-> (test_title, test_author, submission_date)
-> VALUES
-> ("Learn JAVA", "Bob", NOW());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql>
mysql> SELECT * from tests_tbl;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 1 | Learn MYSQL | John | 2023-12-03 |
| 2 | Learn JAVA | Bob | 2023-12-03 |
+---------+-------------+-------------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT test_title,test_author from tests_tbl WHERE test_id=2;
+------------+-------------+
| test_title | test_author |
+------------+-------------+
| Learn JAVA | Bob |
+------------+-------------+
1 row in set (0.00 sec)
mysql>
当然您也可以将 LIKE 子句与 WHERE 子句一起使用,可使用 LIKE 子句代替要签名的等号。当 LIKE 与 % 符号一起使用时,它将像元字符搜索一样工作。可以使用 AND 或 OR 运算符指定多个条件。
将返回 tests_tbl 表中作者姓名以 ohn 结尾的所有记录的示例:
mysql> SELECT * from tests_tbl WHERE test_author LIKE '%ohn';
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 1 | Learn MYSQL | John | 2023-12-03 |
+---------+-------------+-------------+-----------------+
1 row in set (0.00 sec)
mysql>
(3)更新/删除查询
更新UPDATE
用于修改更新MySQL表中的数据,通用的SQL语法, :
UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause]
您可以完全更新一个或多个字段。您可以使用 WHERE 子句指定任何条件。您可以一次更新单个表中的值。
操作示例:
mysql> UPDATE tests_tbl SET test_title = 'Learn C++' WHERE test_id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tests_tbl;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 1 | Learn MYSQL | John | 2023-12-03 |
| 2 | Learn C++ | Bob | 2023-12-03 |
+---------+-------------+-------------+-----------------+
2 rows in set (0.00 sec)
mysql>
删除DELETE
DELETE 命令用于从 MySQL 表中删除数据,通用的 SQL 语法:
DELETE FROM table_name [WHERE Clause]
您可以使用 WHERE 子句指定任何条件。您可以一次删除单个表中的记录。
操作示例:
mysql> INSERT INTO tests_tbl (test_title, test_author, submission_date) VALUES ("Learn JAVA", "Marry", NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from tests_tbl;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 1 | Learn MYSQL | John | 2023-12-03 |
| 2 | Learn C++ | Bob | 2023-12-03 |
| 3 | Learn JAVA | Marry | 2023-12-03 |
+---------+-------------+-------------+-----------------+
3 rows in set (0.00 sec)
mysql> DELETE from tests_tbl WHERE test_id=3;
Query OK, 1 row affected (0.02 sec)
mysql> select * from tests_tbl;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 1 | Learn MYSQL | John | 2023-12-03 |
| 2 | Learn C++ | Bob | 2023-12-03 |
+---------+-------------+-------------+-----------------+
2 rows in set (0.00 sec)
如果未指定 WHERE 子句,则将从给定的 MySQL 表中删除所有记录。
mysql> DELETE from tests_tbl ;
Query OK, 2 rows affected (0.02 sec)
mysql> select * from tests_tbl;
Empty set (0.00 sec)
mysql> INSERT INTO tests_tbl (test_title, test_author, submission_date) VALUES ("Learn MYSQL", "John", NOW());
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> INSERT INTO tests_tbl (test_title, test_author, submission_date) VALUES ("Learn JAVA", "Bob", NOW());
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from tests_tbl;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 4 | Learn MYSQL | John | 2023-12-03 |
| 5 | Learn JAVA | Bob | 2023-12-03 |
+---------+-------------+-------------+-----------------+
2 rows in set (0.00 sec)
mysql>
(4)结果排序
排序ORDER BY
ORDER BY 子句用来对结果集进行排序,该子句命名要排序的列。语法如下:
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
如果列出任何字段,则可以对返回的结果进行排序。
您可以对多个字段的结果进行排序。
您可以使用关键字 ASC 或 DESC 按升序或降序获取结果。默认情况下,它是升序。
您可以使用 WHERE...LIKE子句以通常的方式放置条件。
mysql> select * from tests_tbl ORDER BY test_author ASC;
+---------+-------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+-------------+-------------+-----------------+
| 5 | Learn JAVA | Bob | 2023-12-03 |
| 4 | Learn MYSQL | John | 2023-12-03 |
+---------+-------------+-------------+-----------------+
2 rows in set (0.00 sec)
(验证按升序列出的所有作者姓名)