MySQL常用命令
1、创建/删除数据库 CREATE/DROP DATABASE DATABASENAME; 2、创建数据表 CREATE TABLE table_name( col_name1, col_type [not null],#不允许为空则not null ,不写则默认可以为空,[]代表可写可不写 col_name2, col_type [not null], col_name3, col_type [not null] ) 例: create table t2( r1 int not null, r2 varchar(5) not null); 3、切换数据表目录 use tablename; 4、约束 约束:希望插入数据时做一个检查,比如性别只有男女,其他值无法加入 约束即是数据的检察官,当 出现不符合标准的数据时 ,就不会插入数据库 主键约束:保证实体完整性,主键不能重复,不能为空 如:PRIMARY KEY CREATE TABLE USERS( USER_QQ VARCHAR(20) NOT NULL PRIMARY KEY, USER_NAME VARCHAR(50) NOT NULL, USER_SEX CHAR(2) NOT NULL, ) 外键约束:一个表上的数据来源于另一个表 保证引用完整性 如:表示创建SCORE表,表中的USER_QQ来源于USER表中的USER_QQ列,如果没有则插入失败 CREATE TABLE SCORES( USER_QQ VARCHAR(20) NOT NULL REFERENCES USERS(USER_QQ), GNO INT NOT NULL, SCORE INT NOT NULL ) 检查约束:保证域完整性,即列上的某个值符合某种格式或者某种范围 如:要求游戏编号大于0 CREATEE TABLE GAMES( GNO INT NOT NULL CHECK(GNO>0), GNAME VARCHAR(50) NOT NULL, GTYPE VARCHAR(20) NOT NULL ) 默认约束:保证域完整性,比如玩家性别默认为男 CREATE TABLE USERS( USER_SEX VARCHAR(2) NOT NULL DEFAULT '男' ) 自增列:保证实体完整 CREATE TABLE GAMES( GNO INT NOT NULL AUTO_INCREMENT, ) 5、查看表的基本结构 DESCRIBE/DESC TABLE_NAME Field:字段名 Type:字段类型 NULL:是否可以为空 Key:是否编制索引 Default:默认值 Extra:附加信息,如自增列 查看表的详细结构,可以查到创建的所有语句 SHOW CREATE_TABLE TABLE_NAME 加\G可以格式化结果 6、修改数据表 修改表名 ALTER TABLE OLD_NAME RENAME TO NEW_NAME 修改字段名 ALTER TABLE TABLE_NAME CHANGE OLD_NAME NEW_NAME NEW_TYPE 如: ALTER TABLE GAMES CHANGE GNO GAME_ID VARCHAR(20) 修改字段数据类型 ALTER TABLE TABLE_NAME MODIFY COL_NAME NEW_TYPE 如: ALTER TABLE GAMES MODIFY GNO VARCHAR(20) 添加字段 ALTER TABLE TABLE_NAME ADD NEW_COL_NAME NEW_TYPE 删除字段 ALTER TABLE TABLE_NAME DROP COL_NAME 增补主键约束 ALTER TABLE TABLE_NAME ADD CONSTRAINT COL_NAME PRIMARY KEY (COL_NAME) 如:注意约束名,主键 约束pk,外键则为fk ALTER TABLE USERS ADD CONSTRINT PK_USERS_USERQQ PRIMARY KEY(USERQQ) 添加外键约束 ALTER TABLE F_TABLE ADD CONSTRAINT CON_NAME FOREIGN KEY(F_COL)REFERENCES M_TABLE(M_COL) 如: ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_GAMES FOREIGN KEY(GNO) REFERENCES GAMES(GNO) 添加检查约束 ALTER TABLE TABLE_NAME ADD CONSTRAINT CON_NAME CHECK(EXP) 如: ALTER TABLE GAMES ADD CONSTRAINT CK_GAMES_GNO CHECK(GN0>0) 添加默认值 ALTER TABLE TABLE_NAME ALTER COL_NAME SET DEFAULT VALUE 如: ALTER TABLE USERS ALTER USER_SEX SET DEFAULT '男' 添加 自增列 ALTER TABLE TABLE_NAME MODIFY COLUMN COL_NAME ... AUTO_INCREMENT 如: ALTERR TABLE 'GAMES' MODIFY COLUMN 'GNO' INT NOT NULL AUTO_INCREMENT PRIMARY KEY 7、插入数据 为所有列插入值,列值同数,列值同序 INSERT [INTO] TABLE_NAME VALUES(V1,V2,V3...) 如: INSERT INTO USERS VALUES ('2011-01-22','周天','男','1985-09-08','13800110022') INSERT INTO USERS VALUES ('2011-01-22','周天',DEFAULT,'1985-09-08','13800110022') 为特定列插入值,指定顺序,列值对应 INSERT [INTO] TABLE_NAME(COL1, COL2...) VALUES(V1,V2...) 如: INSERT INTO USERS (USER_QQ, USER_NAME, USER_MOBILE) VALUES('1545','DDAFG','1585435535654') 一次性插入多条记录 INSERT [INTO] TABLE_NAME[(COL1,COL2...COLN)] VALURS(V11,V12...V1N),(V21,V22...V2N),(V31,V32...V3N)... 如: INSERT INTO USERS (USER_QQ, USER_NAME, USER_BIRTHDAY, USER_MOBILE) VALUES ('1545','DDAFG','1938-04-25','1585435535654') ('1545','DDAFG','1983-04-25','1585435535654') 修改数据 修改全部数据 UPDATE TABLE_NAME SET{COL_NAME = EXPRESSION}[,...N] 如: UPDATE USERS SET USER_SEX = '男' UPDATE SCORES SET SCORE = SCORE + 100 8、修改特定数据 UPDATE TABLE_NAME SET{COL_NAME = EXPRESSION}[,...N] WHERE CONDITION_EXPRESSION 如: UPDATE USERS SET USER_SEX = '女' WHERE USER_QQ = '12315' 9、删除数据 使用DELETE命令删除数据 DELETE [FROM] TABLE_NAME [WHERE CONDITION_EXPRESSION] 如: DELETE FROM USERS WHERE USER_SEX = '女' 使用TRUNCATE TABLE删除表中的全部数据 TRUNCATE TABLE TABLE_NAME TRUNCATE TABLE SCORES 10、查询 SELECT COL1,COL2,..COLN FROM TABLE1,TABLE2...TABLEN [WHERE CONDITION] #查询条件 [GROUP BY GROUP_BY_LIST] #把查询的结果进行分组 [HAVING CONDITIONS] # 是统计结果作为查询条件 [ORDER BY ORDER_LIST[ASC|DESC]] #统计结果进行排序 查询表的全部行和列 SELECT * FROM TABLE 查询表的部分列 SELECT USER_QQ, USER_NAME FROM USERS 别名的使用 SELECT USER-QQ AS '玩家QQ',USER_NAME AS '玩家姓名' FROM USERS SELECT USER-QQ '玩家QQ',USER_NAME '玩家姓名' FROM USERS #去掉as DISTINCT查询结果消除重复行 SELECT DISTINCT USER_QQ FROM SCORES LIMIT指定结果集中数据的显示范围 SELECT * FROM USERS LIMIT 2,3 #查询第几条-共显示几条数据 查询 普通条件查询 如:查询qq号为12301的玩家信息 SELECT * FROM USERS WHERE USER_QQ = '12301' 如:查询分数也大于2500分的数据 SELECT * FROM SCORES WHERE SCORE > 2500 如查询游戏编号为1且分数大于4000的分数信息 SELECT * FROM SCORES WHERE GNO= 1 AND SCORE> 4000 模糊查询 between and 如:查询分数在2500到3000的分数, 包括边界 SELECT * FROM SCORES WHERE SCORE >= 2500 AND SCORE <= 3000 SELECT * FROM SCORES WHERE SCORE BETWEEN 2500 AND 3000 不在2500与3700之间的 SELECT * FROM SCORE WHERE SCORE NOT BETWEEN 2500 AND 3700 通配符: '_' 一个字符 % 任意长度 [] 指定范围内 [^]不在括号中 如查询所有姓王的玩家,注意要用like SELECT * FROM USERS WHERE USER_NAME LIKE '王%' 查询空值,主要要用 IS NULL , IS NOT NULL SELECT * FROM USERS WHERE USER_BIRTHDAY IS NULL 对查询结果排序 对单列指定列进行排序,即单列排序,排序依据、排序方式 如:查询分数表中编号为1的所有分数信息,并按照分数升序进行排序 SELECT * FROM SCORES WHERE GNO=1 ORDER BY SCORE ASC/DESC 对多列排序:排序依据,排序方式、优先级,注意条件之间的, 如:查询分数表所有信息,并按照游戏编号的升序和分数的降序进行排序 SELECT * FROM SCORES ORDER BY GNO ASC, SCORE DESC 当ORDER BY 后面出现两个条件时,前面的优先级最高 常用的聚合函数 sum() avg() min() max() count() etc:查询玩家表中一共有多少名玩家信息 SELECT COUNT(NAME) FROM NAMES SELECT COUNT(*) FROM NAME 除非行上所有的列都为空 etc:查询qq号是12301的玩家游戏的总分数 SELECT SUM(SCORE) FORM SCORE WHERE USER_QQ = '12301' etc:查询QQ号是12302的玩家的平均分数 SELECT AVG(SCORE) FROM SCORES WHERE USER_QQ = '12302' etc:查询qq号是12302的玩家的总分、平均分、和最高分 SELECT SUM(SCORE) AS '总分', AVG(SCORE) AS '平均分', MAX(SCORE) AS '最高分' FROM SCORES WHERE USER_QQ = '12302' 结果集内分组, GROUP BY etc:查询每个玩家的总分数、平均分数、最高分数 SELECT USERS, SUM(SCORE) AS '总分数', AVG(SCORE) AS '', MAX(SCORE) AS '' FROM SCORES GROUP BY USERS 筛选分组结果 在使用GROUP BY 字句时,可用HAVING子句为分组统计进一步设置统计条件, HAVING子句是以聚合函数的统计结果为筛选条件。HAVING写在GROUP By之后 查询平均分数大于4000的玩家QQ号 总分数、平均分数 SELECT USER_QQ, SUM(SCORE) AS '总分数', AVG(SCORE) AS '平均分数' FROM SCORES GROUP BY USER_QQ HAVING AVG(SCORE) > 4000 统计结果也可以作为排序依据 查询所有用户的平均分数和总分数并按平均分数倒序排列 SELECT USER_QQ, AVG(SCORE) AS '平均分数', SUM(SCORE) AS '总分数' FROM SCORES GROUP BY USER_QQ ORDER BY AVG(SCORE) DESC SELECT FROM WHERE GBOUP BY 聚合函数 HAVING ORDER BY 多表连接查询 分别从三张表中查询分数信息、显示玩家昵称、游戏名称和分数 SELECT USER_NAME AS '昵称', GNAME AS '游戏名称', SCORE AS '分数' FROM USERS, GAMES, SCORES WHERE USERS.USER_QQ = SCORE.USER_QQ AND GAMES.GNO = SCORES.GNO FROM 后面直接出现多个表名,这种方式即属于内联接,是隐式内联接 显示内联接格式: 速度更快 SELECT COL_LIST FROM TABLE1 [INNER] JOIN TABLE2 ON TABLE1.COL=TABLE2.COL SELECT USER_NAME AS '', GNAME AS '', SCORE AS '' FROM SCORES INNER JOIN GAMES ON GAMES.GNO=SCORES.GNO INNER JOIN USERS ON SCORES.USER_QQ = USERS.USER_QQ SELECT USER_NAME '昵称', SUM(SCORE) AS '', AVG(SCORE) AS '' FROM USERS U INNER JOIN SCORES S ON S.USERS_QQ = U.USERS_QQ GROUP BY U.USERS_QQ, USER_NAME 外连接:做链接的两个表地位不同,有一张表是基础表,基础表数据必须出现,没有null不齐 左外连接时左表是基础表,右外连接时右表是基础表,先出现的表为左 SELECT COL_LIST FORM TABLE1 LEFT/RIGHT [OUTER] JOIN TABLE2 ON TABLE1.COL=TABLE2.COL 比如要求查询所有玩家关于5号游戏的分数信息,基础表是玩家表,有的玩家没有玩游戏也要出现 SELECT USER_NAME AS '', GNO AS '', SCORES AS '' FROM USERS U LEFT JOIN SCORES S ON U.USER_QQ=S.USERS_QQ AND S.GNO = 5 子查询IN, 子查询一般都不写成select * from SELECT * FROM 1111 WHERE NUM IN (SELECT NUM FROM 2222 WHERE NAME = '') EXISTS关键字,如果后面的成立,则执行外围的语句 SELECT * FROM SCORES WHERE EXISTS (SELECT * FROM USERS WHERE USERS_NAME = 'SUNWUKONG') 联合查询UNION [ALL]将多个结果纵向链接为一体,有ALL关键字可以显示全部数据(即使重复的数据也显示出来) SELECT USER_NAME FROM USERS UNION [ALL] SELECT GAME FROM GAMES SELECT + 字符串 表示列名 查询qq号是12301的玩家所有分数并计算总分、平均分,显示到同一结果集中 SELECT USER_QQ, GNO, SCORE FROM SCORES WHERE USER_QQ = '12301' UNION ALL SELECT '总分' ' ', SUM(SCORE) FROM SCORES WHERE USER_QQ = '12301' UNION ALL SELECT '平均分',' ', AVG(SCORE) FROM SCORES WHERE USER_QQ = '12301'