Database Basics

Navicat to access mysql

Practice 1: Use Navicat to access mysql query syntax

Use test; Check the tables in the test libraryshowtables; # Check the contents of the scoreselect * fromscore; Select * from scoreselect name fromscore; Select * from score; select * from score; Separate columns with commas.select name,subject fromscore; Select * from score where name = cao caoselect * from score where name="Cao cao; Select * from score where name = cao Cao; select * from score where name = Cao Cao;select subject,score from score where name="Cao cao; # Summary of the above usage, find Cao Cao's Language resultsselect name,subject,score from score where name="Cao cao and subject='Chinese'; Find the names and scores of Liu Bei and Xiao Qiao in the score tableselect name,score from score where name='liu bei' or name='Joe';
Copy the code

The IP address, port number, username, and password are required for connecting to the database

1. Database statement query syntax:

selectThe results of1Results,2Results,3 fromThe name of the tablewherefield='field1Value 'or/andfield2='field2Value ";Copy the code

2. Separate search items by commas (,) and search conditions by and/or.

The asterisk (*) indicates that all contents are queried.

– Or # stands for comment, shortcut: Quick comment: CTRL +/ Uncomment CTRL + Shift +/

Example 2: Fuzzy query of the database (% represents placeholder zero or bit; _ stands for placeholder one)

use test;
select name from score where name like 'sun %' ;  Query the name of a person whose name starts with sun
select name from score where name like 'he _';  -- Query the name of a person whose name starts with sun
select name from score where name like '% and %';  -- Query the name of the person whose name contains a
select name from score where name like Joe '%' ;  Query the name of a person whose name ends with Joe
Copy the code

Example 3: Screening criteria

select * from student where age> =10;  Query all information about a person aged 10 or older
select * from student where age> =10 and age< =60; Query all information about a person between the ages of 10 and 60
select * from student where age <30  or  age>50;    Select * from age < 30 and age > 50
select * from student where age ! =30;  Query all information about people whose age is not equal to 30
select * from student where age<>30;  Query all information about people whose age is not equal to 30
Copy the code

Example 3: Remove duplication

select distinct name from score;  Remove the names of people who repeatedly query the score table.
Copy the code

Example 4: Nesting

Use test; Select * from * where name = 'cao', name = 'liu', name = 'sun'select ,name,score from score where name in("Cao cao.'liu bei'.'sun'); # query name not cao Cao liu Bei Sun Quan's name and score.select name,score from score where name not in("Cao cao.'liu bei'.'sun'); Select * from student where home address is nullselect * from student where home is null; Select * from student where home address is not emptyselect * from student where home is not null;
Copy the code

Example 4: Between.A and B.

Select * from student where age between 20 and 50;
select*from student where age between 20 and 50;
Copy the code

Example 5: The use of sort order by (default sort ascending, two sort conditions are separated by English comma)

selectThe results of1Results,2Results,3 fromThe name of the tablewhereconditionsorder byfield1 asc, the field2 desc"; # output all information about men and women in order of ageselect*from student where sex='woman' order by age desc; If the age is the same, output the information of the male and female in order of the serial numberselect*from student where sex='male' order by age asc,id desc;
Copy the code

Example 6: Take the first few lines of the content limit (after the number directly means to take the first few lines of the content limit, after the number means to take the first few lines of the content limit)

Take the first four linesselect * from student limit 4; # from the first3Line up to take4Line (take lines 4 to 7)select * from student limit 3.4;
Copy the code

Example 7: Aggregate functions (AVg,sum, Max,min,count)

select avg(age) from student;
select sum(age) from student;
select max(age) from student;
select min(age) from student;
select count(id)fromstudent; # Ask the oldest boyselect name,max(age) from student where sex='male';
Copy the code

Example 8: Rename AS. You can omit as and replace it with a space (temporarily, the new name is displayed in the query result).

select name asName, id,asAge agefrom student asst; Output student table name rename to name,age rename to age, and score table name rename to nameselect student.name asName, student ageasAge, score. The nameasThe namefrom student as st,score assc; # can be abbreviated asselectSt. name,st.age, SC. namefrom student st,score sc ;
Copy the code

Example 9: Multi-table query (at least one of the same information in multiple tables must be queried)

Select * from student; select * from student; select * from studentselect * from student st join score sc on st.name=sc.name;
Copy the code

