Shulou(Shulou.com)06/01 Report--

The following brings you details about the basic operation of Python full-stack MySQL database, I believe you must have seen similar articles. What's the difference in what we bring to you? Let's take a look at the body part. I believe you will definitely gain something after reading the basic operation of Python full-stack MySQL database.

MySQL Database Introduction

MySQL is a fast and easy-to-use relational database management system (RDBMS) that many businesses use to build their own databases.

MySQL is developed, operated and supported by MySQL AB, a Swedish company. It is popular because it has the following advantages:

Released under an open source license, it is available for free.

It is powerful enough to compete with most powerful but expensive database software.

Use the standard SQL database language familiar to the industry.

It runs on multiple operating systems and supports multiple languages, including PHP, PERL, C, C++ and Java.

Very fast, even with large data sets.

PHP is a favorite language for Web developers.

Support for large databases, up to 50 million rows in a table. The default file size limit for each table is 4 GB, although you can increase the theoretical limit to 8 million TB if your operating system supports it.

Can be customized. The open source GPL license guarantees that programmers are free to modify MySQL to suit their particular development environment.

Relational database management systems (RDBMS) have the following characteristics:

A database with tables, columns and indexes can be implemented.

Ensure referential integrity between rows of different tables.

The index can be updated automatically.

Ability to interpret SQL queries and combine information from multiple tables.

RDBMS terminology

A database is a collection of tables with related data. A table is a matrix with data. A table in a database is like a simple spreadsheet column. Each column (data element) contains the same type of data, such as a zip row. A row (also known as a tuple, item, or record) is a set of related data, such as data about subscriptions. Redundancy stores data twice to make the system faster. The same two key values are not allowed in the same table. A key corresponds to only one row Foreign Key used to join two tables Compound Key A compound key is a key consisting of multiple columns because one column is not enough to determine uniqueness Index It acts in the database like an index at the back of a book Reference Integrity is used to ensure that a foreign key always points to an existing row Install MySQL Database

Connect MySQL database directives that have been started

ansheng@Darker:~$ mysql -uroot -pas -h 192.168.56.1-P 3306 Basic database operations

View all current databases

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || test || mysql || performance_schema || sys |+---------------------+5 rows in set (0.00 sec) Default database description information_schema Provides a way to access database metadata, such as database names or table names, column data types or access permissions, etc. database library mysql user permissions related data performance_schema is used to collect database Cloud Virtual Machine performance parameters sys contains a series of views, functions and stored procedures

create a database

--Create database with string utf-8 CREATE DATABASE dbname DEFAULT CHARSET utf8 COLATE utf8_general_ci;--Create database with string gbk CREATE DATABASE dbname DEFAULT CHARATOR SET gbk COLATE gbk_chinese_ci;

enter the database

use dbname;

delete database

drop database dbname;

user-related

Create ansheng user, allow all host connections, password set to as

create user 'ansheng'@'%' identified by 'as';

Change the username of ansheng user to as

rename user 'ansheng'@'%' to 'anshengme'@'192.168.56.1';

Change anshengme user password to 123

set password for 'anshengme'@'192.168.56.1' = Password('123');

Remove anshengme users

drop user 'anshengme'@'192.168.56.1';

The user permission related data is stored in the user table of mysql database, so you can also directly operate on it (not recommended)

user authorization

--view permissions show grants for 'root'@'localhost';--grant permissions on database. table to 'user'@'IP address';--revoke permissions revoke permissions on database. table from 'user'@'IP address'; data table basic operations

create tables

create table name ( column name type can be empty, column name type can be empty)ENGINE=InnoDB DEFAULT CHARSET=utf8

Can it be empty?

create table tb_name( `username_not` varchar(30) NOT NULL , --Not empty `username_null` varchar(30) NULL ENGINE=InnoDB DEFAULT CHARSET=utf8

Default value, you can specify default value when creating column, if not actively set when inserting data, automatically add default value

create table tb_name( nid int not null defalut 2, num int not null)ENGINE=InnoDB DEFAULT CHARSET=utf8

Self-increment. If you set self-increment column for a column, you don't need to set this column when inserting data. It will be self-increment by default (there can only be one self-increment column in the table).

create table tb_name( nid int not null auto_increment primary key, num int null)ENGINE=InnoDB DEFAULT CHARSET=utf8

or

create table tb_name( nid int not null auto_increment, num int null, index(nid))ENGINE=InnoDB DEFAULT CHARSET=utf8

For self-increasing columns, it must be an index (with primary key).

For autoincrement, you can set the step size and start value

show session variables like 'auto_inc%';set session auto_increment_increment=2;set session auto_increment_offset=10;shwo global variables like 'auto_inc%';set global auto_increment_increment=2;set global auto_increment_offset=10;

A primary key, a special unique index, does not allow null values, if the primary key uses a single column, its value must be unique, if it is multiple columns, its combination must be unique.

create table tb1( nid int not null auto_increment primary key, num int null)

or

create table tb1( nid int not null, num int not null, primary key(nid,num))

Foreign key, a special index, can only be specified content

create table color( nid int not null primary key, name char(16) not null)create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid))

delete table

drop table tb_name;

Clear list

--If the empty table adds columns again, then the value of the last self-increment will continue to add delete from tb_name after the empty table;--If the empty table adds columns again, then the value of the data self-increment after the empty table will calculate the truncate table tb_name from the beginning;

modify the table

--add column alter table name add column name type;--delete column alter table name drop column name;--modify column alter table name modify column name type; --type alter table name change original column name new column name type; --column name, type--add primary key alter table name add primary key(column name);--delete primary key alter table name drop primary key; alter table table name modify column name int, drop primary key;--add foreign key alter table from table add constraint foreign key name (Form: FK_slave table_primary table) foreign key slave table (foreign key field) references primary table (primary key field);--delete foreign key alter table name drop foreign key foreign key name;--modify default value ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;--Remove default ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

For the above detailed introduction to the basic operation of Python full-stack MySQL database, do you think it is what you want? If you want to know more about it, you can continue to pay attention to our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.