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!