1. Create databases and Users--Change Password ctrl+shift+c/r Add/Cancel Comment
SET PASSWORD for [email protected]=password (' new password ');
--Query all the databases
SHOW DATABASES;
--Create a database
CREATE DATABASE IF not EXISTS T11;
--Delete Database
DROP DATABASE T11;
--Switch to the specified database
Use MySQL;
--Query all users in the user table in the MySQL database
SELECT ' host ', ' User ' from Mysql.user
--' anti-quote ' difference keyword
--Create User
CREATE USER bdqn identified by ' BDQN ';
--Authorization to the user
GRANT all on *. * to BDQN;
--Delete User
DELETE from Mysql.user WHERE user= ' bdqn '3. Create a modification table--Create a Student information table
CREATE TABLE Student (
Stuno INT (4) Not NULL PRIMARY KEY,
Stuname VARCHAR () not NULL,
Stuage INT (3)--cannot be added,
)
--Modify Table name
ALTER TABLE student RENAME as Stu;
--Add fields to the table
ALTER TABLE Stu ADD stusex VARCHAR (2);
--Modifying the types of fields in a table
ALTER TABLE Stu MODIFY stuname VARCHAR (10);
--delete the specified field from the table
ALTER TABLE Stu DROP stusex;
--Modify the name of the field
ALTER TABLE stu Change stuname ' name ' VARCHAR (10);4. Delete a table--Delete Table
DROP TABLE IF EXISTS student;
--01. Add a new piece of data
INSERT into student (Stuno, ' name ', stuage)
VALUES (1, ' Zhang San ', 50);
--02. A new data must be assigned in the order in which the table was created
INSERT into student
VALUES (2, ' John Doe ', 50)
--03. Add a new data Stuno not self-increment default stuno=0 If you set a self-increment can not be displayed to Stuno assignment
INSERT into student (' name ', stuage)
VALUES (' Harry ', 50);
--04. Delete all data in the table
DELETE from student;
--05. Add more data at the same time
INSERT into student
VALUES (1, ' Zhang San ', 50), (2, ' John Doe ', 50), (3, ' Harry ', 50);5. Backup and recovery of database6. Four kinds of integrity constraints and six constraints-Four kinds of integrity constraints
--01. Entity Integrity Unique constraint, PRIMARY KEY constraint
--that means a record! Make sure every record is unique and meaningful!
--02. Domain Integrity Check constraint, FOREIGN KEY constraint
--the field here refers to the fields! Guarantee that the value of the field must be valid!
--03. Referential Integrity FOREIGN KEY constraint
--the field values in one table are from the field values in the other table!
--The referenced table is the foreign key table/from the table
--The referenced table is the main table
--04. Custom Integrity Check Constraints
--6 major constraints
--01. Primary KEY ConstraintsPK PRIMARY Key
--02. Unique ConstraintUQ Unique
ALTER TABLE Student
ADD CONSTRAINT uq_student_name UNIQUE (name)
--03. FOREIGN KEY ConstraintFK foreign Key
ALTER TABLE Student
ADD tid INT (4); --new field in student Table create TABLE teacher (--Create teacher table
TId INT (4) Not NULL PRIMARY KEY,
Tname VARCHAR (20)
INSERT into teacher--add data to the teacher table
VALUES (1000, ' Teachers 1 '), (1001, ' Teachers 2 '), (1002, ' Teachers 3 '), (1003, ' Teachers 4 ');
ALTER Table Student--Creating a foreign key relationship from a table
ADD CONSTRAINT Fk_teacher
FOREIGN KEY (TID)
REFERENCES Teacher (tId)--04. Default Value ConstraintDF Default
--05. Check ConstraintsCK Check
--06. Non-null constraintsNN NOT NULL7. Modify and delete data in a table--Modify the data in the table
--The Update table name set needs to modify the data where condition
--01. Modify the name of the stuno=5 ' Little black '
UPDATE student SET ' name ' = ' small black '
WHERE stuno=5;
--02. Change All tid=1001 to 1003
UPDATE Student SET tid=1003
WHERE tid=1001
--delete data from a table
--Delete from table name where condition
--delete data from stuage between 20 and 100
DELETE from Student
WHERE
Stuage>=20 and stuage<=100;DELETE from Student
WHERE
Stuage between;--Delete all data from the teacher table
DELETE from teacher;8, the basic query statement--DQL (data Query language) querying language
--01. Check All Student Information
SELECT * from student;
--02. Query The specified student information
SELECT * FROM student WHERE studentname= ' generally ';
--03. The enquiry number is 1009 student number, name and address
SELECT studentno,studentname,address from Student
WHERE studentno=1009;
---04. Use Alias as when querying is also possible to omit
SELECT Studentno as number, studentname as name,
Address as residential from student
WHERE studentno=1009;SELECT studentno number, studentname name,
Address address from Student
WHERE studentno=1009;--05. Querying data in student and grade two tables
SELECT * from Student,grade;
-The result above is a Cartesian product! The product of the data in two tables!
SELECT * from Student,grade
WHERE Student.gradeid=grade.gradeid --06. To distinct query multiple columns, you must be in the first
--Check student numbers between 85 and 95 for student grades
SELECT DISTINCT Studentno,studentresult
From result
WHERE Studentresult between;
--07. Check student's name from 85 to 95
SELECT Studentname from Student
WHERE Studentno in
(SELECT DISTINCT Studentno from result
WHERE Studentresult between;--Verify that our output is correct
UPDATE result SET studentresult=80
WHERE studentno=1000; --08. Check the student's name and address that are not empty in the students ' table
SELECT studentname,address
From student
WHERE address!= '; --09. Check Student's name and address in the student's table with empty address
--NULL means no value differs from ' empty string ' SELECT studentname,address
From student
WHERE address= '; SELECT studentname,address
From student
WHERE address is NULL; SELECT studentname,address
From student
WHERE address is not NULL; --10. Query name is Li Dongfang or Liu struggle student information
SELECT * FROM Student
WHERE studentname= ' Li Dongfang ' OR studentname= ' Liu struggles '
--11. Query name is Li Dongfang or Liu struggle again or JSON student information
SELECT * FROM Student
WHERE
Studentname= ' Li Dongfang ' OR studentname= ' Liu struggles '
Or studentname= ' json '; --12. Use in instead of SELECT * from student
WHERE Studentname in
(' Li Dongfang ', ' json ', ' Liu Struggles '); --13. Query student information for surname Li _ represents a character% representing 0 or more characters
SELECT * FROM Student
WHERE studentname like ' li _ ' SELECT * from student
WHERE studentname like ' li% ' SELECT * from student
WHERE studentname like '% only ' SELECT * from student
WHERE studentname like '% only% '
Mysql (a)
