MySQL Learning Summary

Tag:mysql    basic knowledge    

First, the database-based PHP project current dynamic site is based on the database, the site content using the database management system to manage users, columns, pictures, articles, comments are stored in the database such as Xscms library users C    Lumn pic articles commention Two, why use MySQL?    Oracle,db2,sql Server, ... MySQL-----Open source table structure Small and medium project three, MySQL architecture c/s structure 3306 mysql default port mysql DBMS client server side----database----data table- ---(Record, field) four, PHP programmers mainly learn what to do • Design tables for your project • How to use SQL statements and additions to the program can use tools to complete five, MySQL installation and startup and PHP integration under Linux startup S     Ervice mysqld stop service mysqld start service mysqld Restart six, MySQL directory structure configuration file----My.ini Folder bin----command Folder Data-----Store database Create a folder under the Data folder is to create a database seven, understand the database MySQL statement operations DDL definition and Management data Objects (libraries, tables, views, indexes) create D        ROP ALTER Creating library Create database if not EXISTS test Delete library drop database if exists test        CREATE TABLE Databasename.tablename drop table Databasename.tablename DML Data manipulation, and data logging in tables for INSERT UPDATE DELETE INSERT into users (colname,...) VALUES (Colvalue,...);                 Insert table data INSERT into tableName ([Field List]) VALUES (Value List 1), (Value List 2), (Value list 3); Features: If the field list is not given after the table name, the value list lists all the field values, and you must not add single or double quotes in the default order where all of the fields need to be written, but all values use single quotes or double  Quotation marks (recommended, MySQL can be automatically converted) and then insert the data, it is best to give the field list, then the value and the field list object, you can not modify the table data in the order of the fields in the Update table name set field = ' Value ' [, field 2 = ' value ', ...]            The condition condition is to determine the record to change, you can specify one by condition, or you can specify multiple update cats set id=id-1 where ID >= 8; Delete from table name [condition] Note: No conditions will change/delete entire table delete from tablename;//Delete table tr            Uncate tablename;//Delete table, more efficient as long as you want to update, delete, find, as long as you write to the conditions can be exactly found to manage one or more statements DQL SELECT query SELECT [all|distinct]{*|table.*|[ Table.]            Field1[as alias1][other fields]} from table name [where] [GROUP by] [ORDER BY]            [Having] [Limit Count] using the SELECT statement, the purpose is to find the data according to your thoughts, the results returned to you 1, the field to list the field to query 2, you can use as an alias for each field, (keyword conflict, multi-table query) 3,distinct for the entire The query list cancels the same record 4, in the SQL statement, the column using the expression (arithmetic operation symbol, logical operation symbol, condition) 5,where can be logically shipped in the Select,update,delete  Operator (multiple condition combinations) && | |                     ！ The and OR not comparison operator = (<=>) is not the same as in the program,<=> can compare null values, and = can not compare null values, such as name=null, this will not be found, and                     Name<=>null can find out!! = (<>) < >  >= <= is null used in NULL SQL: Between...and continuous value interval not between and like fuzzy query _ and% two wildcard characters _ Any one character% 0 or more characters not as in single retrieve select * FROM Products where                            ID in (5,10,20,25,30); RegExp rlike fuzzy query Regular expression           6, Multi-table query (connection query) 7, nested query Select ... where (select ...                8,order by field [DESC|SAC] Sort desc Reverse Multiple fields query must sort 9,limit num Limit number of queries num1,num2//from NUM1, take num2 10,group by Ziduannanme Count                () sum () avg () max () min () 11,having gives the conditions of each group of conditions of the grouped condition, having the AVG (price) > 50; Having to use DCL Data control after group by, manage permissions and data changes Grant,revoke,commit,rollback using tes1;//go to a database D ESC TableName; View table structure MySQL weakness type, when created using string manipulation, into the database will automatically convert to the corresponding type \s look at the database status show databases see all library show tables see all Tables show variabl es config file variable desc tableName Look at the use of table structure help? Contents 1, execute the SQL statement, connect the database server mysql-h localhost-u Root-ppassword//-p is not rare, not a part of the password show variables;        Some variables show variables like ' part '; Show databases;//View library create database test; Create a library drop databasetest;//Delete Library 2 CREATE DATABASE 3 Select the default database use DatabaseName Create data table 1, what is Data table record + Field 2, create a data table SQL DDL statement                Careate TABLE [IF not Exists]tablename (field name 1 column type [properties] [index] Field name 2 column type [properties] [index]                ...            Field name n column type [properties] [index]) [Table type] [table character set];                The brackets are all optional types 3, data values and types • Numeric integer 1 byte TINYINT 2 bytes SMALLINT 3 bytes Mediumint 4 byte INT 8 byte BIGINT float 4 byte float (m,d) M total number of digits D            Number of decimal digits reserved 8-byte double (m,d) m+2 byte fixed-point number decimal (m,d) • String sound, image, picture, etc. binary data char (m) 255 characters fixed length Vchar (m) 255 characters variable length char fixed length, processing speed block, disadvantage will waste space, Char will remove the end when connected Trailing space Vchar value change large text text data (article) 2e16-1 byte mediumtext 2e24-1 LO    Ngtext Note: varchar text does not support line wrapping, while text type supports newline blobsSave binary, photo, compressed data mediumblob longblob enum enum type 1-2 bytes----65,535 Members Only one value example in an enum can be used at a time: storage enum (' Male ', ' female ') Set collection type 1,2,3,4,8 bytes-----64 members can be used at a time                Multiple values in the collection • DateTime date yyyy-mm-dd time Hh:mm:ss DATETIME            YYYY-MM-DD hh:mm:ss TIMESTAMP yyyymmddhhmmss year YYYY The time stored in the form as shown above must be noted in the format when creating tables it is best not to use these time formats (PHP timestamp 1970-1-1 0:0:0) to save time with integers (PH Time () in P) · Nullphpmyadmin 4, data field Properties unsigned set the unsigned type, you can increase the space by one more than the numeric field Zerofill can only be used in numeric fields, in the numeric Before the data is prefixed, the field is automatically added with unsigned ·                Auto_increment can only be an integer, the value of the data increases automatically by 1 field value is not allowed to repeat this field empty, null,0, will make this field automatically increase 1 If you grow manually, >= maximum value · NULL and NOT null are empty by default, except when the timestamp is converted to a PHP program in the futureData, can the integer sequence have null to be converted to 0?            Can null in a string be converted to 0?--------------------not necessarily recommended: Do not insert null default default values for each field when creating a table: Create a user table CREATE table users (id int unsigned not nullauto_increment primary key, name Varc Har (+) NOT null default ", Height double (10,2) NOT NULL default 0.00, age int. NOT NULL DEF          Ault 0, Sex char (4) NOT null default ' male ');            5, the index is usually created for querying, to ensure the uniqueness of the data 1, the primary key index is to determine the location of a particular record, it is best to define a primary key for each table only one primary key can be specified in a table The value of the primary key cannot be NULL can have multiple candidate indexes primary key specifies primary key 2, and unique indexes can prevent duplicate values from being created per A table can have multiple unique indexes unique effects, in order to prevent duplication of data 1111111111111111111 3, the general index of the most important technology to promote data The performance index optimization of the library takes precedence over the general index 1111111111111111111 22222222222 3333333333333.            ..........       999999999999999999     Total millions of data disadvantage: take up disk space can improve the search speed, but will slow down the data column insert, delete, modify do not try to create an index for each column technique: Alter table TableName Add ColumnName Type attribute sub-table, create index for a table, do not create index for a table need conditional search, need conditional grouping, need condition sort, such column can use regular index            , but not too much, you can use it alone or when creating a table, the index is a standalone object create index indexname on tableName (field); Drop index IndexName on TableName index key is a synonym for multiple columns can be 4, full-text indexed fulltext type index, Myis The AM table type is used on a varchar char text string, and can be used on multiple data columns to generate a list of all the words appearing in one of the data columns in a data table 6, table type and storage location MySQL and most data Library, MySQL has a concept of storage engine MySQL can choose the best storage engine engine data table type for different data engine requirements (query show engines) we only learn 12 of the MyISAM and InnoDB two        A CREATE TABLE () type InnoDB;            CREATE TABLE () engine InnoDB;            MyISAM is the default selection of different storage engines based on different requirements Note: You can specify different table types in a MySQL library (when you create a table) MyISAM mature and stable, easy to manage,     OPTIMIZE table name emphasizes fast read operations some features do not support   InnoDB recovery rollback support for foreign key space consumption is much larger than MyISAM support MyISAM unsupported feature does not support full-text indexing        Feature MyISAM InnoDB transaction processing does not support support for data row locking unsupported foreign KEY constraints are not supported Support table space relatively small relatively large, maximum twice times full-text indexing support does not support 7, the default character set for Chinese characters: G        BK 2 bytes UTF-8 3 bytes MySQL server, database, data table, field can specify character set, relative to other database appears flexible show character set//view database supported character sets            The UTF-8 in the database is not intermediate-that is, the UTF-8 of the database is UTF8 the character set of the MySQL, including the character set, is used to define the way the MySQL store is proofing rules: How to define a comparison string for a rule 1 character sets can correspond to multiple proofing rules 8, modify table is not changed place ALTER TABLE TableName add ColumnName type attribute//Add field to the end of the table alter         Table TableName Add ColumnName Type property after existing column name//enlarge already existing column after Alter table tableName add ColumnName Type property first//add to first column  ALTER TABLE tableName modify sex char (3);//Modify the properties of the column modify modify the type ALTER TABLE tableName change Oldname newname varchar (30);//Can change the field name, MOdify cannot change the name of the field ALTER TABLE TableName Rename as newtablename;//change the name of alter tables TableName drop ColumnName            drop table function String function 1 concat (S1,..., sn);            Connection 2 Insert (Des,start,len,insert);             Start position of Des begins, Len string length replaced with insert 3 lower (str) upper (str) Convert string to lowercase or uppercase 4 left (str,x) right (str,x) Returns the X-character of the left or right of a string, and if x is empty, nothing is returned

MySQL Learning summary