MySQL simple operation for tables and tables-insert data-delete primary key-delete table-Modify table data-delete table data

--1. Working with databases
Use Ren;


--2. Create a student table
CREATE TABLE Student
(
Sid INT UNSIGNED PRIMARY KEY auto_increment,--UNSIGNED (unsigned) auto_increment (self-increment)
Sname VARCHAR (10),--only 10 characters can be saved
Saddress VARCHAR (15)-only 15 characters are saved
) Engine=myisam DEFAULT Charset=utf8; --Specify character encoding








--3. Inserting data
INSERT into student VALUES (NULL, ' Liyi ', ' Zhengzhou '); --Be careful not to forget the VALUES keyword Note that punctuation is in the English state
INSERT into student VALUES (NULL, ' Liyi 1 ', ' Zhengzhou ');
INSERT into student VALUES (NULL, ' Liyi 2 ', ' Zhengzhou t ');
INSERT into student VALUES (NULL, ' Liyi 3 ', ' Zhengzhou ');
INSERT into student VALUES (NULL, ' Liyi 4 ', ' Zhengzhou s ');
INSERT into student VALUES (NULL, ' Liyi 5 ', ' Zhengzhou h ');
INSERT into student VALUES (NULL, ' Liyi 6 ', ' Zhengzhou g ');




--3.1 Exercises to remove the primary key-------------------------
--ALTER TABLE student drop primary key;--question 1-
-- ----------------------------------------




--4. Querying all content in the student table
SELECT * FROM Student




--5. Delete Table Student
DROP TABLE student;




--6. Modify the table's data
UPDATE student SET sname= ' John Doe ', saddress= ' Xuchang ' WHERE sid=1; --where is the condition for modifying that piece of data
UPDATE student SET sname= ' Harry ', saddress= ' Beijing ' WHERE sid=6; --where is the condition for modifying that piece of data


--7. Delete data from a table
DELETE from student WHERE sid=2; --where is the condition after which data is deleted
DELETE from student WHERE sname= ' Harry '; --where is the condition after which data is deleted
DELETE from student WHERE sid=9; --where is the condition after which data is deleted
DELETE from student WHERE sid=8; --where is the condition after which data is deleted
DELETE from student WHERE sid=1;
DELETE from student WHERE sid=3;
DELETE from student WHERE sid=4;
DELETE from student WHERE sid=5;
DELETE from student WHERE sid=7;






--8. Establish two representations of a database
CREATE DATABASE IF not EXISTS ren1;
CREATE DATABASE IF not EXISTS ' ren2 ';
--If database db does not exist, set up, character set with UTF8 character set
CREATE DATABASE IF not EXISTS ' db ' DEFAULT CHARACTER SET UTF8;


--9. Deleting a database
DROP DATABASE IF EXISTS ren2;
DROP DATABASE IF EXISTS ren1;




--10. View the Database
SHOW DATABASES;


--11. Common Information functions
SELECT VERSION (); --Current database server version information


SELECT DATABASE (); --Query the current library


SELECT Current_User (); --View the current user


SELECT USER (); --View the current user


SELECT curdate (); --View the current user


SELECT current_date (); --View the current date


SELECT Curtime (); --View the current time


SELECT Current_time (); --View the current time


SELECT now (); --View the current date + time


SHOW TABLES; --Show all tables of the current library


SHOW DATABASES; --Show All libraries


SHOW CREATE DATABASE Ren; --View standard Build database statements


----------------View table field information
-- ---------------------------------------------
--Show full columns from studentinfo;--problem 2--
-- --------------------------------------------


SHOW CHARSET;


SHOW ENGINES;


--12. Modify the table name
ALTER TABLE student RENAME s;
ALTER TABLE s RENAME student;


--13. Modifying column names and types
ALTER TABLE student Change Sid Sno INT (Ten) UNSIGNED;


--14. Add a column at the end of the table--------------------------------------------
--ALTER TABLE student add column Sscore tinyint unsigned;--problem 3--
-- -----------------------------------------------------------------

MySQL simple operation for tables and tables-insert data-delete primary key-delete table-Modify table data-delete table data