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:

1. MySQL: An open source product of Oracle Company. In 2008, MySQL was acquired by Sun Company. In 2009, Sun Company was acquired by Oracle
2. Oracle: closed source Oracle products, the highest performance and most expensive database. Second in market share
3. SQLServer: closed source Microsoft product, the third largest market share of Microsoft solutions
4. DB2: closed source IBM product, used in the entire IBM solution.
5. 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

1. Open source: develop source code for free, profit: through selling services, programmers will provide free upgrades and maintenance
2. 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

1. Query all databases

• Format:show databases;

2. 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

3. Viewing database Details

• Format: show create database Database name;

	show create database db1;
Copy the code

4. Deleting a Database

• Format: drop database Specifies the database name.

	drop database db4;
Copy the code

5. 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

1. 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

2. Query all databases to check whether they are created successfully

	show databases;
Copy the code

3. Whether the character sets of the two databases were queried successfully

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

4. Use mydb1 first and then myDB2

    use mydb1;
	use mydb2;
Copy the code

5. Delete two databases

	drop database mydb1;
	drop database mydb2;
Copy the code

SQL related to tables

• ERROR 1046 (3D000): No database selected

1. 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

2. Query all tables

• Format: show tables;

3. Query table details

• Format: show create table name;

	show create table person;
Copy the code

4. View table fields

• Format: desc table name;

	desc student;
Copy the code

5. Delete table

• Format: drop table name:

	drop table student;
Copy the code

6. Modify the name of the table

• Format: rename table original name to new name;

	rename table person to t_person;
Copy the code

7. 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

8. Drop table field

• Alter table drop alter table drop

	alter table t_person drop salary;
Copy the code

9. 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

1. createcreate table t1(name varchar(10),age int)charset=utf8;
2. Query allshow tables;
3. Query table detailsshow create table t1;
4. Query the table fieldsdesc t1
5. Delete tabledrop table t1;
6. Modify the name of the tablerename table t1 to t2;
7. Add a table fieldalter table t1 add salary int first/ after xxx;
8. Drop table fieldalter table t1 drop salary;
9. 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

1. 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

2. 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;

3. 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

4. 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

1. Insert into values(1, 2);
2. Select * from table_name where table_name = 1;
3. Update table_name set table_name = XXX where table_name = XXX;
4. Delete from table_name where table_name = 1; ### Data type
5. 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

2. 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.
3. 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.

4. 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