MySQL Basic Grammar Summary

#创建表
CREATE DATABASE mytest
Use MyTest
CREATE TABLE UserInfo (
UserId INT PRIMARY KEY auto_increment not NULL, #auto_increment to autogrow
Usersex INT (4) DEFAULT ' 0 ', #性别默认为0
Userages DOUBLE (10,2) not NULL, #工资为double类型
UserName VARCHAR () not NULL,
Userpwd VARCHAR () not NULL
);




#向表中插入数据
INSERT into UserInfo VALUES (' ', 0,2000, ' Zhang San ', ' 123456 '), (', 0,4200, ' John Doe ', ' 123456 '), (', 1,3000, ' Harry ', ' 123456 ')


#查询表中的数据
SELECT * from UserInfo;


#删除表
DROP TABLE UserInfo;

#查询表名
DESC UserInfo;


#查看表 userInfo top 2 rows of data
SELECT * from UserInfo ORDER by userId LIMIT 0, 2;


#查看表 the first 2 rows of data in UserInfo (same as above)
SELECT * from UserInfo LIMIT 0, 2;


#删除表 a record in userinfo with number 1
DELETE from UserInfo WHERE userid=1;


#修改表 a record in userinfo with number 1
UPDATE userInfo SET userwages=4500 WHERE userid=2;


#在表userInfo中添加一个字段userAge, type int (100)
ALTER TABLE userInfo ADD userage INT (100);


#将表userInfo名字更改为stuClass
RENAME TABLE userInfo to Stuinfo;


#修改表userInfo列名
ALTER TABLE userInfo Change usersex usersexs INT;


#修改表userInfo列名的类型
ALTER TABLE userInfo MODIFY usersex INT;


#删除表userInfo中的列
ALTER TABLE userInfo DROP userwages;


#文章前面加入4个空格
Update article Set Content=concat (", content);


#查询当前使用的数据库
SELECT DATABASE ();

#当前数据库包含的表信息
SHOW TABLES;

#获取当前时间
SELECT now ();

MySQL Basic Grammar Summary