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

No.1 database concept what is a database?

A database is a special file that stores the required data internally.

RDBMS

The so-called relational database is a database based on the relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra.

SQL

SQL is a structured language and a database language used to operate relational databases.

SQL statement description examples DQL data query language selectDML data operation language insert, update, deleteTPL transaction processing language begin, transaction, commit, rollbackDCL data control language grant, revokeDDL data definition language create, dropCCL pointer control language declare, cursorMySQL

MySQL is a relational database management system.

Features:

Written in C and C++, and tested with a variety of compilers to ensure the portability of the source code to support a variety of operating systems, such as Linux, Windows, AIX, FreeBSD, HP-UX, MacOS, NovellNetware, OpenBSD, OS/2 Wrap, Solaris, etc., provide API for many programming languages, such as C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, etc., and make full use of SQL query algorithm optimized by CPU resources. Effectively improve query speed and provide multi-language support. Common codes such as GB2312, BIG5, UTF8 provide multiple database connections such as TCP/IP, ODBC and JDBC to provide management tools for managing, checking, and optimizing database operations. Large databases. Large databases that can handle tens of millions of records support multiple storage engines MySQL software adopts dual licensing policy, it is divided into community version and commercial version, because of its small size, high speed, low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database MySQL using the standard SQL data language form Mysql can be customized, using the GPL protocol You can modify the source code to develop your own Mysql system online DDL change function copy global transaction identity replication crash-free slave replication multithreaded slave No.2 data integrity

A database is a complete business unit, which can contain multiple tables. In order to store the data more accurately and ensure the correctness of the data, some mandatory validation can be added to the table when creating the table, including data types and constraints.

Data type

Integer: int,bit

Decimal: decimal

String: varchar,char

Date time: date, time, datetime

Enumerated types (enum)

Decimal represents floating-point numbers, such as decimal (5-point 2) indicates coexistence of 5 digits, with 2 decimal places

Char represents a fixed-length string, such as char (3), which will fill in a space if ab is filled in

Varchar represents a variable-length string, such as varchar (3), which stores ab when populated with ab

The string text indicates that large text is stored. It is recommended when the character is greater than 4000.

For files such as pictures, audio, video, etc., they are not stored in the database, but uploaded to a server, and then the numerical type of the file's save path is stored in the table.

Type byte size signed range (Signed) unsigned range (Unsigned) TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT/INTEGER4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615 string

Type byte size example CHAR0-255. type: char (3) input 'ab', is actually stored as' ab', input 'abcd' is actually stored as' abc'VARCHAR0-255Type: varchar (3) input 'ab', is actually stored as' ab', input 'abcd', is actually stored as' abc'TEXT0-65535 large text

Date time type

