MySQL Foundation One (advanced)

MySQL Foundation one1. Understanding MySQL and creating usersabout MySQL

MySQL is one of the most popular relational database management systems, developed by the Swedish Mysqlab Company and currently owned by Oracle Corporation. MySQL is an associated database management system that keeps data in separate tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.

(open source, free)

# relational database: a database that uses relational models to organize data

# Relationship: A two-dimensional table, each relationship has a relationship name, which is the table name, interrelated

# Model: Rows and columns (two-dimensional), referring to field and field information

02 Enter MySQL:

MySQL--uroot-qwe123

03 Create User:

# Create user

' zcm '@'%'qwe123';

# giving permissions to users

' zcm '@'%';

# make changes take effect immediately

Flush privileges;

# Exit

\q

mysql–uzcm–pqwe123;   # Enter new user

04 View Current User:

Select User ();

05 View Current database:

Select database ();

2. Database creation/deletionMySQL CREATE database:

To create the database syntax:

CREATE DATABASE [IF not EXISTS] db_name;

Attention:

Repeat creation will error, so you can add Ifnot exists

The SQL statement must end with a semicolon

02 See which databases are available:

SHOW DATABASES;

03 Delete Database syntax:

DROP DATABASE [IF EXISTS] dbname;

If you do not know the database, whether it exists, remember to add if exists

04 See in which database:

SELECT DATABASE ();

Attention:

The database was created successfully and is not directly used

05 Enter database syntax:

Use DBNAME;

3. Table creation/deletion01 CREATE TABLE syntax:

if  not exists test (?     ID int,?     Name varchar (a)?     );

Data type:

int integer type

VARCHAR string

02 See which table is inside:

Show tables;

03 View database structure Syntax:

DESCRIBE test;? Show CREATE TABLE Test\g

04 Delete Table syntax:

drop table test;

4, the data in the single table of the increase, delete, change, checkInsert input Data

Insert Syntax One:

INSERT into Test (id,name) value (1,' Zhang Chunming ');? INSERT into Test (id,name) VALUES (2,'lucky'), (3,' haha ') );

Insert Syntax Two:

INSERT INTO Test set id=4,name=' hehe ';

Select query Data

Select syntax One:

 from Test;

Select Syntax Two:

 from test where ID >=2;

Update Data

' Don't move ' where id = 3;

Note: Be sure to write the Where condition

Delete Data

 from Test where id = 1;

Note: Be sure to write the Where condition, otherwise all data will be deleted

5. Supplement: MySQL Data type

4 Common types: integer floating-point date type character type

CREATE TABLE TB2 (ID INT, name VARCHAR (20),#specify a length of up to 65,535 characters. Variable lengthSex CHAR (4),#specify a length of up to 255 characters. Fixed lengthPrice DOUBLE (4,2),#double-precision floating-point, m total number, D- decimalDetail text,#variable length, up to 65,535 charactersDates DATETIME,#Date Time type Yyyy-mm-dd HH:MM:SSPing ENUM ('Praise','#枚举, select from the value given);? INSERT into TB2 value (1,'Trousers','male', 20.0,'This pair of trousers is super good!!! ', now (),'Praise');

MySQL Foundation One (advanced)