Shulou(Shulou.com)06/01 Report--

The following is about what commonly used sentences are used in MySQL. The secret of the text lies in being close to the topic. So, don't talk about gossip, let's read the following directly. I believe you will benefit from reading this article on what are the main common sentences in MySQL.

Database (Database)

Data sheet table

Column column

Line row

redundancy

Primary key primary key

Foreign key foreign key

Compound bond

Indexes

Referential integrity

MySQL data type

Three categories: numeric value, date / time, and string (character)

Numerical value

TINYINT 1 byte (0255)

SMALLINT 2 bytes (0cm65 535)

MEDIUMINT 3 byt

INT or INTEGER 4 bytes BIGINT 8 bytes

FLOAT 4-byte DOUBLE 8-byte DECIMAL

Date and time

DATE date value

TIME time value or duration

YEAR year value

DATETIME mixed date and time valu

TIMESTAMP timestamp

String

CHAR 0-255byte, VARCHAR 0-65535 byte

BINARY, VARBINARY, BLOB, TEXT, ENUM and SET

A transaction must meet four conditions (ACID):

Atomicity (atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)

1. Atomicity of transactions: a set of transactions that either succeed or are withdrawn.

2. Stability: illegal data (foreign key constraints, etc.) and transaction withdrawal.

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 the sacrifice of speed.

4. Reliability: after the software and hardware crash, the InnoDB data table driver will use log files to reconstruct and modify.

You can't have both reliability and high speed, and the innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.

The command is as follows:

Mysql >-uroot-p123456 login to MySQL > grant all on test.* to 'pengshiyu'@'localhost'-> identified by' 123456users; create user mysql > quit exit mysql > show databases; view database mysql > create database test; create database mysql > create database test charset utf8; specified character set support Chinese mysql > show create database test; view database information mysql > drop database test; delete database mysql > use test Enter database 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)->); create table mysql > show tables; view table mysql > desc student; view table structure mysql > describe student; view table structure mysql > show columns from student View the table structure mysql > insert into student (name, age, register_data)-> values ('tom', 27,' 2018-06-25'); add records mysql > select * from student; query data mysql > select * from student\ G output mysql > select * from student limit 3 by row; limit the number of queries mysql > select * from student limit 3 offset 5; discard the first five entries mysql > select * from student where id > 3 Conditional query mysql > select * from student where register_data like "2018-06%"; fuzzy query mysql > update student set name = 'cxx' where id = 10; modify mysql > delete from student where id = 10; delete mysql > select * from student order by age; sort default ascendmysql > select * from student order by age desc; descending descendmysql > select age,count (*) as num from student group by age; grouping mysql > select name, sum (age) from student group by name with rollup Summary mysql > select coalesce (name,'sum'), sum (age) from student-> group by name with rollup; summary alias mysql > alter table student add sex enum ('Manger alter table student drop sex; F'); add fields mysql > alter table student drop sex; delete field mysql > alter table student modify sex enum (' MMJ mysql') not null; modify field type mysql > alter table student modify sex-> enum Set the default value mysql > alter table student change sex gender-> enum ('Mauremending F') not null default' Mask; modify the field name 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)->); name the foreign key constraint to create the table mysql > create table A (aint not null) Mysql > create table B (b int not null); insert data 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 An inner join B on A.a = B.b * MySQL > select A. from An inner join B on A.a = B.b; subtraction mysql > select * from A left join B on A.a = B.b; left outer link mysql > select * from A right join B on A.a = B.b; right outer join union mysql > select * from a left join b on a.a=b.b union-> select * from a right join b on a.a=b.b Fully connected mysql > begin; start transaction mysql > rollback; rollback transaction mysql > commit; commit transaction mysql > show index from student; view index mysql > create index name_index on student (name (10)); create index mysql > drop index name_index on student; delete index

What are the main common sentences related to the above MySQL, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.