mysql basic practice

DDL: operation database and table

1.Operation database: CRUD 1.C(Create): establish * Create database * create database Database name; * Create a database, judge whether it exists, and then create it * create database if not exists Database name; * establish db1，Determine whether it exists and specify the end of the character set gbk * create database if not exists db1 character set gbk; 2.R(Retrieve): query * Query the names of all databases * show databases; * View the character set of a database * show create databases Database name; 3.U(Updata): 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 * drop database if exists Database name; 5.Use database * Query the database currently in use * select database(); * Use database * use Database name; 2. Operation table 1.C(Create): establish 1.grammar create table Table name( Column name 1, data type 1, Column name 2, data type 2, ... Listing n data type n ); * Note: comma is not required for the last column(,) * data type 1.int: Integer type * age int, 2.double: Decimal type * score double(5,2) 3.data:Date, only including mm / DD / yyyy, yyyy-MM-dd 4.datatime:Date, including month, day, hour, minute and second, yyyy-MM-dd HH:mm:ss 5.timestamp:Timestamp 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，By default, the current system time is used for automatic assignment 6.varchar: character string * name varchar(20):Name up to 20 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 Replicated table name; 2.R(Retrieve): query * Query all table names in a database * show tables; * Query table structure * desc Table name; 3.U(Updata): modify 1.Modify table name * alter table Table name rename to New table name; 2.Modify the character set of the table * alert 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 New class name after column name modification new data type after modification; * alter table Table name modify Column name new data type; 5.Delete column * alter table Table name drop Listing; 4.D(Delete): delete * Delete table * drop table Table name; * drop table if exists Table name;

DML: adding, deleting and modifying data in the table

1. Add data:

• Syntax:

  • insert into table name (column name 1, column name 2,...) values (data type 1, data type 2,...);

    • 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 values will be added by default.

      insert into table name values (value 1, value 2, value 3,...)

    • 3. In addition to the number type, other types need to be enclosed in quotation marks (either single or double)
      2. Delete data

      * Syntax:

      • delete from table name [where condition]
      • be careful:
        1. If no conditions are 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 name-- Recommended, more efficient. Delete the table first, and then create an empty table exactly the same

3. Modify data
* syntax:
* update table name set column name 1 = value 1, column name 2 = value 2,... [where condition]
* Note:
1. If no conditions are added, all records in the table will be modified

DQL: querying records in a table

* select * from Table name;

DQL: query statement

create table student2(
id int,
name varchar(32),
age int,
sex char,
address varchar(32),
math double,
english double
);

insert into student2 values (1, "Ma Yun", 55, "male", "Hangzhou", 66,78);

select * from student2;

insert into student2 values (2, "Ma Huateng", 45, "male", "Shenzhen", 98,87);

insert into student2 values (3, "Ma Jingtao", 55, "male", "Hong Kong", 56,77);

insert into student2 values (4, "Liu Yan", 20, "female", "Hunan", 76,65);

insert into student2 values (5, "Liu Qing", 55, "male", "Hunan", 86,null);

insert into student2 values (6, "Andy Lau", 57, "male", "Hong Kong", 99,99);

insert into student2 values (7, "Ma De", 22, "female", "Hong Kong", 99,99);

insert into student2 values (8, "demacia", 18, "male", "Nanjing", 56,65);

select * from student2 order by math – sort method;

select * from student2 order by math Desc;

– rank according to math scores or English scores if math scores are the same
select * from student2 ORDER BY math ASC,english DESC;
1. Sort query
*Syntax: order by clause
*order by sort field 1, sort by 1, sort field 2, sort by 2
*Sort by:
*ASC: ascending, default
*DESC: descending order
*Note:
*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: perform vertical calculation by taking a column of data as a whole 1.count: Number of calculations 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 select count(name) from student2; select count(ifnull(english,0)) from student2; select max(math) from student2; select min(math) from student2; select sum(math) from student2; select sum(english) from student2; select avg(math) from student2; 3.Group query: 1.Syntax: group by Grouping field; 2.be careful: 1.The fields queried after grouping can only be grouped fields and 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 It is qualified after grouping. If the conditions are not met, it will not be queried 2.where Can not be followed by aggregate function, having Then you can judge the aggregation function. -- Grouped by gender. Group query the average scores of male and female students select sex,avg(math) from student2 group by sex; -- Grouped by gender. Query the average score and number of male and female students in groups select sex,avg(math),count(id) from student2 group by sex; -- Grouped by gender. Group query the average score and number of male and female students: those with a score less than 70 will not participate in the group select sex,avg(math),count(id) from student2 where math>70 group by sex; -- Grouped by gender. Group query the average score and number of male and female students: those with a score less than 70 will not participate in the group.More than 2 persons select sex,avg(math),count(id) from student2 where math>70 group by sex having count(id)>2; select sex,avg(math),count(id) as Number of people from student2 where math>70 group by sex having Number of people>2;

