MySQL数据库--学习笔记

MySQL数据库–学习笔记

三月份左右做一个小项目的时候需要学数据库，就做了一些笔记，后来也没有再继续学下去，所以内容可能不完整

1.5命令show / use / select

%%%登录：mysql -uname -ppassword

mysql -uroot -p```````

1、show databases; (显示数据库)

show veriables; show variables like ‘character%’;

2、use命令可以让我们使用数据库

格式 use<数据库名> 例如：use mysql

3、select命令 显示当前连接（选择）的信息 显示当前连接的数据库

select database(); select version(); select user(); select now();

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.27-log |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-03-14 13:54:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

1.6操作数据库（数据库的创建、修改、删除）

1.6.1创建数据库

• 创建数据库是在系统磁盘上分出一块区域用于数据的存储与管理
• 创建数据库SQL的语法格式为：

CREATE DATABASE[IF NOT EXISTS]db_name

mysql> create database if not exists test2
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

[DEFAULT]CHARACTER SET [=] charset_name //指定默认字符集[]表示注释

mysql> create database if not exists text3 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| text3              |
+--------------------+
7 rows in set (0.00 sec)

mysql>

• 基本SQL语法格式为：

CREATE DATABASE database_name; //小写字母也可以

mysql> create database test1
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

1.6.2修改数据库

• 修改数据库的语法格式为：

alter database da_name

[default] character set [=] charset_name

mysql> alter database text3 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use text3
Database changed
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | lat