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

The following together to understand the MySQL development should be mastered the sql statement, I believe everyone will benefit a lot after reading, the text in the essence is not much, hope that MySQL development should master the sql statement this short content is what you want.

Create database create database db1; delete database drop database db1; create data table 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)-here 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 Insertion of data rows insert into tb1 (name,age) values ('ax',8); deletion of data in insert into tb12 (name,age) select name,age from tb11; table delete from T1 role truncate table T1 alternative drop table t1delete from tb1 where id > 10delete from tb12 where id > = 2 query of or name='alex' data update update tb1 set name='root' where id > 10 query of data select * from tb;select id,name from tb; table structure view 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 (1meme 5 and 12); select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12; wildcard select * from tb12 where name like "a%" select * from tb12 where name like "a%" select * from tb12 where name like "a _" pagination select * from tb12 limit 10 * from tb12 limit 0L10 select * from tb12 limit 10 select 10 Select * from tb12 limit 20 page-1 10; select * from tb12 limit 10 offset 20 X # page = input ('Please enter the page number you want to view') # page = int (page) # (page-1) * 1 ranking select * from tb12 limit 010; 1 # select * from tb12 limit 10 10; 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 the last 10 pieces of data select * from tb12 order by id desc limit 10; packet select count (id), max (id), part_id from userinfo5 group by part_id There are the following aggregate functions: count max min sum avg****, if the aggregate function results are filtered twice? Must use having * select count (id), part_id from userinfo5 group by part_id having count (id) > 1 position 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 the calculation starts from the beginning 20, and you can see what the current value is with the show create table T1\ G above. Based on session level-view current session value show session variables like 'auto_incre%'-- set session step size set session auto_increment_increment=2;-- start value set session auto_increment_offset=10; based on global settings-view global setting value show global variables like' auto_inc%';-- setting global step size value set global auto_increment_increment=3;-- setting start value set global auto_increment_offset=11

Sql server can be set up on its own when creating a table with a high degree of flexibility.

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, step = 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)-which 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 empty)

Primary key index: constraints cannot be repeated (cannot be empty)

Their characteristic is to speed up the query.

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 Foreign key many-to-many example 1: user table matchmaking 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 Join 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 All display on the right side of department5 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.idleft join userinfo6 on userinfo5.part_id = department5.idselect score.sid Student.sid from scoreleft join student on score.student_id = student.sidleft join course on score.course_id = course.cidleft join class on student.class_id = class.cidleft join teacher on course.teacher_id=teacher.ti

Select count (id) from userinfo5

After reading this article on sql sentences that should be mastered by MySQL developers, many readers will certainly want to know more about it. If you need more industry information, you can 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.