Course introduction

Why database

Common database management systems include MySQL, Oracle, DB2, and SqlServer. Programmers in the client program, through the database management system, to add, delete, change and check the data. Among them, MySQL is the most popular database management system due to open source, free and other factors. Taobao, netease, Baidu, Facebook and many other Internet companies are using MySQL, learn MySQL, and have a broad job market.

Data stored in memory cannot be stored persistently, and stored in common files is inconvenient to manage. Benefits of database management systems:

  • Implement data persistence
  • Use a complete management system unified management, easy to query

Database related Concepts

A database is a repository for storing data. It holds a series of organized data.

DBMS: Database management system, database is managed by DBMS.

SQL: Structure Query Language.

The advantage of SQL

  • Almost all DBMSS support SQL
  • Easy to learn
  • Although simple, but powerful, flexible use can complete many complex, advanced functions.

How does a database store data

  1. Put the data on the table, and then put the table into the library.
  2. A database can have multiple tables, each with a unique name.
  3. A table has its own properties, representing the attributes of the data in the table. A table is similar to a class in C++.
  4. A table consists of columns, also known as fields. Each column is like an attribute of a class in C++.
  5. The data in the table is stored in rows, each row resembling an object in C++.

Log in and log out of MySQL service

# MySQL server startup NETstartThe service nameCopy the code
Net stop MySQL serverCopy the code
# connect to local mysql-u root -p
Copy the code
# exit exitCopy the code

MySQL > select * from ‘MySQL’;

View all current databasesshow databases;
Copy the code
Use the library name;Copy the code
View the tables in the libraryshow tables fromThe library;Copy the code
Create table creattableTable name (column name column type, column name column type, column name column type,... ...). ;Copy the code
Check table structuredescThe name of the table.Copy the code

MySQL syntax specification

  • Case insensitive, but it is recommended to uppercase keywords and lower case others
  • Each command ends with a semicolon
  • Commands can be indented and newline
  • Single-line comments
# single-line comment-- Single-line comment (-- followed by space)
Copy the code
  • Multiline comment
/* Multi-line comments */
Copy the code

SQL language segmentation

  • Data Query Language (DQL) : Query function
  • Data Manipulation Language (DML) : Some books collectively refer to the above two languages as DML, which combines the four functions of adding, deleting, modifying and querying.
  • Data Define Language (DDL) : Data definition Language
  • TCL (Transaction Control Language) : Transaction Control Language

The query

Based on the query

# grammarselectQuery listfromThe name of the table.Copy the code
# query a single fieldselect first_name from employees;
Copy the code
Separate multiple fields by commasselect first_name,last_name from employees;
Copy the code
Select * from all columns where id = 1select * from employees;
Copy the code
# namesASKeywords (Spaces will work)select first_name ASName, last_name surnamefrom employees
Copy the code
# to heavyDistinctThe keywordselect distinctLast_name surnamefrom employees
Copy the code

Conditions of the query

# grammarselectQuery listfromThe name of the tablewhereScreening conditions;Copy the code
# conditional operator> 
<
< =
> =
<> 或 ! =
Copy the code
# logical operatorsand 或 &&
or 或 || 
not 或 !
Copy the code
Query by conditional operatorselect 
	* 
from 
	employees 
where 
	salary>12000;
Copy the code
The logical operator joins the conditional operator queryselect 
	* 
from 
	employees 
where 
	salary>12000 
and
	salary<180000;
Copy the code
# fuzzy querylike 
between and
in
is null
Copy the code
/* like keyword query string requires wildcard % can match several characters (including 0) _ can match one character */
select 
	last_name
from 
	employees
WHERE
	last_name like '_r%'
/* Results Ernst Greenberg Urman... . * /
Copy the code
# Escape character: backslash orescapeThe keywordselect 
	last_name
from 
	employees
WHERE
	last_name like '\ _ %'Select last_name from last_name that begins with an underscorelike 'a_%' escape 'a'# is equivalent to the above statementCopy the code
# between andFind the number in the range of the intervalselect 
	last_name,salary
from 
	employees
WHERE
	salary between 10000 and 15000
Copy the code
# inDetermines whether a field value belongs to an item in the listselect 
	last_name,salary
from 
	employees
WHERE
	salary in (12000.13000)
Copy the code
# is nullCheck whether the field value isnull
# =and! =Can't judgenull

select 
	last_name,salary
from 
	employees
WHERE
	commission_pct is null;
#	commission_pct is not null;
Copy the code

Sorting query

# order bySorted list (ascAscending order.desc# Pay attention to filter first (whereAfter orderingSELECT
	employee_id,salary,manager_id
From 
	employees
where 
	salary BETWEEN 10000 and 20000
order by 
	salary desc; # Multi-keyword sortSELECT
	employee_id,salary,manager_id
From 
	employees
where 
	salary BETWEEN 10000 and 20000
order by 
	manager_id asc,salary desc; Select manager_ID from manager_id and salary from manager_id.Copy the code

function

# callselectFunction name (argument list)Copy the code

Common character functions

Note that indexes in the SQL language all start at 1.

# Get string length (bytes)selectlength("abc123"); String concatenationselect concat(first_name,' ',last_name) 'name'
fromemployees; # capitalize the first letter of last_name and lower case the restselect concat(upper(substr(first_name,1.1)),lower(substr(first_name,3))) 'name'
from employees;
Copy the code

Common mathematical functions

Round roundselect round(1.456.2); # ceil rounded upselect ceil(1.1); # floor is rounded downselect floor(1.9);
Copy the code

+ sign in SQL

The + sign in SQL can only be used to calculate between numbers. If one side is not a number, the system tries to convert it to a number. If either party is NULL, the result must be NULL.

To be continued… .