Python 3.x learning note (MySQL not finished)

1. A database is a warehouse that organizes, stores, and manages data according to its structure.

2.RDBMS is the feature of the relational database management system (relational Management Systems):
1). Data appears in tabular form
2). Each record name of the behavior
3). The data field corresponding to the record name for each column
4). Many rows and columns form a single sheet
5). A number of forms form database

3.RDBMS Terminology
database: The database is a collection of some associated tables.
data table: a table is a matrix of data. Tables in a database look like a simple spreadsheet.
columns: a column (the data element) contains the same data, such as the postal code.
row: a row (= tuple, or record) is a set of related data, such as a user's subscribed data.

redundancy: stores twice times the data, redundancy can make the system faster. (The higher the degree of normalization of a table, the more the relationship between the table and the tables may often require a connection query between multiple tables, and a connection operation can slow down the query.) For example, the student's information is stored in the student table, and the faculty information is stored in the Department table. An association relationship is established with the Department table by using the dept_id field in the student table. If you want to query the name of a student's department, you must find the student's department number (dept_id) from the student table, and then department find the name of the department based on that number. If you often need to do this, the connection query will waste a lot of time. You can therefore add a redundant field, Dept_name, to the student table, which is used to store the names of the students ' faculties. This will not have to be connected every time. )

PRIMARY key: The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data.
foreign Key: A foreign key is used to correlate two tables.
Composite key: composite key (key combination) Multiple columns are used as an index key, and are typically applied to composite indexes.
index: use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.
referential integrity: referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.

Use of 4.mysql
1) Show databases; #显示数据库

2) Use < database name >; # Connect to Database

3) Show tables; #显示表

4) Desc < table name >; #查看表结构, you can also use Show columns from < table name >;

5) SELSCT * FROM < table name >; #查看表数据

6) SELSCT * FROM < table name >\g #查看表数据 (display content Clear) do not add;

7) Grant all on * * to ' username ' @ ' localhost ' identified by ' passwd '; # Create a user and set permissions all to give all permissions

8) Create databases < database name >; #创建数据库

9) CREATE TABLE student (
ID int auto_increment,
Name Char (+) is not NULL,
The age int is not NULL,
Register_date date NOT NULL,
Primary key (ID));
#创建一个简单的表

Show create databases < database name >; #查看数据库的character

Create DATABASE < databases name > CharSet UTF8; #创建数据库并设置character为utf-8

DROP table < table name >; #删除表

Drop databases < database name >; #删除数据库

SELECT * FROM < table name > limit 3 offset 2; #从第三个开始查询限制 (limit) of 3

SELECT * FROM < table name > where register_date like ' 2018-04% '; #查找register_date为2018-04 of all data

Update < table name > Set name= ' Sea ', age=34 where id=5; #修改id为5的name和age为 ' Sea ', ' 34 '

Delete from < table name > where id>4; #删除id all data for >4

SELECT * FROM < table name > order by ID asc/desc; #查询的表是id的升序 (ASC)/reverse (DESC)

Select Name, COUNT (*) as Stu_num from < table name > group by name; #选择name属性并统计整个表格 (Count (*)) group statistics by name a column named Stu_num

Select Name, sum (age) from < table name > group by name; #统计相同名字的年龄总和并按照name分组

Select COALESCE (Name, ' Total age '), sum (ages) from < table name > GROUP by name with Rollup; #统计相同名字的年龄总和并按照name分组, show all totals in the last line, Coalesce function is named

ALTER TABLE < table name > add sex int (one) not null; # Add Phone field and cannot be empty

ALTER TABLE < table name > drop sex; #从student表删除sex字段

ALTER TABLE < table name > Modify sex < field type > NOT null; #修改字段sex不能为空, the Sex field must not be empty before modification

ALTER TABLE < table name > change Sex Gender char (+) NOT null default ' X '; #更改字段sex为gender, the data type is char (32) and cannot be null, the default value is ' X '

MySQL command Daquan
Http://www.jb51.net/article/74564.htm

Python 3.x learning note (MySQL not finished)