Mysql数据库基础操作

最新推荐文章于 2024-04-18 04:54:06 发布

SepstoneTang

最新推荐文章于 2024-04-18 04:54:06 发布

阅读量567

点赞数

分类专栏： Mysql数据库

本文链接：https://blog.csdn.net/qq_38756992/article/details/84991461

版权

Mysql数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.mysql安装:官网下载地址：https://dev.mysql.com/downloads/mysql/

2.数据库基本操作命令

2.1登入连接及选择数据库

notice:以管理员方式运行cmd，mysql配置了path路径，如果没有配置就切换到安装mysql的bin目录再操作如：

mysql数据库服务的启动 net start mysql

mysql数据库服务的停止 net stop mysql

登入

root用户 mysql -u root -p 回车输入你设置的密码，默认不设置密码就直接回车，默认若有密码是root

mysql -uroot P端口号 -h 192.168.31.95 -p密码#远程登录，用户为root@192.168.31.95

设置密码

2.4.1 没登入mysql时 mysqladmin -u root -p123 password '1234' #修改root用户密码

2.4.2 进入mysql库修改user表

mysql>use mysql;

mysql>update user set password=password('你的密码') where u ser='root'; mysql>flush privileges;

查看当前版本信息mysql>select VERSION();

取消正在输入的语句换到mysql>命令等待状态 mysql>\c

显示数据库 mysql>show databases ;

查看显示表

mysql> use 数据库名;

mysql>show tables;

mysql中日期是以2017-11-07为标准形式的,mysql有好一些对日期进行格式化及日期计算的函数

mysql> SELECT CURRENT_DATE;

创建数据库

mysql>create database 数据库名;

找出当前选择了哪个数据库，如果没有就显示null mysql>SELECT DATABASE();

选择某数据库，之后就可以对数据进行相关操作。mysql> use 数据库名

还有一种方式是指定字符集和排序规则：

create database 数据库名 default character set 字符集 collate 排序规则;

mysql> create database databse1 default character set utf8 collate utf8_general_ci;

练习表下载地址：http://www.forta.com/books/0672327120/

2.2检索数据

select 语句：

计算器：比如SELECT 12-5;

检索单个列：SELECT 列名/字段名 FROM 表名;

mysql> SELECT prod_name From products；

检索多个列 SELECT 字段名/列名，字段名/列名 FROM 表名;

mysql>SELECT prod_id,prod_name,prod_price FROM products

检索所有列语法：SELECT * FROM 表名;

mysql>SELECT * FROM products;

检索不同行语法：SELECT DISTINCT 字段名/列名 FROM 表名;

mysql>SELECT DISTINCT vend_id FROM products;

指定返回行数不多于指定行数，SELECT 列名/字段名 FROM 表名 LIMIT 行数

mysql>SELECT prod_name FROM products LIMIT 5;

指定要检索的开始行数和返回行数

语法： SELECT 列名/字段名 FROM 表名 LIMIT 开始位置 , 行数;

mysql>SELECT prod_name FROM products LIMIT 5，5；

2.3排序检索

排序单列：SELECT 字段名 FROM 表名 ORDER BY 按什么字段名排序;

多个字段用,分隔，排序先按order by前面的的字段排,相同时才按后面字段排

mysql>select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

指定排序方向:DESC降序 ASC升序默认升序

语法： SELECT 字段名 from 表名 ORDER BY 按什么字段排序 DESC;

mysql>select prod_id,prod_price,prod_name from products order by prod_price desc;

按中文排序：语法:SELECT * FROM 表名 ORDER BY CONVERT(包含中文列字段 USING gbk);

注意：UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序。

mysql>select * from products order by convert(prod_name using gbk);

2.4过滤数据

where子句操作符
操作符	说明
=	等于
<>	不等于
！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定两个值之间

在同时使用order by和where 子句时，应该让order by 位于where 之后，否则会产生错误

语法：SELECT 字段名 from 表名 where 字段名 =2字段相对应要指定查询的值；

mysql>select prod_name ,prod_price from products where prod_price =2.50；

WHERE子句-AND操作符

语法：[select 列1，列2，列3... from 表名 where 条件1 and 条件2;]

如下例：mysql> select prod_id,prod_price,prod_name from products where vend_id =1003 and prod_price <= 10;

WHERE子句-OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。

语法如下： [select 列名1，列名2...from 表名 where 条件1 or 条件2];

如下例： mysql>select prod_name,prod_price from products where vend_id = 1002 or vend_id =1003;

WHERE子句-IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。

案例：mysql> select vend_id,prod_name,prod_price from products where vend_id in(1002,1003) order by prod_name;

WHERE子句-NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

为了列出除1002和1003之外的所有供应商制造的产品

mysql> select vend_id,prod_name,prod_price from products where vend_id not in(1002,1003) order by prod_name;

2.5通配符

过滤-LIKE

在搜索串中，%表示任何字符出现任意次数。为了找出所有以词jet起头的产品，可使用以下SELECT语句：

mysql>select prod_id,prod_name from products where prod_name like 'jet%';

通配符过滤-多个百分号%,搜索模式'%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

mysql>select prod_id,prod_name from products where prod_name like '%anvil%';

通配符过滤-下划线(_)

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

mysql> select prod_id,prod_name from products where prod_name like '_ ton anvil';

2.6正则表达式

匹配基本字符

mysql> select * from products where prod_name regexp '1000' order by prod_name;

进行or匹配

mysql> select * from products where prod_name regexp '1000|2000' order by prod_name;

匹配几个字符之一

mysql> select * from products where prod_name regexp '[123] Ton' order by prod_name;

匹配范围

mysql> select * from products where prod_name regexp '[1-5] Ton' order by prod_name;

匹配字符类

匹配多个实例

mysql> select * from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name ;

mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'[[:digit:]]{4}' ORDER BY prod_name；

定位符：

mysql>SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

2.6使用数据处理函数

文本处理函数

使用方法案例

mysql> select vend_name,Upper(vend_name) AS vend_name_upcase from vendors order by vend_name;

2.7 日期和时间处理函数

使用案例如下

mysql>select cust_id,order_num from orders where Date(order_date) = '2005-09-01';

2.8数值处理

使用案例如下：

mysql>select abs(-12);

2.9聚集函数

使用案例如下：

mysql>select avg(prod_price) as avg_price from products;

mysql>select count(*) as num_cust from customers;

mysql>select max(prod_price) as max_price from products;

mysql>select min(prod_price) as min_price from products;

mysql>select sum(quantity) as items_ordered from orderitems where order_num = 20005;

组合聚集函数

mysql>select count(*) as num_items, min(prod_price) as price_min , max(prod_price) as price_max, avg(prod_price) as price_avg from products;

3.0数据分组

创建分组

mysql>select vend_id,count(*) as num_prods from products group by vend_id;

HAVING过滤分组

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。WHERE过滤行，而HAVING过滤组！！

mysql>select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

分组和排序

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;

select 子句及顺序

select子句及其顺序
|   子句   |   说明   |   是否必须使用   |
|:---------|----------|-----------------:|
| select   |要返回的列或者表达式| 是    |
| from    |从中检索数据的表| 仅在从表检查数据时使用|
| where   |行级过滤 |       否         |
| group by|分组说明 |仅在按组计算聚集时使用|
| having |组级过滤 |       否         |
| order by|输出排序顺序|       否       |
| limit   |要检索的行数|       否       |

3.1使用子查询

  子查询
   mysql4.1或更高版本支持
   查询(query)任何sql语句都是查询，此术语一般只select。
   子查询(subquery)即嵌套在其他查询中的查询。
利用子查询进行过滤
mysql> select order_num from orderitems where prod_id = 'TNT2';
   mysql> select cust_id from orders where order_num IN (20005,20007);
   mysql> select cust_id from orders where order_num IN (select order_num from orderitems where prod_id = 'TNT2');
   mysql> select cust_name, cust_contact from customers where cust_id IN (10001,10004);
   mysql> select cust_name, cust_contact from customers where cust_id IN (select cust_id from orders where order_num IN (select order_num from orderitems where prod_id = 'TNT2'));
作为计算字段使用子查询
   mysql> select cust_name, cust_state, (select COUNT(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
   相关子查询(correlated subquery)涉及外部查询的子查询
   mysql> select cust_name, cust_state, (select COUNT(*) from orders where cust_id = cust_id) as orders from customers order by cust_name;

3.2联结
   sql最强大的功能之一是能在数据检索查询的执行中联结(join)表。
创建联结
mysql>select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
   完全限定列名：当引用的列可能出现二义性时，必须使用完全限定列名(用一个点分隔表名和列名)，否则，mysql将返回错误。
where子句的重要性
     笛卡儿积(cartesian product) 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行数将是第一个表中的行数乘以第二个表中的行数。
mysql>select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;
   内部联结
     目前为止用的联结称为等值联结(equijoin),它基于两个表之间的相等测试，这种联结也成为内部联结。
mysql>select vend_name, prod_name, prod_price from vendors
     INNER JOIN products ON vendors.vend_id = products.vend_id;
联结多个表
     mysql>select prod_name, vend_name, prod_price, quantity from orderitems, products, vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;
mysql> select cust_name, cust_contact from customers where cust_id IN (select cust_id from orders where order_num IN (select order_num from orderitems where prod_id = 'TNT2'));【嵌套的子查询】
     mysql>select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2';【两个联结】
多做实验 --- 如上所见，执行一个给定sql操作，一般存在不止一种方法。性能可能会受操作类型、表中的数据量、是否存在索引或键以及其他条件的影响。
性能考虑 --- mysql在运行时关联指定每个表以处理联结。这种处理可能非常消耗资源，应慎重，不要联结不必要的表。联结的表越多，性能下降地越厉害。  使用哪种语法 -- ANSI SQL规范首选inner join语法。尽管，where子句定义联结的确比较简单，但使用明确的联结语法能够确保不会忘记联结条件，有时这样做也能影响性能。不要忘了where子句，应保证所有联结都有where子句，否则，mysql将返回比想要的多得多的数据。叉联结(cross join)有时我们会听到返回称为叉联结的笛卡儿积的联结类型。

3.3创建高级联结
讲解另外一些联结类型/含义/使用方法，如何对被联结的表使用表别名和聚集函数。
使用表别名
mysql>select Concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title from vendors order by vend_name;
   mysql>select cust_name, cust_contact from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'TNT2';
使用不同类型的联结
迄今为止，我们使用的只是称为内部联结或等值联结(equijoin)的简单联结。下面介绍其他3种联结：自联结、自然联结和外部联结。
   自联结
     使用表别名能在单条   select语句中不止一次引用相同的表。
  mysql>select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');
mysql>select p1.prod_id, p1.prod_name from products as p1, products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
自然联结
    自然联结是这样一种联结，其中你只能选择哪些唯一的列。一般是通过对表使用select(*)通配符。
mysql>select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'FB';
    迄今为止，我们建立的每个内部联结都是自然联结。
外部联结
     联结包含了那些在相关表中没有关联行的行，这个类型的联结称为外部联结。
mysql>select customers.cust_id, orders.order_num from customers INNER JOIN orders ON customers.cust_id = orders.cust_id;
mysql>select customers.cust_id, orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
mysql> select customers.cust_id, orders.order_num from customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
使用带聚集函数的联结
mysql>select customers.cust_name, customers.cust_id, COUNT(orders.order_num) as num_ord from customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
mysql>select customers.cust_name, customers.cust_id, COUNT(orders.order_num) as num_ord from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
使用联结和联结条件
1)注意使用的联结类型，一般使用内联结。
2) 保证使用正确的联结条件，否则返回不正确的数据。
3)应该总是提供联结条件，否则会得出笛卡儿积。
4)在一个联结中可以包含多个表，甚至对于每个联结可以采用不同类型的联结，虽然合法，但应分别测试每个联结。
   没有*=操作符 --- mysql不支持简化字符*=和=*的使用，这两个操作符在其他DBMS中是很流行。
外部联结的类型 --- 做外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。即左外部联结可通过颠倒from和where子句中的表的顺序为由外部联结。

3.4组合查询
利用union操作符将多条select语句组合成一个结果集。
组合查询
   mysql允许执行多个查询(多条select语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。
   使用组合查询的情况：
    在单个查询中从不同的表返回类似结构的数据
    对单个表执行多个查询，按单个查询返回数据
创建组合查询
使用union
     在给出的每条select语句之间放上关键字union
   mysql>select vend_id, prod_id, prod_price from products where prod_price <= 5;
mysql>select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002);
mysql>select vend_id, prod_id, prod_price from products where prod_price <= 5
     UNION
     select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002);
   union规则
1) union必须由两条及两条以上的select语句组成，语句之间用union关键字分开。
2)union中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
    3)列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换类型。
包含或取消重复行
     union的默认行为，重复的行自动取消，如果想返回所有匹配的行，可使用union all。
mysql>select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002);
mysql>select vend_id, prod_id, prod_price from products where prod_price <= 5
     UNION ALL
     select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002);
对组合查询结构排序
     select语句的输出用order by子句排序。在使用union组合查询时，只能用一个order by，出现在最后一个select语句后。mysql实际是用它来排序所有的select语句。
mysql>select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002);
mysql>select vend_id, prod_id, prod_price from products where prod_price <= 5
     UNION
     select vend_id, prod_id, prod_price from products where vend_id IN (1001,1002) order by vend_id, prod_price;

组合不同的表可使用union组合查询应用不同的表。

3.5全文本搜索
学习如何使用mysql的全文本搜索功能进行高级的数据查询和选择。
理解全文本搜索
   并非所有的引擎都支持全文本搜索。两个最常用的引擎为MyISAM和InnoDB，前者支持全文搜索，后者不支持。
   搜索机制(之前的like、正则)存在几个重要的限制:
1)性能 —— 通配符和正则匹配通常匹配表中所有行(这些搜索极少使用表索引)。因此，搜索行增加，搜索可能非常耗时。
   2) 明确控制 —— 使用通配符和正则很难明确控制匹配什么和不匹配什么。
3) 智能化的结果 —— 虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们不能提供一种智能化的选择结果。
使用全文本搜索
   启用全文本搜索支持
     一般在创建表时启用全文本搜素。create table语句接收FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。
     不要在导入数据时使用FULLTEXT —— 更新索引要花时间。创建一个新表，应先导入所有数据，再修改表定义FULLTEXT。
   进行全文搜索
Match()和Against()执行全文本搜索。
Match()指定被搜索的列
     Against()指定要使用的搜索表达式
mysql>select note_text from productnotes where Match(note_text) Against('rabbit');
mysql>select note_text from productnotes where note_text like '%rabbit%';【like实现】
     mysql>select note_text, Match(note_text) Against('rabbit') as rank from productnotes;
使用查询扩展
     查询扩展功能只用于mysql版本4.1.1或更高级的版本
mysql>select note_text from productnotes where Match(note_text) Against('anvils');【未使用只查询出1条】
     mysql>select note_text from productnotes where Match(note_text) Against('anvils' WITH QUERY EXPANSION);
布尔文本搜索
     mysql支持全文本搜索的另外一种形式，布尔方式(boolean mode)。是个非常缓慢的操作。
     提供以下细节：
