A database database is a special kind of file that stores the data you need.
MySQL database file location in/var/lib/mysql
relational database core elements
- Data rows (Records)
- Data columns (fields)
- Data table (Collection of data rows)
- Database (collection of data tables)
Analogy Excel table, an Excel table file is equivalent to a database, inside a sheet equivalent to a table.
Rdbms
Relational Database Management System
To represent a relational type through a table
Currently the main use of two types of databases: relational database, non-relational database, this section mainly discusses the relational database, for non-relational database will be learned later
RDBMS, the so-called relational database, is a database based on the relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.
View database rank: https://db-engines.com/en/ranking
Key products for relational databases:
- Oracle: Used in previous large-scale projects, banking, telecommunications and other projects
- The most widely used relational database in the Mysql:web era
- MS SQL Server: using in Microsoft projects
- SQLite: Lightweight database, mainly used in mobile platform
Sql
Structured Query Language
SQL is a structured query language, a database language used to manipulate RDBMS, and the current relational database supports operations using the SQL language, which means that all relational databases, such as Oracle,sql server,mysql,sqlite and so on, can be manipulated through SQL operations.
- The SQL statements are mainly divided into:
- DQL: A data query language for querying data, such as Select
- DML: Data manipulation language, adding, modifying, deleting data, such as INSERT, udpate, delete
- TPL: Transaction processing language, processing of transactions, including begin TRANSACTION, COMMIT, rollback
- DCL: Data Control Language for authorization and permission recovery, such as Grant, revoke
- DDL: Data definition language, database, table management, etc., such as create, drop
- CCL: Pointer control Language, which completes the operation of the table by controlling the pointer, such as DECLARE CURSOR
- For web programmers, the focus is on data crud (increase and deletion), must be skilled in writing dql, DML, can write DDL to complete the database, table operations, other languages such as TPL, DCL, CCL understand can
- SQL is a special language dedicated to manipulating relational databases
- Case insensitive
Example:
# Create connection Connection conn = Connect (host= ' localhost ', Port=3306, User= ' root ', Password= ' MySQL ', Database= ' Python1 ', Charset= UTF8 ') # Cursor Object cs = conn.cursor () # update # sql = ' Update students set name= ' bang ' where id=6 ' # delete # executes the SELECT statement and returns the number of rows affected: Query A student data sql = Span class= "hljs-string" > ' select Id,name from students where id = 7 ' # sql = ' Select Id,name FRO M students WHERE ID = 7 ' count=cs.execute (SQL) # prints the number of rows affected print (count) /span>
About MySQL
Click to view MySQL official website
MySQL is a relational database management system, developed by the Swedish MySQL AB Company and later acquired by Sun, which was later acquired by Oracle and is currently part of Oracle's products
Characteristics
Written in C and C + + and tested with a variety of compilers to ensure source code portability
Supports multiple operating systems such as Linux, Windows, AIX, FreeBSD, HP-UX, MacOS, Novellnetware, OpenBSD, Os/2 Wrap, Solaris, etc.
APIs for a variety of programming languages such as C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, etc.
Support multi-threading, make full use of CPU resources
- Optimized SQL query algorithm to improve query speed effectively
- Multi-lingual support, common coding such as GB2312, BIG5, UTF8
- Provides multiple database connectivity pathways such as TCP/IP, ODBC, and JDBC
- Provides management tools for managing, inspecting, and optimizing database operations
- A large database. Can handle large databases that have thousands records
- Supports multiple storage engines
- MySQL software uses a dual licensing policy, it is divided into community and commercial version, because of its small size, speed, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database
- MySQL uses the standard SQL data language form
- MySQL can be customized, using the GPL protocol, you can modify the source code to develop their own MySQL system
- Online DDL change feature
- Replicate global transaction identity
- Copy no crash slave
- Replicating multi-threaded slave machines
Server-side Installation
- Install server-side: Enter the following command in the terminal, enter it, then follow the prompts
sudo apt-get install mysql-server
- The currently used Ubuntu image is already installed in the MySQL server side, no need to install, and set up to boot from the
- The server is used to receive requests from clients, execute SQL statements, manage databases
- Server-side generally managed as a service, named MySQL
- Start the service
sudo service mysql start
- To see if a MySQL service exists in the process
ps ajx|grep mysql
Stop service: sudo service MySQL stop
Restart Service: sudo service mysql restart
Configuration:
- Configuration file directory is/ETC/MYSQL/MYSQL.CNF
- Go to conf.d directory, open mysql.cnf, find and not configured
- Enter the MYSQL.CONF.D directory and open mysql.cnf to see the configuration items
Client
- The client is used by the developer and DBA to communicate with the server through the socket, often with navicat, command-line MySQL
Graphical interface Client Navicat
- can be downloaded to navicat official website
- Copy the compressed files to the Ubuntu virtual machine, put them on the desktop, unzip
tar zxvf navicat112_mysql_cs_x64.tar.gz
- Enter the extracted directory and run the following command
./start_navicat
- Press Ctrl+d or enter the following command to exit, quit or exit
Data integrity
- A database is a complete business unit that can contain multiple tables, and the data is stored in a table
- In order to more accurately store the data in the table to ensure the correct and effective data, you can create a table, the table to add some mandatory validation, including the type of data fields, constraints
Data type
- You can review all supported data types by viewing the Help documentation
- The principle of using data types is: suffice it, try to use a small range of values, instead of large, so that you can save more storage space
- Common data types are as follows:
- Integer: Int,bit
- Decimal: Decimal
- String: Varchar,char
- DateTime: Date, Time, datetime
- Enum type (enum)
- The types of special instructions are as follows:
- Decimal indicates floating-point numbers, such as decimal (5,2), which represent 5-digit coexistence, with decimals being 2 bits
- Char represents a fixed-length string, such as char (3), which fills a space when ' AB ' is
‘ab ‘
- VARCHAR represents a variable-length string, such as varchar (3), which stores ' ab ' when populated with ' AB '
- The string text indicates that large text is stored and is recommended when the character is greater than 4000
- For pictures, audio, video and other files, not stored in the database, but upload to a server, and then in the table to store the file's save path
- A more complete data type can refer to the http://blog.csdn.net/anxpp/article/details/51284106
Constraints
- Primary key PRIMARY Key: The Order of physical storage
- NonEmpty NOT NULL: This field does not allow null values to be filled
- Unique: The value of this field does not allow duplicates
- Default: Default value is used when this value is not filled, and if filled in as
- Foreign key foreign key: the relationship field is constrained, when the value is filled in for the relationship field, it is queried for the existence of the value in the associated table, if it exists, the fill fails and throws an exception if it does not exist.
- Note: Although foreign KEY constraints can guarantee the validity of data, but in the data crud (add, modify, delete, query), will reduce the performance of the database, so it is not recommended to use, then how to ensure the validity of the data? A: Can be controlled at the logical level
MySQL Database (1)
