Methods in the database:
DDL: operation database and table
1. Operation database: CRUD
1. C(Create):establish * Create database: * create database Database name; * Create a database, judge that it does not exist, and then create: * create database if not exists Database name; * Create a database and specify a character set * create database Database name character set Character set name;
2，R(Retrieve): query * Query the names of all databases: * show databases; * Query the character set of a database:Create statement to query 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 then delete it * 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;
2. Operation table
1. C(Create):establish 1. Syntax: create table Table name( Column name 1 data type 1, Column name 2 data type 2, .... Listing n data type n ); * Note: the last column does not need a comma(,) * Database type: 1. int: Integer type * age int, 2. double:Decimal type * score double(5,2) 3. date:Date, only including mm / DD / yyyy, yyyy-MM-dd 4. datetime:Date, including month, day, hour, minute and second yyyy-MM-dd HH:mm:ss 5. timestamp:Time error type Including month, day, hour, minute and second yyyy-MM-dd HH:mm:ss * If you do not assign a value to this field in the future, or assign a value of null，The current system time is used by default to assign values automatically 6. varchar: character string * name varchar(20):Name up to 20 characters * zhangsan 8 Two characters for three characters* 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 Table name like Table name to be copied;
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 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 Table name add Column name data type; 4. Modify column name type alter table Table name change Column name new column type new data type; alter table Table name modify Column name new data type; 5. Delete column alter table Table name drop Listing;
4. D(Delete):delete * drop table Table name; * drop table if exists Table name ;
3. Index
1. key word
*UNIQUE: Create a unique index
*CLUSTER: Build cluster index
*NONCLUSTERED: Build non clustered index
2. grammar
1. Indexing
*CREATE [UNIQUE] [CLUSTER] INDEX <Index name>
ON <Table name>(<Listing>[<order>][,<Listing>[<order>]]...)
2. Modify index
*ALTER INDEX <Old index name> RENAME TO <New citation>
3. Delete index
*DROP INDEX<Index name>
*CREATE UNIQUE CLUSTER INDEX STUSNO ON Student(SNO)
--In the student table Student Student number sno Cluster index on
*CREATE NONCLUSTER INDEX STUSNAME ON Student(SNAME)
--In the student table Student In, is the name sname Build non clustered index
*CREATE UNIQUE INDEX COUCNO ON Course(CNO)
--In the curriculum Course Course number Cno Create a unique index on
*CREATE UNIQUE INDEX SC_SNO_GRADE ON SC(SNO ASC,GRADE DESC)
--Student number in the course selection table sno,achievement Grade To establish a composite index on, the student number is required to be in ascending order, and the score is in descending order when the student number is the same
*DROP INDEX STUSNO
--use drop Delete student table Student Indexed
DML: adding, deleting and modifying data in the table
1. Add data:
* Syntax: * insert into Table name(Column name 1,Column name 2,...Listing n) values(Value 1,Value 2,...value n); * be careful: 1. Column names and values should correspond to each other. 2. If the column name is not defined after the table name, values are added to all columns by default insert into Table name values(Value 1,Value 2,...value n); 3. In addition to numeric types, other types require quotation marks(Both single and double are OK)Lead up
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 2. TRUNCATE TABLE Table name; -- Recommended. It is more efficient. First delete the table, and then create the same table.
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.
DQL: data method in query table
1. Query the records in the table
1. Syntax:
* select * from Table name; select Field list from Table name list where Condition list group by Grouping field having Conditions after grouping order by sort limit Paging limit
2. 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 replace the field list. 2. Remove duplicates: * 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(Expression 1,Expression 2): null The calculation results are null * Expression 1: which field needs to be judged as null * If the field is null Replacement value after. 4. Alias: * as: as It can also be omitted
3. Condition query
1. where Clause followed by condition
2. operator
* > ,< ,<= ,>= ,= ,<>
* BETWEEN...AND
* IN( aggregate)
* LIKE: Fuzzy query
* Placeholder:
* _:Single arbitrary character
* %:Multiple arbitrary characters
* IS NULL
* and or &&
* or or ||
* not or !
-- Query age is over 20 years old
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
-- The query age is equal to 20 years old
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 is greater than or equal to 20 and less than or equal to 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 information of 22, 18 and 25 years old
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);
-- Query English score is null
SELECT * FROM student WHERE english = NULL;
-- No. 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 names of horses? like
SELECT * FROM student WHERE NAME LIKE 'horse%';
-- The second word of the query name is the humanized person
SELECT * FROM student WHERE NAME LIKE "_turn%";
-- A person whose name is three words
SELECT * FROM student WHERE NAME LIKE '___';
-- Query the person whose name contains de
SELECT * FROM student WHERE NAME LIKE '%virtue%';
-- Calculate employee's daily salary(By 30 days).
# select ENAME,SAL/30 from emp;
select ENAME,truncate(SAL/30,2) 'per diem' from emp;
truncate:
sal/30: Field corresponding operation
2: Keep two decimal places
2. Query statement
1. Sort query
* Syntax: order by clause * order by Sort field 1, sort method 1, sort field 2, sort method 2... select * from Table name order by Sorting field sorting method; * Sort by: * ASC: Ascending, default. * DESC: Descending order. * be careful: * If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.
2. Aggregate function
Calculate the vertical data as a single row (single column). 1. count: Calculate the number 1. Generally, non empty columns are selected: primary key 2. count(*) 2. max: Calculate maximum 3. min: Calculate minimum 4. sum: Calculation and 5. avg: Calculate average * Note: Calculation of aggregate function, excluding null Value. Solution: 1. Select columns that do not contain non empty columns for calculation 2. IFNULL function
3. Group query
1. Syntax: group by Grouping field; 2. be careful: 1. Fields queried after grouping: grouped fields, (or) aggregate functions 2. where and having What's the difference? 1. where Limit before grouping. If the conditions are not met, you will not participate in grouping. having Limit after grouping. If the result is not satisfied, it will not be queried 2. where Can not be followed by aggregate function, having You can judge the aggregation function. -- Grouped by gender. Check the average scores of male and female students respectively SELECT sex , AVG(math) FROM student GROUP BY sex; -- Grouped by gender. Check the average scores of male and female students respectively,Number of people SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex; -- Grouped by gender. Check the average scores of male and female students respectively,Number requirements: those with scores lower than 70 will not participate in the grouping SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- Grouped by gender. Check the average scores of male and female students respectively,Number requirements: those with scores lower than 70 will not participate in the grouping,After grouping. The number of people should be more than 2 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 of people FROM student WHERE math > 70 GROUP BY sex HAVING Number of people > 2;
4. Paging query
1. Syntax: limit Start index,Number of queries per page; 2. Formulas: 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 It's a MySQL"dialect"
DCL: permissions
-
SQL classification:
- DDL: manipulating databases and tables
- DML: adding, deleting and modifying data in the table
- DQL: query data in table
- DCL: management user, authorization
-
DBA: Database Administrator
Manage users
Add user
* Syntax: CREATE USER 'user name'@'host name' IDENTIFIED BY 'password';
delete user
* Syntax: DROP USER 'user name'@'host name';
Modify 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
* An administrator is required to run the cmd
2. Start with no authentication mysql Services: mysqld --skip-grant-tables
3. Open new cmd window,Direct input mysql Command, 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 the task manager and end it manually mysqld.exe Process of
8. start-up mysql service
9. Log in with a new password.
Query user
-- 1. Switch to mysql database USE myql; -- 2. query user surface SELECT * FROM USER; * Wildcard: % Indicates that users can log in to the database on any host
Authority management
Query authority
-- Query authority SHOW GRANTS FOR 'user name'@'host name'; SHOW GRANTS FOR 'lisi'@'%';
Grant permission
-- Grant permission grant Permission list on Database name.Table name to 'user name'@'host name'; -- Grant all permissions to user Zhang San on any database and any table GRANT ALL ON *.* TO 'zhangsan'@'localhost';
Revoke permission
-- Revoke permissions: revoke Permission list on Database name.Table name from 'user name'@'host name'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
constraint
classification
* Concept: limit the data in the table to ensure the correctness, effectiveness and integrity of the data. * Classification: 1. Primary key constraint: primary key 2. Non empty constraint: not null 3. Unique constraint: unique 4. Foreign key constraints: foreign key
1. Non NULL constraint
Add constraints when creating tables
* not null，The value of a column cannot be null CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name Is not empty );
After creating the table, add a non empty constraint
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
Delete non empty constraint for name
ALTER TABLE stu MODIFY NAME VARCHAR(20);
2. Unique constraints
After the table is created, add a unique constraint ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
be careful
When creating a table, add unique constraints
When creating a table, add unique constraints CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- cell-phone number );
Delete unique constraint
ALTER TABLE stu DROP INDEX phone_number;
After the table is created, add a unique constraint
* unique，The value of a column cannot be repeated * Unique constraints can have NULL Value, but only one record can be null
3. Primary key constraint
* primary key. 1. be careful: 1. Meaning: non empty and unique 2. A table can only have one field as the primary key 3. The primary key is the unique identifier of the record in the table
When creating a table, add a primary key constraint
create table stu( id int primary key,-- to id Add primary key constraint name varchar(20) );
Delete primary key
-- error alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY;
After creating the table, add the primary key
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
Automatic growth
(1). Concept: if a column is numeric, use auto_increment Can be done automatically (2). When creating a table, add a 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 automatic growth ALTER TABLE stu MODIFY id INT; (4). Add auto growth ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
4. Foreign key constraints
* foreign key,Let the table have a relationship with the table, so as to ensure the correctness of the data. 1. When you create a table, you can add foreign keys * 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 adding a foreign key to the table, click OK ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);
Cascade operation
1. Add cascading operation Syntax: ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name) ON UPDATE CASCADE -- Add cascade ON DELETE CASCADE ;-- Delete Cascade 2. Classification: 1. Cascading updates: ON UPDATE CASCADE 2. Cascade delete: ON DELETE CASCADE
multi-table query
Query syntax
select Column name list from Table name list where....
Classification of multi table query
Internal connection query
Implicit inner connection
1. Implicit joins: Using where Conditional 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`;
Explicit inner join
2. Explicit inner connection: * Syntax: select Field list from Table name 1 [inner] join Table name 2 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 do you query data 2. What are the conditions 3. Which fields to query
External link query
Left outer connection
1. Left outer connection:
* Syntax: select Field list from Table 1 left [outer] join Table 2 on Conditions;
* Queries all the data in the left table and the intersection with the right table.
* example:
-- Query all employee information. If an employee has a department, the Department name will be queried. 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`;
Right outer connection
2. Right outer connection: * Syntax: select Field list from Table 1 right [outer] join Table 2 on Conditions; * All data in the right table and the intersection with the left table are queried. * example: SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
Subquery
* Concept: nested query in query, which is called sub query. -- Query the information of the highest paid employee -- 1 What is the highest salary SELECT MAX(salary) FROM emp; -- 2 Query employee information, and the salary is equal to 9000 SELECT * FROM emp WHERE emp.`salary` = 9000; -- One sql That's it. Subquery SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
Different sub queries
* Different sub queries 1. The result of subquery is single row and single column: * Subqueries can be used as conditions and operators can be used to judge. Operator: > >= < <= = -- Query employees whose salary is less than the average salary SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp); 2. The result of subquery is multi row and single column: * Subqueries can be used as conditions and operators in To 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 subquery is multi row and multi column: * Subqueries can be queried as a virtual table -- The entry date of the employee is 2011-11-11 Employee information and department information after -- 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'
affair
Basic introduction to transaction
1. Concept:
* If a business operation with multiple steps is managed by a transaction, these operations will either succeed or fail at the same time.
2. Operation:
1. Open transaction: start transaction;
2. Rollback: rollback;
3. Submission: commit;
4. Transaction savepoint: save;(That is, when a transaction is rolled back, you can specify to roll back to the savepoint instead of rolling back all)
* When using save When you need to save and rollback Add a recognition point, which can be any character
Example: save transaction a
rollback transaction a
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 transferred 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';
-- If there is no problem in execution, commit the transaction
COMMIT;
-- A problem is found. Roll back the transaction
ROLLBACK;
4. MySQL Transactions in the database are automatically committed by default
* There are two ways to commit a transaction:
* Auto submit:
* mysql It's submitted automatically
* One DML(Addition, deletion and modification)Statement automatically commits a transaction.
* Manual submission:
* Oracle The database is manually committed by default
* You need to start the transaction before committing
* Modify the default commit method of transaction:
* To view the default commit method of a transaction: SELECT @@autocommit; -- 1 For automatic submission 0 for manual submission
* Modify the default submission method: set @@autocommit = 0;
Four characteristics of transaction
1. Atomicity: it is the smallest indivisible operation unit, which either succeeds or fails at the same time.
2. Persistence: when the transaction is committed or rolled back, the database will persist and save the data.
3. Isolation: between multiple transactions. Independent of each other.
4. Consistency: the total amount of data remains unchanged before and after the transaction operation
Isolation level of transaction
* Concept: multiple transactions are isolated and independent of each other. However, if multiple transactions operate on the same batch of data, some problems will be caused. Setting different isolation levels can solve these problems. * Existing problems: 1. Dirty read: a transaction 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 be modified.
* Isolation level: 1. read uncommitted: Read uncommitted * Problems: dirty reading, unrepeatable reading and unreal reading 2. read committed: Read committed( Oracle) * Problems: unrepeatable reading and unreal reading 3. repeatable read: Repeatable reading( MySQL (default) * The problem: unreal reading 4. serializable: Serialization * Can solve all problems * Note: from small to large, the isolation level has higher and higher security, but lower and lower efficiency * Database query isolation level: * select @@tx_isolation; * Set isolation level for database: * set global transaction isolation level Level string;
stored procedure
Concept of stored procedure
*Concept: stored procedure Procedure Is a group of to accomplish a specific function SQL The statement set is compiled and stored in the database. The user can execute it by specifying the name of the stored procedure and giving parameters. *Stored procedures can contain logic control statements and data manipulation statements. It can accept parameters, output parameters, return single or multiple result sets and return values.
Advantages of stored procedures
1. Can realize modular programming 2. Achieve faster execution speed 3. Reduce network traffic 4. Can be used as a security mechanism
System stored procedure
Common system stored procedures
exec sp_databases; --view the database exec sp_tables; --View table exec sp_columns student;--View column exec sp_helpIndex student;--View index exec sp_helpConstraint student;--constraint exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures'; --View stored procedure creation and definition statements exec sp_rename student, stuInfo;--Modify the names of tables, indexes and columns exec sp_renamedb myTempDB, myDB;--Change database name exec sp_defaultdb 'master', 'myDB';--Change the default database for logins exec sp_helpdb;--Database help, query database information exec sp_helpdb master;
System stored procedure example
--Table rename exec sp_rename 'stu', 'stud'; select * from stud; --Column rename exec sp_rename 'stud.name', 'sName', 'column'; exec sp_help 'stud'; --Rename index exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; exec sp_help 'student'; --Query all stored procedures select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
User defined stored procedure
Create syntax
create proc | procedure pro_name
[{@Parameter data type} [=Default value] [output],
{@Parameter data type} [=Default value] [output],
....
]
as
SQL_statements
Create a stored procedure without parameters
--Create stored procedure
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--Calling and executing stored procedures
exec proc_get_student;
Modify stored procedure
--Modify stored procedure alter proc proc_get_student as select * from student;
Stored procedure with parameters
--Stored procedure with parameters
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
System tables sysobjects, syscolumns and function object id
sysobjects
System object table. Save the objects of the current database, such as constraints, default values, logs, rules, stored procedures, etc sysobjects Explanation of important fields: sysObjects ( Name sysname, --object name id int, --object id xtype char(2), -- object type type char(2), -- Object Type (and xtype It looks as like as two peas. uid smallint, -- object Owner's ID ... --Other fields are not commonly used. ) notes:What needs to be explained is xtype and type It's as like as two peas. C = CHECK constraint D = Default value or DEFAULT constraint F = FOREIGN KEY constraint FN = Scalar function IF = Embedded table function K = PRIMARY KEY or UNIQUE constraint L = journal P = stored procedure R = rule RF = Replication filter stored procedure S = System table TF = Table function TR = trigger U = User table V = view X = Extended stored procedure
sysolumns
All fields of the current database are kept in it. Explanation of important fields: sysColumns ( name sysname, --Field name id int, --The name of the table to which this field belongs ID xtype tinyInt, --This field type is not associated sysTypes surface length smallint, --Physical storage length of this field ...)
sysobjects and syscolumns in SQL
View all table names:
select name from sysobjects where type='U'
All field names of query table:
Select name from syscolumns Where ID=OBJECT_ID('Table name')
Example: Sql_Server How to determine whether a field exists in a table?
*Method 1:
select * from syscolumns where id=object_id('Table name') and name='Listing'
Description: if it exists, a description record of this column will be returned; if it does not exist, it will be null;
*Method 2:
select count(*) from sysobjects a,syscolumns b where a.id=b.id and b.name='flag1' and a.type='u' and a.name='T_Pro_ProductClass'
Description: Return 1 if there is,Returns 0 if it does not exist
Object in SQL_ Usage of ID function
int object_id('objectname');
This method returns the database object identification number.
Where, parameter objectname Represents the object to be used, and its data type is nchar or char(If yes char，The system converts it to nchar)
Return type is int，Indicates the number of the object in the system.
For example:
use wf_timesheet
select object_id('usp_check_excess_hours')
JDBC:
Java database connectivity, Java database connection, Java language operation database
quick get start
1. Import driver jar package mysql-connector-java-5.1.37-bin.jar
1.copy mysql-connector-java-5.1.37-bin.jar To project libs Under the directory
2.Right click-->Add As Library
2. Register driver
3. Get database connection object Connection
4. definition sql
5. Get execution sql Statement object Statement
6. implement sql，Accept returned results
7. Processing results
8. Release resources
* Code implementation:
//1. Import the driver jar package
//2. Register driver
Class.forName("com.mysql.jdbc.Driver");
//3. Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4. Define sql statements
String sql = "update account set balance = 500 where id = 1";
//5. Get the object Statement executing sql
Statement stmt = conn.createStatement();
//6. Execute sql
int count = stmt.executeUpdate(sql);
//7. Treatment results
System.out.println(count);
//8. Release resources
stmt.close();
conn.close();
DriverManager: drive management object
* Function:
1. Register driver: tell the program which database driver to use jar
static void registerDriver(Driver driver) :Registered with the given driver DriverManager .
Write code using: Class.forName("com.mysql.jdbc.Driver");
2.Get database connection:
* method: static Connection getConnection(String url, String user, String password)
* Syntax: jdbc:mysql://ip address (domain name): port number / database name
* example: jdbc:mysql://localhost:3306/db3
* Details: if the connection is local mysql Server, and mysql The default service port is 3306, then url It can be abbreviated as: jdbc:mysql:///Database name
Connection: database connection object
1. Get execution sql Object of * Statement createStatement() * PreparedStatement prepareStatement(String sql) 2. Management services: * Open transaction: setAutoCommit(boolean autoCommit) : Call this method to set the parameter to false，The transaction is started * Commit transaction: commit() * Rollback transaction: rollback()
Statement: object to execute sql
implement sql 1. boolean execute(String sql) : Can perform arbitrary sql understand 2. int executeUpdate(String sql) : implement DML(insert,update,delete)Statement DDL(create，alter,drop)sentence
Return value: the number of affected rows. You can judge whether the DML statement is executed successfully by the number of affected rows. If the return value > 0, the execution is successful, otherwise, it fails.
3. ResultSet executeQuery(String sql) : implement DQL(select)sentence
ResultSet: result set object that encapsulates query results
* boolean next(): The cursor moves down one line to determine whether the current line is at the end of the last line(Is there data)，If yes, return false，If not, return true
* getXxx(parameter):get data
* Xxx: Representative data types, such as: int getInt() , String getString()
* Parameters:
1. int: Represents the number of the column,Starting from 1, such as: getString(1)
2. String: Represents the column name. For example: getDouble("balance")
* be careful:
* Use steps:
1. Move cursor down one line
2. Judge whether there is data
3. get data
//Loop to determine whether the cursor is at the end of the last line.
while(rs.next()){
//get data
//6.2 data acquisition
int id = rs.getInt(1);
String name = rs.getString("name");
double balance = rs.getDouble(3);
System.out.println(id + "---" + name + "---" + balance);
}
PreparedStatement: the object that executes sql
implement sql Object of
1. SQL Injection problem: in splicing sql When, there are some sql Special keywords of participate in string splicing. It will cause security problems
1. Enter the user name and password: a' or 'a' = 'a
2. sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
2. solve sql Injection problem: Using PreparedStatement Object to solve
3. Precompiled SQL: Parameter use?As placeholder
4. Step: 1. Import driver jar package mysql-connector-java-5.1.37-bin.jar
2. Register driver
3. Get database connection object Connection
4. definition sql
* be careful: sql Parameter usage? As a placeholder.
For example: select * from user where username = ? and password = ?;
5. Get execution sql Statement object PreparedStatement Connection.prepareStatement(String sql)
6. Here? Assignment:
SetString/SetInt/SetDouble(Parameter 1,Parameter 2)
* method: setXxx(Parameter 1,Parameter 2)
* Parameter 1:? The position number of starts from 1
* Parameter 2:? Value of
7. implement sql，Accept the returned result without passing sql sentence
8. Processing results
9. Release resources
5. Note: it will be used later PreparedStatement To complete all operations of adding, deleting, modifying and querying
1. Can prevent SQL injection
2. More efficient
case
See example: E:\idea_Projects\javaSE_web\day01\src\Demo03
public static Boolean login(String username, String password) {
if (username == null || password == null) {
return false;
}
Connection conn = null;
PreparedStatement pstate = null;
ResultSet rs =null;
try {
conn = JDBCUtils.getConnection();
//Define sql statement
String sql = "select * from user where username = ? and password = ?";
//Get the object executing sql
pstate = conn.prepareStatement(sql);
//Here? assignment
pstate.setString(1,username);
pstate.setString(2,password);
rs = pstate.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,conn,pstate);
}
return false;
}
Extract JDBC tool class: JDBC utils
* Resolution: Profile jdbc.properties url= user= password=
jdbc tool class
See example: E:\idea_Projects\javaSE_web\day01\src\utils
package utils;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
//standard format
//jdbc tool class
public class ju {
private static String url;
private static String username;
private static String password;
private static String driver;
//Read configuration file
static {
try {
Properties prop = new Properties();
prop.load(ju.class.getClassLoader().getResourceAsStream("jdbc.properties"));
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
driver = prop.getProperty("driver");
//Register driver
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//Get Connection object
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//Release resources
public static void close(ResultSet resultSet,
Connection connection,
Statement statement) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(connection, statement);
}
//heavy load
public static void close(Connection connection,
Statement statement) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Database connection pool
1. Concept: it is actually a container(aggregate)，Container for storing database connections. After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container. 2. Benefits: 1. save resources 2. Efficient user access
C3P0: database connection pool technology
* ##### Steps:
1. Import jar package (Two) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar ，
Don't forget to import the database driver jar package
2. Define profile:
name: c3p0.properties perhaps c3p0-config.xml
Path: put the file directly in src Directory.
3. Create core database connection pool object ComboPooledDataSource
4. Get connection: getConnection
public class c3p0Demo01 {
public static void main(String[] args) throws SQLException {
//Create database connection
DataSource ds = new ComboPooledDataSource();
//Get connection object
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
Druid: implementation technology of database connection pool
-
Steps:
- Import the jar package druid-1.0.9 jar
- Define profile:
It is in the form of properties
You can call any name and put it in any directory
- Load the configuration file. Properties
- Get database connection pool object: get DruidDataSourceFactory through factory
- Get connection: getConnection
public class druidDemo {
public static void main(String[] args) throws Exception {
Properties pro = new Properties();
//Load profile
pro.load(druidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));
//Get connection pool object
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//Get connection
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
Druid: database connection pool extraction Utils tool class
-
Define a class JDBCUtils
-
Provide static code blocks, load configuration files, and initialize connection pool objects
-
Provide method
-
Get connection method: get connection through database connection pool
-
Release resources
-
Method of obtaining connection pool
See E:\idea_Projects\javaSE_web\day05_dataSource_jdbcTemplate\src\cn\itcast\datascoure\utils
public class JDBCUtils { private static DataSource dataSource; static { try { Properties properties = new Properties(); properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties")); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //Method to get Connection public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } //Close resource method public static void close(Connection connection, Statement statement) { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } //Close method (ResultSet) overload public static void close(ResultSet resultSet, Connection connection, Statement statement) { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } close(connection, statement); } //Get connection pool object public static DataSource getDataSource() { return dataSource; } }
Spring JDBC
step
* Steps: 1. Import jar package 2. establish JdbcTemplate Object. Dependent on data source DataSource * JdbcTemplate template = new JdbcTemplate(ds); 3. call JdbcTemplate Method to complete CRUD Operation of
5 methods
update
* update():implement DML sentence. Add, delete and modify sentences Example: String updatesql="update account set banalan = ? where id =?" //Parameter 1: SQL statement to execute //Parameter 2: it is a variable parameter. The position of the parameter and the? The positions of the data types correspond one by one, and the data types are also corresponding template.update(updatesql,5000,3); update Method execution DML Statement, when returning a value int use jdbcTemplate There is no need to release resources manually, jdbcTemplate Help us release resources
Example:
public class jdbcTemplateDemo01 {
public static void main(String[] args) {
//Guide Package
//Create a JDBC template object
JdbcTemplate template= new JdbcTemplate(JDBCUtils.getDataSource());
//Call method
String updateSql = "update emp set salary = ? where id = ?";
int update = template.update(updateSql, 555,1001);
System.out.println(update);
}
}
queryForMap
* queryForMap():Query results encapsulate the result set as map Set with column names as key，Value as value Encapsulate this record into a map aggregate
* Note: the length of the result set of this method query can only be 1
Example:
public void test4() {
//Define query statement
String sql = "select * from emp where id = ?";
Map<String, Object> queryForMap = template.queryForMap(sql, 1001);
System.out.println(queryForMap);
}
queryForList
* queryForList():Query results encapsulate the result set as list aggregate
* Note: encapsulate each record into one Map Set, and then Map Load collection to List In collection
Example:
public void test5() {
String sql = "select * from emp";
List<Map<String, Object>> queryForList = template.queryForList(sql);
for (Map<String, Object> map : queryForList) {
System.out.println(map);
}
}
| a key | |
|---|---|
| queryForMap | Only one piece of data can be queried at a time, and this piece of data is encapsulated in the Map |
| queryForList | It can query multiple pieces of data, encapsulate each piece of data into a Map, and then encapsulate the Map into the List collection |
query
* query():Query results, encapsulating the results as JavaBean object
* query(Parameter 1, parameter 2):
Parameter 1: to execute SQL sentence
Parameter 2: RowMapper
Using anonymous inner class implementation
ResultSet Convert to what we need JavaBean
* Generally we use BeanPropertyRowMapper Implementation class. Can complete data to JavaBean Automatic packaging of
* new BeanPropertyRowMapper<type>(type.class)
Example:
public void test6() {
String sql = "select * from account";
List<Emp> empList = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : empList) {
System.out.println(emp);
}
}
queryForObject
* queryForObject: Query results, encapsulating the results as objects
* It is generally used for queries of aggregate functions
Example:
public void test7() {
String sql = "select count(id) from emp";
Integer i = template.queryForObject(sql, Integer.class);
System.out.println(i);
}
Map<String, Object> queryForMap = template.queryForMap(sql, 1001);
System.out.println(queryForMap);
}
### queryForList
```java
* queryForList():Query results encapsulate the result set as list aggregate
* Note: encapsulate each record into one Map Set, and then Map Load to collection List In collection
Example:
public void test5() {
String sql = "select * from emp";
List<Map<String, Object>> queryForList = template.queryForList(sql);
for (Map<String, Object> map : queryForList) {
System.out.println(map);
}
}
| a key | |
|---|---|
| queryForMap | Only one piece of data can be queried at a time, and this piece of data is encapsulated in the Map |
| queryForList | It can query multiple pieces of data, encapsulate each piece of data into a Map, and then encapsulate the Map into the List collection |
query
* query():Query results, encapsulating the results as JavaBean object
* query(Parameter 1, parameter 2):
Parameter 1: to execute SQL sentence
Parameter 2: RowMapper
Using anonymous inner class implementation
ResultSet Convert to what we need JavaBean
* Generally we use BeanPropertyRowMapper Implementation class. Can complete data to JavaBean Automatic packaging of
* new BeanPropertyRowMapper<type>(type.class)
Example:
public void test6() {
String sql = "select * from account";
List<Emp> empList = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : empList) {
System.out.println(emp);
}
}
queryForObject
* queryForObject: Query results, encapsulating the results as objects
* It is generally used for queries of aggregate functions
Example:
public void test7() {
String sql = "select count(id) from emp";
Integer i = template.queryForObject(sql, Integer.class);
System.out.println(i);
}