MySQL Database (2)

Command line connection

• The main use of command operation in the work, requires proficiency in writing
• Open terminal, Run command

mysql -uroot -p回车后输入密码，当前设置的密码为mysql

• Sign Out

quit 和 exit或ctrl+d

• After successful login, enter the following command to see the effect

查看版本：select version();显示当前时间：select now();

Modify input prompt

prompt python>

• \d Full Date
• \u using Users

Database

• View all databases

show databases;

• Working with databases

use 数据库名;

• View the database currently in use

select database();

• Create a database

create database 数据库名 charset=utf8;例：create database python charset=utf8;

• Deleting a database

drop database 数据库名;例：drop database python;

Data Sheet

• View all tables in the current database

show tables;

• View table Structure

desc 表名;

• Create a table
• Auto_increment indicates auto-grow

CREATE TABLE table_name(    column1 datatype contrai,    column2 datatype,    column3 datatype,    .....    columnN datatype,    PRIMARY KEY(one or more columns));

Example: Creating a Class Table

create table classes(    id int unsigned auto_increment primary key not null, name varchar(10));

Example: Creating a student table

CreateTable Students (IdIntunsigned primary key auto_increment not null, name varchar (20) default unsigned default Span class= "Hljs-number" >0, height decimal (5,2", Gender enum ( ' male ',  ' female ',  ' shemale ',  ' secrecy '), cls_id int unsigned default 0)     

• Modify Tables-Add fields

alter table 表名 add 列名 类型;例：alter table students add birthday datetime;

• Modify table-Modify field: Rename version

alter table 表名 change 原名 新名 类型及约束;例：alter table students change birthday birth datetime not null;

• Modify table-Modify field: Do not rename version

alter table 表名 modify 列名 类型及约束;例：alter table students modify birth date not null;

• Modify Table-Delete field

alter table 表名 drop 列名;例：alter table students drop birthday;

• Delete a table

drop table 表名;例：drop table students;

• View creation statements for a table

show create table 表名;例：show create table classes;

Change and delete (curd)

Curd Explanation: Representative creation (create), Updates (update), read (Retrieve), and delete

Query Basic use

• Querying all Columns

select * from 表名;例：select * from classes;

• Querying a specified column
• You can use as to specify an alias for a column or table

select 列1,列2,... from 表名;例：select id,name from classes;

Increase

Format: INSERT [into] tb_name [(Col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...

• Note: The primary key column is autogrow, but requires a placeholder for full column insertion, usually using 0 or default or null to occupy the position, and the actual data will prevail after the insert succeeds
• Full column Insert: The order of values corresponds to the order of the fields in the table

insert into 表名 values(...)例：insert into students values(0,’郭靖‘,1,‘蒙古‘,‘2016-1-2‘);

• Partial column insertion: The order of values corresponds to the given column order

insert into 表名(列1,...) values(值1,...)例：insert into students(name,hometown,birthday) values(‘黄蓉‘,‘桃花岛‘,‘2016-3-2‘);

• The above statement can insert a row of data into a table at a time, and you can insert multiple rows of data at once, which can reduce communication with the database
• Full column multiple row inserts: The order of values corresponds to the given column order

 insert  into table name values (...) ...; Example: insert into classes values (0, ' Python1 '), ( 0, ' Python2 ');         

insert into 表名(列1,...) values(值1,...),(值1,...)...;例：insert into students(name) values(‘杨康‘),(‘杨过‘),(‘小龙女‘);

Modify

Format: UPDATE tbname SET col1={expr1| DEFAULT} [, Col2={expr2|default}] ... [Where Condition judgment]

update 表名 set 列1=值1,列2=值2... where 条件例：update students set gender=0,hometown=‘北京‘ where id=5;

Delete

DELETE from Tbname [Where condition judgment]

delete from 表名 where 条件例：delete from students where id=5;

• Tombstone is essentially a modification operation

update students set isdelete=1 where id=1;

Backup

• Run the mysqldump command

mysqldump –uroot –p 数据库名 > python.sql;# 按提示输入mysql的密码

Recovery

• Connect MySQL to create a new database
• Exit the connection and execute the following command

mysql -uroot –p 新数据库名 < python.sql# 根据提示输入mysql密码

Database design

• Relational database recommendation on the basis of E-R model, we need to draw out the model and relationship according to the design plan of product manager, and make the table structure, which is the first step of the project.
• In the development of a lot of design database software, commonly used such as power designer,db Desinger, these software can visually see the relationship between entities and entities
• The design database may be done by a dedicated database designer or by a development team member, typically by a project manager who leads the crew to complete
• There is no need to complete the database design at this stage, but pay attention to accumulate some experience in this field.

Three paradigms

• After studying and summarizing the problems in use, some specifications are proposed for the design database, which are called the normal Form.
• There are 8 types of patterns that can be found at present, which are generally subject to the 3 paradigm.
• The first paradigm (1NF): The emphasis is on the atomicity of the column, that is, the column cannot be divided into several other columns.

  Consider such a table: "Contacts" (name, gender, phone) if in a real-world scenario, a contact has a home phone and a company phone, the table structure is not designed to reach 1NF. To comply with 1NF we simply split the column (phone), namely: "Contact" (name, gender, home phone, company phone). 1NF is good to distinguish, but 2NF and 3NF are easy to confuse.

• Second Normal (2NF): The first is 1NF, another contains two parts, one is the table must have a primary key, and the other is not included in the primary key column must be completely dependent on the primary key, and not only rely on a part of the primary key.

  Consider an order schedule: "OrderDetail" (orderid,productid,unitprice,discount,quantity,productname). Because we know we can order a variety of products in one order, so a single OrderID is not enough to be the primary key, the primary key should be (Orderid,productid). Obviously Discount (discount), Quantity (quantity) depends entirely on (depending on) Yu Si (Oderid,productid), and unitprice,productname only depends on ProductID. Therefore, the OrderDetail table does not conform to 2NF. Designs that do not conform to 2NF tend to produce redundant data.

  To split the "OrderDetail" table into "OrderDetail" (orderid,productid,discount,quantity) and "Product" (Productid,unitprice, ProductName) to eliminate multiple repetitions of unitprice,productname in the original order table.

• The third paradigm (3NF): First is 2NF, the other non-primary key column must be directly dependent on the primary key, there can be no delivery dependency. That cannot exist: non-primary key column A relies on non-primary key column B, and non-primary key column B depends on the primary key.

  Consider an order table "order" (orderid,orderdate,customerid,customername,customeraddr,customercity) primary key is (OrderID). The non-primary key columns, such as orderdate,customerid,customername,customeraddr,customercity, are completely dependent on the primary key (OrderID), so it conforms to 2NF. However, the problem is that customername,customeraddr,customercity is directly dependent on the CustomerID (non-primary key column), rather than directly relying on the primary key, it is passed through to rely on the primary key, so it does not conform to 3NF. By splitting "order" to "order" (Orderid,orderdate,customerid) and "Customer" (CUSTOMERID,CUSTOMERNAME,CUSTOMERADDR, customercity) thus reaching 3NF. * The concepts of the second paradigm (2NF) and the third paradigm (3NF) are easy to confuse, and the key to distinguishing them is whether the non-primary key columns are completely dependent on the primary key or part of the primary key; 3NF: The non-primary key column is directly dependent on the primary key, or directly dependent on the non-primary key column.

MySQL Database (2)