Table of Contents
Modify table
CRUD (add, delete, modify, check)
insert statement (add data to the table)
update statement (modify data in the table)
delete delete statement
select statement
Modify table
Add column
ALTER TABLE tablename
ADD (column datatype [DEFAULT expr] [, column datatype] …);
Modify column
ALTER TABLE tablename
MODIFY (column datatype [DEFAULT expr] [, column datatype] …);
Delete column
ALTER TABLE tablename
DROP(column)
View the structure of the table: desc table name;
Modify table name: Rename table table name to new table name
Modify table character set: alter table table name character set character set;
#Operation exercises for modifying tables
#Add an image column to the employee table emp, varchar type (required after resume)
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER RESUME
DESC emp #Display the structure of the table and view all columns of the table
#Modify the job column so that its length is 60
AFTER TABLE emp
MODIFY job VARCHAR (60) NOT NULL DEFAULT ''
AFTER TABLE emp
DROP sex
#Indicates changing to employee
RENAME TABLE emp TO employee
#Modify the character set of the table utf8
AFTER TABLE employer CHARSET utf8
#Change the column name to user-name
AFTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
CRUD (Create, Delete, Modify and Check)
insert statement (add data to the table)
Use the INSERT statement to insert data into the table
Quick start case:
1. Create a product table goods(id int, goods_name varchar(10), price double);
2. Add two records
mysql code
#Practice insert statement
#Create a product table goods(id int, goods_name varchar(10), price double);
#Add 2 records
CREATE TABLE `goods`(
id INT, goods_name VARCHAR(10),price DOUBLE);
#adding data
INSERT INTO `goods` (id ,goods_name,price)
VALUES (10,'Huawei mobile phone','2000');
INSERT INTO `goods` (id ,goods_name,price)
VALUES (20,'iPhone','3000');
SELECT * FROM `goods`
Pay attention to details when using insert
#Practice insert statement
#Create a product table goods(id int, goods_name varchar(10), price double);
#Add 2 records
CREATE TABLE `goods`(
id INT, goods_name VARCHAR(10),price DOUBLE NOT NULL);
#adding data
INSERT INTO `goods` (id ,goods_name,price)
VALUES (10,'Huawei mobile phone','2000');
INSERT INTO `goods` (id ,goods_name,price)
VALUES (20,'iPhone','3000');
#Attention to detail
#1. The inserted data should be the same as the data type of the field. For example, adding 'abc' to the int type will cause an error.
INSERT INTO `goods`(id,goods_name,price) VALUES('abc','Xiaomi mobile phone',2000);
#2. The length of the data should be within the specified range of the column. For example, a string of length 80 cannot be added to a column of length 40.
INSERT INTO `goods`(id,goods_name,price)
VALUES(40,'vovo mobile phone vovo mobile phone vovo mobile phone vovo mobile phone',3000);
#3. The data position listed in values must correspond to the arrangement position of the column being added.
INSERT INTO `goods` (id,goods_name,price)
VALUES('vovo mobile phone',40,2000);
#4.Character and date data should be enclosed in single quotes
INSERT INTO `goods`(id,goods_name,price)
VALUES(40,'vovo mobile phone',3000);
#5. Columns can be inserted into null values (provided that the field is allowed to be null), insert into table value (null), when creating the table
#NOT NULL written
#It won’t work if you write it, but it won’t work if you follow it
-- CREATE TABLE `goods`(
-- id INT, goods_name VARCHAR(10),price DOUBLE NOT NULL);
INSERT INTO `goods`(id,goods_name,price)
VALUES(40,'vovo mobile phone',NULL);
#6.insert into tab_name (column name..) values(),(),(),()...
INSERT INTO `goods`(id,goods_name,price)
VALUES(50,'Samsung mobile phone',2300)(60,'Haier mobile phone',1800);
#7. If you are adding data to all fields in the table, you do not need to write the previous field name.
INSERT INTO `goods`
VALUES(40,'Big Brother',50000);
#8. The use of default values. When a field value is not given, if there is a default value, the default value will be added, otherwise an error will be reported.
#If a column does not specify not null, then when adding data, if there is no given value, it will default to null.
#If we want to specify the default value of a certain column, command when creating the table
-- CREATE TABLE `goods`(
-- id INT, goods_name VARCHAR(10),price DOUBLE NOT NULL DEFAULT 100);
INSERT INTO `goods`(id,goods_name)
VALUES(80,'Nokia')
update statement (modify data in the table)
#Demo update statement
#Create an employee table
CREATE TABLE employee(
id INT ,
user_name VARCHAR(20),
birthday DATE,
entry_date DATETIME,
job VARCHAR(20),
salary INT,
`resume` TEXT);
INSERT INTO employee
VALUES (200,'Little Monster','2010-11-11','2010-11-11 11:11:11','Mountain Patroller',3000,
'The king asked me to patrol the mountain')
SELECT * FROM employee;
#Requirement: Modify the records in the employee table created above
#1. Modify the salary of all employees to 5000. If there is no where condition, all records will be modified, so be careful.
UPDATE employee SET salary=5000
SELECT * FROM employee;
#2. Modify the salary of the employee named Little Monster to 3,000 yuan
UPDATE employee
SET salary = 3000
WHERE user_name = 'little monster'
SELECT * FROM employee;
#3.Create old monster
INSERT INTO employee
VALUES (100,'Old Monster','1990-11-11','1990-11-11 11:11:11','Backbeater',4000,
'The king asked me to beat my back')
SELECT * FROM employee;
#4. Increase the old monster’s salary by 1000 on the original basis
UPDATE employee
SET salary = salary + 1000
WHERE user_name= "Old Monster"
SELECT * FROM employee;
delete delete statement
A column cannot be deleted, you can use update to set it to null or ”)
delete from tb1_name
[WHERE where_definition]
After deleting all the data, the table still exists. What should we do if we want to delete the table?
DROP TABLE employee;
select statement
basic grammar
SELECT [DISTINCT] *|{column1, column2,column3} FROM tablename
Note: Select specifies which columns to query data column specifies the column name * means query all columns
FROM specifies which table to query. DISTINCT is optional and refers to whether to remove duplicate data when displaying results.
Use expressions to operate on query columns
SELECT *|{column1 | expression,column2| expression} FROM tablename;
The as statement can be used in the select statement
SELECT columnname as alias from table name;
in where clause
#select statement
#Check the scores of students named Zhao Yun
SELECT * FROM student
WHERE `name` = "Zhao Yun"
#Check students whose English scores are greater than 90 points
SELECT * FROM student
WHERE english > 90
#Query all students whose total score is greater than 200 points
SELECT * FROM student
WHERE (chinese + english + math) > 200;
#Query the scores of students whose math is greater than 60 and (and)id is greater than 4
SELECT * FROM student
WHERE (math>60) AND (id > 4)
#Check students whose English scores are greater than their Chinese scores
SELECT * FROM student
WHERE english>chinese
#Query students named Zhao whose total score is greater than 200 points and whose math scores are lower than their Chinese scores.
#赵% means those whose names start with Zhao are fine
SELECT * FROM student
WHERE ((chinese + english + math) > 200) AND (math<chinese) AND `name` LIKE '赵%'
#Check students whose English scores are between 80-90
SELECT * FROM student
WHERE english <=90 AND english >=80
SELECT * FROM student
WHERE ENGLISH BETWEEN 80 AND 90
#Check students with math scores of 89,90,91
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91
SELECT * FROM student
WHERE math IN (89,90,91);
Sort query results using the order by clause
SELECT column1,column2,column3… FROM table;
order by column asc | desc,….
1.order by specifies the sorting column. The sorting column can be the column name in the table or the column name specified after the select statement.
2.Asc ascending order (default), Desc descending order
3. The ORDER BY clause should be located at the end of the SELECT statement
#select statement
#order by use
#Output after sorting the math scores [ascending order]
SELECT * FROM student
ORDER BY math;
#Output the total score in order from high to low [descending order]
SELECT `name`,(math + chinese + english) AS 'Total score' FROM student
ORDER BY (math + chinese + english) DESC;
# Sort and output the results of the surname Li (ascending order)
SELECT * FROM student
WHERE `name` LIKE '李%'
ORDER BY math
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77980 people are learning the system