常见MySQL数据库语句

##############Author: 帆 #################

（1）数据库

  1.    # 查看所有的数据库
  2.    SHOW DATABASES ;
  3.    # 创建一个数据库
  4.    CREATE DATABASE k;
  5.    # 删除一个数据库
  6.    DROP DATABASE k;
  7.    # 使用这个数据库
  8.    USE k;

（2）表

  1. # 查看所有的表
  2. SHOW TABLES ;
  3.  # 创建一个表
  4. CREATE TABLE n(id INT, name VARCHAR(10));
  5. CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
  6. CREATE TABLE m(id INT, name VARCHAR(10));
  7. # 直接将查询结果导入或复制到新创建的表
  8. CREATE TABLE n SELECT * FROM m;
  9. # 新创建的表与一个存在的表的数据结构类似
  10.  CREATE TABLE m LIKE n;
  11. # 创建一个临时表
  12.  # 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
  13.  CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));
  14. # 直接将查询结果导入或复制到新创建的临时表
  15. CREATE TEMPORARY TABLE tt SELECT * FROM n;
  16. # 删除一个存在表
  17. DROP TABLE IF EXISTS m;
  18.  # 更改存在表的名称
  19. ALTER TABLE n RENAME m;
  20. RENAME TABLE n TO m;
  21. # 查看表的结构(以下五条语句效果相同）
  22. DESC n; # 因为简单，所以建议使用
  23. DESCRIBE n;
  24. SHOW COLUMNS IN n;
  25. SHOW COLUMNS FROM n;
  26.  EXPLAIN n;
  27. # 查看表的创建语句
  28. SHOW CREATE TABLE n;

 （3）表的结构

  1. # 添加字段
  2.  ALTER TABLE n ADD age VARCHAR(2) ;
  3.  # 删除字段
  4.  ALTER TABLE n DROP age;
  5.  # 更改字段属性和属性
  6.  ALTER TABLE n CHANGE age a INT;
  7.  # 只更改字段属性
  8.  ALTER TABLE n MODIFY age VARCHAR(7) ;

 （4）表的数据

  1. # 增加数据
  2.  INSERT INTO n VALUES (1, 'tom', '23'), (2, 'john', '22');
  3.  INSERT INTO n SELECT * FROM n; # 把数据复制一遍重新插入
  4.  # 删除数据
  5.  DELETE FROM n WHERE id = 2;
  6.  # 更改数据
  7.  UPDATE n SET name = 'tom' WHERE id = 2;
  8.  # 数据查找
  9.  SELECT * FROM n WHERE name LIKE '%h%';
  10.  # 数据排序(反序)
  11.  SELECT * FROM n ORDER BY name, id DESC ;

（5）键

  1. # 添加主键
  2.  ALTER TABLE n ADD PRIMARY KEY (id);
  3.  ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id); # 主键只有一个，所以定义键名似乎也没有什么用
  4.  # 删除主键
  5.  ALTER TABLE n DROP PRIMARY KEY ;
  6.  # 添加外键
  7.  ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id); # 自动生成键名m_ibfk_1
  8.  ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id); # 使用定义的键名fk_id
  9.  # 删除外键
  10.  ALTER TABLE m DROP FOREIGN KEY `fk_id`;
  11.  # 修改外键
  12.  ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id); # 删除之后从新建
  13.  # 添加唯一键
  14.  ALTER TABLE n ADD UNIQUE (name);
  15.  ALTER TABLE n ADD UNIQUE u_name (name);
  16.  ALTER TABLE n ADD UNIQUE INDEX u_name (name);
  17.  ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
  18.  CREATE UNIQUE INDEX u_name ON n(name);
  19.  # 添加索引
  20.  ALTER TABLE n ADD INDEX (age);
  21.  ALTER TABLE n ADD INDEX i_age (age);
  22.  CREATE INDEX i_age ON n(age);
  23.  # 删除索引或唯一键
  24.  DROP INDEX u_name ON n;
  25.  DROP INDEX i_age ON n;

（6）视图

  1. # 创建视图
  2.  CREATE VIEW v AS SELECT id, name FROM n;
  3.  CREATE VIEW v(id, name) AS SELECT id, name FROM n;
  4.  # 查看视图(与表操作类似)
  5.  SELECT * FROM v;
  6.  DESC v;
  7.  # 查看创建视图语句 
  8. SHOW CREATE VIEW v;
  9.  # 更改视图
  10.  CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
  11.  ALTER VIEW v AS SELECT name FROM n ;
  12.  # 删除视图
  13.  DROP VIEW IF EXISTS v;

