---恢复内容开始---
mySQL安装及基本操作命令(一) mysql 端口:3306; 连接数据库: mysql -hlocalhost -uroot -p 创建数据库: create database school; 查看表 : show databases; mysql字段类型 数字类型 //*表示常用类型 *int 10位 //一般时间转换成时间戳用int(10)表示 *smallint 6位 *tinyint 3位 mediumint bigint double *float (小数类型)
字符串类型 char 固定字符串长度 (效率高,一般用于固定字符串长度) varchar 可变字符串长度 text 创建表单(数据库) SCHOOL students Id int(10) PK auto_inc UserName varchar(8) Email varchar(30) Number char(9) //分析过程 Code char(18) Teil char(11) Regtime int(10) 命令 CREATE TABLE `gyjj_users`( `Id` INT(10) NOT NULL AUTO_INCREMENT, `UserName` VARCHAR(6) NOT NULL COMMENT "学生姓名", `Email` VARCHAR(30) NOT NULL COMMENT "学生邮箱", `Number` CHAR(9) NOT NULL COMMENT "学号", `Code` CHAR(18) NOT NULL COMMENT "身份证", `Teil` CHAR(11) NOT NULL COMMENT "手机", `RegTime` INT(10) NOT NULL COMMENT "注册时间", PRIMARY KEY (`Id`)
);
创建另一张表单: CREATE TABLE `gyjj_users_extra`( `Id` INT(10) NOT NULL AUTO_INCREMENT, `UId` INT(10) NOT NULL COMMENT "学生PK", `QQ` VARCHAR(10) NOT NULL COMMENT "QQ号", `Score` FLOAT(6,2) NOT NULL COMMENT "学分",
PRIMARY KEY (`Id`)
); 查找RegTime ,Code SELECT *FROM gyjj_users WHERE RegTime>1498363200 and Code LIKE "34001%";
查找Teil为138开头或者158开头 SELECT * FROM gyjj_users WHERE Teil LIKE "138%" or Teil LIKE "158%"; 查找指定位子: SELECT * FROM gyjj_users LIMIT 1,2; //从第几位开始,取几位
SELECT COUNT(*) FROM gyjj_users; //输出数据个数
SELECT * FROM gyjj_users ORDER BY Id DESC;倒序排列
SELECT * FROM gyjj_users ORDER BY Id ASC;正序排列
查找男女各多少 SELECT COUNT(*),Sex FROM gyjj_users GROUP BY Sex; // GROUP BY
SELECT COUNT(*),City,Sex FROM gyjj_users GROUP BY City,Sex;
alter table gyjj_users add Sex varchar(3) NOT NULL;
//双表联查 SELECT a.*,b.score FROM gyjj_users AS a LEFT JOIN gyjj_users_extra AS b ON a.Id = b.UId WHERE a.UserName="pan"; 查看表名:SHOW COLUMNS FROM gyjj_users;
插入语句:INSERT INTO gyjj_users SET UserName="zh",Email="yimiao@qq.com",Number="201532122",Code="340011111111111111",Teil="13830226929",RegTime=1498370982;
查询语句: SELECT * FROM gyjj_users;
更新语句:UPDATE gyjj_users SET Teil="11111111111" WHERE Id=1;
删除语句:DELETE FROM gyjj_users WHERE Id=1;
|
---恢复内容结束---
mySQL安装及基本操作命令(一) mysql 端口:3306; 连接数据库: mysql -hlocalhost -uroot -p 创建数据库: create database school; 查看表 : show databases; mysql字段类型 数字类型 //*表示常用类型 *int 10位 //一般时间转换成时间戳用int(10)表示 *smallint 6位 *tinyint 3位 mediumint bigint double *float (小数类型)
字符串类型 char 固定字符串长度 (效率高,一般用于固定字符串长度) varchar 可变字符串长度 text 创建表单(数据库) SCHOOL students Id int(10) PK auto_inc UserName varchar(8) Email varchar(30) Number char(9) //分析过程 Code char(18) Teil char(11) Regtime int(10) 命令 CREATE TABLE `gyjj_users`( `Id` INT(10) NOT NULL AUTO_INCREMENT, `UserName` VARCHAR(6) NOT NULL COMMENT "学生姓名", `Email` VARCHAR(30) NOT NULL COMMENT "学生邮箱", `Number` CHAR(9) NOT NULL COMMENT "学号", `Code` CHAR(18) NOT NULL COMMENT "身份证", `Teil` CHAR(11) NOT NULL COMMENT "手机", `RegTime` INT(10) NOT NULL COMMENT "注册时间", PRIMARY KEY (`Id`)
);
创建另一张表单: CREATE TABLE `gyjj_users_extra`( `Id` INT(10) NOT NULL AUTO_INCREMENT, `UId` INT(10) NOT NULL COMMENT "学生PK", `QQ` VARCHAR(10) NOT NULL COMMENT "QQ号", `Score` FLOAT(6,2) NOT NULL COMMENT "学分",
PRIMARY KEY (`Id`)
); 查找RegTime ,Code SELECT *FROM gyjj_users WHERE RegTime>1498363200 and Code LIKE "34001%";
查找Teil为138开头或者158开头 SELECT * FROM gyjj_users WHERE Teil LIKE "138%" or Teil LIKE "158%"; 查找指定位子: SELECT * FROM gyjj_users LIMIT 1,2; //从第几位开始,取几位
SELECT COUNT(*) FROM gyjj_users; //输出数据个数
SELECT * FROM gyjj_users ORDER BY Id DESC;倒序排列
SELECT * FROM gyjj_users ORDER BY Id ASC;正序排列
查找男女各多少 SELECT COUNT(*),Sex FROM gyjj_users GROUP BY Sex; // GROUP BY
SELECT COUNT(*),City,Sex FROM gyjj_users GROUP BY City,Sex;
alter table gyjj_users add Sex varchar(3) NOT NULL;
//双表联查 SELECT a.*,b.score FROM gyjj_users AS a LEFT JOIN gyjj_users_extra AS b ON a.Id = b.UId WHERE a.UserName="pan"; 查看表名:SHOW COLUMNS FROM gyjj_users;
插入语句:INSERT INTO gyjj_users SET UserName="zh",Email="yimiao@qq.com",Number="201532122",Code="340011111111111111",Teil="13830226929",RegTime=1498370982;
查询语句: SELECT * FROM gyjj_users;
更新语句:UPDATE gyjj_users SET Teil="11111111111" WHERE Id=1;
删除语句:DELETE FROM gyjj_users WHERE Id=1;
修改表字符属性:alter table `tablename` convert to character set utf8;
|