Common sql grammar in MySQL

---------------------------

Start mysql server

net start mysql

Close

net stop mysql

Get into

Sign out

exit

status;

Displays various information about the current version of mysql.

#---------------------------

MySql User Management

Modify password: First enter the bin directory of mysql installation path under DOS, and then type the following command:

mysqladmin -uroot -p123 password 456;

Increase user

# Format: grant permission on database. * to username @logon host identified by'password'
/*
Add a user user1 password to password1, so that it can login on the local machine, and all databases have the right to query, insert, modify, delete. First connect mysql to root user, and then type the following command:

grant select,insert,update,delete on . to user1@localhost Identified by "password1";

If you want the user to be able to login mysql on any machine, change localhost to "%".
If you don't want user1 to have a password, you can type another command to remove the password.

grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
*/

grant all privileges on wpj1105.* to sunxiao@localhost identified by '123';

# All privileges have all privileges

#----------------------------

MySql database operation basis

show database

show databases;

Determine whether there is a database wpj1105, or delete it first

drop database if exists wpj1105;

Create a database

create database wpj1105;

Delete the database

drop database wpj1105;

Use this database

use wpj1105;

Display tables in the database

show tables;

First judge whether the table exists, then delete it.

drop table if exists person;

Create table

create table person(
id int auto_increment primary key,
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100)
)default charset=utf8;

Delete table

drop table person;

View the structure of the table

describe person; #It can be abbreviated as desc person.

insert data

insert into person values(null,'a','male','1998-10-2','...');
insert into person values(null,'b','female','1996-03-6','...');
insert into person values(null,'c','male','1996-08-8','...');
insert into person values(null,'d','female','1997-12-8','...');

Data in Query Table

select * from person;
select id,name from person;

Modify a piece of data

update person set sex='male' where id=4;

Delete data

delete from person where id=5;

select * from emp; #Notes

And and

select * from person where date>'1988-1-2' and date<'1988-12-1';

Or or

select * from person where date<'1988-11-2' or date>'1988-12-1';

between

select * from person where date between '1988-1-2' and '1988-12-1';

In queries for data in the enactment set

select * from person where id in (1,3,5);

Sort asc Ascending Descending desc ending

select * from person order by id asc;

Grouping Query # Aggregation Function

select max(id),name,sex from person group by sex;

select min(date) from person;

select avg(id) as 'Seeking average' from person;

select count(*) from person; #Total in the statistical table

select count(sex) from person; #If there is an empty sex in the statistics table, it will not be counted.~

select sum(id) from person;

Query data after article I to Article j (excluding article i)

select * from person limit 2,5; #Display 3-5 pieces of data

Consolidation exercises

create table c(
id int primary key auto_increment,
name varchar(10) not null,
sex varchar(50) , #DEFAULT `Male',
age int unsigned, #Can't be negative (default is 0 if negative)
sno int unique #Not duplicated
);

drop table c;
desc c;

insert into c (id,name,sex,age,sno) values (null,'Zhang San','male',26,1);
insert into c (id,name,sex,age,sno) values (null,'Funny','male',25,2);
insert into c (id,name,sex,age,sno) values (null,'Li Si','male',35,3);
...

select * from c;

Modifying data

update c set age=66 where id=2;
update c set name='tearful',age=21,sex='female' where id=2
delete from c where age=21;

Common Query Statements

select name,age ,id from c
select * from c where age>40 and age<60; #and
select * from c where age<40 or age<60; #or
select * from c where age between 40 and 60 #between
select * from c where age in (30,48,68,99); #In queries for data in a specified collection
select * from c order by age desc; #order by (asc ascending des descending)

Group query

select name,max(age) from c group by sex; #Maximum age by sex

Aggregate function

select min(age) from c;
select avg(age) as 'Average age ' from c;
select count(*) from c; #Total data in statistical tables
select sum(age) from c;

Modify the name of the table

#Format: alter table tbl_name rename to new_name
alter table c rename to a;

Table structure modification

create table test
(
id int not null auto_increment primary key, #Set primary key
name varchar(20) not null default 'NoName', #Set default values
department_id int not null,
position_id int not null,
unique (department_id,position_id) #Set unique values
);

Modify the name of the table

Format: alter table tbl_name rename to new_name

alter table test rename to test_rename;

Add a field (column) to the table

# Format: alter table table name add columnname type; / alter table table name add (columnname type);

alter table test add columnname varchar(20);

Modify the name of a field in the table

alter table tablename change columnname newcolumnname type; #Modify the field name of a table
alter table test change name uname varchar(50);

select * from test;

Table position adds column test

alter table position add(test char(10));

Table position modifies column test

alter table position modify test char(20) not null;

Table position modifies column test defaults

alter table position alter test set default 'system';

Table position removes test defaults

alter table position alter test drop default;

Table position removes column test

alter table position drop column test;

Table depart_pos deletes primary keys

alter table depart_pos drop primary key;

Table depart_pos Adding Primary Key

alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);

Textual loading of data into database tables (e.g. D:/mysql.txt)

load data local infile "D:/mysql.txt" into table MYTABLE;

Import. SQL file commands (such as D:/mysql.sql)

source d:/mysql.sql; #Or /. d:/mysql.sql;