MySQL database basic operations (i)

1. Prerequisites for using MySQL database

A. Installing the MySQL server

B. Installing the MySQL Client

C. "Client" Connection "server Side"

D. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)

2. Connect database commands

Mysql-u root-p

3. Common commands

Show tables;                   # View all tables in the database

SELECT * from table name;             # View all the contents of the table

CREATE TABLE table name (  whether the column name type  can be empty,    whether the column name  type  can be empty) Engine=innodb DEFAULT Charset=utf8  #创建表

For example

CREATE TABLE ' tab1 ' (  ' nid ' Int (one) not null auto_increment,                   # NOT NULL means cannot be null, auto_increment indicates self-increment  ' name ' varchar (255) Default Zhangyanlin,                 # Default means  ' email ' varchar (255),  PRIMARY KEY (' nid ')                                      # Set the NID column as the primary key) Engine=innodb DEFAULT Charset=utf8;

drop table name #删除表

Delete from table name TRUNCATE TABLE table name #删除表内容

Add column:   ALTER TABLE table name add column name type Delete column:   ALTER TABLE table name drop Column name Modify column:          ALTER TABLE table name modify column name type;  --type ALTER TABLE name change original column name          new column name type;--column name, type  add primary key:          ALTER TABLE table name add primary key (column name); Delete PRIMARY key:          alter TABL e table Name drop primary key;          ALTER TABLE name  modify  column name int, drop primary key;  Add foreign key: ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop F  Oreign key FOREIGN Key name  modifies default value: ALTER TABLE TESTALTER_TBL alter I SET default 1000; Delete defaults: ALTER TABLE TESTALTER_TBL alter I drop DEFAULT;

4. Adding and removing changes and checking operation

Increase

Insert into table (column name, column name ...) values (value, value,...) Insert into table (column name, column name ...) values (value, Value,...), (value, value, Value ...) Insert into table (column name, column name ...) select (column name, column name ...) from table
Cases:
    Insert into TAB1 (name,email) VALUES (' Zhangyanlin ', ' [email protected] ')

By deleting

Delete from table                                      # Remove table all data delete from table where id=1 and Name= ' Zhangyanlin '   # Delete id = 1 and name= ' Zhangyanlin ' row of data

Change

Update table Set name = ' Zhangyanlin ' where id>1

Check

SELECT * FROM Table SELECT * from table where ID > 1select nid,name,gender as GG from table where ID > 1

    SELECT * FROM table where ID > 1 and name! = ' Aylin ' and num = n;    SELECT * FROM table where ID between 5 and;    SELECT * FROM table where ID in (11,22,33)    select * FROM table where ID not in (11,22,33)    select * FROM table where ID in (sel ECT nid from table)

SELECT * FROM table where name like ' zhang% '  # Zhang starts all (multiple strings)    select * FROM table where name like ' Zhang_ '  # Zhang Open All (one character) of the head

    SELECT * from table limit 5;            -First 5 lines    select * from table limit 4,5;          -5 lines starting from line 4th    select * FROM table limit 5 offset 4    -5 lines starting from line 4th

SELECT * FROM table ORDER BY column ASC              -arranges from small to large from "column"    select * FROM table ORDER BY column Desc             -rank from large to small from "column"    select * FR OM table order BY column 1 desc, column 2 ASC    -arranged from large to small according to "column 1", if same, sort by column 2 from small to large

　　

Select Num from table GROUP by num
Select Num,nid from table GROUP by Num,nid
Select Num,nid from table where nid > Group by num,nid Order Nid desc
Select Num,nid,count (*), SUM (score), Max (score), Min (score) from table group by Num,nid
Select Num from table GROUP by NUM have max (ID) > 10

Special: Group by must precede the where, order by

MySQL database basic operations (i)