PHP mobile internet development notes (8)-MySQL database basic review [2] bitsCN.com
I. Data Tables
To ensure data integrity and consistency, you must specify the field name, field type, and field attribute when creating a table. constraints, indexes, and ), primary key and foregin key.
Constraints:
Not null non-empty constraint
Unique uniqueness constraint
Primary key constraint
Foreign key constraint of f
') dictionaryModels. City.objects.all (). Values_list (' id ', ' name ', ' pro_id ', ' pro__id ', ' pro__name ') list 2, reverse lookup, through the table without ForeignKey find result = Models.a3 = Pro.city_set.all (). Objects.values (' id ', ' name ', ' City__name ') result = models. Province.objects.all ()RESULT[0] # get HebeiResult[0].city_set.all () # Get all the cities under Hebei Zhangjiakou, Xingtai For pro in Result:A1 = Pro.idA2 = Pro.nameA3 = Pro.city_set.all ()All the municipalitie
Users'(name,fullname,password) VALUES (%s,%s,%s) '
cur.execute (sql, ("aaaaa","asd666" ,"6666"))#获取插入的最后一条数据的自增ID Print (CUR.LASTROWID) #只对一次增加一个可以直接看出, for adding multiple quantities at a time execute multiple SQL at one time='INSERT into Users (Name,fullname,password) VALUES (%s,%s,%s)'Cur.executemany (sql,[("aaadsaaa","Asd6dasd66","6666"),("adsaaaaa","asd6awf66","6666"),("awaaaaa","asd666","6666")]) Note that execute and Executemany will return the number of execution impact bars after exec
problem Use the keyword like % = "any string"; _ = "any character."
4. Three logical values of TRUE = 1, FALSE = 0, UNKNOWN = 1/2 and = MIN; or = MAX; not (x) = 1-x The null null value is special and requires special treatment
5.Aggregation Set function Sum sum function, avg averaging function, count count function, min minimum function, max Max function, COUNT (*) tuple number
6.Grouping Grouping Example:
Tags: otherwise technology images title new image data ble codeUse Codefirst in an existing database, except for the first time you add an entity to do it immediatelyEnable-migrationsAdd-migration initial-ignorechangesUpdate-database, the others are the same as the new database, and the changes are performedAdd-migration newmodifyUpdate-databaseNote: Generate all
established; (3) Transmission MVD: If a-->-->b,b-->-->c, then a-->-->c established; (4) MVD does not satisfy the decomposition rule: (5) If a-->-->b, then a-->--> (R-a-b) is established;
4NF Applied to MVD, not general FD; the condition and bcnf are similar; Definition: If there is a non-trivial a-->-->b, and a must be a super-key, then 4NF; 4NF Decomposition algorithm 1. To find a violation of 4NF MVD; 2. Decompose to {AB} {R-a-b} according to A-->-->b; 3. Calculate projection fd; 4.
the local HTML template file [if there is a rendering, the returned data is given back.) ]) {%for I in k2%}{{i}}{% ENFOR%}{%for i in k2.keys%}{{i}}{% ENFOR%}{%for i in k2.values%}{{i}}{% ENFOR%}{%for k,v in k2.items%}{{k}}-{{v}}{% ENFOR%} 4.ORMA. Creating Classes and fieldsClass User (models. Model):id = models. Intergerfiled ()Name = models. CHARFIENLD (max_length =) #字符长度 Python manage.py makemigrationsPython manage.py Migrate#settings. PY Registration App B. Operation IncreaseModels. User.
GROUP by Deptno));-the department name of the department with the highest average salarySELECT dname from dept WHERE deptno =(SELECT Deptno from(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno)WHERE avg_sal =(SELECT MAX (avg_sal) from(SELECT deptno, AVG (SAL) avg_sal from emp GROUP by Deptno)));-the department name of the department with the lowest average salary levelSELECT Dept.dname,t1.deptno,grade,avg_sal from(SELECT Deptno,grade,avg_sal from(SELECT AVG (SAL) avg_sal, deptno from
;===============================================================================================================Multi-Table query:
Ways to query Multiple tables:
* Cross Connection:* SELECT * from a B; ---obtained is the Cartesian product of two tables.* Inner Connection: INNER JOIN--inner can be omitted* Explicit Intra-connection: SELECT * from A inner join B on condition;* SELECT * FROM customer C INNER JOIN orders o c.cid = o.cid;* Implicit internal connection: SELECT * from a a
degree= (select MAX (degree) from score)Select top 1* from score order BY degree DESC--The 43rd query and "Li June" with the sex of all students sname.Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ')--The 44th query and "Li June" with the same sex and classmates sname.Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ') and class= (select Class from student
A bit with the original database in the use of codefirst far, but always review it, must be comprehensive point.One, insert two users in the user table (users)Second, generate the Post interfaceMVC generates a lot of interfaces and doesn't use Ajax, which is rarely used in real-world development, just for demonstration purposes.But no matter what the build, attributes, entity objects, etc. are to be used.Co
ForAlter DeleteV. viewsVirtual tables for easier and more intuitive display of certain requirements data (based on a compact, easy-to-design database)creat View asQuery statements, table joins GoVI. triggersTrigger, an operation is executed, triggering the contents of a trigger, a special stored procedure (not performed by exec, by the operation of the database (pruning) to trigger execution)are divided in
Originally learned time, quite smooth, did not expect to use again, or encountered a lot of problems, resulting in the failure of the update, so, can only reconstruct the migration, so as not to look at the chaos.First, delete the migration, restore the data to the (a) End state1. Delete Folder2. Delete a table3. Delete Columns4. Comment out the newly added attributesSecond, re-operation EF6 in the original database using Codefirst Total
statementsBegin Try...end TryBegin Catch...end Catch -- 1. Variables
Declare @name nvarchar (ten) -- Statement
Set @name = ' Wuhan University ' -- Assign Value
Print @name -- Output View Code --SELECT statement
Declare @id int
Set @id=Ten
if @id>5
begin
--when the condition is met, execute the following code
Print 'OK'
End
Else
begin
--when the condition is not met, execute the following code
Print 'No'
End
--Loops
Declare @id int
Set @id=1
while @idTen
begin
Print @id
"math" class * * SelectSname fromStudentwhereSidinch (
SelectSid fromMarkwhereSid not inch(
SelectSid fromMarkwhereCidinch(
SelectCid fromCoursewhereCName='Mathematics'
)
)
) 15. Check the name of the student whose total is less than the average Step1, calculates the average total score step1.1 calculates the personal total score tableSelectSidsum(Cmark) SMK fromMarkGroup bySID step1.2 averages the total score tableSelect avg(SMK) from(Step1.1Step2, find out in the Personal score table to find
10002 elective courses Mleftjoin on s.sid =where Cmark - 5. Find out the names of all students who have failed courses Select from Left Join on S.sid=where cmark 6. Student number of at least 10002 elective courses SelectSid fromMark SC1where not exists(
Select * fromMark SC2whereSid=10002 and not exists
(Select * fromMarkwhereSid=Sc1.sid andCid=sc2.cid)) andSid!=10002 Group bySid 7. The names of students who have at least enrolled in all courses Zhang San elective SelectSid fromMar
Tags: default CTS alt position ACK ROM Operation Select ReviewOne, the operation of the table Tables: creating, modifying, deleting all existing tables can be viewed through the select * from sysobjects where xtype= ' U ' Use commas to separate multiple columns PRIMARY key:primary key non-empty: notnull Unique :unique defaults: Default() checking:check () foreign key:foreign KEY ( column name ) references table name ( column name ) For example:--Create a table Use practicedb CREATE TABLE ClassI
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.