mysql 操作笔记

冷不防 2022-05-31 06:29 193阅读 0赞

在线演示地址 [https://app.bytescout.com/sql-trainer/index.html][https_app.bytescout.com_sql-trainer_index.html]  
启动和关闭数据库

> net start mysql

> net stop mysql

登录数据库

> mysql -h localhost -u root -p 数据库名称

查询所有的数据库（所有的命令都要以分号结尾）

> show databases;

选择数据库

> use 数据库名;

查询所有数据表

> show tables;

删除某个数据库

> drop database databaseName;

查看数据库字符编码

> show create database test1;

设置当前窗口的数据库字符编码，即使基于会话session级别的，关闭此窗口，重新打开另外的窗口操作数据库依然是原来的字符编码

这里将utf-8设置为gbk：

> set character\_set\_database=gbk;

> set character\_set\_server=gbk;

展示编码

> show variables like ‘%char%’;

![这里写图片描述][70]

设置全局的数据库字符编码，即使基于整个MySQL服务的，当重启MySQL服务的时候，编码依然会变为原来的字符编码

> set global character\_set\_database=gbk;

> set global character\_ser\_server=gbk;

> show variables like ‘%char%’;

![这里写图片描述][70 1]  
设置永久的字符编码，即需要在配置文件中修改数据库的字符编码

编辑 /etc/my.cnf，

在里面加入，已经有\[XXX\]的，在里面直接加入即可。

\[mysqld\]

character-set-server=utf8

\[client\]

default-character-set=utf8

\[mysql\]

default-character-set=utf8

然后重启数据库即可，service mysql restart

显示表结构

> describe tableName; (或者简写: desc tableName;)

创建表

>create table tableName( 
    
     id int(10) NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
    
    name varchar(20)
    
    );

显示某一个表

> show create table tableName;  
> select \* from tableName;  
> ![这里写图片描述][70 2]

导入.sql文件(文件所在路径是F:\\file.sql

> source F:/file.sql;

删除表

> drop table tableName;

删除表中的所有数据，但是表结构依然存在

> delete from tableName;

向表中加入数据并查看

> insert into tableName(id,name…) values(‘1’,‘admin’,…);

> select \* from tableName;

更新表中的数据,如果没有where，则将影响所有的记录

> update tableName set name=‘administrator’ where id=‘1’;

查看服务器版本和当前日期

> select version(),current\_date;

> select version();

> select now();

把mysql作为一个简单的计算器

> select pi();

> select pi()\*10;  
> ![这里写图片描述][70 3]

使用load

> load data local infile filePath into table tableName;

退出MYSQL命令: exit (回车)

修改密码。

格式:mysqladmin -u用户名-p旧密码password 新密码

*创建用户:*  
命令: >CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;

例子: CREATE USER ‘dog’@‘localhost’ IDENTIFIED BY ‘123456’;

> CREATE USER ‘dog2’@‘localhost’ IDENTIFIED BY ‘’;

host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

授权:  
命令:GRANT privileges ON databasename.tablename TO ‘username’@‘host’

PS: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.\*.

例子: GRANT SELECT, INSERT ON mq.\* TO ‘dog’@‘localhost’;

查询所有用户

> SELECT User, Host, Password FROM mysql.user;

显示所有的用户（不重复）  
熟悉Mysql的朋友们都知道DISTINCT这个修饰的作用吧，对了，就是去除重复的数据，所以我们可以使用如下命令显示所有你的Mysql的用户而忽略那些仅仅是主机名不同的用户。

> SELECT DISTINCT User FROM mysql.user;

授权:  
命令:

> GRANT privileges ON databasename.tablename TO ‘username’@‘host’  
> 说明:  
> privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL  
> databasename：数据库名 所有用\*  
> tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.\*  
> 例子:  
> GRANT SELECT, INSERT ON test.user TO ‘pig’@’%’;  
> GRANT ALL ON *.* TO ‘pig’@’%’;

\#\#\#\#SQL命令  
注意：  
（1）所有的SQL命令都必须以英文的分号结尾！之后  
（2）一条命令可书写多行中；  
（3）SQL命令不区分大小写；  
（4）编写SQL命令时可以使用单行注释：\#…还可以使用多行注释：/*…*/  
常用的MySQL管理命令：  
（1）quit; 断开到服务器的链接；  
（2）show databases; 显示服务器上已有的所有数据库；  
（3）use 库名； 开始使用指定的数据库；  
（4）show tables; 显示当前库所有的表；  
show create table 表名

\#\#\#\#常用的SQL语言：  
（0）设置客户端工具提交的SQL语句所用字符编码为UTF8  
SET NAMES UTF8  
（1）试着丢弃指定的数据库  
DROP DATABASE IF EXISTS库名;  
（2）新建一个数据库  
CREATE DATABASE 库名(不能已经存在) CHARSET=UTF8;  
（3）进入指定的库  
USE 库名;  
（4）创建一个新的数据表  
CREATE TABLE 表名(列名 类型,列名 类型,列名 类型); （DECIMAL小数）  
（5）向表中插入一行数据/记录  
INSERT INTO 表名 VALUES( 值, 值, 值);  
提示：一行中数据值的数量必须与表声明中列的数量相同，字符串和日期数据必须用英文单引号括起来，  
数字可以也或不可以。  
（6）从表中删除一行记录  
DELETE FROM 表名； \#删除所有的记录行，仍保留本身  
DELETE FROM 表名 WHERE 条件子句; \#只删除满足条件的记录。  
（7）修改表中的一行记录————用的比较少，最不好记忆！！  
UPDATE 表名 SET 列名=值,列名=值,列名=值,列名=值; \#修改所有的行。  
UPDATE 表名 SET 列名=值,列名=值,列名=值,列名=值 WHERE 条件子句; \#只修改满足条件的记录。  
（8）查询表中已有的所有数据  
SELECT \* FROM 表 (form:表单 from:从。。来)

\#\#\#\#SQL语句分为四大类组：  
（1）DDL：Data Define Language,数据定义语言（定义列）  
包括：.CREATE、.DROP、ALTER（架构师改表列）、TRUNCATE（截断表）  
（2）DML:Data Manipulate Language,数据操作语言（操作行）  
包括：.INSERT、.DELECT、.UPDATE  
（3）DQL:Data Query Language,数据查询语言  
包括：.SELECT  
（4）DCL:Data Contyol Language,数据控制语言  
包括：GRANT（授权）、REVOKE（收权）  
.CREATE(重点)

\#\#\#\#MySQL中的列类型  
CREATE TABLE 表名（列名 列类型）  
提示：不同数据库中列类型是不同的————MySQL中的列类型是最丰富的。

（1）数值类型（引号可用可不用）
      TINYINT      微整数，1个字节，-128~127
      SMALLINT     小整数，2个字节，-32768~32767
      INT          整数，4个字节，-2147483648~2147483647
      BIGINT       大整数，8个字节，很大的数
      FLOAT(M,D)   单精度浮点数，4个字节，1234.5678  1.2346×10^38
      DOUBLE(M,D)  双精度浮点数，8个字节，1.2343×10^38
      DECIMAL(M,D) 定点小数，可以保证精确计算
      BOOL         布尔，真假类型，只能取值为TRUE/1或FALSE/0,  底层等价于YINYINT(1)
          true   false
    （2）日期时间类型（必须用括号括起来）
     DATE      日期，数据形如'2017-5-8'	    
     TIME      时间，数据形如'10:22:3'
     DATETIME  日期时间类型，数据形如'2012-4-5 12:15:42'
    （3）字符串类型(电话号码和密码)
      CHAR(M)     定长字符串，可能产生空间浪费，但存取速度快，M不能超过255  character（身份证号）
      VARCHAR(M)  变长字符串，不会产生空间浪费，但存取速度稍慢，M不能超过65535(论坛帖子类型)
      TEXT(M)     变长字符串，M不能超过4G
             笔记本图片，用字符串记录其URL地址

例如：CHAR(6) 即使数据长度不够，也要用/0占位  
‘a/0/0/0/0/0’  
‘ab/0/0/0/0’  
VARCHAR(6) 实际占用的空间可能是 字符个数+1  
‘a/0’  
‘ab/0’

\#\#\#\#MySQL中的约束  
Constraint:约束，表中某个列上的数据往往都是有限制的，如sex CHAR(1) 只能存“男”或“女”、scoreDECIMAL(4,1)  
不能存500.6、phoneCHAR(11)必须是符合电话号码的格式要求、uname不能有重复值、upwd不能为空…  
MySQL中有如下约束类型：  
.（1）PRIMARY KEY:主键约束  
语法：列名 类型 PRIMARY KEY  
声明为主键的列上的值唯一（不能出现重复值），并且不能为空（NULL），表中的记录会自动按照主键列上的  
值由小到大排序————一个表中至多只能有一个列声明为主键列。  
（2）UNIQUE:唯一约束，特别的，与众不同的  
语法：列名 类型 UNIQUE  
声明为UNIQUE的列上不允许出现重复值,但可以出现NULL，且可以有多个NULL。  
（3）NOT NULL：非空约束  
语法：列名 类型 NOT NULL  
声明为NOT NULL的列上不允许出现空值。  
（4）CHECK：检查约束  
语法：列名 类型 CHECK(条件)  
例如：score DECIMAL(4,1) CHECK(score<=100 AND score>=0)  
提示：MySQL默认不支持检查约束  
（5）FOREIGN KEY：外键约束  
语法： deptId INT,  
FOREIGN KEY(deptID) REFERENCES dept(did)  
声明为外键的列上出现的值必须在另一个表的主键列上存在。  
外键列上的值可以重复，也可以为空。有的项目设计师不喜欢用外键————因为他会降低INSERT/UPDATA D的  
执行速度。  
（6）DEFAULT：默认值约束  
语法：列名 类型 DEFAULT 值  
若某个列上为指定明确的值，将自动使用默认值。  
例如：CREATE TABLE user(uidINT, sexCHAR(1) DEFAULT’男’)  
INSERT INTO user VALUE(1,‘男’);  
INSERT INTO user VALUE(2,‘女’);  
INSERT INTO user VALUE(3,DEFAULT) \#3号用户为男  
INSERT INTO user(uid) VALUE(4) \#4号用户为男  
AUTO\_INCREMENT：  
MySQL 数据库专有的关键字，只能用在INT PRIMARY KEY的主键列上，用于实现自增列，若赋值为NULL，则会  
查找当前已有的最大值。+1用于新的记录行。

\#\#\#\#简单查找  
MySQL中的约束  
Constraint:约束，表中某个列上的数据往往都是有限制的，如sex CHAR(1) 只能存“男”或“女”、scoreDECIMAL(4,1)  
不能存500.6、phoneCHAR(11)必须是符合电话号码的格式要求、uname不能有重复值、upwd不能为空…  
MySQL中有如下约束类型：  
.（1）PRIMARY KEY:主键约束  
语法：列名 类型 PRIMARY KEY  
声明为主键的列上的值唯一（不能出现重复值），并且不能为空（NULL），表中的记录会自动按照主键列上的  
值由小到大排序————一个表中至多只能有一个列声明为主键列。  
（2）UNIQUE:唯一约束，特别的，与众不同的  
语法：列名 类型 UNIQUE  
声明为UNIQUE的列上不允许出现重复值,但可以出现NULL，且可以有多个NULL。  
（3）NOT NULL：非空约束  
语法：列名 类型 NOT NULL  
声明为NOT NULL的列上不允许出现空值。  
（4）CHECK：检查约束  
语法：列名 类型 CHECK(条件)  
例如：score DECIMAL(4,1) CHECK(score<=100 AND score>=0)  
提示：MySQL默认不支持检查约束  
（5）FOREIGN KEY：外键约束  
语法： deptId INT,  
FOREIGN KEY(deptID) REFERENCES dept(did)  
声明为外键的列上出现的值必须在另一个表的主键列上存在。  
外键列上的值可以重复，也可以为空。有的项目设计师不喜欢用外键————因为他会降低INSERT/UPDATA D的  
执行速度。  
（6）DEFAULT：默认值约束  
语法：列名 类型 DEFAULT 值  
若某个列上为指定明确的值，将自动使用默认值。  
例如：CREATE TABLE user(uidINT, sexCHAR(1) DEFAULT’男’)  
INSERT INTO user VALUE(1,‘男’);  
INSERT INTO user VALUE(2,‘女’);  
INSERT INTO user VALUE(3,DEFAULT) \#3号用户为男  
INSERT INTO user(uid) VALUE(4) \#4号用户为男  
AUTO\_INCREMENT：  
MySQL 数据库专有的关键字，只能用在INT PRIMARY KEY的主键列上，用于实现自增列，若赋值为NULL，则会  
查找当前已有的最大值。+1用于新的记录行。  
7.简单查询————对查询到的记录进行筛选/条件查询  
语法： SELECT …  
FROM 表名  
WHERE 条件判定;  
示例：查询出编号为8的员工的所有的信息————相等查询。\*:表示所有的列  
SELECT \* FROM emp WHERE eid=8;  
练： 查询出姓名为KING 的员工所有信息；  
SELECT \* FROM emp WHERE ename=‘KING’;  
查询出部门编号为10的员工所有信息;  
SELECT \* FROM emp WHERE deptId=10;  
查询出部门编号为10的员工所有信息,按工资降序排列；  
SELECT \* FROM emp WHERE deptId=10 ORDER BY salary DESC;  
【既有WHERE又有ORDER BY，先写WHERE】  
示例：查询出工资大于等于6000的员工的所有信息；  
SELECT \* FROM emp WHERE salary >= 6000;  
练：查询出1991年之后出生的员工；  
SELECT \* FROM emp WHERE birthday >= ‘1991-1-1’;  
示例：查询出不在10号部门的员工的所有信息；【!= 或者 <>：不等于】  
SELECT \* FROM emp WHERE deptId !=10;  
8.简单查询————多条件查询  
.语法： SELECT …FROM…WHERE 条件1 AND 条件2;【条件完整独立】  
示例：查询出工资在6000（包含）~7000（不包含）之间的员工信息  
SELECT \* FROM emp WHERE salary >=6000 AND salary<7000;  
练：查询出所有在1990年出生的员工所有信息；  
SELECT \* FROM emp WHERE birthday > ‘1990-1-1’ AND birthday <= ‘1990-12-31’;  
上述语句变种：列名 BETWEEN 值1 AND 值2  
SELECT \* FROM emp WHERE birthday BETWEEN ‘1990-1-1’ AND ‘1990-12-31’;  
查询出工资大于8000的10号部门的员工所有信息；  
SELECT \* FROM emp WHERE salary > 8000 AND deptId=10;

.语法： SELECT ...FROM...WHERE  条件1 OR 条件2
      示例：查询出部门编号为10或30的员工所有的信息；
            SELECT * FROM emp WHERE deptId=10 OR deptId=30;
      练：查询出工资小于等于6000或者大于10000的员工信息；
            SELECT * FROM emp WHERE salary <=6000 OR salary >10000; 
         查询出工资大于8000以及在10号部门的所有员工信息；
        SELECT * FROM emp WHERE salary >8000 OR deptId=10;
         查询出所有1990年和1992年出生的所有员工信息；
        SELECT * FROM emp WHERE (birthday >= '1990-1-1' AND birthday <= '1990-12-31') 
        OR (birthday >= '1992-1-1' AND birthday <= '1992-12-31' );
         查询出10、30、40号部门的所有员工信息；
        SELECT * FROM emp WHERE (deptId=10) OR (deptId=30) OR (deptId=40);
        【上述语句等价于： WHERE 列名 IN (...)
             SELECT * FROM emp WHERE deptId IN (10,30,40);】
    
    .（取反/非）语法：SELECT ...FROM...WHERE  条件1 NOT 条件2
      练：查询出不在10、30、40号部门的所有员工信息；
           SELECT * FROM emp WHERE (deptId!=10) AND (deptId!=30) AND (deptId!=40);
       等价于 SELECT * FROM emp WHERE deptId NOT IN (10,30,40);
         查询所有不在1990和1992年出生的员工；
       SELECT * FROM emp WHERE NOT ((birthday >= '1990-1-1' AND birthday <= '1990-12-31') 
         OR (birthday >= '1992-1-1' AND birthday <= '1992-12-31' ));
         等价于 SELECT * FROM emp WHERE (birthday < '1990-1-1') OR (birthday >'1990-12-31') OR ();

示例：查询部门编号为NULL 的员工所有信息  
SELECT \* FROM emp WHERE deptId IS NULL;  
9.简单查询————条件查询之————模糊查询  
语法：SELECT…  
FROM…  
WHERE 列名 LIKE 模糊匹配表达式;  
模糊匹配表达式一般都是字符串类型：  
WHERE title LIKE ‘戴尔’; \#标题必须等于’戴尔’;  
WHERE title LIKE ‘%戴尔’; \#%匹配任意多个任意字符;  
WHERE title LIKE ‘戴尔%’; \#%匹配任意多个任意字符;  
WHERE title LIKE ‘%戴尔%’; \#%匹配任意多个任意字符;  
WHERE title LIKE ‘*戴尔’; \#%匹配任意一个任意字符;  
WHERE title LIKE '戴尔*’; \#%匹配任意个任意字符;  
练：.查询出所有姓名中带字符E员工所有信息；  
SELECT \* FROM emp WHERE ename LIKE ‘%E%’;  
查询出所有姓名中以E开头的员工所有信息；  
SELECT \* FROM emp WHERE ename LIKE ‘E%’;  
查询出所有姓名中倒数第二个字符是E员工所有信息；  
SELECT \* FROM emp WHERE ename LIKE ‘%E\_’;  
查询出姓名中包含字符E或者A的员工所有信息；  
SELECT \* FROM emp WHERE ename LIKE ‘%E%’ OR ename LIKE ‘%A%’;  
10.简单查询————分页查询【重点】  
若要查询的满足条件的数据行太多，不方便一次性全部显示给用户，可以“一页一页”的显示。  
分页查询在不同的数据库  
语法：  
SELECT…  
FROM …  
WHERE …  
ORDER BY…  
LIMIT star,count;  
star 是一个数字，表从哪一行开始获取数据；  
count 是一个数据，表示一次最多读取多少行记录。  
假设：每页最多显示5条记录，  
第一页：…LIMIT 0,5;  
第二页：…LIMIT 5,5  
第三页：…LIMIT 10,5  
…  
第n页：…LIMIT (n-1)*5,5  
示例：查询所有员工，每页显示5条记录，要第1页，  
SELECT \* FROM emp LIMIT 0,5;  
示例：查询所有员工，每页显示5条记录，要第2页，  
SELECT \* FROM emp LIMIT 5,5;  
示例：查询所有员工，每页显示5条记录，要第3页，  
SELECT \* FROM emp LIMIT 10,5;  
示例：查询所有员工，每页显示5条记录，要第4页，  
SELECT \* FROM emp LIMIT 15,5;  
查询出姓名中包含字符E的员工信息，按照姓名升序排列，每页显示3条记录，显示第1页，  
SELECT \* FROM emp WHERE ename LIKE ‘%E%’ ORDER BY ename ASC LIMIT 0,3;  
查询出姓名中包含字符E的员工信息，按照姓名升序排列，每页显示3条记录，显示第2页，  
SELECT \* FROM emp WHERE ename LIKE ‘%E%’ ORDER BY ename LIMIT 3,3;  
查询出姓名中包含字符E的员工信息，按照姓名升序排列，每页显示3条记录，显示第3页，  
SELECT \* FROM emp WHERE ename LIKE ‘%E%’ ORDER BY ename LIMIT 6,3;  
11复杂查询————分组查询/聚合函数【难点】  
语法：MySQL提供五个聚合函数：  
COUNT()多少个 MIN() MAX() SUM()求和 AVG()平均  
示例：查询所有员工数量  
SELECT COUNT(eid) FROM emp;  
SELECT COUNT(ename) FROM emp;  
SELECT COUNT(deptId) FROM emp;  
练：查询10号部门的员工数量。  
SELECT COUNT(*) FROM emp WHERE deptId=10;  
查询所有员工工资最大值；  
SELECT MAX(salary) FROM emp;  
查询所有员工中年龄最大的生日；  
SELECT MIN(birthday) FROM emp;  
查询所有员工工资的总和；  
SELECT SUM(salary) FROM emp;  
查询所有员工工资的平均值（两种）；  
SELECT AVG(salary) FROM emp;  
SELECT SUM(salary)/COUNT(*) AS 平均工资 FROM emp;  
分组查询：  
语法：SELECT COUNT(…),SUM(…),AVG(…),MAX(…),MIN(…)  
FROM 表名  
GROUP BY 列名;  
示例: 查询出每个部门的编号，及该部门下员工的数量  
SELECT deptId,COUNT(*) FROM emp GROUP BY deptId;  
练：查询出每个部门的编号、平均工资、最大工资、最小工资  
SELECT deptId,AVG(salary),MAX(salary),MIN(salary) FROM emp GROUP BY deptId;  
12.复杂查询————子查询————【难点！】  
示例：查询“Development” 部门所有员工信息。  
步骤1：查询Development部门编号=>10  
SELECT did FROM dept WHERE dname=‘Development’;  
步骤2:查询10号部门中员工的所有信息。  
SELECT \* FROM emp WHERE deptId=10;  
整合步骤1和步骤2 ：  
SELECT \* FROM emp WHERE deptId=(  
SELECT did FROM dept WHERE dname=‘Development’  
);  
练习：查询出工资大于SMITH的工资的员工所有信息。  
SELECT \* FROM emp WHERE salary > (SELECT salary FROM emp WHERE ename=‘SMITH’);  
查询出比KING年长的员工所有信息。先查king的生日 再查大的.  
SELECT \* FROM emp WHERE birthday < (SELECT birthday FROM emp WHERE ename=‘KING’);  
查询出工资高于平均工资的员工信息。先查平均工资  
SELECT \* FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

[https_app.bytescout.com_sql-trainer_index.html]: https://app.bytescout.com/sql-trainer/index.html
[70]: /images/20220531/9d35ab4fc03143b0926a1dc54844609b.png
[70 1]: /images/20220531/a9acbd1b970d483890c116ca247bdf61.png
[70 2]: /images/20220531/bc44babdfd48425587bc548582c7e92f.png
[70 3]: /images/20220531/217d6f6827364abc85bf7a74f8e5c943.png