SQL(Structure Query Language): connect the client and the sql server.
How to login the server?
input the command in cmd:
mysql -u root -p
******
The DDL(Data Defination Language): to describe the data which exsits in the real world and save the data. E.i. craete the database and table's structure.
CREATE ALTER DROP TRUNCATE
DATABASE:
1. Create a database:
CREATE DATABASE mydb1;( use the server default charset )
2. Show the details of mydb1 database:
SHOW CREATE DATABASE mydb1;
3. Create a database whose charset is GBK:
CREATE DATABASE mydb2 CHARACTER SET gbk;
4. Create a database whose charset is GBK and has the collation:
CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
5. Show all the databases:
SHOW DATABASES;
6. Delete the database:
DROP DATABASE mydb3;
7. Change the database's charset:
ALTER DATABASE mydb2 CHARACTER SET utf8;
TABLE:
1. To use a table, we should choose a database first:
USE database_name;
2. Create a table, and we can set the character and the collation of this table:
CREATE TABLE table_name(
field1 datatype,
field2 datatype,
field3 datatype
)character set xxx collate xxx
for exmaple:
CREATE TABLE employee (
id INT,
name VARCHAR(100),
gender VARCHAR(10),
birthday DTAE,
entry_date DATE,
job VARCHAR(100),
salary FLOAT(8,2),
resume TEXT
);
3. Check the structure of the table:
DESC employee;
4. Add a column in employee table:
ALTER TABLE employee ADD image BLOB;
5. Modify the job column's varchar length:
ALTER TABLE employee MODIFY job VARCHAR(60);
6. Delete specific column:
ALTER TABLE employee DROP image;
7. Check all the tables in the database:
SHOW TABLES;
8. Rename the table's name:
RENAME TABLE employee TO worker;
9. Check the details of the table:
SHOW CREATE TABLE worker;
10. Change the table's charset；
ALTER TABLE worker CHARACTER SET gbk;
11. Change the column's name:
ALTER TABLE worker CHANGE name username VARCHAR(10);
The DML(Data Manipulation Language): to insert, delete and modify data in table.
INSERT UPDATE DELETE
1. Check all the information in the table:
SELECT * FROM worker;
2. Insert information of worker into the worker table:
INSERT INTO worker (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (1,'pp','male','2000-01-01','2008-08-08','coder',100,'common');
INSERT INTO worker VALUES (2,'cc','female','2001-01-01','2009-08-08','UI',200,'excellent');
INSERT INTO worker VALUES(3,'架构师','male','2002-01-01','2010-08-08','cto',300,'fabulous'); -----> this may lead to the character problem. The following has the solution:
3. Check the character set:
SHOW VARIABLES LIKE 'character%';
(*)client: the client's charset.
(*)connection: the database connection's charset.
database: one of the database in the server's cahrset.
(*)results: the result set which was returned to sql client's charset.
server: when we install the server's charset.
system: the database system's charset.
To solve the Chinese encoding problem, we can change the client's charset:
SET character_set_client=gbk; -----> this is a temporary method to change the charset: change the client's charset
INSERT INTO worker VALUES(3,'架构师','male','2002-01-01','2010-08-08','cto',300,'fabulous');
SET character_set_results=gbk; -----> change the results' charset
4. Change all the works' salary as 50:
UPDATE worker SET salary=50;
5. Change pp's salary as 30:
UPDATE worker SET salary=30 WHERE username='pp';
6. Change 架构师's salary as 20 and job as 'web front end':
UPDATE worker SET salary=20,job='web front end' WHERE username='架构师';
7. Add 10 salary of cc:
UPDATE worker SET salary=salary+10 WHERE username='cc';
8. Delete the data whose username is pp:
DELETE FROM worker WHERE username='pp';
9. Delete all the data in the table:
DELETE FROM worker; -----> delete the data one by one, when the data's amount is large, it will cost a lot of time
10. Delete the table:
TRUNCATE TABLE worker; -----> delete the table's structure and rebuild the table structure, when the data's amount is large, its efficiency is very high
DQL(Data Query Language): query the data in table.
Query data in single table:
1. Query the student's name and the English grade:
SELECT name,english FROM student;
2. Filter the repeated english grade:
SELECT DISTINCT english FROM student;
3. Add 10 for all the student:
SELECT name,math+10 FROM student;
4. Count the total grade of student:
SELECT name,chinese+english+math FROM student;
5. Use alias to represents the total grade:
SELECT name,chinese+english+math AS total FROM student;
6. Query the total grade whose name is 晨晨
SELECT name,math+english+chinese AS total FROM student WHERE name='晨晨';
7. Order the data of the table:
SELECT name,math+english+chinese AS total FROM student ORDER BY total DESC;
Data integrity: Ensure the data insert into the database is correct and avoid the mistake, when user inserts the data.
Entity integrity: could identify a data uniquely
CREATE TABLE t2(
id INT PRIMARY KEY, -----> The primary key is unique and should not be null.
name VARCHAR(100),
idcard VARCHAR(100)
);
another way:
CREATE TABLE t3(
id INT,
name VARCHAR(100),
idcard VARCHAR(100),
PRIMARY KEY(id)
);
the second method could declare composite keys:
CREATE TABLE t4(
id1 INT,
id2 INT,
PRIMARY KEY(id1,id2)
);
Domain integrity: the field in database must follow specific regulation or data type.
Schema:
type: id INT
length: id INT(3)
not null: username VARCHAR(10) NOT NULL
unique: username VARCHAR(10) UNIQUE -----> could bu null, if exists must be unique
example:
username VARCHAR(100) NOT NULL UNIQUE
example:
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT, -----> the id could increase automatically
username VARCHAR(20) NOT NULL UNIQUE,
idcardnum VARCHAR(18) UNIQUE,
gender VARCHAR(10) NOT NULL
);
When we want to insert data into the table, we can use the following command:
INSERT INTO user(id,username,idcardnum,gender) VALUES(1,'A','001','male');
INSERT INTO user(username,idcardnum,gender) VALUES('B','002','female'); ------> we can ignore the id, it will increace automatically (recommended)
INSERT INTO user VALUES(NULL,'B','002','female'); ------> if the id is null, it will also fill the id value automatically
Advice:
we don't recemmend you to mantain the database by itself, some database such as Oracle doesn't have these function.
Referential integrity:
Mutiple-tables:
1. one-to-many relationship:
CREATE TABLE department(
id INT PRIMARY KEY,
name VARCHAR(100),
addr VARCHAR(100)
);
CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR(100),
gender VARCHAR(100),
salary FLOAT(8,2),
department_id INT,
CONSTRAINT department_id_fk FOREIGN KEY(department_id) REFERENCES department(id)
);
How to define the foreign key?
CONSTRAINT foreign_key_name FOREIGN KEY (foreign_key) REFERENCES main_table (primary_key)
foreign_key_name: defined by yourself, must be unique in current database
foreign_key: the key which should be constrained in current table
2. many-to-many relationship:
CREATE TABLE teacher(
id INT PRIMARY KEY,
name VARCHAR(100),
salary FLOAT(8,2)
);
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(100),
grade VARCHAR(10)
);
CREATE TABLE teacher_student(
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 student(id)
);
INSERT INTO teacher VALUES(1,'PP',100);
INSERT INTO teacher VALUES(2,'CC',200);
INSERT INTO student VALUES(1,'LBJ','A');
INSERT INTO student VALUES(2,'KL','A');
INSERT INTO teacher_student VALUES(1,1);
INSERT INTO teacher_student VALUES(1,2);
INSERT INTO teacher_student VALUES(2,1);
INSERT INTO teacher_student VALUES(2,2);
3. one-to-one relationship:
CREATE TABLE person(
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE id_card(
id INT PRIMARY KEY,
num VARCHAR(18),
CONSTRAINT id_fk FOREIGN KEY (id) REFERENCE person(id)
);
Multi-Table Query:
1. Join Query:
Basic grammer: SELECT content FROM table1_name JOIN_TYPE table2_name [ON JOIN_CONDITION] [WHERE FILTER_CONDITION]
table1: on the left of JOIN_TYPE called left table, table2 is the right table.
1.1 Cross Join:
SELECT * FROM customer CROSS JOIN orders;
1.2 Inner Join:
1.2.1 Implicit Inner Join: not use ON key word (i.e. not use the JOIN_CONDITION), use the WHERE key word.
Requirment: query all the customers who have orders and the orders' information:
SELECT * FROM customer AS c, orders AS o WHERE c.id=o.customer_id;
1.2.2 Explicit Inner Join: use the WHERE key word.
Requirment: query all the customers who have orders and the orders' information:
SELECT * FROM customer AS c, INNER JOIN orders AS o ON c.id=o.customer_id;
Requirment: query the customer whose order price is larger than 200, and the order's information:
SELECT * FROM customer AS c INNER JOIN orders AS o ON c.id=o.customer_id WHERE o.price>=200;
1.3 Outer Join: return the result which meet the join requirment and return the rest information in the left table.
1.3.1 Left Outer Join:
Requirment: query the customers' information and display the order information:
SELECT * FROM customer AS c LEFT OUTER JOIN orders AS o ON c.id=o.customer_id;
1.3.2 Right Outer Join:
Requirment: query the customers' information and display the order information:
SELECT * FROM orders AS o RIGHT OUTER JOIN customer AS c ON c.id=o.customer_id;
Requirment: query all the orders and display the customers' information:
SELECT * FROM customer AS c RIGHT OUTER JOIN orders AS o ON c.id=customer_id;
2. Subquery: one query command serve as another query command's condition.
SELECT * FROM table1 WHERE id=(SELECT id FROM table2);
2.1 Subquery returns a scalar:
SELECT s.* FROM teacher_student AS ts, student AS s WHERE ts.s_id=s.id AND ts.t_id=2;
2.2 Subquery returns a single row
SELECT * FROM student WHERE id IN (SELECT s_id FROM teacher_student WHERE t_id=2);
3. Union Query: merge two query results, and deprive the repeated data line. And return the unrepeated result.
SELECT * FROM orders WHERE price>= 200 UNION SELECT * FROM orders WHERE customer_ id =1;
4. Report Query: the report query is used to group the data and count the data.
[SELECT ...] FROM ... [WHERE ...] [GROUP BY ... [HAVING...] ] [ORDER BY...]
GROUP BY: group the data
HAVING: filter the data after group the data
Count the total data in the table:
SELECT COUNT(*) FROM student;
....... SUM() .....
....... AVG() .....
....... MAX() .....
....... MIN() .....
Group the data by product and diplays the product's price:
SELECT product,SUM(price) FROM orders GROUP BY product;
Filter the condition via HAVING command: the HAVING command could only be used in GROUP BY
SELECT product,SUM(price) FROM orders GROUP BY product HAVING SUM(price) > 100;
Databse's Backup and Recovery:
1. Backup database(table structure and data)
c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql
2. Recovery database: the database's name must be created by you and choos the database
mysql> create database mydb1;
mysql> use mydb1;
mysql> source d:/mydb1.sql;