MySQL Database basics

MySQL Database Basics

1. Database system

    The database system is a kind of system that manages the array resources specially, the database stores one or more groups of processed data, the software that manages this database becomes the database management system.

  Composition: Database  (database) data  Managment Management database system  ? MySQL database function:?    1) lasting retention?   2) Easy to query and extract the data to meet the criteria, data access speed fast? 3) Handle concurrent access? 4) Rights Management   

2. Database System Classification:

  relational database: MySQL, SQL Server, Oracle, DB2, Informix, sysbase  non-relational database: Redis, Mogodb

3. Databases commonly used in development

  ibm:db2 Oracle  : Oracle  MySQL  microsoft:sql Server Access and more

4. What is the status of the database in the Dynamic Web site?

  1, Dynamic Web site is the operation of the data.  When you browse the website, you will find that the content of the page changes, and the layout body is unchanged.  2, the development of web systems are inseparable from the database, the so-called Dynamic Web site is based on database development system, the most important thing is to write the program around the database (business logic).

What is the difference between 5.MySQL and MySQL:

  MySQL refers to a complete database system.  MySQL refers to a client program called MySQL.

Structure of the 6.MySQL database:

  fields, data table, database (multiple fields consist of one row of data)

  Database: The folder database where the information is stored  has an Excel file (datasheet)? Data  in the data table is the data row and data column composition?     a row of data that you see is made up of one or more fields

7.SQL: To interact with MySQL, you need to interact with a language called SQL (Structured Query language). SQL is today's standard database language, and many databases use SQL as the Interactive language: SQL Server, Oracle

  Data Definition language (DDL): Define and manage data objects, such as establishing a database, data table  Data Manipulation Language (DML): used to manipulate data contained in database objects.    database Query Language (DQL): Used to query the data contained in a database object and to make a query on the table.  Data Control Language (DCL): Managing the language of a database

8. Steps to connect to a database

  1, connect MySQL server  2, select Database  3, the data table for the increase and deletion check.  4. Close the database

9. Connect to the database

  MySQL  -u user name root  -p password  -H host name  -p port number 3306  database syntax features:  mysql>  1, each SQL command is used to complete a semicolon      Yes.        2. mysql requires that you continue to enter commands.        3, if there is a relatively long command I can split into multiple lines to execute.        4, ' > ' waits for the next line, waiting for the end of the string to start with a single quotation mark  5, ' > for the next line, waiting for the end of the string to begin with double quotation marks.  6, the query command is not case-sensitive, usually use uppercase letters to write out the SQL keyword and function name, write down the database, data table and data column names in lowercase letters

10. Exit MySQL

  quit  exit  \q? Common operations  \c Cancel command input  \g instead of Terminator;  \s View server-side information   \h View Help

11. Create a Database

Format:    CREATE database if not EXISTS DB name default CharSet UTF8;?  Note:  1, the database is unique  2, if not exists first to determine whether the database exists, there is no creation, does not exist on the creation.   3. Create the database and set the encoding set to UTF8 

12. Displays all database names under the current database server

show databases;

Use  database name  Select database  Note: The  database names under Windows are case insensitive and are strictly differentiated under Linux.

13. Deleting a database

Drop database name

View selected Databases select database (); View the version number of the current database select version     ();

14. Operation of the data sheet

Show tables view the data tables in the database.

Create data table     format: Create TABLE        table name (some information for the column);?     Example:     CREATE TABLE t1 (id int (), name varchar (100)); CREATE table ' Test T1 ' (id int (ten), name varchar (50));   \g formatted output (text type, erect display)?   drop table data table name? The drop table [if exists] data table name is attempted to delete tables. 

15. Record operation increase, delete, change, check

(1) Inserting data insert into             table name (Field 1, Field 2, Field 3) VALUES (value 1, value 2, value 3);? Insert into table name (Field 1, Field 2, Field 3) values (a value 1,a value 2,a value 3);? (2) Query table data         format: Update table name Set field = a value where condition;? Update table name set field 1= value 1, field 2= value 2 where condition;? Update table name Set field = field + value where condition;? (4) Delete the data delete from table name where field = a value;? Delete from table name; (used cautiously)? Delete from table name where field = value;     

