数据库基操

最新推荐文章于 2024-09-15 14:50:06 发布

代昌术

最新推荐文章于 2024-09-15 14:50:06 发布

阅读量62

点赞数

文章标签：运维 linux 数据库 mysql

本文链接：https://blog.csdn.net/2301_77698736/article/details/131633948

版权

一、MySQL 中数据库的基本操作

1.查看当前用户、创建用户，并查看所有用户；

mysql> select user();    #查看当前用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> create user user_1;    #创建user_1用户
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;    #查看所有用户
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| user_1           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

2.创建数据库，并查看创建数据库的语句；

mysql> create database mysql_1;    # 创建mysql_1数据库
Query OK, 1 row affected (0.01 sec)

mysql> show create database mysql_1;    #查看创建mysql_1数据库的语句
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| mysql_1  | CREATE DATABASE `mysql_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.使用数据库，并查看当前连接的数据库；

mysql> show databases;    #显示当前MySQL服务器上的所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_1            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use  mysql_1;    #使用mysql_1数据库
Database changed

mysql> select database();    #查看当前连接的数据库
+------------+
| database() |
+------------+
| mysql_1    |
+------------+
1 row in set (0.00 sec)

另：查看当前所连接的数据库的字符集（CHARACTER）和校对规则（COLLATION）；

方法1：

mysql> select @@character_set_database,@@collation_database;    #查看当前连接数据库的字符集（CHARACTER）和校对规则（COLLATION）
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

方法2：用2中查看创建数据库语句的命令查看字符集和校对规则；

4.删除数据库；

mysql> drop database mysql_1;    #删除数据库
Query OK, 0 rows affected (0.01 sec)

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

二、MySQL 中的数据类型

1.数据类型

（1）Text 类型：char，varvhar；tinytext，text，mediumtext，longtext；blob，mediumblob，longblob；enum，set；

（2）Number 类型：tinyint，smallint，mediumint，int，bigint；float，double，decimal；

（3）Date 类型：date，datatime，timestamp，time，year；

2.创建表，并定义所有数据类型；

mysql> create table customer_information(
    -> customer_id int(10) COMMENT '客户id',
    -> customer_gender char COMMENT '性别',
    -> full_name varchar(50) COMMENT '全名',
    -> Personal_profile text COMMENT '个人简介',
    -> work_experience mediumtext COMMENT '工作经历',
    -> description longtext COMMENT '描述',
    -> profile_picture blob COMMENT '个人照片',
    -> large_pictuer mediumblob COMMENT '大头贴',
    -> video_Introduction longblob COMMENT '视频介绍',
    -> degree enum('学士','硕士','博士') COMMENT '学位',
    -> occupation set('java工程师','运维工程师','技术支持') COMMENT '职业',
    -> phone_number tinyint(11) COMMENT  '手机号',
    -> id_number smallint(18) COMMENT '身份证号',
    -> bank_card1 mediumint(20) COMMENT '银行卡号1',
    -> bank_card2 bigint(20) COMMENT '银行账户2',
    -> account_balance1 float(15,2) COMMENT '账户余额1',
    -> account_balance2 double(15,2) COMMENT '账户余额2',
    -> birthday date COMMENT '生日',
    -> brith_data time COMMENT '出生日期',
    -> birth_datatime DATETIME COMMENT '具体出生日期',
    -> birth_year year COMMENT '出生年份'
    -> )engine=InnoDB default character set utf8mb4 collate utf8mb4_0900_ai_ci;
Query OK, 0 rows affected, 7 warnings (0.03 sec)

mysql> desc customer_information;
+--------------------+-------------------------------------------------------+------+-----+---------+-------+
| Field              | Type                                                  | Null | Key | Default | Extra |
+--------------------+-------------------------------------------------------+------+-----+---------+-------+
| customer_id        | int                                                   | YES  |     | NULL    |       |
| customer_gender    | char(1)                                               | YES  |     | NULL    |       |
| full_name          | varchar(50)                                           | YES  |     | NULL    |       |
| Personal_profile   | text                                                  | YES  |     | NULL    |       |
| work_experience    | mediumtext                                            | YES  |     | NULL    |       |
| description        | longtext                                              | YES  |     | NULL    |       |
| profile_picture    | blob                                                  | YES  |     | NULL    |       |
| large_pictuer      | mediumblob                                            | YES  |     | NULL    |       |
| video_Introduction | longblob                                              | YES  |     | NULL    |       |
| degree             | enum('学士','硕士','博士')                              | YES  |     | NULL    |       |
| occupation         | set('java工程师','运维工程师','技术支持')                 | YES  |     | NULL    |       |
| phone_number       | tinyint                                               | YES  |     | NULL    |       |
| id_number          | smallint                                              | YES  |     | NULL    |       |
| bank_card1         | mediumint                                             | YES  |     | NULL    |       |
| bank_card2         | bigint                                                | YES  |     | NULL    |       |
| account_balance1   | float(15,2)                                           | YES  |     | NULL    |       |
| account_balance2   | double(15,2)                                          | YES  |     | NULL    |       |
| birthday           | date                                                  | YES  |     | NULL    |       |
| brith_data         | time                                                  | YES  |     | NULL    |       |
| birth_datatime     | datetime                                              | YES  |     | NULL    |       |
| birth_year         | year                                                  | YES  |     | NULL    |       |
+--------------------+-------------------------------------------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)