​
4. Paging query
1. Syntax: the index starting from limit and the number of queries per page;
2. Formula: starting index = (current page number - 1) * number of queries per page
– 3 pieces of data per page
​ select * from student2 limit 0,3; – first page
​

select * from student2 limit 3,3; -- Page 2 select * from student2 limit 6,3; -- Page 3 3.limit It's a Mysql "Dialect" of

DQL: querying records in a table

1.Syntax: select Field list from Table name list where Condition list group by Grouping field having Conditional qualification after grouping order by sort limit paging 2.Basic query 1.Query of multiple fields select Field 1,Field 2... from Table name; *be careful: * If you query all fields, you can use*To replace the field list 2.Remove duplication * distinct 3.Calculation column * Generally, four operations can be used to calculate the values of some columns. (generally, only numerical calculation will be performed) * IFNULL(Expression 1,Expression 2): null Participate in the calculation, and 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 create table student3( id int, name varchar(20), age int, sex varchar(5), address varchar(100), math int, english int ); insert into student3(id,name,age,sex,address,math,english) values(1,"Jack Ma",55,"male","Hangzhou",66,78),(2,"pony ",45,"female","Shenzhen",98,87),(3,"Ma Jingtao",55,"male","Hong Kong",56,77),(4,"Liuyan",20,"female","Hunan",76,65),(5,"Liu Qing",20,"male","Hunan",86,null),(6,"Lau Andy",57,"male","Hong Kong",99,99),(7,"Madder",22,"female","Hong Kong",99,99),(8,"Demacia",18,"male","Nanjing",56,65); select * from student3; -- Query name and age select name,age from student3; select address from student3; -- Remove duplicate result sets select distinct address from student3; select name,address from student3; -- calculation math and english Sum of scores select name,math,english,math+english from student3; -- If so null Participate in the calculation, and the calculation results are null select name,math,english,math+ifnull(english,0) from student3; -- Alias select name,math,english,math+IFNULL(english,0) as grade from student3; 3. Condition query 1.where Sentence followed by condition 2.operator * >,<,<=,>=,=,<> * BETWEEN ... AND * IN(aggregate) * LIKE: Fuzzy query * Placeholder: * _:Single arbitrary character * %:Multiple arbitrary characters (can be 0 or arbitrary) * IS NULL * and or && * or or || * not Or! -- Query who are older than or equal to 20 select * from student3 where age>=20; -- Query people 20 or older select * from student3 where age = 20; -- Query people whose age is not equal to 20 select * from student3 where age!=20; select * from student3 where age<>20; -- Query people aged 20 or older and 30 or younger select * from student3 where age>=20 && age<=30; select * from student3 where age>=20 and age<=30; -- Recommended use select * from student3 where age between 20 and 30; -- Query the information of age 22, 19 and 25 select * from student3 where age=22 or age = 19 or age = 25;-- Recommended use select * from student3 where age=22 || age=19 || age=25; select * from student3 where age in (22,19,25); -- Query for students without English scores select * from student3 where english = null; -- The statement is wrong. null out of commission=(!=)judge select * from student3 where english is null; -- Query English score is not null select * from student3 where english is not null; -- What are the students surnamed MA in the class? select * from student3 where name like "horse%"; -- The second word in the query name is the person select * from student3 where name like "_turn%"; -- A person whose name is three words select * from student3 where name like "___"; -- Query the person whose name contains De select * from student3 where name like "%virtue%";

constraint

