MySQL Grammar Encyclopedia

#----The command line to connect to MySQL---------

#启动mysql服务器
net start MySQL

#关闭
net stop MySQL

#进入
Mysql-h host Address-u user name-P user Password

#退出
Exit

#---------------------------
#----MySQL User management---------

#修改密码: First, under DOS, enter the bin directory of the MySQL installation path, and then type the following command:
mysqladmin-uroot-p123 password 456;

#增加用户
#格式: Grant permissions on the database. * To username @ Login Host Identified by ' password '
/*
For example, Add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:  
grant select,insert,update,delete on *. * To [email protected]  identified by "Password1";  
If you want the user to be able to log on to MySQL on any machine, Then change the localhost to "%".  
If you don't want the User1 to have a password, you can make a command to remove the password.  
grant select,insert,update,delete on mydb.* to [email protected]  identified by" ";  
*/

Grant all privileges the wpj1105.* to [e-mail protected] identified by ' 123 '; #all Privileges All Permissions

#----------------------------
#-----MySQL Database operation base-----

#显示数据库
show databases;

#判断是否存在数据库wpj1105, delete some words first
Drop database if exists wpj1105;

#创建数据库
Create Database wpj1105;

#删除数据库
Drop database wpj1105;

#使用该数据库
Use wpj1105;

#显示数据库中的表
Show tables;

#先判断表是否存在, there is a first delete
drop table if exists student;

#创建表
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (50),
Sex varchar (20),
Date varchar (50),
Content varchar (100)
) default Charset=utf8;

#删除表
drop table student;

#查看表的结构
describe student;  # can be abbreviated as DESC student;

#插入数据
insert into student values (NULL, ' AA ', ' Male ', ' 1988-10-2 ', ' ... ');
insert into student values (null, ' BB ', ' female ', ' 1889-03-6 ', ' ... ');
insert into student values (null, ' cc ', ' Male ', ' 1889-08-8 ', ' ... ');
insert into student values (null, ' DD ', ' female ', ' 1889-12-8 ', ' ... ');
insert into student values (NULL, ' ee ', ' female ', ' 1889-09-6 ', ' ... ');
insert into student values (NULL, ' FF ', ' null ', ' 1889-09-6 ', ' ... ');
#查询表中的数据
select * from student;
select id,name from student;

#修改某一条数据
Update student set sex= ' man ' where id=4;

#删除数据
Delete from student where id=5;

# and and
SELECT * FROM student where date> ' 1988-1-2 ' and date< ' 1988-12-1 ';

# or OR
SELECT * FROM student where date< ' 1988-11-2 ' or date> ' 1988-12-1 ';

#between
SELECT * from student where date between ' 1988-1-2 ' and ' 1988-12-1 ';

#in query to develop data within a collection
SELECT * from student where ID in (1,3,5);

#排序 ASC Ascending desc Descending
SELECT * FROM student order by ID ASC;

#分组查询 #聚合函数
Select Max (ID), name,sex from student group by sex;

Select min (date) from student;

Select AVG (ID) as ' averaging ' from student;

Select COUNT (*) from student; #统计表中总数

Select count (Sex) from student; #统计表中性别总数 If there is a piece of data in which sex is empty, it is not counted ~

Select SUM (ID) from student;

#查询第i条以后到第j条的数据 (excluding article i)
SELECT * FROM student limit 2, 5; #显示3-5 data

#巩固练习
CREATE TABLE C (
ID int primary KEY auto_increment,
Name varchar (ten) is not NULL,
Sex varchar (a), #DEFAULT ' man ',
Age int unsigned, #不能为负值 (default is 0 if negative)
Sno Int unique #不可重复
);

drop table C;
Desc C;

Insert into C (ID,NAME,SEX,AGE,SNO) VALUES (null, ' Ko tao ', ' Male ', 68, 1);
Insert into C (ID,NAME,SEX,AGE,SNO) VALUES (null, ' AA ', ' Male ', 68,2);
Insert into C (ID,NAME,SEX,AGE,SNO) VALUES (null, ' plain ', ' male ', 35, 3);
...

SELECT * from C;

#修改数据
Update C set age=66 where id=2;
Update c set name= ' Flower ', age=21,sex= ' woman ' where id=2
Delete from c where age=21;

#常用查询语句
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-60  #between
select * from C where AG E in (30,48,68,99);     #in Query the data within the specified collection
select * from C Order BY age desc;      #order by (ASC ascending des Descending)

#分组查询
select Name,max (age) from C Group by sex;  #按性别分组查年龄最大值
#聚合函数
Select min (age) from C;
select avg (age) as ' average ages ' from C;
select count (*) from c;  #统计表中数据总数
Select SUM (age) from C;

#修改表的名字
#格式: ALTER TABLE Tbl_name rename To new_name
alter table C Rename to A;
&NBSP;
#表结构修改
create table test
(
id int not NULL Auto_increment primary key, #设定主键
name varchar () NOT NULL default ' NoName ', #设定默认值
department_id int not NULL,
position_id int not NULL,
unique (department_id,position_id) #设定唯一值

#修改表的名字
#格式: ALTER TABLE tbl_name Rename to New_name
ALTER TABLE test Rename to Test_rename;

#向表中增加一个字段 (column)
#格式: ALTER TABLE tablename add columnname type;/alter table tablename Add (columnname type);
ALTER TABLE test add columnname varchar (20);

#修改表中某个字段的名字
ALTER TABLE tablename change columnname newcolumnname type; #修改一个表的字段名
ALTER TABLE test change name uname varchar (50);

SELECT * from Test;

#表position Add column Test
ALTER TABLE position add (test char);
#表position Modify Column Test
ALTER TABLE position modify Test char (a) not null;
#表position Modify the column test default value
ALTER TABLE position ALTER TEST set default ' system ';
#表position Remove the test default value
ALTER TABLE position ALTER test drop default;
#表position Remove column test
ALTER TABLE position drop column test;
#表depart_pos Delete primary key
ALTER TABLE Depart_pos drop PRIMARY key;
#表depart_pos Increase the primary key
ALTER TABLE Depart_pos add primary key Pk_depart_pos
(department_id,position_id);

#用文本方式将数据装入数据库表中 (e.g. D:/mysql.txt)
Load data local infile "d:/mysql.txt" into table MYTABLE;

#导入. sql file commands (for example, D:/mysql.sql)
SOURCE D:/mysql.sql; #或者/. D:/mysql.sql;

MySQL Grammar Encyclopedia