文章目录
登录数据库
## 密文密码进入
mysql -uroot -p
## 明文密码进入
mysql -uroot -p 密码
-u用户名
-hIP
-p密码
注释
单行注释“ - - ”
--
和注释内容中有一个空格
-- 注释内容
单行注释“#”
#注释内容
多行注释“/**/”
/*注释内容*/
内联注释“/*! */”
/*!注释内容*/
退出、帮助
操作 | |
---|---|
退出数据库 | exit 、\q 、quit |
帮助 | help |
查询命令 | help 部分命令 |
DDL | 数据定义语言 | create、drop |
---|---|---|
DML | 数据操作语言 | crud、insert、update、select |
DCL | 数据控制语言 | grant、revoke |
选择数据库 use
mysql> use 数据库名;
Database changed
mysql>
创建 create
创建数据库
注意
- 数据库名称使用英文
- 不要和原有数据库名称相同
语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
//创建普通数据库;[IF NOT EXISTS]表示不存在时
[DEFAULT] CHARACTER SET [=] charset_name
//设置编码格式
[DEFAULT] COLLATE [=] collation_name
示例
//创建数据库
CREATE DATABASE 数据库名;
//名为 数据库名 的数据库不存在,则进行创建
CREATE DATABASE IF NOT EXISTS 数据库名;
//名为 数据库名 的数据库不存在,则进行创建;并且编码为utf8mb4
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET utf8mb4;
创建数据表
创建MySQL数据表需要以下信息:
- 表名
- 表字段名 列名不用加引号
- 定义每个表字段
CREATE TABLE 表名 ( 列名 数据类型 [约束条件], 列名 数据类型 [约束条件], 列名 数据类型 [约束条件]);
CREATE TABLE IF NOT EXISTS 表名( 列名 INT(10), 列名 INT(10) AUTO_INCREMENT Unique, 列名 INT(10) AUTO_INCREMENT PRIMARY KEY, 列名 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, 列名 INT (10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, 列名 VARCHAR(2), 列名 VARCHAR(2) NOT NULL, 列名 VARCHAR(2) NOT NULL default “m” check (列名 in ("m","f")), 列名 DATE, PRIMARY KEY (列名,列名,列名))ENGINE=InnoDB DEFAULT CHARSET=utf8;)ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;
-
IF NOT EXISTS :如果不存在
-
IF EXISTS :如果存在
-
AUTO_INCREMENT :定义列为自增的属性,一般用于主键,数值会自动加1;
- 每张表只能有1个自动增长字段,自增字段是主键或者有唯一索引
- 将自动增长字段设置为非主键如果未添加唯一索引将会报错 --> id int auto_increment
-
NOT NULL :在操作数据库时如果输入该字段的数据为NULL ,就会报错
-
PRIMARY KEY :用于定义列为主键; 您可以使用多列来定义主键,列间以逗号分隔
- 使用 PRIMARY KEY 字段定义主键后,不能在最后使用它创建多个主键;只能使用一次
-
default “m” check (sgender in (“m”,“f”)) :只能写 m 或 f
-
ENGINE 设置存储引擎
-
CHARSET 设置编码
创建数据库 mysqladmin
[root@host]# mysqladmin -u root -p create 数据库名Enter password:******
复制表的结构
复制结构
create table 新表名 like 源表
复制结构、数据
create table 新表名 select * from 源表
删除 drop
删除数据库
格式:
drop database 数据库名;
删除数据表
语法
DROP TABLE 表名 ;
mysqladmin
[root@host]# mysqladmin -u root -p drop 数据库名Enter password:******
database [y/N] y
插入数据 insert
- 插入数据 字符、日期(2000-1-1) 需要带引号
INSERT INTO table_name ( field1, field2,...fieldN )VALUES ( value1, value2,...valueN );
- 实例
INSERT INTO table_name ( field1, field2,...fieldN )VALUES ( 'value1','value2','...valueN' );INSERT INTO table_name ( field1, field2,...fieldN )VALUES ( "value1", "value2","...valueN" );
复制表的信息
结构一样
insert into 表名 select * from 数据表 [where];
结构不一样
insert into 表名(列名) select 列名 from 数据表 [where];
更新 UPDATE
UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE 列="a"]
删除 DELETE
DELETE FROM table_name [WHERE Clause]
清空表 truncate
truncate 表名
查看表格格式
describe 表名;desc 表名;
查看 show
查看数据库中的所有库
show databases
查看库中的表
show tables
查看数据库基本信息
show create database 数据库名;+----------+----------------------------------------------------------------+| Database | Create Database |+----------+----------------------------------------------------------------+| mm | CREATE DATABASE `mm` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |+----------+----------------------------------------------------------------++----------+-------------------------------------------------------------------------------------+| Database | Create Database |+----------+-------------------------------------------------------------------------------------+| mh | CREATE DATABASE `mh` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |+----------+-------------------------------------------------------------------------------------+
查看表的创建信息信息
show create table 表名;+-------+--------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------+| zw | CREATE TABLE `zw` ( `id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`sex` varchar(5) DEFAULT '男',PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci|+-------+--------------------------------------------------------------------------------------------------+
修改表 ALTER
ALTER {DATABASE | SCHEMA} [db_name]ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME [DEFAULT] CHARACTER SET [=] charset_name //修改字符集 [DEFAULT] COLLATE [=] collation_name
修改数据库的字符集
- 查看字符集
show variables 1ike'characeer%';
- 修改数据库字符集
alter database testdb character set utf8;alter database testdb default character set utf8;//修改数据库的字符集,并设置为默认字符集,数据库中的表都会继承此字符集
修改表名
- 使用ALTER TABLE 语句中使用 RENAME 子句来实现
#尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl:ALTER TABLE testalter_tbl RENAME TO alter_tbl;
添加列
ALTER TABLE 表名 ADD 列名 INT [约束条件];##默认值为0ALTER TABLE 表名 ADD 列名 INT default 0;
- 位置
- 列默认添加到数据表字段的末尾
- FIRST (设定位第一列)
- AFTER 字段名(设定位于某个字段之后)
ALTER TABLE 表名 ADD 列名 INT FIRST;ALTER TABLE 表名 ADD 列名 INT AFTER 列名;
尝试以下 ALTER TABLE 语句, 在执行成功后,使用 SHOW COLUMNS 查看表结构的变化
删除表字段
-
如果数据表中只剩余一个字段则无法使用DROP来删除字段
-
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段
ALTER TABLE 表名 DROP 列名;
修改列类型
- 修改类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新类型 [约束条件];
修改名字 类型 CHANGE
- 在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型
ALTER TABLE testalter_tbl CHANGE 旧列名 新列名 新类型 [约束条件];
修改字段默认值
- 使用 ALTER 来修改字段的默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
- 使用 ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
-
修改数据表类型,可以使用 ALTER 命令及 TYPE 子句
-
**注意:**查看数据表类型可以使用 SHOW TABLE STATUS 语句。
ALTER TABLE testalter_tbl ENGINE = MYISAM;SHOW TABLE STATUS LIKE
删除表的约束
- 删除not null约束
alter table 表名 modify 列名 类型 [约束];
- 删除unique约束
alter table 表名 drop index 唯一约束名;
- 删除primary key约束
alter table 表名 drop primary key;
- 删除foreign key约束
alter table 表名 drop foreign key 外键名;
ALTER TABLE 对 Null 值和默认值的影响
- 当你修改字段时,你可以指定是否包含值或者是否设置默认值
- 如果你不设置默认值,MySQL会自动设置该字段默认为 NULL
#指定字段 j 为 NOT NULL 且默认值为100ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
查询 select
语法
SELECT 列名,列名FROM 数据库.表名,数据库.表名[WHERE 条件][LIMIT N][ OFFSET M]
-
*通配符 表示所有列
-
可以查询多个表使用 , 隔开
-
SELECT 命令可以读取 一条或者多条记录。
-
你可以使用 LIMIT 属性来设定返回的记录数。
-
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
查询当前用户
select user();
查询当前数据库
select database()
查看版本
select version();
选择判断 WHERE
语法
SELECT 表.列名[,表.列名,...] FROM 表[,表,...] [WHERE 条件] [GROUP BY 列名] [HAVING 条件] [ORDER BY .....] [LIMIT .......]
SELECT 列名, 列名,... FROM 表名1, 表名2...WHERE 条件
条件
WHERE 列名<100WHERE 列名 is nullWHERE 列名<=>nullWHERE 列名 is not nullWHERE 列名 IN (1,2,3)WHERE 列名 LIKE "3%"WHERE 列名 IN (1,2,3)
- 你可以使用 AND 或者 OR 指定一个或多个条件
WHERE 条件1 AND 条件2.....WHERE 条件1 OR 条件2.....
下表中实例假定 A 为 10, B 为 20
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false |
<=> | 安全等号,相当于等于 | (A = B) 返回false |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true |
- 如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据
列表查询 IN
列名 IN (列表)列名 NOT IN (列表)
- 尽量不要使用,in会放弃索引,进行全表扫描
模糊查询 LIKE
列名 LIKE ‘3%’
- 放弃索引,全表查询,效率低
符号 | 说明 |
---|---|
‘a%’ | 以a开头 |
‘%a%’ | 包含a |
‘_a%’ | 第二个字母是a |
- **% **表示任意多的任意字符
- _ 表示一个字符
正则表达式 regexp
列名 regexp '^root'
范围查询 BETWEEN
列名 between 1000 AND 1500
- 闭区间
排序 ORDER BY
SELECT 列名, 列名,... FROM 表名1, 表名2...ORDER BY 列名 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
- ASC 升序、 DESC降序
分组 GROUP BY
SELECT column_name, function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;
- 将相同结果放在一个组
- 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT sex,count(sex) from gxsf group by sex;
筛选 HAVING
SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;
分页 LIMIT
SELECT * FROM COMPANY LIMIT 条数;SELECT * FROM COMPANY LIMIT 起始位置,条数;
SELECT * FROM COMPANY LIMIT 4;SELECT * FROM COMPANY LIMIT 4,4;
where 和having之后都是筛选条件,但是有区别的:
-
where在group by前, having在group by 之后
-
聚合函数(avg、sum、max、min、count),不能作为条件放在where之后,但可以放在having之后
运算
运算 | 结果 | 说明 |
---|---|---|
SELECT 100+80 | 180 | 进行正常的运算 |
SELECT 100+‘80’ | 180 | 将字符转换为数字,进行运算 |
SELECT 100+‘abc’ | 100 | 将字符转换为数字,无法转换则为0 |
SELECT ‘THIS’+‘IS’ | 0 | 将字符转换为数字,无法转换则为0 |
SELECT NULL+80 | NULL | 其中一个值为NULL,则结果为NULL |
多表查询
- 在查询时没有指定关系,数据库无法确定关联关系,就行全部匹配,会出现笛卡尔积现象
select 表名.列名,表名.列名 from 表名,表名 where 表名.列名=表名.列名;
连接
内连接
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录
select 表名.列名,表名.列名 from 表名 [inner] join 表名 on(表名.列名=表名.列名);
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;以上 SQL 语句等价于:SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
外连接
- **LEFT JOIN(左连接):**以左表为主,显示全部,即使右边没有匹配记录
- **RIGHT JOIN(右连接):**获取右表所有记录,即使左表没有对应匹配的记录
select 表名.列名,表名.列名 from 表名 left join 表名 on(表名.列名=表名.列名);
select 表名.列名,表名.列名 from 表名 right join 表名 on(表名.列名=表名.列名);
自连接
- 表自身与自身关联:
- 每次查询出来的结果也是一个表
表 | ||
---|---|---|
id | name | up_id |
1 | 网络 | null |
2 | linux | null |
3 | 路由器 | 1 |
4 | 交换机 | 1 |
select inter.name,a.name upfrom inter left join (select id,name from inter where up_id is null) a on (inter.up_id=a.id)where up_id is not null;
子查询
select * from 表名 where 列名=(select 列名 from 表名 [where])
select * from 表名,select 列名 from 表名 [where]
联合查询 UNION
描述
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
- 两个查询语句列数要一样
语法
SELECT expression1, expression2, ... expression_nFROM tables[WHERE conditions]UNION [ALL | DISTINCT]SELECT expression1, expression2, ... expression_nFROM tables[WHERE conditions];
参数
- DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选,返回所有结果集,包含重复数据。
查看当前数据库与当前连接的概要信息
status
status--------------mysql Ver 14.14 Distrib 5.7.26, for Win64 (x86_64)Connection id: 7Current database: mmCurrent user: root@localhostSSL: Not in useUsing delimiter: ;Server version: 5.7.26 MySQL Community Server (GPL)Protocol version: 10Connection: localhost via TCP/IPServer characterset: utf8Db characterset: utf8mb4Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 3 hours 10 min 28 secThreads: 1 Questions: 42 Slow queries: 0 Opens: 340 Flush tables: 1 Open tables: 37 Queries per second avg: 0.003--------------
当前链接的id号当前选择的使用的数据库当前链接的登录用户是否使用了ss!当前会话的行终结符是分号当前mysql版本为mariadb分支当前mysql服务器的版本号协议版本使用的链接类型,通过本机的套接字文件进行连接服务器使用的字符类型当前数据库使用的字符类型当前mysql客户端使用的字符类型当前链接使用的字符类型套接字文件路径数据库的启动时长
replace