（7）联接

  1. # 内联接
  2.  SELECT * FROM m INNER JOIN n ON m.id = n.id;
  3.  # 左外联接
  4.  SELECT * FROM m LEFT JOIN n ON m.id = n.id;
  5.  # 右外联接
  6.  SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
  7.  # 交叉联接
  8.  SELECT * FROM m CROSS JOIN n; # 标准写法 
  9. SELECT * FROM m, n;
  10.  # 类似全连接full join的联接用法
  11.  SELECT id,name FROM m
  12.  UNION
  13.  SELECT id,name FROM n;

（8）函数

  1. # 聚合函数
  2.  SELECT count(id) AS total FROM n; # 总数
  3.  SELECT sum(age) AS all_age FROM n; # 总和
  4.  SELECT avg(age) AS all_age FROM n; # 平均值
  5.  SELECT max(age) AS all_age FROM n; # 最大值 
  6. SELECT min(age) AS all_age FROM n; # 最小值
  7.  # 数学函数
  8.  SELECT abs(-5); # 绝对值
  9.  SELECT bin(15), oct(15), hex(15); # 二进制，八进制，十六进制
  10.  SELECT pi(); # 圆周率3.141593
  11.  SELECT ceil(5.5); # 大于x的最小整数值6
  12.  SELECT floor(5.5); # 小于x的最大整数值5
  13.  SELECT greatest(3,1,4,1,5,9,2,6); # 返回集合中最大的值9
  14.  SELECT least(3,1,4,1,5,9,2,6); # 返回集合中最小的值1
  15.  SELECT mod(5,3); # 余数2
  16.  SELECT rand(); # 返回０到１内的随机值，每次不一样
  17.  SELECT rand(5); # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
  18.  SELECT round(1415.1415); # 四舍五入1415
  19.  SELECT round(1415.1415, 3); # 四舍五入三位数1415.142
  20.  SELECT round(1415.1415, -1); # 四舍五入整数位数1420
  21.  SELECT truncate(1415.1415, 3); # 截短为3位小数1415.141
  22.  SELECT truncate(1415.1415, -1); # 截短为-1位小数1410
  23.  SELECT sign(-5); # 符号的值负数-1
  24.  SELECT sign(5); # 符号的值正数1
  25.  SELECT sqrt(9); # 平方根3
  26.  SELECT sqrt(9); # 平方根3
  27.  # 字符串函数
  28.  SELECT concat('a', 'p', 'p', 'le'); # 连接字符串-apple
  29.  SELECT concat_ws(',', 'a', 'p', 'p', 'le'); # 连接用','分割字符串-a,p,p,le
  30.  SELECT insert('chinese', 3, 2, 'IN'); # 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese
  31.  SELECT left('chinese', 4); # 返回字符串'chinese'左边的4个字符-chin
  32.  SELECT right('chinese', 3); # 返回字符串'chinese'右边的3个字符-ese
  33.  SELECT substring('chinese', 3); # 返回字符串'chinese'第三个字符之后的子字符串-inese
  34.  SELECT substring('chinese', -3); # 返回字符串'chinese'倒数第三个字符之后的子字符串-ese
  35.  SELECT substring('chinese', 3, 2); # 返回字符串'chinese'第三个字符之后的两个字符-in
  36.  SELECT trim(' chinese '); # 切割字符串' chinese '两边的空字符-'chinese'
  37.  SELECT ltrim(' chinese '); # 切割字符串' chinese '两边的空字符-'chinese '
  38.  SELECT rtrim(' chinese '); # 切割字符串' chinese '两边的空字符-' chinese'
  39.  SELECT repeat('boy', 3); # 重复字符'boy'三次-'boyboyboy'
  40.  SELECT reverse('chinese'); # 反向排序-'esenihc'
  41.  SELECT length('chinese'); # 返回字符串的长度-7
  42.  SELECT upper('chINese'), lower('chINese'); # 大写小写 CHINESE chinese
  43.  SELECT ucase('chINese'), lcase('chINese'); # 大写小写 CHINESE chinese
  44.  SELECT position('i' IN 'chinese'); # 返回'i'在'chinese'的第一个位置-3
  45.  SELECT position('e' IN 'chinese'); # 返回'i'在'chinese'的第一个位置-5
  46.  SELECT strcmp('abc', 'abd'); # 比较字符串，第一个参数小于第二个返回负数- -1
  47.  SELECT strcmp('abc', 'abb'); # 比较字符串，第一个参数大于第二个返回正数- 1
  48.  # 时间函数
  49.  SELECT current_date, current_time, now(); # 2018-01-13 12:33:43 2018-01-13 12:33:43
  50.  SELECT hour(current_time), minute(current_time), second(current_time); # 12 31 34
  51.  SELECT year(current_date), month(current_date), week(current_date); # 2018 1 1
  52.  SELECT quarter(current_date); # 1
  53.  SELECT monthname(current_date), dayname(current_date); # January Saturday
  54.  SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date); # 7 13 13
  55.  # 控制流函数
  56.  SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f'); # t f
  57.  SELECT ifnull(NULL, 't'), ifnull(2, 't'); # t 2
  58.  SELECT isnull(1), isnull(1/0); # 0 1 是null返回1，不是null返回0
  59.  SELECT nullif('a', 'a'), nullif('a', 'b'); # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
  60.  SELECT CASE 2
  61.  WHEN 1 THEN 'first'
  62.  WHEN 2 THEN 'second'
  63.  WHEN 3 THEN 'third'
  64.  ELSE 'other'
  65.  END ; # second
  66.  # 系统信息函数
  67.  SELECT database(); # 当前数据库名-test
  68.  SELECT connection_id(); # 当前用户id-306
  69.  SELECT user(); # 当前用户-root@localhost
  70.  SELECT version(); # 当前mysql版本
  71.  SELECT found_rows(); # 返回上次查询的检索行数

