catalogue
1.1 common database management systems
2.1 relationship between SQL, DB and DBMS
2.3 SQL statement classification
2.5 understanding of SQL script
2.7 create statement of view table
1, Database description
1. Database
The database can obtain the data in the file through a simple sql statement (the bottom layer is inseparable from IO stream)
1.1 common database management systems
1.2 login to mysql
Open cmd first, and then enter mysql -u user name - p password (you can enter first when entering the password, so it will not be visible when entering the password).
1.3 change password
1.4 mysql uninstall
c disk - program files mysql, uninstall the software first
Next
Disk c - Program Files and ProgramDate, forcibly delete the MySQL directory (ProgramDate is hidden, remember to open it)
2. MySQl details
2.1 relationship between SQL, DB and DBMS
Table 2.2
2.3 SQL statement classification
Learning MySQL mainly focuses on learning general SQL statements. Then, SQL statements include addition, deletion, modification and query. How are SQL statements classified?
2.4 importing data
The data of this demonstration are used in later exercises
Step 1: log in to mysql database management system
Step 2: check which databases are available
show databases； (this is not an SQL statement, but a MySQL command)
Step 3: create our own database (bjpower node)
create database bjpowernode； (this is not an SQL statement, but a MySQL command)
Step 4: use bjpowernode data
use bjpowernode； (this is not an SQL statement, but a MySQL command)
Step 5: check which tables are in the currently used database
show tables； (this is not an SQL statement, but a MySQL command)
Step 6: initialize data
source plus file path (drag and drop directly)
Note: after data initialization, there are three tables (department table, employee table and salary grade table)
Additional:
1. Delete database: drop database bjpwernode;
2. View table structure: desc xxx; (three tables in bjpowernode)
Department table:
mysql> desc dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | DEPTNO | int(2) | NO | PRI | NULL | |Department number | DNAME | varchar(14) | YES | | NULL | |Department name | LOC | varchar(13) | YES | | NULL | |Department location +--------+-------------+------+-----+---------+-------+
Employee table:
mysql> desc emp; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | EMPNO | int(4) | NO | PRI | NULL | |Employee number | ENAME | varchar(10) | YES | | NULL | |Employee name | JOB | varchar(9) | YES | | NULL | |post | MGR | int(4) | YES | | NULL | |Superior leader No | HIREDATE | date | YES | | NULL | |Entry date | SAL | double(7,2) | YES | | NULL | |a monthly salary | COMM | double(7,2) | YES | | NULL | |subsidy | DEPTNO | int(2) | YES | | NULL | |Department number +----------+-------------+------+-----+---------+-------+
Salary scale:
mysql> desc salgrade; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | GRADE | int(11) | YES | | NULL | |Grade | LOSAL | int(11) | YES | | NULL | |minimum wage | HISAL | int(11) | YES | | NULL | |Maximum salary +-------+---------+------+-----+---------+-------+
3. View the data in the table: selcet * from xxx;
Specific data in dept table
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
Specific data in employee table emp
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+
Specific data in salary grade table salgrade
mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
2.5 understanding of SQL script
The document is in At the end of sql, such a file is called "sql script". What is sql script?
2.6 common SQL commands
Many statements for importing presentation data are also common commands
1,select database(); Check which database is currently in use
mysql> select database(); +-------------+ | database() | +-------------+ | bjpowernode | +-------------+
2,select version(); View the version number of mysql
mysql> select version(); +------------+ | version() | +------------+ | 5.7.36-log | +------------+ 1 row in set (0.00 sec)
3. \ c: terminate a statement being compiled
4. Exit command: exit mysql
2.7 create statement of view table
1. Check the statements of the created table: show create table xxx(emp...);
2. View tables in other libraries: show tables from xxx(student, sys, etc.)