Type byte size example DATE4'2020-01-01'TIME3'12:29:59'DATETIME8'2020-01-01 12:29:59'YEAR1'2017'TIMESTAMP4'1970-01-01 0012:29:59'YEAR1'2017'TIMESTAMP4'1970-01-01 0012:29:59'YEAR1'2017'TIMESTAMP4'1970 01' UTC ~ '2038-01-01 0012:29:59'YEAR1'2017'TIMESTAMP4'1970 01' key constraint primary key primary key: physically stored order is not empty not null: null value is not allowed in this field unique unique: the value of this field does not allow repetition of default default: the default value is used when this value is left empty If you fill in the foreign key foreign key: constrain the relationship field, when you fill in the value for the relationship field, you will query whether the value exists in the associated table. If it exists, it succeeds. If it does not exist, it fails and throws an exception No.3 command line script database operation show databases. View the database use database name; use the database select database (); view the currently used database create database database name charset=utf8 Create database drop database database name; delete database table operation show tables; to view all table desc table names in the current database; view table structure to create tables create table students (id int unsingned primary key auto_increment not null, name varchar (20) default', age int unsingned default 0, height descimal (3Magne2) default 1.8, gender enum ('male', 'female') default 'male') alter table table name add column name type Add field alter table table name change type and constraint; modify field alter table table name change original name new name type and constraint; modify field (rename field) alter table table name drop column name; delete field drop table table name; delete table show create table table name; view table creation statement data addition, deletion, modification and query

Increase

The insert into table name is values (); all columns are inserted into the insert into table name (column 1.) Values (); insert some columns into the insert into table name (column 1.) Values (),...; insert multiple pieces of data

Delete

Delete from table name where condition; delete selected data

Change

Update table name set column 1 = value 1, column 2 = value 2. Where condition; modify data

Check

Select column 1, column 2. From table name; query data backup and recovery

Backup

Mysqldump-uroot-p database name > database name. Sql

Restore

Create a new database mysql-uroot-p new database name

< 数据库名.sqlNo.4 数据库设计 关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步，在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系，设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成 三范式 经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式 第一范式(1NF) 强调的是列的原子性，即列不能够再分成其他几列 第二范式(2NF) 首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分 第三范式(3NF) 首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况 E-R模型 E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表 R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多 关系也是一种数据，需要通过一个字段存储在表中 实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值 实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值 实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值 逻辑删除对于重要数据，并不希望物理删除，一旦删除，数据无法找回删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0对于非重要数据，可以进行物理删除数据的重要性，要根据实际开发决定No.5 MySQL查询准备测试数据创建数据库create database python charset=utf8;使用数据库use python;创建students表create table students( id int unsigned primary key auto_increment not null, name varchar(20) default '', age tinyint unsigned default 0, height decimal(5,2), gender enum('男','女','中性','保密') default '保密', cls_id int unsigned default 0, is_delete bit default 0);创建classes表create table classes ( id int unsigned auto_increment primary key not null, name varchar(30) not null);向students表中插入数据insert into students values(0,'×××',18,180.00,2,1,0),(0,'小月月',18,180.00,2,2,1),(0,'彭于晏',29,185.00,1,1,0),(0,'刘德华',59,175.00,1,2,1),(0,'黄蓉',38,160.00,2,1,0),(0,'凤姐',28,150.00,4,2,1),(0,'王祖贤',18,172.00,2,1,1),(0,'周杰伦',36,NULL,1,1,0),(0,'程坤',27,181.00,1,2,0),(0,'刘亦菲',25,166.00,2,2,0),(0,'金星',33,162.00,3,3,1),(0,'静香',12,180.00,2,4,0),(0,'郭靖',12,170.00,1,4,0),(0,'周杰',34,176.00,2,5,0);向classes表中插入数据insert into classes values (0, "python_01期"), (0, "python_02期"); 查询所有字段 select * from students; 查询指定字段 select name from students; 使用as给字段起别名 select id as 序号,name as 姓名,gender as 性别 from students; 使用as给表起别名 select s.id,s.name from students as s; 消除重复行 select distinct gender from students;条件 使用where子句对表中的数据筛选，结果为True的行会出现在结果集中 where后面支持比较运算符、逻辑运算符、模糊查询、范围查询、空判断 比较运算符 等于: = 大于: >

Greater than or equal to: > =

Less than: (select avg (age) from students)

Query the names of all the classes in which the students are in the class.

Select name from classes where id in (select cls_id from students)

Demand: find the oldest and tallest student in the class

Select * from students where (height,age) = (select max (height), max (age) from students); the execution order of the query

From Table name > where > group by > slect distinct > having > order by > limit

No.6 Python interacts with MySQL to prepare data to create JD.com database create database jing_dong charset=utf8; uses JD.com database use jing_dong Create a commodity goods data sheet create table goods (id int unsigned primary key auto_increment not null, name varchar (150) not null, cate_name varchar (40) not null, brand_name varchar (40) not null, price decimal (10Let3) not null default 0, is_show bit not null default 1, is_saleoff bit not null default 0) Insert data insert into goods values into the goods table (0minute notebook r510vc 15.6in notebook', 'notebook', 'Asustek', '33996th laptop', 'notebook', 'notebook', 'Lenovo', '49990inch notebook', 'notebook', 'Lenovo', '4999century defaultdefaultnotebook'); insert into goods values (0PowerG150th 15.6in notebook', 'Ben', 'Raytheon', '8499mdefaultdefault) Insert into goods values (0meme x550cc 15.6in notebook', 'notebook', 'Asustek', '2799mm ultrabook', 'Lenovo', '4880 ultrabook'); insert into goods values (0meme 330p 13.3inch ultrabook', 'ultrabook', 'Lenovo', '4299 ultrabook') Insert into goods values, 'ultrabook', 'Sony', '7999 tablet mini', 'tablet computer', 'Apple', '1998 baby iPad air 9.7inch tablet', 'tablet air', 'Apple', and insert into goods values. Insert into goods values (0quotation iPad mini is equipped with retina display', 'tablet', 'Apple', '2788 desktop', 'Dell'); insert into goods values (0Gradeideacentre c340-inch all-in-one computer', 'desktops', 'Lenovo', '3499 minute defaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDeDefaultDefaultDefaultDefaultDeDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultdededefaultDefaultDefaultDefaultdededefaultDefaultDefaultDefaultDefaultDefaultDefaultdededefault Insert into goods values, 'desktops', 'Apple', '9188 desktops', 'defaultPowerDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefault'; insert into goods values' desktops', 'desktops', 'Acer', '3699 mineDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultDefaultdedefault Insert into goods values (0meme poweredge ii server, 'server / workstation', 'Dell', '5388 desktop computer', 'server / workstation', 'Apple', '28888 professional desktop computer', 'server / workstation', 'Apple', '28888 poweredge ii server', 'insert into goods values', 'notebook accessories', 'Sony', '6999laptop accessories', 'Sony') Insert into goods values (0meme 'business backpack', 'notebook accessories', 'Sony', '995th minute defaultdatabase default'); insert into goods values (0meme x3250m4 rack server', 'server / workstation', 'ibm','6888',default,default); insert into goods values (' business backpack', 'notebook accessories', 'Sony', '99mom defaultdefaultdefault'); database design

Create a commodity classification table

Create table goods_cates (id int unsigned primary key auto_increment not null, name varchar (40) not null)

Create a list of commodity brands

Create table goods_brands (id int unsigned primary key auto_increment not null, name varchar (40) not null)

Create a list of goods

Create table goods (id int unsigned primary key auto_increment not null, name varchar (40) default', price decimal (5jue 2), cate_id int unsigned, brand_id int unsigned, is_show bit default 1, is_saleoff bit default 0, foreign key (cate_id) references goods_cates (id), foreign key (brand_id) references goods_brands (id))

Create a customer table

Create table customer (id int unsigned auto_increment primary key not null, name varchar (30) not null, addr varchar (100), tel varchar (11) not null)

Create an order table

Create table orders (id int unsigned auto_increment primary key not null, order_date_time datetime not null, customer_id int unsigned, foreign key (customer_id) references customer (id))

Create an order status table

Create table order_detail (id int unsigned auto_increment primary key not null, order_id int unsigned not null, goods_id int unsigned not null, quantity tinyint unsigned not null, foreign key (order_id) references orders (id), foreign key (goods_id) references goods (id)); PyMySQL operation flow

Connection object

Used to establish a connection to the database

Conn=connect (parameter list) host: connected mysql host, if the local machine is' localhost'port: the port of the connected mysql host, the default is 3306database: database name user: connection user name password: connection password charset: communication encoding method. It is recommended to use utf8close () to close the connection commit () to submit cursor () to return the cursor object, execute sql and return the result.

Cursor object

Returns the cursor object, executes the sql and returns the result

Rowcount read-only attribute, indicating the number of rows affected after the last execution of execute () connection gets the current connection object close () closes the execute (operation [, parameters]) execution statement, and returns the number of affected rows, which is mainly used to execute insert, update, delete statements, or create, alter, drop, etc. When fetchone () executes the query statement, it gets the first row data of the query result set and returns a tuple fetchall () when executing the query Get all the rows of the result set, one row forms a tuple, and then load these tuples into a tuple and return to add, delete, modify and check.

Add, delete and correct

Import pymysqlif _ _ name__ = ='_ main__': conn = pymysql.connect (host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor () # add count = cursor.execute ('insert into goods (name,cate_name,brand_name) values ("hard disk", "" "")') print (count) count = cursor.execute ('insert into goods (name) values ("CD")) print (count) # Update count = cursor.execute (' update goods set name= "Mechanical hard disk" where name= "hard disk") # Delete count = cursor.execute ('delete from goods where id=6') print (count) conn.commit () conn.close ()

Query a piece of data

Import pymysqlif _ _ name__ = ='_ main__': conn = pymysql.connect (host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor () count = cursor.execute ('select id,name from goods where id

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.