- Database: a collection that stores, maintains, and manages data
- DataBase Management System (DBMS)
Common database management system
- Relational type
- Relational type
Microsoft SQL Server
- First normal form (1NF): no duplicate columns;
- The second normal form (2NF): attributes are completely dependent on the primary key [eliminating some sub function dependencies]. The data of each row cannot be repeated, that is, each instance or row can be uniquely distinguished. In order to distinguish, a column is added to the table to store the unique identification of the instance. This unique attribute column is called the primary key, or primary key, or primary code
- The third normal form (3NF): attributes do not depend on other non primary attributes [eliminate transitive dependency].
If two tables store information, table A has A name and table B does not have A name
Relationship is essentially a two-dimensional table, in which each row is a tuple and each column is an attribute
- Start the MySQL service with the command net start mysql8.
Stop the service with the command net stop mysql8.
- Structured query language
- CRUD(Create, Read, Update, and Delete)
- DDL (Data Definition Language): Data Definition Language, which is used to define database objects: libraries, tables, columns, etc.
- DML (data management language): data operation language, which is used to define the addition, deletion and modification of database records (data).
- DCL (Data Control Language): Data Control Language used to define access rights and security levels.
- DQL (Data Query Language): a Data Query Language used to query records (data).
Ends with a semicolon and is not case sensitive
DDL operation database
The CREATE DATABASE statement is used to create a new database:
Coding method: gb2312,utf-8,gbk,iso-8859-1
//create database database name CREATE DATABASE mydb1; //create database database name character set encoding method CREATE DATABASE mydb2 character SET GBK; //create database database name set encoding method collate collation CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
view the database
show databases; View all databases in the current database server
show create database database name; View the definition information of the mydb2 database created earlier
modify the database
alter database database name character set encoding method
ALTER DATABASE mydb2 character SET utf8; Check the database in the server and modify the character set of mydb2 to utf8;
drop database database name
Select database(); View the database currently in use
USE mydb2; Switch database
DDL operation table
Create a new table
CREATE TABLE Table name( Column name 1 data type [constraint], Column name 2 data type [constraint], Listing n data type [constraint] ); //Note: the table name and column name are user-defined. Multiple columns are separated by commas. The comma of the last column cannot be written
Common data types: int: integer double: Floating point, for example double(5,2)Indicates a maximum of 5 digits, of which there must be 2 decimal places, that is, the maximum value is 999.99；Rounding is supported by default char: Fixed length string type; char(10) 'aaa ' 10 places varchar: Variable length string type; varchar(10) 'aaa' Third place text: String type, such as novel information; blob: Byte type, save file information(Video, audio, pictures)； date: Date type, format: yyyy-MM-dd； time: Time type, format: hh:mm:ss timestamp: Timestamp type yyyy-MM-dd hh:mm:ss It will be assigned automatically datetime:Date time type yyyy-MM-dd hh:mm:ss
//delete drop table Table name; //Show all tables show tables; //View the field information of the table desc Table name; //Add column alter table Table name add New column name new data type //Modify the column to a length of 60 alter table Table name change Old column name new column name new data type(60); //Change the column name to username ALTER TABLE user CHANGE name username varchar(100); //Delete the image column. Only one column can be deleted at a time. alter table Table name drop Listing //Modify table name alter table Old table name rename New table name; //View table creation details show create table Table name; //Modify the character set of the table alter table Table name character set Coding mode
Addition, deletion and modification;
String type and date type should be enclosed in single quotation marks;
//insert insert into Table name(Listing) values(Data value); // -Note: //one Multiple columns and column values are separated by commas //two The column name should correspond to the column value one by one. Single quotation marks should be added around the non numeric column value. Empty value: null //three You can omit column names - > when adding data to all columns, in order //Add multiple rows at the same time insert into Table name(Listing) values(First row of data),(Second row data),(),(); //Query all data select * from Table name //Modify (update) operation UPDATE Table name SET Column name 1=Column value 1,Column name 2=Column value 2 ... WHERE Listing=value //Delete operation DELETE from Table name[ WHERE Listing=Value] - DELETE Delete the data in the table, and the table structure is still in use;The deleted data can be retrieved - TRUNCATE Delete is to delete the table directly DROP Drop, and then create the same new table. - Deleted data cannot be retrieved. Execution speed ratio DELETE Come on. //Null condition: column name is null (no assignment) or column name = '' (empty string) //Get current system time: select now();
insert into manager values(1, 'Prince', 18, 'male', '123', 'Beijing', '110'), (2, 'princess', 20,' female ',' 456 ',' Shanghai ',' 220 '), (3,' Prince ', 23,' male ',' 789 ',' Nanjing ',' 330 ');
Access rights and security levels;
//Create user create user user name@appoint ip identified by password;// Only the specified IP can log in (user name without quotation marks and password with quotation marks) create user user name@'% ' identified by password //Any IP can log in //User authorization grant Authority 1,Authority 2,........,jurisdiction n on Database name.* to user name@IP; Grant the specified database permissions to the specified user //For example: (user name in quotation marks) grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1'; grant all on *.* to 'test456'@'127.0.0.1'//Grant all database permissions to the specified user //User authority query show grants for user name@IP;(User name (quoted) //Revoke user rights revoke Authority 1,Authority 2,........,jurisdiction n on Database name.* from user name@IP; //Delete user: drop user user name@IP;
Query record (data)
The returned result set is a virtual table
Query keyword: SELECT
SELECT Listing FROM Table name[ WHERE --> BROUP BY-->HAVING--> ORDER BY] * Represents all columns
//Query all columns SELECT * FROM stu; //Query specified column SELECT sid, sname, age FROM stu; //Condition query give WHERE Clause, in WHERE The following operators and keywords can be used in clause: =,!=,<>,<,<=,>,>=; BETWEEN...AND； IN(set)； IS NULL； AND；OR； NOT； //Range query Listing in (Column value 1,Column value 2) SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003'); SELECT * FROM tab_student WHERE sid NOT IN('S1001','S1002','S_1003'); //Query records with null age SELECT * FROM stu WHERE age IS NULL; //Interval query SELECT * FROM stu WHERE age BETWEEN 20 AND 40;//With critical values //Query the records of non male students SELECT * FROM stu WHERE gender!='male'; //or SELECT * FROM stu WHERE NOT gender='male';
//Use keyword LIKE Listing like 'expression' //The expression must be a string //Wildcard: _(Underline): Any character %: Any 0~n Characters,'Zhang%' //Query the student record whose name is composed of five letters and the fifth letter is "i" SELECT * FROM stu WHERE sname LIKE '____i'; //)Query student record 'z%' whose name starts with "z" //The second letter is "I" '_i%' //Contains' a ''% a% '
Field control query
//(1) Remove duplicate records SELECT DISTINCT sal FROM emp; //(2) View the sum of sal and comm columns SELECT *,sal+comm FROM emp; // Convert NULL to a value of 0 (anything added to NULL will still be NULL) SELECT *,sal+IFNULL(comm,0) FROM emp; //(3) Add the alias ass(sal+IFNULL(comm,0) to the column name, which is not beautiful) SELECT *, sal+IFNULL(comm,0) AS total FROM emp; //AS can be omitted
//Sort and query select *from stu order by Listing asc((default)/desc; //Multi column sorting: when the values of the previous columns are the same, the subsequent column values will be sorted SELECT * FROM emp ORDER BY sal DESC,empno ASC;
A function used to do vertical operations
COUNT(Listing): Statistics specified column is not NULL Number of record lines; SELECT COUNT(*) FROM emp;//All columns
- GROUP BY
//Query the department number and salary of each department: SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
- HAVING clause
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
The difference between having and where:
1.having is to filter data after grouping, and where is to filter data before grouping
2. Grouping function (statistical function) can be used after having, but not after where
Limit the starting row of the query result and the total number of rows
Subscript starts at 0
// Query 5 rows of records, starting from 0 Note that the starting line starts from 0, that is, the first line! 2.Query 10 rows of records, starting from 3 3.8.1 Paging query If there are 10 records on one page, how should I check the records on page 3? l The starting behavior of the first page record is 0, and a total of 10 rows are queried; limit 0,10 l On the second page, the record start behavior is 10, and a total of 10 lines are queried; limit 10,10 SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno; SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000; SELECT * FROM emp LIMIT 0, 5;
##Development tools to achieve database operation
Multi table associated query?