Basic MySQL operations,

Basic MySQL operations,

I. Databases

1. MySQL: relational database, that is, using a two-dimensional table to store data.

2. Common databases

• Oracle: Oracle (with the highest usage );
• DB2: IBM;
• SQL Servlet: Microsoft;
• Sybase: Celes;
• MySQL: Oracle;

3. RDBMS (Relational database management system): Relational database management system.

Ii. SQL:

1. SQL Overview:

• Structured Query Language ).
• SQL enables us to access databases
• SQL is an ANSI standard computer language, SQL99.
• SQL dialect. a dbms not only supports SQL standards, but also has some proprietary syntaxes called dialects. For example, limit statements can only be used in MySQL.

2. SQL syntax

• SQL statements can be written in one or more rows, ending with a semicolon
• Space and indentation can be used to enhance the readability of statements.
• MySQL is case-insensitive. We recommend that you use uppercase keywords.

3. SQL statement Classification

① The Most Important DDL (Data Definition Language) Statement in SQL:

Defines database objects (databases, tables, columns, etc.), creates, deletes, and modifies databases and table structures.

② SQL DML (Data Manipulation Language) Language:

Used to define database records (data), add, delete, and modify table records;

③ SQL DCL (Data Control Language) Language: Used to call access permissions and security levels;

④ SQL DQL (Data Query Language) Language: Used to Query records (Data );

Iii. DDL statements

1. Database

• View All DATABASES: show databases;
• Switch (select the database to be operated) Database: USE Database Name;
• Create database: create database if not exists mydb1 CHARSET = utf8;
  • Create database mydb1
• Delete DATABASE: drop database [if exists] mydb1;
  • Drop database mydb1;
• Modify DATABASE encoding: alter database mydb1 character set utf8;

2. Data Type (column type)

• Int: integer
• Double: Float Type. For example, double (999.99) indicates a maximum of five digits, which must have two decimal places, that is, the maximum value is;
• Decimal: float type. This type is used before the form, because there will be no missing precision issues;
• Char: a fixed-length string type, such as char (255). If the data length is insufficient, the length is specified.
• Varchar: variable-length string type, varchar (65535), Zhang San
• Text (clob): string type
  • • Very small
    • Small
    • Medium
    • Large
  • Blob: byte type:
    • Very small
    • Small
    • Medium
    • Large
• Date: date type, in the format of yyyy-MM-dd;
• Time: time type. Format: hh: mm: ss;
• Timestamp: timestamp type;

All string types in the database must use single quotation marks, and double quotation marks are not allowed. Single quotation marks must also be used for the date type.

3. Table

• Create a table:

Create table [if not exists] TABLE Name (
Column name column type,
Column name column type,
.......
Column name column type
);

• View the names of all TABLES in the current database: show tables;
• View the TABLE creation statement: show create table name;
• View table structure: DESC name;
• Delete TABLE: drop table Name:
• Modify TABLE: Prefix: alter table name
  • Modify to add columns:

Alter table name ADD (
Column name column type,
Column name column type,
.......
Column name column type
);

• • MODIFY the column type (if the modified column already has data, the new type may affect the existing data): alter table name MODIFY column name column type;
  • Modify the column name: alter table name CHANGE original column name new column name column type;
  • Modify the delete column: alter table Name DROP column name;
  • Modify TABLE Name: altet table original TABLE name rename to new TABLE name;

4. DML statements

DQL: SELECT * FROM table name;

DML (data operation language), operations recorded on the table (add, delete, modify)

1. Insert data

• Insert into Table Name (column name 1, column name 2,...) VALUES (column value 1, column value 2 ,....);
  • The name of the column to be inserted is given after the table name. other unspecified columns are equivalent to inserting null values. All insert records are always inserted into one row, and it is impossible to insert half rows.
  • The column VALUES are given after VALUES. The order and number of VALUES must correspond to the specified column.
• Insert into table name VALUES (column value 1, column value 2)
  • If no column is provided, all columns are inserted.
  • The number of values must be the number of columns in this table.
  • Value Order, which must be the same as the column sequence given during table creation.

2. modify data

• UPDATE table name SET column name 1 = column value 1, column name 2 = column value 2,... [WHERE condition]
• Condition (optional ):
  • The condition must be a boolean value or expression: UPDATE t_person SET sex = 'male', age = age + 1 WHERE sid = '1 ';
  • Operator: = ,! =, <>,>, <,> =, <=, BETWEEN... AND, IN (...), is null, NOT, OR, AND
  • UPDATE stu SET age = 36 WHERE name in ('hangsan', 'lisi ');
  • UPDATE stu SET age = 10 WHERE age is null;

3. delete data

• Delete from table name WHERE condition;
• If no value is added, the entire table is deleted.
• Truncate table Name: TRUNCATE is a DDL statement. It first deletes and drops the TABLE, then creates the TABLE, and cannot be undone.

V. DCL

A project creates one user, and a project corresponds to only one database.

This user can only have permissions on this database, and you cannot operate other databases.

1. Create a user

• Create user username @ IP address identified by 'Password ';
  • You can only log on to the specified IP address.

 

mysql> CREATE USER zhangsan@localhost IDENTIFIED BY '123';Query OK, 0 rows affected (0.00 sec)mysql> QUIT;Bye$ mysql -u zhangsan@localhost -pEnter password:ERROR 1045 (28000): Access denied for user 'zhangsan@localhost'@'localhost' (using password: YES)$ mysql -u zhangsan -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.

 

• Create user username @ '%' identified by 'Password ';
  • Users can log on to any IP address.

2. Authorize the user