* Concept: define the data in the table. Ensure the correctness, effectiveness and integrity of data. * Classification: 1.Primary key constraint: primary key 2.Non empty constraint: not null 3.Unique constraint: unique 4.Foreign key constraints: foreign key * Non empty constraint: not null 1. Add constraints when creating tables create table student04( id int, name varchar(20) not null -- name Is not empty ); 2. After creating the table, add a non empty constraint alter table student04 modify name varchar(20) not null; 3. delete name Non NULL constraint alter table student04 modify name varchar(20); * Unique constraint: unique,The value cannot be repeated -- Add unique constraints when creating tables create table student04( id int, phone_number varchar(20) unique -- Added unique constraint ); select * from student04; insert into student04(id,phone_number) values(1,'1111'); insert into student04(id,phone_number) values(2,'1112'); * 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. A primary key is the unique identifier of a record in a table 2. When creating a table, add a primary key constraint create table student05( id int primary key, -- Add a primary key constraint to a primary key name varchar(20) not null ); 3. Delete primary key alter table student05 drop primary key; insert into student05(id,name) values(1,'Ice Black Tea'); insert into student05(id,name) values(2,'Green Tea'); 4. After creating the table, add the primary key alter table student05 modify id int primary key; 5. Automatic growth 1. Concept: if a column is numeric, use auto_increment Can be used to complete the automatic growth of value 2. When creating a table, add a primary key constraint and complete the primary key growth create table student06( id int primary key auto_increment, -- to id Add primary key constraint name varchar(20) not null ); -- Delete auto growth alter table student06 modify id int; -- Add auto growth alter table student06 modify id int auto_increment; select * from student06; insert into student06 values (null,'aaa'); * Foreign key constraints: foreign key 1. When creating a table, you can add foreign keys to make the relationship between the table and the table, so as to ensure the correctness of the data * 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 employee drop foreign key The name of the foreign key; 3. Add foreign keys after creating tables alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id); create table emp( id int primary key auto_increment, name varchar(20) not null, age int, dep_name varchar(30), -- Department name dep_location varchar(30) -- Department address ); select * from emp; -- Add data insert into emp(name,age,dep_name,dep_location) values('Zhang San',20,'R & D department','Guangzhou'), ('Li Si',21,'R & D department','Guangzhou'),('Wang Wu',20,'R & D department','Guangzhou'),('Lao Wang',20,'Sales Department','Shenzhen') ,('king',22,'Sales Department','Shenzhen'),('Xiao Wang',20,'Sales Department','Shenzhen'); select * from emp; -- Data redundancy -- Solution: split into 2 tables -- Create department table(id,dep_name,dep_location) --One party, main table create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) ); -- Create employee table(id,name,age,dep_id) -- Multi party, from table 4. Cascade operation 1. Add cascaded actions: 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 updata cascade on delete cascade; 2. Classification: 1. Cascaded updates: ON UPDATE CASCADE 2. Cascade deletion: ON DELETE CASCADE

Database design