Example 10: Grouping

USE test;
SELECT *FROM score WHERE name='zhou yu'; # Directly calculate the average score of zhou Yu's all grades (whereFor conditional filtering before grouping)SELECTName name,AVGAverage (score)FROM score WHERE  name='zhou yu'; # Divide people with the same name into groups and find the average score (HAVINGUsed for conditional filtering after grouping)SELECT name,avg(score) FROM score GROUP BY name HAVING name = 'zhou yu'; Find the average of each subjectSELECT subject,AVG(score) FROM score GROUP BYsubject; Get the total score for each subject and sort it in descending orderSELECT subject,SUM(score) FROM score GROUP BY subject ORDER BY SUM(score) DESC; Find the names of the top three peopleSELECT name FROM score GROUP BY name ORDER BY SUM(score) DESC LIMIT 3; # Top 3 in total score except cao Cao and Zhou YuSELECT name FROM score WHERE name NOT IN("Cao cao.'zhou yu') GROUP BY name ORDER BY SUM(score) DESC LIMIT 3;
SELECT name FROM score GROUP BY name HAVING name NOT IN("Cao cao.'zhou yu') ORDER BY SUM(score) DESC LIMIT 3;
SelectFinal query syntax (longest query syntax) :selectThe results of1Results,2Results,3 fromThe name of the tablewhereconditionsgroup byfieldhavingconditionsorder byfield1 asc, the field2 desc'limit Number of start rows, number of end rows;Copy the code

Syntax for adding, modifying, and deleting tables

1. Create tables:

Create tableTable name (field one field type (length), field2Field type (length));PrimaryKey Primary key: the primary key cannot be empty and cannot be repeated.Copy the code

2. Add content:

Insert intoTable name (field1, the field2, the field3)values(' field1The value of ', '2', 'value3'); (When adding content, write each field, and the value of each field corresponds to each field)Copy the code

Alter table alter table alter table alter table alter table

Update the table namesetfield1='field1New value ', field2='field2The new value 'whereModify a field in a row='Change the value of a field in a row';Copy the code

4. Delete data

(deleteandfromIf no condition is added, all data will be deleted.DeleteGeneral andwhereDelete part of the data together,truncateTo clear the table, the table structure is still there.dropDelete all data in the table directly, and the associated table will also be deleted. Delete speed:drop>truncate>delete)Copy the code
Delete fromThe name of the tablewhereConditions;Truncate tableThe name of the table.Drop tableThe name of the table.Alter tableThe name of the tableadd columnField name character type (character size); # add a columnAlter tableThe name of the table the modifycolumnField name character type (character size); # Change the character type of a columnAlter tableThe table name changecolumnOriginal field Name New field name Character Type (character size) # Modify field nameAlter tableThe name of the tabledrop columnThe field name. Delete a column of contentAlter tableThe original name of the table to renametoNew table name # table renameDESCThe name of the table. Check table structureSelect*fromThe name of the table1And the name of the table2Join two tables together (Cartesian product)Copy the code

Create table Lee

CREATE table Lee(id INT PRIMARY KEY,yingxiong CHAR(100),pifu VARCHAR(100),
changci INT,victory CHAR(10));
SELECT*FROMLee; Add content to the tableINSERT INTOusageINSERT INTO Lee(id,yingxiong,pifu,changci,victory) VALUES('1'.'Arthur'.Death Knight..'500'.'266');
UPDATE Lee SET changci='10000'WHERE yingxiong='huang'; # modify dataDELETE FROM Lee WHERE id='10';/TRUNCATE TABLE Lee;/DROP TABLELee; # delete dataALTER TABLE lee ADDoddsCHAR(6); Add a columnALTER TABLE lee MODIFY COLUMNoddsVARCHAR(12); # change the data type of the fieldALTER TABLELee CHANGE conferencepercent CHAR(10); # change the name of the fieldALTER TABLE lee DROP COLUMN percent; Drop a columnALTER TABLE wzry RENAME toLee# renameDESClee; Join two tables together (cartesian product)Select*from student st,teacher  order by st.name desc;
Copy the code

Subqueries, that is, nested, query from the inner layer to the outer layer. (A very complex content can be implemented step by step to simplify the problem)

1. Treat the SQL statement as a condition

Select * from student; select * from score; select * from student50The names, ages and home addresses of the above persons. (Bridge two tables with common field names)SELECT name,age,home FROM student WHERE name IN(
SELECT name FROM score WHERE subject='Chinese' AND score>50);
Copy the code

2. Treat the SQL statement as a table (it must be temporarily named for table processing)

Group the score table by person, then list the total score of each person, then sort the total score in descending order, then fetch the first three rows of data. That is, all the information of the top three people who have the highest total score in the subject in the score table.SELECT * from(SELECT * from (SELECT name,sumTotal score (score)from score 
GROUP BY name) st ORDER BYTotal scores of st.desc) stt LIMIT 3;
Copy the code

Multi-table joint search (there must be at least one kind of same information in multiple tables when querying)

1. Inner link (inner check, filter out non-shared information/do not display non-shared information) inner can be omitted

Select * from student; select * from student; select * from studentselect * from student st join score sc on st.name=sc.name;
select * from student st inner join score sc on st.name=sc.name;
Copy the code

Outer connection (outer connection, divided into left outer connection (left connection) right outer connection (right connection)

Select * from student; select * from student;SELECT*FROM student st LEFT JOIN score sc ON st.name=sc.name;
SELECT*FROM student st LEFT OUTER JOIN score sc ON st.name=sc.name; # join right (right table (score table) does not share data retention)SELECT*FROM student st RIGHT JOIN score sc ON st.name=sc.name;
SELECT*FROM student st RIGHT OUTER JOIN score sc ON st.name=sc.name; Select * from student; select * from score; select * from student50The names, ages and home addresses of the above persons.SELECT st.name,st.age,st.home FROM student st JOIN score sc on st.name=sc.name 
WHERE sc.subject='Chinese' AND sc.score>50;
Copy the code

Inner join, left join and right join difference:

Inner join is to output the same content in two tables and filter out the different content. The left link is based on the left table, first output all the information of the left table, and then output the content matching the right table and the left table, the mismatch in the form of empty value. The right link is based on the right table, first output all the information of the right table, and then the output of the content matching the left table and the right table, the mismatch in the form of empty value.

More than three tables in a multi-table search.

The middle table has the field names of the upper table and the field names of the lower table. Select student from score and teacher from scoreSELECT*FROM student st JOIN score sc ON st.name=sc.name JOIN teacher te ON sc.subject=te.subject;
SELECT*FROM student st LEFT JOIN score sc ON st.name=sc.name LEFT JOIN teacher te ON sc.subject=te.subject; Formatting timeSelect *fromThe name of the tablewhereDate_format (date_format)%Y_%m_%D)='xxxx_xxx_xx'; # format time:yearyearsmonthmonthdaydaySELECT*FROM mt WHERE DATE_FORMAT(create_time,'%Y_%m_%d')='2018 _03_30';
Copy the code

Data paging

SELECT*FROMMt LIMIT (page number- 1)*Number of lines, number of lines; # View data on a page # paginationSELECT*FROM zt_user;
SELECT*FROM zt_user LIMIT 0.5;
SELECT*FROM zt_user LIMIT 5.5;
SELECT*FROM zt_user LIMIT 10.5;
SELECT*FROM zt_user LIMIT 15.5; Transaction syntax:Starttransaction; Update the table namesetThe field name1='field1The value of 'whereThe field a=The value of field A1; Update the table namesetThe field name2='field2The value of 'whereThe field a=The value of field A2;Commit;
Copy the code

Use Navicat to access sqlserver

Practice 2: query syntax for accessing sqlserver using Navicat

Sqlserver in the use of comments/* Comment the content */or- -Comment content Seletct top several line field namesfromThe name of the tablewhereConditions;Copy the code
Sqlserver in the use of comments/* Comment the content */or- -Comment content Seletct top several line field namesfromThe name of the tablewhereConditions;Copy the code

Use PLSQLT to access Oracle

Update data:

select * from student;
/ * * /
insert into student values('020'.'zhaoyun'.'88'.'male'.'16652368965'.'shu');
commit;
/ * delete * /
delete from student where id = '020';
commit;
/ * modify * /
update student set age = '55' where name = "Cao cao;
commit; Step 1: # To make the table editable, typeSelect*from student for update;
Commit; Step 2: Click the small lock in the upper left corner of the table to release the lock, and then edit the data. Step 3: After modifying the data (add, delete and modify), click the check mark, and then click the small lock to lock the table. Then click the gear to execute.Copy the code

Formatting time:

select*fromTable name to_char(time corresponding field name, XXXX_xx_xx)='1998_05_03 ';Format time Year: YYYY Month: MM Day: DD
select *from scott.emp;
select *from scott.emp where to_char(hiredate,'yyyy')='1981';
select *from scott.emp where to_char(hiredate,'yyyy_mm_dd')='1981 _12_03';
 
-- Take the first few lines
select *from student where rownum< =3;
select *from student where rownum< =9;
commit;
-- Take line to line
Take lines 3 through 8
select*from(
select rownum r,st.*from student st) b where b.r > =3 and b.r< =8;
Copy the code

Database theory knowledge:

Database concepts:

SQLStructured Query LanguageCopy the code

Type of database:

Relational database: Mysql SQLserver Oracle DB2 Access Informix VF Non-relational database: redis mongodbCopy the code

LAMP:

Linux, Apache, MySQL, PHP

Grammar differences (can recite)

Mysql uses limit, sqlserver uses top, and Oracle uses rownum. Oracle data is required to perform data changescommitOperation (data change: add delete modify)Copy the code

Database stored procedures:

Is a precompiled SQL statement with the advantage of allowing a modular design that the user creates once and can call again later.Copy the code

Index: Speeds up the query of data, similar to the table of contents of a book

Index is a special query table, which can be used to speed up data retrieval. Index is similar to the table of a book. In a book, through the table of a book, you can quickly find the required information from the book without flipping through the whole book, while index is a database that can quickly find the required data in the table without scanning all the database. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down the data entry speed and increases the size of the database.

Delete speed DROP > TRUNCate > DELETE

Primary and foreign keys

Primary key: A data column in A database table that uniquely identifies data objects. (The primary key cannot be empty and cannot be repeated.) Foreign key: If table A has A name(not the primary key of A) and table B has A name(the primary key of table B), name in table A is the foreign key of table B

Transactions:

A set of logical operations in a database that are characterized by either all success or all failure. Start transaction;

Starttransaction; Update the table namesetThe field name1='field1The value of 'whereThe field a=The value of field A1; Update the table namesetThe field name2='field2The value of 'whereThe field a=The value of field A2;Commit;
Copy the code

Start transaction;

Update table_name set table_name = ‘1’ where table_name = ‘1’; Update table_name set table_name = 1 where table_name = 1 where table_name = 1; Commit;

What is outer join: refers to left outer join and right outer join

Inner join is to output the same content in two tables and filter out the different content. The left link is based on the left table, first output all the information of the left table, and then output the content matching the right table and the left table, the mismatch in the form of empty value. The right join is the opposite.

Query table structure Desc;

The keyword to be duplicated in the database: DISTINCT

Maximum number of tables that can be queried in a multi-table lookup: A multi-table lookup can be performed as long as the same fields exist

Select *from tableA, tableB;

Database execution plan (query plan) :

When the database executes SQL statements, it prepares several plans and finally selects the plan that consumes the least resources to execute, which is the execution plan.

The differences between the three databases:

MYSQL

Default port number developed by AB (Oracle) : 3306 Client connection tool: Navicat, SQLyog 1. Relational database 2. open source 3. fast, reliable and easy to use 4. cross-platform 5. A lot of startups use it

SQLserver

Microsoft developed the default port number: 1433 client connection tool: navicat can only run on Windows e-commerce banking insurance power these industries also use SQLserver

Oracle

Oracle development default port: 1521 Client connection tool: PLSQL 1. Support multi-user, large transaction volume processing 2. High data security 3. Cross-platform

Syntax differences of the three databases: 1. All three databases use structured Query Language (STRUCTURED Query Language) 1. SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL

Where is the keyword in reverse order: it is in the database (desc).

How to use for update in Oracle: To make an Oracle table editable

  1. Execute for update statement eg:select*from a for update;
  2. Points to open small lock
  3. Modify the data
  4. Click the checkmark
  5. Close small lock
  6. Click on the submit

What to do when you are new to your job:

1. What database does the company use? 3. Then ask the company what database the business needs, what account number, port number and IP are. What are the tables you need? What are the fields in the table? What does each field mean? 4. Write SQL statements and add comments. After writing SQL statements, save the files and call them again.

Software testing professional books

Blog Source: Blog of rainy Night