The Python path database

I. Introduction to the Database

1. Database

A database (DATABASE,DB) is a collection of organized, shareable data that is stored in a computer for a long period of time. The data in the database is organized, described and stored according to a certain digital model, with small redundancy, high data independence and extensibility, and can be shared for various users.

2. Database classification

2.1 Relational database: Mysql,oracle,sql server,db2,sqlite

2.2 Non-relational database: Redis, MongoDB

3. Database system

The database systems DBS (Data Base system) is typically comprised of software, database, and data administrators. Its software mainly includes operating system, various host languages, utilities and database management system. The database is managed by the database management system, and the data inserting, modifying and retrieving are all through the database management system. The Data Manager is responsible for creating, monitoring, and maintaining the entire database so that the data can be used effectively by anyone who has access to it.

Second, the management of MySQL 1.mysql

1.1 Installation

Linux:

--yum-y Install mariadb mariadb-serveror--yum-y install MySQL Mysql-server

Windouws:

1.2: Start

--service mysqld start  # open --chkconfig mysqld on   # set boot OR-- systemctl start mariadb--systemctl enable mariadb

1.3: View

--PS aux |grep mysqld    # View process # View Port

1.4: Set Password

' 123 '   # set initial password, empty initial password so the-P option is not used ' 1234 ' # To modify the root user password

1.5: Login

--MySQL               # Local login, Default user root, blank password, user for [email protected]# Local login, specify user name and password, user is [ Email protected]# telnet, user for [email protected]

Common commands for 2.mysql

-- --to start the MySQL service with the Stop MySQL service command:-- --net start MySQL--net stop MySQL-- --Login and Exit commands:----Mysql-h server ip-p port number-u user name-p password--prompt command prompt--delimiter specify delimiter--Mysql-h 127.0.0.1-p 3306-uroot-p123--Quit------Exit----\q;----\s; ------My.ini file: [MySQL] default-character-set=gbk [mysqld] character-set-server=GBK-- --prompt Command Prompt (\d: current date \d: Current database \u: Current user)-- --\ t (start log) \ t (end log)-- --show warnings;-- --Help ()? \h-- --\g;-- --Select Now ();--select version ();--Select User;-- --\c Cancel Command----delimiter specifying delimiters

3. What if I forget my password?

Method 1: Skip the authorization table when you start MySQL

[[Email protected] ~]#Service mysqld Stop[[Email protected] ~]#mysqld_safe--skip-grant-table &[[Email protected] ~]#MySQLMysql> Select User,host,password fromMysql.user;+----------+-----------------------+-------------------------------------------+| user | Host | Password |+----------+-----------------------+------------------------------------------- +| Root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF | | Root |                                           Localhost.localdomain | || Root |                                           127.0.0.1 | || Root |                                           :: 1 |          || |                                           localhost |          || |                                           Localhost.localdomain | || Root | %                     | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |+----------+-----------------------+--------------------------------- ----------+MySQL> Update Mysql.user Set Password=password ("123") Where user="Root"  andhost="localhost"; MySQL>Flush Privileges;mysql>Exit[[email protected]~]#Service mysqld Restart[[Email protected] ~]#mysql-uroot-p123

Method 2: Delete the Library

~]#  rm-rf/var/lib/mysql/mysql[[email protected] ~]#  service mysqld restart[[ Email protected] ~]#  MySQL

4.SQL: Structured statement specification:

4.1: Case insensitive (Command suggested capitalization)

4.2: End symbol as a semicolon

4.3:--single-line comment,/* Multiline Comment */

5.mysql Data types

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

5.1: Numeric type

The following table shows the storage and scope of each integer type that is required.

5.2: Date and Time type

The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and tear.

Each time type has a valid value range and a ' 0 ' value, and the ' 0 ' value is used when specifying an illegal MySQL value that cannot be represented.

5.3: String type

The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

　　

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.

Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string.

A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

6. Operation of the database

6.1:show DATABASES; --View all the databases

6.2:create database database_name--Creating databases

6.3:show CREATE DATABASE database_name--View the information for creating databases

6.4:drop Database Database_naem--delete one of the databases

6.5:use database_name--Using a database

---1. Create a database (create a corresponding folder on disk) Create databases    [if not exists] db_name [character Set XXX]     --2. View    the database show databases; View all databases    show create database db_name; View how the database was created -3. Modifying the    Database --4. Deleting a database    drop databases [if  exists]    db_name; --5. Using    the database - Note: After entering a database, there is no way to return to the previous state, but you can use the use to switch to    view the currently used databases select database ();

7. Operation of the data sheet

7.1: Create a table

