# 查看所有的表SHOWTABLES;# 创建一个表CREATETABLE 表名(
字段名 字段类型 约束,...);CREATETABLE t_test(id INT, name VARCHAR(10));# 直接将查询结果导入或复制到新创建的表CREATETABLE 表名 查询语句;CREATETABLE t_test SELECT*FROM t_user;# 新创建的表与一个存在的表的数据结构类似CREATETABLE 新表名 LIKE 已存在表名;CREATETABLE t_test LIKE t_user;# 创建一个临时表# 临时表将在你连接MySQL期间存在。当断开连接时,MySQL将自动删除表并释放所用的空间。也可手动删除。CREATETEMPORARYTABLE 表名(
字段名 字段类型 约束,...);CREATETEMPORARYTABLE t_test(id INT, name VARCHAR(10));# 直接将查询结果导入或复制到新创建的临时表CREATETEMPORARYTABLE 表名 查询语句;CREATETEMPORARYTABLE t_test SELECT*FROM t_user;# 删除一个存在表DROPTABLEIFEXISTS 表名;DROPTABLEIFEXISTS t_test;# 更改存在表的名称ALTERTABLE 表名 RENAME 新表名;ALTERTABLE n RENAME m;RENAMETABLE 表名 TO 新表名;RENAMETABLE n TO m;# 查看表的结构(以下五条语句效果相同)DESC n;# 因为简单,所以建议使用DESCRIBE n;SHOWCOLUMNSIN n;SHOWCOLUMNSFROM n;EXPLAIN n;# 查看表的创建语句SHOWCREATETABLE 表名;SHOWCREATETABLE t_test;
表结构
# 添加字段ALTERTABLE 表名 ADD 字段名 字段属性;ALTERTABLE t_test ADD age VARCHAR(2);# 删除字段ALTERTABLE 表名 DROP 字段名;ALTERTABLE n DROP age;# 更改字段的属性ALTERTABLE 表名 MODIFY 字段名 字段属性;ALTERTABLE n MODIFY age VARCHAR(7);
表的数据
# 插入数据INSERTINTO 表名 VALUES(值1,值2,值3...);INSERTINTO t_user VALUES(1,'tom','23'),(2,'john','22');INSERTINTO 表名(属性1,属性2,属性3...)VALUES(值1,值2,值3...);INSERTINTO t_user(id,name,gender)values(1,'test',0);# 删除数据DELETEFROM 表名 WHERE 条件;DELETEFROM t_user WHERE id =1;# 更改数据UPDATE 表名 SET 属性名=属性值 WHERE 条件;UPDATE t_user SET name='张三'WHERE id=1;# 数据查找SELECT*FROM 表名 WHERE 条件;SELECT*FROM t_user WHERE id=1;# 数据排序# DESC 表示降序SELECT*FROM 表名 WHERE 条件 ORDERBY 字段名 [DESC];SELECT*FROM t_user ORDERBY id DESC;
# 创建视图CREATEVIEW 视图名[(字段名,字段名...)]AS 查询语句;CREATEVIEW v ASSELECT id, name FROM t_test;CREATEVIEW v(id, name)ASSELECT id, name FROM t_test;# 查看视图(与表操作类似)SELECT*FROM 视图名;SELECT*FROM v;DESC 视图名;DESC v;# 查看创建视图语句SHOWCREATEVIEW 视图名;SHOWCREATEVIEW v;# 更改视图CREATEORREPLACEVIEW 视图名 AS 查询语句;CREATEORREPLACEVIEW v ASSELECT name, age FROM t_test;ALTERVIEW 视图名 AS 查询语句;ALTERVIEW v ASSELECT name FROM t_user ;# 删除视图DROPVIEWIFEXISTS 视图名;DROPVIEWIFEXISTS v;
联接
# 内联接SELECT*FROM 表名2INNERJOIN 表名2ON 表名.字段名=表名2.字段名;SELECT*FROM m INNERJOIN n ON m.id = n.id;# 左外联接SELECT*FROM 表名1LEFTJOIN 表名2ON 表名.字段名=表名2.字段名;SELECT*FROM m LEFTJOIN n ON m.id = n.id;# 右外联接SELECT*FROM 表名1RIGHTJOIN 表名2ON 表名.字段名=表名2.字段名;SELECT*FROM m RIGHTJOIN n ON m.id = n.id;# 交叉联接SELECT*FROM 表名1CROSSJOIN 表名2;SELECT*FROM m CROSSJOIN n;# 标准写法SELECT*FROM m, n;# 类似全连接full join的联接用法SELECT id,name FROM m
UNIONSELECT id,name FROM n;
用户
# 增加用户CREATEUSER'test'@'localhost' IDENTIFIED BY 'test';
# 在用户表中插入用户信息,直接操作User表不推荐
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 刷新系统权限表,使授予权限生效
FLUSH PRIVILEGES ;
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';# 取消该用户的删除权限
存储过程
# 创建存储过程DELIMITER// # 无参数CREATEPROCEDURE getDates()BEGINSELECT*FROM test ;END//CREATEPROCEDURE getDates_2(IN id INT)# in参数BEGINSELECT*FROM test WHERE a = id;END//CREATEPROCEDURE getDates_3(OUT sum INT)# out参数BEGINSET sum =(SELECTcount(*)FROM test);END//CREATEPROCEDURE getDates_4(INOUT i INT)# inout参数BEGINSET i = i +1;END//DELIMITER;# 删除存储过程DROPPROCEDUREIFEXISTS getDates;# 修改存储过程的特性ALTERPROCEDURE getDates MODIFIESSQLDATA;# 修改存储过程语句(删除再重建)略# 查看存储过程SHOWPROCEDURESTATUSLIKE'getDates';# 状态SHOWCREATEPROCEDURE getDates_3;# 语句# 调用存储过程CALL getDates();CALL getDates_2(1);CALL getDates_3(@s);SELECT@s;SET@i=1;CALL getDates_4(@i);SELECT@i;# @i = 2
函数
# 聚合函数SELECTcount(id)AS total FROM n;# 总数SELECTsum(age)AS all_age FROM n;# 总和SELECTavg(age)AS all_age FROM n;# 平均值SELECTmax(age)AS all_age FROM n;# 最大值SELECTmin(age)AS all_age FROM n;# 最小值# 数学函数SELECT abs(-5);# 绝对值SELECT bin(15), oct(15), hex(15);# 二进制,八进制,十六进制SELECT pi();# 圆周率3.141593SELECT ceil(5.5);# 大于x的最小整数值6SELECT floor(5.5);# 小于x的最大整数值5SELECT greatest(3,1,4,1,5,9,2,6);# 返回集合中最大的值9SELECT least(3,1,4,1,5,9,2,6);# 返回集合中最小的值1SELECTmod(5,3);# 余数2SELECT rand();# 返回0到1内的随机值,每次不一样SELECT rand(5);# 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。SELECTround(1415.1415);# 四舍五入1415SELECTround(1415.1415,3);# 四舍五入三位数1415.142SELECTround(1415.1415,-1);# 四舍五入整数位数1420SELECTtruncate(1415.1415,3);# 截短为3位小数1415.141SELECTtruncate(1415.1415,-1);# 截短为-1位小数1410SELECT sign(-5);# 符号的值负数-1SELECT sign(5);# 符号的值正数1SELECT sqrt(9);# 平方根3SELECT sqrt(9);# 平方根3# 字符串函数SELECT concat('a','p','p','le');# 连接字符串-appleSELECT concat_ws(',','a','p','p','le');# 连接用','分割字符串-a,p,p,leSELECTinsert('chinese',3,2,'IN');# 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINeseSELECTleft('chinese',4);# 返回字符串'chinese'左边的4个字符-chinSELECTright('chinese',3);# 返回字符串'chinese'右边的3个字符-eseSELECT substring('chinese',3);# 返回字符串'chinese'第三个字符之后的子字符串-ineseSELECT substring('chinese',-3);# 返回字符串'chinese'倒数第三个字符之后的子字符串-eseSELECT substring('chinese',3,2);# 返回字符串'chinese'第三个字符之后的两个字符-inSELECT trim(' chinese ');# 切割字符串' chinese '两边的空字符-'chinese'SELECT ltrim(' chinese ');# 切割字符串' chinese '两边的空字符-'chinese 'SELECT rtrim(' chinese ');# 切割字符串' chinese '两边的空字符-' chinese'SELECTrepeat('boy',3);# 重复字符'boy'三次-'boyboyboy'SELECT reverse('chinese');# 反向排序-'esenihc'SELECT length('chinese');# 返回字符串的长度-7SELECT upper('chINese'), lower('chINese');# 大写小写 CHINESE chineseSELECTucase('chINese'),lcase('chINese');# 大写小写 CHINESE chineseSELECT position('i'IN'chinese');# 返回'i'在'chinese'的第一个位置-3SELECT position('e'IN'chinese');# 返回'i'在'chinese'的第一个位置-5SELECT strcmp('abc','abd');# 比较字符串,第一个参数小于第二个返回负数- -1SELECT strcmp('abc','abb');# 比较字符串,第一个参数大于第二个返回正数- 1# 时间函数SELECTcurrent_date,current_time,now();# 2018-01-13 12:33:43 2018-01-13 12:33:43SELECThour(current_time),minute(current_time),second(current_time);# 12 31 34SELECTyear(current_date),month(current_date), week(current_date);# 2018 1 1SELECT quarter(current_date);# 1SELECT monthname(current_date), dayname(current_date);# January SaturdaySELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);# 7 13 13# 控制流函数SELECTif(3>2,'t','f'),if(3<2,'t','f');# t fSELECT ifnull(NULL,'t'), ifnull(2,'t');# t 2SELECT isnull(1), isnull(1/0);# 0 1 是null返回1,不是null返回0SELECTnullif('a','a'),nullif('a','b');# null a 参数相同或成立返回null,不同或不成立则返回第一个参数SELECTCASE2WHEN1THEN'first'WHEN2THEN'second'WHEN3THEN'third'ELSE'other'END;# second# 系统信息函数SELECTdatabase();# 当前数据库名-testSELECT connection_id();# 当前用户id-306SELECTuser();# 当前用户-root@localhostSELECT version();# 当前mysql版本SELECT found_rows();# 返回上次查询的检索行数