最近发现对于数据库知识已经忘得差不多了,我的工作基本不涉及mysql数据库 年后人心都较为浮躁,因此希望找点事情弥补空虚的灵魂。MySQL数据库以前用过 这里将其拿出来深入研究了一下用文件创建数据库的方法,也许以后用得着。 #####-----------------------
最近发现对于数据库知识已经忘得差不多了,我的工作基本不涉及mysql数据库
年后人心都较为浮躁,因此希望找点事情弥补空虚的灵魂。MySQL数据库以前用过
这里将其拿出来深入研究了一下用文件创建数据库的方法,也许以后用得着。
#####-----------------------mysql数据库开始(一)----------------#####
1、# 创建数据库语句
create database mydb default character set utf8;
# 运用数据库语句
use mydb;
# 创建表格,这里只简单的创建一张表格
# 设置InnoDB主要是为了事务操作的需要
create table mytable(
id int primary key auto_increment,
name varchar(20),
count int not null
)type=InnoDB;
# cmd进入数据库方法
mysql -u root -p
#输入密码进入mysql命令行
#将以上sql语句放到.sql文件中,并导入数据库,这里是windows方式
#注意:这里路径不能添加引号,只需要直接放到source后即可,否则报错
#source $path\mydb.sql
mysql> source E:\mydb.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.09 sec)
#describe mytable或者desc mytable检查表格字段是否有误
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| count | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2、#向表格中导入数据或者导出数据
#首先采用.txt文本导入方式
#按照表格字段进行示例如下:
id name count
1 张三 1000
2 李四 500
3 王老虎 100
#将三组数据复制放到新建txt文本中,这么命名为:mydb.txt
mysql> load data infile 'E:\mydb.txt' into table mytable
-> fields terminated by '\t' #表示字段间距
-> lines terminated by '\n'; #表示行间距
Query OK, 3 rows affected (0.03 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
#由于数据较少这里通过简单的查询可以查看数据导入信息
mysql> select * from mytable;
+----+--------+-------+
| id | name | count |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 500 |
| 3 | 王老虎 | 100 |
+----+--------+-------+
3 rows in set (0.05 sec)
#由于Id字段是自动增加的,所以这里尝试一下不添加字段
#预编写sql语句:load data local infile 'E:\mydb.txt' into table mytable(name,count);
#导入数据为:
赵大 1000
王二小 500
三亚子 100
mysql> load data local infile 'E:\mydb.txt' into table mytable(name,count);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
#查询新数据,这里的方法只适合数据较小的情况
mysql> select * from mytable;
+----+--------+-------+
| id | name | count |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 500 |
| 3 | 王老虎 | 100 |
| 4 | 赵大 | 1000 |
| 5 | 王二小 | 500 |
| 6 | 三亚子 | 100 |
+----+--------+-------+
6 rows in set (0.00 sec)
#可以看到确实增加了三行数据,而且ID自增。
#这里尝试一下将表中数据导出到txt文件中,这里我们有选择的导出数据,如果全部导出与之类似
#下面方式会报错
select * into outfile 'E:\mydb1.txt'
lines terminated by '\r\n'
fields terminated by '\t'
from mytable;
#这个地方有一个奇特的现象fields行必须添加到lines上面即如下操作才不会报错:
mysql> select * into outfile 'E:\mydb1.txt'
-> fields terminated by '\t' #在此例中该行可有可无
-> lines terminated by '\r\n'
-> from mytable ;
Query OK, 6 rows affected (0.00 sec)
#导出数据并不包括表字段。
#####----------------------mysql的分割线----------------------#####
这里先介绍这些,东西不是很多,但是还是相对较为实用。

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download
The most popular open source editor

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment