MySQL Basics 1

I. Introduction of the Database
1. Structured Query Language (Structured Query language)
2, SQL: industry standard. (supported by each database vendor)
Sql-server: The standard has been extended. TSQL dialect
Oracle: Scaling standards. Plsql

Provisions:
Shell>window under command
An order in the mysql>mysql, usually by; end (enter database)

Second, install MySQL database
How to verify that the installation is successful!
Shell>mysql-u Root-psorry
Show all databases
Mysql>show DATABASES;
In addition to test, the other 2 databases do not move.
Iii. SQL statements
1. Ddl:data definition Language (data definition language)
Keyword: CREATE ALTER DROP (Action object: Database and table structure. is to define the structure of the database or table)
------------------------------------------------------------------
Create a database called MYDB1.
Mysql>create DATABASE mydb1;
View the database creation details
Mysql>show CREATE DATABASE mydb1;
Create a MYDB2 database that uses the GBK character set.
Mysql>create DATABASE mydb2 CHARACTER SET GBK;
Create a MYDB3 database that uses the UTF8 character set and with the proofing rules.
Mysql>create DATABASE mydb3 CHARACTER SET UTF8 COLLATE utf8_general_ci;
View all databases in the current database server
Mysql>show DATABASES;
View the definition information for the MYDB2 database that you created earlier
Mysql>show CREATE DATABASE mydb2;
Delete the previously created MYDB2 database
Mysql>drop DATABASE mydb2;

View the database in the server and modify the character set of the MYDB1 library to GBK;
Mysql>alter DATABASE mydb1 CHARACTER SET GBK;
Back up the data in the test library and restore

Backup:
Shell>mysqldump-h localhost-u Root-psorry Test>c:/test.sql
Recover database: (delete test database)
Create test Database
Mysql>create DATABASE test;
Mysql>use test;
Recovery:
Way One:
Mysql>source C:/test.sql;
Select Database
Mysql>use test;
Way two:
Shell>mysql-u Root-psorry Test<c:/test.sql

Show all tables in the database
Mysql>show TABLES;
-------------------------------------------------
Create an Employee Information table
Mysql>create TABLE Employee (
ID int,
Name varchar (100),
Gender varchar (10),
Birthday date,
Entry_date date,
Job varchar (100),
Salary float (8,2),
Resume varchar (200)
);
View all tables in the database
Mysql>show TABLES;
View the structure of a table
MYSQL&GT;DESC employee;
An image column is basically added to the employee table above.
Mysql>alter TABLE employee ADD (image blob);
Modify the job column so that it has a length of 60.
Mysql>alter TABLE employee MODIFY Job varchar (60);
Delete the gender column.
Mysql>alter TABLE employee DROP gender;
The table name is changed to user.
Mysql>rename TABLE employee to user;
Modify the table's character set to Utf-8
Mysql>alter TABLE user CHARACTER SET UTF8;
Column Name name modified to username
Mysql>alter TABLE User change name username varchar (100);

* 2, Dml:data manipulation Language (Data manipulation language)
Keywords: INSERT UPDATE DELETE (Action object: data in table. Is the record used in the Operation table)
------------------------------------------------------------------
In MySQL, the data for a character or string or date type is enclosed in single quotation marks. The keyword NULL represents a null value, unlike an empty string value. ‘‘
Use the INSERT statement to insert information for three employees into a table.
Mysql>insert into User (Id,username,birthday,entry_date,job,salary,resume) VALUES (1, ' Huangshanshan ', ' 1992-09-08 ', ' 2012-11-15 ', ' CEO ', ' 10000 ', ' beautiful girl ');
Mysql>insert into the user VALUES (2, ' Niuyang ', ' 1992-09-08 ', ' 2012-11-15 ', ' CTO ', ' 10000 ', ' beautiful boy ');
Mysql>insert into User VALUES (3, ' in Lin ', ' 1992-09-08 ', ' 2012-11-15 ', ' CMO ', ' 10000 ', ' handsome pot ');
What is the encoding used to tell the server client?
Mysql>set CHARACTER_SET_CLIENT=GBK;
View data
Mysql>select * from user;
Resolve the returned data in Chinese garbled
Mysql>set CHARACTER_SET_RESULTS=GBK;
Various encodings in MySQL:
See where the code is used
Mysql>show VARIABLES like ' character% ';
Character_set_client: This variable notifies the server client what encoding to use
Character_set_connection: What encoding is used when this variable notifies the server client of the link
Character_set_database: Code used in the database
Character_set_results: Is the character set that the database uses when returning to the client, and if not specified, the default character set of the server is used.
Character_set_server: Is the default character set specified when the server is installed
Character_set_system: Is the character set set used by the database system.

Revise the salary of all employees to 5000 yuan.
Mysql>update user SET salary=5000;
Change the salary of the employee named ' Niuyang ' to 30000 yuan.
Mysql>update user SET salary=30000 WHERE username= ' Niuyang ';
Change the salary of the employee named ' Huangshanshan ' to $20000 and the job to HR.
Mysql>update user SET salary=20000,job= ' HR ' WHERE username= ' Huangshanshan ';
The salary of Lian Lin will be increased by 10000 yuan on the original basis.
Mysql>update user SET salary=salary+10000 WHERE username= ' to Lian Lin ';

Deletes a record in the table named ' to Lin '.
Mysql>delete from user WHERE username= ' to Lian Lin ';
Deletes all records in the table. (delete one by one)
Mysql>delete from user;
Use truncate to delete records in a table. (Destroy the entire table, then rebuild the table structure)
Mysql>truncate user;

------------------------------------------------------------------
* 3, Dql:data query Language (data query Language)
Keywords: SELECT
Query the information for all students in the table.
Mysql>select Id,name,chinese,english,math from student;
or select * from student;
Check the names of all the students in the table and the corresponding English scores.
Mysql>select name,english from student;
Filter duplicate data in a table.
Mysql>select DISTINCT 中文版 from student;

Add 10 extra-long points to all students ' language scores.
Mysql>select id,name,chinese+10 from student;
Count each student's total score.
Mysql>select Id,name,chinese+english+math from student;
Use aliases to represent student scores. (as can be omitted)
Mysql>select Id,name,chinese+english+math as total score from student;
Mysql>select Id,name,chinese+english+math from student;

Search for student's grades with the name Wu
Mysql>select Id,name,chinese,english,math from student WHERE name= ' Harry ';
Query students with English scores greater than 90 points
Mysql>select name,english from student WHERE english>90;
Find all students with a total score greater than 200
Mysql>select Name,chinese+english+math as total score from student WHERE (Chinese+english+math) >200;
------------------------------------------------------------------
(elective) 4, Dcl:data control Language (Data Control Language)
Grant, etc.
Four, multi-table relationship and design
V. constraints

MySQL Basics 1