Database Basic Commands

Conventions:
Commands entered under Shell>window

mysql> after entering MySQL program, enter the MySQL command

I. Introduction to SQL
1. sql:structured Query Language structured querying language
He's the client's channel for dealing with the database.
2, the SQL has the industry standard. Ansi
Industry-Standard SQL, known as Mandarin.
Different databases are expanded on the basis of the SQL industry standard, and the extended part is called dialects.
3. Verify the installation is successful
Shell>mysql-u root-p
Mysql>show DATABASES; Shows that there are currently several libraries

Ii. DDL: Data definition language Language
Role: Defines a database or table structure.
object to manipulate: the structure of a database or table.
Keyword: CREATE ALTER DROP

Create a database called MYDB1.
Mysql>create DATABASE mydb1;
View the database creation details
Mysql>show CREATE DATABASE mydb1;
Create a MYDB2 database that uses the GBK character set.
Mysql>create DATABASE mydb2 CHARACTER SET GBK;
Create a MYDB3 database that uses the GBK character set and with the proofing rules.
Mysql>create DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
View all databases in the current database server
Mysql>show DATABASES;
View the definition information for the MYDB2 database that you created earlier
Mysql>show CREATE DATABASE mydb1;
Delete the previously created MYDB3 database
Mysql>drop DATABASE mydb3;

Always select a database before creating a table
Mysql>use test;
Create an Employee table
Mysql>create TABLE Employee (
ID int,
Name varchar (200),
Gender varchar (10),
Birthday date,
Entry_date date,
Job varchar (200),
Salary float (8,2),
Resume text
);

Displays all tables in the current database
Mysql>show TABLES;
An image column is basically added to the employee table above.
Mysql>alter TABLE employee ADD image blob;
View the definition of a table structure
Mysql>desc employee;
Modify the job column so that it has a length of 60.
Mysql>alter TABLE Employee MODIFY Job varchar (60); The
deletes the image column.
Mysql>alter TABLE employee DROP image; The
table name is changed to user.
Mysql>rename TABLE employee to user;
View the table's creation details
mysql>show create table user;
Modify the table's character set to GBK
Mysql>alter table user CHARACTER set GBK;
Column Name name modified to username
Mysql>alter TABLE user change name username varchar;
Dml:data manipulation Language Data Manipulation language
function: Manipulate the data in the table.
Key: INSERT UPDATE DELETE

Special Note: Date or string, character to use single quotation marks.

