cool hit counter A beginner's guide to Mysql written for newbies (II)_Intefrankly

A beginner's guide to Mysql written for newbies (II)


Modify data table

  • Field-specific operations

First I create another data table

CREATE TABLE t1(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT,
    -> salary FLOAT(8,2)
    -> );
  • Add field ALTER TABLE tablename ADD newcol_name col_definition [FIRST| AFTER col_name]; Add a new id field to the t1 table you just created, and put that field at the beginning of the field. ALTER TABLE t1 ADD id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST; Add multiple columns,ALTER TABLE table_name ADD (col_name1 col_definition1, col_name2 col_definition2);
  • Delete field ALTER TABLE tablename DROP col_name; Delete the id field you just created again. ALTER TABLE t1 DROP id;
  • Modify column name ALTER TABLE tablename CHANGE id uid col_newdefinition [FIRST| AFTER col_name]; Change the id column name and column definition from earlier ALTER TABLE t4 CHANGE id uid TINYINT UNSIGNED NOT NULL;
  • Modify column definition ALTER TABLE tablename MODIFY col_name col_definition [FIRST| AFTER col_name]; ALTER TABLE t4 CHANGE uid newid;
  • Operations against constraints
    • Add default constraint ALTER TABLE tablename ALTER col_name SET DEFAULT default; Add a default value to the age field of the t1 table

    ALTER TABLE t1 ALTER age SET DEFAULT 20;

    • Delete the default constraint ALTER TABLE tablename ALTER col_name DROP DEFAULT;

    ALTER TABLE tablename ALTER age DROP DEFAULT;

    ALTER TABLE t1 ADD CONSTRAINT newid PRIMARY KEY (newid); newid must not be preceded by a primary key constraint

    • Add unique constraint ALTER TABLE tablename ADD UNIQUE [INDEX| KEY] [index_name] index_type; Adding a unique constraint to username

    ALTER TABLE t1 ADD UNIQUE (username);

    • Add foreign key constraint ALTER TABLE tablename ADD FOREIGN KEY [index_name] (index_col_name,...) reference_definition; Add another pid foreign key constraint to the newid field of the t1 table for the since table

    ALTER TABLE t1 ADD FOREIGN KEY (newid) REFERENCES province (pid);

  • Operations against data tables
    • Renaming of data tables (two ways)
  • Recorded operations Note: Records and data are synonymous in the following
    • Insert data (add)

    Method I.INSERT [INTO] tablename[(col_name1,col_name2...)] {VALUES|VALUE}(col_data1...)[,()] insert Inserting a record is possible by omitting the column name, But the assignment should correspond to the number of columns, If you want to insert multiple records, brackets() Add a comma between(,)。 Note when inserting datadefault harmonyNULL usage, If a field is auto-numbered( self-growth), At the time of insertion, You can useNULL perhapsdefault come in place of, If a field has been setdefault, Re-insertion can be done withdefault to replace the default value of, The system automatically fills in the default values。 Column values can be passed in as values, Expressions or functions, Functions such as password encryptionmd5() function( Hash function to find the string), for examplemd5("123")。 Method II.INSERT [INTO] tablename SET col_name={col_data|DEFAULT} To set the values of the columns of a data directly by set Method three.INSERT [INTO] tablename[(col_name,...)] SELECT ... This method inserts the results of a query from one data table into another specified data table, enabling multiple data insertions

    • Update data (change)

    Single table update: a record (data) can be modified (without conditions), or multiple records (data) can be modified (specify conditions) UPDATE tablename SET col_name1={col_data1|DEFAULT}[,col_name2={col_data2|DEFAULT}]... [WHERE where_condition]

    • Deleting data (delete)

    Single table delete: you can delete one record (data) or multiple (records) DELETE FROM tablename [WHERE where_condition] Note: When data is deleted and then inserted, if the data table field has a self-growing (AUTO_INCREMENT) column, the column will be added from the next value up from the value of the deleted data, rather than filling in the deleted value, as in the use of the self-growing id column

    • Query (select) data (check)

    Query operation is an important operation of database operation, and there are more ways to query, all query SELECT * FROM tablename , Querying data for certain columns( Selecting the order in which certain columns appear by name is going to affect the order of the results, and select the listed nickname It also affects the names in the results) SELECT col_name1,col_name2 FROM tablenameSELECT tablename.col_name1,tablename.col_name2 (This can be used for multi-table queries when two data tables have the same column names) , alias queries SELECT col_name AS col_newname( nickname) FROM tablename (AS can be omitted, but it is recommended that it be added for ease of identification) Query expression. SELECT col_name1[,col_name2...] [ FROM tablename [WHERE where_condition] [GROUP BY {col_name|position(position of query column in statement, not recommended)} [ASC(ascending)| DESC(descending order)],...] [HAVING where_condition] [ORDER BY {col_name|position|exp(expression)} [ASC| DESC], ...] [LIMIT {[offset,]row_count|row_count OFFSET offset}]] For example, the previously used SELECT version(); // View the current version of mysql SELECT now(); // Query the time of the current system This is followed by the function expression WHERE expression: filters the rows, or displays all rows if the WHERE clause is not specified. In WHERE expressions, you can use functions or operators supported by mysql. GROUP BY groups the results of a query, and you can specify the order in which the query results are listed HAVING can specify the conditions for grouping ORDER BY sorts the results of a query, based on one or more columns of criteria LIMIT limits the number of results of a query, noting that the returned results are numbered from 0


Recommended>>
1、Hong Kong bikesharing refund law guarantees full refund of balance and 15 deposit within 10 working days
2、Western Rainbow Market First Rich movie
3、WeChat auxiliary unblock simple operation 5 minutes to get 15 20 red packets
4、Its March 8 Womens Day so happy holidays to all the women in the world
5、A chart to get you started with data analysis

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号