- English words of DataBase: DataBase abbreviation: DB
- What is a database?
Warehouse for storing and managing data
- Characteristics of database
1. Persistent data storage, in fact, the database is a file system
2. Easy to store, manage and operate data
3. A unified way is used to operate the database - SQL
- Common database software
- MySQL: open source free database, small database, has been acquired by Oracle. The MySQL 6. X version is also charging. Later, Sun acquired mysql, and Sun was acquired by Oracle
- Oracle: charge for large database, products of Oracle company.
- DB2: IBM's database products, for a fee. Often used in banking system
- SQL Server: a medium-sized database charged by MicroSoft. C ා,. net and other languages are often used.
- SQLite: a small embedded database, which is applied to the mobile phone, such as Android
- Go to mysql installation directory to find my.ini file
Copy datadir = " "
- Uninstall MySQL
- Delete the MySQL folder under the C:/ProgramData directory
- Go to mysql installation directory to find my.ini file
- To configure
MySQL service startup
1. Start the service manually 2. CMD - > servicse.msc open the service window 3. Open cmd with administrator net start mysql: start mysql service net stop mysql: close mysql service
1. Mysql-uroot-p password 4. Password of mysql-hip-uroot-p connection target 5. mysql --host=ip --user=root --password = password to connect to the target
1. exit 2. quit
MySQL directory structure
MySQL installation directory: basedir = "D: / development / MySQL /" Configuration file my.ini Mysql database directory: datadir="C:/ProgramData/MySQL /..." Databases: folders Table: Documents Data: data in files
- What is SQL?
Structured query language: Structured Query Language insert delete truncate update select drop In fact, it defines the rules for operating all relational databases. There are different ways of database operation, called "dialect". Mesh database: Structure database: Object oriented database: Tree database:
- SQL general syntax
3. SQL statements can be written in one or more lines, ending with semicolons. 2. You can use spaces and indents to enhance the readability of statements. 3. SQL statements in MySQL database are not case sensitive, and it is recommended to use uppercase keywords. 4. 3 Notes Single line comment: - comment content or ා comment content (unique to mysql) Multiline comment: / * comment*/
- SQL classification
1) DDL(Data Definition Language) data definition language Used to define database objects: database, table, column, etc. Keywords: create, drop,alter, etc 2) DML(Data Manipulation Language) It is used to add, delete and modify the data of tables in the database. Keywords: insert, delete, update, etc 3) DQL(Data Query Language) data query language Records (data) used to query tables in a database. Keywords: select, where, etc 4) DCL(Data Control Language) It is used to define the access and security level of the database, and to create users. Keywords: GRANT, REVOKE, etc
- Operation database: CRUD
1. C(Create): create To create a database: create database database name; Create database, judge it does not exist, and then create: useful create database if not exists database name; Create database and specify character set create database database name character set character set name; Exercise: create a db4 database, determine if it exists, and make the character set gbk create database if not exists db4 character set gbk; 2. R(Retrieve): query Query the names of all databases: show databases; Query character set of a database: query creation statement of a database show create database database name; 3. U(Update): modify Modify the character set of the database alter database database name character set character set name; 4. D(Delete): delete Delete database drop database database name; Judge whether the database exists and delete it again drop database if exists database name; 5. Use database Query the name of the database currently in use select database(); Use database use database name; useful****
- Operation table: CRUD
1. C(Create): create 1. syntax: create table table name( Column name 1 data type 1 constraint, Column name 2 data type 2 constraint, .... Column name n data type N constraint ); Note: the last column does not need to be comma (,) Database type: 1. int: integer type age int, 2. double: decimal type Score double (5,2) 5 is the number of digits on both sides of the decimal point 3. date: date, including only yyyy MM DD 4. datetime: date, including yyyy MM DD HH: mm: SS 5. timestamp: time error type, including yyyy MM DD HH: mm: SS If you do not assign a value to this field in the future, or the value is null, the current system time will be used by default to assign the value automatically 6. varchar: string variable character name varchar(20): 20 characters maximum zhangsan 8 characters three 2 characters 7. char: immutable character Create table create table student( id int, name varchar(32), age int , score double(4,1), birthday date, insert_time timestamp ); Copy table: create table name like the copied table name; 2. R(Retrieve): query Query all table names in a database show tables; Query table structure desc table name; 3. U(Update): modify 1. Modify table name alter table name rename to new table name; 2. Modify the character set of the table alter table table name character set character set name; 3. Add a column alter table name add column name data type; 4. Modify column name type alter table name change column name new column new data type; alter table name modify column name new data type; 5. delete columns alter table table name drop column name; 4. D(Delete): delete drop table table name; Drop table if exists table name;
Client graphical tool: SQLYog
- DML: adding, deleting and modifying data in the table
1. Add data: Syntax: insert into table name (column name 1, column name 2,... Column name n) values (value 1, value 2,... Value n), (value 1, value 2,... Value n); Be careful: 1. The column name and value should correspond one by one. 2. If no column name is defined after the table name, all columns will be added with values by default insert into table name values (value 1, value 2,... Value n); 3. Except for the number type, other types need to be enclosed in quotation marks (single and double can be used) 4. When adding, type int, and code is normal (SQL is weak type language) 2. Delete data: Syntax: delete from table name [where condition] Be careful: 1. If no condition is added, all records in the table will be deleted. 2. If you want to delete all records 1. delete from table name; -- not recommended. How many records will be deleted-- When deleting data, the primary key auto increase record is still in use. The next auto increase is based on the last record 2. TRUNCATE TABLE table name; -- recommended. For higher efficiency, delete the table first, and then create the same table. The auto increase record has been cleared. The next time you add data, you should start from 1 Primary key auto grow; ID 1 2 3... 10 1 2 3 3. Modify data: Syntax: update table name set column name 1 = value 1, column name 2 = value 2,... [where condition]; Be careful: 1. If no conditions are added, all records in the table will be modified. 2. The condition is usually the only key in the table;
select * from table name
select field list from table name list where condition list group by group field Conditions after having grouping order by Limit paging limit
- Basic query
1. Query of multiple fields select field name 1, field name 2... from table name; Be careful: If you query all fields, you can use to override the field list. 2. Remove the repetition: distinct 3. calculation column Generally, four operations can be used to calculate the values of some columns. (generally only numerical calculation will be carried out) Ifnull (expression1, expression2): null participates in the operation, and the evaluation results are all null Expression 1: which field needs to judge whether it is null Expression 2: the replacement value if the field is null. 4. Starting alias: As: as can also be omitted
- Conditional query
1. where clause followed by condition 2. operator > ,< ,<= ,>= ,=(==) ,<>(!=) BETWEEN...AND 10~ 30 In (set) corresponds to multiple data LIKE: fuzzy query Placeholder: _: single arbitrary character %: multiple arbitrary characters IS NULL is null IS NOT NULL is not empty And and Or or not non
-- Query age is more than 20 years old SELECT * FROM student WHERE age > 20; SELECT * FROM student WHERE age >= 20; -- Query age equal to 20 SELECT * FROM student WHERE age = 20; -- Query age is not equal to 20 years old SELECT * FROM student WHERE age != 20; SELECT * FROM student WHERE age <> 20; -- Query age ≥ 20 ≤ 30 SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age >= 20 AND age <=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30; -- Query the information of the age of 22, 18 and 25 SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25 SELECT * FROM student WHERE age IN (22,18,25); -- Query English score as null SELECT * FROM student WHERE english = NULL; -- Wrong. null Value cannot be used = (!=) judge SELECT * FROM student WHERE english IS NULL; -- Query English score is not null SELECT * FROM student WHERE english IS NOT NULL; -- What are the people with the surname ma? like SELECT * FROM student WHERE NAME LIKE 'Horse%'; -- The second word of the name is the person SELECT * FROM student WHERE NAME LIKE "_turn%"; -- People whose name is three words SELECT * FROM student WHERE NAME LIKE '___'; -- Inquire the person whose name contains virtue SELECT * FROM student WHERE NAME LIKE '%Virtue%';
- Sort query
2. Sorting Query Syntax: order by clause order by sort field 1 sort method 1, sort field 2 sort method 2 Sort by: ASC: ascending, default. DESC: descending. Be careful: If there are multiple sorting conditions, the second condition will be determined only when the condition values of the current edge are the same.
- Aggregate function: take a column of data as a whole and perform vertical calculation.
1. count(): count 1. Generally select a non empty column: primary key 2. count() 2. max(): calculate the maximum value 3. min(): calculate the minimum value 4. sum(): Calculation and 5. avg(): calculate the average value A column of data: the represented field is put into () Note: the calculation of aggregate function excludes null value. Solution: 1. Select no non empty columns for calculation 2. IFNULL function
- Group query:
1. Syntax:group by Group fields; 2. Be careful: 1. Fields queried after grouping: grouping fields, aggregation functions 2. where and having The difference? 1. where Limit before grouping, and do not participate in grouping if the conditions are not met.havingLimit after grouping. If the result is not satisfied, it will not be queried 2. where It cannot be followed by an aggregate function,havingThe aggregation function can be judged. -- Group by gender. Check the average scores of male and female students respectively SELECT sex , AVG(math) FROM student GROUP BY sex; -- Group by gender. Check the average scores of male and female students respectively,Number SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex; -- Group by gender. Check the average scores of male and female students respectively,Number requirement: people with score lower than 70 will not participate in grouping SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- Group by gender. Check the average scores of male and female students respectively,Number requirement: people with score lower than 70 will not participate in grouping,After grouping. More than 2 people SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; SELECT sex , AVG(math),COUNT(id) Number FROM student WHERE math > 70 GROUP BY sex HAVING Number > 2;
- Paging query
1. Syntax: index starting from limit, number of queries per page; 2. Formula: starting index = (current page number - 1) number of items displayed per page --3 records per page SELECT * FROM student LIMIT 0,3; -- page 1 SELECT * FROM student LIMIT 3,3; -- page 2 SELECT * FROM student LIMIT 6,3; -- page 3 3. limit is a MySQL "dialect" 4. The first index and the second offset of the parameter after the limit 5. limit is followed by only one parameter to limit the number of display records
Concept: limit the data in the table to ensure the correctness, validity and integrity of the data.
1. Primary key constraint: the primary key guarantees to query with this column of fields to find unique data 2. Non NULL constraint: not null ensures that the column data cannot be null 3. Unique constraint: unique ensures the uniqueness of this column of data 4. Foreign key constraint: foreign key designs multiple tables, and the data of slave tables depends on the main table; 5. Default constraint: default 1 if there is no data in this column, there is a default value; if timestamp is null when adding data, the current system time is default
- Non empty constraint: not null, the value of a column cannot be null
1. Add constraints when creating tables CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name Not empty ); 2. After creating the table, add a non empty constraint ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL; 3. delete name Non empty constraint of ALTER TABLE stu MODIFY NAME VARCHAR(20);
- Unique constraint: unique, the value of a column cannot be repeated
1. note: A unique constraint can have a null value, but only one record can be null 2. Add unique constraints when creating tables CREATE TABLE stu( id INT, Phone number varchar (20) unique ); 3. Delete unique constraint ALTER TABLE stu DROP INDEX phone_number; 4. After the table is created, add a unique constraint ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
- Primary key constraint: primary key
1. Be careful: 1. Meaning: non empty and unique 2. A table can only have one field as primary key 3. The primary key is the unique identification of the records in the table 2. Add a primary key constraint when creating a table create table stu( id int primary key auto_increment,-- to id Add primary key constraint name varchar(20) ); 3. Delete primary key -- error alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY; 4. After creating the table, add the primary key ALTER TABLE stu MODIFY id INT PRIMARY KEY; 5. Automatic growth: 1. Concept: if a column is numeric, use the auto_increment It can be done automatically //When there is automatic growth,Failure to add data will still increase 2. When creating the table, add the primary key constraint and complete the self growth of the primary key create table stu( id int primary key auto_increment,-- to id Add primary key constraint name varchar(20) ); 3. Delete auto growth ALTER TABLE stu MODIFY id INT; 4. Add automatic growth ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
- Foreign key constraint: foreign key, which enables tables to have relationships with tables, so as to ensure the correctness of data.
1. When creating a table, you can add a foreign key Syntax: create table table name( .... Foreign key column constraint foreign key name foreign key (foreign key column name) references main table name (main table column name) ); 2. Delete foreign key ALTER TABLE table name DROP FOREIGN KEY foreign key name; 3. After creating the table, add the foreign key ALTER TABLE table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY (FOREIGN KEY field name) REFERENCES main table name (main table column name); 4. Cascade operation 1. Add cascade operation Syntax: ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY reference main table name (main table column name) ON UPDATE CASCADE ON DELETE CASCADE; 2. classification: 1. Cascade update: ON UPDATE CASCADE 2. Cascade deletion: ON DELETE CASCADE
- Relationship between multiple tables
1. One to one (understanding): user table id username password user information table email phone ID card... User member information account bank number balance point Such as: person and ID card Analysis: one person has only one ID card, and one ID card can only correspond to one person 2. One to many (many to one): For example: departments and employees Analysis: a department has multiple employees, and an employee can only correspond to one department 3. Many to many: For example: students and courses Analysis: a student can choose many courses, and a course can also be chosen by many students
- Implementation relationship:
3. One to many (many to one): For example: departments and employees Implementation method: establish a foreign key on one side and point to the primary key of one side. 4. Many to many: For example: students and courses Implementation method: the implementation of many to many relationship requires the help of the third intermediate table. The intermediate table contains at least two fields, which are the foreign keys of the third table and point to the primary keys of the two tables respectively 5. One to one (understanding): Such as: person and ID card Implementation method: one to one relationship can be implemented by adding a unique foreign key to the primary key of the other party.
-- Create tourism route classification table tab_category -- cid Primary key of tourism route classification, automatic growth -- cname Tourism line classification name is not empty, unique, string 100 CREATE TABLE tab_category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) NOT NULL UNIQUE ); -- Create tour route table tab_route / rid Primary key of tourism route, automatic growth rname Travel route name is not empty, unique, string 100 price Price rdate Listing time, date type cid Foreign key, classification / CREATE TABLE tab_route( rid INT PRIMARY KEY AUTO_INCREMENT, rname VARCHAR(100) NOT NULL UNIQUE, price DOUBLE, rdate DATE, cid INT, FOREIGN KEY (cid) REFERENCES tab_category(cid) ); /Create user table tab_user uid User primary key, self growth username User name length 100，Unique, non empty password Password length 30，Not empty name Real name length 100 birthday Birthday sex Gender, fixed length string 1 telephone Phone number, string 11 email Mailbox, string length 100 / CREATE TABLE tab_user ( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE NOT NULL, PASSWORD VARCHAR(30) NOT NULL, NAME VARCHAR(100), birthday DATE, sex CHAR(1) DEFAULT 'male', telephone VARCHAR(11), email VARCHAR(100) ); / //Create favorites rid Tourist route id，Foreign key date Collection time uid user id，Foreign key rid and uid Can't duplicate, set compound primary key, the same user can't collect the same line twice / CREATE TABLE tab_favorite ( rid INT, -- line id DATE DATETIME, uid INT, -- user id -- Create compound primary key PRIMARY KEY(rid,uid), -- composite keys FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY(uid) REFERENCES tab_user(uid) );
- Concept: some specifications to follow when designing a database. To follow the requirements of the paradigms in the back, you must first follow all the requirements of the paradigms in the front
When designing a relational database, we should follow different specifications and design a reasonable relational database. These different specifications are called different paradigms. The higher the paradigms are, the smaller the redundancy of the database is. At present, there are six paradigms of relational database: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the bath cod paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm).
1. First normal form (1NF): each column is an indivisible atomic data item 2. Second normal form (2NF): on the basis of 1NF, noncode attributes must completely depend on the code (on the basis of 1NF, the partial functional dependence of noncode attributes on the main code is eliminated) 3. Third normal form (3NF): on the basis of 2NF, any non primary attribute does not depend on other non primary attributes (eliminating the transfer dependency on the basis of 2NF)
1. Function dependency: A -- > B. if the value of attribute A (attribute group) is used, the value of unique attribute B can be determined. B depends on A For example: Student No. --- name. (student number, course name) -- > score 2. Full function dependency: a -- > B. If a is an attribute group, then the value of attribute B depends on all attribute values in attribute group A. For example: (student number, course name) -- > score 3. Partial function dependency: A -- > B. If A is an attribute group, the value of B attribute only depends on some values in A attribute group. For example: (student number, course name) -- > name 4. Transfer function dependency: A -- > b, B -- > C. if the value of the unique B attribute can be determined by the value of the A attribute (attribute group), and the value of the unique C attribute can be determined by the value of the B attribute (attribute group), then the C transfer function depends on A For example: Student No. --- Department name, department name -- > department head 5. Code: if an attribute or attribute group in a table is completely dependent on all other attributes, the attribute (attribute group) is called the code of the table For example: the code in the table is: (student number, course name) Primary attribute: all attributes in the code attribute group Non primary attribute: attribute of attribute group except code
From the first paradigm to the second paradigm: take out the part of the dependency and its main attributes to form a new table, and the rest to form a table
From the second paradigm to the third paradigm: take out the attributes of transitive dependency, leave a attribute that depends on the primary attribute, and the attributes of transitive dependency form a new table
- command line
Syntax: Backup: mysqldump -u username - p password database name > saved path Reduction: 1. Log in to the database 2. Create database 3. Use database 4. Executive documents. source file path
- Graphical tools
- Query statement
select List of column names from List of table names where....
- Prepare SQL (easy to test)
# Create department table CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('Development Department'),('Marketing Department'),('Finance Department'); # Create employee table CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), -- Gender salary DOUBLE, -- wages join_date DATE, -- Date of entry dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) -- Foreign key, related department table(Primary key of department table) ); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Sun WuKong','male',7200,'2013-02-24',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Zhu Bajie','male',3600,'2010-12-02',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Tang Seng','male',9000,'2008-08-08',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('White bone essence','female',5000,'2015-10-07',3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('spider goblin','female',4500,'2011-03-14',1);
- Cartesian product
There are two sets A,B. take all the components of these two sets. To complete a multi table query, you need to eliminate useless data
- Internal connection query
1. Implicit joins: UsingwhereConditional elimination of useless data * Example: -- Query all employee information and corresponding department information SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- Query the name and gender of the employee table. Name of department table SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`; SELECT t1.name, -- Name of employee form t1.gender,-- Gender of employee table t2.name -- Name of department table FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`; 2. Explicit internal connection: * Syntax: select List of fields from Table name1 [inner] join Table name2 on condition * For example: * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`; 3. Internal connection query: 1. From which tables to query data 2. What are the conditions 3. Which fields to query
- External connection query
1. Left outer connection: * Syntax:select List of fields from surface1 left [outer] join surface2 on Conditions; * All data in the left table and its intersection are queried. * Example: -- Query all employee information. If the employee has a department, query the Department name. If there is no department, the Department name will not be displayed SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`; 2. Right outer connection: * Syntax:select List of fields from surface1 right [outer] join surface2 on Conditions; * It queries all the data in the right table and its intersection part. * Example: SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
* Concept: a nested query in a query is called a subquery. -- Query the highest paid employee information -- 1 Query the maximum wage of 9000 SELECT MAX(salary) FROM emp; -- 2 Query employee information, and the salary is equal to 9000 SELECT * FROM emp WHERE emp.`salary` = 9000; -- One article sql Just do it. Subquery SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp); * Different sub queries 1. The result of a subquery is single row and single column: * Subqueries can be used as conditions and operators to judge. Operator: > >= < <= = * -- Query the person whose salary is less than the average salary SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp); 2. The result of a subquery is multi row and single column: * Subqueries can be used as conditions, using operatorsinTo judge -- query'Finance Department'and'Marketing Department'All employee information SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department'; SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2; -- Subquery SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department'); 3. The result of a subquery is multi row and multi column: * Subquery can participate in query as a virtual table -- Query employee's entry date is 2011-11-11 Future employee and department information -- Subquery SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- Common internal connection SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
-- Departmental table CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- department id dname VARCHAR(50), -- Department name loc VARCHAR(50) -- Department location ); -- Add 4 departments INSERT INTO dept(id,dname,loc) VALUES (10,'Teaching and Research Department','Beijing'), (20,'Ministry of science and Engineering','Shanghai'), (30,'Sales Department','Guangzhou'), (40,'Finance Department','Shenzhen'); -- Job list, job name, job description CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) ); -- Add 4 jobs INSERT INTO job (id, jname, description) VALUES (1, 'Chairman', 'Manage the whole company, receive orders'), (2, 'manager', 'Management staff'), (3, 'Salesperson', 'Selling products to customers'), (4, 'Clerk', 'Using office software'); -- Employee list CREATE TABLE emp ( id INT PRIMARY KEY, -- staff id ename VARCHAR(50), -- Employee name job_id INT, -- post id mgr INT , -- Superior leader joindate DATE, -- Date of entry salary DECIMAL(7,2), -- wages bonus DECIMAL(7,2), -- bonus dept_id INT, -- Department No CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) ); -- Add staff INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'Sun WuKong',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'Lu Jun Yi',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'Lin Chong',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'Tang Seng',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'Li Kui',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'Song Jiang',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'Liu Bei',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'Zhu Bajie',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'Luo Guanzhong',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'Wu Yong',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'Sha Seng',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'Li Kui',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'Little white dragon',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'Guan Yu',4,1007,'2002-01-23','13000.00',NULL,10); -- Salary scale CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- level losalary INT, -- minimum wage hisalary INT -- Maximum wage ); -- Add 5 salary levels INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);
-- 1.Query all employee information. Query employee number, employee name, salary, job name, job description /* Analysis: 1.Employee No., employee name, salary, need to query emp table job name, job description need to query job table 2.Query condition emp.job_id = job.id */ SELECT t1.`id`, -- Employee number t1.`ename`, -- Employee name t1.`salary`,-- wages t2.`jname`, -- Job title t2.`description` -- Job description FROM emp t1, job t2 WHERE t1.`job_id` = t2.`id`; -- 2.Query employee No., employee name, salary, job name, job description, department name, Department location /* Analysis: 1. Employee No., employee name, salary emp job name, job description job department name, Department location dept 2. Condition: emp.job_id = job.id and emp.dept_id = dept.id */ SELECT t1.`id`, -- Employee number t1.`ename`, -- Employee name t1.`salary`,-- wages t2.`jname`, -- Job title t2.`description`, -- Job description t3.`dname`, -- Department name t3.`loc` -- Departmental position FROM emp t1, job t2,dept t3 WHERE t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`; -- 3.Query employee name, salary, salary grade /* Analysis: 1.Employee name, salary emp salary grade salarygrade 2.Condition EMP. Salary > = salarygrade. Losalary and EMP. Salary < = salarygrade. Hisalary emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary */ SELECT t1.ename , t1.`salary`, t2.* FROM emp t1, salarygrade t2 WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`; -- 4.Query employee name, salary, job name, job description, department name, Department location, salary grade /* Analysis: 1. Employee name, salary emp, job name, job description job department name, Department location, dept salary grade salarygrade 2. Conditions: emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary */ SELECT t1.`ename`, t1.`salary`, t2.`jname`, t2.`description`, t3.`dname`, t3.`loc`, t4.`grade` FROM emp t1,job t2,dept t3,salarygrade t4 WHERE t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id` AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`; -- 5.Find out the department number, department name, Department location and department number /* Analysis: 1.Department number, department name, Department location dept table. emp table of department number 2.Use group queries. Complete grouping according to emp.dept.u ID and query count(id) 3.Use subquery to associate the query result of step 2 with dept table */ SELECT t1.`id`,t1.`dname`,t1.`loc` , t2.total FROM dept t1, (SELECT dept_id,COUNT(id) total FROM emp GROUP BY dept_id) t2 WHERE t1.`id` = t2.dept_id; -- 6.Query the names of all employees and their immediate superiors,Employees without leaders also need to query /* Analysis: 1.Name emp, name of immediate superior emp * emp The id and mgr of the table are self related 2.Condition emp.id = emp.mgr 3.Query all data in the left table, and intersection data * Use left outer connection query */ /* select t1.ename, t1.mgr, t2.`id`, t2.ename from emp t1, emp t2 where t1.mgr = t2.`id`; */ SELECT t1.ename, t1.mgr, t2.`id`, t2.`ename` FROM emp t1 LEFT JOIN emp t2 ON t1.`mgr` = t2.`id`;
- Basic introduction to the transaction
1. Concept: When there are multiple add, delete and change operations, we usually execute these steps in the method transaction; * If a multi-step business operation is managed by a transaction, these operations either succeed at the same time or fail at the same time. 2. Operation: 1. Open transaction: start transaction; 2. Rollback:rollback; Return to the data state before transaction opening; 3. Submission:commit; NamelysqlSuccessful implementation; 3. Example: CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- Add data INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); SELECT * FROM account; UPDATE account SET balance = 1000; -- Zhang San transfers 500 yuan to Li Si -- 0. Open transaction START TRANSACTION; -- 1. Zhang San account -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2. Li Si account +500 -- Error... UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- No problem in execution found, commit transaction COMMIT; -- Problem found, rollback transaction ROLLBACK; 4. MySQL Default auto commit of transactions in database * There are two ways to commit a transaction: * Auto submit: * mysql It's auto submitted * One article DML(Additions and deletions)Statement automatically commits a transaction once. * Manual submission: * Oracle The database defaults to manually commit transactions * Transaction needs to be opened before committing * Modify the default commit method of transaction: not recommended * To view the default commit method for a transaction:SELECT @@autocommit; -- 1 On behalf of auto submit 0 on behalf of manual submit * To modify the default submission method: set @@autocommit = 0;
- Four characteristics of affairs (interview questions)
1. Atomicity: it is the smallest indivisible operation unit. It can either succeed at the same time or fail at the same time. 2. Persistence: when a transaction is committed or rolled back, the database will persist the data. Add, delete and change commit; 3. Isolation: between multiple transactions. Independent of each other. 4. Consistency: before and after transaction operation, the total amount of data remains the same 1000/1000 commit 500/1500 rollback 1000/1000
- Isolation level of transactions (understanding)
* Concept: multiple transactions are isolated and independent from each other. However, if multiple transactions operate on the same batch of data, some problems will arise, which can be solved by setting different isolation levels. * Problems: 1. Dirty read: a transaction that reads uncommitted data from another transaction 2. Non repeatable reading(Virtual reading): In the same transaction, the data read twice is different. 3. Unreal reading: a transaction operation(DML)For all records in the data table, if another transaction adds a piece of data, the first transaction cannot query its own modification. * Isolation level: 1. read uncommitted: Read not submitted * Problems: dirty reading, non repeatable reading, unreal reading 2. read committed: Read submitted( Oracle) * Problems: non repetition and unreal reading 3. repeatable read: Repeatable reading( MySQL Default) * Problems: unreal reading 4. serializable: Serialization java Multi thread synchronization lock in; thread safety, low efficiency; * Can solve all problems * Note: the isolation level is getting higher and higher, but the efficiency is getting lower and lower * Database query isolation level: * select @@tx_isolation; * Database set isolation level: * set global transaction isolation level Level string; * Demonstration: set global transaction isolation level read uncommitted; start transaction; -- Transfer operation update account set balance = balance - 500 where id = 1; update account set balance = balance + 500 where id = 2;
- SQL classification
1. DDL: operation database and table 2. DML: add, delete and modify data in the table 3. DQL: query the data in the table 4. DCL: manage users, authorize
- DBA: Database Administrator
- DCL: manage users, authorize
1. Managing users 1. Add user: * Syntax:CREATE USER 'User name'@'host name' IDENTIFIED BY 'Password'; 2. Delete user: * Syntax: DROP USER 'User name'@'host name'; 3. Change user password: UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'User name'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR 'User name'@'host name' = PASSWORD('New password'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); * mysql I forgot. root User's password? 1. cmd -- > net stop mysql Stop it mysql service * Administrator is required to run this cmd 2. Start with no validation mysql Services: mysqld --skip-grant-tables 3. Open up new cmd window,Direct input mysql Order, hit enter. You can log in successfully 4. use mysql; 5. update user set password = password('Your new password') where user = 'root'; 6. Close both windows 7. Open Task Manager and end manually mysqld.exe Process 8. start-up mysql service 9. Sign in with the new password. //Reinstall! 4. Query user: -- 1. Switch to mysql data base USE myql; -- 2. query user surface SELECT * FROM USER; * Wildcard: % Indicates that the user can log in to the database on any host
- Privilege management
1. Query permission: -- Query authority SHOW GRANTS FOR 'User name'@'host name'; SHOW GRANTS FOR 'lisi'@'%'; 2. Grant permission: -- Grant authority grant Permission list on Database name.Table name to 'User name'@'host name'; -- Grant all permissions to Zhang San's users on any database or table GRANT ALL ON *.* TO 'zhangsan'@'localhost'; 3. Revoke authority: -- Revoke authority: revoke Permission list on Database name.Table name from 'User name'@'host name'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';