MySQL get started quickly

MySQL Introduction 1, what is a database? A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than more than 60 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just a way to store and manage data, but to transform it into the kind of data management that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.    The main database is: Sqlserver,mysql,oracle, SQLite, Access, MS SQL Server, etc., this article is mainly about MYSQL2, database management is what use? A. Saving data to a file or memory B. Receive a specific command, and then do the appropriate operation of the file PS: If you have the above management system, you do not have to create files and folders yourself, but directly to the above-mentioned software to carry out the file operation, they collectively referred to as the database management system (DBMS, Database Management system MySQL installation MySQL is an open source relational database management system (RDBMS), which uses the most common database management language-Structured Query Language (SQL) for database management. In WEB applications MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software. To use MySQL, you must have the condition a. Install MySQL server B. Install MySQL client B. "Client" Connection "server Side" C. "Client" sends a command to the "server side MySQL" service to accept the command and perform the appropriate action (additional etc) 1,: HTTP://DEV.MYSQL.COM/DOWNLOADS/MYSQL/2, install Windows installation Please refer to: http://www.cnblogs.com/lonelywolfmoutain/p/4547115. HTML Linux Installation: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html Note: The above two links have a complete installation method, the master is also reference to his installation, After installation mysql.server start MySQL service MySQL operation one, connection database Mysql-u User-p                   Example: Mysql-u root-p Common errors are as follows: Error 2002 (HY000): Can ' t connect to local MySQL server through socket '/tmp/ Mysql.sock ' (2), it means that the MySQL server daemon (Unix) or service (Windows) was not running. Quit connection: Quit or Ctrl+d two, view number Database, create databases, use database to view database: show databases; copy code default database: MySQL-user rights related data test-for user test data inform     Ation_schema-mysql itself schema-related data creation database: Create databases db1 DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # UTF8 Code CREATE DATABASE db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # GBK encoding using the database: use DB1; Copy code shows all tables in the currently used database: show TABLES; third, user management copy code create user ' user name ' @ ' IP address ' identified by ' password '; delete Users drop user ' username ' @ ' IP address ', modify user rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify the password set password for ' username ' @ ' IP address ' = password (' new password ') Copy the Code Note: User rights related data is stored in the MySQL database user table, so you can also directly manipulate it (not recommended) four, Rights Management MySQL has the following limitations for permissions: Copy code all privileges all permissions except grant select onlyCheck Permissions Select,insert check and insert Permissions ... usage no access permission alter                  Use ALTER TABLE ALTER routine to use ALTER procedure and drop procedure create  Using the CREATE table create routine using the CREATE procedure create temporary tables using the Create            Temporary tables create user uses the Create user, drop user, rename user, and revoke all privileges Create view using the CREATE VIEW delete using the Delete drop using the DR OP table execute uses call and stored procedure file using SELECT INTO outfile and load da                   TA infile grant OPTION use the index insert with GRANT and REVOKE index Using the lock table process with the Insert lock tables use show full Processlist sElect using select Show databases using show databases show view using S                How view update uses the flush shutdown with update reload Use mysqladmin shutdown (off MySQL) Super???? Use change master, kill, logs, purge, master, and set global. also allows mysqladmin????????            Debug login Replication Client server location access replication slave by replication Slave use copy code for database and other internal permissions as follows:                Database name. * All database names in the database. The table specifies a table database name in the database. Stored procedures in the specified database for stored procedures *. *             The user and IP permissions for all databases are as follows: User name @ip address user can only access user name under Change IP @192.168.1.% user can only access under IP segment (wildcard% = any) User name @% user can access under any IP (default IP address is%) 1, view permissions: Show grants for ' user ' @ ' IP address ' 2, grant grant permissions on database. Table to ' user ' @ ' I P Address ' 3, revoke permissions on the database. The table from ' user ' @ ' IP address ' authorization instance is as follows: Copy code grant all privileges on DB1.TB1 to ' user name ' @ ' IP ' grant select on Db1.* to ' username ' @ ' IP ' Grant select,insert on * * to ' user name ' @ ' IP ' revoke select on DB1.TB1 from ' username ' @ ' ip ' copy code mysql table operation 1, view table show t                    Ables             # View database All tables select * from table name;  # View all contents of the table 2, CREATE TABLE table name (whether the column name type can be empty, whether the column name type can be empty) Engine=innodb DEFAULT Charset=utf8 to an instance. CREATE table ' Tab1 ' (' nid ' Int (one) not NULL auto_increment, # NOT NULL means cannot be null, auto_increment represents self-increment ' name ' varchar (25                                      5) Default Zhangyanlin, # defaults means ' email ' varchar (255), PRIMARY KEY (' nid ') # Set the Nid column as the primary key) Engine=innodb default Charset=utf8; Note: defaults, you can specify a default value when you create a column, and if you do not actively set it when inserting data, automatically add a default value increment if you set it for a column Self-increment column, you do not have to set this column when inserting data. The default will be self-increment (only one self-increment in the table) Note: 1, for self-increment column, must be index (with primary key) 2, for self-increment you can set the step and start value primary key, a special unique index, do not allow null value, if the primary key uses a single column, its value must be unique, In the case of multiple columns, the combination must be unique.  3. drop table Table name 3, clear table contents Delete from table name TRUNCATE TABLE name 4, modify table Copy code add column: ALTER TABLE table name add column name type Delete column: ALTER TABLE name drop  Column Column name modify columns: ALTER TABLE table name modify column name type;        --Type  ALTER TABLE name change original column name new column name type;          --Column name, type add PRIMARY key: ALTER TABLE name add primary key (column name); Delete primary key: ALTER TABLE name drop PRIMARY key;  ALTER TABLE name modify column name int, drop primary key; Add foreign key: ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop F Oreign key FOREIGN Key name modifies default value: ALTER TABLE TESTALTER_TBL alter I SET default 1000; Delete defaults: ALTER TABLE TESTALTER_TBL alter I DROP DEF Ault; Copying code for these operations is not looking very troublesome, it is a waste of time, don't panic! Have specialized software can provide these functions, operation is very simple, this software name is called Navicat Premium, everyone in the online download, practice practiced hand, but the following is about the table content operation or suggest to write the command to do 5, basic data type MySQL data type roughly divided into: numerical            , time and string copy code bit[(m)] bits (101001), M for length of bits (1-64), default M=1 tinyint[(m)] [unsigned] [Zerofill]            A small integer that holds a range of integer numeric ranges: Signed: 128 ~ 127.        Unsigned: 0 ~ 255 Special: MySQL has no boolean value and is constructed using tinyint (1). int[(M)][unsigned][zerofill] integer, data type is used to hold some range of integer numeric ranges: Signed: 2147483648 ~ 21474 83647 unsigned: 0 ~ 4294967295 Special: M in integer type is only for display and has no limit on storage range.                For example: Int (5), when inserting data 2 o'clock, the data is displayed as: 00002 bigint[(m)][unsigned][zerofill] Large integer, data type is used to hold some range of integer numeric ranges: Signed:-9223372036854775808 ~ 9223372036854775807 unsigned: 0 ~ 18 446744073709551615 decimal[(m[,d]) [unsigned] [Zerofill] Accurate decimal values, M is the total number of digits (minus sign), D is the number of decimal points.            The M maximum value is 65,d maximum of 30.        Special: The reason why this type of decaimal is needed for accurate numerical calculations is that it stores the exact value internally as a string.                float[(m,d)] [UNSIGNED] [Zerofill] single-precision floating-point number (not accurate decimal value), M is the sum of numbers, D is the number after the decimal point. Unsigned: -3.402823466E+38 to-1.175494351e-38, 0 1.175494351E-38 to 3 .402823466E+38 signed: 0 1.175494351E-38 to 3.402823466E+38 *     The larger the value, the less accurate * * * * * * * * double[(M,D)] [UNSIGNED] [Zerofill] double-precision floating-point number (not accurate decimal value), M is the total numbers, D is the number after the decimal point.           Unsigned: -1.7976931348623157E+308 to-2.2250738585072014e-308 0 2.2250738585072014E-308 to 1.7976931348623157E+308 signed: 0 2.225 0738585072014E-308 to 1.7976931348623157E+308 * * * * * * * The larger the value, the less accurate * * * CHAR (m) char data type is used to denote fixed-length words can contain up to 255 characters in a string.            where m represents the length of the string. PS: Even if the data is less than m length, the M-length varchar (m) Varchars data type is used for variable-length strings and can contain up to 255 characters.            where m represents the maximum length of a string that is allowed to be saved by the data type, as long as a string that is less than the maximum value can be saved in that data type. Note: Although varchar is more flexible to use, the char data type can be processed faster and sometimes more than 50% of the varchar processing speed from the overall system performance perspective.        Therefore, users in the design of the database should be comprehensive consideration of various factors, in order to achieve the best balance text text data type used to save the large string of long, can be set to more than 65535 (2**16−1) characters.        Mediumtext a TEXT column with A maximum length of 16,777,215 (2**24−1) characters.        Longtext a TEXT column with A maximum length of 4,294,967,295 or 4GB (2**32−1) characters. Enum enum type, an enumColumn can have a maximum of 65,535 distinct elements.            (The practical limit is less than 3000.) Example: CREATE TABLE Shirts (name VARCHAR (+), size ENUM (' X-small ', ' SMA                ll ', ' Medium ', ' large ', ' x-large '));        INSERT into shirts (name, size) VALUES (' Dress shirt ', ' large '), (' T-shirt ', ' Medium '), (' Polo shirt ', ' small ');            Set set Type a set column can have a maximum of distinct members.                Example: CREATE TABLE myset (Col SET (' A ', ' B ', ' C ', ' d '));        INSERT into MySet (col) VALUES (' A,d '), (' D,a '), (' A,d,a '), (' A,d,d '), (' d,a,d ');            DATE Yyyy-mm-dd (1000-01-01/9999-12-31) Time HH:MM:SS (' -838:59:59 '/' 838:59:59 ') year        YYYY (1901/2155) DATETIME yyyy-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP YYYYMMDD HHMMSS (1970-01-01 00:00:00/2037 year) copy generationCode MySQL table content Operation table content operation is nothing more than add and change, of course, with the most or check, and check this piece of things, the most difficult to use, of course, for the great God that is so easy, for me this small white or very difficult to use flexibly, the following we come one by one operation 1, add insert into table (column name, column name ...) values (value, value,...) Insert into table (column name, column name ...) values (value, Value,...), (value, value, Value ...) Insert into table (column name, column name ...) select (column name, column name ...) from table example: INSERT into TAB1 (name,email) VALUES (' Zhangyanlin ', ' [Email prot   Ected] ') 2, delete delete from table # Remove table all data delete from table where id=1 and name= ' Zhangyanlin ' # Delete id = 1 and name= ' Zhangyanlin ' that row of data 3, change Update table set name = ' Zhangyanlin ' where id>14, check select * FROM table SELECT * FROM table whe  Re ID > 1select nid,name,gender as GG from table where ID > 1 look at the condition of this block too much, I'll give you a list. As for the combination, we have to see the level of understanding ha a, condition judgment where select * FROM    Table where ID > 1 and name! = ' Aylin ' and num = 12;    SELECT * FROM table where ID between 5 and 16; SELECT * FROM table where ID in (11,22,33) select * FROM table where ID not in (11,22,33) select * FROM table where ID in (Selec  T nid from table) b, wildcard like a select * from table where name like ' zhang% ' # Zhang starts all (multiple strings)  SELECT * FROM table where name like ' Zhang_ ' # Zhang starts all (one character) C, limits the limit select * from table limit 5;          -First 5 lines select * FROM table limit 4, 5;              -5 rows starting from line 4th select * FROM table Limit 5 offset 4-5 rows starting from 4th Line D, sort asc,desc select * FROM table ORDER BY column ASC    -Rank from small to large according to "column" SELECT * from table ORDER BY column desc-rank from largest to smallest according to "column" SELECT * from Table order BY column 1 desc, column 2 ASC -Rank from large to small according to "column 1", if same, sort by column 2 from small to large e, GROUP by copy code Select Num FROM table GROUP by NUM Select Num,nid from table group by Nu M,nid Select Num,nid from table where nid > Group by num,nid Order Nid desc Select num,nid,count (*), SUM (score), MA X (Score), Min (score) from table GROUP by Num,nid Select Num from table GROUP by NUM have max (ID) > 10 Special: Group by must be in W Here, before ORDER by reprint: Http://www.cnblogs.com/aylin Copy Code

MySQL get started quickly