SQL basics (1), SQL BASICS (

SQL basics (1), SQL BASICS (

I. mysql management commands

/Applications/XAMPP/xamppfiles/bin/mysql-uroot

1. display all databases

Show databases;

2. Enter the mysql database

USE mysql;

3. display the currently displayed Library

Select database ();

4. display the names of all tables in the current database

Show tables;

5. query all records of the SQL table name

SELECT * FROM SQL;

6. log out of the mysql server

Exit;

# Query the current MySQL Encoding

# Method of importing SQL files source d:/xx. SQL

 

II,

SQL statement category
1: DDL Data Define Language
Define database features and table structure-operation Columns

CREATE/DROP/ALTER/TRUNCATE

Create database crm;

Create database crm1 character set UTF8;

Create table student (

Id int primary key AUTO_INCREMENT,

Name VARCHAR (25 ),

Score DOUBLE (10, 2 ),

Birthday DATE

);

Drop database crm1;

Drop database student;

Alter table student ADD sex VARCHAR;

Alter table student DROP birthday;

Alter table student MODIFY score INT;

 

2: DML Data Manipulate Language
Operation Table Record row-operation row
INSERT/DELETE/UPDATE

 

Insert into student VALUES (null, 'cluster', 99, '2017-8-14 ');

Insert into student (partial column name) VALUES (...);

Delete from student WHERE score = 99;

UPDATE student SET score = score/2 WHERE name = 'cluster ';

3: DQL Data Query Language ***
SELECT

 

 

SELECT * FROM student order by score; // ascending

SELECT * FROM student order by score DESC; // descending ORDER

4: DCL Data Control Language
Control Database User Permissions
GRANT/REVOKE

Iii. SQL Functions

MAX ();

MIN ();

AVG ();

COUNT ();

SUM ();