Database Summarydisadvantages of traditional record data: Hard to save, backup difficult, inconvenient to finddatabase: A warehouse that stores data that describes the basic information of a transactionPros: 1. Persisting storage2. Very high reading and writing speed3. Guarantee the validity of the data RDBMSrelational database management systemmunitions currently use two types of databases: 1. Relational database, 2 non-relational databasekey products for relational databases:Oracle: Major projects in the use of banks, telecommunications and other projectsthe most widely used relational database in the Mysql:web eraMS SQL Server: Microsoft projects in use ( Microsoft Corporation)SQLite: Lightweight database, mainly used in mobile platformrelational database core elementsdata row (a record, which is equivalent to an instance object in Python) instance object data column (field, equivalent to the property of an instance object in Python) the properties of the field instance object data Table (a collection of data rows, equivalent to a list in Python)Database (a collection of data tables that can have n data tables in a database) SQLStructured Query LanguageStructured Query LanguageThe script that operates in the database is called SQL, Structured Query language,the current relational database supports operations using the SQL language Oracle,mysql,ms SQL Server, SQLiteSQL statements are mainly:1.dql:data Query Language querying language, select2.dml:data Manager language operation, additions and deletions, Update,delete,insertTPL: Transaction processing language: Begin Transaction,commit,rollbackDcl:data Control Language: Authorization and recycling, Grant,revokeddl:data Define language: Database, table management: Create,dropCCL: Pointer Control Language: Controls the operation of the pointer completion table, DECLARE CURSORfor Web programs, the focus is on data crud and must be skilled at writing Dql,dml to write DDL to complete the databasethe operation of the table,SQL is a special language that is designed to operate the database Case insensitive MYSQLFeatures: Free open source, not money across the platform, providing multi-lingual API interface, the use of a wide range ofBasic Useserver-side installationTerminal: sudo apt-get install mysql-serverfor: Receiving requests from clients, executing SQL statements, managing databasesserver-side generally managed by service: name is MySQLstart, stop, restartsudo service MySQL startsudo service MySQL stopsudo service mysql restartTo see if a process is in process MySQL servicePS ajx|grep MySQLgrep (gelobal Regular expression print)configuration file directory is/etc/mysql/mysql.cnfclient: For developers and DBAs, the socket is used to communicate with the server.navicat command line MySQL Client Commandssudo apt_get install mysql-clientMySQL--help: detailed connected commands view Help documentationmysql-uroot-p MySQL: Go to mysql command lineCtrl +d, quit, exit DataDatabase-----Data table-------data row (instance object)-----data column (attribute of instance object) fielddadabase--table---select,update,delete,insert,----ALTER TABLE add/change/,p/\Modifythe principle of using data types: Suffice it, try to use a small range of values, instead of large, you can save storage spaceCommon data types: integer: int bitdecimal: Decimalenum: Enumstring: Char,varchar,text (greater than 4000)Date Type: Date,time, datetimefor pictures, videos, audio and other files, upload to a server, and then store this file in the table save pathconstraint: Primary key primary key unique indicatorauto_increment: Automatic growthunsigned: unsigned shaping signedNOT NULL: Non-emptyUnique : Unique, the value of this column does not allow duplicatesDefault : Defaults, not filled with default values, fill in to fill in the subjectFOREIGN key: foreign key, generally do not,: increase the coupling between data and data, not conducive to post-maintenance, optimization mysql command line connectionmysql-uroot-pPassword: MySQLLog out: Ctrl+d, Quit,exit select version (); View versionsSelect Now (); Show Current Timeprompt: Modify the input prompt to use \d,\d,\h,\u \date \databasee\user\host Database Operations Commandshow databases;Use database name;Select Database (); View the databases currently in useCREATE database name Charset=utf8; ( character set format to UTF8) creates a database when the drop database database name; operation orders for data sheetsshow tables;desc table name; View table Structure describleCREATE TABLE table name (field, type, constraint)ALTER TABLE name add column name type;ALTER TABLE name change old column name, new column name type;ALTER TABLE table name modify column name modify TypeALTER TABLE name drop column name; Delete Columndrop table table name drop tables CurdCreate update refer delete Database DesignBased on the E-R model, the relational database proposes to extract the model and relation according to the planning design .make a table structure,Development, Design Database wheel: DB Designer, Power designer, visually see the relationship between entities and entitiesthree-paradigm: column is not divided, only means, reference primary keyE-r model E represents entry one-to-one, one-to-many, many-to-many,tombstone Isdelete bit default 0eliminate duplicate rows distinct: Use distinct before columns in select to eliminate duplicate rowsAggregation FunctionsCount (*)Mid (column) max (column) AVG (column) sum (column) Group:GROUP By field, indicating that the same data in this field is placed in a groupHaving is the filtering of data after grouping Connection Querywhen the columns of the query result are from multiple tables, you need to concatenate multiple tables into a large data set and select the appropriate columns to returnInternal connection query: Data matched to two tablesRight Connection query: Two tables matching data + right table-specific dataleft JOIN query two tables matching data + left table-specific dataSELECT * FROM students inner joins classes on students.cls_id = classes.id;on what condition is it connected to the Chengde large table, then the result set is displayed with their public properties,Slect is to select what to display
The most commonly used data types in MySQL, Constraints, and tables, fields, rows of crud operations directives, the difference between function and precedure, index, security, transaction
