Look at the catalogue. Are you going to run away? Don't panic. All the examples listed are examples. You can use this article whether you are a student party or just engaged in work. You can understand it at a glance. It's done without looking at copy and paste. It's good enough for testing. It's also good for those who are new to the industry but not in the testing field. This article is also for your own use. The directory name is straightforward. It is mainly convenient to go straight to the topic copy.
catalogue
Query database basic information
Field information of query table
Modify field name and data type
Query records ending in a string
limit combined with order by query
Subquery of comparison operator
preface
Before learning databases, you must first install databases, such as Mysql, Oracle, SQL Server and Sqlite. These databases are OK. Mysql has good performance and is suitable for all platforms. It is open source. It is the most popular relational database. SQLserver database has good expansibility, maintainability and security. It is a relatively comprehensive database. Another mainstream database is Oracle. Oracle database is suitable for large databases, and Mysql and SQLserver are suitable for small and medium-sized databases. Generally, Mysql can be used. It is light-weight and has good performance.
concept
During database operation, special database operation rules and syntax need to be used. This syntax is SQL(Structured Query Language) structured query language.
The main function of SQL is to establish a connection with the database to add, delete, modify and query. SQL is the standard language of relational database management system.
The database server is actually a software, such as the MySQL software (or mariadb software) we installed. The MySQL server software needs to be installed on the server hardware (that is, a computer) before it can be accessed by the outside world.
What is SQL language:
1. Data definition language (DDL). Used to create databases and data tables.
2. Data manipulation language (DML). It is used to insert, modify and delete data from the data table.
3. Data query language (DQL). Used to query data from a data table.
4. Data control language (DCL). Used to set or modify the permissions of database users or roles.
Note:
When using SQL to operate a database, all SQL statements end with a semicolon. (you can switch databases without semicolons)
In SQL statements, it is not case sensitive. When writing SQL statements, you can use case differences to increase readability according to the situation.
data type
Common data types are:
1. Integer type (INT) placeholder 4 bytes
2. There are two floating-point types: single precision floating-point type (FLOAT) occupying 4 bytes and double precision floating-point type (DOUBLE) occupying 8 bytes
3. String type (CHAR) occupies 3 bytes and (VARCHAR)4 bytes
4. Date and time type (YEAR) accounts for 1 byte, (DATE) for 4 bytes and (TIME) for 3 bytes.
basic operation
Create a database, create a table, add, modify, delete forms, and add, delete, modify and query! Build a library at will. MySQL has no strict requirements on the case of letters. You must specify whether you use uppercase or lowercase. You are free, happy in uppercase and unhappy in lowercase. 😕
Create database
create database Database name; create database qingan;
Query database basic information
show create database Database name; show create database qingan;
Use database
use Database name; use qingan;
Create data table
Here we will use all the above examples, learn and apply them flexibly, and apply what we have learned. 💗， From database creation to table creation
CREATE DATABASE qingan; use qingan; create table wubieshi( id varchar(18), sex char(10), c_name varchar(10) );
query form
show tables;
Query form basic information
show create table Form name; show create table wubieshi;
Field information of query table
desc Form name; desc wubieshi;
Modify table name
alter table Original form name rename to Name you want to modify; alter table wubieshi rename to qing;
Modify field name and data type
Here, because the table name has been changed above, the table name "qing" will be used later.
alter table Form name change Original field name modify field name data type; alter table qing change id id_number varchar(40);
Modify data type
alter table Table name modify Field name data type; alter table qing modify id varchar(10);
Add field
alter table Table name add Add field name data type; alter table qing add c_name varchar(30);
Delete field
alter table Table name drop Field name; alter table t_beauty drop c_name;
Delete data table
drop table Table name; drop table qing;
Add, delete, modify and query
Let's simplify here. Don't be so troublesome, so you must clear it when you build the database and table. Don't delete or modify it later. You've made a mistake.
Add:
INSERT INTO Table name (field name 1),Field name 2,...) VALUES (Value 1,Value 2,...);
insert into qing(id,sex,home,call_num) VALUES('Xiao Fang','female','Beijing',132465);Add multiple pieces of data at the same time:
insert into qing(id,sex,home,call_num) VALUES('Xiao Fang','female','Beijing',132465),
('Little','female','Shanghai',777555),
('Xiaomu','male','Guangdong',1525465)Change:
This is to change the search id of Qingxue to Xiaofang. Don't get confused.
UPDATE Table name SET Field name 1=Value 1 [WHERE Conditional expression]; update qing set id = 'Xiao Fang' where id='Clear snow';
Specify a field data and change all id field names to small north
update student set id='Xiaobei';
Delete:
DELETE FROM Table name [WHERE Conditional expression]; delete from qing where id = 'Little';
Delete all data:
delete from Table 1; delete from wubieshi;
Check:
select * from Table name; select * from wubieshi;
Query specified fields:
select Field name 1,Field name 2 from Table name; select id,name form qing;
Filter duplicate information:
Filter out duplicate name
select distinct Field name from Table name; select distinct name from qing;
Aggregate function
count
How many people are there in the query table
select count(*) from Table name; select count(*) from qing;
max
Calculate the maximum value of the specified column. This is just an example. No one will calculate the id, which is meaningless.
select max(Field name) from Table name; sele max(id) from qing;
min
Calculate the minimum value of the index column
select min(Field name) from Table name; select min(id) from qing;
sum
Sum
select sum(Field name) from Table name; select sum(id) from qing;
avg
Average
select avg(Field name) from Table name; select avg(id) from qing;
Relational operator
| Relational operator | explain |
| = | be equal to |
| <> | Not equal to |
| < | less than |
| > | greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| != | Not equal to |
Relational operators don't need to be memorized, but they must be able to. After all, they still need to be used later. Look at the use
select * from student where Field name>=condition; select * from student where age>=17;
Keyword query
in
Used to determine whether the value of a field is in the specified set
select * from Table name where Field name in ('Field 1');
select * from qing where id in ('Mu Xue');not in
It is used to judge whether the value of a field is not in the specified set or not
select * from Table name where Field name not in ('Field 1');
select * from qing where id not in ('Mu Xue');between and
select * from Table name where Field name between Condition 1 and Condition 2; select * from qing where age between 15 and 18;
not between and
select * from Table name where Field name not between Condition 1 and Condition 2; select * from qing where age not between 15 and 18;
Reverse null query
Values (fields) in the query table that are not null
select * from Table name where Field name is not null; select * from qing where id is not null;
and query
select * from Table name where Conditional judgment and Conditional judgment; select * from qing where age>18 and id='Mu Xue';
or query
select * from Table name where Condition query or Condition query; select * from qing where age>15 or id='Mu Xue';
String matching query
Normal string query
Query the person with id 'mu Xue'
select * from Table name where Field name like character string; select * from qing where id like 'Mu Xue';
%Wildcard string query
Query the person with id 'mu Xue ~', such as Mu Xue, Mu Xue Xue, Mu Xue, etc
select * from Table name where Field name like 'Field 1%'; select * from qing where id like 'Mu Xue%';
Query records ending in a string
Query records ending with '~ snow', such as bathing snow, summer snow, etc
select * from Table name where Field name like '%Field 1'; select * from qing where id like '%snow';
Generic string query
The underscore wildcard only matches a single character. If you want to match multiple characters, you need to use multiple underscore wildcards continuously. A single underscore matches only one string, and two underscores match multiple strings.
select * from Table name where Field name like 'Field 1__'; select * from qing where id like 'x__'; select * from qing where id like 'x_';
Limit limit query
select * from qing limit i,n;
#qing: table name
#i: the index value of the query result (starting from 0 by default). When i=0, i can be omitted
#n: the quantity returned for the query result
#i and n are separated by an English comma ","
Query 5 pieces of data, starting from 3 and then 5 pieces of data
select * from qing limit 2,5;
order by query
ORDER BY is sorted from small to large by default. asc is also sorted from small to large. It can be ignored and not written. desc is sorted from large to small
select * from Table name order by Field name asc; select * from qing order by age asc;
select * from Table name order by Field name desc; select * from qing order by age desc;
limit combined with order by query
select * from qing order by age asc limit 5;
group by
Take the same value from multiple columns, as shown in table t_ Take the same field name id value in beauty
select count(*), Field name from Table name group by Field name; select count(*), id from qing group by id;
Use with where
Query the data with ID > 1001 in the table
select count(*), Field name from Table name where Field name>value group by Field name; select count(*), id from qing where id>1001 group by t_beauty;
Using with having
Query the data with salary greater than 2000 in the class. It is worth noting that having must be followed by aggregation function
select sum(Field name 1),Field name 2 from Table name group by Field name 2 having sum(Field name 1)>value; select sum(salary),class from qing group by calss having sum(salary)>2000;
Data table constraints
Primary key constraint
create table qing( id int primary key, names varchar(20) );
Non NULL constraint
create table qing( id int, names varchar(20) not null );
Default constraint
The default value is given
create table qing( id int, names varchar(20), gender varchar(10) default 'cha' );
Unique constraint
create table qing( id int, names varchar(20) unique );
Foreign key constraint
CONSTRAINT fk_qing FOREIGN KEY(Id) REFERENCES qingan(id) CONSTRAINT From table FOREIGN KEY(Field name) REFERENCES Main table(Field name)
create table qing( id int, names varchar(20) unique, CONSTRAINT fk_qing FOREIGN KEY(Id) REFERENCES qingan(id) );
# After creating a data table number, the syntax is as follows: ALTER TABLE From table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field from table) REFERENCES Main table (Primary key field); # Delete foreign key constraint alter table From table name drop foreign key Foreign key name;
Alias
select * from Original table name as Table name; select * from qing as qingan;
select Original field name as Field name from Table name; select id as c_id from qingan;
Table Association query
It is applicable to the same data between tables, such as the same values in nname and CNAME fields in class and classroom1
select * from Table name 1 where Field name 1=(select Field name 2 from Table name 2 where Field name 3='value'); select * from class where nname=(select cname from classroom1 where cid=2);
Association deletion
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id delete from surface where Field name=value; delete from qing where id=1;
Multi table join query
SELECT * FROM Table 1 CROSS JOIN Table 2; select * from qing cross join qingan;
Internal connection query
Inner join, also known as simple connection or natural connection, is a very common connection query. Inner join uses comparison operators to compare the data in two tables and list the data rows that match the join conditions to form a new record.
Here are the fields with the same query id:
SELECT Table name 1.field,Table name 2.field, ... FROM Table 1 [INNER] JOIN Table 2 ON Table 2.Relation field=Table 1.Relation field SELECT qing.id,qingan.cid FROM qing INNER JOIN qingan ON qingan.cid = qing.id;
If there are three tables, I need to query the name with the same id:
SELECT qing.id,qing.bname,qingan.gname FROM qing INNER JOIN qingan ON qingan.hid = qing.hid;
External connection query
External connection is divided into left external connection and right external connection. Its usage is similar. One table is the master table and the other is the slave table.
1. LEFT [OUTER] JOIN left (outer) join: returns all records in the left table and records matching the connection conditions in the right table.
2. RIGHT [OUTER] JOIN right (outer) join: returns all records in the right table and records matching the join conditions in the left table.
Note: the field names can be different when associating queries. At that time, the data types need to be consistent. For the right outer connection, you only need to change the following example left to right. Pay attention to the master-slave table
Note: external connection query can also be deleted!!!
select Table 1.Field 1,Table 1.Field 2,Table 2.Field 3 from Table 1 left outer join Table 2 on Table 1.Relationship field 1=Table 2.Relationship field 2; select class.did,class.dname,student.sname from class left outer join student on class.did=student.classid;
Subquery
Subquery refers to a query in which one query statement is nested within another query statement;
Subquery of comparison operator
Query qingan's class information
select * from Table 1 where Field name 1>(select Field name 2 from Table 2 where Field name 3=Query data); select * from d_class where c_id>(select d_id from d_student where d_name='qingan');
exists
The parameter after the exists keyword can be any sub query. It does not produce any data, and only returns TRUE or FALSE. When the return value is TRUE, the outer query will be executed
If qingan is in the student table, query all class information from the class table (see example:)
select * from Table 1 where exists (select * from Table 2 where Field name 2=Query value); select * from d_class where exists (select * from d_student where d_name='qingan');
any
Any keyword means to return a result as an outer query condition if any of the conditions are met.
Query the id whose class id is larger than student id (see the example)
select * from Table 1 where Field 1 > any (select Field 2 from Table 2); select * from d_class where d_id > any (select c_id from student);
all
The all keyword is somewhat similar to any, but the results returned from the sub query of the all keyword must meet all internal query conditions at the same time
Query the id larger than the id of the class to which the student belongs (see the example)
select * from Table 1 where Field 1 > all (select Field 2 from Table 2); select * from class where d_id > all (select c_id from student);
Here's the database. It's almost enough for testing. I've given examples to a friend. Give me a comment and praise. I haven't written a database for a long time. This is also the blogger's own review. I hope it is also useful for everyone.