最佳实践

概述

参考：

基础操作

显示当前存在哪些数据库

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

通常 MySQL 部署成功后，都有几个默认的数据库

  • information_schema
  • mysql
  • performance_schema
  • sys

创建数据库

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

开始使用数据库

mysql> use menagerie
Database changed

创建表

mysql> create table pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.01 sec)

显示表、查看表信息

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

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

将数据加载到表中

-- 直接从文件中将数据加载表中
mysql> LOAD DATA LOCAL INFILE '/pet.txt' INTO TABLE pet;

-- 使用 SQL 语句一条一条插入数据到表中
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

查询数据

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 |
| Whistler | Gwen   | bird    |      | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | 0000-00-00 |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

删除记录后 ID 不连续问题

使用如下指令重排 ID 即可，${table_name} 换成自己的表名

SET @i=0;
UPDATE `${table_name}` SET `id`=(@i:=@i+1);
ALTER TABLE `${table_name}` AUTO_INCREMENT=0

其他

创建一个 utf8mb4 类型的数据库

create database db2 DEFAULT CHARACTER SET utf8mb4;

创建表

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

为 emp 表添加记录(有 id，name，sex，age 字段)

insert into emp (id,name,sex,age) values(1,‘xiaoming’,’m’,30);

修改 emp 表的内容（第几行第几个字段）

update emp set age=18 where id=4;

批量执行 sql 程序

mysql < hellodb_innodb.sql

备注：也可不进入数据库的情况下查看数据库

mysql -e ‘show databases’


最后修改 December 29, 2024: SQL (4afdc4ea)