• GRANT permission 1,..., permission n ON database. * TO username @ IP address;
  • Permissions, users, and data
  • Assign users the specified permissions on the specified database.
  • Example: grant create, ALTER, DROP, INSERT, UPDATE, DELETE, select on mydb1. * TO user1 @ localhost;
    • Grant create, alter, drop, insert, update, delete, and select permissions to user1 users on the mydb1 database.
• Grant all on database. * TO username @ IP address;
  • Assign all permissions to the specified database.

3. Revoke authorization

• REVOKE permission 1,..., permission n ON database. * FROM username @ IP address;
  • Revokes the specified permissions of a specified user on a specified database.
  • Example: revoke create, ALTER, drop on mydb1. * FROM user1 @ localhost;
    • Revoke the create, alter, and drop permissions of user1 users on the mydb1 database.

4. View Permissions

• Show grants for username @ IP Address
  • View permissions of a specified user

5. delete a user

• Drop user username @ IP Address

Vi. DQL

The data query language. The query does not modify the database table records.

(1) Basic Query

1. Field (column) Control

1) query all columns

SELECT * FROM table name;
SELECT * FROM emp;

--> "*" Indicates querying all columns.

2) query specified Columns

SELECT column 1 [, column 2,..., column N] FROM table name;
SELECT empno, ename, sal, comm FROM table name;

3) Only one record is completely repeated.

When multiple rows in the query results are identical, only one row is displayed. This is rarely the case when you query all columns, but it is more likely to query only one or several columns.

Select distinct * | column 1 [, column 2,..., column N] FROM table name;
Select distinct sal FROM emp;

---> Make sure to query the salary of the employee table. If the same salary exists, it is only displayed once.

4) Column Operations

① Addition, subtraction, multiplication, and Division operations can be performed on columns of the quantity type.

　　SELECT sal*1.5 FROM emp;
　　SELECT sal+comm FROM emp;

② Continuous operations can be performed on string types

　　SELECT CONCAT('$',sal) FROM emp;

For example: select concat ('My name', ename, 'my job is ', job) FROM emp;

③ Convert NULL values

Sometimes it is necessary to convert NULL to other values. For example, if the com + 1000 column has a NULL value, then NULL + 1000 or NULL, and we want to treat NULL as 0 for calculation.

　　SELECT IFNULL(comm,0)+1000 FROM emp;

④ Alias Columns

You may have noticed that the column names in the query result set are not very nice after column operations. In this case, we need to create an alias for the column, in this way, the column name in the result set will display the alias.

Select ifnull (comm, 0) + 1000 AS bonus FROM emp;

--> The AS parameter can be omitted.

Select concat ('My name', ename, 'my job is ', job) description FROM emp;

2. condition Control

1) conditional Query

Like the previous UPDATE and DELETE statements, the SELECT statement can also use the WHERE statement to control records.

• SELECT empno, ename, sal, comm FROM emp WHERE sal> 10000 AND comm is not null;
• SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 20000, AND 30000;
• SELECT empno, ename, job FROM emp WHERE jab IN ('manager', 'President ');

2) fuzzy search

When you want to query employees with a name of two characters, you can use fuzzy search.

• SELECT * FROM emp WHERE ename LIKE 'zhang _';

Fuzzy search requires the operator LIKE, which matches any character. Note that a "_" matches only one character rather than multiple characters.

• SELECT * FROM emp WHERE ename LIKE '___';/* Three underlines, whose names consist of three words */
• SELECT * FROM emp WHERE ename LIKE 'zhang % ';/* surname Zhang, the name has no words */

"%" Matches 0 ~ N arbitrary characters

• SELECT * FROM emp WHERE ename LIKE '% A % ';

Employees whose names contain ".

(2) sorting

1) Ascending

SELECT * FROM WHERE emp ORDER BY sal ASC;

---> Sort by sal in ascending order

---> ASC can be omitted.

2) descending order

SELECT * FROM WHERE emp ORDER BY sal DESC;

---> Sort by sal in descending order

---> DESC cannot be omitted.

3) use multiple columns as sorting Conditions

SELECT * FROM WHERE emp ORDER BY sal ASC,  comm DESC;

---> Use sal in ascending order. If sal is the same, use comm in descending order.

(3) Aggregate functions

Aggregate functions are used to perform vertical operations on a column.

1) COUNT

SELECT COUNT(*) FROM emp；

---> Calculate the number of rows of records where all columns in the emp table are not NULL.

SELECT COUNT(comm) FROM emp；

---> Calculate the number of rows of records whose comm column is not NULL in the emp table.

2) MAX

SELECT MAX(sal) FROM emp；

---> Query the highest salary

3) MIN

SELECT MIN(sal) FROM emp；

---> Query minimum wage

4) SUM

SELECT SUM(sal) FROM emp；

---> Query total wages

5) AVG

SELECT AVG(sal) FROM emp；

---> Query average salary

(4) group query

In group query, records are grouped by a column and group information is queried.

For example, you can view the number of records of all departments.

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno；

---> Use the deptno group to query the Department number and the number of records for each department.

SELECT job, MAX(sal) FROM emp GROUP BY job；

---> Use the job group to query the maximum salary of each job.

Group Conditions

Before grouping, the condition is that the salary is greater than 15000. The number of records in each group is queried by group of departments. After grouping, the condition is that the number of records is greater than or equal to 2.

SELECT deptno,COUNT(*) FROM emp WHERE sal>15000 GROUP BY deptno HAVING COUNT(*)>=2;

(5) limit clause (dialect)

LIMIT is used to LIMIT the start row of the query result and the total number of rows.

For example, the query starts with 5th rows, and a total of three rows of records are queried.

SELECT * FROM emp LIMIT 4,3;

---> 4 indicates that the record starts from the first row. 3 indicates that a total of three rows are queried, that is, rows 5, 6, and 7.