MYSQL基础

SunRain
132 阅读6分钟

登录MYSQL监控

  • MYSQL -U ROOT -P123456; 密码和-P之间没有空格；
  • MYSQL -U ROOT -P; enter之后输入密码；

修改ROOT密码

  • SET PASSWORD FOR root@localhost=PASSWORD('1234');

创建用户

  • CREATE USER test@localhost IDENTIFIED BY '1234';

设置权限

  • GRANT ALL ON db1.＊ TO test@localhost; “赋予的权限”如果是所有权限，就设置为“ALL”；如果仅允许SELECT和UPDATE，就设置为“SELECT,UPDATE”；如果是所有数据库的所有表，就设置为“＊.＊”。

显示当前用户

  • SELECT USER();

显示当前版本

  • SELECT VERSION();

查看所有数据库

  • SHOW DATABASES;

查看当前数据库名称

  • SELECT DATABASE();

访问其他数据库

  • SELECT ＊ FROM db2.table;

使用数据库

  • use 数据库名;

创建数据库

  • CREATE DATABASE <数据库名>;

删除数据库

  • drop database <数据库名>;

创建表格

  • CREATE TABLE 表名 (列名1 数据类型1，列名2 数据类型2…);

查看所有表格

  • SHOW TABLES;

删除表格

  • DROP TABLE table_name ;

删除表格全部记录

  • DELETE FROM 表名;

确认表的列结构

  • DESC 表名;

插入记录

  • INSERT INTO 表名 VALUES (数据1,数据2…);
  • INSERT INTO 表名 (列名1,列名2…) VALUES (数据1,数据2…), (数据1,数据2…);

查询数据

  • SELECT 列名1，列名2… FROM 表名;

复制表格

  • CREATE TABLE tb1A SELECT ＊ FROM tb1;

数据类型

image.png

字符串数据类型

image.png

日期与时间类型

image.png

修改列的数据类型

  • ALTER TABLE 表名 MODIFY 列名 数据类型;
  • ALTER TABLE tb1C MODIFY name VARCHAR(100);

添加列

  • ALTER TABLE表名ADD列名 数据类型;
  • ALTER TABLE tb1C ADD birth DATETIME;
  • ALTER TABLE tb1D ADD birth DATETIME FIRST;添加列指定位置为第一个
  • ALTER TABLE tb1E ADD birth DATETIME AFTER empid;将列birth添加到表tb1E的列empid的后面。
  • ALTER TABLE tb1C MODIFY birth DATETIME FIRST;更改位置

修改列的数据类型或位置的同时也修改列名

  • ALTER TABLE 表名 CHANGE 修改前的列名 修改后的列名 修改后的数据类型;
  • ALTER TABLE tb1C CHANGE birth birthday DATE;

删除列

  • ALTER TABLE 表名 DROP 列名;
  • ALTER TABLE tb1C DROP birthday;

主键

  • CREATE TABLE表名(列名 数据类型PRIMARY KEY …);
  • CREATE TABLE t_pk (a INT PRIMARY KEY ,b VARCHAR(10));

设置唯一键

  • CREATE TABLE t_uniq (a INT UNIQUE，b VARCHAR(10));

具有自动连续编号功能的列的定义

  • ALTER TABLE 表名 AUTO_INCREMENT = 1; 注意添加数据时可以指定自增列的值，下一条数据会根据最大的值接着递增，如果删除所有数据，也会从之前最大的值接着递增；

设置默认值

  • CREATE TABLE 表名 (列名 数据类型 DEFAULT 默认值…);
  • ALTER TABLE tb1G MODIFY name VARCHAR(10) DEFAULT '未输入姓名';

创建索引

  • CREATE INDEX 索引名 ON 表名 (列名);
  • CREATE INDEX my_ind ON tb1G (empid);

显示索引

  • SHOW INDEX FROM 表名;
  • SHOW INDEX FROM 表名 \G; \G格式化显示

删除索引

  • DROP INDEX 索引名 ON 表名;
  • mysql> DROP INDEX my_ind ON tb1G;

复制表的列结构和记录

  • CREATE TABLE 新表名 SELECT ＊ FROM元表名；记录和表结构
  • CREATE TABLE 新表名 LIKE 元表名;只要表结构
  • CREATE TABLE tb1_bkc LIKE tb1;只要表结构
  • INSERT INTO 表名 SELECT ＊ FROM 元表名；复制其他表的记录
  • INSERT INTO tb1_bkc(name) SELECT empid FROM tb1;选择某一列进行复制

存在则删除

  • DROP TABLE IF EXISTS tb1A;

查询指定字段

  • SELECT sales,empid FROM tb;
  • SELECT 列名 AS 别名 FROM 表名; 别名查询

使用列值进行计算并显示

  • SELECT sales ＊ 10000 as 销售额 FROM tb;
  • SELECT a/b FROM 表名;
  • SELECT a+b FROM 表名;
  • SELECT AVG(sales) FROM tb;
  • SELECT SUM(sales) FROM tb;
  • SELECT COUNT(sales) FROM tb;
  • SELECT CONCAT(empid,name,'先生') FROM tb1; 拼接显示
  • SELECT RIGHT(empid,2) FROM tb1; 显示字符串右边两个字符
  • SELECT LEFT(empid,2) FROM tb1; 显示字符串左边两个字符
  • SELECT SUBSTRING(empid,2,3) FROM tb1; 从列empid的第2个字符开始连续显示3个字符。
  • SELECT REVERSE(name) FROM tb1; 倒着显示出来
  • SELECT ＊ FROM tb LIMIT 3; 只显示3条
  • SELECT 列名 FROM 表名 WHERE 条件; 带条件查询

