MySQL-(3) Basic Database Operations

MySQL-(3) Basic database Operations 1. Basic database Operations create a database: create database name; // create a database note. By default, in windows, database names and table names are case-insensitive. in linux, database names and table names are case-sensitive, we recommend that you use lower case letters to define the Database Name and table name. Display Database: show databases; // display all databases using the database: use Database Name; // Select Delete Database: drop databases database name; create data table: create [temp] table [if not exits] data table name [(create_definition,…)] [Table_options] [select_statement] the parameter in [] is the optional parameter temp: If this keyword is used, it indicates creating a temporary table ifnot exist: this keyword is used to avoid MySQL error create_definition when the table already exists in the Database: column attribute of the table. When creating a table in Mysql, the table must contain at least one table_option column: description of some of the table's characteristic parameters select_statement: select statement, you can quickly create a table www.2cto.com and define create_definition as follows: col_nametype [not NULL | NULL] [DEFAULT default_value] [auto_increment] [primary key] [reference_definition] col_name: field name type: Field type notNULL | NUL: indicates whether the column is allowed to be empty DEFAULTdefault_vaule: the default value of this column auto_increment: Indicates automatic serial number, A table can be automatically numbered by only one column, and must be indexed with primarykey: indicates whether the column is the primary key referce_definition: add comments to the field Example: usedb_test; createtable tb_admin (idint auto_increment primary key, nicknamevarchar (50) not null, passwordcarchar (50) not null, createtimedatatime ); view the table structure show columns or describe/descshow [full] columns from data table name [from database name]; show [full] columns from data table name. database Name; desc data table name; www.2cto.com rename table name: rename table data table name 1 to data table name 2; Delete table: drop table data table name; 2. select to query the database select select_list // the content to be queried, and select the columns from data table name // Set the database name where primary_constraint // conditions that must be met when querying the database, this is the condition for satisfying rows: group by group_columns // how to group the results order by sorting_columns // how to sort the results having secondary_constraint // The second condition limit count/ /limit the number of output results, for example: select * from tb_test where names = 'chen'; note that if the data in the queried database contains Chinese characters, garbled characters may occur during output, before performing the query operation, run: set names gb2312; // here names is a field in table (1) the query in keyword with the in keyword can determine whether a field is in a specified set. If the field is in the Set, the query conditions are met. Select * from tb_test where names [not] in ('chen', 'lil'); // tb_test is a data table, and names is a field (a column) in the data table) www.2cto.com (2) with the between and keyword select * from tb_test where id [not] between 5 and 15; here, all information about IDs in the range of 5-15 is output. (3) select * from tb_test where names like '% chen_qing %' with the like keyword; two Wildcards are used here. '%' indicates matching one or more characters, it can be a string of any length or 0. '_' indicates that only one character is matched. % Chen_qing % indicates that there can be any number of characters before c, one character between n and q, and multiple characters After g. Author: Chen qingting