Clears table data truncate the table         name;

16. Modify User Password

    In the case of exiting the MySQL service     , enter 1, mysqladmin-u user name-p password New password     Enter the old password:?     2. Log in to MySQL using Set password for ' username ' @ ' login host ' =password (' new password ');  

17. Modify the table's field information

Modify the character set of a table  ALTER TABLE name CharSet UTF8 Modify the field's Type  ALTER TABLE table name modify field FirstName type Modify field name and modify field type at the same time  ALTER TABLE Table name change old field name new field name segment type Modify field character set  ALTER TABLE table name modify field Name Type CharSet UTF8 add new field  ALTER TABLE table name add field name Type Delete Field  ALTER TABLE name drop field name Modify tables name the  old table name rename as new table name

18. Delete the default anonymous account

Why I entered MySQL directly can also enter the database. is an anonymous user, MySQL is created by default. He has the permission to test and information_schema these two libraries. We can erase him.

  Delete anonymous users: Drop user ' @ ' localhost ';

Data types for 19.MySQL databases:

MySQL data types are divided into four categories: numeric type, String type, date type, NULL.

1 Numeric type:*tinyint (1 bytes) 0~255-128~127SmallInt (2 bytes)Mediumint (3 bytes)*int (4 bytes)BigInt (8 bytes)*float (4 bytes) float (6,2) float (m,d)*double (8 bytes) Double (m,d)Decimal (custom) string-shaped numeric decimal (m,d)      M Precision D Scale2 String typeNormal string*char Fixed length stringCHAR (8)*varchar variable string varchar (8) Binary typeTinyblobBlobMediumblobLongblob Text typeTinytext*text commonly used in <textarea></textarea>MediumtextLongtext *enum EnumerationSet Set3 Time and Date type:Date Month DayTime seconds and minutesDateTime Month Day Time minuteTimestamp time stampYear? 4 null valueNull means "No value" or "Unknown value"You can test whether a value is nullCannot perform arithmetic calculations on null valuesAn arithmetic operation on a null value, the result of which is still null 0 or null all mean false, the rest of the values mean true? MySQL operator:  arithmetic operator: +-*/%  comparison operator: = > < >= <= <>! =  database-specific comparisons: In,not in, was Null,is not null,like, between and  logical operators: And OR Not? like: Supports special symbols% and _; Where% represents any number of arbitrary characters, _ represents any one character. table field constraints:  unsigned   unsigned (positive)  Zerofill   Leading 0 padding  Default   default  NOT NULL   non-empty  auto_increment this constraint can be exploited when a unique identifier or sequential value is generated. This constraint can only be used for integer types, and values typically start at 1. When you insert a null into a auto_increment column for each row plus 1, MySQL inserts a value that is greater than the maximum value +1 that has occurred. Can only have one auto_increment column in a table, and must be defined as primary key or unique to be able to use?  PRIMARY key primary key index (non-null not duplicates)  unique   Unique index   (can be null but not duplicated)  index   General index            

19. Build Table Statement Format:

CREATE TABLE table name (field name Type [Field constraint], field name type [Field constraint], field name type [Field constraint] ...  ); Example:    mysql> CREATE table stu (    ID int unsigned NOT NULL auto_increment primary key,    N Ame varchar (8) NOT null unique,--age    tinyint unsigned,---    sex enum (' m ', ' W ') not null default ' m ', ClassID char (6) ;      

20. Inserting data

1 inserting the specified field insert into     T1 (field 1, Field 2, Field 3 ...) VALUES (' Value 1 ', ' Value 2 ', ' Value 3 ',....); 2 Insert all fields insert into     T1 values (' value 1 ', ' Value 2 ', ' Value 3 ',....); 3 inserting multiple data inserts into     T1 (field 1, Field 2, Field 3 ...) VALUES (' Value 1 ', ' Value 2 '), (' Value 1 ', ' Value 2 ') ....; 4 inserting results insert into     T1 (field 1, Field 2, Field 3 ...) Select field 1, Field 2, Field 3 ... from T1;     INSERT into T1 () SELECT * FROM T1;5 inserts a single piece of data into T1 set field 1= value 1, field 2= value 2 ...;     

MySQL Database basics