MySQL simple use delete and change

Modify the configuration file found in the My.in profile client refers to the MySQL clients Port3306default-charachter-set=utf-8default-charachter-set=utf8basedir and DataDir storage path. There is a server character encoding modified to UTF8 is not UTF-8DEFAULT-CHARACHTER-SET=UTF8 data type: shaping: tinyint 1 bytes signed and unable to store ranges -128~128, 0  ~255smallint 2 bytes mediumint 3 bytes int 4 bytes bigint 8 bytes Float type: floatdouble time storage: year:time:date:datetime:timestamp: fixed length type: 1 bytes 8 bits maximum 8 11111111 =255, char (5): ABC three characters, followed by two spaces Vchar (5) ABC value saved three characters 0~65535tinytexttext ( (Mediumtext) Lingtext (+) Enumset maximum 64 character data table operation float (8,2) indicates that the total lattice has 8 bits, two digits after the decimal point and 6 bits before the decimal point create   Table Tb_name (username varchar), age tinyint unsgined, salary float (8,2)), query data table for presence show tables;show tables from MySQL; View all list query table structures in MySQL: Show columns from username; write record: Insert Intousername (username) VALUES (' Zhangsan '); Find record exists: SELECT * from username; null vs. Non-null: Data uniqueness: auto_increment Auto-numbering must be primary key create Tb_user (SID smallint NOT NULL Auto_crement primary key, Userna Me Vchar (20)); The primary KEY constraint is described earlier, and there is a DEFAULT constraint: Create Tb_Username (SID smallint not NULL auto_increment primary Key,name Vchar (a), age enum (' + ', ' 30 '), constraint: guarantees data integrity and a Sex: Constraints are divided into table constraints, column constraint type non-null primary key unique default foreign KEY constraint MySQL create FOREIGN KEY constraint: Conditional parent table and subclass must have the same engine InnoDB cannot use temporary table character must be the same, length can be different foreign key column and reference column must be created index if the foreign key does not exist In, MySQL will automatically create index edit data table default storage engine MySQL, config file default-storage-engine=innodb create table tables PID smallint, foreign key (PID) reference ' parent table ' (ID) foreign key Yue Su's reference operation: Cascade: The schedule is deleted or updated and automatically deleted or updated, the child table also changes set NULL: Parent table changes, child table is set to NULL restrict: Deny delete to parent table or update Action No action: Standard SQL keyword , the same example in MySQL with restrict demonstrates the setting permissions of the child table: Create user2 (SID smallint NOT NULL primary key Auto_increment,username Vchar (), PID smallint unsgined, foreign key (PID) preference Db_username (ID) on DELETE cascade); View data Table command: Show create table user1; table-level constraint column approx. Bundle add single row ALTER TABLE User1 add age tinyint unsigned NOT null default 10;alter table user1 add age2 tinyint unsigned not null D  Efault after age;  After the newly added column in the Age column, first inserts the structure of the Add multiple columns column without the need to add parentheses to view the table: Show columns from User1; Delete field: ALTER TABLE User1 drop AGS; Delete multiple fields alter TABLE User1 drop Age1,droP Age1; You can also add a column for adding a column modify table to add a PRIMARY KEY constraint ALTER TABLE USER1 add constraint pk_user1_id primary key (ID); ALTER TABLE user1 add constraint PK  _USER1_ID primary key (ID); add UNIQUE constraint: ALTER TABLE user1 add unique (ID); Show create table user1; View table Add FOREIGN KEY constraint: ALTER TABLE user1 add foreign key (ID) reference db_username (ID); Modify field ALTER TABLE User1 age set defalue 15; Delete constraint: ALTER TABLE User1 drop primary key; Delete Unique constraint: View table structure: Show indexes from USER1\G delete foreign key constraint; Insert default value INSERT into User1 values (' Zhangsan ', Default) Insert multiple data: INSERT into User1 values (' Zhangsan ', default), (' null ', 29); Insert Data set demo Insert User1 set username= ' Ben ', Password = ' 123 '; Insert data Select the demo update data has single table and multiple table update user1 set Age=5uodate user1 set age=age-sid,sex=0 all even row data uodate user1  Set age=age-sid,sex=0 where sid%2=0; Query statement: GROUP BY grouping SELECT * from GROUP by 1 1 represents the location of the field generally directly specifying the name having a part of doing a grouping is equivalent to where , you need to ensure that the condition of the grouping is aggregate function Max min avg count or hvaing the condition field must be in the Select field Example: Error Demo: Select Sname from Tb_user GROUP by 1 have age &G  t;3; Age is not in the Select query field, so the error is correctly demonstrated: select Sname,age FRom Tb_user GROUP by 1 have age >3;  This time after it contains the age so correct or use the function select Sname from Tb_user GROUP by 1 have count (ID) >=2; This is also the correct sort query: SELECT * from the user order by ID Desc;select * from the user order by Id,age desc;limit using SELECT * FROM user order  by id DESC limit 2, 2; The first 2 represents the third data start query (0,1,2), the second 2 represents the display two data insert data from another table to get information Insert User2 (username) Select username from user1 where age>=30  ; Set the encoding of the client set name gbk;show columns use1\g; Query all field Details grid query distinct group BY order by limit function, etc. can be in the subquery appear subquery outside the query can be additions and deletions that the check AVG and round select round (AVG (Good_price), 2) From User1;select sname, sage from User1 where good_price>= (select round (AVG (Good_price), 2) from User1); subquery any some All modifier all needs to conform to the two any and some values required to conform to a comparison operator subquery No in subquery any =in! =all Example: Creating tables CREATE table if not exists tb_user (cate_id smallint unsigned NOT null primary key auto_increment, Cage_name var  char () not null); Select Catefrom User1 group by Cate; Query classification inserts a query into the new table data desc tb_user; Query table structure multiple table update: Update tb_1 INNER JOIN tb_2 oN tb_1name =tb_2name set tb_1name=tb_2sid; Modify the name and character type of multiple fields using Changealter table tb_1 change sname sname_id smallint not nu ll, classname classname_id smallint not null; outer links have a distinction between the left and right links

MySQL simple use delete and change