View all records in a table
Mysql>select * from user;
Use the INSERT statement to insert information for three employees into a table.
Recommendation: Mysql>insert into User (Id,username,gender,birthday,entry_date,job,salary,resume) VALUES (1, ' zql ', ' 0 ', ' 1991-09-07 ', ' 2013-04-
' CTO ', 10000.00, ' Beauty ');
Mysql>insert into User VALUES (2, ' gfy ', ' 1 ', ' 1987-09-07 ', ' 2013-04-12 ', ' CEO ', 10000.00, ' hand ');
Mysql>insert into User (Id,username,gender,birthday,entry_date,job,salary,resume) VALUES (3, ' Wang Xiangyun ', ' 1 ', ' 1989-09-07 ', ' 2013-04-12 ', ' UFO ', 10000.00, ' good
Boy ');

Questions when inserting Chinese: (Encoding problem)
To view the current encoding of the database:
Mysql>show VARIABLES like ' character% ';
Notifies the server client of the coded character set used
Mysql>set CHARACTER_SET_CLIENT=GBK;
garbled when displayed
Mysql>set CHARACTER_SET_RESULTS=GBK;

Revise the salary of all employees to 5000 yuan.
Mysql>update user SET salary=5000;
Change the salary of the employee named ' Wang Xiangyun ' to 3000 yuan.
Mysql>update user SET salary=3000 WHERE username= ' Wang Xiangyun ';
The employee's salary, named ' Wang Xiangyun ', was revised to $4000 and the job changed to CMO.
Mysql>update user SET salary=4000,job= ' CMO ' WHERE username= ' Wang Xiangyun ';
The ZQL salary will be increased by 1000 yuan on the original basis.
Mysql>update user SET salary=salary+1000 WHERE username= ' zql ';

Delete the record with the name ' Wang Xiangyun ' in the table.
Mysql>delete from user WHERE username= ' Wang Xiangyun ';
Deletes all records in the table.
Mysql>delete from user; (deletion of one article)
Use truncate to delete records in a table.
Mysql>truncate user; (destroys the entire table and then rebuilds the table structure)

Four, dql simple: Data Query Language
Keywords: SELECT

Query the information for all students in the table.
Mysql>select * from student; (not recommended)
Mysql>select Id,name,chinese,english,math from student;
Check the names of all the students in the table and the corresponding English scores.
Mysql>select name,english from student;
Filter duplicate data in a table.
Mysql>select DISTINCT 中文版 from student;

The SELECT statement supports some basic operations

Add 10 extra-long points to all students ' math scores.
Mysql>select id,name,math+10 from student;
Count each student's total score.
Mysql>select Name,chinese+english+math from student;
Use aliases to represent student scores.
Mysql>select name as names, Chinese+english+math total score from student;
Search for student's grades with name Harry
Msyql>select Name,english,chinese,math from student WHERE name= ' Harry ';
Query students with English scores greater than 90 points
Msyql>select Name,english,chinese,math from student WHERE english>90;
Find all students with a total score greater than 200
Mysql>select Name,chinese+english+math from Student WHERE (Chinese+english+math) >200;

Where statement supports op-expressions

Like statements,% represents 0 or more arbitrary characters, _ represents an arbitrary character, example first_name like ' _a% ';

Check the English score between 80-90 students.
Mysql>select * FROM student WHERE 中文版 between and 85;
Query math scores for 89,90,91 's classmates.
Mysql>select * FROM student WHERE math in (89,90,91);
All students surnamed Li are queried for their grades.
Mysql>select * FROM student WHERE name is like ' Li% ';
Query Math >80, the language of >80 students.
Mysql>select * FROM student WHERE math>80 and chinese>80;
Sort the math scores after the output.
Mysql>select * FROM student the ORDER by math;//is ascending by default
Sort the output after the total score, and then output in order from high to low
Mysql>select Name,chinese+english+math from Student ORDER by (Chinese+english+math) DESC;
A sort of math grade for a student surnamed Li output
Mysql>select Name,math from student WHERE name like ' li% ' ORDER by math;

V. Data integrity
Three aspects:
1. Entity integrity: Specifies that a row in a table is the only entity in a table.
This is usually done by defining the primary key.
Keyword: PRIMARY key
Features: cannot be null, must be unique

CREATE TABLE Shang_hai1 (
ID int PRIMARY KEY,
Name varchar (100)
);
It is not recommended for practical development.
CREATE TABLE Shanghai2 (
ID int PRIMARY KEY auto_increment,
Name varchar (100)
);
Insert into SHANGHAI2 (name) VALUES (' AA ');
2. Domain Integrity
A column (that is, a field) of a database table must conform to a specific data type or constraint.
Not NULL: cannot be empty
Unique: Must be unique
CREATE TABLE Shanghai3 (
ID int PRIMARY KEY,
Name varchar (+) is not NULL,
Idnum varchar (+) Unique
);

About primary keys:
(recommended) Logical PRIMARY key: for programmers. Not related to specific business
Business PRIMARY key: User can also use. Related to the specific business.

3. Referential integrity (multi-table design)
One-to-many
CREATE TABLE Department (
ID int PRIMARY KEY,
Name varchar (100)
);
CREATE TABLE Employee (
ID int PRIMARY KEY,
Name varchar (100),
Salary float (8,2),
dept_id int,
Constraint DEPT_ID_FK foreign KEY (dept_id) references Department (ID)
);

Many-to-many
CREATE TABLE Teacher (
ID int PRIMARY KEY,
Name varchar (100),
Salary Float (8,2)
);
CREATE TABLE Student1 (
ID int PRIMARY KEY,
Name varchar (100),
Grade varchar (10)
);
CREATE TABLE Teacher_student1 (
t_id int,
s_id int,
Primary KEY (T_ID,S_ID),
Constraint T_ID_FK foreign KEY (t_id) references teacher (ID),
Constraint S_ID_FK foreign KEY (s_id) references Student1 (ID)
);

One
CREATE TABLE Human (
ID int PRIMARY KEY,
Name varchar (100)
);
CREATE TABLE Idcard (
ID int PRIMARY KEY,
Num varchar (100),
Constraint HUANM_ID_FK foreign key (ID) references human (ID)
);