MySQL from entry to Entry (01)

# # # database

Learning database is to learn how to communicate with database software,SQL language is used for programmers and database software to communicate language.
DBMS: DataBaseManagementSystem database management system (database) software, including: MySQL/Oracle/used, DB2, SQLite, etc
Common DBMS introduction:

MySQL: An open source product of Oracle Company. In 2008, MySQL was acquired by Sun Company. In 2009, Sun Company was acquired by Oracle
Oracle: closed source Oracle products, the highest performance and most expensive database. Second in market share
SQLServer: closed source Microsoft product, the third largest market share of Microsoft solutions
DB2: closed source IBM product, used in the entire IBM solution.
SQLite: a lightweight database that provides only basic add, delete, and change operations. Installation package tens of k, mainly used in mobile devices and embedded devices.

The whole set of website solutions include: development language operating system Web server software database software
Open source and closed source

Open source: develop source code for free, profit: through selling services, programmers will provide free upgrades and maintenance
Closed source: not open source revenue: By selling products + services, there will be technology attacks, but no closed source product companies will have a group of people responsible for maintenance and upgrade

Structured Query Language: A Structured Query Language used by programmers to communicate with database software (DBMS)
How to connect to the database software: Check the mysql service open the window key +r enter services.msc
Open the Mysql Client in the Mysql/MariaDB folder in the Start menu and enter the password
On Linux or MAC, open the terminal, enter mysql -u user name -p, press Enter, and enter the password

Disconnect: Close the window or execute exit;

