mysql..sql语句入门

最新推荐文章于 2025-06-22 16:52:05 发布

原创最新推荐文章于 2025-06-22 16:52:05 发布 · 173 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了MySQL中的数据加载、查询、删除及表管理等基本操作。涵盖了从外部文件加载数据到表中、执行数据查询、删除特定记录的过程，并展示了如何使用变量、连续插入多条记录以及管理自动增长字段的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

– 从表里面加载数据

mysql> load data local infile '/home/mysqldba/pet.txt' into table pet  lines terminated by '\r\n';

– 查询

mysql> select * from pet;
+--------+--------+----------------------+------+------------+------------+
| name   | owner  | species              | sex  | birth      | death      |
+--------+--------+----------------------+------+------------+------------+
| name   | owner  | species              | s    | 0000-00-00 | 0000-00-00 |
| Fluffy | Harold | cat                  | f    | 1993-02-04 | 0000-00-00 |
| Claws  | Gwen   | cat                  | m    | 1994-03-17 | 0000-00-00 |
| Buffy  | Harold | dog                  | f    | 1989-05-13 | 0000-00-00 |
| Fang   | Benny  | dog                  | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane  | dog                  | m    | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen   | bird                 | f    | 1998-09-11 | 0000-00-00 |
| Whis   | Gwen   | bird            1997 |      | NULL       | NULL       |
| Slim   | Benny  | snake                | m    | 1996-04-29 | 0000-00-00 |
+--------+--------+----------------------+------+------------+------------+
9 rows in set (0.00 sec)



mysql> delete from pet where name='Whis'
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | s    | 0000-00-00 | 0000-00-00 |
| Fluffy | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang   | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
| Slim   | Benny  | snake   | m    | 1996-04-29 | 0000-00-00 |
+--------+--------+---------+------+------------+------------+
8 rows in set (0.01 sec)

mysql>

– 查看表的元信息

mysql> show create table person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

– mysql 可以直接用变量

mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.00 sec)

– 描述表的信息，界面比oracle好看多了，最起码不用自己定义访问方式了

mysql> describe person;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(60)             | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

– 可以连续插入多个值

mysql> INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
    ->             (2000,2,23),(2000,2,23);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

– 测试 AUTO_INCREMENT，估计是封装了oracle的序列吧，当然这是猜测

mysql> CREATE TABLE animals (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> select * from animals;
Empty set (0.01 sec)

mysql> desc animals;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| name  | char(30)     | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
|  7 | cat     |
+----+---------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE animals AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into animals(name) values('bird');
Query OK, 1 row affected (0.01 sec)

mysql> select * from animals;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | dog     |
|   2 | cat     |
|   3 | penguin |
|   4 | lax     |
|   5 | whale   |
|   6 | ostrich |
|   7 | cat     |
| 100 | bird    |
+-----+---------+
8 rows in set (0.00 sec)

mysql> insert into animals(name) values('bird');
Query OK, 1 row affected (0.01 sec)

mysql> select * from animals;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | dog     |
|   2 | cat     |
|   3 | penguin |
|   4 | lax     |
|   5 | whale   |
|   6 | ostrich |
|   7 | cat     |
| 100 | bird    |
| 101 | bird    |
+-----+---------+
9 rows in set (0.00 sec)




mysql> alter table  animals   AUTO_INCREMENT=8388605
    -> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into animals(name) values('bird');
Query OK, 1 row affected (0.01 sec)

mysql> insert into animals(name) values('bird');
Query OK, 1 row affected (0.00 sec)

mysql> insert into animals(name) values('bird');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'
mysql> insert into animals(name) values('bird');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'

查看DDL

MEDIUMINT的 Maximum Value Signed 为8388607

mysql> show create table animals\G;
*************************** 1. row ***************************
       Table: animals
Create Table: CREATE TABLE `animals` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_a_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8388607 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR:
No query specified

–其中BIGINT能支持的位数是最大的

– 查看mysql支持的存储引擎，默认InnoDB ，支持事物

mysql> show engines
    -> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

– 导出 hdp 数据库

[mysqldba@mysql5 ~]$ mysqldump -uroot -h127.0.0.1 -p hdp >hdp.dmp
Enter password:
[mysqldba@mysql5 ~]$ ls
1.sql  db_login.sh  hdp.dmp  pet.txt

– 再导入测试

mysql> drop database hdp;
Query OK, 7 rows affected (0.06 sec)


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

mysql> exit
Bye
[mysqldba@mysql5 ~]$ mysql -u root -h 127.0.0.1  -p hdp <hdp.dmp
Enter password:

–其实dmp文件里面是一些sql语句

–这里只截取一部分

mysqldba@mysql5 ~]$ strings hdp.dmp
-- MySQL dump 10.13  Distrib 5.7.21, for linux-glibc2.12 (x86_64)
-- Host: 127.0.0.1    Database: hdp
-- ------------------------------------------------------
-- Server version   5.7.21
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Table structure for table `animals`
DROP TABLE IF EXISTS `animals`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `animals` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_a_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;