mysql-01

数据库:

          概念:

                存储数据的仓库

                本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。

                所有的关系型数据库都可以使用通用的 SQL 语句进行管理

                DBMS DataBase Management System

        优点:    
               执行效率高
               底层有树结构在里面
               数据库支持 "事务",来解决 业务中存在问题  "转账操作"!
               即使关机了,数据永久保存!

常见数据库

        MySQL：开源免费的数据库，小型的数据库，已经被 Oracle 收购了。

        Oracle：收费的大型数据库，Oracle 公司的产品。

        DB2 ：IBM 公司的数据库产品,收费的。

        SQL Server：MicroSoft 公司收费的中型的数据库。

        SQLite: 嵌入式的小型数据库。

mysql的基本语法:

        安装mysql之后的登录方式:
             1)方式登录 dos窗口登录   
             2)方式登录 mysql自带的客户端登录

C:\Windows\system32>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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>

数据库的DDL语句(数据库的定义语句)之库的操作

        1)查询当前mysql自带的数据库:(注:库在我们电脑磁盘上----> 文件夹)

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

        2)创建库:

                方式1:

create database javaee;
Query OK, 1 row affected (0.79 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| javaee             |
| myee_2203          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

              方式2:

mysql> create database if not exists javase;
Query OK, 1 row affected (0.50 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| javaee             |
| javase             |
| myee_2203          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

        3)查询创建库的字符集

mysql> show create database javaee;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| javaee   | CREATE DATABASE `javaee` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

        4)改库的字符集

mysql> alter database javaee default character set gbk;
Query OK, 1 row affected (0.50 sec)

mysql> show create database javaee;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| javaee   | CREATE DATABASE `javaee` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

        5)删除库

                方式1:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| javaee             |
| javase             |
| myee_2203          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database javaee;
Query OK, 0 rows affected (0.53 sec)

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

                     方式二:

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

mysql> drop database if exists javase;
Query OK, 0 rows affected (0.41 sec)

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

数据库DDL语句(数据库定义语句)之建表,修改表,查询表,删除表...

          1)mysql常见的数据类型
                  int :整数类型默认最大长度11位字符,给int类型的数据的时候,当前存储的值的真实长度

                  varchar(最大支持255个长度):字符串类型

                  date:仅仅是日期类型

                  datetime:日期+时间类型

                  timestap:时间戳

                   double:小数类型 

                    clob:大字符类型          支持 "大文本"

                    blob:大字节类型        最大支持4G

         2)建表:

mysql> use javase;
Database changed
mysql> create table students(
    -> id int,
    -> name varchar(10),
    -> age int,
    -> birthday date,
    -> sex varchar(2),
    -> email varchar(10)
    -> );
Query OK, 0 rows affected (1.44 sec)

        3)查询当前库中有哪些表

mysql> show tables;
+------------------+
| Tables_in_javase |
+------------------+
| students         |
+------------------+
1 row in set (0.11 sec)

        4)查看表的结构 

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| sex      | varchar(2)  | YES  |     | NULL    |       |
| email    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

        5)修改表的字段名称 

mysql> alter table students change sex gender varchar(2);
Query OK, 0 rows affected (1.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| email    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

         6)修改表的字段类型 modify

mysql> alter table students modify email varchar(50);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

        7)修改表:给表中添加一个新的字段

mysql> alter table students add socre double(3,1);
Query OK, 0 rows affected, 1 warning (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| socre    | double(3,1) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

        8)复制一张表

mysql> create table teachers like students;
Query OK, 0 rows affected, 1 warning (3.14 sec)

mysql> show tables;
+------------------+
| Tables_in_javase |
+------------------+
| students         |
| teachers         |
+------------------+
2 rows in set (0.00 sec)

        9)删除表

                方式1:

mysql> drop table students;
Query OK, 0 rows affected (1.41 sec)

mysql> show tables;
+------------------+
| Tables_in_javase |
+------------------+
| teachers         |
+------------------+
1 row in set (0.00 sec)

        方式2:

mysql> drop table if exists teachers;
Query OK, 0 rows affected (1.23 sec)

mysql> show tables;
Empty set (0.00 sec)

        10)删除表单一行:

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table students drop id;
Query OK, 0 rows affected (3.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)