Access denied for user 'root'@'localhost' (using password: YESCopy the code

#### Database related SQL statements

To save data in the database software, you need to build a database and then build a table, and finally operate the data in the table

Query all databases

Format:show databases;

Creating a database

Format: create database Database name; Create data using the default character set

	create database db1;
Copy the code

Character set format: create database Database name character set UTF8 / GBK;

	create database db2 character set utf8;
	create database db3 character set gbk;
Copy the code

Viewing database Details

Format: show create database Database name;

	show create database db1;
Copy the code

Deleting a Database

Format: drop database Specifies the database name.

	drop database db4;
Copy the code

Using a database

You must use the database before operating on tables and data. Otherwise, an error will be reported
Format: use database name;

	use db1;
Copy the code

### database

Create character set utf8 for myDB1 and GBK for myDB2

	create database mydb1 character set utf8;
	create database mydb2 character set gbk;
Copy the code

Query all databases to check whether they are created successfully

	show databases;
Copy the code

Whether the character sets of the two databases were queried successfully

    show create database mydb1;
	show create database mydb2;
Copy the code

Use mydb1 first and then myDB2

    use mydb1;
	use mydb2;
Copy the code

Delete two databases

	drop database mydb1;
	drop database mydb2;
Copy the code

SQL related to tables

ERROR 1046 (3D000): No database selected

Create a table

Format: create table Table name (field name field type, field name field type);

	create table student(name varchar(10),age int);	
Copy the code

Charset = UTF8 / GBK; charset=utf8/ GBK;

create table person(name varchar(10),gender varchar(5))charset=gbk;
Copy the code

Query all tables

Format: show tables;

Query table details

Format: show create table name;

	show create table person;
Copy the code

View table fields

Format: desc table name;

	desc student;
Copy the code

Delete table

Format: drop table name:

	drop table student;
Copy the code

Modify the name of the table

Format: rename table original name to new name;

	rename table person to t_person;
Copy the code

Add a table field

Alter table name add name type;
Alter table table_name add table_name first;
Alter table table_name add table_name type after XXX;

	alter table t_person add salary int;
	alter table t_person add id int first;
	alter table t_person add age int after name;
Copy the code

Drop table field

Alter table drop alter table drop

	alter table t_person drop salary;
Copy the code

Alter table field

Alter table alter table name change original name new name new type;

	alter table t_person change age salary int;
Copy the code

Table related SQL statement review

createcreate table t1(name varchar(10),age int)charset=utf8;
Query allshow tables;
Query table detailsshow create table t1;
Query the table fieldsdesc t1
Delete tabledrop table t1;
Modify the name of the tablerename table t1 to t2;
Add a table fieldalter table t1 add salary int first/ after xxx;
Drop table fieldalter table t1 drop salary;
Alter table fieldalter table t1 changeOriginal name new name new type;

#### table related exercises:

Create database mydb1 character set UTF8 and use the database

	create database mydb1 character set utf8;
	use mydb1;
Copy the code

Alter table emP; alter table emP; alter table emP

	create table emp(name varchar(10)) charset=utf8;
Copy the code

Add table field age at the end

	alter table emp add age int;
Copy the code

Add the ID field first

	alter table emp add id int first;
Copy the code

Add gender after name

	alter table emp add gender varchar(5) after name;
Copy the code

Change gender to salary sal

	alter table emp change gender sal int;
Copy the code

Delete age;

	alter table emp drop age;
Copy the code

Change the table name to T_emp

	rename table emp to t_emp;
Copy the code

Delete t_emp table

	drop table t_emp;
Copy the code

Deleting a Database

	drop database mydb1;
Copy the code

Data related SQL

To execute SQL related to data, you must ensure that a database is already in use and that a table for the data exists

	create database mydb2 character set utf8;
	use mydb2;
	create table person(name varchar(10),age int)charset=utf8;
Copy the code

Insert data

Insert into values(1, 2, 3); insert into values(1, 2, 3);

	insert into person values('Tom'.18);
Copy the code

Insert into values(1, 2); insert into values(1, 2); insert into values(1, 2);

	insert into person(name)values('Jerry');
Copy the code

F insert into person values(‘ Lucy ‘,20),(‘ Lily ‘,21);

	insert into person(name)values('zhangsan'), ('lisi');
Copy the code

Insert Chinese:

	insert into person values('Andy Lau'.30);
Copy the code

If an error message is displayed during the execution of the above code, execute the following SQLCopy the code

set names gbk;
Copy the code

Query data

Select * from table where (select * from table where (select * from table where))
For example:

Query all names in the person table

select name from person;

Query name and age from person table where age > 20

select name,age from person where age>20;

Query all field information for all data in the person table

select * from person;

Modify the data

Update table_name set table_name = XXX, table_name = XXX where table_name = XXX;
For example:

	update person set age=8 where name='Tom';
	update person set age=10 where age is null;
Copy the code

Delete the data

Delete from table name where condition;
For example:
1. Delete Tom

	delete from person where name='Tom';
Copy the code

2. Delete those younger than 20 years oldCopy the code

	delete from person where age<20;
Copy the code

3. Delete all dataCopy the code

	delete from person;
Copy the code

### add/delete

Insert into values(1, 2);
Select * from table_name where table_name = 1;
Update table_name set table_name = XXX where table_name = XXX;
Delete from table_name where table_name = 1; ### Data type
Integer types: int(m) and BigInt (m) Bigint is equivalent to long in Java, where M represents the display length and zerofill keyword is used

	create table t1(name varchar(10),age int(10) zerofill);
	insert into t1 values('Tom'.18);
	select * from t1;
Copy the code

Float numbers: double(m,d) m for total length D for decimal length 58.234 m=5 d=3, ultra-high precision float numbers decimal(m,d) are only used when ultra high precision operations are involved.
String:

Char (m): fixed length m=10 the execution efficiency is higher than that of ABC. The maximum execution efficiency is 255
Varchar (m): variable length m=10 ABC takes 3 to save more storage space. If the maximum value exceeds 65535, text is recommended
Text (m): The maximum value is 65535.

Date:

Date: Only the year, month and day can be saved
Time: Only minutes and seconds can be saved
Datetime: saves year, month, day, hour, minute, second. The default value is null and the maximum value is 9999-12-31
Timestamp: timestamp (milliseconds from 1970), saves year, month, day, hour, minute, second. Default value: current system time, maximum value 2038-1-19
For example:

	create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
	insert into t_date values('2020-1-18'.null.null.null);
	insert into t_date values(null.'17:35:18'.'the 2020-3-17 12:30:23'.null);
Copy the code

Related Posts

ES deep paging solution

Performance monitoring blackbox_exporter+Prometheus+Grafana network detection

Flask Usage details ⚡ Basics | Python theme month