（9）用户

  1. # 增加用户
  2.  CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
  3.  INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test')); # 在用户表中插入用户信息，直接操作User表不推荐
  4.  # 删除用户
  5.  DROP USER 'test'@'localhost';
  6.  DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
  7.  FLUSH PRIVILEGES ;
  8.  # 更改用户密码
  9.  SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
  10.  UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
  11.  FLUSH PRIVILEGES ;
  12.  # 用户授权
  13.  GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
  14.  # 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
  15.  FLUSH PRIVILEGES ; # 刷新系统权限表,使授予权限生效
  16.  # 撤销用户授权
  17.  REVOKE DELETE ON *.* FROM 'test'@'localhost'; # 取消该用户的删除权限

（10）存储过程

  1. # 创建存储过程
  2.  DELIMITER // # 无参数
  3.  CREATE PROCEDURE getDates()
  4.  BEGIN
  5.  SELECT * FROM test ;
  6.  END // 
  7. CREATE PROCEDURE getDates_2(IN id INT) # in参数
  8.  BEGIN
  9.  SELECT * FROM test WHERE a = id;
  10.  END //
  11.  CREATE PROCEDURE getDates_3(OUT sum INT) # out参数
  12.  BEGIN
  13.  SET sum = (SELECT count(*) FROM test);
  14.  END //
  15.  CREATE PROCEDURE getDates_4(INOUT i INT) # inout参数
  16.  BEGIN
  17.  SET i = i + 1;
  18.  END //
  19.  DELIMITER ;
  20.  # 删除存储过程
  21.  DROP PROCEDURE IF EXISTS getDates;
  22.  # 修改存储过程的特性
  23.  ALTER PROCEDURE getDates MODIFIES SQL DATA ;
  24.  # 修改存储过程语句（删除再重建）略
  25.  # 查看存储过程
  26.  SHOW PROCEDURE STATUS LIKE 'getDates'; # 状态
  27.  SHOW CREATE PROCEDURE getDates_3; # 语句
  28.  # 调用存储过程
  29.  CALL getDates();
  30.  CALL getDates_2(1);
  31.  CALL getDates_3(@s);
  32.  SELECT @s;
  33.  SET @i = 1;
  34.  CALL getDates_4(@i);
  35.  SELECT @i; # @i = 2

（11）其他语句

  1. # 查看所有的表信息（包括视图）
  2.  SHOW TABLE STATUS;

（12）其他

  1. # 数据库备份
  2.  mysqldump -u root -p db_name > file.sql
  3.  mysqldump -u root -p db_name table_name > file.sql
  4.  # 数据库还原   我的是E盘
  5.  mysql -u root -p < E:file.sql
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/btsn/p/11245746.html