--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
