Introduction to MySQL Common Statements
This article mainly introduces common statements such as databases, data tables, data types, strings, and time and date. Interested friends can refer to it.
Database
Data table table
Column column
Row
Redundancy
Primary key
Foreign key foreign key
Compound key
Index
Referential integrity
MySQL data type
Three categories: numerical value, date/time and string (character)
Valuable value
TINYINT 1 byte ( 0, 255)
SMALLINT 2 bytes (0, 65 535)
MEDIUMINT 3 bytes
INT or INTEGER 4 bytes BIGINT 8 bytes
FLOAT 4 bytes DOUBLE 8 bytes DECIMAL
Date time
DATE date value
TIME time value or duration
YEAR year value
DATETIME mixed date and time value
TIMESTAMP timestamp
characters String
CHAR 0-255 bytes, VARCHAR 0-65535 bytes
BINARY, VARBINARY, BLOB, TEXT, ENUM and SET
Transactions must meet 4 conditions (ACID):
Atomicity (atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)
1. Atomicity of transactions: A set of transactions either succeeds or is withdrawn.
2. Stability: If there is illegal data (foreign key constraints and the like), the transaction will be withdrawn.
3. Isolation: transactions run independently. If the result of one transaction affects other transactions, the other transactions will be withdrawn.
100% isolation of transactions requires sacrificing speed.
4. Reliability: After the software or hardware crashes, the InnoDB data table driver will use the log file to reconstruct and modify it.
Reliability and high speed cannot have both. The innodb_flush_log_at_trx_commit option determines when to save transactions to the log.
The command is as follows:
mysql> -uroot -p123456 登陆
mysql> grant all on test.* to 'pengshiyu'@'localhost'
-> identified by '123456'; 创建用户
mysql> quit 退出
mysql> show databases; 查看数据库
mysql> create database test; 创建数据库
mysql> create database test charset utf8; 指定字符集支持中文
mysql> show create database test; 查看数据库信息
mysql> drop database test; 删除数据库
mysql> use test; 进入数据库
mysql> create table student(
-> id int auto_increment,
-> name char(32) not null,
-> age int not null,
-> register_data date not null,
-> primary key (id)
-> ); 创建表
mysql> show tables; 查看表
mysql> desc student; 查看表结构
mysql> describe student; 查看表结构
mysql> show columns from student; 查看表结构
mysql> insert into student(name, age, register_data)
-> values('tom', 27, '2018-06-25'); 增加记录
mysql> select * from student; 查询数据
mysql> select * from student\G 按行输出
mysql> select * from student limit 3; 限制查询数量
mysql> select * from student limit 3 offset 5; 丢弃前5条数
mysql> select * from student where id > 3; 条件查询
mysql> select * from student where register_data like "2018-06%"; 模糊查询
mysql> update student set name = 'cxx' where id = 10; 修改
mysql> delete from student where id = 10; 删除
mysql> select * from student order by age; 排序默认ascend
mysql> select * from student order by age desc; 降序descend
mysql> select age,count(*) as num from student group by age; 分组
mysql> select name, sum(age) from student group by name with rollup; 汇总
mysql> select coalesce(name,'sum'), sum(age) from student
-> group by name with rollup; 汇总取别名
mysql> alter table student add sex enum('M','F'); 增加字段
mysql> alter table student drop sex; 删除字段
mysql> alter table student modify sex enum('M','F') not null; 修改字段类型
mysql> alter table student modify sex
-> enum('M','F') not null default 'M'; 设置默认值
mysql> alter table student change sex gender
-> enum('M','F') not null default 'M'; 修改字段名称
mysql> create table study_record(
-> id int not null primary key auto_increment,
-> day int not null,
-> stu_id int not null,
-> constraint fk_student_key foreign key (stu_id) references student(id)
-> );命名外键约束
创建表
mysql> create table A(a int not null);
mysql> create table B(b int not null);
插入数据
mysql> insert into A(a) values (1);
mysql> insert into A(a) values (2);
mysql> insert into A(a) values (3);
mysql> insert into A(a) values (4);
mysql> insert into B(b) values (3);
mysql> insert into B(b) values (4);
mysql> insert into B(b) values (5);
mysql> insert into B(b) values (6);
mysql> insert into B(b) values (7);
交集 内连接
mysql> select * from A inner join B on A.a = B.b;
mysql> select a.*, b.* from A inner join B on A.a = B.b;
差集
mysql> select * from A left join B on A.a =B.b; 左外连接
mysql> select * from A right join B on A.a =B.b; 右外连接
并集
mysql> select * from a left join b on a.a=b.b union
-> select * from a right join b on a.a = b.b; 全连接
mysql> begin; 开始事务
mysql> rollback; 回滚事务
mysql> commit; 提交事务
mysql> show index from student; 查看索引
mysql> create index name_index on student(name(10)); 创建索引
mysql> drop index name_index on student;删除索引Related recommendations:
MySQL statement list: create, authorize, query, Modify
30 tips for optimizing MySQL statements
Several mysql statements commonly used in PHP_PHP tutorial
The above is the detailed content of Introduction to MySQL Common Statements. For more information, please follow other related articles on the PHP Chinese website!
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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1317
25
1268
29
1242
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
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.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
MySQL for Beginners: Getting Started with Database Management
Apr 18, 2025 am 12:10 AM
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
Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module
Apr 18, 2025 am 08:42 AM
When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.
