https://blog.csdn.net/weixin_46047285/article/details/104717199
Meet you!
# 1. MySQL person
The definition of # # 1.1 database
DataBase (DataBase * * * *, * * DB * *), is a by data structure to ` ` store and manage data computer software system, each DataBase has one or more different API is used to create, access, management, search and copy the saved data,
Concept of * * * * : stored in a computer for a long time, organized, a collection of large amounts of data can be Shared, it is a data "warehouse"
* * * * used to save, and safety management data (such as: add and delete etc.), reduce redundant...
# # MySQL 1.2 introduction
MySQL is the most popular Relational Database Management System, * * * * MySQL in WEB applications is one of the best RDBMS (Relational Database Management System: * * * *) Relational Database Management System application software, one of the
Concept: * * * * is * * now popular open source of * * * * * *, * * free * * * * relational database
* *
History: * * * * developed by Swedish MySQL AB company, belong to product of Oracle's at present,
Features: * * * *
- free, open source database- small, fully functional
- easy to use
- can be run on Windows or Linux operating system
- can be applied to small and medium-sized and even large web application
Website: * * * * * * * *
https://www.mysql.com/
RDBMS is a Relational Database Management System (Relational Database Management System) features:
- 1. The data in the form of table
- 2. Every behavior records name
- 3. Each as a recording of data domain name corresponds to the
- 4. Number of rows and columns of a form
- 5. Some form of database
# # 1.3 relational database
(SQL)
- MySQL, Oracle, SQL Server, SQLite, DB2,...
* * - relational database by foreign key link to establish the relationship between the table and table * *
# # 1.4 non-relational database
(no ()
- Redis, directing,...
- * * index which was usually non-relational database objects stored in the database in the form of * *, * * through each object and the relationship between the object's own properties to decide * *
# # 1.5 connect to the database
Open the MySQL command window
- in the DOS command line window * * * *
- in a window into
* * \ mysql installation directory \ bin * *
- to set the environment variable
Connect to the database statement: * * * * ` mysql - h - u user name server host address - p user password `
Note: -p behind can not add Spaces, otherwise will be as the content of the password, login failures!
` ` ` SQL
Update the user set the password=password (' 123456 ') where the user='root'; Change passwords
Flush privileges; Refresh the database
Show the databases; Show all database
Use dbname; Open a database
Show tables; Show all the tables in the mysql database
Describe the user; Display user in table mysql database table column information
The create database name. Create the database
Use databasename; Select the database
exit; Exit the Mysql
? Command key words: seeking help
# said annotation
` ` `
# # # (1) structured query SQL
Structured query classification:
The name of the | | | command |
| -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - |
| | DDL data definition language to define and manage the data object, such as database, data table | CREATE, DROP the ALTER |
| | DML data manipulation language for manipulating objects contain data | INSERT, UPDATE the DELETE |
| | DQL data query language to query data table structure | SELECT |
| | DCL data control language used to manage database language, including administrative authority and data changes | GRANT COMMIT ROLLBACK |
# # # (2) command line operations database
Create a database: create database (if not the exists] database name;
Delete the database: drop the database [if exists] database name;
Check the database: show the databases;
Using the database: use the database name;
` ` `
The CREATE DATABASE Java;
DROP the DATABASE IF the EXISTS Java;
The CREATE DATABASE IF NOT EXISTS class01;
DROP the DATABASE IF the EXISTS class01;
USE ` class01 `;
SHOW the DATABASES - view all data table
` ` `
# # 1.6 create data table
Is a kind of DDL
Grammar:
` ` ` SQL
The create table (if not the exists] ` table name ` (
'the field name 1 column type [properties] [index] [note],
'the field name 2 column type [properties] [index] [note],
#...
'n' field name column type [properties] [index] [note]
) type [table] [table character set] [note];
` ` `
Note: * * * * the quotes for difference between MySQL reserved words and common characters and introduced (keyboard esc button below).
# # # (1) the data values and the column type
The column type: specified type of the column to store data in the database
Numerical type
1.
2. The type string
3. Type the date and time
4. A NULL value
- understood as "no value" or "unknown values
"- do not use NULL for arithmetic, the result is still NULL
# # # (2) data field properties
UnSigned * * * * :
- an unsigned integer
- the data columns do not allow the negative statement.
ZEROFILL:
Filled - 0
- lack of digits, to fill with zeros, such as int (3), 5 005
Auto_InCrement * * * * :
- automatic growth, each adding a data, automatically on a record number of + 1 (the default)
- usually used to set the primary key index, * * * * must be an integer type,
- can be defined start and step length
- the current table Settings step (AUTO_INCREMENT=100) : affects only the current table
- SET @ @ auto_increment_increment=5; Affect all use on the table (global)
NULL and NOT NULL:
- the default is NULL, namely the column without inserting value
- if set to the NOT NULL, then the column must have a value
DEFAULT:
- the default
- is used to set the default
- for example, gender fields, the default value is "male", or "female"; Without specifying the value of the column, the default value is the value of "male"
` ` ` SQL
# to create the student table (column, field)
# student id int password varchar (20) name, gender, varchar (2), date of birth (datatime), home address, email
# to create table before, must first select the database
The CREATE TABLE IF NOT EXISTS ` student02 ` (
` id ` INT (4) NOT NULL AUTO_INCREMENT COMMENT 'student id',
` name ` VARCHAR (30) NOT NULL DEFAULT 'students' COMMENT' name ',
` sex ` VARCHAR (2) NOT NULL DEFAULT 'male' COMMENT 'gender',
` birthday ` DATETIME DEFAULT NULL COMMENT 'date of birth,
` address ` VARCHAR (100) the DEFAULT NULL COMMENT 'home address'
` email ` VARCHAR (50) DEFAULT NULL COMMENT 'email',
PRIMARY KEY (` id `) - set the PRIMARY KEY
) ENGINE=INNODB DEFAULT CHARSET=utf8
# see the definition of database
SHOW the CREATE DATABASE school;
# see the definition of data table
SHOW CREATE TABLE student;
# display table structure
DESC student;
# set strict inspection mode (fault tolerance)
The SET sql_mode='STRICT_TRANS_TABLES';
` ` `
MyISAM MySQL data table type: * * * *, * *, * * InnoDB HEAP, BOB, CSV, etc... nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull