MySQL--基本操作

最新推荐文章于 2024-10-28 17:56:47 发布

原创最新推荐文章于 2024-10-28 17:56:47 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

1 篇文章

订阅专栏

一、MySQL的基本操作：

1.在命令行中启动：
net start mysql80

2.登录：
mysql -u用户名 -p密码

3.退出：q
uit/exit

4.修改密码：
mysqladmin -u用户名 -p原密码 password
（输入新密码）

5.显示当前连接的信息：
select database();  数据库
Select version();  版本
Select now();  当前时间
Select user();  当前用户

二、数据库的基本操作

1.创建数据库：
Create  database  数据库名；
Create  database  if  not  exist  数据库名;

2.删除数据库：
Drop  database  [if  exist]  数据库名;

3.数据库存储引擎：
查看系统支持的引擎：show engines;
查看默认的存储引擎：show variables like ‘%storage_engine%’;

4.显示所有数据库
Show databases;  

5.使用数据库（在创建表前必须先使用数据库）
use 数据库名;

三、数据表的基本操作

1.创建数据表：（在创建数据表之前，要先引用指定的数据库）
Create  table  数据表名(
字段1  数据类型  [约束条件],
字段2  数据类型  [约束条件],
……
[约束条件]
);

其中约束条件有以下几种情况：
（1）使用主键约束：
    单子段主键：在定义列的同时定义主键或定义完所有列后再定义主键
    Id INT(11) PRIMARY KEY 或：CONSTRAINT 约束名 PRIMARY KEY（字段名）
    多字段主键：PRIMARY KEY(字段1，字段2 ，……）
（2）使用外键约束：
    CONSTRAINT 外键名 FOREIG KEY(字段名） REFERENCES 主表名（主键名）
（3）使用唯一性约束：字段末尾UNIQUE 或 CONSTRAINT 约束名 UNIQUE（字段名）
（4）使用默认值：字段名 数据类型 DEFAULT 默认值
（5）属性值自动增加：AUTO_INCREMENT

2.查看表的数据结构：
DESCRIBE 表名
DESC 表名
SHOW CREATE TABLE 表名
SHOW CREATE TABLE 表名\G  （更简洁）

3.修改数据表：
修改表名：         ALTER TABLE 旧表名 RENAME TO 新表名
修改字段的数据类型：ALTER TABLE 表名 MODIFY 字段名 数据类型
修改字段名：       ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型
添加字段：         ALTER TABLE 表名 ADD 字段名 数据类型 [FIRST/AFTER 字段名]
删除字段：         ALTER TABLE 表名 DROP 字段名
修改字段的排列位置：ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2
修改表的引擎：     ALTER TABLE 表名 ENGINE=新引擎
删除表的外键：     ALTER TABLE 表名 DROP FOREIGN KEY 外键名

4.删除数据表：
DROP TABLE [IF EXIST] 表1，表2，……
删除被其他表关联的主表：先删除外键，再删除表

四、查询语句

1、基本查询语句

Select *|字段名
From表1，表2，……
[Where表达式
Group by……
Having 表达式
Order by……
Limit ……];

Select 字段1,字段2,……
From 表
WHERE 查询条件;

2、单表查询

1.查询所有字段
select * from 表名;
select 全部字段 from 表名;

2.查询指定字段
select 字段名1,字段2，……，字段n from 表名;

3.查询指定记录
select 字段1，字段2，……
from 表名
where 查询条件;

4.带IN的查询
select s_id, f_name
from fruits
where s_id IN (101,102);

5.带BETWEEN AND的范围查询
select f_name, f_price from fruits where f_price BETWEEN 2.00 AND 10.20;

6.带LIKE的字符匹配查询
（1）%匹配任意长度的字符
（2）下划线'_'只能匹配任意一个字符

7.带AND的多条件查询
select f_id, f_price from fruits where s_id='101' AND f_price>=5;

8.带OR的多条件查询
select s_id,  f_name, f_price from fruits where s_id=101 OR s_id=102;

9.查询结果不重复
select distinct 字段名 from 表名;

10.对查询结果排序 ORDER BY
select f_name from fruits ORDER BY f_name;
select f_name, f_price from fruits ORDER BY f_name, f_price;
select f_name, f_price from fruits ORDER BY f_price DESC;    降序

11.分组查询(分组查询一般与集合函数一起使用，如MAX(), MIN(), COUNT()等)
分组：GROUP BY 字段1，字段2，……
过滤分组：HAVING 条件
select s_id ,COUNT(*) AS total from fruits GROUP BY s_id HAVING COUNT(f_name)>1;

在GROUP BY 字句中使用WITH ROLLUP，可在所查询出的分组记录之后增加一条记录，记录所有查询记录之和
select s_id ,COUNT(*) AS total
from fruits
GROUP BY s_id WITH ROLLUP;

12.使用LIMIT 限制查询结果
select * from fruits LIMIT 4;  查询结果的前4行
select * from fruits LIMIT 4,3;  从第五个记录开始，行数为3

3.使用聚合函数查询

AVG() 平均值

COUNT() 返回某列的行数

MAX()

MIN()

SUM()

4.连接查询

（1）内连接查询：FROM表 1 INNER JOIN 表2 ON 表达式

SELECT s_id,s_name,f_name
FROM fruit,suppliers
WHERE fruit.s_id=suppliers.s_id;
等价于：
SELECT suppliers.s_id, s_name, f_name, f_price
FROM fruit INNER JOIN suppliers
ON fruit.s_id=suppliers.s_id;

自连接查询：（同一个表）
SELECT f1.s_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id=f2.s_id AND f2.f_id='a1';

（2）外连接查询

左连接：LEFT OUTER JOIN

左连接的结果包括指定的左表所有的行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

SELECT customers.c_id, o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id=orders.c_id;

右连接：RIGHT JOIN

与左连接相反

SELECT customers.c_id, o_num
FROM customers RIGHT OUTER JOIN orders
ON customers.c_id=orders.c_id;

5.子查询：带ANY/SOME，ALL，EXISTS，NOT EXISTS，IN，比较运算符

（1）SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2);
（2）SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2);
（3）SELECT * FROM fruits
     WHERE EXISTS
     (SELECT s_name FROM suppliers WHERE s_id=107);
EXISTS 后面的子查询如果有返回行说明True，此时外层查询语句进行；如果没有返回行，则为FALSE，此时外层查询语句不进行
（4）SELECT c_id FROM orders WHERE o_num IN
    (SELECT o_num FROM orderitems WHERE f_id='c0');
（5）SELECT s_id, f_name FROM fruits
    WHERE s_id=
    (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city='Tianjin');

6.合并查询结果

UNION 将多条SELECT查询结果合成单个结果表，删除重复行

UNION ALL 合并时不删除重复行业不对结果进行自动排序

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price<9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN (101,103);

7.使用正则表达式查询(REGEXP)

^	匹配文本的开始字符
$	文本结束字符
.	匹配任何单个字符
*	匹配0个或多个在他前面的字符
+	匹配前面的字符一次或多次
‘字符串’	匹配包含指定的字符串的文本
[ ]	匹配字符集合中的任何一个字符
[^ ]	匹配不包括在括号中的任何字符
{n, }	匹配前面的字符串至少n次
{n,m}	匹配前面的字符串至少n次，至多m次

SELECT * FROM fruits WHERE f_name REREXP ‘^be’;
SELECT * FROM fruits WHERE f_name REGEXP ‘y$’;
SELECT * FROM fruits WHERE f_name REGEXP ‘a.g’;
SELECT * FROM fruits WHERE f_name REGEXP ‘^ba*’;
SELECT * FROM fruits WHERE f_name REGEXP ‘^ba+’;
SELECT * FROM fruits WHERE f_name REGEXP ‘on’;
SELECT * FROM fruits WHERE f_name REGEXP ‘on|ap’;
SELECT * FROM fruits WHERE f_name REGEXP ‘[345]’;
SELECT * FROM fruits WHERE f_name REGEXP ‘[a-e1-2]’;
SELECT * FROM fruits WHERE f_name REGEXP ‘x{2,}’;
SELECT * FROM fruits WHERE f_name REGEXP ‘x{1,3}’;

五.插入、更新和删除数据

1.插入数据

