Database notes 01 introduction to MySQL and SQL statements (Part I)

1, Server, database introduction:

The server:

• Concept: a computer that can provide a service. A server is a combination of hardware and software
• Database server: a computer equipped with database software, which can provide data addition, deletion, modification and query services
• Web server: a computer equipped with web service software (tomcat,nginx,node.js), which can provide Internet services (browsing network resources)

Database:

• Database: data warehouse, a software that can add, delete, modify and query data
• Relational database: data storage is a two-dimensional table composed of rows and columns, which is similar to EXCEL, and there is a corresponding association relationship between tables
• Non relational database: similar to JSON, such as redis {S1: {}, S2: {}}

Common database products:
Oracle: charging for Oracle's large relational database
DB2: charging for IBM's large relational database
MySQL: small and medium-sized relational databases of MySQL company are free. Later, it was acquired by oracle and charged after mysql6 (community version, flagship version)
Sql Server: charging for Microsoft's small and medium-sized relational databases
Sqllite: Android built-in database, micro relational database, free
NoSql series database: not only sql

2, For database access and uninstall after installation:

Post installation access:
Enter cmd in win+R, open it, enter mysql -u root -p, and enter your mysql password on the next line:

At the same time, you can find it by right clicking - > Management - > service in the computer

Uninstall after installation:
MySQL is uninstalled. You must pay attention to it
Uninstall steps: first go to the computer to manage the service: stop the service - > uninstall in the control panel - > residual data has a data path in the installation directory: about 77 lines - > find the corresponding data and delete it

You can also directly delete the corresponding data from the following directory. Note that the programdata directory is hidden by default. You need to display the hidden file in the setting to find the corresponding directory

Or stop with the command: net stop mysql runs as an administrator. services.msc in cmd can open the corresponding management panel

3, Database operation language SQL:

SQL: SQL (Structured Query Language) refers to the structured query language. SQL enables us to access the database. SQL is an ANSI standard computer language. For relational databases, their operating languages should follow the SQL specification, but different databases are allowed, that is, the relationship between Mandarin and dialect. SQL can be divided into DDL, DML, DCL, DQL, etc, Most of what you need to master is DQL (data query language), which is mainly used to query data

1. Database operation language classification:

• 1)DML(data manipulation language):
  They are SELECT, UPDATE, INSERT and DELETE. Just like their names, these four commands are the language used to operate the data in the database.
• 2)DDL(data definition language):
  There are more DDL commands than DML. The main commands include CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the TABLE structure, data type, links and constraints between tables, and most of them are used when creating tables.
• 3)DCL(Data Control Language):
  It is a database control function. It is a statement used to set or change database user or role permissions, including (grant,deny,revoke, etc.) statements. By default, there are only sysadmin, dbcreator, and DB_ Owner or DB_ Only securityadmin and other personnel have the right to execute DCL.

2.DDL:

Database creation language:

Create database: create database mydb;
Delete database: drop database mydb;
Query all databases: show databases; //The semicolon should not be less, indicating the end of the sql statement
 To view database creation statements: show create database mydb;  //You can see the code of the database
 Modify database: alter database mydb character set 'gbk'; 

Table language in Library:

1)Select library first: use mydb;  
//Switch to the current library
2)Table creation: create table student(id int,name varchar(10),sex char(6),sal double(5,2),birthday timestamp); 
//student is the name of the table, 5 in double is a total of 5 digits, 2 is two digits after the decimal point, and timestamp is the timestamp. Note that the field name must not have keywords
3)View all tables in the Library: show tables;
4)View table structure: desc student;
5)Modification of fields in a table (addition, deletion, modification and query): operations on header information:
Add a header field: alter table student add(age int);  
//Field name + data type
 Delete a header field: alter table student drop sex;	  
//Delete sex in the header field
 Modify header field name: alter table student change name username varchar(20); 
//Change the name to the following and the new data type
 Modify the data type of header field: alter table student modify sal int;  
//Change the sal field from double above to int
//The above modify can also be modified using change, just keep the same name; And case is not distinguished in SQL
6)Modify table name: alter table student rename to stu; 
//Change the table name from student to stu
7)Delete this table: drop table student;  
//Delete the student table

3.DML:

1)Insert a row of data into the table:
insert into student(id,name,sex,sal) values(1,'zhangsan','man',999.90); 
//The string is enclosed in single quotation marks, and the information of the specified date is inserted in the format 'YYYY-MM-DD hh:mm:ss'
2)Insert information in individual header fields:
insert into student(id,sal) values(4,234.5);
3)Insert corresponding values into all header fields in the same order as the header, and the header fields can be omitted:
insert into student values(1,'lisi','man',2.0)
4)Delete all: 
delete from student;
5)Condition deletion:
delete from student where id=1;  
//Delete the line with id 1
 delete id For 2 or 3: delete from student where id=2 or id=3; //or,and can be expressed by | &, or is used to juxtapose multiple similar attributes, and is multiple attributes
6)Modify all:
update student set name='newValue';  
//The names of all information in the table are newValue, and multiple attributes are separated by commas
7)Condition modification:
update student set name='zhang' where id=2; 
//Update the name in the second message to zhang