1. Relationship between multiple tables 1. Classification: 1. One to one (understanding): * Such as: person and ID card * Analysis: a person has only one ID card, and one ID card can only correspond to one person 2. One to many (many to one): * E.g. departments and employees * Analysis: a department has multiple employees, and an employee can only correspond to one department 3. Many to many: * E.g. students and courses * Analysis: a student can choose multiple courses, and a course can also be selected by many students 2. Implementation relationship: 1. One to many (many to one): * E.g. departments and employees * Implementation method: establish the primary key of the party whose foreign key points to one in more than one party 2. Many to many: * E.g. students and courses * Implementation method: the implementation of many to many relationship requires the help of the intermediate table in Chapter 3. The intermediate table contains at least two fields, which are used as the foreign keys of the third sheet and point to the primary keys of the two tables respectively. 3. One to one (understanding): * Such as: person and ID card * Implementation method: one-to-one relationship is implemented. You can add a unique foreign key on either side to point to the key on the other side 2. Paradigm of database design * Concept: some specifications to be followed when designing and database. To follow the latter paradigm requirements, you must first follow all the previous paradigm requirements When designing a relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. Various paradigms present sub specifications, and the higher the paradigm data The smaller the library redundancy. At present, there are six paradigms of relational database: the first paradigm(1NF),Second paradigm(2NF),Third paradigm(3NF),Bath-Codd paradigm(BCNF),The fourth paradigm(4NF)And the fifth paradigm(5NF,Perfect paradigm) * Classification: 1.First paradigm(1NF): Each column is an indivisible atomic data item 2.Second paradigm(2NF): At 1 NF On the basis of, non code attributes must be completely dependent on code(At 1 NF On this basis, the partial functional dependence of non main attributes on main codes is eliminated) * Several concepts: 1. Functional dependencies: A---->B，If passed A The value of the attribute (attribute group) to determine the unique B Property, it is called B Depend on A E.g. student number--->full name. (Student number, course name)-->fraction 2.Full functional dependency: A---->B,If A Is an attribute group, then B The determination of attribute value depends on A All attribute values in the attribute For example: (student number, course name)---->fraction 3.Partial functional dependencies: A---->B，If A Is an attribute group, then B Attribute value determination only depends on A Some values in the attribute group are sufficient For example: (student number)---->Course name)------>full name 4.Transfer function dependency: A----->B， B----->C. If passed A The value of the attribute (attribute group) determines the unique value B Property value, and then through B The value of the attribute (attribute group) determines the unique value C Property, it is called C The transfer function depends on A E.g. student number ---->Department name----->Dean of Department 5.Code: if an attribute or attribute group is completely dependent on all other attributes in a table, the attribute (attribute group) is called the code (candidate 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: the attribute of the division code attribute group 3.Third paradigm(3NF): In 2 NF On this basis, any non primary attribute does not depend on other non primary attributes (in 2 NF (eliminate delivery dependency)

Backup and restore of database

1. Command line: * Backup: mysqldump -u user name -p Password database name> Saved path * Restore: 1. Login database 2. Create database 3. Use database 4. Execute the file. source File path 2. Graphical tools:

Multi table query:

* Query syntax: select Column name list from Table name list where... get ready sql

– 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), salary DOUBLE, join_date date, dept_id int, FOREIGN key (dept_id) REFERENCES dept(id) ); 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-04',2); insert into emp(name,gender,salary,join_date,dept_id) values('Tang Monk','male','9000','2008-08-08',2); insert into emp(name,gender,salary,join_date,dept_id) values('Baigujing','female','5000','2015-10-17',3); insert into emp(name,gender,salary,join_date,dept_id) values('spider goblin','female','4500','2011-05-20',1); * Cartesian product: * There are two sets A，B，Go to all the composition of the two sets * To complete multi table query, you need to eliminate useless data * Classification of multi table query: 1.Internal connection query: 1. Implicit joins: Using where Conditions to eliminate 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`, t1.gender, t2.`name` FROM emp t1, dept t2 WHERE t1.dept_id=t2.id; 2. Show internal connections: * 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. Query data from those tables 2. What are the conditions 3. Query those fields 2.External connection query: 1. Left outer connection: * Syntax: select * from Table name 1 left [outer] join Table name 2 on Conditions; * All data in the left table and its intersection are queried. 2. Right outer connection: * Syntax: select * from Table name 1 right [outer] join Table name 2 on Conditions; * All the data in the right table and its intersection are queried. 3.Subquery: * Concept: nested queries in queries are called subqueries -- Query the highest employee information SELECT *,MAX(salary) salary FROM emp; -- 1 What is the highest salary? nine thousand SELECT MAX(salary) FROM emp; -- 2 Query employee information, and the salary is equal to 9000 SELECT * FROM emp WHERE salary = 9000; -- One sql That's it SELECT * FROM emp WHERE salary =(SELECT MAX(salary) FROM emp); * Different situations of sub query 1. The result of subquery is single row and single column: * Subqueries can be used as conditions and operators 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: * -- query'Finance'All employee information of the Department SELECT id FROM dept WHERE `name`='Finance Department' or name = 'Marketing Department'; 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-05-20 Subsequent employee information and department information SELECT * FROM emp WHERE join_date > '2011-05-20'; SELECT id FROM dept WHERE emp.dept_id = dept.id; SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE join_date > '2011-05-20') t2 WHERE t1.id = t2.dept_id; -- Common internal connection SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND join_date >'2011-05-20';

​

affair