条件运算符 image.png

  • SELECT ＊ FROM tb WHERE empid LIKE 'A101';

LIKE通配符 image.png

  • SELECT ＊ FROM tb1H WHERE age IS NULL;

  • SELECT ＊ FROM tb1H WHERE age IS NOT NULL;

  • SELECT DISTINCT empid FROM tb; 去重显示

  • SELECT ＊ FROM tb WHERE(sales>=200 OR empid LIKE '%1')AND month=4; 多个条件用and

    SELECT empid,sales, 
    CASE WHEN 
            sales>=100 
            THEN '高' 
        WHEN sales>=50 
            THEN '中等' 
            ELSE '低' 
            END 
        AS 评价 FROM tb;

image.png

指定记录的显示范围

  • SELECT ＊ FROM tb ORDER BY sales DESC LIMIT 2 OFFSET 3;

分组显示

  • SELECT 列名 FROM 表名 GROUP BY 用于分组的列名;

计算各组的记录数

  • SELECT COUNT(＊) FROM tb GROUP BY empid;

分组合计

  • SELECT empid,SUM(sales) AS 合计 FROM tb GROUP BY empid;

设置条件分组显示

  • SELECT 统计列 FROM 表名 GROUP BY 分组列 HAVING 条件;
SELECT empid,SUM(sales) FROM tb GROUP BY empid HAVING SUM(sales)>=200;

提取数据之后分组

SELECT empid,AVG(sales) FROM tb WHERE sales>=50 GROUP BY empid;`

分组之后再排序

SELECT empid,AVG(sales) FROM tb GROUP BY empid ORDER BY AVG(sales) DESC;

更新数据

  • UPDATE表名SET列名 = 设置的值;
  • UPDATE tb SET remark='无特殊记录';

更新满足条件的数据

  • UPDATE 表名 SET 列名 = 设置的值 WHERE 条件;
  • UPDATE tb SET remark='优秀' WHERE sales>=100;
  • UPDATE tb SET remark='加油！' ORDER BY sales LIMIT 3;

复制符合条件的记录

  • INSERT INTO 已存在的表 SELECT ＊ FROM tb WHERE empid LIKE 'A101';

排序后复制

CREATE TABLE tb_2to5 SELECT ＊ FROM tb ORDER BY sales DESC LIMIT 4 OFFSET 1;

删除指定记录

  • DELETE FROM 表名 WHERE 条件;

连表查询

SELECT 列名1 FROM 表名1 UNION SELECT 列名2 FROM 表名2;

连表查询带条件

(SELECT empid FROM tb WHERE sales>=200) UNION (SELECT empid FROM tb1 WHERE age>=35);

连表查询不去重

(SELECT empid FROM tb WHERE sales>=200) UNION ALL (SELECT empid FROM tb1 WHERE age>=35);

连表查询

SELECT 列名 FROM 表1 JOIN 要连接的表2 ON 表1的列 = 表2的列;
SELECT ＊ FROM tb INNER JOIN tb1 ON tb.empid=tb1.empid;  内连接
SELECT tb.empid,tb1.name,tb.sales FROM tb JOIN tb1 ON tb.empid=tb1.empid;

表别名查询

SELECT x.empid,y.name,x.sales FROM tb as x JOIN tb1 as y ON x.empid=y.empid;

连表查询 USING表格连接字段相同时

SELECT tb.empid,tb1.name,tb.sales FROM tb JOIN tb1 USING(empid);

多表连接查询

SELECT tb.empid,tb.sales,tb1.name,tb3.region FROM tb JOIN tb1 USING(empid) JOIN tb3 USING(empid) ;
SELECT tb.empid,tb.sales,tb1.name,tb3.region FROM tb JOIN tb1 ON tb.empid=tb1.empid JOIN tb3 ON tb.empid=tb3.empid ;

使用了JOIN（或者INNER JOIN）的“内连接”只会提取与连接键相匹配的记录,如果要显示就使用外连接

SELECT 列名 FROM表1 LEFT JOIN 要连接的表2 ON表1的列 = 表2的列; 左外连接 以左边的表字段为主
SELECT 列名 FROM表1 RIGHT JOIN 要连接的表2 ON表1的列 = 表2的列;  右外连接 以右边的表字段为主

自连接

SELECT ＊ FROM tb1 AS a JOIN tb1 AS b;

子查询

SELECT ＊ FROM tb WHERE sales IN (SELECT MAX(sales) FROM tb);
SELECT ＊ FROM tb1 WHERE age >= (SELECT AVG(age) FROM tb1); 查询提取大于等于平均值的记录
SELECT ＊ FROM tb1 WHERE empid IN (SELECT empid FROM tb WHERE sales>=200); 显示销售额大于等于200万元的员工姓名

存在和不存在

SELECT ＊ FROM tb1 WHERE EXISTS (SELECT ＊ FROM tb WHERE tb.empid=tb1.empid);
SELECT ＊ FROM tb1 WHERE NOT EXISTS (SELECT ＊ FROM tb WHERE tb.empid=tb1.empid);
avatar
文章
阅读
粉丝