Review common points of knowledge in MySQL
Boot off service mode for win version. Connect mysql command: mysql-u alex-p View all databases: show databases;use db_name; switch database show tables; Show All tables select * from table name; Select Name,age,id from table name; Connection: Default: User rootTo create a user:Create user ' alex ' @ ' 192.168.1.1 ' identified by ' 123123 '; Identified set password create user ' alex ' @ ' 192.168.1.% ' identified by ' 123123 '; % represents any create user ' alex ' @ '% ' identified by ' 123123 '; All IPUser ManagementCreate user ' username ' @ ' IP address ' identified by ' password ', delete user ' username ' @ ' IP address ', modify user rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify password Set password for ' username ' @ ' IP address ' = password (' new password ') PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)AuthorizedPermission Person grant permission Grant Select,insert on Db1.t1 to ' Alex ' @ '% '; Set Alex to view only the T1 table of the DB1 database grant all privileges on Db1.t1 to ' Alex ' @ '% '; All of Alex's privileges except grant can be revoke all privileges from db1.t1 to ' Alex '; Cancel this permission show grants for ' user ' @ ' IP address '--View permissions grant permissions on database. Table to ' user ' @ ' IP address '--authorized revoke permission on number According to the library. Table from ' user ' @ ' IP address '--Cancel permissionsSQL statement RulesAction folder (Operations database)Create database DB2; CREATE DATABASE DB2 default CharSet UTF8; Database default UTF8 show databases; Drop database DB2;Action file (action table)show tables;CREATE TABLE t1 (ID int,name char (10)), creating tables, id,name fields, name limit tencharacters, exceeding the first 10. CREATE TABLE t1 (ID int,name char) default Charset=utf8; table defaults to UTF8CREATE table T1 (#列名, type, NULL,#列名, type, NOT NULL, cannot be empty#列名, type, not Null,default 1, #列名, type, not null,auto_increment, self-increment ID int,name Char (TEN)) Engine=innodb default Charset=utf8; The table defaults to UTF8, and the engine defaults to InnoDBSELECT * from T1; InnoDB supports transactions that can be rolled back and atomically manipulated. MyISAM does not support transaction auto_increment representations: self-increment primary key: Indicates, constraint (cannot be duplicated, and cannot be empty); Accelerated lookup, a table can have only one self-increment column, only one primary key. But the primary key is not necessarily a row. Empty table: delete from T1; Clears the table, adding the self-increment ID of the data from the previous. TRUNCATE table t1; empty tables, self-increment ID start from scratch. Delete tables: drop table T1; View table structure and type: desc tabename; See how the table was created: show create TABLE tablename;Show CREATE TABLE tablename \g; add a \g to remove spacesSelf-increment: Changes the value of the self-increment ID when the next time the data is inserted by a command: ALTER TABLE tablename auto_increment=13; Change the next insertion of the number, the self-increment primary key is 13self-increment step in MySQL
manipulating file contents (action content)Insert data: INSERT INTO T1 (id,name) VALUES (1, ' Egon '); Delete data: delete from T1 where id<6 modified: Update T1 set age=18; Modify the Age column all to 18update T1 set age=18 where age=17; Change the age of 17 to 18 View data: SELECT * from T1;CREATE TABLE t3 (ID int auto_increment primary key,name char (TEN)) default Charset=utf8; Sets the ID as the primary key and self-increment. Insert into t3 (name) VALUES (' abcdef '); So when inserting the data you just need to insert the name field.Data TypeThe number tinyint int unsigned indicates that the field property is not signed when the table is created (such as a minus sign) signed represents a signed bigint above three kinds is the difference of length FLOAT can only represent less accurate decimals, if the length is small, you can use double You can only represent less accurate decimals, and if you have a small length, decimal is very precise, because the internal principle is to store long decimals as strings. Decimal (10,5) 10 represents the total number of digits, and 5 represents a fewThe size range of the numbers supported by bigint is:19-bit, save phone number and so on. signed Range: 9223372036854775808 to 9223372036854775807the number ranges supported by INT are:10-bit,signed Range: 2147483648 to 2147483647unsigned range: 0-4294967295stringChar (Ten)If the character is not full, it is automatically populated to 10 characters, such as padding spacesvarchar (TEN)if not filled, the hard disk will only occupy the actual character length, save space, find speed without char fastchar and varchar can only store up to 255 characterstext can store 65,535 charactersIf more content needs to be written to the file, write the path to the database.Optimization Recommendations: When you create a table, the fixed-length data is put forward, and the long back is placed. Time Type DATETIME enum enum
The equivalent of providing an option when inserting dataa single-choice string data type that is suitable for storing "radio values" in the form interface. When you set an enum, you need to give a "fixed number of options" and store only one of the values. Format enum: enum ("option 1", "Option 2", "option 3",...) In fact, the enum option corresponds to a number, in turn 1,2,3,4,5 ..., with a maximum of 65,535 options, you can use the string format of the option, or you can use the corresponding number.
SET specifies that only any combination of a,b,c,d can be insertedmulti-select string data type, suitable for storing the "multi-select value" of the form interface. When setting the set, it is also necessary to give a "fixed number of options", storing several of these values. Format set: Set ("Option 1", "Option 2", "option 3",...) Similarly, the set of each option value corresponds to a number, in turn 1,2,4,8,16 ..., a maximum of 64 options when used, you can use the SET option of the string itself (multiple options are separated by commas), you can also use multiple options of the sum of numbers (such as: 1+2+4=7) ===== ================================================================================ foreign key CREATE table UserInfo (UID bigint auto_increment primary key, name varchar (+), department_id int, constraintFk_user_depart foreign Key ("department_id",) References department (' id ') #外键约束) Engine=innodb default Charset=utf8; CREATE TABLE Department (IDbigint auto_increment primary Key,title char (.))engine=innodb default Charset=utf8;The name of the foreign key, above the red place can not be the same as other foreign keys, it is recommended to use Table _ table This naming the primary key:
Two ways of writing a primary key. A table can have only one primary key, and the primary key can consist of multiple columns.
MySQL Knowledge point review (i)