1. Basic introduction to transaction 1. Concept: * If a business operation contains multiple steps and is managed by things, these business operations either succeed or fail at the same time * example: -- Create account table 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); UPDATE account set balance = 1000; SELECT * FROM account; -- Zhang San transferred 500 yuan to Li Si -- 0.Open transaction START TRANSACTION; -- 1.Zhang San account -500 UPDATE account SET balance= 500 WHERE NAME = 'zhangsan'; -- Wrong... -- 2.Li Si account +500 UPDATE account SET balance = 1500 WHERE `name` = 'lisi'; -- If no problem is found in execution, commit the transaction COMMIT; -- A problem is found. Roll back the transaction ROLLBACK; 2. Operation: 1. Open transaction: start transaction; 2. RollBACK : rollback; 3. Commit transaction: commit 3. MySQL Transactions in the database are automatically committed by default * There are two ways to commit a transaction: * Auto submit: * mysql Is automatic submission * One DML(The addition, deletion and modification) statement will automatically commit the transaction * Manual submission: * Oracle The database is manually committed by default * You need to start the transaction before committing * To modify the default commit method of a transaction: * View the default commit method for transactions SELECT @@autocommit; -- 1 Automatic submission on behalf of 0 Submitted manually on behalf of * To modify the default submission method: SET @@autocommit = 0; 2. Four characteristics of transactions: 1. Atomicity: it is an indivisible minimum unit operation, which either succeeds or fails at the same time 2.Persistence: after the transaction is committed or rolled back, the database will persist the data 3.Isolation: multiple transactions are independent of each other 4.Consistency: the total amount of data remains unchanged before and after the transaction operation 3. Isolation level of transactions (understand) * Concept: multiple transactions are isolated and independent of each other. However, if multiple transaction operations process the same batch of data, some problems will occur, which can be solved by designing different isolation levels * Problems: 1. Dirty read: one transaction reads uncommitted data from another transaction 2. Non repeatable read (wasted): in the same transaction, the transactions read twice are 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 the data modified by itself * Isolation level 1. read uncommited:Read uncommitted * Problems: dirty reading, unrepeatable reading (wasted), unreal reading 2. read commited: Read committed * Problems: unrepeatable reading (wasted reading) and unreal reading 3. repeatable read: Repeatable reading * The problem: unreal reading 4. serializable: Serialization * Can solve all problems * Note: from small to large, the isolation sector is more and more safe, but less and less efficient * Database isolation domain: * set global transaction isolation level Level string;

DCL: used for management and authorization

* SQL Classification: 1. DDL: Operations on databases and tables 2. DML: Add, delete and modify data in the table 3. DQL : Data in query table 4. DCL: Manage and authorize users * DBA: Database Administrator * DCL: Management, authorization 1. Manage users 1. Add user: -- Create user CREATE user 'user name' @ 'host name' MIDDLEINT BY 'password'; 2. Delete user: -- delete user DROP USER 'user name'@'host name'; 3. Modify user password: : UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'user name'; UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'user name'; UPDATE USER SET password = password('abc') WHERE USER = 'root'; set password for 'user name'@'host name' = password('New password'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); * mysql I forgot root User password? 1. cmd ---> net stop mysql stop it mysqk service * An administrator is required to run the cmd 2. Start with no authentication mysql Services: mysql -skip-grant-tables 3. Open new cmd Window, direct input mysql Command, click enter to log in 4. use mysql; 5. update user set 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 ---> net start mysql 9 . Log in with a new password 4. Query user: -- 1. switch mysql database use mysql; -- query user surface SELECT * FROM USER; * Wildcard: % Indicates that users can log in to the database on any host 2. to grant authorization: 1. Query authority: -- View permissions show GRANTS FOR 'user name'@'host name'; 2. Grant permissions: -- Grant permissions GRANT Permission list ON Database name.Table name to 'user name'@'host name'; GRANT SELECT ON db2.account to 'lisi'@'%'; -- Grant all permissions to user Zhang San on any database and any table grant all on *.* to 'zhangsan'@'localhost'; 3. Revoke permissions: -- Revoke permissions revoke Permission list on Database name.Table name from 'user name'@'host name'; -- Revoke all permissions of Zhang San revoke all on *.* from 'zhangsan'@'localhost';

​

​

Add new comment

0 comments