CREATE TABLE table_name (

field firstname data type [constraint],

。。。。。

field firstname data type [constraint])

7.2: Modify Table

--Add columns (fields)
ALTER TABLE TAB_NAME add [column] name type [integrity constraint][first|after field name];
--Modify a column type
ALTER TABLE tab_name Modify column name type [integrity constraint][first|after field name];
--Modify column names
ALTER TABLE TAB_NAME change [column] columns name new column name type [integrity constraint][first|after field name];

--Delete a column
ALTER TABLE Tab_name drop [column] name;
--Modify Table name
Rename table name to the new name;
--Fix the character set used by the table
ALTER TABLE student character set UTF8;

7.3: View Table

DESC table_name--View table details

Show TABLES--View all lists of the current library

Show CREATE TABLE table_name--View table creation information

7.4: Delete Table

DROP TABLE table_name

7.5: Add primary key, delete primary key

ALTER TABLE table_name ADD PRIMARY KEY (field name, ...);

ALTER TABLE users DROP PRIMARY KEY;

--1. Create tables (similar to an Excel table) create TABLE Tab_name (field1 type[integrity constraint], field2 type,.            .         FIELDN type) [character set XXX]; --Create an Employee table Employee CREATE TABLE employee (ID int primary key auto_increment, name varchar (20), gender bit default1,--Gender char (1) Default 1-----or TINYINT (1) Birthday date, entry_date date, job varchar (20), salary double (4,2) unsigned, resume text--Note that this is the last field without a comma); /*constraint: Primary KEY (non-null and unique): The field that uniquely distinguishes the current record is called the primary key! Unique notthe null Auto_increment primary key field must be a numeric type. FOREIGN KEY constraint foreign key*/--2. View table Information Desc tab_name view table structure Show Columns fromtab_name view table structure Show tables View all tables in the current database Show create TABLE tab_name view current database table Table statements--3. Modify Table Structure--(1Add column (field) ALTER TABLE tab_name add [column] column name type [integrity constraint][first|after field name]; ALTER TABLE user add addr varchar (20) notNull unique first/After username; #Add multiple fieldsALTER TABLE users2 add addr varchar (20), add age int First, add birth varchar (20) after name; --(2Modify a column type ALTER TABLE tab_name modify column name type [integrity constraint][first|after field name]; ALTER TABLE USERS2 modify age tinyint default20;      ALTER TABLE USERS2 modify age int after ID; --(3Modify the column names ALTER TABLE TAB_NAME change [column] column name new column name type [integrity constraint][first|after field name]; ALTER TABLE USERS2 change age-Age int default28First ; --(4Delete a list of ALTER TABLE Tab_name drop [column] names; --think: Delete multiple columns?      Delete one and fill in one? ALTER TABLE USERS2 Add salary float (6,2) unsigned notNULL after name, drop addr; --(5) Modify table name rename table name to a new name; --(6The character set used by the table is ALTER TABLE student character set UTF8;--4. Delete table drop tables tab_name;---5Add primary key, delete primary key ALTER TABLE Tab_name add primary key (field name,...)    ALTER TABLE users drop PRIMARY key; Eg:mysql>CREATE TABLE test5 (num int auto_increment); ERROR1075 (42000): incorrect table definition; There can is only one auto column andit must be defined as a key CREATE TABLE test (num int primary key auto_increment); --think, how do I delete a primary key?   ALTER TABLE test modify ID int; --Auto_increment is gone, but this write key still exists, so add the following sentence ALTER TABLE test drop PRIMARY key;--You can't just delete the primary key with this sentence .--unique index ALTER TABLE tab_name add unique [index|Key] [Index name]     (Field name,...) ALTER TABLE users add unique (name)--index value defaults to field name show create table users; ALTER TABLE users add unique key user_name (name);--index value is user_name--Add a federated index ALTER TABLE users add unique index name_age (name,age);#Show create table users;--Delete a unique index ALTER TABLE tab_name DROP {index|key} index_name

Create an article table

CREATE TABLE article (            ID int primary key auto_increment,            title varchar,            publish_date int,            click_num INT,            is_top TINYINT (1),            content TEXT          );

7.6: Integrity constraint value PRIMARY KEY constraint

Single Field primary key

Primary key field features: Non-null and unique

CREATE TABLE users (            ID INT primary KEY,            name varchar), City            varchar c16/>);

View Code

Multi-field Federated primary key

CREATE TABLE users2 (            ID INT,            name varchar), city            varchar,            primary KEY ( Name,id)          );

View Code

(1) A table can have only one primary key

(2) The primary key type is not necessarily an integral type

　　　　　　

　　

The Python path database