Database module (2 days) SQL basics

Section review:1. Using ATM to elicit DBMS2. MySQL-Service Side-Client3. Communications and communication-Authorized-SQL statements-databases create database db1;?                            Drop database db1; -The data table first creates the TB2 Department table CREATE table TB1 user table (id int notNULL auto_increment primary key, name char (10), department_id int, p_id int, constraint fk_1 foreign key ( department_id,p_id) references TB2 (TID,XID)) engine=innodb default charset=UTF8;                                                        Create a foreign key can write multiple, note that the multi-column inside is a primary key supplement: Primary key A table can have only one primary key primary key can be composed of multiple columns                    Add: Foreign key? CREATE TABLE T5 (nid int (11) not NULL auto_increment, pid Int (11) notNULL, num int (11), primary key (NID,PID) These two columns make up a primary key, a multi-column primary key is not commonly used, but you know the ENGINE=innodb DEFAULT charset=UTF8; CREATE TABLE t6 (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)) engine=innodb default charset=UTF8; -data row INSERT into TB1 (name,age) VALUES ('Alex', 18); Delete fromtb1; Increment column count does not empty TRUNCATE table tb1; self-increment count empty delete fromTB1 where ID > 10Update TB1 set name='Root'where ID > 10Select* fromTB; Select Id,name fromTB; 4for self-add: desc T10;                Show CREATE TABLE T10;                Show CREATE TABLE T10 \g; ALTER TABLE T10 auto_increment=20Modify the self-increment of a table MySQL: self-increment step based on session level: Show session variables like 'auto_inc%'; View global Variables set session auto_increment_increment=2; Set Session Stride#set session auto_increment_offset=10;based on global level: showGlobalVariables like'auto_inc%'; View global variable SetGlobalauto_increment_increment=2; Set Session Stride#set global auto_increment_offset=10;SQL Server: Self-increment step: base table Level: CREATE table ' T5 ' ( ' Nid ' Int (11) not NULL auto_increment, ' pid ' int (11) not NULL, ' num ' int (11DEFAULT 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 (11DEFAULT NULL, PRIMARY KEY (' nid ', ' pid ') ENGINE=innodb auto_increment=4, step =20 DEFAULT charset=UTF8 today's content: 0. Unique index (can be federated Unique index) syntax create TABLE t1 (id int).        :, num int, xx int, unique unique index name (column name, column name), constraint ...) #1 1 1 2 1 2the primary role of the unique index PS: unique: The constraint cannot be duplicated (can be empty) PS: Primary key cannot be duplicated (cannot be null) the action is to speed up the lookup if You can use it later, but for the non-empty limit, you may consider primary keys and constraints .1variants of foreign keys A. User tables and departmental table users:1 Alex 1 2 root 1 3 Egon 2 4 Laoyao 3Department:1Service2Security3PR===A one -to-many B. user table and Blog Table User table:1Alex2Root3Egon4Laoyao Blog table: FK ()+only1/yuanchenqi/4 2/ALEX3714/1 3/asdfasdf/3 4/ffffffff /2 ===>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 notNULL Auto_increment PRIMARY KEY, username varchar (64) notnull, password VARCHAR (64) notnull, user_id int notnull, unique UQ_U1 (user_id), CONSTRAINT fk_admin_u1 FOREIGN key (user_id) Refe Rences userinfo1 (ID)) engine=innodb default charset=UTF8;                C. User table (Lily net) Blind Date record Example 1: User table Blind Date expression Example 2: User table host table User host relationship table==="multi-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 notnull, HostID int notnull, 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; 2. SQL statement data row Operations Supplement CREATE TABLE TB12 (ID int auto_increment PRIMARY key, name varchar ( /c2>32), age int) engine=innodb default charset=UTF8; Add insert into TB11 (name,age) VALUES ('Alex', 12); Single INSERT into TB11 (name,age) VALUES ('Alex', 12), ('Root', 18) multiple insert INTO TB12 (name,age) Select Name,age fromtb11 A table, find it, and put it in another table . Delete delete fromtb12; Delete fromTB12 where ID!=2Delete fromTB12 where ID =2Delete fromTB12 where ID > 2Delete fromTB12 where ID >=2Delete fromTB12 where ID >=2orName='Alex'change update tb12 set name='Alex'where id>12 andName='xx'Update TB12 set name='Alex', age=19 where id>12 andName='xx'additions and deletions to those who are the most to check the more select* fromtb12; Select Id,name fromtb12; Select Id,name fromTB12 where ID > 10orName ='XXX'; Select Id,name as CNAME fromTB12 where ID > 10orName ='XXX'; As alias Select Name,age,11 fromtb12; Additional constants Column 11 other: Select* fromTB12 WHERE id! = 1Select* fromTB12 where IDinch(1,5,12); Select* fromTB12 where ID not inch(1,5,12); Select* fromTB12 where IDinch(SELECT ID fromtb11) where the select can only write a list of select* fromTB12 where ID between 5 and12; Wildcard character: Select* fromTB12 where name like"a%"%represents a task multiple characters (0 or more) Select* fromTB12 where name like"A_"_ refers to a character page: select* fromTB12 Limit 10; Select* fromTB12 Limit 0,10; From 0 onwards fetch 10 Select* fromTB12 Limit 10,10; The front is the start position select* fromTB12 Limit 20,10; Select* fromTB12 Limit offset 20; Starting from 20, fetch 10 pages in conjunction with Python#page = input (' Please enter the page number to view ')                    #page = Int (page)                    #(page-1) * Ten                    #select * from TB12 limit 0,10; 1                    #select * from TB12 limit 10,10;2Sort by: Select* fromtb12 ORDER BY id desc; Big to small select* fromTB12 ORDER by ID ASC; Small to large Select* fromtb12 ORDER by age Desc,id desc; Take 10 Data Select* fromTB12 ORDER BY id desc limit 10;

View Code

Database module (2 days) SQL basics