mysql< >

1 --########## 01, database Overview ##########2 --1, information: All kinds of things that can be touched in the real world3 --2, data: Mapping of information in the computer World (reflection)4 5 --in Java, data can be stored in various containers in memory, but when the computer is powered down (or after the program is closed), the data stored in memory is erased.6 7 in order to solve the problem of data persistence, human beings put forward various solutions, such as: ropes notes, words appear (in stone, tortoise shell, bamboo slips, paper), the Use of computers (single file, file system, database)8 9 --The core problem of the solution:Ten --1, data persistence problem (for example: Use of mind memory will forget) One --2, a large number of data management issues A  - --a database is a warehouse that organizes, stores, and manages data in accordance with its data structure -  the --Database features: - --1. Data sharing - --2, reduce the redundancy of data - --3, ensure the independence of the data + --4. Centralized control of data - --5. Completeness and consistency of data + --6, the data of the fault repair A  at --Classification of databases (based on data type and development history) - --1, Hierarchical data Model-----> Hierarchical Database - --2, web-based data Model-----> Mesh Database - --3, relational data Model-----> relational Database (Theoretical basis: relational algebra) -  - --Mainstream database: in --Relational database: MySQL (Oracle), Oracle (Oracle), SQL Server (Microsoft), DB2 (IBM) - -- Non-relational database (NoSQL): Mongodb, HBase, Cassandra, etc. to  + --MySQL database: - --Features: small size, powerful features, open source free (Enterprise special value), widely used the --The domestic internet giants bat: all have their own custom version for MySQL *  $ --MySQL Database engine: The early MySQL database default engine uses the MyISAM engine, the newer version default engine uses the InnoDB engine insteadPanax Notoginseng --1, MyISAM engine: Query and delete faster, but do not support transactions - --2, InnoDB Engine: Support transaction, is the preferred engine for MySQL as a relational database the  + --Build MySQL database environment: A --1, installation the --2, set + --3, check whether the MySQL service is turned on, enter the command: Services.msc - --4, in the Command line window input: Mysql-uroot-p Set Password $ --If you see Welcome to MySQL Monitor ... The installation and configuration are successful $  - --########## 02, MySQL database syntax ########## - --MySQL database syntax the --MySQL code specification: - --1, SQL statements written in MySQL are semicolon-terminated;Wuyi --2, forcing the keyword in the SQL statement to use uppercase, other lowercase the --3, named, by the name of multiple words, between multiple words using an underscore _ connection -  Wu --SQL language: Structured querying language (structured query Language) - --1, data definition language ddl:create, DROP, ALTER, TRUNCATE About --2, data manipulation language Dml:insert, UPDATE, DELETE $ --3, data query Language Dql:select - --4, Data Control Language Dcl:commit, ROLLBACK -  - --SQL file: File saved as a. sql suffix name A --Comment on SQL statement: Use-two middle horizontal lines, connect the content to be annotated +  the --Data definition language DDL - --For database library understanding: Analogy supermarket, supermarkets have a number of areas, respectively, sell different goods, such as: fresh, fruit, meat and so on $  the --The concept of libraries in MySQL databases: database the  the --View all libraries in MySQL database: (Default four libraries: Information_schema, MySQL, Performance_schema, test) the --Note: These four libraries, the first three are related to the MySQL Data System library, usually do not operate, test this library is left for us to use at random - SHOW DATABASES; in  the --Create a library the CREATE database name; About  the --Delete library the DROP database name; the  + --View the type of engine supported by the MySQL database (the support column in the results of the view column, labeled Yes or default is the supported engine type, labeled No is an unsupported engine type) - SHOW ENGINES \g; the Bayi --Since there can be multiple database libraries in MySQL databases, the first thing you need to do is to explicitly indicate which database library you are using. the the name of the database used to use; the  - --Understanding of the tables in the database's library: Analogy supermarkets, there are several shelves in different areas for placing goods -  the --MySQL database really has a number of tables in a library: table, recall relational database, these tables are actually the embodiment of the relationship the --table features are represented by fields: field. the  the --View all the tables used in this library - SHOW TABLES; the  the --Create a table the --CREATE table table name94 -- ( the --field name 1 data type, the --Field Name 2 data type, the --     ...98 --field name N data type About -- ); - 101 --most commonly used data types102 --INT: integral type103 --VARCHAR (length): Character type104  the --View table structure106 --Notation 1107 DESC table name;108 --Notation 2109 DESCRIBE table name; the 111 --Modify table name the ALTER table Old table name RENAME new table name;113  the --Modifying the data type of a table's field the ALTER table name MODIFY field name data type; the 117 --Modify the field name and data type of the table118 ALTER table name change old field name new data type for new field name;119  - --Add fields (use the first keyword to add the required fields to the front of the table structure, and use the After keyword to add the required fields after the specified field)121 ALTER table name ADD new field name data type first;122 ALTER table name ADD new field name data type after an existing field name;123 124 --delete field the ALTER table name DROP field name;126 127 --Delete table - DROP table name;129  the --########## 03, MySQL basic additions and deletions to check the operation ##########131 --Use the command line to encode SQL statements, inefficient, consider using graphical client tools, such as: MySQL work Bench, Navicat, etc., after repeated comparisons, recommend the use of SQLyog the 133 --Accent notes ': When using special characters (keywords) in MySQL SQL statements, it is common to consider using accent marks, where the keyboard position is generally below the ESC key134 135 CREATE TABLE userinfo136 (137 userid INT,138 username VARCHAR (Ten),139 ' Password ' VARCHAR (Ten) $ );141 142 DESC userinfo;143 144 --1, new data INSERT145 -A: Full Insert form: INSERT into table name (Field 1, Field 2, ..., field N) VALUES (value 1, value 2,..., value n);146 INSERT into UserInfo (userid, username, ' password ') VALUES (1, ' Zhang San ', ' 123 ');147 --B: Shorthand insert form: INSERT into table name values (value 1, value 2,..., value n);148 INSERT into UserInfo VALUES (2, ' John Doe ', ' 456 ');149 --C: Insert multi-line form: Max --Previous version: INSERT into table name values (value 1, value 2,..., value N), insert into table name values (value 11, value,..., value 1n);151 INSERT into UserInfo VALUES (3, ' Harry ', ' 789 '); the INSERT into UserInfo VALUES (4, ' Zhao Liu ', ' 999 ');153 --Subsequent version: INSERT into table name values (value 1, value 2,..., value N), (value 11, value,..., value 1n);154 INSERT into UserInfo VALUES (5, ' xiaoming ', ' 888 '), (6, ' Little Red ', ' 777 ');155 156 --NOTE: null values and empty strings157 --empty string: ', a pair of single quotation marks containing content158 INSERT into UserInfo VALUES (7, ' Xiao Zhang ', ');159 --Null value: null, special value the INSERT into UserInfo VALUES (8, ' Xiao Li ', NULL);161 --The following sentence inserts a string null162 INSERT into UserInfo VALUES (9, ' Xiao Wang ', ' NULL ');163 164 --2, modify data UPDATE165 --A: Modify the value of a single field: UPDATE Table name SET field name = new value WHERE conditional clause; (Note: The scope is bounded by the Where condition)166 UPDATE userinfo SET ' password ' = ' 666 ' WHERE username = ' Zhao Liu ';167 UPDATE userinfo SET ' password ' = ' 999 ' WHERE userid = 4;168 UPDATE userinfo SET ' password ' = ' 666 ' WHERE username = ' Zhao Liu ' and userid = 4;169 --B: Modify the values of multiple fields: UPDATE table name SET field Name 1 = new value 1, field name 2 = new value 2, ..., field name n = new value n WHERE conditional clause; (Note: Pay special attention to the use of commas between multiple field assignments after SET) the UPDATE userinfo SET username = ' Zhao Yun ', ' password ' = ' 999 ' WHERE userid = 4;171 UPDATE userinfo SET username = ' Zhao Liu ', ' password ' = ' 666 ' WHERE username = ' Zhao Yun ';172 UPDATE userinfo SET username = ' Zhao Yun ', ' password ' = ' 999 ' WHERE username = ' Zhao Liu ' and userid = 4;173 174 --3. Deleting data delete TRUNCATE175 --A: Delete the data that satisfies the condition: Delete from table name WHERE conditional clause; (note: bounded by where condition)176 DELETE from userinfo WHERE ' password ' = ' 777 ';177 --B: Delete all data:178 --notation 1: delete:delete from table name without using the WHERE clause;179 DELETE from UserInfo; the --notation 2: Use TRUNCATE table name; (Note: Truncate is often translated as truncation and the data contents of the database tables are linked)181 TRUNCATE TABLE userinfo;182 183 --The simplest query184SELECT * from UserInfo;

mysql< >