mysql的基础（表的操作）-CSDN博客

本文链接：https://blog.csdn.net/weixin_55734444/article/details/124303410

这篇博客详细介绍了MySQL的基础操作，包括数据库的创建、查看与删除，表的创建、查询与删除，用户创建与权限管理，以及数据的增删改查和表结构的修改。重点讲解了各种SQL语句的语法和使用场景。

摘要由CSDN通过智能技术生成

文章目录

数据库基础操作

创建数据库

语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;

mysql> create database zj;
Query OK, 1 row affected (0.00 sec)

查看当前实例有哪些数据库

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

删除数据库

语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;

mysql> drop database zj;

表操作

创建表

语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=‘存储引擎类型’

mysql> create table student(id int not null,name varchar(111),age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

查看当前数据库有哪些表

mysql> show tables;
+--------------+
| Tables_in_zj |
+--------------+
| student      |
+--------------+
1 row in set (0.01 sec)

删除表

语法：DROP TABLE [ IF EXISTS ] ‘table_name’

mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)

用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

（‘USERNAME’@‘HOST’）的HOST值可为：

IP地址，如：192.168.160.130
通配符：
    %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
    _：匹配任意单个字符

数据库用户创建

语法：CREATE USER ‘username’@‘host’ [IDENTIFIED BY ‘password’];

创建数据库用户

mysql> create user 'zj'@'127.0.0.1' identified by '090.Com!';
Query OK, 0 rows affected (0.00 sec)

使用新创建的用户和密码登录

[root@localhost ~]# mysql -uzj -p'090.Com!' -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.37 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>

删除数据库用户

语法：DROP USER ‘username’@‘host’;

mysql> drop user 'zj'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

表内容的增删改查

创建表

mysql> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;                  //查看表是否创建成功
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

增加表内容（insert）

语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),
(…),…

mysql> insert student (name,age) values ('zhangsan',12),('lisi',14),('wangwu',15);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看表内容（select）

语法：SELECT column1,column2,… FROM table_name [WHERE clause] [ORDER BY
‘column_name’ [DESC]] [LIMIT [m,]n];

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   12 |
|  2 | lisi     |   14 |
|  3 | wangwu   |   15 |
+----+----------+------+
3 rows in set (0.00 sec)

删除表内容（detele）

语法：DELETE FROM table_name [WHERE clause]

mysql> delete from student where age=15;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   12 |
|  2 | lisi     |   14 |
+----+----------+------+
2 rows in set (0.00 sec)

修改表内容（update）

mysql> update student set age=100 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   12 |
|  2 | lisi     |  100 |
+----+----------+------+
2 rows in set (0.00 sec)

表结构的添加、删除、修改（alter）

新建一个表

mysql> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from test order by id desc;
+----+----------+------+
| id | name     | age  |
+----+--------