MySQL usage details-based on individual learning summary

1. Installation configuration 2. Start the MySQL service and configure

Mysql>\s (status is OK) View current server Status View encoding status server Characterset:utf8 Db C Haracterset:utf8 Client CHARACTERSET:GBK Conn. CHARACTERSET:GBK Modified:---Enter configuration file My.iniClient:[MySQL]            default-character-Set=UTF8 service side:[mysqld]            character-Set-Server=Utf8

View status and modify configuration3. Log in/out of MySQL

Login: Common parameters:-U--username=name User Name (note: version 5.5 removed the--username notation)        -p, password-H--Host server name        -P--Port Port Number        -D--database, open the specified databases        --Prompt=name, set the command prompt        --delimiter=name, specifying delimiters        -V--version, output release information and exitcase: a) MySQL-Rroot-p b) MySQL-h127.0.0.1 -Uroot-p c) MySQL-h127.0.0.1 -Uroot-P--PROMPT=ABC (valid for current connection only)d) MySQL-Uroot-Proot-V

Login

    exit:        exit        quit        \q        + C

Exit4.Mysql Common commands:

    SELECT VERSION ();     SELECT Now ();     SELECT USER ();    Modify delimiter DELIMITER     + path    end output log \ t

operation and maintenance of common commands

MySQL statement specification: 1. keyword and function name all uppercase 2. All lowercase 3.SQL statements, such as database names, table names, field names, and so on, must be terminated with a delimiter-ending 4.SQL statement, as long as the word, tag, or quotation mark characters are not split into two parts, you can continue writing on the next line 5. Database name, table name Try not to use MySQL's reserved words, if needed, use the inverse quotation marks (') to enclose the name.

5. Database operations:

A) Create the database:CREATE{DATABASE | SCHEMA}[IF not EXISTS] db_name [[DEFAULT] CHARACTER SET [=]Charset_name]CREATE DATABASETestCHARACTER SET=UTF8 B) View the list of data under the current server: show {DATABASES| SCHEMAget the name of the database that is currently open:SELECT DATABASE();    c) View the warning message from the previous action: show WARNINGS; d) Delete the specified databaseDROP{DATABASE|SCHEMA}[IF EXISTS] db_nameCase:DROP DATABASEDb_aaa1 show DATABASES e) View the definition of the specified database: showCREATE{Dataase|SCHEMA}db_namef) Modify the encoding of the specified database:ALTER{DATABASE|SCHEMA}db_name [DEFAULT]     CHARACTER SET[=]Charset_name g) Open the specified database: Use db_name

Create an equal statementData types in 6.Mysql--specific commonly used to refer to

    Integer type-  -int  --tinyint (1), 0 is false, the rest is true    floating-point type  --float 4 bytes    String Type --CHAR (m)  m bytes, 0<=m<=255  | VARCHAR (m) 0<=m<=65535 l+1 bytes  | ENUM (' value1 ', ' value2 ') 1 or two bytes, depending on the number of enumeration values up to 65,535 values    datetime       year TIMESTAEP binary type video format, Picture path AH et

Data Type7. (i) database table related operations

A. What is a table?

1 A         data table is one of the most important components of a database, and is the basis of other objects 2         data tables are data structures that are stored 3         Data represents  a         Two-dimensional network of data 4 data tables consisting of rows (row) and columns (column) containing a specific entity class 5         The data table must have an existing table structure, and then the data 6         data table has at least one column, can have no row or multiple rows 7         data table Name requirements unique, and do not contain special characters

B. How do I create a data table?

1         CREATE TABLE [IF not EXISTS]Tbl_name (Field Name field type[integrity Constraint conditions]。。。 ) ENGINE=Engine name CHARSET='Encoding Method';2 Integrity constraints:3                         1.PRIMARYKey Primary Key V--unique field, and cannot be duplicated4                         2. Auto_increment Self-growth5                         3.FOREIGN KEYFOREIGN Key6                         4. not NULLNon-empty7                         5.UNIQUE KEYonly8                         6. Default Defaults

C. How can I view data tables and table structures in a database?

1 View the data table under the database: Show TABLES 2 to view the table structure for the specified table: 3 DESC Tbl_name 4 DESCRIBE tbl_name 5  from Tbl_name

D. Creating a table

Case one: Create a news category Cms_cate number, category name, category description

 1  create  table  if  not  exists   Cms_cate ( 2  ID tinyint  ,  3  catename varchar  (50   4  catedesc varchar  (200  ),  5 ) Engine=  MyISAM Charset=  UTF8; 

Code

Case TWO:
Create a news sheet cms_news
Number, news title, news content, press release time, clicks, whether pinned, news category, publisher

1 CREATE TABLEPersons2 (3Id_pint  not NULL,4LastNamevarchar(255) not NULL,5FirstNamevarchar(255),6Addressvarchar(255),7Cityvarchar(255),8 UNIQUE(id_p)9);

Code One

1 CREATE TABLE IF  not EXISTSCms_testnews (2IdINT PRIMARY KEY,3TitleVARCHAR( -)Unique  not NULL,4ContentTEXT   not NULL,5PubtimeINT Unique  not NULL,6IstopTINYINT(1) COMMENT'0 stands for Pail Top, 1 for pinned' 7) ENGINE=InnoDB CHARSET=UTF8;

Code two7. (ii) MySQL storage engine

A. What is a storage engine?

1         the storage engine refers to the type of the table. The storage type of the database determines how the table is stored on the computer. Users can choose the right storage engine based on different storage methods, 2         transaction processing, etc.

B. How can I view MySQL's storage engine?

1 view MySQL-supported storage engines: Show ENGINES \g;2 Engine : Storage engines3 Support: Whether this storage engine is supported4 Comment: Engine features5 transactions: Whether transactional processing is supported6 XA: Whether it is a distributed transaction processing7 savepoints: Whether to support SavePoint8View display supported storage engine information: show VARIABLES like 'have%'9To view the default storage engine: Show VARIABLES like 'Storage_engine'

C.mysql common storage Engine and features:

1InnoDB Storage Engine--after version 5.12Transaction modification:---access to configuration files My, rollback, repair, distributed, multi-version concurrency control things security3 4MyISAM Storage Engine--before version 5.15frm--Storage Table Structure6MyD--Storing Data7Myi--Storage Index8 Memory Storage Engine9Data stored in memory, low security, fast, short life cycle

D. How to choose the right storage engine?

. ....

。。。。。

。。。。 To be continued in!

MySQL usage details-based on individual learning summary