Python Advanced _mysql (1)

What is a database?

A database is a warehouse that organizes, stores, and manages data according to its structure, and each database has one or more different APIs for creating, accessing, managing, searching, and replicating the saved data.

go to MySQL (Linux operations database)

-u user name--uroot-pqwe123

Create user

Creating a user creates a database

  #创建用户mysql  > CREATE USER  " jianeng   " @  %   " identified \q\by "  qwe123   "  > GRANT all on *. * to  " jianeng   " @"  %   " ; #使更改立即生效mysql  > FLUSH privileges; #退出mysql  > \q 

View in which database: SELECT DATABASE(); Hall null

View Current User:SELECT USER();

See what databases are available: SHOW DATABASES; # What database does MySQL have?

We start the database operation with a graph

First we need to create a History_book database :

Create DATABASE [if not exists] ' db_name ';

Mysql>create database [if not exists] ' history_book ';
Query OK, 1 row Affected (0.00 sec)

Mysql>create database [if not exists] ' history_book ';
ERROR 1007 (HY000): Can ' t create database ' mydb '; Database exists

At this time, we completed the creation of a History_book library. If a database with the same name is present, an error will be given.

We'll look at the contents of the database: show databases;

Mysql> SHOW DATABASES;
+--------------------+| Database            |+--------------------+| information_schema  | |     mysql               | | performance_schema  | | sys                 | | | History_book        |+--------------------+

You will find that the database you created already exists.

For databases We do not need, we need to delete the database :

drop database [if exists] ' db_name ';

NOTE: The SQL statement must end with a semicolon. MySQL is case-insensitive

Now we have access to the database we created :

Use db_name;

We can view the information inside the database:

Select Database ();

You will find that you are currently not entering a database and need to use it use to enter a database.

mysql> use history_book;database changedmysqlSelect  Database (); +--------------+| Database ()   |+--------------+| history_book |+--------------+1inset (0.00 sec)

We just created a database, but there was nothing in it. It's equivalent to creating an empty library.

All we need to do is to empty the library and put a bookshelf (that is, create a table for History_book).

Create a data table

CREATE TABLE [if not EXISTS] table (id int, name Varchai (20));

Mysql>CREATE TABLE Tang_book (Idint,    Name varchar ( -)); Query OK,0Rows Affected (0.03sec) MySQL> CREATE TABLE Song_book (IDint, Name varchar ( -)); Query OK,0Rows Affected (0.06sec) MySQL> CREATE TABLE Qing_book (IDint, Name varchar ( -)); Query OK,0Rows Affected (0.04Sec

We created a bookshelf for History_book.

View History_book's bookshelf.

view tables in a database

A data table (or table) is one of the most important components of a database and is the basis of other objects

View a list of data tablesSHOW TABLES [FROM db_name]

SHOW TABLESView the data tables in the current database.

SHOW TABLES FROM ‘mysql‘View the mysql data tables in this database.

 from History_book; +------------------------+| Tables_in_history_book |+------------------------+| Qing_book              | | Song_book              | | Tang_book              |+------------------------+3inset (0.00 sec)

Data type:int integer type VARCHAR variable length string

View the structure of a bookshelf (table)

To view the tables created:

SHOW CREATE TABLE tb_name;(\G)Table Creation Details

Mysql> mysql> Show create table Tang_book; +-----------+-----------------------------------------------| Table     |  Create Table                                  +-----------+-----------------------------------------------| Tang_book | CREATE TABLE ' Tang_book ' (  int(one) DEFAULT NULL,  ' name ' varchar (20< /c10>) Default NULL) ENGINE=innodb default Charset=utf8 |+-----------+----------------------------------- ------------1inset (0.00 sec)

To view the structure of a table:

Desc Tb_name;

mysql> desc Tang_book; +-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    int(one)     | YES  |     | NULL    |       | | name  | varchar | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+2inset (0.00 Sec

To delete a table:

drop TABLE TableName;

So far, we have found that we have just built a history library and several bookshelves, without a book. That is, we have not deposited a data. Next, we are going to deposit books for our library.

increase, deletion, search and change of data in a single tableInsert Inserts Data

Method One:

INSERT [
{values| VALUE} ({expr| DEFAULT},...), (...),...;

Method Two:

INSERT [SET col_name={expr|  DEFAULT},... in

SELECT query data

 SELECT * FROM tab_name [WHERE];

mysql> INSERT INTO Tang_book (id,name) VALUES (1,'Book_1'),    (2,' Book'),    (3,'Book_3'),    (4,'Book_4'); Query OK,4Rows Affected (0.07sec) Records:4Duplicates:0Warnings:0MySQL>Select* fromTang_book;+------+--------+| ID | Name |+------+--------+|1| Book_1 | |2| Book | |3| Book_3 | |4| Book_4 |+------+--------+4Rowsinch Set(0.01Sec

We can update the data in the table when we find a problem with the data in the table.

Update updates Data

UPDATE Tb_name
SET col_name1={expr1| Default}[,col_name2

={expr2| DEFAULT}] ...
[WHERE where_condition];

mysql> Update Tang_bookSetName='book_2' whereId=2; Query OK,1Row affected (0.02sec) Rows matched:1Changed:1Warnings:0MySQL>Select* fromTang_book;+------+--------+| ID | Name |+------+--------+|1| Book_1 | |2| Book_2 | |3| Book_3 | |4| Book_4 |+------+--------+4Rowsinch Set(0.01Sec

Delete Deletes data

DELETE from Tbl_name [WHERE Where_conditon];

It is important to note that not adding where will delete all records, which is dangerous

Here's a look at the data types in MySQL

# mysql data type integer floating-point date type character type MySQL>CREATE TABLE TB2 (-ID INT,Name VARCHAR ( -), #指定长度, up to 65,535 characters. ***variable length-A sex CHAR (4), #指定长度, up to 255 characters. ***Fixed length-Price DOUBLE (4,2), #双精度浮点型, m total number, D decimal place-detail text, #可变长度, up to 65,535 characters-Dates DATETIME, #日期时间类型 yyyy-mm-DD HH:MM:SSPing ENUM ('Praise','Bad Reviews') #枚举, select in the given value);

Python Advanced _mysql (1)