Mysql1-Basic Knowledge points

Directory 0, MySQL installation and configuration One, the basic concept of the second, basic grammar three, commonly used directives four or four kinds of SQL statements     0, MySQL installation and configuration http://www.cnblogs.com/hikarusun/ archive/2012/04/26/2471039.htmlhttp://jingyan.baidu.com/article/597035521d5de28fc00740e6.htmlhttp:// database.51cto.com/art/201304/387439.htmhttp://yanln.iteye.com/blog/ 2193842 Ports: 3306 Accounts: rootwindowsservicename:mysql56 Visualizer: Navicat for MySQL "very powerful" Start MySQL Service: Right-click My Computer-admin-services and applications-service-mysql57, boot up, if not started, error may occur: Cann ' t connect to MySQL Server on Official localhost Document: General installation of MySQL will have official documents "http://dev.mysql.com/doc/download"     basic concept 1, MySQL is a relational database, consisting of one or more tables. Table: Table header, row, column, key (unique), value 2, MySQL statement execution there are two ways: (1) console execution of a single statement. (2) through the SQL script to complete the operation of the database, the script consists of one or more MySQL statements (SQL statement + extension statement), the script suffix is saved when the name is. sql. 3, MySQL is based on the client-server database. The server stores the management data and deals with the data files directly; The client deals with the user and obtains and operates data through the connection server. The server software is the MySQL DBMS; the client can be the tool that MySQL provides, the scripting language, the Web application development language, the programming language and so on.     Basic Syntax 1, identifier: Name some objects, such as databases, tables, columns, variables, and so on. Under Windows, identifiers are case insensitive and Linux is sensitive. 2, Keywords: such as create, drop, select and so on. Identifiers cannot have duplicate names with keywords. 3, Functions: such as String functions, mathematical functions, date-time functions, search functions, cryptographic functions, information functions. 4. Data type "The following types are not comprehensive and are not guaranteed to be completely accurate with version updates" 1) numeric Type A, integer: TinYint, smallint, mediumint, int, bigint, respectively, have 1, 2, 3, 4, 8 bytes. You can specify the number of digits, such as int (4), and note that the number of 4 digits is not the number of bytes. B, floating-point number: float, double, real, decimal. such as double (20,4), 20 specifies the maximum number of digits, and 4 specifies the precision after the decimal point. 2) Dates and times: date, Time, DateTime, timestamp, year "More about date and time, more information about date and time Types" 3) string type (the decoration after the text and the string represents the number of characters, not the number of bytes) A, string: char, varchar Note that char and varchar can be decorated with a 0-255 after it. For char, this value is the actual size, which is shorter than the length, and is shorter than this. For varchar, this value is the maximum size, truncated compared to this value, and is not populated with a small value. The benefits of varchar: You can save space when you do not know the length of the field, and make it easier to search for comparisons. The number of bytes that a Chinese character occupies is related to encoding: GBK is 2 bytes, UTF-8 is 3 bytes. The advantage of fixed-length strings: MySQL handles fixed-length strings much faster; it is not allowed to index variable-length columns (in doubt). B, Texts: tinytext, Text, Mediumtext, Longtext. Character Range: 255, 65535, 2^24-1, 2^32-1. C, binary (can store pictures, etc.): Tinyblob, Blob, Mediumblob, Longblob. BYTE range: 255, 65535, 2^24-1, 2^32-1. 5, in the MySQL statement, all the spaces are ignored, so cross-line does not matter.    Common command 1, login and exit (the premise of the login command is that the MySQL directory has been added to the path or bin, or in the directory where MySQL is located) mysql -u kismet -p=》会提示输入密码 mysql -u kismet -password ***** mysql -u kismet -h hostname -p/password mysql -u ... -h ... -p/password ... -D databasename mysql -D …… -u kismet -p < test.sql（登录时执行脚本） quit http服务器：/../../httpd -k start/stop/restart2. Create, view, and delete a database create database 数据库名 [其他选项]; show databases;【其中的informa_schema和mysql是自带的数据库】 drop database 数据库名;3, select the database to operate-d;use database name, (can not semicolon) 4, create, view and delete table (1) CREATE TABLE table name (column declaration), such as: CREATE TABLE students (ID int unsigned NOT NULL Auto_increment PRIMARY KEY,........., Tel char (one) null default "-"); (2) Column declaration: Name + type (may have unsigned) + (Null/not null) + (auto_ Increment) + (primary key) + (default+ ... Null/not null: Nullable (the default can be empty) Auto_increment: Applies to an integer column (inserted as null, the value is maximum + 1), only one, and must be indexed; using the auto_increment column, you can specify the insertion value. As long as it has not been used, the subsequent insert increment is based on the specified value; the last_insert_id () function can get the most recently inserted auto_increment value (personal guessing is useful in triggers) PRIMARY key: The column is the primary key, the index columns, the values are unique, Column-Unique default: Specifies defaults, supports only constants, does not support functions, and, for null columns, defaults to NULL if not specified (3) other parts if not exists: if the table exists, it will be an error; To avoid an error, you can change to: create if not exists Table students ... engine: You can add Engine=innodb/myisam at the end (after the parentheses) ... Specify the storage engine (4) Show tables;describe table name "equivalent to: Show columns from table name" (5) drop table name; 5. Inserting data into the table "The Blood and Tears lesson: INSERT statements must specify the corresponding relationship between the insertion value and the column, otherwise the program will crash if the database expands or adjusts the order!" "Insert the full line: If some columns can be automatically generated by MySQL without the need to manually add them, you can set them to null,mysql, such as the self-increment of the primary key, timestamp type, etc., this way is best not to use, because once the table structure changes (such as the Order of changes , add a column), the INSERT statement will fail. Insert a part of a row: Specify the column name, you can insert only part of the data (the other condition is that the column allows null or provide a default value, otherwise it will error), or do not insert multiple rows in sequence: Insert multiple data into a single insert more efficient than insert the retrieved data: does not require column name matching To insert non-duplicated data in order: The test finds that the criterion of repetition or not is primary key, unique index, and the common key can be repeated: the insert operation may be time-consuming relative to the query (especially if there are many indexes that need to be updated) ( Update and delete in the same vein), sometimes to ensure the efficiency of the retrieval, you can reduce the priority of the insertion. insert [into] `user` VALUES(null,‘asdfadsf‘,null,30)#插入完整的行 insert [into ] ' user ' (name,age) Values ( ' hello ' ,45) #插入行的一部分 insert `user`(name,age) values(‘hello‘,45),(‘world‘,400)#插入多行 insert `user`(name,age) select name,age from tmp#插入检索出的数据 insert ignore into …… insert low_priority into#低优先级插入6, Query the table data: see 47, update the table data can be updated multiple columns, separated by commas; You can update new values based on old values in the table (old and new values are not necessarily the same column); The new value can be obtained using a subquery, and the new value can be updated with the Ingore keyword when an error occurs; update user set name=‘zhou‘,age=11 where id=10;#可以有多列，用逗号隔开 update user set age=age+1 where id=1;#根据表中旧值更新新值 update user set age=id+1 where id=10;#旧值与新值不一定是同一列 update user set name=(select name from teacher where id=10) where id=10#新值可以使用子查询获得 update ignore user ……#使用ignore，当更新出现错误时可以继续更新 update user set name =null wherenull 8, delete the data in the table delete from table name where delete condition; Delete from table name;//Clear table data, cannot use delete * Fromo table name, if you want to delete entire table, try not to use Delete, slow, Because it deletes data row by line, using TRUNCATE table to do the same work, but faster (actually deleting the entire table and recreating a table), such as TRUNCATE table ' user ', there is no way to undo the update and delete, so before executing, Be sure to use Select to verify that the conditions of the WHERE clause are being pursued, otherwise the consequences will be unthinkable "9, modify the table itself: should be cautious when creating tables, modify the table to avoid (1) Add a column ALTER TABLE table name add column alias data type (can add other properties) [after insertion position ]; (2) Modify a column ALTER TABLE name change column name the new Name column data type (you can add other attributes), (3) Delete the column ALTER TABLE name drop column names, (4) Rename the table ALTER TABLE name rename a new table name; Renam e table name to new name; (5) Other: Add four or four SQL statements such as foreign keys 1, Ddl:data Definition Language (1) include: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME (2) does not require a commit and therefore cannot be rolled back. 2. Dml:data manipulation language includes: SELECT, INSERT, UPDATE, DELETE, MERGE, call, EXPLAIN PLAN, LOCK TABLE3, Dcl:data Control Language includes: GRANT, REVOKE4, tcl:transaction Control language including: SavePoint, ROLLBACK, SET Transaction

Mysql1-Basic Knowledge points