INSERT INTO table_name (字段名） VALUES （值1）, （值2）, ……

插入数据时可以指定字段名，也可以不指定字段名，当指定字段名时，字段列的顺序可以不和表中定义的列的顺序一样，当不指定字段名时，插图值得顺序要和表定义的列的顺序一样；可以插入全部字段，也可以插入指定一部分字段。

指定字段名：
INSERT INTO person (age, name, id, info)
VALUES(22,'Suse',2,'dancer');
不指定字段：
INSERT INTO person 
VALUES(22,'Suse',2,'dancer');
同时插入多条数据：
INSERT INTO person (naem, age, info)
VALUES('Evans',27,'secretary'),('Dale',22,'cook');

将查询结果插入到表中：

INSERT INTO 表1 （字段）SELECT （字段） FROM 表2 WHERE 条件

INSERT INTO person (id, name ,age, info)
SELECT id, name, age, info FROM person_old;

2.更新数据

UPDATE 表名

SET column_name1 = values1, column_name2 = values2,……

WHERE 条件;

UPDATE person SET age=15, name='LiMing' WHERE id=11;

3.删除数据（DELETE）

DELETE FROM table_name [WHERE 条件]

当没有指定条件时，删除整个表中的内容

DELETE FROM person WHERE age BETWEEN 19 AND 22;

六、索引

索引的分类：

（1）普通索引和唯一索引：唯一索引（UNIQUE）的列的值必须唯一，但允许有空值（主键索引是一种特殊的唯一索引，不允许有空值）

（2）单列索引和组合索引：使用组合索引时要遵循最左前缀集合

（3）全文索引：FULLTEXT，全文索引只为CHAR, VARCHAR, TEXT列创建索引，MySQL中只有MyISAM存储引擎支持全文索引

（4）空间索引：SPATIAL, 对空间数据类型的字段建立的索引，MySQL中的四种空间数据类型：GEOMETRY、POINT、LINESTRING、POLYGON。空间索引的列必须为 NOT NULL，空间索引只能在MyISAM存储引擎的表中创建。

1、创建索引

（1）在创建表的时候创建索引：

CREATE TABLE 表名 [字段名字段类型]

[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (列名（索引长度）） [ASC|DESC]

#index 和key是同义词，都是用来创建索引。

1.创建普通索引    
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
cement VARCHAR(255) NULL,
year_publication YEAR NOT NULL.
INDEX(year_publication)
);

SHOW CREATE TABLE book \G         #查看表结构
explain select * from book where year_publication=1990 \G   #查看索引是否正在使用

2.创建唯一索引
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

3.创建单列索引
CREATE TABLE t2
(id INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);

4.创建组合索引
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
INDEX MultiIdx(id, name, age(100))
);

5.创建全文索引
CREATE TABLE t4 
( id INT NOT NULL,
name CHAR(30) NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx (info)
)ENGINE=MyISAM;

6.创建空间索引
CREATE TABLE t5
(g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM;

注意：在组合索引中，遵循“最左前缀”，例如上面由id, name, age构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下列组合：（id,name,age）、(id,name)、id,不能搜索(name,age)或者(age).

（2）在已经存在的表上创建索引

ALTER TABLE 或者 CREATE INDEX

使用ALTER TABLE语句创建：

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (字段(长度） [ASC|DESC]

# ASC和DESC表示升序或降序

CREATE TABLE book ADD INDEX BkNameIdx(bookname(30));   #创建普通索引，这里也是单列索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx(bookid);   #创建唯一索引
ALTER TABLE book ADD INDEX BkAuAndInfoIdx(authors(30),info(50));  #创建组合索引

创建全文索引，引擎要为MyISAM
CREATE TABLE t6
(
id INT NOT NULL,
info CHAR(255)
)ENGINE=MyISAM;
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info);    #创建全文索引  

创建空间索引，引擎要设为MyISAM
CREATE TABLE t7
(g GEOMETRY NOT NULL)ENGINE=MyISAM;
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);           #创建空间索引

查看指定表中创建的索引：

SHOW INDEX FROM 表名 \G

使用 CREATE INDEX 创建索引

CREATE INDEX [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名(长度)) [ASC|DESC]

CREATE INDEX BkNameIdx ON book(bookname);                    #创建普通索引
CREATE UNIQUE INDEX UniqidIdx ON book(bookid);               #创建唯一索引
CREATE INDEX BkAuAndInfoIdx ON book(authors(20),info(50));   #创建组合索引
CREATA FULLTEXT INDEX ON t6(info);                           #创建全文索引
CREATE SPATIAL INDEX spatIdx ON t7(g);                       #创建空间索引

2、删除索引

使用ALTER TABLE 删除索引：

ALTER TABLE 表名 DROP INDEX 索引名;

使用DROP INIDEX 删除索引

DROP INDEX 索引名 ON 表名;

ALTER TABLE book DROP INDEX UniqidIdx;

DROP INDEX UniqidIdx ON book;