MySQL Introduction Basic Grammar 1

Posted by bruce080 on Wed, 22 May 2019 20:15:30 +0200

I. Basic Use of MySQL

1.1 Basic operations on the command line

     Mysql-u username-p carriage return
     Enter password: xxx
     Enter the database
     (All commands in mysql end with a semicolon and enter help; query all mysql commands)

1.2 MySQL directory structure

    bin: All executable files exe
    File of MySQL InstanceConfig.exe Server Configuration Wizard
    data: Documents for system database and demo database
    my.ini Configured File File

1.3 Relationships among database servers, databases and tables

**

Four Types of SQL Statements

2.1 DDL (Data Definition Language)
Data Definition Language for creating tables and libraries. For example: create/alter/drop
2.2 DML(Data Manipulation Language)
Data manipulation language, add, delete and modify data. For example: insert/update/delete
2.3 DQL (Data Query Language)
Data query language, data query operation. For example: select/show
2.4 DCL (Data Control Language)
Data Control Language, grant/revoke for User Management

2.5 Three annotations in MySQL:
1) - Single line comment
2)/* Multi-line comments*/
3) # One-line comment, not recommended, only works in mysql
**

MySQL Management Database

//3.1 View all databases
show databases;

//3.2 Creating a Database
create database db1;

// 3.2.1 Specifies the default character set as GBK
create database db2 default character set gbk;

//3.3 View database creation statements
show create database db1;

3.4 Proofreading Rules
3.4.1 What are proofreading rules
Character Set: An encoding table of all characters in a language, each of which corresponds to one encoding.
Proofreading rules: The same character set can have more than one proofreading rule, and different proofreading rules have different sorting methods.

The Role of Proofreading Rules
 utf8_general_ci ignores case to sort, such as: a B c D sort order
 utf8_bin uses binary encoding for sorting, such as: BDac

3.4.2 View Character Sets and Proofreading Rules:
View all character sets

Proofreading rules for displaying GBK character sets

3.4.3 Specifies that the default character set of the database is GBK and the proofreading rule gbk_chinese_ci

create database db3 default character set gbk collate gbk_chinese_ci;

3.5 Delete database

drop database db3;

3.6 Modify the default character set of the database and change the character set of DB 3 to utf8

alter database db3 default character set utf8;

IV. TABLE MANAGEMENT

4.1 Query all tables

use db1; -- Enter the database
 show tables; -- Displays all tables in the current database

4.2 create Table create

//Create a student table
create table student(
    id int,
    name varchar(20),
    birthday date
);

Write variable names before data types

4.3 Data types of columns in MySQL

4.4 View a table structure

show tables;

1. Return in sql format

show create table student;

2. Return in tabular format

desc student;

3. copy table
For example, create an employee table with the same structure as the students.

create table employee like student;

4. Delete table drop

1) Delete a table:
drop table table table name;
2) Delete multiple tables:
drop table table table 1, table 2;

5. Modify table alter
1. Add fields: add

//Example 1: The student table adds a remark field of type char(10)
alter table student add remark char(10);

//Example 2: Adding two fields at the same time, one named a int and the other named b int
alter table student add a int，add b int;

2. Modify fields: modify

//Example: Change the attribute of the remark field in the student table to varchar(100)
alter table student modify remark varchar(100);

3. Modify the field name: change

//Example: Change the comment field name in the student table to intro with the same type.
alter table student change remark intro varchar(200);

4. Delete fields: drop

//Delete fields a and b in the student table
alter table student drop a,drop b;

5. Modify the table name: rename

//Example: rename the student form college_student
alter table student rename college_student;

V. Data Management and Operation

5.1 Insert data
- 5.1.1 Insert all columns

//Example: Insert three records into the student table
-- Insert 3 records into the student table, and mysql Single quotation marks and double quotation marks can be used in Chinese strings. It is recommended to use single quotation marks. Dates also use single quotation marks
insert into student values(1, 'Guan Yu', '2000-11-11');
insert into student values(2, 'Zhang Fei', '2002-11-11');
insert into student values(3, 'Liu Bei', '1900-11-11');

-- One article SQL Statement inserts three records, only mysql It can be written like this.
insert into student values(4, 'Zhao Yun', '2000-11-11'),(5, 'Kong Ming', '2000-11-11'),(6, 'Lv Bu', '2000-11-11');

Be careful:

1) Data type matching with column data type
2) The length of a string must not exceed the maximum length defined in the column
3) Location matching with column location
4) Use single quotation marks for characters and date types

- 5.1.2 Insert Partial Columns

insert into student (id,name) values (7, 'Little Joe');

5.2 Modification of data

Syntax:

**
UPDATE: Table name
SET: Modified columns and values
WHERE: Modify qualified records and modify all columns if you don't write Where

-- Modify the table structure to add gender columns char(1)
alter table student add gender char(1);

select * from student;

-- To update student All the gender columns in the table are changed to males.
update student set gender='male';

-- Modify Xiao Qiao as a Female
update student set gender='female' where name='Little Joe';

-- Modify multiple columns at the same time
update student set name='White clouds', birthday = '2010-03-20', gender='female' where name='Zhao Yun';

5.3 Delete data
5.3.1 Delete all data in the table

delete from student;

5.3.2 Delete some row data

-- delete id 3 students
delete from student where id=3;

5.4 Query data (emphasis)

**
5.4.1 Query all columns

Example: Query all columns
select * from student；

5.4.2 Query specified columns

Example: Multiple columns separated by commas
 Query the name and gender columns
select name,gender from student;

5.4.3 Specify aliases when querying

Example: Assign an alias (AS) to a column when querying
 The result is Chinese characters, as with aliases.
select id as number, name as name, birthday as birthday, gender as sex from student;
The alias as as can be omitted
 select id number, name name, gender gender from student;

5.4.4 Consolidated Column Query
1. Requirements: Adding Mathematics, English Scores, and Adding Scores

alter table student add math int, add english int;

Requirements: Query the total scores of each student in mathematics and English

Merge query results (as can be omitted)
Select*, (math + english) as total score from student;

5.4.5 Removal of distinct data
Requirements: Query where students come from

-- Adding City columns
alter table student add city varchar(10);

select * from student;

-- Inquire about the cities where students come from
select distinct city from student;

5.5 Conditional Query
Where clause

- 5.5.2 comparison operator

-- Query students with math scores greater than 85
select * from student where math > 85;

-- Search for Gender Not Male
select * from student where gender <> 'male'
-- Not recommended
select * from student where gender != 'male'
-- Query Mathematics in80reach90Between
select * from student where math >=80 and math<=90;

-- Use between.. and，Baotou and Baotai
select * from student where math between 80 and 90;
-- query id It's 1.,2,5 Students
select * from student where id=1 or id=2 or id=5;

-- Use in query
select * from student where id in(1,2,5);
-- Look up all students with Bai surname.%Wildcards, which can match any character
select * from student where name like 'white%';

-- Query for names containing white characters
select * from student where name like '%white%';

-- Look up the students whose surname is Bai. They only have 2 words._ Match 1 character
select * from student where name like 'white_';
-- Insert a record
insert into student (id,name) values (7,'Zhang Fei');

-- Search for gender null Students
select * from student where gender is null;

- 5.5.3 blanking conditions

1) Judging whether it is an empty string: gender =''
2) Judging whether it is empty: gender is null
 3) The difference between null and empty string: null has no data, empty string has data, only empty string

Topics: Database MySQL SQL encoding

Programmer Think