Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "the SQL statement that the development of MySQL must know". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn the "MySQL development must be able to SQL statements" it!

Create a database

Create database db1

Delete database

Drop database db1

Create a datasheet

Create table tb1 user table (id int not null auto_increment primary key, name char (10), department_id int, p_id int,) engine=innodb default charset=utf8

Primary key (primary key) A table can have only one primary key, which can consist of one or more columns

Creation of foreign keys

CREATE TABLE T5 (nid int (11) NOT NULL AUTO_INCREMENT, pid int (11) not NULL, num int (11), primary key (nid,pid)-this is where the two columns are set as primary keys) ENGINE=InnoDB DEFAULT CHARSET=utf8 Create table T1 (id int auto_increment primary key, name char (10), id1 int, id2 int, CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES T1 (nid) Pid)-- here is setting the foreign key) engine=innodb default charset=utf8

Operation of data rows

Data insertion

Insert into tb1 (name,age) values ('ax',8); insert into tb12 (name,age) select name,age from tb11

Deletion of data in a table

Delete from T1; truncate table T1; drop table T1 delete from tb1 where id > 10 delete from tb12 where id > = 2 or name='alex'

Update of data

Update tb1 set name='root' where id > 10

Query of data

Select * from tb; select id,name from tb

View of table structure

Show create table T1; desc T1

Other

Select * from tb12 where id! = 1 select * from tb12 where id in; select * from tb12 where id not in; select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12

Wildcard character

Select * from tb12 where name like "a%" select * from tb12 where name like "a _"

Pagination

Select * from tb12 limit 10; select * from tb12 limit 0L10; select * from tb12 limit 10; select * from tb12 limit 20 offset 10; select * from tb12 limit 10 offset 20; # page = input ('Please enter the page number you want to view') # page = int (page) # (page-1) * 10 # select * from tb12 limit 0from tb12 limit 10; 1 # select * from tb12 limit 10 Magiment2

Sort

Select * from tb12 order by id desc; to small select * from tb12 order by id asc; small to large select * from tb12 order by age desc,id desc; take 10 pieces of data select * from tb12 order by id desc limit 10

Grouping

The aggregate functions of select count (id), max (id) and part_id from userinfo5 group by part_id; are as follows: count max min sum avg * if the result of the aggregate function is filtered twice? Must use having * select count (id), part_id from userinfo5 group by part_id having count (id) > 1; select count (id), part_id from userinfo5 where id > 0 group by part_id having count (id) > 1

Self-increment setting

Table self-increment setting

Alter table T1 auto_increment=20;-this means that from the beginning of 20, you can see what the current value is with the show create table T1\ G above.

Based on session level

-- View the current session value show session variables like 'auto_incre%'-- set the session step size set session auto_increment_increment=2;-- set the starting value set session auto_increment_offset=10

Based on global settin

-- View the global setting value show global variables like 'auto_inc%';-- set the global step value set global auto_increment_increment=3;-- set the starting value set global auto_increment_offset=11

Sql server can be set up by itself when creating the table. REATE TABLE T5 (nid int (11) NOT NULL AUTO_INCREMENT,pid int (11) NOT NULL,num int (11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4 with step size = 2 DEFAULT CHARSET=utf8

CREATE TABLE `t6` (

Nid int (11) NOT NULL AUTO_INCREMENT,pid int (11) NOT NULL,num int (11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, step = 20 DEFAULT CHARSET=utf8

Unique index

Create table T1 (id int, num int, xx int, unique qu1 (num, xx)-- it means that the data of the two columns cannot be the same on a row, for example, if both columns are 1 and 1, it is not possible.

Unique index: constraints cannot be repeated (can be null) primary key indexes: constraints cannot be repeated (cannot be empty) they are all characterized by accelerated queries.

Foreign key one-to-one

Create table userinfo1 (id int auto_increment primary key, name char (10), gender char (10), email varchar (64)) engine=innodb default charset=utf8 Create table admin (id int not null auto_increment primary key, username varchar (64) not null, password VARCHAR (64) not null, user_id int not null, unique uq_u1 (user_id) CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1 (id)) engine=innodb default charset=utf8

Many-to-many foreign keys

Example 1: user table dating table example 2: user table host table user host relationship table = "many-to-many create table userinfo2 (id int auto_increment primary key Name char (10), gender char (10), email varchar (64) engine=innodb default charset=utf8 Create table host (id int auto_increment primary key, hostname char (64)) engine=innodb default charset=utf8 Create table user2host (id int auto_increment primary key, userid int not null, hostid int not null, unique uq_user_host (userid,hostid), CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2 (id)) CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host (id)) engine=innodb default charset=utf8

Connected table operation

Select * from userinfo5,department5 select * from userinfo5 Department5 where userinfo5.part_id = department5.id select * from userinfo5 left join department5 on userinfo5.part_id = department5.id select * from department5 left join userinfo5 on userinfo5.part_id = department5.id # userinfo5 all show # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id on the left # department5 shows all on the right select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id when null will appear, one line hides select * from department5 left join userinfo5 on userinfo5.part_id = department5.id left join userinfo6 on userinfo5.part_id = department5.id select score.sid Student.sid from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.ti select count (id) from userinfo5 At this point, I believe that everyone on the "MySQL development must be able to SQL statements" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.