1. Create a database named text. Note that the ` ` symbol is the symbol below the Esc key in the upper left corner of the keyboard, not a comma. (if you use SQLyog to create a list, remember to refresh the list!!!)
Format: create database database name
CREATE DATABASE `text`
2. Create a table
CREATE TABLE IF NOT EXISTS `student`( `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT 'Student number', `name` VARCHAR(10) NOT NULL DEFAULT 'nameless' COMMENT 'name', `sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender', PRIMARY KEY(id) )ENGINE=INNODB DEFAULT CHARSET=utf8
NOT NULL means that the content of the filled element cannot be empty
DEFAULT indicates the DEFAULT value of this element
COMMENT indicates the value to be filled in for this element. Prompt
PRIMARY KEY(id) sets the id element as the primary key. Generally, a table has only one primary key
ENGINE=INNODB DEFAULT CHARSET=utf8 set the engine to INNODB and the document format to UTF-8
IF NOT EXISTS
Function of primary key: the primary key of a database refers to a column or a combination of columns, and its value can uniquely label each row in the table. The primary key can be used to represent a specific row that is precisely located. If there is no primary key, it is impossible to accurately locate whether a record is the relevant row record you want, which will make it difficult to update or delete a specific row in the table. If there is a primary key to restrict the uniqueness of row records, you can use the primary key to solve the problem.
Role of the storage engine: the storage engine serves the storage service and saves data through the storage engine. Just like how a computer saves data to disk, in a database, a storage engine means what kind of engine stores data on disk.
3. Insert a statement into the table
Format: insert into table name (field 1, field 2,...) values('value 1 ',' value 2 ',...), (' value 1 ',' value 2 ',...)
Note: data must correspond to fields.
INSERT INTO `student`(`id`,`name`)VALUES('001','Zhang San'),('002','Li Si'),('003','Wang Wu')
2. Delete
1. Delete fields in the table
Format: ALTER TABLE table name DROP field name
ALTER TABLE student DROP age
2. Delete data
Format: DELETE FROM table name TRUNCATE TABLE table name
DELETE FROM `student` TRUNCATE TABLE `student`
3. Change
1. Modify table
Format: ALTER TABLE database name MODIFY table column attribute
Format: ALTER TABLE database name CHANGE old table name new table column attribute
ALTER TABLE student MODIFY sex VARCHAR(10) ALTER TABLE student CHANGE sex age INT(10)
2. Modify the value corresponding to the field in the table
Format: update table name set field name to be modified = 'new value' where constraint
UPDATE `student` SET `name`='Xiao Zhang' WHERE id=1
Note: constraints are necessary. If no constraints are added, the field values specified in the whole table will be modified. If there are multiple constraints, they can be connected with and and or
4. Check (key points)
1. Basic query
select statement
--Show student All information in the table-- SELECT * FROM student --Show only student Some information in the table-- SELECT `studentno`,`loginpwd`,`address`,`borndate` FROM student
In general, we make the information in the form easier to understand. The extracted fields are usually aliased in the select statement. Use the as keyword, as can be omitted, with a space in the middle.
SELECT `studentno` Student number,`loginpwd` password,`address` Home address,`borndate` date of birth FROM student
Used with CONCAT connection string function:
usage method:
SELECT CONCAT('Student No.:',`studentno`) Student number,`loginpwd` password,`address` Home address,`borndate` date of birth FROM student
De duplication: (de duplication)
Key words: DISTINCT
--Filter out subjects with the same class hours--- SELECT DISTINCT `classhour` FROM SUBJECT
2. Fuzzy query:
1. Key words: like
--Query the person surnamed Li whose name is two words-- SELECT studentname FROM student WHERE studentname LIKE 'Lee_' --Query the person whose last name is Lian whose first name is three words-- SELECT studentname FROM student WHERE studentname LIKE 'Practice__' --Search for people with "Na" in their names-- SELECT studentname FROM student WHERE studentname LIKE '%Na%'
Note: an underscore "" represents a character, and a "%" represents an uncertain number of characters.
2. Key words: in
--Students whose address is "Chaoyang, Beijing"-- SELECT studentname,sex FROM student WHERE address IN('Chaoyang, Beijing')
Note: in can only be followed by a specific value, not a fuzzy value! In other words, unlike like, it cannot query a certain range of values, but only specific values. Otherwise, the result is contrary to expectations.
3. Keyword: is null (is not null)
--Query students without birthdays-- SELECT studentname,sex FROM student WHERE borndate IS NULL --Query students with birthdays-- SELECT studentname,sex, borndate FROM student WHERE borndate IS NOT NULL
4. Key words: between
--Query students with student numbers between 1000 and 1004, including 1000 and 1004-- SELECT studentname,sex, borndate FROM student WHERE studentno BETWEEN 1000 AND 1004
3. Associated table query:
First, briefly introduce the Cartesian product in the database: the implementation of Cartesian product in SQL is cross join. All connection methods will form a temporary Cartesian product table. Cartesian product is a concept in relational algebra, which represents any combination of data in each row of the two tables. The connection of the two tables in the above figure is Cartesian product (cross connection)
inner join:
SELECT s.studentno,studentname,sex,subjectno,studentresult FROM student s INNER JOIN result r WHERE s.studentno=r.studentno
Note: the intersection of the two is taken, and the other parts unique to the two are filtered out.
left join:
SELECT s.studentno,studentname,sex,subjectno,studentresult FROM student s LEFT JOIN result r ON s.studentno=r.studentno
Note: take all the elements in the left table (student here) and filter out the unique elements in the right table.
right join:
SELECT s.studentno,studentname,sex,studentresult FROM result r RIGHT JOIN student s ON s.studentno=r.studentno
Note: take all the elements in the right table (student here) and filter out the unique elements in the left table.