要匹配的词
1)要排斥的词
     2)排列提示
     3)表达式分组
     -4)等
     select note_text from productnotes Match(note_text) Against('heavy' IN BOOLEAN MODE);
     select note_text from productnotes Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
     select note_text from productnotes Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);【包含】
     select note_text from productnotes Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);【或】
     select note_text from productnotes Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);【短语】
     select note_text from productnotes Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);【增减等级】
     select note_text from productnotes where Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
   -全文本搜索的使用说明
1)短词（<=3的字符）被忽略且从索引中排除
2)mysql自带一个内建非用词(stopword)列表，全文本搜索时被忽略。
3) 忽略单引号。
     4)不具有词分隔符的语言不能恰当地返回全文本检索结果。
     5)仅在MyISAM数据库引擎中支持全文本搜索。全文本布尔操作符
|   布尔操作符   |   说明   |
|:---------------|---------:|
|       +        |包含，词必须存在|
|       -        |排除，词必须存在|
|       >        |包含，而且增加等级值|
|       <        |包含，而且减少等级值|
|      ()        |把词组成子表达式|
|       ~        |取消一个词的排序值|
|       *        |词尾的通配符|
|      ""        |定义一个短语|
全文本搜索的一个重要部分就是对结果排序，符合条件的文本出现的次序越靠前，优先级越高，越先返回。
使用完整match()说明 -- 传递给match的值必须与FULLTEXT()定义一样，，如果指定多个列，必须列出它们(次序正确)
搜索不区分大小写，除非使用binary方式。

3.6操纵表

创建表

常用的约束

CREATE TABLE 表名（

列名1 列的类型 [约束],

名2 列的类型 [约束]，

............ 列表N 列的类型约束）

注意：

1.最后一行没有逗号

2.不能使用mysql的关键字作为标识符。如（order是表中排序关键字）

解决方式：

01.使用反引号`order`; 02.开发中习惯t_表名;

CREATE TABLE `order` (
`order_num` int(11) NOT NULL AUTO_INCREMENT,
`order_date` datetime NOT NULL,
`cust_id` int(11) NOT NULL,
PRIMARY KEY (`order_num`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8;

更新表

修改表vendors增加vend_phone 字段
mysql> ALTER TABLE vendors ADD vend_phone CHAR(20);
修改表vendors删除vend_phone 字段
mysql> ALTER TABLE vendors DROP COLUMN vend_phone

删除表

DROP TABLE 表名；

重命名表

RENAME TABLE 原表名 TO 新表名;

3.7插入

创建如下表

mysql>insert into mytable(列名1,列名2) values('小爱','女');

mysql>insert into 表名 values(列1的值，列2的值，列3的值....);

插入多个列

mysql>insert into mytable(name,sex) values('shaw','男'),('John','男');

3.8删除更新

更新数据

mysql>update customers set cust_email = '1120450882@qq.com' where cust_id =10005;

删除数据

指定行 mysql>delete from customers where cust_id = 10006;

清空表里面的数据（逐行删除） mysql>delete from 表名；

清空表里面的数据（直接删除表，再新建一个相同的新的表结构） mysql>truncate 表名；

3.9数据库备份，导入导出

使用mysqlddump命令进行备份一个数据库

mysqldump u用户名 -p密码数据库名[表名1，表名2]>D:XX.sql(路径)

备份多个数据库

mysqldump u用户名 -p密码数据库名1,数据库名2>D:XX.sql(路径)

备份所有数据库

mysqldump u用户名 -p密码 --all-databases>D:XX.sql(路径)

从本地导入数据库

mysql -u用户名 -p密码数据库名 < D:XX.sql(路径) ；

mysqld字符集设置

mysqldump -uusername -ppassword --default-character-set=gb2312 db1 table1 > tb1.sql

SepstoneTang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库基础操作

1.mysql安装:官网下载地址：https://dev.mysql.com/downloads/mysql/2.数据库基本操作命令 2.1登入连接及选择数据库 notice:以管理员方式运行cmd，mysql配置了path路径，如果没有配置就切换到安装mysql的bin目录再操作如： mysql...
复制链接

扫一扫